Page 83 - DLIS405_INFORMATION_STORAGE_AND_RETRIEVAL

Information Storage and Retrieval

Notes from sys.sysindexkeys as k

where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path(‘’)) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path(‘’)) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’,
c1.name as ‘index’,
c2.name as ‘exactduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

Acceptance Testing Phase

Now that you have promoted your database and application code to the next level in the development
lifecycle, you need to start reviewing those indexes you created. Hopefully, in this phase of your
application development cycle you will have users actually trying out your application. They will
be running your application code through its paces by testing those reports, and search capability
you’ve built into your application. Having your testing staff use your application gives you an
opportunity to now see which indexes are being used by real application usage.
One of the things you should consider doing in this phase is to monitor your index usage. Look at
how often each index is being used. Pay particular attention to which indexes are being updated
heavily. As part of this analysis, you should determine those indexes that are constantly being
updated, and never used. For those indexes that are not being used but are being update, you might
want to consider whether you need those indexes.
In addition, for indexes that are being used and updated frequently you might want to review the
FILLFACTOR to make sure it is appropriate, so that you don’t get too many page splits. You can
use the Dynamic Management Views (DVM’s) that became available with SQL Server 2005 to find
index usage information. Keep in mind this dynamic information is only collected since the last
time SQL Server started, so it might not contain all of the statistics associated with indexes, especially
those indexes that are infrequently used. Here is a sample script that shows you the index usage
information for the AdventureWorks database.

78 LOVELY PROFESSIONAL UNIVERSITY