Databases Reference
In-Depth Information
5 to RAID 10, and making sure your RAID controller hardware cache is used for writes instead of
reads will all help write performance.
Next, you will take a look at the most expensive statements within your cached stored procedures
for average I/O, using the query shown in Listing 15-44.
LISTING 15-44:
Top statements by average I/O
-- Lists the top statements by average input/output
-- usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count
AS [Avg IO],SUBSTRING(qt.[text],qs.statement_start_offset/2,
(CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) AS [Query Text]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC OPTION (RECOMPILE);
-- Helps you find the most expensive statements for I/O by SP
This query identii es the most expensive cached statements for I/O, ordered by average I/O. If your
system is showing any signs of I/O pressure, you should dei nitely take a look at the results of this
query. Even if you are not seeing I/O pressure, it never hurts to be aware of which statements within
your stored procedures are causing the most I/O pain.
Next, using the query shown in Listing 15-45, you will look for nonclustered indexes that have more
writes than reads.
LISTING 15-45:
Possible bad nonclustered indexes
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name],
i.index_id,user_updates AS [Total Writes],
user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
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()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION
(RECOMPILE);
-- Look for indexes with high numbers of writes
Search WWH ::
Custom Search