February 4, 2009 at 3:16 am
Hi All,
I just got asked this question today but a work mate, and I didn't have an answer.
Other than it depends on the size of the indexes and the usefulness of them.
Sometimes I come across a query with loads of bookmark lookups and as a result I add a small index to the table and WHAM performance boost all over the system.
My question I guess before adding new indexes what sorts of things should you be thinking about?
removing older indexes, etc
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 4, 2009 at 4:43 am
"As many as are necessary, but no more."
Indexes are used to speed up queries but have the downside that they may slow down inserts and updates. Hence you need to look at the plans for your critical queries and add indexes as appropriate. As a first approximation, if there's a table scan then you probably need another index.
There's actually a system view which indicates where the optimizer would have liked to use an index but couldn't...
SELECT i.database_id AS [DBid],
m.[name] AS DBName,
i.[statement] AS [Table],
CASE
WHEN (i.equality_columns IS NOT NULL AND i.inequality_columns IS NULL)
THEN i.equality_columns
WHEN (i.equality_columns IS NULL AND i.inequality_columns IS NOT NULL)
THEN i.inequality_columns
ELSE
i.equality_columns + ', ' + i.inequality_columns
END AS [Equality],
i.included_columns AS [Included],
i.index_handle [Ix_Handle_Key]
FROM sys.dm_db_missing_index_details i join master..sysdatabases m
on i.database_id = m.dbid
ORDER BY database_id, Equality, i.index_handle
This actually comes from code based on the script here[/url].
Obviously, you still need to think about whether you actually need an index, since the optimizer records the informaction whether it's an ad-hoc query that's only ever going to run once or something that gets run hundreds of times a day!
Derek
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply