Analysing Indexes Part 2
Here's the next instalment, expect there to at least another two or three.
- I’m still only working with basic indexing
- No partitioning
- I’m not looking at xml indexes.
14.0 Collecting Raw Index usage data
- Collects table name, index name , reads and writes where there is a value for reads or writes
- Collect daily / hourly or as required
- The greater the granularity the greater volume of data collected
- Values stored in the 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 data – create in a monitoring database.
- It’s best to create a dedicated database to collect data, most DBA’s have their own database
- For the examples here we’ll assume it’s called ServerAdmin
|
--
-- create in ServerAdmin database
--
Use ServerAdmin;
go
IF EXISTS (SELECT * FROM sys.objects WHERE objectproperty(OBJECT_ID(N'[dbo].[tbl_IndexActivity]'),'IsUserTable') = 1)
DROP TABLE [dbo].[tbl_IndexActivity];
--
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 constraint DF_IndexActivity_TheDate default getdate() not null
); |
14.2 Run this query in a scheduled job
- I don’t actually create a stored procedure here as I would have to create it within the monitored database, something I might not want to do.
|
--
-- Run this is the context of the database to be analysed
-- every night for daily stats
--
insert into ServerAdmin.dbo.tbl_IndexActivity(TableName,IndexName,IndexType,Reads,Writes)
select object_name(s.object_id) as TableName, isnull(i.name,'HEAP') as IndexName,
case i.index_id
when 0 then 'HEAP'
when 1 then 'CLUS'
else 'NC'
end as IndexType
,reads=user_seeks + user_scans + user_lookups
,writes = user_updates
from sys.dm_db_index_usage_stats s join sys.indexes i
on s.object_id = i.object_id and i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
and s.database_id = db_id()
order by reads desc;
go |
- 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 difference to see what’s happening
- Although I’ve suggested this collection to run 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 decide which indexes I want to analyse in greater detail.
14.3 Most Writes for an hour period
- 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 store 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.reads as Reads,ia2.writes-ia1.writes as Writes
from ServerAdmin.dbo.tbl_indexactivity ia1 join ServerAdmin.dbo.tbl_indexactivity ia2 on
ia1.tablename = ia2.tablename and ia1.indexname=ia2.indexname
where datepart(hh,ia1.thedate) = 10 and convert(char(8),ia1.thedate,112) = convert(char(8),getdate(),112)
and datepart(hh,ia2.thedate) = 11 and convert(char(8),ia2.thedate,112) = convert(char(8),getdate(),112)
and ( (ia2.reads-ia1.reads) + (ia2.writes-ia1.writes) ) >0
order by (ia2.writes-ia1.writes) desc;
go |
Abridged results
· Table names and index names are fictitious
|
tablename |
indexname |
Reads |
Writes |
|
TransportLinks |
PK_TransportLinks |
153575 |
188427 |
|
EuropeSites |
Idx_EuropeSites_Manager |
42112 |
57345 |
|
RegionalManager |
Uk_RegionalManager_Name |
49716 |
50318 |
|
LocalAreaSales |
Idx_LocalAreaSales_One |
0 |
41580 |
|
LocalAreaSales |
Idx_LocalAreaSales_Two |
0 |
41580 |
14.4 Most Reads for an hour period
- Assumes collection of data hourly
|
--
-- 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.reads as Reads,ia2.writes-ia1.writes as Writes
from ServerAdmin.dbo.tbl_indexactivity ia1 join ServerAdmin.dbo.tbl_indexactivity ia2 on
ia1.tablename = ia2.tablename and ia1.indexname=ia2.indexname
where datepart(hh,ia1.thedate) = 10 and convert(char(8),ia1.thedate,112) = convert(char(8),getdate(),112)
and datepart(hh,ia2.thedate) = 11 and convert(char(8),ia2.thedate,112) = convert(char(8),getdate(),112)
and ( (ia2.reads-ia1.reads) + (ia2.writes-ia1.writes) ) >0
order by (ia2.reads-ia1.reads) desc;
go
|
Abridged results
|
tablename |
indexname |
Reads |
Writes |
|
SalesItems |
Uk_SalesItems_StockNumber |
264492 |
4 |
|
SalesItems |
Idx_SalesItems_Reject |
257247 |
0 |
|
EmployeeGroup |
PK_EmployeeGroup |
212268 |
11 |
|
TransportLinks |
PK_TransportLinks |
153575 |
188427 |
|
RestrictedItems |
Idx_RestrictedItems_Analysis |
76818 |
38608 |
14.5 Most Reads and Writes for an hour period
|
--
-- Example query to extract index usage
-- Results for the hour 10:00 to 11:00 today
-- Highest Reads and Writes
--
select ia1.tablename,ia1.indexname,ia2.reads-ia1.reads as Reads,ia2.writes-ia1.writes as Writes
from ServerAdmin.dbo.tbl_indexactivity ia1 join ServerAdmin.dbo.tbl_indexactivity ia2 on
ia1.tablename = ia2.tablename and ia1.indexname=ia2.indexname
where datepart(hh,ia1.thedate) = 10 and convert(char(8),ia1.thedate,112) = convert(char(8),getdate(),112)
and datepart(hh,ia2.thedate) = 11 and convert(char(8),ia2.thedate,112) = convert(char(8),getdate(),112)
and ( (ia2.reads-ia1.reads) + (ia2.writes-ia1.writes) ) >0
order by ((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes)) desc;
go
|
Abridged results
|
tablename |
indexname |
Reads |
Writes |
|
TransportLinks |
PK_TransportLinks |
153575 |
188427 |
|
SalesItems |
Uk_SalesItems_StockNumber |
264492 |
4 |
|
SalesItems |
Idx_SalesItems_Reject |
257247 |
0 |
|
EmployeeGroup |
PK_EmployeeGroup |
212268 |
11 |
|
RestrictedItems |
Idx_RestrictedItems_Analysis |
76818 |
38608 |
|
NearestPub |
Uk_NearestPub |
49716 |
50318 |
14.6 Most Reads and Writes showing %age proportion for an hour period
|
--
-- Example query to extract index usage
-- Results for the hour 10:00 to 11:00 today
-- Highest Reads and Writes with proportion shown
--
select ia1.tablename,ia1.indexname,ia2.reads-ia1.reads as Reads,ia2.writes-ia1.writes as Writes
, convert(int, (ia2.reads-ia1.reads)*1.0/((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes))*100.0) as 'Read%'
, convert(int, (ia2.writes-ia1.writes)*1.0/((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes))*100.0) as 'Write%'
from ServerAdmin.dbo.tbl_indexactivity ia1 join ServerAdmin.dbo.tbl_indexactivity ia2 on
ia1.tablename = ia2.tablename and ia1.indexname=ia2.indexname
where datepart(hh,ia1.thedate) = 10 and convert(char(8),ia1.thedate,112) = convert(char(8),getdate(),112)
and datepart(hh,ia2.thedate) = 11 and convert(char(8),ia2.thedate,112) = convert(char(8),getdate(),112)
and ( (ia2.reads-ia1.reads) + (ia2.writes-ia1.writes) ) >100
order by ((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes)) desc;
go
|
Abridged results
|
tablename |
indexname |
Reads |
Writes |
Read% |
Write% |
|
TransportLinks |
PK_TransportLinks |
153575 |
188427 |
44 |
55 |
|
SalesItems |
Uk_SalesItems_StockNumber |
264492 |
4 |
99 |
0 |
|
SalesItems |
Idx_SalesItems_Reject |
257247 |
0 |
100 |
0 |
|
EmployeeGroup |
PK_EmployeeGroup |
212268 |
11 |
99 |
0 |
|
RestrictedItems |
Idx_RestrictedItems_Analysis |