Analysis of Guide Plans for table UK Retail$Item

Analysing Indexes Part 2

· = I’m still only working with basic indexing <= o:p>

· = No partitioning

· = I’m not looking at xml indexes.

· = I’m using a local database to store data cal= led DBA

14.0 Collecting Raw Index usage data<= /p>

<= /p>

· = Collects table name, index name , reads and writes= where there is a value for reads or writes

· = Collect daily / hourly as required

· = Figures stored in dmv’s are cumulative so we= have to store snapshots and query for weekly/daily/hourly differences to extract usage figures.

14.1 Table to store da= ta – create in DBA / Monitoring database

--<= /o:p>

-- create in DBA database

Use DBA;

IF <= /span>EXISTS (SELECT * FROM sys.objects<= /span> WHERE objectpro= perty(OBJECT_ID(N'[dbo].[tbl_Index= Activity]'),'IsUserTable') =3D 1)

DROP TABLE = [dbo].[tbl_IndexActivity];

create table = dbo.tbl_IndexActivity

(

TableName sysname not<= /span> null,

IndexName sysname not<= /span> null,

IndexType varchar(4) not null,

Reads bigint not null,

Writes bigint not null,

TheDate datetime constraint DF_IndexActivity_TheD= ate default getdate= () not null

);

= ; &n= bsp;

14.2 Run this query in= a scheduled job

<= /p>

· = I don’t actually create a stored procedure h= ere as I would have to create it within the monitored database, something I mig= ht not want to do.

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

-- every night for daily stats

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;

Typical data collected

Table Name	Index Name	Index Type	Reads	Writes	The Date
Stores	PK_Stores=	CLUS	1819868	1833391	2007-08-18 20:02:01.= 020
SiteLocation	PK_ SiteLocation	CLUS	1235222	1238951	2007-08-18 20:02:01.= 020
Employee<= /span>	PK_ Employee	CLUS	1146006	1147732	2007-08-18 20:02:01.= 020
DiscountLines	PK_ DiscountLines	CLUS	436612	439336	2007-08-18 20:02:01.= 020
PostedItems	PK_ PostedItems=	CLUS	401824	594833	2007-08-18 20:02:01.= 020
SalesEntry	PK_SalesEntry	CLUS	401386	103919	2007-08-18 20:02:01.= 020
Transaction	UK_Transaction<= /o:p>	CLUS	374314	280853	2007-08-18 20:02:01.= 020
OutgoingMessages	PK_OutgoingMessages<= o:p>	CLUS	363475	13959	2007-08-18 20:02:01.= 020

· = This will create lots of data as it will create a = row for each index which has been accessed

· = Remember that dmv’s contain cumulative data = so we may find some indexes have not been used recently.

· = Once we have sets of data we can view the differen= ce to see what’s happening

· = Although I’ve suggested this collection to r= un daily the interval could be any suitable value down to a couple of minutes = for real time stats.

· = I’m using this basic data collection to deci= de which indexes I want to analyse in greater detail.

· = This is probably the most basic data collection

14.3 Most Writes for an hour period

<= /p>

Example assumes collection of data hourly
Modify the SARGs for ia1.= thedate and ia2.thedate to return different = data sets.
You may wish to to collect top 25 results in each category and st= ore in another table
This data would make a good OLAP cube

-- Example query to extract index usage

-- Results for the hour 10:00 to 11:00 today=

-- Highest Writes

select ia1.tablename,ia1.indexname,= ia2.reads-ia1<= span style=3D'color:gray'>.reads as R= eads,ia2.writes= -ia1.writes= as Writes

from DBA.db= o.tbl_indexactivity ia1 join dbo.tbl_indexactivity ia2 on

ia1.tablename =3D ia2.tablename and ia1.indexname=3Dia2.indexname

where datepart(hh,ia1.thedate) <= span style=3D'color:gray'>=3D 10 = and convert(char(8),ia1.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and <= /span>datepart(hh,ia2.thedat= e) =3D 11 and convert(char(8),ia2.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and ( (ia2.reads<= span style=3D'color:gray'>-ia1.reads<= span style=3D'color:gray'>) + (ia2.writes= -ia1.writes= ) ) >0

order by (ia2.writes= -ia1.writes= ) desc;

= Sample results

tablename	indexname	Reads	Writes
UKRetail<= /span>	PK_UKRetail	153575	188427
RetailStores	PK_RetailStores=	42112	57345
SpecialOffers	PK_SpecialOffers	49716	50318
StoreMovements<= /o:p>	Idx_7	0	41580
StoreMovements<= /o:p>	Idx_8	0	41580

14.4 Most Reads for an hour period

<= /p>

Assumes collection of data hourly

--<= /o:p>

-- Example query to extract index usage

-- Results for the hour 10:00 to 11:00 today=

-- Highest Reads

select ia1.tablename,ia1.indexname,= ia2.reads-ia1<= span style=3D'color:gray'>.reads as R= eads,ia2.writes= -ia1.writes= as Writes

from DBA.db= o.tbl_indexactivity ia1 join dbo.tbl_indexactivity ia2 on

ia1.tablename =3D ia2.tablename and ia1.indexname=3Dia2.indexname

where datepart(hh,ia1.thedate) <= span style=3D'color:gray'>=3D 10 = and convert(char(8),ia1.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and <= /span>datepart(hh,ia2.thedat= e) =3D 11 and convert(char(8),ia2.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and ( (ia2.reads<= span style=3D'color:gray'>-ia1.reads<= span style=3D'color:gray'>) + (ia2.writes= -ia1.writes= ) ) >0

order by (ia2.reads<= span style=3D'color:gray'>-ia1.reads<= span style=3D'color:gray'>) desc;

= sample results&nbs= p; =

tablename	indexname	Reads	Writes
RetailItem	UK RetailItem	264492	4
RetailItem	Idx_22	257247	0
DefaultDimension	PK_DefaultDimension<= o:p>	212268	11
UKRetail<= /span>	PK_UKRetail	153575	188427
TimeAnalysis	UK_TimeAnalysis_idx<= o:p>	76818	38608

14.5 Most Reads and Wr= ites for an hour period

<= /p>

· = Assumes collection of data hourly

--<= /o:p>

-- Example query to extract index usage

-- Results for the hour 10:00 to 11:00 today=

-- Highest Reads and Writes

select ia1.tablename,ia1.indexn= ame,ia2.reads<= span style=3D'color:gray'>-ia1.reads = as Reads,ia= 2.writes-ia1= .writes as = Writes

from DBA.db= o.tbl_indexactivity ia1 join dbo.tbl_indexactivity ia2 on

ia1.tablename =3D ia2.tablename and ia1.indexname=3Dia2.indexname

where datepart(hh,ia1.thedate) <= span style=3D'color:gray'>=3D 10 = and convert(char(8),ia1.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and <= /span>datepart(hh,ia2.thedat= e) =3D 11 and convert(char(8),ia2.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and ( (ia2.reads<= span style=3D'color:gray'>-ia1.reads<= span style=3D'color:gray'>) + (ia2.writes= -ia1.writes= ) ) >0

order by ((ia2.reads= -ia1.reads<= span style=3D'color:gray'>)+(ia2.writ= es-ia1.writes= )) desc;

= sample results&nbs= p; =

= ; &n= bsp;

tablename	indexname	Reads	Writes
UKRetail	PK_UKRetail	153575	188427
RetailItem	UK RetailItem	264492	4
RetailItem	Idx_22	257247	0
DefaultDimension	PK_DefaultDimension	212268	11
TimeAnalysis	UK_TimeAnalysis_idx	76818=	38608=
SpecialOffers	PK_SpecialOffers	49716=	50318=

14.6 Most Reads and Wr= ites showing %age proportion for an hour period

Assumes collection of data hourly

--<= /o:p>

-- Example query to extract index usage

-- Results for the hour 10:00 to 11:00 today=

-- Highest Reads and Writes with proportion shown<= /o:p>

select ia1.tablename,ia1.indexname,= ia2.reads-ia1<= span style=3D'color:gray'>.reads as R= eads,ia2.writes= -ia1.writes= as Writes

, convert(int, (ia2.reads-ia1<= span style=3D'color:gray'>.reads)*1.0= /((ia2.read= s-ia1.reads<= span style=3D'color:gray'>)+(ia2.writ= es-ia1.writes= ))*100.0) <= span style=3D'color:blue'>as 'Read%'

, convert(int, (ia2.writes-ia1= .writes)*1.= 0/((ia2.read= s-ia1.reads<= span style=3D'color:gray'>)+(ia2.writ= es-ia1.writes= ))*100.0) <= span style=3D'color:blue'>as 'Write%'

from DBA.db= o.tbl_indexactivity ia1 join dbo.tbl_indexactivity ia2 on

ia1.tablename =3D ia2.tablename and ia1.indexname=3Dia2.indexname

where datepart(hh,ia1.thedate) <= span style=3D'color:gray'>=3D 10 = and convert(char(8),ia1.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and <= /span>datepart(hh,ia2.thedat= e) =3D 11 and convert(char(8),ia2.thedate,11= 2) =3D convert(= char(8),getdate(),112)=

and ( (ia2.reads<= span style=3D'color:gray'>-ia1.reads<= span style=3D'color:gray'>) + (ia2.writes= -ia1.writes= ) ) >100

order by ((ia2.reads= -ia1.reads<= span style=3D'color:gray'>)+(ia2.writ= es-ia1.writes= )) desc;

= Sample results&nbs= p; =

tablename	indexname	Reads	Writes	Read%	Write%
UKRetail<= /span>	PK_UKRetail	153575	188427	44	55
RetailItem	UK RetailItem	264492	4	99	0
RetailItem	Idx_22	257247	0	100	0
DefaultDimension	PK_DefaultDimension<= o:p>	212268	11	99	0
TimeAnalysis	UK_TimeAnalysis_idx<= o:p>	76818	38608	66	33
SpecialOffers	PK_SpecialOffers	49716	50318	49	50

· = So all this allows us to view index usage at the granularity of data collection.

· = My target Database data collection grows at 4,700 = rows per hour, that’s about 3.4 million rows per month

· = This data will allow us to collect more detailed information from the dmv’s to fully analyse index usage

· = I’d probably suggest selecting the top 100 i= ndexes for each category for a 7 day period and using this to extract more detailed statistics.

15.0 So Just how many indexes do I have ?

-- How many indexes ?

-- run in contect of database to whose stats are to be counted

select type_desc,count(*)= from sys.indexes<= o:p>

where objectprop= erty(OBJECT_ID,'IsUserTable') =3D 1

group by type_desc;

type_desc	(No column name)
CLUSTERED=	2936
HEAP	17
NONCLUSTERED	4067

15.1 How many empty tables do I have ?&nb= sp;

· &n= bsp; Many third party applications have unused sections which may result in many empty tables

· &n= bsp; Navision provides functionality we do not use so having a definitive list of unused tables will help provide a filter to use= in subsequent analysis

· &n= bsp; This method uses sysindexes, the SQL 2000 table

15.2 = Create this= table in the DBA database

USE [DBA];

IF <= /span>EXISTS (SELECT * FROM sys.objects<= /span> WHERE objectpro= perty(OBJECT_ID(N'[dbo].[tbl_Empty= Tables]'),'IsUserTable') =3D 1)

DROP TABLE = [dbo].[tbl_EmptyTables]= ;

create table = dbo.tbl_EmptyTables

(

TableName sysname not<= /span> null,

[Object_id] bigint not null,

IndexName sysname not<= /span> null,

Row_Count bigint not null

);

15.3 Run this script in the context of the database to be analysed

<= /o:p>

· &n= bsp; Extracts by clustered index and by HEAP=

--<= /o:p>

-- run in database to be analysed

insert into D= BA.dbo.tbl_Em= ptyTables(TableName,= [Object_id],IndexName,= Row_Count)

select object_nam= e(id),id,name,rows from dbo.sy= sindexes

where rows <= span style=3D'color:gray'>=3D 0 and i= ndid =3D 1

and objectprop= erty(ID,'IsUserTable') =3D 1;<= /span>

insert into D= BA.dbo.tbl_Em= ptyTables(TableName,= [Object_id],IndexName,= Row_Count)

select object_nam= e(id),id,isnull<= span style=3D'color:gray'>(name,'HEAP'),rows from dbo.sy= sindexes

where rows <= span style=3D'color:gray'>=3D 0 and i= ndid =3D 0

and objectprop= erty(ID,'IsUserTable') =3D 1;<= /span>

15.4 Add this unique index to check there are no duplicate entries

use DBA;

create unique= index uk_tbl_EmptyTables_Object_ID on dbo.tbl_= EmptyTables([object_id]);

go=

Sample result set=

Table Name	Object id	Index Name	Row Count
PriceType=	67832	PK_ PriceType	0
DistributionFilter	119887	PK_ DistributionFilt= er	0
POSGroupHeader<= /o:p>	1187550	PK_ POSGroupHeader	0
Complaints	1656045	HEAP	0
PharmacyTask	2163849	PK_ PharmacyTask	0
ActivityCenter<= /o:p>	2567543	HEAP	0

15.5 Use sys.partitions to list tables with zero rows

· &n= bsp; This query returns the same results but is probabl= y a better method to employ.

· &n= bsp; Leave out the having clause to produce a row count= for all tables in a database

-- count zero rows in tables

select obj.na= me ,sum(par.rows) as rows from <= span style=3D'color:green'>sys.partitions par with (nolock)

join sys.objects<= /span> obj with (nolock) on par.[obj= ect_id] =3D obj.[ob= ject_id]

where par.in= dex_id in (0= ,1)

and obj.ty= pe =3D 'U'

group by obj= .name

having sum(par<= span style=3D'color:gray'>.rows)=3D 0= ;

Sample result set

name	rows
BizTalk Suspended Qu= eue	0<= /p>
BizTalk Technical Notification	0<= /p>
dtproperties	0<= /p>
IMPSales<= /span>	0<= /p>
msVisualBasicGlobals=	0<= /p>
Object Comparison Bu= ffer	0<= /p>

16.0 How many statisti= cs?

· = Now the viewing of statistics is somewhat differen= t in SQL 2005

· = We don’t want to see stats on indexes

· = You can also keep a watch on how many “auto stats” your database ha= s with this query

· = I generally consider high numbers of auto stats be= ing indicative of inadequate indexing.

· = I generally will periodically delete all system st= ats from a database

· = If there were stats and you create an index the st= ats remain

· = The stats may have come from who knows when and may not be relevant any more

· = As always be careful, your database may take a temporary performance hit if it’s running on auto stats

16.1 List all statisti= cs

-- show statistics in the database

select object_nam= e(s.object_id) as Table_Name,* from sys.stats s

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

and s.auto= _created | s.u= ser_created =3D 1;<= /o:p>

ü = The symbol in the AND statement ( | ) is SHIFT + Backslash ( left of Z on my keyboard )

sample results

Table name	Object id	name	Stats id	Auto created	User created	No recompute
Employees=	119887	sts_Employees1<= /o:p>	2	0	1	0
Employees=	119887	sts_Employees2<= /o:p>	3	0	1	0
Sales	1187550	_WA_Sys_00000002_08B= 54D69	2	1	0	0
RegionSouth	1656045	sts_RegionSouth_Pole=	3	0	1	0
RegionNorth	1656045	_WA_Sys_Rows_75D7831= F	4	1	0	0

System generated statistics always start _WA_Sys_
There a= re also hypothetical indexes which you sh= ould never see, these start hind_%

16.2 How many statistics do I have ?

-- How many statistics ?

-- run in contect of database to whose stats are to be counted

select

case convert(char(1),auto_cr= eated)+convert(char(1),user_cr= eated)

when '10' then 'Auto Created'=

when '01' then 'User Created'=

else 'Both'

end as Sta= tsType,

count(*) as HowMany from sys.stats s

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

and s.auto= _created | s.u= ser_created =3D 1

group by convert(= char(1),auto_created)+convert(= char(1),user_created);

StatsType	HowMany
User Created	7786
Auto Created	121

16.3 View to display Statistics Information

<= /p>

· = It used to be quite simple to view statistics , as against indexes in SQL 2000

· = Not so easy in SQL 2005

· = This view which should be created in the user data= base will show basic information on statistics

-- view to provide information on statistics only<= /o:p>

-- filters out index stats and system objects

-- per database create in database

IF <= /span>EXISTS (SELECT * FROM sys.views WHERE object_id= =3D OBJECT_ID(N'[dbo].[dm_db_sta= tistics]'))

DROP VIEW [= dbo].[dm_db_statistics];

create view d= bo.dm_db_statistics

select object_nam= e(s.[object_= id]) as TableN= ame,c.name as ColumnName,s.name as St= atName,s.auto_cre= ated,s.user_cre= ated,s.no_recom= pute,s.[object_= id],

s.stats_id,s= c.stats_column_id,<= /span>sc.column_id,= stats_date(s.[object_id], s.stats_id) = as LastUpdated

from sys.stats s join sys.st= ats_columns sc on sc.[object_id] =3D s.[object_id] and sc.stats_id =3D s.stats_id

join sys.columns<= /span> c on c.[object_id] =3D sc.[object_id] and c.column_id =3D sc.column_id

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

and s.auto= _created | s.u= ser_created =3D 1;<= /o:p>

Sample results= ( please ex= cuse formatting )

Table Name	Column Name	Stat Name	Auto created	User created	No recompute	Object id	Stats id	Stats column id	Column id	Last Updated
tblReindexingStrat	Freq of Online Reind= exing	_WA_Sys_Freq of Onli= ne Reindexing_164452B1	1<= /p>	0<= /p>	0<= /p>	373576369=	2<= /p>	1<= /p>	10=	2007-07-16 16:34:21.= 780
wt_current_sysperfin= fo	counter_name	_WA_Sys_counter_name= _173876EA	1<= /p>	0<= /p>	0<= /p>	389576426=	2<= /p>	1<= /p>	3<= /p>	2007-07-16 16:34:21.= 827
product_file	header	_WA_Sys_header_24927= 208	1<= /p>	0<= /p>	0<= /p>	613577224=	2<= /p>	1<= /p>	1<= /p>	2007-07-16 16:34:21.= 890
Trace20070504a<= /o:p>	TextData<= /span>	_WA_Sys_00000003_2D2= 7B809	1<= /p>	0<= /p>	0<= /p>	757577737=	2<= /p>	1<= /p>	3<= /p>	2007-07-10 14:15:07.= 863

When th= ere are many indexes and statistics the results set can be large and make anal= ysis difficult
The app= lication I support has over 15,000 indexes and statistics, many of these relate= to unpopulated tables.
Should = the system procedure sp_createstats ha= ve been run against the database then statistics will have been created on empty tables.
This ca= n be a useful procedure in as much as you can command it to create statistics= on columns of multi column or compound indexes
However= , great as this may sound, if you have a large number of covered indexes or precise compound indexes you might not actually benefit from these ext= ra statistics – and these will have to be maintained – so use this proc with care. http://msdn2.microsoft.com/en-us/library/ms186834.aspx

16.4 Display statistic= s on empty tables

-- find all stats on empty tables

select object_nam= e(s.[object_= id]) as TableN= ame,c.name as ColumnName,s.name as St= atName,s.auto_cre= ated,s.user_cre= ated,s.no_recom= pute,s.[object_= id],

s.stats_id,s= c.stats_column_id,<= /span>sc.column_id,= stats_date(s.[object_id], s.stats_id) = as LastUpdated , sum(par<= span style=3D'color:gray'>.rows) as [rows]

from sys.stats s join sys.st= ats_columns sc on sc.[object_id] =3D s.[object_id] and sc.stats_id =3D s.stats_id

join sys.columns<= /span> c on c.[object_id] =3D sc.[object_id] and c.column_id =3D sc.column_id

join sys.partitio= ns par with (nolock) on par.[obj= ect_id] =3D s.[obje= ct_id]

join sys.objects<= /span> obj with (nolock) on par.[obj= ect_id] =3D obj.[ob= ject_id]

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

and s.auto= _created | s.u= ser_created =3D 1

group by object_name(s.[object_id]),c.name ,s.name ,s.auto_created,s.user_created,s.no_recompute,s.[object_id],

s.stats_id,s= c.stats_column_id,<= /span>sc.column_id,= stats_date(s.[object_id], s.stats_id) =

having sum= (par.rows)=3D 0;=

<= /b>

Sample abridged result se= t

<= /b>

Table Name

Column

Name

Stat Name=

Auto c= reated

User c= reated

No rec= ompute

Last U= pdated

Rows

CouponPosting

Account_No_

_WA_Sys_00000009_4526633A

1<= /p>

0<= /p>

0<= /p>

NULL

0<= /p>

CouponPosting

AccountType

_WA_Sys_00000008_4526633A

1<= /p>

0<= /p>

0<= /p>

NULL

0<= /p>

PriceEntry

Code

Code

0<= /p>

1<= /p>

0<= /p>

NULL

0<= /p>

PriceEntry

Type

Type

0<= /p>

1<= /p>

0<= /p>

NULL

0<= /p>

ControlContacts

Entry No_

Entry No_

0<= /p>

1<= /p>

0<= /p>

NULL

0<= /p>

CreditControlLog

Contact Entry

Contact Entry

0<= /p>

1<= /p>

0<= /p>

NULL

0<= /p>

CreditControlLog

Currency Code

Currency Code

0<= /p>

1<= /p>

0<= /p>

NULL

0<= /p>

·      =     Note that updating stats on an empty table does no= t update the last updated date.

·      =     Basic information for a table ( or a view ) can be viewed using sp_autostats system procedure

·      =     Detailed information can be shown using dbcc show_statistics

reviewed November 2007

15.5 & 16.4 added

Table Name	Column Name	Stat Name=	Auto c= reated	User c= reated	No rec= ompute	Last U= pdated	Rows
CouponPosting	Account_No_	_WA_Sys_00000009_4526633A	1<= /p>	0<= /p>	0<= /p>	NULL	0<= /p>
CouponPosting	AccountType	_WA_Sys_00000008_4526633A	1<= /p>	0<= /p>	0<= /p>	NULL	0<= /p>
PriceEntry	Code	Code	0<= /p>	1<= /p>	0<= /p>	NULL	0<= /p>
PriceEntry	Type	Type	0<= /p>	1<= /p>	0<= /p>	NULL	0<= /p>
ControlContacts	Entry No_	Entry No_	0<= /p>	1<= /p>	0<= /p>	NULL	0<= /p>
CreditControlLog	Contact Entry	Contact Entry	0<= /p>	1<= /p>	0<= /p>	NULL	0<= /p>
CreditControlLog	Currency Code	Currency Code	0<= /p>	1<= /p>	0<= /p>	NULL	0<= /p>