| cbrichards via SQLMonster.com 2006-11-16, 7:12 pm |
| I have a complex query statement within a stored procedure. In its original
state, on production, it takes about 10 minutes to run.
If I restore this same database to our dev environment and run the procedure,
it takes less then 5 seconds. Of course, the production and DEV explain plans
are quite different.
On production I have ran DBCC DBReindex and update statistics [tableName]
with fullscan for every table on the database. Still, it takes 10 minutes,
but less than 5 seconds in DEV.
Because I could not replicate and thus troubleshoot the problem on DEV, I
restored the database under a different name on the same production server.
Running the procedure took about 10 minutes for the second production copy.
I then rewrote the procedure on the second production copy, taking the
procedure arguments and assigning them to local variables. The procedure
completed in about 10 seconds.
What I construe from this is that on production there was paramater sniffing
going on.
What I still do not understand, is that the explain plans are still quite
different between DEV and the second production copy version. There is 4Gb
memory and 2 processors on DEV, and 8GB (with 6 GB AWE memory), and 4
processors in production.
We are running SQL Server 2000, SP4, on Windows 2003.
Are there configuration settings that may be different?
--
Message posted via http://www.webservertalk.com
|