Awesome SQL power

By patrickwellink
March 20, 2007
Comments Off on Awesome SQL power

A couple of days ago I went to a SQL 2005 SQL Deep Dive. This was a powerpoint only presentation of two days with a few demo’s in it. It was a very interesting deep dive and I learned a couple of things I thought I needed to share.

SQL Server keeps some statistics on tables and indexes. Once SQL server is started the statistics are kept and the statistics are lost after a reset. The SQL Guru’s called them DMV’s. Below are a few usefull DMV queries

List Indexes not used :

select object_name(i.object_id),
from sys.indexes i, sys.objects o
where  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(‘##YOUR DATABASE##’) )
and o.type = ‘U’
and o.object_id = i.object_id
order by object_name(i.object_id) asc

Average Stalls per Read, Write and Total IO

select database_id, file_id
 ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as ‘avg_read_stall_ms’
 ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as ‘avg_write_stall_ms’
 ,io_stall_read_ms + io_stall_write_ms as io_stalls
 ,num_of_reads + num_of_writes as total_io
 ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as ‘avg_io_stall_ms’
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc

Top 50 IO per statement

        (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
   (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,
  qt.dbid, dbname=db_name(qt.dbid),
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
       [Avg IO] DESC

Rarely used indexes

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
 ,, i.index_id
 , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
 sys.indexes i
where database_id = @dbid
and objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

Temp DB usage

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
 , t3.sql_handle, t3.statement_start_offset
 , t3.statement_end_offset, t3.plan_handle
from sys.dm_db_session_space_usage as t1,
  sys.dm_exec_requests t3,
(select session_id,
   sum(internal_objects_alloc_page_count) as task_alloc,
   sum (internal_objects_dealloc_page_count) as task_dealloc
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2   — tempdb is database_id=2
and t1.session_id = t3.session_id
order by allocated DESC

For the query above you will need to get the query and the plan handles (wich will have the actual SQL statement beeing executed) here is a query to get those :

(select text from sys.dm_exec_sql_text(put_sql_handle_here)) as sql_text
,(select query_plan from sys.dm_exec_query_plan(put_plan_handle_here)) as query_plan

List out indexes with the most contention e.g. blocking

declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
 ,, i.index_id –, partition_number
 , 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 (@dbid, NULL, NULL, NULL) s
 ,sys.indexes i
where objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc

There were many more interesting queries, but it came clear that a lot of these queries were incorparated into the new Sql Performance DashBoard. (You will need SQL 2005 SP2 for this to work) Go and get the SQL Performance Dashboard >>here<< 

Below is a screenshot of this amazing tool. Everything is click-able and you can drill down into the details.

SQL Perf DashBoard.JPG


Comments: 0

Comments are closed.

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta