| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-08-09 : 11:33:59
|
| Anyone have experience with the Index Tuning Wizard?I'd like to give it a try, but there's not much on creating the INPUT needed using Profiler.Any comments appreciated.Sam |
|
|
MuadDBA
628 Posts |
Posted - 2004-08-09 : 12:27:16
|
| Not a lot. I guess it's helpful for extreme novices, but if you run a profiler looking for the standard poor perfoming queries, you can figure out a lot of your needed indexes pretty easily.However, sometimes queries that could use indexes wouldnt show up as poor performing simply because they don't take that long to complete even though they're poorly written. It's only when you pile them up on the server that you create problems (ie you have 1000 users running a query that takes 1 second to execute....not bad, huh? Except it could be executing in .1 second if you had the proper indexes...) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 13:27:13
|
| Sam, just create a trace file/table using SQL Proiiler. Let the trace run for a few hours. You can just use the default events to make it easy. Then send the trace file/table through the Index Tuning Wizard. It'll ask for its location in the wizard.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-09 : 13:53:22
|
| Thanks Tara. It's chugging away. I'll keep an eye on the file size.Is Index Tuning for novices as Crazy Joe suggests ? Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 13:58:32
|
| I rarely get anything from the wizard that I didn't already catch in my performance tuning analysis, so I rarely use it. The wizard just tells you what SQL Server thinks about the indexes and statistics.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-09 : 14:05:07
|
| What "performance tuning analysis" do you do? I'm wondering if I can do better. I usually design indicies to get desirable execution plans and performance in my stored procs. I've been a little disappointed (I wonder if others have this problem) that the SQL optimizer often overlooks using the right index on a query and goes with an index scan using a lesser index.I've also found that reindexing can change the optimizer's selection of indexes. I suppose this may have something to do with statistics being reset. It's like herding cats if you ask me.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 14:07:13
|
| Performance tuning analysis involves making sure that the FKs are indexed (this is not done by default, many people forget to add these after the FK constraint has been added), making sure that there are indexes to support the WHERE clauses of a lot of queries but not all as too many indexes impact inserts, reindexing, updating statistics, execution plans, SQL Profiler durations, etc...Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-08-10 : 11:43:20
|
| Yeah, be careful with the index tuning wizard....it has suggested some pretty wild indexes for me, such as indexes covering 50% of the fields in a table, etc....sure that might be great from a query performance angle, but what about my disk space? What about when I go to insert data? It doesn't take things like that into account, really.Every now and then I find a query wehre the optimizer deliberately looks past my indexes like a 2 year-old who refuses to drink out of a cup and instead insists on a bottle. I use index hints for that (sotrry, no comparable analogy for 2 year olds yet, mine's only 13 months). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-10 : 12:35:42
|
| Agreed. Sam, after the index tuning wizard gives suggestions, do not implement them without first doing analysis to see if it is correct. The wizard gives you the option of showing you the recommendations or doing them for you. Don't let it do it for you.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-10 : 14:14:50
|
| Thanks,I'm gathering more stats this afternoon. My run yesterday suggested I drop several indicies but no improvements.Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-10 : 19:30:21
|
| OK. Today I gathered a 20 Mb file of stats, and the Index Tuning Wizard says there's no events in the file related to the database.I can reload the file in Profiler and view the events.Any ideas? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-11 : 12:02:17
|
| I've seen that message a few times. Try adding some more events instead of using the default. I would add Stored Procedures: SP:StmtCompleted and TSQL: SQL:StmtCompleted.Tara |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-08-11 : 13:26:16
|
| to extend this coversation,say i add a column to an already indexed table.Oracle gives an option to analyze a table and re-validate the structure.Anything parallel to this in SQL?Also,iam assuming that all indexes should be created in the Indexfilegroup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-11 : 14:07:02
|
| I don't know what revalidate the structure means.Indexfilegroup is not a default setup.Tara |
|
|
|
rlahoty
Starting Member
11 Posts |
Posted - 2004-08-11 : 15:35:23
|
| You can use DBCC CHECKDB to validate the structural integrity of all the objects in that database. And yes, Tara is right in saying that there is no default index file group as such. You can create a new file group and explicitly create indexes on them.HTH-Rajeev Lahoty |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-11 : 15:38:47
|
| But what does checking the integrity of a table/database have to do with adding a column?Tara |
|
|
|
|
Index Tuning Wizard