April 15, 2008 at 10:27 am
hi guys! i am trying to optimize a slow query, i put the workload in database tuning advisor, and i got to that i have to create statistics to over 30 columns and create 20 indexes and i will 80% improvement, my question is, if i go ahead and create statistics would this put a lock in my tables? what about creating the indexes?
April 15, 2008 at 12:42 pm
How big are the tables? Millions of rows?
Creating statistics on a table is usually automatic. You might want to check the table properties on that regard. You can set up a maintenance task (or a scheduled script) to update the statistics.
I don't think creating indexes will cause locks, but I'm really not sure. I know it can take a while, depending on the index and the table it's on. The thing that matters more, in most cases, is too many indexes will slow down updates/inserts/deletes in the table (while usually speeding up selects).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2008 at 1:26 pm
Take the tuning adviser's advice with a large pinch of salt. It was a tendency to massively over-recommend.
What I would suggest is that you try the indexes that it's suggested one by one and see which ones have the most effect on your query. It's unlikely that all of them are necessary.
As for the statistics, providing you have auto create statistics on, the query processor will create them as needed. Feel free to try them out, see if they improve the query though.
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
April 15, 2008 at 1:49 pm
Everything Gail said and look at your execution plan (I'm getting sick of typing that. Anyone have a macro?) to see which indexes have a positive affect and which have the most positive affect. You may find that the majority of the cost is taken up by a single merge join or table scan that one index will fix, making the rest of the recommendations completely redundant.
"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
April 15, 2008 at 1:56 pm
Grant Fritchey (4/15/2008)
... and look at your execution plan (I'm getting sick of typing that. Anyone have a macro?)
Ctrl-C, Ctrl-V. I'm starting to keep a text file of common replies. 😀
Absolutely on the execution plan. You can also, if you have a test system with similar data volumes, implement all the indexes, look at the execution plan to see which ones the optimiser likes the most, then drop the ones it's not using.
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
April 15, 2008 at 2:25 pm
thank you guys!!, for what it is worth, i did look at the execution plan, and i compared both. :). One more question, i have my database properties as autocreate and autoupdate statistics, how come my statistics are out of date?
April 15, 2008 at 8:47 pm
here is some info on statistics maintenance. this should answer your question. statistics are only updated when a certain threshold or percent of updates of occured to the data. if the threshold is not reached then statistics are not updated. hope that helps
April 16, 2008 at 7:37 am
yes it did! i scheduled a job to rebuild my indexes every sundaty at 1:00 am, another one to regorganize my indexes every week on sun at 2 am and then another one to update statistics every week on sun at 4:00 am :). does it sound like the right thing to do?
thank you guys!!! i really apreciate it.
April 17, 2008 at 12:21 am
Severe overkill. 😉
Reindex completely rebuilds an index and updates the statistics with a full scan.
Reorganise shuffles leaf pages of an index into order. If the index has just been rebuilt, this will have nothing to do. Reorganise does not update statistics
Update statistics updates the stats based on a sample of the table. If the index was very recently rebuilt, this can make your stats less accurate.
Drop the reorg and the update stats jobs. The reindex does everything that the two of them do (and more).
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
April 17, 2008 at 7:14 am
Thank you so much!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply