July 2007 - Posts

Excellent Partitioning Article

I've done some work with table partitioning using the "sliding window" scenario using KImberley's article as a reference point. I was reading through my Technet Magazine and found this excellent article on partitioning by Noah Gomez  http://download.microsoft.com/documents/uk/technet/downloads/technetmagazine/40_47_simplifydb_desfin.pdf

I actually found there were a number of aspects of partitioning that didn't sit very well with what I was doing, try altering the partition function and/or partitions when your database is in use, some slight issues if your database is 24 x 7, however that's another story.

If you don't subscribe to Technet Magazine it might be worth a look, there are sometimes some real gems, there was a fantastic article last year on how to create load balanced sql 2005 servers behind your web site/farm.

Posted by GrumpyOldDBA with no comments
Filed under:

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

76818

38608

66

33

NearestPub

Uk_NearestPub

49716

50318

49

50

 

  • So this allows us to view index usage at the granularity of data collection.
  • For the application I’m supporting data collection grows at 1,700 rows per hour, that’s about 1.3 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 indexes for each category for a 7 day period and using this to extract more detailed statistics. ( This is in Part 3 )

 

15.0  So Just how many indexes do I have ?

 

--

-- How many indexes ?

-- run in context of database to where they are to be counted

--

select type_desc,count(*) from sys.indexes

where objectproperty(OBJECT_ID,'IsUserTable') = 1

group by type_desc;

go

 

 

type_desc

(No column name)

CLUSTERED

2936

HEAP

17

NONCLUSTERED

4067

 

15.1  How many empty tables do I have ?      

  • Many third party applications have unused sections which may result in many empty tables
  • Having a definitive list of unused tables will help provide a filter to use in subsequent analysis
  • As far as I can see this method is accurate, however, row count is not so easily found in SQL 2005, it’s available within system functions which cannot be accessed normally.

 

15.2      Create this table in the ServerAdmin database

 

 USE [ServerAdmin];

GO

--

IF  EXISTS (SELECT * FROM sys.objects WHERE objectproperty(OBJECT_ID(N'[dbo].[tbl_EmptyTables]'),'IsUserTable') = 1)

DROP TABLE [dbo].[tbl_EmptyTables];

--

create table dbo.tbl_EmptyTables

(

TableName sysname not null,

[Object_id] bigint not null,

IndexName sysname not null,

Row_Count bigint not null

);

go

 

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

  • Extracts by clustered index and by HEAP
  • Easiest to use dbo.sysindexes although not ideal.

 

 --

-- run in database to be analysed

--

insert into ServerAdmin.dbo.tbl_EmptyTables(TableName,[Object_id],IndexName,Row_Count)

select object_name(id),id,name,rows from dbo.sysindexes

where rows = 0 and indid = 1

and objectproperty(ID,'IsUserTable') = 1;

--

insert into ServerAdmin.dbo.tbl_EmptyTables(TableName,[Object_id],IndexName,Row_Count)

select object_name(id),id,isnull(name,'HEAP'),rows from dbo.sysindexes

where rows = 0 and indid = 0

and objectproperty(ID,'IsUserTable') = 1;

go

 

 

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

 

 use ServerAdmin;

go

--

create unique index uk_tbl_EmptyTables_Object_ID on dbo.tbl_EmptyTables([object_id]);

go

 

  • This produces a result set of over 2,000 for the application I am supporting

 

16.0  How many statistics?

  • Now the viewing of statistics is somewhat different 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 has with this query
  • I generally consider high numbers of auto stats being indicative of inadequate indexing.
  • I generally will periodically delete all system stats from a database
  • If there were stats and you create an index the stats 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
  • The subject of statistics will be discussed later in this series

 

16.1  List all statistics

 

--

-- show statistics in the database

--

select object_name(s.object_id),* from sys.stats s

where objectproperty(s.OBJECT_ID,'IsUserTable') = 1

and s.auto_created | s.user_created = 1;

go

 

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

 

Abridged results

 

(No column name)

Object

id

name

Stats

id

Auto

created

User

created

No

recompute

Employees

119887

sts_Employees1

2

0

1

0

Employees

119887

sts_Employees2

3

0

1

0

Sales

1187550

_WA_Sys_00000002_08B54D69

2

1

0

0

RegionSouth

1656045

sts_RegionSouth_Pole

3

0

1

0

RegionNorth

1656045

_WA_Sys_Rows_75D7831F

4

1

0

0

 

  • System generated statistics always start _WA_Sys_
  • There are also hypothetical indexes which you should 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_created)+convert(char(1),user_created)

when '10' then 'Auto Created'

when '01' then 'User Created'

else 'Both'

end as StatsType,

count(*) as HowMany from sys.stats s

where objectproperty(s.OBJECT_ID,'IsUserTable') = 1

and s.auto_created | s.user_created = 1

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

go

 

 

StatsType

HowMany

User Created

7786

Auto Created

121

 

15.3  View to display Statistics Information

  • 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 database that you wish to display basic information on statistics

 

--

-- view to provide information on statistics only

-- filters out index stats and system objects

-- per database create in database

--

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[dm_db_statistics]'))

DROP VIEW [dbo].[dm_db_statistics];

go

create view dbo.dm_db_statistics

as

select object_name(s.[object_id]) as TableName,c.name as ColumnName,s.name as StatName,s.auto_created,s.user_created,s.no_recompute,s.[object_id],

s.stats_id,sc.stats_column_id,sc.column_id,stats_date(s.[object_id], s.stats_id) as LastUpdated

from sys.stats s join sys.stats_columns sc on sc.[object_id] = s.[object_id] and sc.stats_id = s.stats_id

join sys.columns c on c.[object_id] = sc.[object_id] and c.column_id = sc.column_id

where objectproperty(s.OBJECT_ID,'IsUserTable') = 1

and s.auto_created | s.user_created = 1;

go

 

 

Sample results ( please excuse formatting )

 

Table Name

Column Name

Stat Name

Auto created

User created

No recompute

Object id

Stats id

Stats column id

Column id

Last Updated

IndexStats

LastUpdate

_WA_Sys_ IndexStats _164452B1

1

0

0

373576369

2

1

10

2007-07-16 16:34:21.780

WeeklyStats

counter_name

_WA_Sys_counter_name_173876EA

1

0

0

389576426

2

1

3

2007-07-16 16:34:21.827

Product2007

header

_WA_Sys_header_24927208

1

0

0

613577224

2

1

1

2007-07-16 16:34:21.890

Trace20070707

TextData

_WA_Sys_00000003_2D27B809

1

0

0

757577737

2

1

3

2007-07-10 14:15:07.863

 

 

  • When there are many indexes and statistics the results set can be large and make analysis difficult
  • The application I support has over 15,000 indexes and statistics, many of these relate to unpopulated tables.
  • Should the system procedure  sp_createstats  have been run against the database then statistics will have been created on empty tables.
  • This can 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 extra statistics – and these will have to be maintained – so use this proc with care.

 

http://msdn2.microsoft.com/en-us/library/ms186834.aspx

 

( to be continued )

More TokenAndPermUserStore

I was interested in Jasper’s post

 

http://sqlblogcasts.com/blogs/sqldbatips/archive/2007/07/18/troubleshooting-performance-issues-with-tokenandpermuserstore-in-sql2005.aspx?CommentPosted=true#commentmessage

 

The following may also be of interest to readers.

 

http://support.microsoft.com/default.aspx/kb/927396

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

 

I’m currently working on optimizing a SQL 2005 application, it would appear the main issues are probably within the app rather than sql server, but that’s another story!

As part of the optimization we’ve deployed a number of plan guides and as part of that process I’ve been clearing the cache :-

 

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

go

 

However of late we’re waiting on some changes before continuing – here’s the results I got from my server ( names changed to protect the innocent ) with this query:

Be careful how you run this on a production server.

( my cache size at the time was 450mb btw )

I’d probably be inclined to track all the counts over time as on my system this is extremely volatile, my proc cache varies between 800mb and 2.1 gb

 

select name, count(*) from sys.dm_os_memory_cache_entries

group by name

order by count(*) desc ;

 


 

name

count

Bound Trees

108

Broker dormant rowsets

17

Database4

77

Extended Stored Procedures

18

Database5

23

master

139

model

26

msdb

200

mssqlsystemresource

112

Object Plans

147

ObjPerm - master

16

ObjPerm - mssqlsystemresource

1

ObjPerm - tempdb

1

ObjPerm – Database1

49

ObjPerm – Database2

1843

SchemaMgr Store

123

Service broker mapping table

1

Service Broker Null Remote Service Binding Cache

1

Service broker routing cache

3

SOS_StackFramesStore

1

SQL Plans

7871

sxcCacheStore

52

SystemRowsetStore

354

tempdb

63

Temporary Tables & Table Variables

12

TokenAndPermUserStore

837993

Database1

1392

Database3

35

Database2

4208

 

You can also use this query, provided by Microsoft  EMEA Customer Service & Support - SMS&P” , thank you.

 

 select * from sys.dm_os_memory_cache_entries

      where cast(entry_data as xml).value('(//@class)[1]', 'bigint') = 65535;

 

As a footnote I’m using 32 bit sql on 64bit o/s with rollup 3161 applied.

 

Posted by GrumpyOldDBA with 20 comment(s)

More Tom Davidson Magic

If you ever downloaded these two SQL 2000 documents, and I can’t find the links to them any more, you’ll know how great Tom Davidson’s white paper on waits and queues is and how it is the standard reference for what all those waits that you find in sysprocesses.

 

KB702_SQL Performance Tuning using Waits and Queues

and

KB703_SQL Server 2000 P&T Survival Guide.doc

 

Have been two of my staple references, and I adapted these for my own use, I thought I’d blogged the changes I did but I guess perhaps I didn’t in the end, I wanted a better way to store captured information for historical analysis. If anyone’s interested in the sql 2000 stuff then drop me a mail and I’ll forward it to them, there’s a lot of images in my documents so maybe that’s one of the reasons I didn’t blog it as adding the images to a post is a bit time consuming.

 

Well Tom has reworked his magic on SQL 2005 – I’m sort of hoping some of this will make its way into the performance dashboard in SQL 2008.

 

 

http://blogs.msdn.com/sqlcat/archive/2007/07/13/performance-analysis-tool-dmvstats.aspx

 

Posted by GrumpyOldDBA with 2 comment(s)

Correction to Indexes Part 1

13.2

 

--

-- use with great care !!!

--

SELECT object_name(a.[object_id]) as TableName,a.index_id, isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as pages,sum(a.page_count)*1.0/128 as Mb

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

group by a.[object_id],a.index_id, b.name

order by pages desc;

GO 

 

 

 

TableName

index_id

IndexName

pages

Mb

Employees

1

PK_Employees

86897

678.860351

Sales

1

PK_Sales

72222

564.529296

TableInfo

0

HEAP

13909

108.583007

Areas

1

PK_Areas

5753

44.618164

 

13.5

 

declare @table table(object_id int,ind_name sysname,index_id int)

insert into @table

select top 25 ui.[object_id],si.name,ui.index_id

from dbo.Unused_Indexes ui join sys.indexes si

on ui.[object_id] = si.[object_id] and ui.index_id = si.index_id

where objectproperty(ui.[object_id],'IsUserTable') = 1

group by ui.[object_id],ui.index_id,si.name

having count(*) >30

order by count(*) desc,ui.[object_id],ui.index_id asc

--

select  object_name(ui.[object_id]) as TableName,ui.ind_name as IndexName,      

max(fps.index_level)as IndexDepth,sum(fps.page_count) as TotalPages,max(fps.record_count) as LeafRows, sum(fps.page_count)/128 as Mb

from @table ui

CROSS APPLY master.dbo.fn_db_index_physical_stats (DB_ID(),ui.[object_id],ui.index_id,NULL, 'Detailed') AS fps

group by ui.[object_id],ui.ind_name

order by sum(fps.page_count) desc ;

 

Abridged results

 

TableName

IndexName

IndexDepth

TotalPages

LeafRows

Mb

Employees

Idx_Employees_123

4

54811

11159545

428.526367

Invoices

Idx_Invoices_paper

4

44532

5162428

347.488281

Invoices

Idx_Invoices_ink

4

39539

5162428

308.612304

DepartmentSales

Idx_DS_Promotions

3

16791

3199181

130.397460

Sales2006

Idx_sales2007_opened

2

116

31291

0.893281

Sales2006

Idx_sales2007_area

2

103

31291

0.800585

Sales2006

Idx_sales2007_size

2

87

31291

0.654960

 

 

 

Posted by GrumpyOldDBA with no comments

Analysing Indexes Part 1

Actually what started as a small document has continued to grow.  I'm working on performance tuning a well known application and as I document as I work so others can follow what I'm doing I thought I'd add it to my blog. There's nothing particularly startling in here but if you want to drill into index performance on your database this may help. As I remarked on Tony or maybe Simon's blog it's fine looking at small tables but when row counts are in hundreds of millions and secondary indexes hit 10Gb ( each ) then performance and storage become more critical - and difficult to test - it's not a quick task to drop and create a handful of indexes on a 250 million row table.

  • SQL Server 2005 maintains system views which contain information concerning indexes.
  • As these are views the information is not carried forward from a server restart
  • The information returned is cumulative so great care must be taken in evaluating the results.
  • To obtain consistent data and results we must store the information from the system views in permanent tables for later analysis.
  • The presented queries assume no partitioning

 

1.0  Operational cost on indexes ( assuming no RFI )

 

Operation

Read

Write

Select

Always

No

Insert

No

Always – every index

Update

Always

Only if row qualifies

Delete

Always

Only if row qualifies

 

 

2.0  Index Information

 

Object

Description

Notes

dbo.sysindexes

This is the SQL 2000 system table taken forward as a view.

This table may not be supported going forward.

 

sys.dm_db_index_physical_stats

This is actually a function and replaces dbcc showcontig

 

sys.indexes

2005 system table contains property information only for each index.

 

sys.dm_db_index_usage_stats

System view which records the access usage of every index in the database

We can view the number of times this index has been used, the type of access and the last access date

sys.dm_db_index_operational_stats

System function which records the operational cost of access to the index

We can view information such as the number of pages, locks, latches and waits.

sys.objects

Contains information on database objects

 

 

sys.dm_db_missing_index_details

System view which stores information on indexes the optimiser considers are missing.

 

sys.dm_db_missing_index_group_stats

System view which stores usage and access details for the missing indexes similar to sys.dm_db_index_usage_stats

We can also view the improvement that the index is computed to make, this is essentially the same type of output as shown by the Tuning Advisor.

sys.dm_db_missing_index_groups

Presumably this view will be of more use in SQL2008

 

sys.dm_db_missing_index_columns

A system function which returns the columns for a missing index

Requires the index handle as a parameter

3.0  sys.dm_db_index_usage_stats information

http://msdn2.microsoft.com/en-us/library/ms188755.aspx

 

  • There are groups of data columns which interest us
    • click on the link ( above ) for full table details
  • User access counts
  • User access dates
  • System access counts ( e.g. update statistics, index rebuilds )
  • System access dates

 

4.0  List unused tables/indexes

  • This will largely indicate empty tables as maintenance tasks will create entries within sys.dm_db_index_usage_stats where there is data in the table/index
    • Update statistics tablename
    • Dbcc dbreindex(tablename)
  • I’d normally full qualify queries with three part naming; this is omitted for this document.

 

--

-- run in database to analyse

--

Select object_name(i.object_id) as TableName,isnull(i.name,'HEAP') as IndexName, i.index_id

from sys.indexes i join sys.objects o on o.object_id = i.object_id

where objectproperty(o.object_id,'IsUserTable') = 1

and i.index_id NOT IN

(select s.index_id from sys.dm_db_index_usage_stats s

where s.object_id=i.object_id and i.index_id=s.index_id

and database_id = db_id() )

order by object_name(i.object_id),i.index_id;

 

 

 

5.0  Query to filter by user access

  • This sorts by lack of user access
  • This is a count of the times there was a seek, scan or lookup on the index/table
  • I’ve discounted updates from my criteria as an update will occur regardless of the index being used for selects
  • Essentially any index with zero as a value for seek, scan and lookup will be unused.

 

 

--

-- run in database to be analysed

--

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,

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups, user_updates as Updates

from sys.dm_db_index_usage_stats s join sys.indexes i on i.object_id = s.object_id

and i.index_id = s.index_id

where database_id = db_id() and objectproperty(s.object_id,'IsUserTable') = 1

order by (user_seeks + user_scans + user_lookups ) asc;

 

  • Remember that these values are cumulative since start up so we must snapshot the data on a regular basis to enable proper analysis
  • We must also collect data over a suitable time period based upon the usages of your database.
  • In an environment which runs, say, month end processes, it is vital that we capture the full work life cycle.
  • This is especially important if the CEO or MD has a favourite report they run once a month and you remove the index(es) which make this run quickly.

 

6.0  Capture snapshot of index activity

 

--

-- Run this is the database to be analysed

--

Insert into dbo.Unused_indexes

SELECT getdate(),* FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID()

and (last_user_seek < convert(char(11),getdate()-1) or last_user_seek is null)

and (last_user_scan < convert(char(11),getdate()-1) or last_user_scan is null);

 

 

  • I would schedule this to run every night just after midnight
  • I’m collecting information where an index has not been used in the preceding 24 hours for a user seek or user scan. ( see  Working with snapshots  for explanation of this )
  • The collection date is added for clarity.
  • A primary key could be defined on the columns TheDate,database_id,object_id,index_id
  • The table is a Heap

 

6.1  Table to capture index activity

 

CREATE TABLE dbo.Unused_Indexes

(

            TheDate datetime NOT NULL,

            database_id smallint NOT NULL,

            [object_id] int NOT NULL,

            index_id int NOT NULL,

            user_seeks bigint NOT NULL,

            user_scans bigint NOT NULL,

            user_lookups bigint NOT NULL,

            user_updates bigint NOT NULL,

            last_user_seek datetime NULL,

            last_user_scan datetime NULL,

            last_user_lookup datetime NULL,

            last_user_update datetime NULL,

            system_seeks bigint NOT NULL,

            system_scans bigint NOT NULL,

            system_lookups bigint NOT NULL,

            system_updates bigint NOT NULL,

            last_system_seek datetime NULL,

            last_system_scan datetime NULL,

            last_system_lookup datetime NULL,

            last_system_update datetime NULL

)

 

 

 

 

7.0  sys.dm_db_index_operational_stats

http://msdn2.microsoft.com/en-us/library/ms174281.aspx

 

  • There are groups of data columns which interest us
    • click on the link ( above ) for full table details
  • This is a very important table which contains a wealth of information
  • We can view waits, page splits, and volume of access at leaf and non-leaf levels

 

8.0  View index usage by work - Selects

  • This query shows all indexes, reads to top , writes to bottom

 

--

-- Run this is the database to be analysed

--

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=range_scan_count + singleton_lookup_count

, 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count

, 'leaf_page_splits' = leaf_allocation_count

, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count

, 'nonleaf_page_splits' = nonleaf_allocation_count

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id = s.object_id and i.index_id = s.index_id

where objectproperty(s.object_id,'IsUserTable') = 1

order by reads desc, leaf_writes, nonleaf_writes;

 

 

8.1  This query only shows those indexes which have reads

 

--

-- Run this is the database to be analysed

--

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=range_scan_count + singleton_lookup_count

, 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count

, 'leaf_page_splits' = leaf_allocation_count

, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count

, 'nonleaf_page_splits' = nonleaf_allocation_count

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id = s.object_id and i.index_id = s.index_id

where objectproperty(s.object_id,'IsUserTable') = 1

and (range_scan_count + singleton_lookup_count)>0

order by reads desc, leaf_writes, nonleaf_writes;

 

 

 

 

8.2  Indexes without reads but with high writes may be being maintained unnecessarily

 

--

-- Run this is the database to be analysed

--

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=range_scan_count + singleton_lookup_count

, 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count

, 'leaf_page_splits' = leaf_allocation_count

, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count

, 'nonleaf_page_splits' = nonleaf_allocation_count

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id = s.object_id and i.index_id = s.index_id

where objectproperty(s.object_id,'IsUserTable') = 1

and (range_scan_count + singleton_lookup_count)=0

and (leaf_update_count+ leaf_delete_count+ nonleaf_update_count + nonleaf_delete_count)>0

order by leaf_writes desc, nonleaf_writes desc;

 

9.0  Order by writes to find your most heavily updated index

  • The inserts have been separated as we cannot avoid this io
  • Indexes without writes are excluded
  • Secondary indexes appearing high on the list may benefit from being placed on a separate filegroup
  • High levels of leaf writes on a HEAP may indicate the possibility of fragmentation which cannot be removed

 

--

-- Run this is the database to be analysed

--

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

,'total_writes'=leaf_update_count+ leaf_delete_count+nonleaf_update_count + nonleaf_delete_count

,'total_insert_writes'=leaf_insert_count+nonleaf_insert_count

, 'leaf_writes'=leaf_update_count+ leaf_delete_count

, 'nonleaf_writes'=nonleaf_update_count + nonleaf_delete_count

, 'insert_leaf_writes'=leaf_insert_count

, 'insert_nonleaf_writes'=nonleaf_insert_count

, 'leaf_page_splits' = leaf_allocation_count

, 'nonleaf_page_splits' = nonleaf_allocation_count

, reads=range_scan_count + singleton_lookup_count

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id = s.object_id and i.index_id = s.index_id

where objectproperty(s.object_id,'IsUserTable') = 1

and (leaf_update_count+ leaf_delete_count+nonleaf_update_count + nonleaf_delete_count+leaf_insert_count+nonleaf_insert_count)>0

order by total_writes desc,total_insert_writes desc;

 

10.0  Simple query for user access reads and writes

 

--

-- Run this is the database to be analysed

--

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

 

 

 

In much the same way as ::fn_virtualfilestats  or sys_dm_io_virtual_file_stats  return information concerning file activity   sys.dm_db_index_operational_stats  allows us to view similar information for our indexes

http://msdn2.microsoft.com/en-us/library/ms190326.aspx

http://msdn2.microsoft.com/en-us/library/ms187309.aspx

 

11.0  Query to display waits and blocks

  • this query is cumulative so is of questionable value

 

--

-- Run this is the database to be analysed

--

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  

,row_lock_count, row_lock_wait_count

,[block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))

, row_lock_wait_in_ms

, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))

from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s join sys.indexes i

on i.object_id = s.object_id and i.index_id = s.index_id

where objectproperty(s.object_id,'IsUserTable') = 1

order by row_lock_wait_count desc

 

 

 

 

 

 

 

11.1  Typical sample output

 

Table

Index

Index Type

Row lock count

Row lock wait count

block %

Row lock wait in ms

avg row lock waits in ms

Sales

$21

NC

32679

34

0.10

104076

2973.60

Employees

PK_Employees

CLUS

958510

32

0.00

1055219

31976.33

Invoices

PK_Invoices

CLUS

100332

27

0.03

19049

680.32

Returns

Clx_Returns_Date

CLUS

1771122

21

0.00

168156

7643.45

Areas

PK_Areas

CLUS

50762

12

0.02

937

72.08

 

  • In general terms as this is from start up the values are likely diluted, however we can see that on the Employees table ( none of these tables are real, although the data is ) we have some serious waits when a wait does occur.
  • You would want to snapshot and compare to extract operational data values.

 

12.0  Find your worst scans

  • An index scan may not be an issue so we’ll concentrate on table scans
  • A clustered index scan is a table scan so we’ll select on index id 0 and 1

 

--

-- run in database to be analysed

--

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,

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups

from sys.dm_db_index_usage_stats s join sys.indexes i on i.object_id = s.object_id

and i.index_id = s.index_id

where database_id = db_id() and objectproperty(s.object_id,'IsUserTable') = 1

and user_scans>0 and i.index_id<2

order by user_scans desc;  

 

 

12.1  Add a calculation and sort by proportion of scans to seeks

 

--

-- run in database to be analysed

--

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,

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups

,cast((user_scans*1.0/(user_seeks+user_scans))*100 as numeric(5,2)) as '%age'

from sys.dm_db_index_usage_stats s join sys.indexes i on i.object_id = s.object_id

and i.index_id = s.index_id

where database_id = db_id() and objectproperty(s.object_id,'IsUserTable') = 1

and user_scans>0 and i.index_id<2

order by '%age' desc;

 

13.0  Working with snapshots

 

In section 6.0 ( above ) we devised a simple query to collect index data

This method allows us to analyse usage far better.

Each index will have a row in the table if it were not used in the previous 24 hours

The snapshot taken just after midnight

After collecting data for, say, 32 days if an index has 32 entries in the table then it was not used in this time at all

 

13.1  This query shows counts for index entries

  • It assumes that the table Unused_Indexes was created in the user database

 

--

-- run in database to be analysed

--

select object_name(ui.[object_id]),si.name,ui.index_id,count(*) as Days

from dbo.Unused_Indexes ui join sys.indexes si

on ui.[object_id] = si.[object_id] and ui.index_id = si.index_id

where objectproperty(ui.[object_id],'IsUserTable') = 1

group by ui.[object_id],ui.index_id,si.name

order by count(*) desc,ui.[object_id],ui.index_id asc;

 

 

 

13.2      How large are indexes in my database

·          Great care must be exercised when running a query which includes sys.dm_db_index_physical_stats as for large tables this may well run for some time.

·          An alternative would be to gather the information as a separate process .. see 13.4

 

--

-- use with great care !!!

--

SELECT object_name(a.[object_id]) as TableName,a.index_id, isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as pages,sum(a.page_count)*1.0/1024 as Mb

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

group by a.[object_id],a.index_id, b.name

order by pages desc;

GO 

 

 

 

TableName

index_id

IndexName

pages

Mb

Employees

1

PK_Employees

86897

84.860351

Sales

1

PK_Sales

72222

70.529296

TableInfo

0

HEAP

13909

13.583007

Areas

1

PK_Areas

5753

5.618164

 

 

13.3  How large are my unused indexes

  • Great care must be exercised when running a query which includes sys.dm_db_index_physical_stats as for large tables this may well run for some time.
  • An alternative would be to gather the information as a separate process .. see 13.4
  • This expands upon the query in 13.1
  • One of the joys of SQL 2005 is the ability to join using a table valued function, sadly some of microsoft’s functions don’t work, sys.dm_db_index_physical_stats for example, however this is not a problem as all we need to do is create our own table valued function then we can happily join sys.dm_db_index_physical_stats to any table of our choice.
  • IMPORTANT queries using this may be long running, especially on a large database – be very careful.

 

13.4  Table Valued Function for sys.dm_db_index_physical_stats

  • I personally would place this in the master database and might be tempted to make this part of the sys schema, for the examples that follow I’ve placed the function in master.

 

Create function dbo.fn_db_index_physical_stats

--

-- sys.dm_db_index_physical_stats is actually a function not a view

-- sadly you can't use CROSS APPLY with it, however, drop it in a table

-- valued function and you can!

--

(

@db_id int

,@object_id int

,@index_id int

,@partition_number int

,@mode nvarchar(16)

)

Returns @table TABLE

(

[database_id] [smallint] NULL,

[object_id] [int] NULL,

[index_id] [int] NULL,

[partition_number] [int] NULL,

[index_type_desc] [nvarchar](60) NULL,

[alloc_unit_type_desc] [nvarchar](60) NULL,

[index_depth] [tinyint] NULL,

[index_level] [tinyint] NULL,

[avg_fragmentation_in_percent] [float] NULL,

[fragment_count] [bigint] NULL,

[avg_fragment_size_in_pages] [float] NULL,

[page_count] [bigint] NULL,

[avg_page_space_used_in_percent] [float] NULL,

[record_count] [bigint] NULL,

[ghost_record_count] [bigint] NULL,

[version_ghost_record_count] [bigint] NULL,

[min_record_size_in_bytes] [int] NULL,

[max_record_size_in_bytes] [int] NULL,

[avg_record_size_in_bytes] [float] NULL,

[forwarded_record_count] [bigint] NULL

)

BEGIN

            insert into @table

            select *

            from sys.dm_db_index_physical_stats (@db_id, @object_id, @index_id ,@partition_number,@mode )

            return

END;

--end function

go

 

 

13.5  Sample Query to show size of 25 unused indexes

  • This query is selecting indexes which have 30 entries in the table Unused_Indexes

 

declare @table table(object_id int,ind_name sysname,index_id int)

insert into @table

select top 25 ui.[object_id],si.name,ui.index_id

from dbo.Unused_Indexes ui join sys.indexes si

on ui.[object_id] = si.[object_id] and ui.index_id = si.index_id

where objectproperty(ui.[object_id],'IsUserTable') = 1

group by ui.[object_id],ui.index_id,si.name

having count(*) >30

order by count(*) desc,ui.[object_id],ui.index_id asc

--

select  object_name(ui.[object_id]) as TableName,ui.ind_name as IndexName,      

max(fps.index_level)as IndexDepth,sum(fps.page_count) as TotalPages,max(fps.record_count) as LeafRows, sum(fps.page_count)/1024 as Mb

from @table ui

CROSS APPLY master.dbo.fn_db_index_physical_stats (DB_ID(),ui.[object_id],ui.index_id,NULL, 'Detailed') AS fps

group by ui.[object_id],ui.ind_name

order by sum(fps.page_count) desc ;

 

Abridged results

 

TableName

IndexName

IndexDepth

TotalPages

LeafRows

Mb

Employees

Idx_Employees_123

4

54811

11159545

53.526367

Invoices

Idx_Invoices_paper

4

44532

5162428

43.488281

Invoices

Idx_Invoices_ink

4

39539

5162428

38.612304

DepartmentSales

Idx_DS_Promotions

3

16791

3199181

16.397460

Sales2006

Idx_sales2007_opened

2

116

31291

0.113281

Sales2006

Idx_sales2007_area

2

103

31291

0.100585

Sales2006

Idx_sales2007_size

2

87

31291

0.084960

 

  • The value for index depth designates the depth of the b-tree, a depth of 4 means that there will be 4 io to return a value from that index.
  • Total pages is the sum of used pages reported fro all levels of the index, not just the leaf level
  • Leaf Rows should indicate the number of rows in the index, normally the number of rows in the table.
  • Mb is the calculation of the physical storage space the index is using

 

 

 

 

References

 

How can SQL Server 2005 help me evaluate and manage indexes?

            http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx

 

 

 

 

Posted by GrumpyOldDBA with 7 comment(s)
Filed under: ,