analysing indexes part 3

Analysing Indexes Part 3

This is the long time coming third part of analysing indexes. It nee= ds to be viewed along with this diagram which shows the basic data collection tasks set out in parts 1 and 2 and also with parts 1 and 2 of this document= .

Dynamic management vie= ws used in this document
sys.indexes	Information about indexes which can help identify potential issues. See example i_2 below
sys.index_columns	If you want to write functions or queries that extract index information from a table this is where to go. see example i_1 below
sys.columns	From the point of view of index analysis the main use of this table is to list column names, however you can also check for mixed collations, something which can cause performance issues. This table replaces syscolumns. See example= i_3 below
sys.partitions	This is where the row count for a table is store= d. This is also where you go to see if a table is partitioned and how many r= ows are in each partition
sys.allocation_units	I can only quote BOL “Contains a row for each allocation unit in the database “ Conside= r a drill down of sp_spaceused if you like
sys.filegroups	Contains information on filegroups
sys.sysfiles	Contains information about the physical files wh= ich make up the filegroup(s)

Example i_1 ( sys.index_columns )

--<= /o:p>

-- lots of useful information in sys.index_columns=

use AdventureWorks;

select * from sys.index_co= lumns

where [object_id] = =3D object_id= ('person.stateprovince');

Result set from above que= ry

object_id	Ind= ex id	Ind= ex column id	Col= umn id	Key= ordinal	Par= tition ordinal	Is descending key	Is = included column
2066106401	1	1	1	1	0	0	0
2066106401	2	1	5	1	0	0	0
2066106401	3	1	2	1	0	0	0
2066106401	3	2	3	2	0	0	0
2066106401	4	1	7	1	0	0	0
2066106401	7	1	1	1	0	0	0
2066106401	7	2	3	2	0	1	0
2066106401	7	3	2	3	0	1	0
2066106401	7	4	4	4	0	0	0
2066106401	7	5	5	0	0	0	1

<= /b>

Example i_2 ( sys.indexes )

--<= /o:p>

-- find indexes with non default locking

--

Use AdventureWorks;

go

select object_nam= e([object_id]),[name],type_desc,=

case allow_row_loc= ks

when 1 then= 'Yes'

else 'No'

end as 'Row Locks',

case allow_page_lo= cks

when 1 then= 'Yes'

else 'No'

end as 'Page Locks'

from sys.indexes<= o:p>

where not(allow_row_locks)= =3D1 or not(allow_page_locks)= =3D1;

Result set from above que= ry

Table name

Index name

type_desc

Row Locks

Page Locks

queue_messages_19770= 58079

queue_clustered_inde= x

CLUSTERED=

Yes

No=

queue_messages_19770= 58079

queue_secondary_inde= x

NONCLUSTERED

Yes

No=

queue_messages_20090= 58193

queue_clustered_inde= x

CLUSTERED=

Yes

No=

queue_messages_20090= 58193

queue_secondary_inde= x

NONCLUSTERED

Yes

No=

queue_messages_20410= 58307

queue_clustered_inde= x

CLUSTERED=

Yes

No=

queue_messages_20410= 58307

queue_secondary_inde= x

NONCLUSTERED

Yes

No=

Example i_3 ( sys.columns )

--

-- how many collations in my database=

--

Use AdventureWorks;

go

select distinct collation_name from sys.columns;=

Result set from above que= ry

Collation_name

Latin1_General_CI_AS=

UNICODE_CODEPOINT

NULL

Latin1_General_CI_AS= _KS_WS

Latin1_General_BIN

Latin1_General_CI_AS is the default collation of the installed server and the AdventureWorks database.

<= /b>

Example i_4 showing distribution of a partitioned i= ndex

--

-- shows distribution of partitioned indexes=

-- ( I'm not 100% totally sure on this query but it app= ears correct )

--

select object_nam= e(sp.[object= _id]) as 'Table Name',si.name as 'Index Name',fg.name as 'Filegroup Name',sf.name as 'File Name',= sf.filename as 'Physical File', sp.*

from

sys.partitions sp join sys.indexes<= /span> si on sp.[object_id] =3D si.[object_id]

join sys.allocati= on_units au on sp.hobt_id =3D au.container_id

join sys.filegrou= ps fg on au.data_space_id =3D fg.data_space_id

join sys.sysfiles= sf on fg.data_space_id =3D sf.groupid

where si.[ob= ject_id] =3D objec= t_id('dbo.logdata')

and au.typ= e in (1,3)

order by sp.partition_number,sp.index_i= d;

The results do not format very well in portrait so I’ll place them i= nto a separate document

indexpt3-extra.doc

Example i_5 how much spac= e does a table and each index take up

--

-- see allocation of pages and space for the indexes on= a table

-- works with partitioned tables and indexes=

--

select

object_name(si.[object_id]) as 'Table Name',

si.name as 'Index Name',<= /o:p>

sum(au.total_pages) as 'Total Pages',

sum(au.used_pages) as 'Used Pages',

sum(au.data_pages) as 'Data Pages',

(sum(au.tota= l_pages) * 8) / 1024 as 'Total Space (MB= )',

(sum(au.used= _pages) * 8) / 1024 as 'Used Space (MB)= ',

(sum(au.data= _pages) * 8) / 1024 as 'DataSpace (MB)'=

from

sys.indexes si join sys.partitions sp on si.= [object_id] =3D sp.[object_id] and <= /span>si.index_id =3D sp.index_id

join sys.allocati= on_units au on sp.partition_id =3D au.cont= ainer_id

where si.[ob= ject_id] =3D objec= t_id('dbo.logdata')-- replace dbo.l= ogdata with your tablename

group by si.[object_id], si.index_= id, si.name;<= o:p>

Table Name

Index Name

Total Pages

Used Pages

Data Pages

Total Space (MB)

Used Space (MB)

Data Space (MB)

LogData

PK_LogData

5532

5497

5000

43

42

39

LogData

idx_logdata_numkey

17

14

12

0

0

0

--

-- using sys.dm_db_index_physical_stats

--

select object_nam= e([object_id]),index_id,index_type_desc,<= /span>sum(page= _count) as pages = from sys.dm_db_index_physical_stats(db_id(),object_id('dbo.logging'),null,null,null)

where [object_id] = =3D object_id= ('dbo.logdata')=

group by object_name([object_id]),index_id,index_type_desc;<= o:p>

Table Name

Index id

index_type_desc

pages

LogData

1

CLUSTERED INDEX=

5450

LogData

2

NONCLUSTERED INDEX

12

( You will note that I rarely make use of SQL 2000 compatibility vie= ws )

=

16.0 NOTES:

·      =    All of the analaysis has also been run agai= nst SQL2008 and as far as I can see everything so far works just the same.<= /span>

·      =    A number of factors may affect the data which is presented through the management views, there is, as far as I can tell, no guarantee that data for a specific index actually exists within the managem= ent views at all times, you can compare results with the queries listed below ( 16.1 through 16. 6 ).=

·         Tables without data may also be represented within= sys.dm_db_index_usage_stats , this doesn’t appear to make any logical sense, however tables with transient data may be considered and it could be that the actual process of updating stats forces the index into the dmv.

·      =    This is for investigation further down the line; I may obtain information later which may help explain.<= /span>

·      =    An index rebuild pushes the clustered index into the system view if there is data in the table

·      =    sp_updatestats will update the dmv’s for ea= ch index the procedure updates, whereas dbcc update statistics pushes an entry= for every index.

o    This is because sp_updatestats only affects statistics which need an update whereas dbcc update statistics forces an up= date regardless.

·      =    Dbcc dbreindex appears to put only the clustered index into the dmv’s as does the more correct command alter index= all rebuild

·      =    The tests have not been exhaustive but give an indication of what may or may not place the index details into the dmv.

·      =    Taking the database offline will clear the ( index= ) dmv’s of the data for that database.

16.1 List indexes on user tables which have at least 1 row of data

--

-- indexes on tables with data

--

select ind.* = from sys.indexes<= /span> ind=

where ind.[o= bject_id] in (<= span style=3D'color:blue'>select [object_id] from sys.partitions where rows > 0)

and objectprop= erty(ind.[objec= t_id],'IsUserTable')=3D1;<= /span>

go

16.2 List indexes on user tables which have no data

--

-- indexes on tables with no data

--

select ind.* = from sys.indexes<= /span> ind=

where ind.[o= bject_id] in (<= span style=3D'color:blue'>select [object_id] from sys.partitions where rows =3D 0)

and objectprop= erty(ind.[objec= t_id],'IsUserTable')=3D1;<= /span>

go

16.3 How many indexes in total on user tables

--

-- how many indexes from user tables =

--

select * from sys.dm_db_in= dex_usage_stats where database_id =3D db_id()

and objectprop= erty([object_id],'IsUserTable')=3D 1;

go

16.4 Which of my indexes in 16.1 are within the sys.dm_db_index_usage_stats dmv

--

-- which indexes are in dmv where the table contains da= ta

--

select ind.* = from sys.indexes<= /span> ind join sys.dm_db_in= dex_usage_stats ust

on ind.[o= bject_id] =3D ust.[object_id] and ind.[index_id] =3D ust.[index_id]

where ind.[o= bject_id] in (<= span style=3D'color:blue'>select [object_id] from sys.partitions where rows > 0)

and objectprop= erty(ind.[objec= t_id],'IsUserTable')=3D 1

and ust.da= tabase_id =3D db_id= ();

go

<= /b>

<= /b>

<= /b>

<= /b>

16.5 Which of my indexes = in 16.1 are not within the sys.dm_db_index_usage_stats dmv<= /span>

--

-- which indexes are not in the dmv but have data in the table

--

select * from sys.indexes<= /span> where

convert(var= char(20),[ob= ject_id])+'@@'+ convert(varchar(4),[index_= id])

not in ( select convert= (varchar(20),[objec= t_id])+'@@'+ convert(varchar(4),[index_= id]) from sys.dm_db_index_usage_stats where database_id = =3D db_id())<= o:p>

and [object_id] i= n (select [object_id] from sys.partitions where rows > 0)=

and objectprop= erty([object_id],'IsUserTable')=3D1;

go

16.6 Which indexes are in= sys.dm_db_index_usage_stats but don’t have any data

--

-- which indexes are in dmv but have no data=

--

select ind.* = from sys.indexes<= /span> ind with (nolock) join sys.dm_db_in= dex_usage_stats ust with (nolock)=

on ind.[o= bject_id] =3D ust.[object_id] and ind.[index_id] =3D ust.[index_id]

where ind.[o= bject_id] in (<= span style=3D'color:blue'>select [object_id] from sys.partitions where rows =3D 0)

and objectprop= erty(ind.[objec= t_id],'IsUserTable')=3D 1

and ust.da= tabase_id =3D db_id= ();

go

This may seem strange but in my production systems I did find this t= o be the case

17.0 Finding Unused Inde= xes

·      =    In section 6.0 we set up a job to collect index information. The query ran every night and recorded if an index had not been used in the previous 24 hours for a user = scan or user seek.

·      =    The number of days you need to collect has to be b= ased around your database usage cycle

·      =    Run for at least two months if you have a monthly cycle; e.g. month end processes.

o    ( Assumes collected information is stored in a tab= le database named DBA )

17.1 indexes with no seek= s or scans

--

-- Run this in the context of the monitored database

-- results where there were no seeks or scans

--

select object_nam= e([object_id])as ObjectName,[object_id],index_id, = count(*)= as HowMany from DBA.dbo.tbl_Un= used_Indexes

where user_seeks+user_scans=3D0

group by [object_id],index_id<= /p>
order by count(*) desc,[object_id];

go

Sample results shown, the collection cycle is 71 days

ObjectName

object_id

index_id

HowMany

RegionalSales

74235815

3

71

RegionalSales

74235815

4

71

RegionalSales

74235815

5

71

Emloyees<= /span>

138756593

2

71

Publishers

288472552

9

71

Publishers

288472552

10

71

Authors

291037064

2

70

Authors

291037064

6

70

ApplicationName=

320472666

2

69

StoreLocation

348996770

3

68

StoreLocation

348996770

4

68

17.2 Indexes with no user activity

<= /p>
·      =    This query shows the indexes which have no user sc= ans, user seeks or user lookups

·      =    This query also lists the index name and index typ= e

·         Where table DBA.dbo.tbl_unused_indexes is a= daily snapshot of the collected data.

--

-- Run this in the context of the monitored database

-- results where there were no seeks, scans or lookups<= o:p>

--

select object_nam= e(uu.[object= _id])as ObjectN= ame,si.name,si.type_de= sc,uu.[object= _id],uu.index_i= d, count<= span style=3D'color:gray'>(*) as HowM= any

from DBA.db= o.tbl_unused_indexes uu join sys.indexes si on

uu.[object_id] =3D si.[object_id] and uu.index_id =3D si.index_id

where uu.use= r_seeks+uu.user_sc= ans+uu.user_lo= okups=3D0

group by uu<= span style=3D'color:gray'>.[object_id],uu.index_id,s= i.name,si.type_desc

order by count(*)= desc,uu.[object_id];=

go

Sample results shown, the collection cycle is 71 days

ObjectName<= /p>

Index Name<= /p>

Index Type<= /p>

object_id

index_id

How Many Days

RegionalSales

Index123

NONCLUSTERED

74235815

3

71

RegionalSales

Index45

NONCLUSTERED

74235815

4

71

RegionalSales

Index21

NONCLUSTERED

74235815

5

71

Emloyees

Idx_Employees_Pets

NONCLUSTERED

74235815

7

71

Publishers

Index8

NONCLUSTERED

74235815

20

71

Publishers

UK_Publishers_hlp_idx

NONCLUSTERED

138756593

2

71

Authors

Idx_Authors_CarReg

NONCLUSTERED

288472552

9

70

Authors

Idx_Authors_CarType

NONCLUSTERED

288472552

10

70

ApplicationName

Index66

NONCLUSTERED

291037064

2

69

18.0 Simple index analys= is in more depth

·      =    This is an example of how you may wish to further diagnose index usage

·      =    This assumes that as well as the nightly collection defined in 6.0 and 14.1 an additio= nal data collection job has been defined which collects aggregated index data e= very hour.

·      =    Realise that collection of hourly data will genera= te large data sets

18.1 Table to collect hou= rly basic index stats ( also defined in section 14 )

--

-- create table in DBA database

--

CREATE TABLE = dbo.tbl_IndexActivity

(

            TableName sysname NOT NULL,

            IndexName sysname NOT NULL,

            IndexType varchar(4) NOT NULL,

            Reads bigint NOT NULL,

            Writes bigint NOT NULL,

            TheDate datetime NOT NULL CONSTRAINT DF_IndexActivity_TheDate DEFAULT (getdate= ())

);

GO

            =

<= /b>

18.2 create hourly job executing this script

--<= /o:p>

-- Run this in the context of the database to be analys= ed

--

insert into D= BA.dbo.tbl_In= dexActivity(TableName,= IndexName,IndexType,= Reads,Writes)

select object_nam= e(s.object_id) as TableName, isnull(i= .name,'HEAP') as IndexName,

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType

,reads=3Duser_seeks + user_scans + user_lookups

,writes =3D user_updates

from sys.dm_db_in= dex_usage_stats s join sys.= indexes i

on s.object_id =3D i.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

and s.data= base_id =3D db_id= ()

order by rea= ds desc;

go

18.3 Extract index data where indexes only u= sed once in daily monitoring period

·      =    The monitoring period for these examples was 71 da= ys

·      =    We’re interested in indexes which were unused for = 70 days

·      =    Used once

·      =    Find index details

--

-- run this in the context of the monitored database

--

select object_nam= e(uu.[object= _id])as ObjectN= ame,si.name,si.type_de= sc,uu.[object= _id],uu.index_i= d, count<= span style=3D'color:gray'>(*) as HowM= any

from DBA.db= o.tbl_unused_indexes uu join sys.indexes si on

uu.[object_id] =3D si.[object_id] and uu.index_id =3D si.index_id

where uu.use= r_seeks+uu.user_sc= ans+uu.user_lo= okups=3D0

group by uu<= span style=3D'color:gray'>.[object_id],uu.index_id,s= i.name,si.type_desc

having count(*) =3D 70 =

order by uu<= span style=3D'color:gray'>.[object_id];=

go

<= /b>

Sample results=

<= /b>

Object Name

Index name

Index type

object_id

Index id

How Many

Staff

idx_2

NONCLUSTERED

397712965

3

70

Customer<= /span>

idx_4

NONCLUSTERED

472805192

5

70

Customer<= /span>

idx_5

NONCLUSTERED

472805192

6

70

Customer<= /span>

idx_7

NONCLUSTERED

472805192

8

70

Customer<= /span>

idx_9

NONCLUSTERED

472805192

25

70

ReactionLog

idx_1

NONCLUSTERED

755286246

2

70

UKRetail<= /span>

UK_Retail_idx_9=

NONCLUSTERED

852939602

2

70

UKRetailLog

UK_RetailLog_idx_3

NONCLUSTERED

1022183683

2

70

RetailJob=

idx_2

NONCLUSTERED

1759097803

3

70

·      =    Using the information presented we can now examine= the data collected for the index within the table dbo.tbl_index_activity the hourly aggregated data

·      =    Sadly because data within the dmv’s is not always maintained we have to manually inspect the data – or write some very complex queries!!

18.4 List indexes and and= data by index and date order for an individual table

--

-- display summary read/write figures for indexes for a given table

-- run in the database being monitored

--

select * from dba.db= o.tbl_indexactivity

where tableName =3D= 'SalesEntry'

order by ind= exName,thedate;

go

Sample output - there would be typically 744 rows for each index for each monitored month

Table Name

Index Name

Index Type

Reads

Writes

The Date

SalesEntry

idx_12

NC

0

504389

2007-08-17 14:02:00.= 917

SalesEntry

idx_12

NC

0

504549

2007-08-17 15:02:00.= 883

SalesEntry

idx_12

NC

0

504640

2007-08-17 16:02:00.= 350

SalesEntry

idx_12

NC

0

508696

2007-08-17 17:02:00.= 147

SalesEntry

idx_15

NC

79379

7689884

2007-07-17 09:25:29.= 717

SalesEntry

idx_15

NC

79406

7699110

2007-07-17 10:02:00.= 397

SalesEntry

idx_15

NC

79434

7716152

2007-07-17 11:02:00.= 733

SalesEntry

idx_15

NC

79458

7731601

2007-07-17 12:02:00.= 740

18.5 Detailed information= for a single index

--

-- display detailed figures for a single indexe for a g= iven table

--

select * from dba.db= o.tbl_indexactivity

where tablename =3D= ‘SalesEntry'

and IndexName =3D= 'Idx_ SalesEntry_3'

order by ind= exname,thedate;

go

Subset of results showing= aggregation of values in sys.dm_db_index_usage_stats

Table Name

Index Name

Index Type

Reads

Writes

The Date

SalesEntry

Idx_ SalesEntry_3

NC

24

7392180

2007-07-17 09:25:29.= 717

SalesEntry

Idx_ SalesEntry_3

NC

24

7392420

2007-07-17 10:02:00.= 397

SalesEntry

Idx_ SalesEntry_3

NC

24

12654939

2007-08-08 13:02:00.= 247

SalesEntry

Idx_ SalesEntry_3

NC

24

12656868

2007-08-08 14:02:00.= 500

SalesEntry

Idx_ SalesEntry_3

NC

0

85

2007-08-08 16:02:02.= 087

SalesEntry

Idx_ SalesEntry_3

NC

0

66144

2007-08-08 17:02:00.= 180

SalesEntry

Idx_ SalesEntry_3

NC

0

1237899

2007-08-13 21:02:00.= 220

SalesEntry

Idx_ SalesEntry_3

NC

0

23372

2007-08-13 21:02:00.= 220

SalesEntry

Idx_ SalesEntry_3

NC

0

982012

2007-08-17 17:02:00.= 147

<= /p>
·      =    Note the three sets of data returned

·      =    This shows one month of data; the actual index activity will be the sum of the three sets of figures;

·      =    Reads =3D ( 24 – 24 ) + ( 0 – 0 ) + ( 0 – 0 ) =3D = 0

·      =    Writes =3D ( 12656868– 7392180 ) + 1237899+ 982012= =3D 7,484,599

·      =    In the next section we will consider a more detail= ed analysis of the collected data, however for this index, in the period of one month we have recorded 0 read operations.

·      =    The number of writes will relate to the number of table inserts ( in this case ) and this value is of little interest from a performance point of view other than it would appear this is a busy table a= nd this index should be removed.

NOTE: All following quer= ies and analysis will assume no breaks in data collection=

19.0 NOTES about seeks scans and lookups

·      =    If we consider the values for scans, seeks and lookups we can use the = collected data to give a likely indication of index usefulness

·      =    For the clustered index a high level of scans is likely to be an indication of inadequate secondary indexes, this should be = set against the size of the table as a scan of a 10 row table is probably fine whereas a scan of a 100 million row table is not.

·      =    High levels of lookups may indicate inadequate secondary indexes, however this should be compared to the typical queries t= he application constructs. If your application exclusively executes select * statements then every select will likely be a scan or a lookup; again we’re= typically considering large tables here.

·      =    Seeks are generally good, BUT, beware of generalis= ing when viewing the results.

19.1 Query to show indexes without read activity over the monitored period

·&nb= sp;        In this instance the aggregated data has been collected hourly for nearly two months

·&nb= sp;        As mentioned previously I didn’t find sys.dm_db_index_usage_stats consistent i= n the data held, I did ask the SQLCAT team but they were unable to offer any explanation and my own tests to try to reproduce this in a test environment have come to nothing.

·&nb= sp;        You might want to run this query to check how many index stats were collected each hour, for my test period the number ranged from 623 to 1,749

o    My test peri= ods covered 128 days collecting data every hour.

19.1.1 How many index stats for each hour peri= od

--

-- see how many stats we collected

--

select convert(char(11),thedat= e),datepart(hh,thedate= ),count<= span style=3D'color:gray'>(*)

from dbo.tb= l_indexactivity

group by convert(= char(11),thedate),= datepart(hh,thedate)

order by convert(char(11),thedat= e),datepart(hh,thedate= );

19.1.2 index performance order by zero reads

--

-- analyse collected data for period<= /p>
-- showing indexes without reads

--

select start.tablename,= start.indexname,= endd.reads-star= t.reads as r= eads,endd.write= s-start.writ= es as writes

from dbo.tb= l_indexactivity start join dbo.tbl_indexactivity endd on

(start.t= ablename=3Dendd.tab= lename and start.i= ndexname=3Dendd.ind= exname)

where convert(char(11),start<= span style=3D'color:gray'>.thedate)=3D 'aug 20 2007' and datepart(hh,start.thed= ate)=3D8

and convert(char(11),endd.thedate)=3D 'oct 16 2007' and datepart(hh,endd.theda= te)=3D8

order by endd.reads-start.read= s asc;

·&nb= sp;        Substitute relevant dates and times, the query chooses the data sets collect= ed at 08:00 each day.

·&nb= sp;        You can cheat the date conversions by limiting the= date to 11 characters.

Sample results

<= /b>

tablename

indexname

reads

writes

RetailSales

PK_RetailSales<= /o:p>

0

526

UKStores<= /span>

PK_UKStores

0

30351

PurchaseHeader<= /o:p>

Idx_4

0

157266

RetailTransaction

Idx_8

0

4219382

RetailTransaction

Idx_5

0

11891821

ExpenseEntry

Idx_4

0

43290

OrderingLines

Idx_5

0

3467000

RetailLines

PK_RetailLines<= /o:p>

0

83

<= /b>

·&nb= sp;        This more detailed analysis backs up the data collected earlier in this series of documents.

·&nb= sp;        Removing indexes from a database is a task fraught with risk and one that is likely to evoke intense opposition when presented= to a change board, this is why this series of examples explores different ways= to analyse the dynamic management views, ways to collect the data and ways to determine index usefulness.

·&nb= sp;        In the example above, which is real data, albeit w= ith identities hidden, the table RetailTransaction has registered 16 million us= er writes ( io ) on two unused indexes in the monitored period

19.1.3 number of user writes where indexes are= not used

--

-- how many user writes where no reads

--

select sum(endd= .writes-sta= rt.writes) as writes

from dbo.tb= l_indexactivity start join dbo.tbl_indexactivity endd on

(start.t= ablename=3Dendd.tab= lename and start.i= ndexname=3Dendd.ind= exname)

where convert(char(11),start<= span style=3D'color:gray'>.thedate)=3D 'aug 20 2007' and datepart(hh,start.thed= ate)=3D8

and convert(char(11),endd.thedate)=3D 'oct 16 2007' and datepart(hh,endd.theda= te)=3D8

and endd.reads-star= t.reads=3D0<= span style=3D'color:gray'>;

The result= on my sample of indexes gave 172,477,495 writes over a 57 day period

Now in rou= gh terms if this application is used 5 days x 10 hours this equates to 420,000 extra disk writes an hour

ü&nb= sp; Unnecessary = indexes can impose a considerable io burden on your database server.

20     Finding little used indexes

·&nb= sp;        It’s a fact of life that databases can end up with many indexes, I’ve found examples where index tuning had been attempted by indexing every single column which looked as if it might be used in a query= or a join.

·&nb= sp;        This approach generally does not produce effective indexes, but it does produce lots of indexes.

·&nb= sp;        Single column indexes are often used for bookmark lookups, if the clustered index has a very high level of lookups this may be indicative of this very fact.

·&nb= sp;        As mentioned before, removing indexes is not a sim= ple task and one which is unlikely to gain much backing as the direct benefits = are difficult to show, unlike say, adding an index which takes a query from 20 minutes to 4 seconds.

·&nb= sp;        So taking the data returned in 18.3 , indexes only used on one day out of 71 we can query the hourly data collected in table tbl_index_usage_stats and investigate which indexes saw little use and when it was, with the caveat that the index may not have been in the views at all for one or more days.

·&nb= sp;        As I noted right at the start there appears to be = no guarantee that an unused index actually has it’s statistics stored within t= he views, so indexes without reads will not be stored unless they have updates= or inserts applied, which sort of makes sense except you might imagine that rebuilding the index would force it into the views.

·&nb= sp;

·&nb= sp;        However, just to muddy the waters further, indexes= on static tables which are not used will not appear in the views either unless enforced index rebuilds or statistic updates are applied<= /p>

20.1   Actions that put the index details int= o the dynamic management views

sys.dm_db_index_usage_stats

sys.dm_db_index_operational_stats

Update statistics tablenam= e

Not always, may ignore heaps

Not always, may ignore heaps

Alter index ALL

Not always and not non clustered indexes but includes heaps

Not always

sp_updatestats

no

Tables only ( heap / clustered index )

sp_updatestats resample

No

ditto

Shrink database

no

mostly

Ø&nb= sp; Da= tabase offline clears all entries from dm= vs

Ø&nb= sp; Ch= ange database to read only clears dmvs

·&nb= sp;        What I was not able to do was correlate the data within the index dmvs against actual table changes, although I can do this = in a test environment this is never going to be the same as a full blown product= ion system.

·&nb= sp;        A test showed that the actual heap, index 0, was ignored although secondary indexes declared upon the Heap were registered in the dmvs.

Having established the basic rules it seems fair to conclude that any indexes with an ID other than 0 wh= ich exist within sys.indexes but do not appear within the dmvs in the data collection over the 128 days of hourly data collections are truly unused and can be safely removed from the database.

20.2 indexes not recorded in data collection

--<= /o:p>

-- run this query in the monitored database<= /span>

--

select * from sys.indexes<= o:p>

where convert(varchar(12),[objec= t_id])+'@@'+convert= (varchar(4),index_i= d)

not in( select convert<= /span>(varchar(12),[objec= t_id])+'@@'+convert= (varchar(4),index_i= d)

from dba.db= o.tbl_Unused_Indexes)

and objectprop= erty([object_id],'IsUserTable') =3D 1 and i= ndex_id>0;<= /span>

go

Table name	Index name	type_desc	Row Locks	Page Locks
queue_messages_19770= 58079	queue_clustered_inde= x	CLUSTERED=	Yes	No=
queue_messages_19770= 58079	queue_secondary_inde= x	NONCLUSTERED	Yes	No=
queue_messages_20090= 58193	queue_clustered_inde= x	CLUSTERED=	Yes	No=
queue_messages_20090= 58193	queue_secondary_inde= x	NONCLUSTERED	Yes	No=
queue_messages_20410= 58307	queue_clustered_inde= x	CLUSTERED=	Yes	No=
queue_messages_20410= 58307	queue_secondary_inde= x	NONCLUSTERED	Yes	No=

Table Name	Index Name	Total Pages	Used Pages	Data Pages	Total Space (MB)	Used Space (MB)	Data Space (MB)
LogData	PK_LogData	5532	5497	5000	43	42	39
LogData	idx_logdata_numkey	17	14	12	0	0	0

Table Name	Index id	index_type_desc	pages
LogData	1	CLUSTERED INDEX=	5450
LogData	2	NONCLUSTERED INDEX	12

ObjectName	object_id	index_id	HowMany
RegionalSales	74235815	3	71
RegionalSales	74235815	4	71
RegionalSales	74235815	5	71
Emloyees<= /span>	138756593	2	71
Publishers	288472552	9	71
Publishers	288472552	10	71
Authors	291037064	2	70
Authors	291037064	6	70
ApplicationName=	320472666	2	69
StoreLocation	348996770	3	68
StoreLocation	348996770	4	68

ObjectName<= /p>	Index Name<= /p>	Index Type<= /p>	object_id	index_id	How Many Days
RegionalSales	Index123	NONCLUSTERED	74235815	3	71
RegionalSales	Index45	NONCLUSTERED	74235815	4	71
RegionalSales	Index21	NONCLUSTERED	74235815	5	71
Emloyees	Idx_Employees_Pets	NONCLUSTERED	74235815	7	71
Publishers	Index8	NONCLUSTERED	74235815	20	71
Publishers	UK_Publishers_hlp_idx	NONCLUSTERED	138756593	2	71
Authors	Idx_Authors_CarReg	NONCLUSTERED	288472552	9	70
Authors	Idx_Authors_CarType	NONCLUSTERED	288472552	10	70
ApplicationName	Index66	NONCLUSTERED	291037064	2	69

Object Name	Index name	Index type	object_id	Index id	How Many
Staff	idx_2	NONCLUSTERED	397712965	3	70
Customer<= /span>	idx_4	NONCLUSTERED	472805192	5	70
Customer<= /span>	idx_5	NONCLUSTERED	472805192	6	70
Customer<= /span>	idx_7	NONCLUSTERED	472805192	8	70
Customer<= /span>	idx_9	NONCLUSTERED	472805192	25	70
ReactionLog	idx_1	NONCLUSTERED	755286246	2	70
UKRetail<= /span>	UK_Retail_idx_9=	NONCLUSTERED	852939602	2	70
UKRetailLog	UK_RetailLog_idx_3	NONCLUSTERED	1022183683	2	70
RetailJob=	idx_2	NONCLUSTERED	1759097803	3	70

Table Name	Index Name	Index Type	Reads	Writes	The Date
SalesEntry	idx_12	NC	0	504389	2007-08-17 14:02:00.= 917
SalesEntry	idx_12	NC	0	504549	2007-08-17 15:02:00.= 883
SalesEntry	idx_12	NC	0	504640	2007-08-17 16:02:00.= 350
SalesEntry	idx_12	NC	0	508696	2007-08-17 17:02:00.= 147
SalesEntry	idx_15	NC	79379	7689884	2007-07-17 09:25:29.= 717
SalesEntry	idx_15	NC	79406	7699110	2007-07-17 10:02:00.= 397
SalesEntry	idx_15	NC	79434	7716152	2007-07-17 11:02:00.= 733
SalesEntry	idx_15	NC	79458	7731601	2007-07-17 12:02:00.= 740

tablename	indexname	reads	writes
RetailSales	PK_RetailSales<= /o:p>	0	526
UKStores<= /span>	PK_UKStores	0	30351
PurchaseHeader<= /o:p>	Idx_4	0	157266
RetailTransaction	Idx_8	0	4219382
RetailTransaction	Idx_5	0	11891821
ExpenseEntry	Idx_4	0	43290
OrderingLines	Idx_5	0	3467000
RetailLines	PK_RetailLines<= /o:p>	0	83

	sys.dm_db_index_usage_stats	sys.dm_db_index_operational_stats
Update statistics tablenam= e	Not always, may ignore heaps	Not always, may ignore heaps
Alter index ALL	Not always and not non clustered indexes but includes heaps	Not always
sp_updatestats	no	Tables only ( heap / clustered index )
sp_updatestats resample	No	ditto
Shrink database	no	mostly