June 19, 2007 at 10:59 am
HI
I have been asked to do an index review on a bunch of our regional servers
We have a database(A) that gets replicated to various regional servers.
This database(A) is used heavily for reporting but the regionals are not.
What would you recommend the steps to use and tools to use to decide what indexes are to be used and what are not.
I am very new to performance tuning so any advice would be appreciated
June 20, 2007 at 2:31 am
A starting point would be to use profiler to capture a days work (Use the Performance Tuning template) and then get the Index Wizard to analyse the data. This will give you a good idea are what is needed in terms of not used and required indexes.
The thing you need to be careful of is that you say the one is used heavily for reporting and the others not. Indexes that might be needed for reporting but not used on the others would slow them down.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 20, 2007 at 5:39 am
You can also just look for bad & missing items. Make sure every table as a primary key, a clustered key, that you have no duplicate indexes, and that any compound indexes start with the most unique column. Use care if you decide to change things, even the smallest index plan can have a large affect on performance, positive or negative.
June 20, 2007 at 8:17 am
i personally can't stand the DTA
i would run a profiler trace and look for queries that do a lot of reads and compare to rows returned. My personal ideal is 2 to 1 reads per row returned. if you are 10 to 1 then it's a problem.
then look at the query, execution plan and the indexes on the table(s). and play with the indexes as needed.
one thing i noticed yesterday is that sometimes a query hint can make performance worse than without it
June 20, 2007 at 8:30 am
Thanks guys this advice is all great!
How do you work out the ratio of reads per row returned?
June 20, 2007 at 8:32 am
You can also use new 2005 dynamic views :
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_details
- to determine missing indexes,
and
sys.dm_db_index_usage_stats - to find unused indexes
June 21, 2007 at 1:50 pm
set statistics io on
your query goes here
set statistics io off
run all three lines and click the messages tab and it will show you the number of reads. or you can run profiler, take the queries with the most reads and see how many rows they return
June 21, 2007 at 2:03 pm
Is there any way to get the rowcount in profiler (directly, without re-rerunning the query)? I'm guessing no, but hopping for a yes on this one!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply