May 27, 2011 at 4:15 pm
We have a 100 GB database on SQL 2005 Standard Edition (64bit). One of the reports takes less than 30 second to run on the test server as well as on the replicated copy of the production database. However it takes over 10 minutes to run on production. Here are the Statistics IO results:
Replicated Database
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sales'. Scan count 1159, logical reads 14436, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Drivers'. Scan count 684, logical reads 3622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 682, logical reads 13627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DailyPolicyLog'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesInsurance'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Vehicles'. Scan count 288, logical reads 17583, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MainDistricts'. Scan count 0, logical reads 572, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'offices'. Scan count 1, logical reads 1943, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dealers'. Scan count 2, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Production Database
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sales'. Scan count 3, logical reads 93969, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Drivers'. Scan count 570, logical reads 165937, physical reads 173, read-ahead reads 204, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 0, logical reads 6056, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dealers'. Scan count 1256, logical reads 2512, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MainDistricts'. Scan count 0, logical reads 1144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'offices'. Scan count 0, logical reads 4470, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 15798441, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Vehicles'. Scan count 686, logical reads 17954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The logical reads are too high on the production environment, the execution plans are also different on both servers.
May 28, 2011 at 1:33 am
have you done routine maintenance of the database ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 28, 2011 at 2:29 am
As you already noticed there are different execution plans for the same query.
Compare the execution plans and try to find the difference.
Assuming your indexes are up to date, it's likely due to a different data distribution.
Hard to tell without seeing both actual execution plans...
@"Mr. Alphabet Soup":
It starts to get annoying to see replies with the "answer" being a lot shorter than the alphabet soup under your name. Sometimes you're leaving the impression that your reply is more an advertisement of yourself and your blog than intended to help the OP...
May 28, 2011 at 3:21 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
May 28, 2011 at 4:13 am
I'd guess either differences in data or statistics being out of date on one of the servers. Hard to say without seeing the execution plans.
"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
May 28, 2011 at 5:39 am
Are you sure the queries are the same on prod and dev? The reason I ask is that your STATISTICS IO output lists different tables in dev and prod.
May 28, 2011 at 11:10 pm
@Syed: Yes we do perform regular maintenace jobs.
@Gila & Grant: I will try to add the definitions and execution plans as soon as I get a chance
@Nils: Yes the queries are exactly the same as we copied and pasted the same query in Management Studio
May 30, 2011 at 3:39 am
@"Mr. Alphabet Soup":
It starts to get annoying to see replies with the "answer" being a lot shorter than the alphabet soup under your name. Sometimes you're leaving the impression that your reply is more an advertisement of yourself and your blog than intended to help the OP...
Are you a part of this world or earth ,I think No because you don't know about these alphabets meaning,try to solve the problem and dont try to attack personally Mr. Shakky Shark
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 30, 2011 at 3:47 am
Can you post the execution plans for the query on the two different queries.
May 30, 2011 at 5:43 am
Syed Jahanzaib Bin hassan (5/30/2011)
@"Mr. Alphabet Soup":
It starts to get annoying to see replies with the "answer" being a lot shorter than the alphabet soup under your name. Sometimes you're leaving the impression that your reply is more an advertisement of yourself and your blog than intended to help the OP...
Are you a part of this world or earth ,I think No because you don't know about these alphabets meaning,try to solve the problem and dont try to attack personally Mr. Shakky Shark
Hey dude that's now 20 + different posters that made that exact same remark to you. "MAYBE" it's you!
May 30, 2011 at 10:04 am
Syed Jahanzaib Bin hassan (5/30/2011)
@"Mr. Alphabet Soup":
It starts to get annoying to see replies with the "answer" being a lot shorter than the alphabet soup under your name. Sometimes you're leaving the impression that your reply is more an advertisement of yourself and your blog than intended to help the OP...
Are you a part of this world or earth ,I think No because you don't know about these alphabets meaning,try to solve the problem and dont try to attack personally Mr. Shakky Shark
:ermm:
Do you seriously think that anybody not knowing what that alphabet soup is supposed to mean must be from a different planet??? :ermm:
I do know that someone did pass a certain test (or series of tests) in a certain area by showing to be able to answer most of the questions asked during the test. Nothing more, but nothing less either.
On this site, at least what I experienced, certificates don't matter. All that matters is the ability to help answering questions. Sometimes, a one-liner just isn't enough.
May 31, 2011 at 10:23 am
Hello All: Please avoid trivial arguments on this forum and kindly take a look at the attached execution plans and DDL scripts. I would like to accept the fact that there was no DBA before me and I have noticed that the database is lacking normalization and there are way too many indexes (in fact many of them were copied from database tuning advisor and have the _dta prefix) and I am trying my best to review the index utilization and remove under\un-utilized indexes. After rebuilding the indexes and updating statistics with full scan it is doing a little better though is still quite a bit of database tuning to be performed. Any feedback will be greatly apprecialted by our team.
May 31, 2011 at 10:37 am
SC48035 (5/31/2011)
Hello All: Please avoid trivial arguments on this forum and kindly take a look at the attached execution plans and DDL scripts. I would like to accept the fact that there was no DBA before me and I have noticed that the database is lacking normalization and there are way too many indexes (in fact many of them were copied from database tuning advisor and have the _dta prefix) and I am trying my best to review the index utilization and remove under\un-utilized indexes. After rebuilding the indexes and updating statistics with full scan it is doing a little better though is still quite a bit of database tuning to be performed. Any feedback will be greatly apprecialted by our team.
Sorry for the cross fire but Syed has a very long history of giving bad, incomplete and out right dangerous advice. Unfortunately we have to call him out on every thread since we don't want that bad info to lead someone on the wrong path once they find this page on google.
Anyhow the very first thing that pops up in both plans is that you have a timeout in "Reason for early terminaison". That means that the server gave up trying to optimize the plan and took the last "valid" plan to give the correct results.
Now just the sheer number of joins might be at fault. If you have way too many indexes that just compounds the problem because it gives an exponential number of ways to get the data back.
In the test env. I'd consider dropping all unused indexes (there are plenty of script that identity all the "bad" indexes). I would start with the ones that are just never used (read). Then give it another go to see if the server can optimize the query.
However I think your likeliest shot here is to use the divide and conquer method. Break the query in smaller steps and drop in temp tables. That way the server will get better estimates on row count and be able to optimize correctly.
I wouldn't advise option #1 normally, but since you seem to have a tone of indexes, you'll have to do it anyway at some point.
I wish I had a magic wand to solve your issue, but I'm afraid it's going to take a fair amount of work and trial / error.
May 31, 2011 at 11:10 am
SC48035 (5/31/2011)
@Ninja's_RGR'us : Thanks for your feedback, will try my best to follow your advice.
There are a lot of people following this thread... hopefully they'll have other ideas. Mine is more based on a gut feeling than anything else. I never had a single query with 60-80 tables line this one and I never had too many indexes.
Now the ironic part is that the server is telling you that you have... wait for it : a missing index warning!
Other tips that might help :
- You seem to be selecting over and over from the same 1-2 objects. See if you can bring it down to 1 query (not sure it's possible, but worth investigating).
- It also seems that you are selecting from views. When doings views inner join views you often get a lot of redundant joins and that makes the optimizer work much harder. It even much worse when those views also use other views. This advice brings this project to a whole new level, but I'd seriously consider writing this query from base tables only. Just that could make all the difference in the world.
That last advice is obviously the longest to put in place and might not be worth it if this job runs unattended at night.
Whipping out the unsused index has an immediate server wide impact on all writes.
I'd seriously consider the server wide approach at first and maybe come back to this one. Maybe your manager can clear the priorities for you.
This is what I use to tune the whole server at once :
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply