November 30, 2006 at 1:33 pm
I am attempting to get serious about upgrading performance in some of existing databases, though one is more important than all the rest. I already run showcontig and do reindexing. (This site was very helpful with that). Now as I look more into index tuning, the most obvious place to start looking seems to be in views to find the columns that are Joined or are used in the "where" clause and are not indexed.
Showcontig shows indexIDs but thats only for existing indexes. What about views where the creator/developers didn't know any better. I'd like to identify these areas and clean up the mess?
I can search syscomments to extract table names that follow "ON" conditions or "where" from views, but that's just scratches the surface. Then is there a way to search and find out if these columns are indexed? As I write this I'm getting some ideas but would really appreciate feedback and suggestions.
Does anyone know how I would approach this?
thanks
Matthew Mark
December 1, 2006 at 2:04 am
I assume that you have looked into the Index Tuning Wizard (if not, see BOL). What extra are you looking for?
December 1, 2006 at 2:11 am
I think the first thing you should do is run the Index tuning wizard to help identify any major bottle necks.
If you want comprehensive audit of your table design there is a great tool called Quest DB analysis. It will give you a report of all tables without indexes, without clustered indexes and also any foreign keys that have not been indexed. You can download a 7 day trial version from the quest software website.
December 4, 2006 at 4:14 am
be wary of the sql2k index tuning wizard - use it as a guide by all means - but don't take it's recommendations as gospel. I'm told the 2005 advisor is much better, so you might want to try this - it will run against a 2k database I think.
There's no easy way to analyse you indexes/fk's as most tools assume declared RFI, which you might not have, or unique column names, which again you might not have.
Drawing the schema may help - hopefully most foreign key columns will have the term ID in the name, which may help - however adding lots of single column indexes to tables may just lead to lots of unused indexes - I usually look for poor performing queries rather than a blanket approach .. each table should have a PK and clustered index as a start.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 4, 2006 at 9:23 am
Index tuning advisor/wizard is a great tool. However, as stated by others, it cannot be trusted as is. Once you received suggestions, a wise move would be to try to identify why exactly these suggestions were made, critically think through them, and them make a decision which (if any) to accept.
In order to analyze the suggestions, you'd probably need to look into the dataset you fed into the index tuning wizard/advisor. The dataset I am referring to here is the profiler/server side trace. I usually analyze this data to see which stored procedures or tsql got executed, which might trigger a certain index suggestion.
But, in general, add indexes with caution. Remember that indexes can cause trouble [i.e. degrade performance] (if added to a table which is updated very often). And if you do have tables where mostly selects are made, still, don't just add an index. Analyzing the profiler/server side trace, identify the stored procedures/tsql which runs most frequently, rank in desc order of frequency, and then optimize[using query analyzer, sub-tree cost, io-cost as a guide], adding indexes which would improve the most frequent sps/tsql first.
More information about index tuning can be found here: http://www.sql-server-performance.com/optimizing_indexes.asp
Lastly, to get information about indexes, explore sysindexes table. Joining this table with some other system tables, you should be able to get what you wanted (i.e. identify which tables have indexes on which columns).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply