September 26, 2006 at 8:22 am
I'm looking for more information on the Index Tuning Wizard. I think I have a pretty good basic understanding of it, but the more I run it the more questions I seem to have and I keep coming across the same very simple explanations. I'd like to have a deeper understanding of how it works and why I'm getting the results I do.
I ran the wizard yesterday after profiling one of the databases. I saved the recommendations to a script and upon reviewing the script I found it recommended indexing up to 12 columns of data in one index, and it did this on several tables. I thought it analyzed and recommended covering indexes on up to 4 columns. I realize that these are suggestions and that you have to make some judgement calls, but why would it recommend indexes like this?
On the advanced tab it is recommended that you limit workload queries to sample to 200. On a busy day around here, I can hit two hundred queries in the profiler within seconds. Does that mean that I should just run profiler for a few minutes at a time? That doesn't seem very representative of the activities going on. Which 200 queries does the tuning wizard sample and how does it choose them? If I run it again on the same Workload will it grab the same 200 queries?
As for the reports returned by the tuning wizard, they look very interesting but I'm not sure I understand what the results mean. I can't seem to find anything but very simple explanations about the reports. Can somebody recommend a really good reference for the tuning wizard and reports?
Thanks!
Linda
September 28, 2006 at 8:24 am
to be blunt the 2k tool is crap and likely to do more harm than good. I'd suggest using an eval of sql2005 and the tuning advisor which is much much better - I don't particularly like any of the tools but the 2k one is very good at suggesting dropping indexes which you might laster need!!!
if it helps, for 2k run the trace for as long as you can during your busy workload. It'll make for a large trace but you'll get better results.
I don't wish to be rude but if you don't understand what is being suggested get on a suitable training course or read books by Kalen Delaney or Kimberley Tripp on indexes.
multi column ( covered/composite ) indexes are often the best way to obtain performance improvements, it's a somewhat complex subject - there should be enough articles on ms site too.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 28, 2006 at 8:39 am
I don't mean to be rude either but this is a Discussion Forum and if this is not a good place to bring up a discussion, then what is it here for? FWIW, I have had MS courseware training, but it only goes so far. I am looking to apply those skills real world and I believe my post did ask for good reference material. Since I work on my own in a small shop I rely on places like this and user groups to share information and insights with others in the field.
September 29, 2006 at 6:24 am
Discussion is good - what I feel is that too many posts are not making any attempts to research first, so many answers are found in BOL and some investment in a few books would boost knowledge. There is a wealth of information in the articles on this site ( and others ) authors spend much time, I'm sure, putting their pieces together - even if we don't all agree on content or conclusions < grin >. I try to read every article posted.
A subscription to Technet is well worthwhile. I agree we all have to start somewhere but honestly did you attempt to seek out information prior to posting or just post? I stand by my comment about the 2k index tuning wizard - if you really want to use the tools then use the sql2005 one - much better. However, if you don't have a reasonable understanding of indexes you'll still struggle and may end up doing more harm than good - this is more than evident ( from chatting to ms support staff ) from many of the postings where changes are made without understanding the consequences.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 29, 2006 at 8:01 am
Yes I have made a good attempt to research my questions in BOL, the courseware materials, some of the WROX reference books I have and by Googling. The reason I asked the questions I did is because I seem to keep coming across the same information reiterated and I wanted to go a little deeper into the subject. I also read a lot of the SQL Central articles and have a list of favorites. Sometimes there is more than just "book smarts" to this stuff, but I do like to have an understanding that goes beyond the basics.
Aside from you, I have always heard everyone tout the Index Tuning Wizard as a useful tool, but I'm not sure I agree with that assessment after my experience with it. I have found that it seems to make recommendations that would do more harm then good. In fact I applied very few of the recommendations. I just wondered if I'm not seeing the "beauty" of this tool and if that's the case, I must be missing something.
As for SQL2005, I would love to get there but for the moment it is SQL2K.
October 2, 2006 at 10:01 am
no you're not missing anything - it's basically rubbish! You can use an eval of sql2005 to run the tuning advisor then transfer the information back to sql2k. I don't actually use these tools ( well I set up the 2k when another dba used it and came up with "proof" we had incorrect indexes on a database. I had to evaluate how it produced the information and if it had any value. I think if you disregard it's recommendations to drop indexes - it can only evaluate what queries were run during the trace - it may help. Kimberley Tripp demo'd the 2005 advisor to show that it could come up with the same index changes she'd choose , in this case 3 and 4 column secondary indexes.
The best hint I can suggest is that the answer lies somewhere within selectivity and covered indexes - usually multicolumn indexes get the best gains. the clustered index can be very useful to negate poor selectivity, but I don't think that's covered anywhere.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 4, 2006 at 8:55 am
That's one of the things that doesn't seem to make sense to me about the Tuning Wizard is that you're supposed to limit the number of queries you run the trace on, and then it comes back and makes recommendations on such a small sliver of day-to-day operations.
I've had it come back and propose dropping clustered indexes and recreating them on fields that make no sense. If you don't have an idea about day-to-day operations and the data model, you could make some serious indexing missteps. I seriously thought I must be doing something wrong, to get information back like that.
October 5, 2006 at 1:21 am
yup - that's what takes me back to my initial comment!!
I was fortunate to be able to take a 6.5 5 day training course on indexes way back, and follow up with a 7.0 3 day a couple of years later. Since then I've attended some days from Kalen Delaney and Kimberley Tripp. It's a bit extreme doing training courses on indexes I suppose, but I figure if someone is prepared to impart their knowledge then take advantage. At least I consider myself to have some fundamental knowledge and background, not withstanding each sql server version tends to change things around anyway!! If you ever get a chance to attend any of Kalen or Kimberley's courses/events on indexes do so - quite mind blowing!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 5, 2006 at 7:36 am
So far I like the way they have broken up the MS courseware for SQL 2005 into smaller, and what appears to be more in-depth coverage of SQL topics. I "believe" indexing is one of them, but I'll have to go back and look.
I've heard several people mention Kalen Delaney and Kimberley Tripp........I bookmarked both of their blog sites! Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply