September 27, 2016 at 8:21 am
Can a query run fine on one server with read/write db with out filling tempdb and the same query runs in other server where its the same copy of DB in read only mode fills up tempdb. Is that possible?
September 27, 2016 at 8:24 am
Absolutely possible, but don't focus on the read/write or read-only status of the database: look for differences in the query plans and sorting/hashing operations spilling to tempdb.
-- Gianluca Sartori
September 27, 2016 at 8:28 am
I looked at estimated execution plan and created the missing index it suggested. But still no luck. Can you tell how can i differentiate query plans.
September 27, 2016 at 8:32 am
Compare the two execution plans. If the plans are simple, you can do that visually/manually.
If the plans are complex, you can look for subtle changes using SSMS: https://blogs.msdn.microsoft.com/sql_server_team/comparison-tool-released-with-latest-ssms/
-- Gianluca Sartori
September 27, 2016 at 10:13 am
If you're getting wildly different plans between servers, there has to be differences. The differences can be in the statistics on the data in the database. The differences can be in the data. The differences can be in the structures. The differences can be in the database settings. The differences can be in the server settings.
Follow Gianluca's advice to identify differences in the server settings and database settings. After that, determining why there are differences just looking at or comparing plans requires stepping out of the plans and looking at the rest of the structures, data & statistics. However, the plans should point you in the right direction to start the comparison.
"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
September 27, 2016 at 12:56 pm
The DB is exact copy of source(Query runs without filling tempdb) that gets copied daily morning to destination(Query fills tempdb). So stats, DB settings are same. Coming to server settings what are the main settings needs to be compared.
September 27, 2016 at 1:55 pm
psred (9/27/2016)
The DB is exact copy of source(Query runs without filling tempdb) that gets copied daily morning to destination(Query fills tempdb). So stats, DB settings are same. Coming to server settings what are the main settings needs to be compared.
Max Degree of Parallelism, Cost Threshold for Parallelism and all the ANSI connection defaults have to be checked.
"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
September 27, 2016 at 1:57 pm
psred (9/27/2016)
The DB is exact copy of source(Query runs without filling tempdb) that gets copied daily morning to destination(Query fills tempdb). So stats, DB settings are same. Coming to server settings what are the main settings needs to be compared.
Hit reply before I was done.
Are you sure these are exact copies, like with a backup & restore, not replication or something similar? There could be all sorts of changes and differences slip in if we're not talking about a straight backup & restore.
"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
September 27, 2016 at 2:36 pm
Yes the DB is exact copy daily morning from source to destination. I see MAXDOP different one is 4 other is 8.
September 27, 2016 at 8:52 pm
There has to be something else causing the issue. If you're seeing different execution plans, it's because the mechanisms used to generate those plans have different inputs.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply