December 30, 2010 at 12:46 pm
Eric Russell 13013 (12/30/2010)
Index 1: CafeNumber, BusinessDate
Index 2: BusinessDate, CafeNumber, DField
Index 3: id, BusinessDate, CafeNumber, PosNumber
There is a reason for multiple indexes. For example, if a reporting type query only searches on BusinessDate, and none of the indexes have BusinessDate as the leading column, then that will most likely result in a full table scan. Some of these composite indexes may have originally been created in an attempt to support a specific covered query scenario, so I wouldn't drop them just because they seem odd.
If there is already a clustered primary key on [id], then Index 3 may be redundant and perhaps not used. That's the one that stands out without knowing more about your database.
SQL Server 2005/2008 provides much better options for evanualting things like index usage than v2000. For example, once you have this database ported over to QA, you can use the Database Tuning Advisor (DTA) while smoke testing the application and running sample procedure calls.
Also, there are some DMV queries that will reveal which indexes are used and how frequently. For example, it will give you the actual number of times an index was utilized in a query and even the cost of an index to maintain in terms of how frequently it's updated. However, the result of these queries are based on SQL Server's internal usage statistics, so you first need to perform a sample workload of the application on the server before you get back useful information.
I'll dump the queries I typically use below:
--Unused indexes and tables
SELECT
'[' + object_name(i.object_id) + '].[' + i.name + ']' as TableIndexName,
i.index_id
FROMsys.indexes i
INNER JOIN sys.objects o
ONo.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s
ONi.object_id=s.object_id
ANDi.index_id=s.index_id
ANDdatabase_id = DB_ID()
WHEREobjectproperty(o.object_id,'IsUserTable') = 1
ANDs.index_id IS NULL
ORDER BY TableIndexName ASC
--Index usage. Least used appear first.
SELECTobject_name(s.object_id) AS ObjectName
, s.object_id
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
FROMsys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ONi.object_id = s.object_id
AND i.index_id = s.index_id
WHEREdatabase_id = db_id ()
ANDobjectproperty(s.object_id,'IsUserTable') = 1
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC
--Index scan appear more than index seeks
SELECTobject_name(s.object_id) AS ObjectName
, s.object_id
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
FROMsys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ONi.object_id = s.object_id
AND i.index_id = s.index_id
WHEREdatabase_id = db_id ()
ANDobjectproperty(s.object_id,'IsUserTable') = 1
ANDuser_scans > user_seeks
ORDER BY user_scans DESC
--Index updated more than it is used
SELECTobject_name(s.object_id) AS ObjectName
, s.object_id
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
FROMsys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ONi.object_id = s.object_id
AND i.index_id = s.index_id
WHEREdatabase_id = db_id ()
ANDobjectproperty(s.object_id,'IsUserTable') = 1
ANDuser_updates > (user_scans + user_seeks)
ANDs.index_id > 1
ORDER BY user_updates DESC
Yeah, I do need to get around to buying that DMV book I've been meaning to get. I was just waiting for it to be finished first, but I suppose I could buy it now and read the chapters that are done.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 30, 2010 at 12:59 pm
Stefan Krzywicki (12/30/2010)
Yeah, I do need to get around to buying that DMV book I've been meaning to get. I was just waiting for it to be finished first, but I suppose I could buy it now and read the chapters that are done.
Red Gate has a free eBook on DMVs.
http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 30, 2010 at 1:00 pm
Eric Russell 13013 (12/30/2010)
Stefan Krzywicki (12/30/2010)
Yeah, I do need to get around to buying that DMV book I've been meaning to get. I was just waiting for it to be finished first, but I suppose I could buy it now and read the chapters that are done.Red Gate has a free eBook on DMVs.
http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv
Thanks, I'll take a look.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 30, 2010 at 1:02 pm
Thanks Gail, thanks everyone who replied. After reading this thread and many of the links I added some new indexes to 2 tables and a query that had been taking 3 hours to run now takes 30 seconds. I did make some other changes at the same time, but the indexes were likely a big part of that improvement.
Thanks! Learning new things is always fun. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply