May 17, 2012 at 1:08 pm
Elizabeth.Block (5/17/2012)
Jeff Moden (5/17/2012)
Elizabeth.Block (5/16/2012)
The indexes weren't fragmented but they weren't good either.Can you be more specific about that please? For example, how do you know they weren't good if they weren't fragmented?
I know they weren't fragmented because I checked the tools say they're not fragmented. I found the slowest query that they used very frequently. We timed the query on test and it ran in 2 seconds; in production it ran in 24 seconds. We restored production to test and then the query on test ran in 24 seconds. After rebuilding the indexes for the 3 largest tables for that query, voila, the query ran in 2 seconds again. I had never seen this before so it was a new one to me.
Sounds more like stale stats than a corrupt index.
May 17, 2012 at 3:04 pm
I thought it was stale stats too but we updated the stats on those tables and it didn't help.
Now for the bad news... I rebuilt the 3 indexes yesterday and now the performance is bad again. I updated stats on the 3 tables which didn't help. So now I have to dig deeper. I'm going to have someone run diagnostics on the hard drive with tempdb on it, because it's having really high disk queue lengths.
May 17, 2012 at 3:09 pm
Elizabeth.Block (5/17/2012)
I thought it was stale stats too but we updated the stats on those tables and it didn't help.Now for the bad news... I rebuilt the 3 indexes yesterday and now the performance is bad again. I updated stats on the 3 tables which didn't help. So now I have to dig deeper. I'm going to have someone run diagnostics on the hard drive with tempdb on it, because it's having really high disk queue lengths.
How did you update the stats, how big is/are the table/s you rebuilt the 3 indexes?
May 17, 2012 at 3:28 pm
Lynn Pettis (5/17/2012)
Elizabeth.Block (5/17/2012)
I thought it was stale stats too but we updated the stats on those tables and it didn't help.Now for the bad news... I rebuilt the 3 indexes yesterday and now the performance is bad again. I updated stats on the 3 tables which didn't help. So now I have to dig deeper. I'm going to have someone run diagnostics on the hard drive with tempdb on it, because it's having really high disk queue lengths.
How did you update the stats, how big is/are the table/s you rebuilt the 3 indexes?
I have 3 large tables - 7.5 million, 11.7 million, and 81 million
Here is the approximate code I've used.
--code to update statistics on LargeTable
update statistics LargeTable
ALTER INDEX ALL ON LargeTable
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
May 17, 2012 at 3:53 pm
I do have the actual execution plan for the monster query so I will start going through that.
May 17, 2012 at 4:12 pm
Elizabeth.Block (5/17/2012)
Lynn Pettis (5/17/2012)
Elizabeth.Block (5/17/2012)
I thought it was stale stats too but we updated the stats on those tables and it didn't help.Now for the bad news... I rebuilt the 3 indexes yesterday and now the performance is bad again. I updated stats on the 3 tables which didn't help. So now I have to dig deeper. I'm going to have someone run diagnostics on the hard drive with tempdb on it, because it's having really high disk queue lengths.
How did you update the stats, how big is/are the table/s you rebuilt the 3 indexes?
I have 3 large tables - 7.5 million, 11.7 million, and 81 million
Here is the approximate code I've used.
--code to update statistics on LargeTable
update statistics LargeTable
ALTER INDEX ALL ON LargeTable
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
The above is not just updating your statistics, it is rebuilding your indexes. One of the side effects of this is that it does a full scan to accomplish this.
Try the following next time things go south on the table:
UPDATE STATISTICS dbo.LargeTable
WITH FULLSCAN;
May 17, 2012 at 4:15 pm
Please note that the autoupdate statistics process does a sampling, not a full scan, so it may not be good enough for your query.
May 17, 2012 at 4:32 pm
Right, I had forgotten about full scan for update stats. That would be so much faster than rebuilding the index. Thanks!
May 18, 2012 at 6:34 am
Elizabeth, I will point out that you have been going at this one performance issue for 3 days now. It is quite possible that getting a good performance tuning professional on board would quickly identify the true root cause of this query issue and you would be up and functional. Just a thought. It can be fun hunting and pecking to find a problem but if having your "very large query that runs very frequently" run in an acceptable time is important - and it sounded from your posts like it was - then a professional "sniper" is definitely the way to go.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2012 at 10:23 am
Thanks, Kevin, I'll keep it in mind. Due to budget considerations I don't think that's an option. I'm currently trying to do what 4 DBAs used to do. I have ordered Grant's book SQL Server 2008 Query Performance Tuning Distilled and will devour it when it comes. Right now I just have to learn tuning as fast and as best as I can. I am really enjoying learning all this cool stuff!
May 20, 2012 at 2:03 am
You might want to try the free trial of Qure Optimizer, a workload tuning solution by DBSophic. I have seen quite a few cases where users didn't get what they were looking for from DTA, but other tools where able to help. Qure Optimizer has its own very sophisticated indexing algorithm (it doesn't rely on the Missing Index DMV's), and all recommendations are automatically benchmarked and verified against a copy-of-production. Given what you experienced with DTA, it's worth a try. Here are the links:
Qure Optimizer free trial download
Short intro video (recommended)
May 20, 2012 at 10:42 am
gil 17845 (5/20/2012)
You might want to try the free trial of Qure Optimizer, a workload tuning solution by DBSophic. I have seen quite a few cases where users didn't get what they were looking for from DTA, but other tools where able to help. Qure Optimizer has its own very sophisticated indexing algorithm (it doesn't rely on the Missing Index DMV's), and all recommendations are automatically benchmarked and verified against a copy-of-production. Given what you experienced with DTA, it's worth a try. Here are the links:Qure Optimizer free trial download
Short intro video (recommended)
Thanks for the tip! I'll look into this.
May 20, 2012 at 11:40 am
Evil Kraig F (5/15/2012)
DTA is, at best, a wonderful moron.
The tool isn't the problem, the way people use it is. In my experience, 'moronic' activities in databases are more frequently associated with DBAs than DTAs 😛
Cast your eye over the following paper one day. As well as being a great source of accurate information, it compares properly-used DTA with the efforts of experienced DBAs. Interesting stuff.
http://research.microsoft.com/pubs/76555/vldb04.pdf
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 20, 2012 at 11:49 am
Elizabeth.Block (5/17/2012)
I do have the actual execution plan for the monster query so I will start going through that.
This is where I would start, comparing actual plans for a 2-second run with a 24-second run. Several people have invited you to share the plans, so I will just repeat that request together with an offer to help analyse it. The reason for the performance difference will likely be immediately apparent. Reading between the lines of your replies, you could also look at implementing benchmarking and waits and queues analysis, so you can anticipate problems and take corrective action before bad things happen. Brent Ozar has a good introduction to the topic here[/url].
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 20, 2012 at 4:01 pm
gil 17845 (5/20/2012)
and all recommendations are automatically benchmarked and verified against a copy-of-production.
I've evaluated a couple such products in the last year and, I have to tell you, I'll never let such things into "my house".
First, while it sounds nice that this runs and tests against a copy of production, it also means that you actually need a copy of production. Try that with a multi-terabyte DB or even a lowly 50GB db if you simply don't have the space for it. Yes, you can run it against the production copy for "lesser" results... I can just see all of you allowing such a thing to happen. :hehe:
Second, I've reviewed some of the code changes these things recommend. One of the things that it recommended was to make a 20 union query of nearly the identical single query to replace it. Each unioned query had more than a 1000 lines of code plus UNION (it didn't even recognize that a UNION ALL could have been done). It made several other recommendations for code changes that I'd have to kill a developers entire family for just to make sure the gene pool was stopped :-P. It also took several DAYs of run time to come up with all this crap and I STILL had to review it for sanity.
If you want to find and fix code performance problems, get a good DBA or Developer either as an FTE or a consultant and let them have at it. You'll get a MUCH bigger bang for the buck in my not so humble opinion.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply