SQL Server Performance Forum – Threads Archive

very slow stored proc

I have a complex stored proc that was taking 35-40 secs to execute. It is now taking 3min 30+secs to execute. This stored proc still runs in 35 secs on my test sql server box. Nothing has changed on the production configuration. The indexes on the production server are updated every Sunday. Any clues???

what are the system details for each machine: #, MHz, type of CPU, mem etc
are there differences in the execution plan?
what is the actual query?

What means indexes on the production server are updated?.
DBCC RBINDEX ?.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Production machine: 8 550MHz Intel PIII Xeon
2 G RAM
WIN NT 4.0 SP6 Test machine: 4 2.8 GHz Intel P4
2 G RAM
Server 2000 – SP3 Executaion plans seem to be exactly the same I ran the DBCC RBREPAIR during the day yesterday and I have a Database Maintenence plan that runs on Sunday that Reorganizes the Indexes based on 10%. Last night I ran the maintenance plan again. I also added a plan to update the stats based on 70% of database. In addition I used the database maintenance to check the database integrity with index and repair. This brought execution down to 1-2 minutes – which is still not good for the users.

Try to update statistics on the tables/indexes involved on production box and execute the sp. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, also run DBREINDEX on critical tables, that include update statistics for that tables.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

does the execution plan show parallel operations?
if so, try various values for OPTION (MAXDOP x)
from 1,2,4, & 0 (use all procs) i find it rather suspicious that what once ran in 35-40sec on a 8xPIII-550 also runs in ~35sec on a 4xXeon MP2.8
( do you mean there are four physical Xeon MP 2.8 processors in your test system or are there actually two Xeon 2.8 processors with HT enabled?)

I have tried the sp_updatestats on the databases and DBCC DBREINDEX I have also run my maintenance plans overnight which do the same thing Neither of these have helped the problem I am checking the execution plan again for parallel operations to see if we can configure Thanks for you input so far….any other hints would be greatly appreciated!

Beside Joe said (I think this is the point), look for hypothetical index. If there delete it.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

There were no hypothetical indexes on the database…darn! I ran a trace on each machine while running this stored proc. Below is one area where I saw differences between the production and development machines. Since this Fetch happens anywhere from 500 to 10,000 times depending on the parameters and data – I think this is where my problem is. FETCH NEXT FROM tempResultsWithVendRamp_Cursor INTO @Railroad1, @Railroad2, @Railroad3, @Railroad4, @Railroad5, @Railroad6, @drayRailroad1, @drayRailroad2, @drayRailroad3 SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted150170 Duration of 15 and Reads of 17 on the produstion machine. On the deveolpment box the duration is always 0 and reads of 13. Does this help? Any clues? (Again this query worked fine last week)

What type of cursor is this server or client based?
Try to submit the trace to index tuning wizard for any recommendation on index that may help to fine tune execution. Besides try to use temp tables instead of cursor, as they’re prone to use lot of resources on SQL. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Last night I rebooted the production server and the query is now running in 12sec to 60 secs. I will wait and see if it gets worse as the load increases before claiming a small victory….though 60 secs is still too long. Any opinions on why a reboot would help? 2 cursors are created in the stored proc to scroll through and update the 2 temp tables used in the stored proc….so server side. I will run the Index Tuning wizard to see if I can get better results. Thanks for your help!

A reboot helps to drain few values in memory which helps to re-initialise the process to gain the performance. This may not help at all the times, better to workout from code side or add-up more resources HTH. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>