July 2007 - Posts

31 July 2007 09:36

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:
22 July 2007 22:38

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