Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2002-05-08 : 15:51:08
|
| I have a production and development environment. When I execute a stored procedure against the dev environment it runs in a matter of minutes. When run against production it takes upwards of two hours. When displaying the execution plan on dev and prod they are different. How do I determine what is the cause of the delay and would the execution plan be more similar? I know that table size and index statistics will cause the optimizer to choose different optimazation strategies but the results I got are radically different. Any ideas or resources where I could look would be appreciated.Thanks. |
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-05-08 : 16:31:55
|
| Does the sproc have a cursor in it? |
 |
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-05-08 : 16:38:50
|
| No. We are not allowed to even say the "C" word here. Just plain SQL. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-08 : 16:53:31
|
| I think you've covered everything, meaning you've mentioned everything that would cause the two plans to be so different. Have you tried running UPDATE STATISTICS WITH FULLSCAN on both machines? After you do that, run DBCC DROPCLEANBUFFERS to clear the data cache, then run the queries on both machines and check the plans. If there is a vast difference in the data stored on the two machines, you may have to use index hints to force the optimizer to choose the one you want.Another thing to consider is the number of users on the production box who may be holding locks on the tables. Run sp_who, sp_who2, and sp_lock to see if anyone is blocking something.EDIT: Oooops, missed this in your original post about stored procedures. In addition to DBCC DROPCLEANBUFFERS, you should also run DBCC FREEPROCCACHE to clear the procedure cache. You might also what to do this:DECLARE @dbid intSET @dbid=DB_ID('databasename')DBCC FLUSHPROCINDB(@dbid)That will force a recompile of all of the stored procedures in that database. It probably isn't necessary, but it pretty much guarantees that an old execution plan isn't used.Edited by - robvolk on 05/08/2002 17:06:09 |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-05-08 : 17:29:48
|
| Sometimes SQL Server has issues with parallel plan execution. Do your development and production machines have the exact same number of processors? Try setting the "max degree of parallelism" option to 1 and see if things improve.Edited by - izaltsman on 05/08/2002 17:30:30 |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-08 : 17:32:28
|
| Check indexes and relationships on the tables involved. Is the production environment truly identical to the deveopment one? |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-05-08 : 17:37:59
|
| The environments are the same because we use a schema synching tool. We did the update stats and it seemed to work as the time of execution was similar to that of development. I suspected as much. I was told that the recompute stats option was set to on for all indexes. Evidently not. Thanks for the suggestions. Always appreciated. |
|
|
|
|
|
|
Prod vs Dev and Execution Plan