Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2005-08-23 : 12:06:35
|
| I've tried everything I know to try and improve the performance of a database that I have inherited. Have changed all the settings possible and things have improved marginally.Now its down to the individual indexes. my problem is that i've used a number of books now and have found some of the advice on index selection seems to be a bit contradictory. After speaking to users i'm trying to get a picture of what each table does and then pick sensible new indexes on my worst performing queries. The problem is i'm not exactly sure about what all the stats mean in the execution plan. I added a new index and see that my queries are now using the index, but the figures in the execution plan when i hover over the index symbol look the same as the old table scan stats did. Has anyone got a number of points to look at or better still a link to analysing the execution plan.thanks in advance! |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-23 : 13:03:14
|
| on fields you commonly search or join on you may want to apply an index if the field has a high number of distinctive values. you do this in hopes of creating as many index seeks and clustered index seeks as possible. the optimizer may not choose to use an index for a number of reasons including too few records in the table, a function like REPLACE, COALESCE or RIGHT in the where clause, a wild card to the left of the search paramter etc... Too make things a little more confusing, the optimizer may not always choose the best execution plan if there are too many possible plans for the query, the statistics are out of date etc... It is a little bit of an art. Why don't you post some code, the table definition, and some sample data for one ofn the queries you are struggling with.====================================================Regards,Sean RoussyThank you, drive through |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-23 : 15:01:03
|
| It will be helpful if you could pick a single query that is giving you trouble. If you could post the code of that query, along with the table DDL (including indexes) we could probably help you out.Throwing in some sample data would also be helpful.-ec |
|
|
|
|
|
|
Execution Plan's & Query selection