December 10, 2008 at 12:02 pm
I have a production server that replicates data to another server. The production server has 28gb of RAM and the subscriber has 12gb and the rest of the hardware is pretty equal. I have a query that runs in prod and takes approx 15 minutes to complete and this same query runs in about 4 seconds at the subscriber. There is no load on the subscriber and very little on the prod server. When I look at the output from set statistics IO on I see that one of the tables in production has a scan count of 225606 and logical reads of 375091134 while the subscriber shows scan count of 6 and 9973 logical reads. How can this be if both tables are identical as far as data and index structure?
December 10, 2008 at 12:13 pm
The optimizer is usually pretty good. Where it tends to have most failures is in getting bad information. Make sure the statistics are updated on your production table - I would update statistics with FULLSCAN just to be sure.
The next place to look is fragmented indexes. A replicated table tends to get data inserted and updated differently (replication does updates row-by-row by default) and you may have done the most recent snapshot after a great deal of data was already in the production table.
If those both fail, it is possible that the additional resources on the production server have made the optimizer make a bad choice. Memory tends to not do this, but it could. It is usually additional processors causing parallelism that can make a query perform poorly (usually due to a poor query). Memory could be causing the optimizer to do something like a HASH join on the production server and a LOOP join on the replicated server and the loop may in fact be more efficient. These issues can be easily seen if you display the execution plans.
Update the statistics, make sure you don't have fragmented indexes, and if you still have issues, post the queries and the execution plans.
December 10, 2008 at 2:16 pm
You should also check the silly stuff, default connection settings, service pack and hot fix levels, that kind of thing. There's usually something if it's not what Michael listed above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2008 at 11:45 am
thanks for both replies. I have checked configuration settings and they are the same. I have updated statistics, checked that all tables have the same indexes, and looked at any fragmentation info and everything seems in order. The execution plans are very different. In production I'm doing a Table Spool where the subscriber server does not do this. I think this is where we are dying in production. I don't know how to make it not do the Table spool however. Any advice on this?
thanks
December 13, 2008 at 9:30 am
Can you post the plans in .sqlplan (xml) format?
December 13, 2008 at 10:33 am
Unfortunately msdn and BOL doesnt have much say on this than
The Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query). If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
SET ROWCOUNT or TOP With Partitioned View May Cause Poor Performance leading to table spool http://support.microsoft.com/kb/255633
You can also force the query to use a plan generated on your subsriber and see the results...
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Forcing_Query_Plans.doc
December 14, 2008 at 8:08 am
To really answer the question, you've got to post the execution plans.
When updated statistics, did you use the FULL SCAN option?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2008 at 8:52 am
Grant Fritchey (12/14/2008)
To really answer the question, you've got to post the execution plans.
And the query, the table schemas and index definitions please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2008 at 7:45 am
Are you 100% certain that all objects involved in the query are 100% identical?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 15, 2008 at 11:52 am
I'm working on getting this information. Thanks
December 16, 2008 at 4:07 am
Just a thought but
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
Worth a read as it sounds like you're encountering the same symptoms we had.
It explains what the effects of parameter sniffing can be.
That said our result set varied hugely based on the parameters passed in so stopping the sniffing was a good thing in our case but may make your's worse.
December 16, 2008 at 5:07 am
Could be worth a try to run the query using the OPTION (RECOMPILE) directive on production to see if it changed the plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply