Performance tuning issues

I have built a solution which runs for two hours on a server with 4CPU 2GHz each and 2GB of RAM on windows 2000 server (CPU utilization almost 70% and almost out of RAM). I moved the two source databases and the solution to a new box runing 8 xeon's at 3GHz each and 16GB of RAM running widows 2003 server 32bit and it still runs for 2 hours (CPU utilization 10% and ample RAM left).

I was expecting it to run much faster. So I started exploring the performance tuning features in SSIS and started tweaking the following:

Control Flow:

DefaultBufferMaxRows: Based on row size and buffer size, calculated the max rows.

DefaultBufferSize: Set this to max 100MB

DataFlow Destination:

Rows Per Batch: Set this to equal to the numbe of rows expected from the source.

Maximum Insert Commit Size: Set this to zero since memory was not an issue

I took the recommendations from other threads on similar issues here including the excellent recommendations at http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx but now the job is running for 6 hours.

Can anyone explain what I am doing wrong here? I have tried each of the above one by one and all together. No matter what combination I try it does not work any faster and both source and destination database are on the same server. Even selects from the same database also slowed down from 10 minutes to one hour.

Any assistance is appreciated, I need to get this job run in an hour.

Thanks!

- Philips.

How complex is your solution? I would recommend you to look at a lower grain;take a look a the log execution and compare it againt previous logs in the old server to see if you can identify a specifc part of the process as the bottleneck...|||

I'd also recommend watching the OVAL webcast that I talk about here:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||Did you setup the server to access more than 4gb of memory?

You need to configure Windows and then SQL server.|||Yes, SQL server is using around 14GB of memory and awe is turned on. Thanks!|||

It is a financial warehouse job which collects data from an ERP system loads a staging area and then the datamart. It also creates aggreagate tables. There are around 8 packages called from the master package.

My problem is I cannot find any way of using those four performance tuning settings accurately.

Any changes I make to the default setting is slowing the job down.

|||

Thanks! I went through it.

I am at a point where I am willing to drop all the control flow task and use plain old insert into statements and use the power of the DBMS engine than try to get the SSIS engine to do it. Unless I figure out what is wrong and why.

|||

Philips-HCR wrote:

Thanks! I went through it.

I am at a point where I am willing to drop all the control flow task and use plain old insert into statements and use the power of the DBMS engine than try to get the SSIS engine to do it. Unless I figure out what is wrong and why.

There's nothing wrong with doing that. The use of SSIS does not dictate that you should use data-flows to move your data about. If SQL is an option then invariably it will be the best option. it depends on your requirements and your preferences. You can issue SQL from an Execute SQL Task and still leverage all the other good stuff in SSIS like logging, workflow, portability etc... if you so wish.

-Jamie

Performance problems when starting the NS-Instance

Hello,

I've migrated a notification service solution from SQL 2000 to SQL 2005. The solution works fine but when I start the NS-Instance on SQL 2005 the CPU goes up to 100% and stays there.
I could not follow the migration advice on MSDN because the new SQL 2005 DB is on a different server than SQL 2000. So i figured out my own workaround. The solution works fine but I don't know why the CPU goes up on 100% !!

Here's my migration szenario:

1. I made a backup of the two instance databases from SQL 2000
2. On a diferrent server I installed SQL 2005
3. I adapted the ADF-file (changed the notify syntax and in my case new server name and paths)
4. In SQL mmanagement Studio 2005 I created a new Instance with the adapted ADF-file
5. I Restored the two instance DB's in SQL 2005 to get the old data
6. Upgrade of the NS-Instance
7. Update of the Instance DB's with the new ADF file in NS-Console
8. Once again update in SQL Managament Studio (without it it would't work!)
9. Registeration of the Instance
10. Enabling Instance
11. Start Instance
Can anybody help ?

Hi Semilogic -

Here's a link to an article I wrote for Simple-Talk a while back that discusses upgrading an instance to 2005 from 2000. Maybe it'll help prove your methodology.

As for the 100% CPU utilization - it may be trying to catch up. When an instance goes offline for a while and is then started, it immediately attempts to try to catch up by processing all of those quantums that it missed while stopped.

HTH...

Joe

|||

Well, duh!!!

Here's the link:

http://simple-talk.com/sql/sql-server-2005/building-effective-sql-server-notification-applications-part-i/

|||It works now!!

The transaction log file was full !!

Thankssql

Performance of SQL Server Stored Proc slows down 300% over a few weeks

Hi
I have a problem on my solution where a stored procedure that normally takes
about 200ms to execute will, over a period of about 1 to 2 weeks, gradually
slow down to over a second. Eventially, it'll even slow down to 3 seconds to
execute.
I have tried defragmenting indexes associated with tables in the query, I've
even dropped and recreated the indexes. This has a small effect but not
dramatic. As an attempt to halt the degradation in performance, I did create
a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis but
it has not stopped the execution time of the sp from degrading.
I have found that restarting the SQL Server will always bring the execution
time back down to around 200ms, but it is only a temporary solution. In a
week or two, the performance will have dropped it to 1 second again, as
described above.
I'm a little bit stumped. Has anyone else seen this or know how to stop it?
Thanks
AdrianWhat Service Pack? Any other apps running on that box? Memory settings?
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally
> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
> gradually slow down to over a second. Eventially, it'll even slow down to
> 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query,
> I've even dropped and recreated the indexes. This has a small effect but
> not dramatic. As an attempt to halt the degradation in performance, I did
> create a scheduled job to execute the DBCC INDEXDEFRAG command on a daily
> basis but it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the
> execution time back down to around 200ms, but it is only a temporary
> solution. In a week or two, the performance will have dropped it to 1
> second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop
> it?
> Thanks
> Adrian
>|||SQL 2000 SP1. The machine is running quite a few other apps as well but the
machine has 1G on it. The machine doesn't seem to be starving for memory.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
> What Service Pack? Any other apps running on that box? Memory settings?
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>|||You might want to look at installing SP 3a. There are a lot of reasons to
update, including security and some fixes that address issues such as memory
leaks.
How much memory is SQL server using? How do your memory settings look in
SQL? Are you using a fixed memory setting or allowing SQL to dynamically
allocate memory? Even though SQL doesn't appear to be starving for memory
right now, you might want to monitor memory usage of SQL and your other
apps; particularly during the slow-downs.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
> SQL 2000 SP1. The machine is running quite a few other apps as well but
> the machine has 1G on it. The machine doesn't seem to be starving for
> memory.
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>|||In addition to the other posts, you might want to check the execution plans
between the different
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Adrian Dams" <adriandams@.yahoo.com> wrote in message news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx
.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally tak
es about 200ms to
> execute will, over a period of about 1 to 2 weeks, gradually slow down to
over a second.
> Eventially, it'll even slow down to 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query, I'
ve even dropped and
> recreated the indexes. This has a small effect but not dramatic. As an att
empt to halt the
> degradation in performance, I did create a scheduled job to execute the DB
CC INDEXDEFRAG command
> on a daily basis but it has not stopped the execution time of the sp from
degrading.
> I have found that restarting the SQL Server will always bring the executio
n time back down to
> around 200ms, but it is only a temporary solution. In a week or two, the p
erformance will have
> dropped it to 1 second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop it
?
> Thanks
> Adrian
>|||Thanks for the response.
Originally the sql server was set to dynamically allocate memory. The
machine had 500M and the sql server had used up about 220M.
I changed the memory settings so that the SQL Server would only use 150M
fixed. The odd thing is that the SQL Server didn't use 220M as it had
previously done but it did consume 180M and stabilise there - more than
150M!!
I will certainly apply SP3a and see how that helps
Thanks again
Adrian
"Michael C#" <xyz@.yomomma.com> wrote in message
news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
> You might want to look at installing SP 3a. There are a lot of reasons to
> update, including security and some fixes that address issues such as
> memory leaks.
> How much memory is SQL server using? How do your memory settings look in
> SQL? Are you using a fixed memory setting or allowing SQL to dynamically
> allocate memory? Even though SQL doesn't appear to be starving for memory
> right now, you might want to monitor memory usage of SQL and your other
> apps; particularly during the slow-downs.
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
>|||I like to stop and re-start the SQL Server service to ensure new memory
settings kick in. I don't know exactly what your configuration is or what
apps you have on that box, but I would definitely take a look at whether or
not you really need those other apps running on the same box; and if not,
move them somewhere else. SQL is resource-hungry, and the less other stuff
you have on that box the better off you'll be. Speaking of which, make sure
you have plenty of hard drive space on that box.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eRIQbJKMFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> Originally the sql server was set to dynamically allocate memory. The
> machine had 500M and the sql server had used up about 220M.
> I changed the memory settings so that the SQL Server would only use 150M
> fixed. The odd thing is that the SQL Server didn't use 220M as it had
> previously done but it did consume 180M and stabilise there - more than
> 150M!!
> I will certainly apply SP3a and see how that helps
> Thanks again
> Adrian
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
>|||if it is the case that your stored proc performs to expectations after
a reboot, then you probably want to add a "with recompile" at the top
of the stored proc, so that it will generate a new execution plan each
time it fires. the execution plan is probably falling out of scope over
time and causing table scans. when you restart sql server, the proc
cache is cleared, so a new one is made, hence the better performance.
if you cannot alter the stored proc, then you have two alternatives:
first, you can schedule a job that runs sp_recompile "proc_name"...do
this once a day and you should be fine.
second, you can pass the "with recompile" as a parameter to your stored
proc...this is usually done when the parameter value is atypical or
the data has changed significantly.
hth,
hans|||Thanks for all your suggestions. I will certainly try them and report back
on the progress
Adrian
"Hans Nelsen" <hnelsen@.owh.com> wrote in message
news:1111705078.992925.138370@.z14g2000cwz.googlegroups.com...
> if it is the case that your stored proc performs to expectations after
> a reboot, then you probably want to add a "with recompile" at the top
> of the stored proc, so that it will generate a new execution plan each
> time it fires. the execution plan is probably falling out of scope over
> time and causing table scans. when you restart sql server, the proc
> cache is cleared, so a new one is made, hence the better performance.
> if you cannot alter the stored proc, then you have two alternatives:
> first, you can schedule a job that runs sp_recompile "proc_name"...do
> this once a day and you should be fine.
> second, you can pass the "with recompile" as a parameter to your stored
> proc...this is usually done when the parameter value is atypical or
> the data has changed significantly.
> hth,
> hans
>

Performance of SQL Server Stored Proc slows down 300% over a few weeks

Hi
I have a problem on my solution where a stored procedure that normally takes
about 200ms to execute will, over a period of about 1 to 2 weeks, gradually
slow down to over a second. Eventially, it'll even slow down to 3 seconds to
execute.
I have tried defragmenting indexes associated with tables in the query, I've
even dropped and recreated the indexes. This has a small effect but not
dramatic. As an attempt to halt the degradation in performance, I did create
a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis but
it has not stopped the execution time of the sp from degrading.
I have found that restarting the SQL Server will always bring the execution
time back down to around 200ms, but it is only a temporary solution. In a
week or two, the performance will have dropped it to 1 second again, as
described above.
I'm a little bit stumped. Has anyone else seen this or know how to stop it?
Thanks
Adrian
What Service Pack? Any other apps running on that box? Memory settings?
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally
> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
> gradually slow down to over a second. Eventially, it'll even slow down to
> 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query,
> I've even dropped and recreated the indexes. This has a small effect but
> not dramatic. As an attempt to halt the degradation in performance, I did
> create a scheduled job to execute the DBCC INDEXDEFRAG command on a daily
> basis but it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the
> execution time back down to around 200ms, but it is only a temporary
> solution. In a week or two, the performance will have dropped it to 1
> second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop
> it?
> Thanks
> Adrian
>
|||SQL 2000 SP1. The machine is running quite a few other apps as well but the
machine has 1G on it. The machine doesn't seem to be starving for memory.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
> What Service Pack? Any other apps running on that box? Memory settings?
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>
|||You might want to look at installing SP 3a. There are a lot of reasons to
update, including security and some fixes that address issues such as memory
leaks.
How much memory is SQL server using? How do your memory settings look in
SQL? Are you using a fixed memory setting or allowing SQL to dynamically
allocate memory? Even though SQL doesn't appear to be starving for memory
right now, you might want to monitor memory usage of SQL and your other
apps; particularly during the slow-downs.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
> SQL 2000 SP1. The machine is running quite a few other apps as well but
> the machine has 1G on it. The machine doesn't seem to be starving for
> memory.
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>
|||In addition to the other posts, you might want to check the execution plans between the different
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Adrian Dams" <adriandams@.yahoo.com> wrote in message news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally takes about 200ms to
> execute will, over a period of about 1 to 2 weeks, gradually slow down to over a second.
> Eventially, it'll even slow down to 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query, I've even dropped and
> recreated the indexes. This has a small effect but not dramatic. As an attempt to halt the
> degradation in performance, I did create a scheduled job to execute the DBCC INDEXDEFRAG command
> on a daily basis but it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the execution time back down to
> around 200ms, but it is only a temporary solution. In a week or two, the performance will have
> dropped it to 1 second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop it?
> Thanks
> Adrian
>
|||Thanks for the response.
Originally the sql server was set to dynamically allocate memory. The
machine had 500M and the sql server had used up about 220M.
I changed the memory settings so that the SQL Server would only use 150M
fixed. The odd thing is that the SQL Server didn't use 220M as it had
previously done but it did consume 180M and stabilise there - more than
150M!!
I will certainly apply SP3a and see how that helps
Thanks again
Adrian
"Michael C#" <xyz@.yomomma.com> wrote in message
news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
> You might want to look at installing SP 3a. There are a lot of reasons to
> update, including security and some fixes that address issues such as
> memory leaks.
> How much memory is SQL server using? How do your memory settings look in
> SQL? Are you using a fixed memory setting or allowing SQL to dynamically
> allocate memory? Even though SQL doesn't appear to be starving for memory
> right now, you might want to monitor memory usage of SQL and your other
> apps; particularly during the slow-downs.
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
>
|||I like to stop and re-start the SQL Server service to ensure new memory
settings kick in. I don't know exactly what your configuration is or what
apps you have on that box, but I would definitely take a look at whether or
not you really need those other apps running on the same box; and if not,
move them somewhere else. SQL is resource-hungry, and the less other stuff
you have on that box the better off you'll be. Speaking of which, make sure
you have plenty of hard drive space on that box.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eRIQbJKMFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> Originally the sql server was set to dynamically allocate memory. The
> machine had 500M and the sql server had used up about 220M.
> I changed the memory settings so that the SQL Server would only use 150M
> fixed. The odd thing is that the SQL Server didn't use 220M as it had
> previously done but it did consume 180M and stabilise there - more than
> 150M!!
> I will certainly apply SP3a and see how that helps
> Thanks again
> Adrian
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
>
|||if it is the case that your stored proc performs to expectations after
a reboot, then you probably want to add a "with recompile" at the top
of the stored proc, so that it will generate a new execution plan each
time it fires. the execution plan is probably falling out of scope over
time and causing table scans. when you restart sql server, the proc
cache is cleared, so a new one is made, hence the better performance.
if you cannot alter the stored proc, then you have two alternatives:
first, you can schedule a job that runs sp_recompile "proc_name"...do
this once a day and you should be fine.
second, you can pass the "with recompile" as a parameter to your stored
proc...this is usually done when the parameter value is atypical or
the data has changed significantly.
hth,
hans
|||Thanks for all your suggestions. I will certainly try them and report back
on the progress
Adrian
"Hans Nelsen" <hnelsen@.owh.com> wrote in message
news:1111705078.992925.138370@.z14g2000cwz.googlegr oups.com...
> if it is the case that your stored proc performs to expectations after
> a reboot, then you probably want to add a "with recompile" at the top
> of the stored proc, so that it will generate a new execution plan each
> time it fires. the execution plan is probably falling out of scope over
> time and causing table scans. when you restart sql server, the proc
> cache is cleared, so a new one is made, hence the better performance.
> if you cannot alter the stored proc, then you have two alternatives:
> first, you can schedule a job that runs sp_recompile "proc_name"...do
> this once a day and you should be fine.
> second, you can pass the "with recompile" as a parameter to your stored
> proc...this is usually done when the parameter value is atypical or
> the data has changed significantly.
> hth,
> hans
>

Performance of SQL Server Stored Proc slows down 300% over a few weeks

Hi
I have a problem on my solution where a stored procedure that normally takes
about 200ms to execute will, over a period of about 1 to 2 weeks, gradually
slow down to over a second. Eventially, it'll even slow down to 3 seconds to
execute.
I have tried defragmenting indexes associated with tables in the query, I've
even dropped and recreated the indexes. This has a small effect but not
dramatic. As an attempt to halt the degradation in performance, I did create
a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis but
it has not stopped the execution time of the sp from degrading.
I have found that restarting the SQL Server will always bring the execution
time back down to around 200ms, but it is only a temporary solution. In a
week or two, the performance will have dropped it to 1 second again, as
described above.
I'm a little bit stumped. Has anyone else seen this or know how to stop it?
Thanks
AdrianWhat about updating statistics, or recompiling the sp, have you tried?
AMB
"Adrian Dams" wrote:
> Hi
> I have a problem on my solution where a stored procedure that normally takes
> about 200ms to execute will, over a period of about 1 to 2 weeks, gradually
> slow down to over a second. Eventially, it'll even slow down to 3 seconds to
> execute.
> I have tried defragmenting indexes associated with tables in the query, I've
> even dropped and recreated the indexes. This has a small effect but not
> dramatic. As an attempt to halt the degradation in performance, I did create
> a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis but
> it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the execution
> time back down to around 200ms, but it is only a temporary solution. In a
> week or two, the performance will have dropped it to 1 second again, as
> described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop it?
> Thanks
> Adrian
>
>|||What Service Pack? Any other apps running on that box? Memory settings?
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally
> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
> gradually slow down to over a second. Eventially, it'll even slow down to
> 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query,
> I've even dropped and recreated the indexes. This has a small effect but
> not dramatic. As an attempt to halt the degradation in performance, I did
> create a scheduled job to execute the DBCC INDEXDEFRAG command on a daily
> basis but it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the
> execution time back down to around 200ms, but it is only a temporary
> solution. In a week or two, the performance will have dropped it to 1
> second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop
> it?
> Thanks
> Adrian
>|||No, I haven't tried that. Thanks for the suggestion. I'll get back to you
with the result
Adrian
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:36EEAAEC-4B19-4325-861A-1130FF81D4F6@.microsoft.com...
> What about updating statistics, or recompiling the sp, have you tried?
>
> AMB
> "Adrian Dams" wrote:
>> Hi
>> I have a problem on my solution where a stored procedure that normally
>> takes
>> about 200ms to execute will, over a period of about 1 to 2 weeks,
>> gradually
>> slow down to over a second. Eventially, it'll even slow down to 3 seconds
>> to
>> execute.
>> I have tried defragmenting indexes associated with tables in the query,
>> I've
>> even dropped and recreated the indexes. This has a small effect but not
>> dramatic. As an attempt to halt the degradation in performance, I did
>> create
>> a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis
>> but
>> it has not stopped the execution time of the sp from degrading.
>> I have found that restarting the SQL Server will always bring the
>> execution
>> time back down to around 200ms, but it is only a temporary solution. In a
>> week or two, the performance will have dropped it to 1 second again, as
>> described above.
>> I'm a little bit stumped. Has anyone else seen this or know how to stop
>> it?
>> Thanks
>> Adrian
>>|||SQL 2000 SP1. The machine is running quite a few other apps as well but the
machine has 1G on it. The machine doesn't seem to be starving for memory.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
> What Service Pack? Any other apps running on that box? Memory settings?
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I have a problem on my solution where a stored procedure that normally
>> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
>> gradually slow down to over a second. Eventially, it'll even slow down to
>> 3 seconds to execute.
>> I have tried defragmenting indexes associated with tables in the query,
>> I've even dropped and recreated the indexes. This has a small effect but
>> not dramatic. As an attempt to halt the degradation in performance, I did
>> create a scheduled job to execute the DBCC INDEXDEFRAG command on a daily
>> basis but it has not stopped the execution time of the sp from degrading.
>> I have found that restarting the SQL Server will always bring the
>> execution time back down to around 200ms, but it is only a temporary
>> solution. In a week or two, the performance will have dropped it to 1
>> second again, as described above.
>> I'm a little bit stumped. Has anyone else seen this or know how to stop
>> it?
>> Thanks
>> Adrian
>|||Is it possible to rebuild the stored procedure via script? This problem
exists on a customer site and the slow down in the sp is a big problem for
them. If possible, and if the rebuild works, I could set it up as a
scheduled job. If its not possible, it won't unfortunately work for me as a
solution
Adrian
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:36EEAAEC-4B19-4325-861A-1130FF81D4F6@.microsoft.com...
> What about updating statistics, or recompiling the sp, have you tried?
>
> AMB
> "Adrian Dams" wrote:
>> Hi
>> I have a problem on my solution where a stored procedure that normally
>> takes
>> about 200ms to execute will, over a period of about 1 to 2 weeks,
>> gradually
>> slow down to over a second. Eventially, it'll even slow down to 3 seconds
>> to
>> execute.
>> I have tried defragmenting indexes associated with tables in the query,
>> I've
>> even dropped and recreated the indexes. This has a small effect but not
>> dramatic. As an attempt to halt the degradation in performance, I did
>> create
>> a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis
>> but
>> it has not stopped the execution time of the sp from degrading.
>> I have found that restarting the SQL Server will always bring the
>> execution
>> time back down to around 200ms, but it is only a temporary solution. In a
>> week or two, the performance will have dropped it to 1 second again, as
>> described above.
>> I'm a little bit stumped. Has anyone else seen this or know how to stop
>> it?
>> Thanks
>> Adrian
>>|||You might want to look at installing SP 3a. There are a lot of reasons to
update, including security and some fixes that address issues such as memory
leaks.
How much memory is SQL server using? How do your memory settings look in
SQL? Are you using a fixed memory setting or allowing SQL to dynamically
allocate memory? Even though SQL doesn't appear to be starving for memory
right now, you might want to monitor memory usage of SQL and your other
apps; particularly during the slow-downs.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
> SQL 2000 SP1. The machine is running quite a few other apps as well but
> the machine has 1G on it. The machine doesn't seem to be starving for
> memory.
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> What Service Pack? Any other apps running on that box? Memory settings?
>> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
>> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I have a problem on my solution where a stored procedure that normally
>> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
>> gradually slow down to over a second. Eventially, it'll even slow down
>> to 3 seconds to execute.
>> I have tried defragmenting indexes associated with tables in the query,
>> I've even dropped and recreated the indexes. This has a small effect but
>> not dramatic. As an attempt to halt the degradation in performance, I
>> did create a scheduled job to execute the DBCC INDEXDEFRAG command on a
>> daily basis but it has not stopped the execution time of the sp from
>> degrading.
>> I have found that restarting the SQL Server will always bring the
>> execution time back down to around 200ms, but it is only a temporary
>> solution. In a week or two, the performance will have dropped it to 1
>> second again, as described above.
>> I'm a little bit stumped. Has anyone else seen this or know how to stop
>> it?
>> Thanks
>> Adrian
>>
>|||In addition to the other posts, you might want to check the execution plans between the different
execution times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Adrian Dams" <adriandams@.yahoo.com> wrote in message news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally takes about 200ms to
> execute will, over a period of about 1 to 2 weeks, gradually slow down to over a second.
> Eventially, it'll even slow down to 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query, I've even dropped and
> recreated the indexes. This has a small effect but not dramatic. As an attempt to halt the
> degradation in performance, I did create a scheduled job to execute the DBCC INDEXDEFRAG command
> on a daily basis but it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the execution time back down to
> around 200ms, but it is only a temporary solution. In a week or two, the performance will have
> dropped it to 1 second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop it?
> Thanks
> Adrian
>|||Thanks for the response.
Originally the sql server was set to dynamically allocate memory. The
machine had 500M and the sql server had used up about 220M.
I changed the memory settings so that the SQL Server would only use 150M
fixed. The odd thing is that the SQL Server didn't use 220M as it had
previously done but it did consume 180M and stabilise there - more than
150M!!
I will certainly apply SP3a and see how that helps
Thanks again
Adrian
"Michael C#" <xyz@.yomomma.com> wrote in message
news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
> You might want to look at installing SP 3a. There are a lot of reasons to
> update, including security and some fixes that address issues such as
> memory leaks.
> How much memory is SQL server using? How do your memory settings look in
> SQL? Are you using a fixed memory setting or allowing SQL to dynamically
> allocate memory? Even though SQL doesn't appear to be starving for memory
> right now, you might want to monitor memory usage of SQL and your other
> apps; particularly during the slow-downs.
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
>> SQL 2000 SP1. The machine is running quite a few other apps as well but
>> the machine has 1G on it. The machine doesn't seem to be starving for
>> memory.
>>
>> "Michael C#" <xyz@.yomomma.com> wrote in message
>> news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> What Service Pack? Any other apps running on that box? Memory
>> settings?
>> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
>> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I have a problem on my solution where a stored procedure that normally
>> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
>> gradually slow down to over a second. Eventially, it'll even slow down
>> to 3 seconds to execute.
>> I have tried defragmenting indexes associated with tables in the query,
>> I've even dropped and recreated the indexes. This has a small effect
>> but not dramatic. As an attempt to halt the degradation in performance,
>> I did create a scheduled job to execute the DBCC INDEXDEFRAG command on
>> a daily basis but it has not stopped the execution time of the sp from
>> degrading.
>> I have found that restarting the SQL Server will always bring the
>> execution time back down to around 200ms, but it is only a temporary
>> solution. In a week or two, the performance will have dropped it to 1
>> second again, as described above.
>> I'm a little bit stumped. Has anyone else seen this or know how to stop
>> it?
>> Thanks
>> Adrian
>>
>>
>|||I like to stop and re-start the SQL Server service to ensure new memory
settings kick in. I don't know exactly what your configuration is or what
apps you have on that box, but I would definitely take a look at whether or
not you really need those other apps running on the same box; and if not,
move them somewhere else. SQL is resource-hungry, and the less other stuff
you have on that box the better off you'll be. Speaking of which, make sure
you have plenty of hard drive space on that box.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eRIQbJKMFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> Originally the sql server was set to dynamically allocate memory. The
> machine had 500M and the sql server had used up about 220M.
> I changed the memory settings so that the SQL Server would only use 150M
> fixed. The odd thing is that the SQL Server didn't use 220M as it had
> previously done but it did consume 180M and stabilise there - more than
> 150M!!
> I will certainly apply SP3a and see how that helps
> Thanks again
> Adrian
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
>> You might want to look at installing SP 3a. There are a lot of reasons
>> to update, including security and some fixes that address issues such as
>> memory leaks.
>> How much memory is SQL server using? How do your memory settings look in
>> SQL? Are you using a fixed memory setting or allowing SQL to dynamically
>> allocate memory? Even though SQL doesn't appear to be starving for
>> memory right now, you might want to monitor memory usage of SQL and your
>> other apps; particularly during the slow-downs.
>> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
>> news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
>> SQL 2000 SP1. The machine is running quite a few other apps as well but
>> the machine has 1G on it. The machine doesn't seem to be starving for
>> memory.
>>
>> "Michael C#" <xyz@.yomomma.com> wrote in message
>> news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> What Service Pack? Any other apps running on that box? Memory
>> settings?
>> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
>> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I have a problem on my solution where a stored procedure that normally
>> takes about 200ms to execute will, over a period of about 1 to 2
>> weeks, gradually slow down to over a second. Eventially, it'll even
>> slow down to 3 seconds to execute.
>> I have tried defragmenting indexes associated with tables in the
>> query, I've even dropped and recreated the indexes. This has a small
>> effect but not dramatic. As an attempt to halt the degradation in
>> performance, I did create a scheduled job to execute the DBCC
>> INDEXDEFRAG command on a daily basis but it has not stopped the
>> execution time of the sp from degrading.
>> I have found that restarting the SQL Server will always bring the
>> execution time back down to around 200ms, but it is only a temporary
>> solution. In a week or two, the performance will have dropped it to 1
>> second again, as described above.
>> I'm a little bit stumped. Has anyone else seen this or know how to
>> stop it?
>> Thanks
>> Adrian
>>
>>
>>
>|||if it is the case that your stored proc performs to expectations after
a reboot, then you probably want to add a "with recompile" at the top
of the stored proc, so that it will generate a new execution plan each
time it fires. the execution plan is probably falling out of scope over
time and causing table scans. when you restart sql server, the proc
cache is cleared, so a new one is made, hence the better performance.
if you cannot alter the stored proc, then you have two alternatives:
first, you can schedule a job that runs sp_recompile "proc_name"...do
this once a day and you should be fine.
second, you can pass the "with recompile" as a parameter to your stored
proc...this is usually done when the parameter value is atypical or
the data has changed significantly.
hth,
hans|||Thanks for all your suggestions. I will certainly try them and report back
on the progress
Adrian
"Hans Nelsen" <hnelsen@.owh.com> wrote in message
news:1111705078.992925.138370@.z14g2000cwz.googlegroups.com...
> if it is the case that your stored proc performs to expectations after
> a reboot, then you probably want to add a "with recompile" at the top
> of the stored proc, so that it will generate a new execution plan each
> time it fires. the execution plan is probably falling out of scope over
> time and causing table scans. when you restart sql server, the proc
> cache is cleared, so a new one is made, hence the better performance.
> if you cannot alter the stored proc, then you have two alternatives:
> first, you can schedule a job that runs sp_recompile "proc_name"...do
> this once a day and you should be fine.
> second, you can pass the "with recompile" as a parameter to your stored
> proc...this is usually done when the parameter value is atypical or
> the data has changed significantly.
> hth,
> hans
>