Wednesday, October 13, 2010
Which query is scanning that index?
SQL 2005's db_dm_index_usage_stats DMV allows DBAs to analyse how often indexes are being used (or not used), including individual counts for seeks, scans & lookups on each index in your database. I was thrilled when I first saw this DMV as there was previously no way to determine whether indexes weren't being used & could be safely removed from a table without risk of queries suddenly full scanning the table & bringing a system's performance to its knees.
Another useful metric reported by db_dm_index_usage_stats is its user_scans column. This reports how many times each index has been fully scanned without a filter predicate (not range scanned). Full scans are usually bad from a performance perspective so it is useful to know whether you have any indexes that are being fully scanned.
Even more useful would be to know which QUERIES are scanning those indexes so you could do something about tuning them. Unfortunately, db_dm_index_usage_stats doesn't quite go this far but I recently decided to try & develop a script to work this out. After scratching around the various performance DMVs & not getting very far, I decided to post a question to the SQL MVP discussion forum.
Thankfully, an ex SQL MVP who now works for Microsoft on the SQL dev team - Umachandar Jayachandran - came up with this innovative approach:
with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as s)
select top 50 st.text, qp.query_plan, qs.ec as exec_count, qs.tlr as total_reads
from (
select s.sql_handle, s.plan_handle, max(s.execution_count) as ec, max(s.total_logical_reads) as tlr
from sys.dm_exec_query_stats as s
where s.max_logical_reads > 100
group by s.sql_handle, s.plan_handle) as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply (select distinct relop.value('@Index','nvarchar(130)') as IndexRef
from qp.query_plan.nodes(
N'//s:Batch/s:Statements/s:StmtSimple/s:QueryPlan[1]//
s:RelOp[@PhysicalOp = ("Index Scan")]/*[local-name() = ("IndexScan")]/
s:Object[@Database = ("[DBNameHere]")
and @Table = ("[TableNameHere]")
and @Index = ("[IndexNameHere]")]'
) as ro(relop)
) as r
Notice that this query leverages three performance DMVs:
* dm_exec_query_stats - identifies base query performance stats
* dm_exec_query_plan - cross joined to aggregated view of dm_query_stats to pick up the XML query plan for each query.
* dm_exec_sql_text - also cross joined to pick up each sql statement.
The innovative part of this query is the way it uses XQuery syntax to interrogate the XML typed query_plan column from dm_exec_query_plan, traversing the XML Showplan structure of the query_plan column's nodes collection. It starts at the Batch element, navigating through Statements/StmtSimple/QueryPlan before searching through RelOp elements for one with PhysicalOp attribute = "Index Scan" and an IndexScan element with a child Object element containing the required DBname, TableName & IndexName. This sounds like quite a mouthful, but if you compare the above sentence with some XML output from a query plan which scans an index you should be able to follow the XML traversal fairly easily.
Behind the scenes, this query is actually trawling the procedure cache, performing the above parse operation on every plan in the cache. On systems with large caches (10s of Gb) this might take minutes, but this shouldn't result in a significant performance degradation as the scan is non-blocking & although it works a single CPU hard, it shouldn't consume multi CPUs (it hasn't in my testing so far at least). The filter on max_logical_reads attempts to limit the number of plans parsed. Whilst I have set the filter to 100 reads here, you might experiment with this based on the size of index scans you're evaluating. There's no point parsing query plans that don't have a maximum logical reads count at least the same size as the index scan. Setting this filter to a size close to the number of pages your scanned index is using for storage will usually eliminate the majority of smaller plans within the cache from being XML parsed.
Keep in mind that this query only works with execution plans currently cached in the procedure cache. This is a consequence of using these DMVs which are limited to accessing contents of the current cache. Because the procedure cache is fairly transient, results may vary if the system is under memory pressure or if you restart SQL Server regularly, or manually clear procedure cache (eg by using dbcc freeproccache).
Another limitation of this query is that it only shows stored procedures, without showing the specific statements within stored procedures that are scanning the index. This shouldn't be too hard to work out by looking at the stored proc code, but other DMVs can help zero in on the actual statements. I'll try & improve this aspect of the code when time permit
Measure database file i/o with fn_virtualfilestats (SQL2000 AND SQL2005)
I'm posting another admin script today which can be useed on either SQL Server 2000 or SQL Server 2005 to measure i/o behaviour against specific database files, using the fn_virtualfilestats() system TSQL function.
This script is useful when you need to identify the source of disk bottlenecks on a database server.
For example, you might have identified that a disk bottleneck on your exists on a server via use of a Perfmon counter such as [\\YOURSERVERNAME\LogicalDisk(D:)\Avg. Disk Read Queue Length], but multiple database files exist on the D:\ volume and you're not sure which specific file is causing the i/o activity that is the source of the bottleneck.
The output from this script provides a break-down of specific i/o data on a per-database-file basis, allowing you to see how many bytes have been read to or written from each file (total, since the last restart of SQL Server) and also the total stall time for each file (ms).
More columns were added to the output of fn_virtualfilestats() for SQL Server 2005, providing even further detailed data than from the SQL Server 2000 version of fn_virtualfilestats(). Most significantly, stall time is only provided as a total in the SQL 2000 version but is broken down into read stalls & write stalls in the SQL 2005 version.
When this script runs, it determines which version of SQL Server it is running against & provides the appropriate output for the discovered version. So, when you run this against SQL 2000 you can expect the new SQL 2005 output columns to remain blank.
I've often found fn_virtualfilestats() very useful in demonstrating how much i/o the tempdb can generate compared to user databases. In fact, this is what prompted me to post this script today - I used it yesterday to show a customer that their tempdb was generating 5 x the write i/o activity compared to their main production database. This led to some further useful discussion about file placement.
/**********
* fn_virtualfilestats() script
* Greg Linwood
* greg@SolidQualityLearning.com
* Feb 25th, 2006
*
* Collects fn_virtualfilestats() for all databases
* on either SQL 2000 or SQL 2005 instances.
**********/
set nocount on
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go
declare @ver int
set @ver = cast(serverproperty('Productversion') as char(1))
create table #filestats (
DbId int not null,
DBName varchar (255) not null,
fileid int not null,
FileName varchar (255) not null,
SampleTime datetime not null,
TS bigint null,
NumberReads bigint null,
BytesRead bigint null,
IoStallReadMS bigint null,
NumberWrites bigint null,
BytesWritten bigint null,
IOStallWriteMS bigint null,
IOStallMS bigint null,
BytesOnDisk bigint null,
constraint pk primary key (DbId, fileid, SampleTime))
declare @database_name varchar(255)
declare @database_id int
declare @file_id int
declare @file_name varchar (1000)
declare @sql nvarchar(4000)
declare cr cursor for
select sd.name, sd.dbid, saf.fileid, saf.name
from sysaltfiles saf
join sysdatabases sd on saf.dbid = sd.dbid
order by sd.name, saf.fileid
open cr
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
while @@fetch_status = 0
begin
if @ver = 8
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, NumberWrites, BytesWritten, IOStallMS)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, NumberWrites, BytesWritten, IOStallMS
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
if @ver = 9
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
end
go
close cr
deallocate cr
go
select * from #filestats
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go
This script is useful when you need to identify the source of disk bottlenecks on a database server.
For example, you might have identified that a disk bottleneck on your exists on a server via use of a Perfmon counter such as [\\YOURSERVERNAME\LogicalDisk(D:)\Avg. Disk Read Queue Length], but multiple database files exist on the D:\ volume and you're not sure which specific file is causing the i/o activity that is the source of the bottleneck.
The output from this script provides a break-down of specific i/o data on a per-database-file basis, allowing you to see how many bytes have been read to or written from each file (total, since the last restart of SQL Server) and also the total stall time for each file (ms).
More columns were added to the output of fn_virtualfilestats() for SQL Server 2005, providing even further detailed data than from the SQL Server 2000 version of fn_virtualfilestats(). Most significantly, stall time is only provided as a total in the SQL 2000 version but is broken down into read stalls & write stalls in the SQL 2005 version.
When this script runs, it determines which version of SQL Server it is running against & provides the appropriate output for the discovered version. So, when you run this against SQL 2000 you can expect the new SQL 2005 output columns to remain blank.
I've often found fn_virtualfilestats() very useful in demonstrating how much i/o the tempdb can generate compared to user databases. In fact, this is what prompted me to post this script today - I used it yesterday to show a customer that their tempdb was generating 5 x the write i/o activity compared to their main production database. This led to some further useful discussion about file placement.
/**********
* fn_virtualfilestats() script
* Greg Linwood
* greg@SolidQualityLearning.com
* Feb 25th, 2006
*
* Collects fn_virtualfilestats() for all databases
* on either SQL 2000 or SQL 2005 instances.
**********/
set nocount on
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go
declare @ver int
set @ver = cast(serverproperty('Productversion') as char(1))
create table #filestats (
DbId int not null,
DBName varchar (255) not null,
fileid int not null,
FileName varchar (255) not null,
SampleTime datetime not null,
TS bigint null,
NumberReads bigint null,
BytesRead bigint null,
IoStallReadMS bigint null,
NumberWrites bigint null,
BytesWritten bigint null,
IOStallWriteMS bigint null,
IOStallMS bigint null,
BytesOnDisk bigint null,
constraint pk primary key (DbId, fileid, SampleTime))
declare @database_name varchar(255)
declare @database_id int
declare @file_id int
declare @file_name varchar (1000)
declare @sql nvarchar(4000)
declare cr cursor for
select sd.name, sd.dbid, saf.fileid, saf.name
from sysaltfiles saf
join sysdatabases sd on saf.dbid = sd.dbid
order by sd.name, saf.fileid
open cr
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
while @@fetch_status = 0
begin
if @ver = 8
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, NumberWrites, BytesWritten, IOStallMS)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, NumberWrites, BytesWritten, IOStallMS
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
if @ver = 9
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
end
go
close cr
deallocate cr
go
select * from #filestats
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go
Subscribe to:
Posts (Atom)