THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Looking for Good DMV/Database Admin Queries!

I like to collect useful database administration queries that leverage the SQL Server 2005 and 2008 DMVs.  Heck, I'm still interested in SQL Server 2000 queries too.  I thought I'd make my search public so that a) you can share your favorite queries here or great reference queries written by others and publicly posted on the Internet, and b) everyone can benefit from this collaborative approach to DMV queries.  If you're aware of collections of scripts, for example like those available from the SQLCAT team, please post the location of the collections or libraries.

 

The intent is to provide ourselves with a set of scripts they can use to perform tasks that would otherwise require them to hit BOL heavily to research what DMVs or system catalog views they need to access to get what they want. These types of activities are not easily performed from within the query tool user interface.

 

I’m requesting everyone to post or reference your favorite queries in any of the following categories below. The queries could be in your notes, from web sites like MSDN or TechNet or SQLServerPedia.com, from our great SQL Server bloggers and MVPs.  (Be sure to give credit to the originator when you post it here.)  Speaking of favorite scripts, you might want to check out SQLServerPedia.com, if you haven't already done so.  The wiki is getting quite large and there's lots of new information popping up daily.  Looking for more good query samples?  If you didn't already know it, be sure to check the Samples folder in your SQL Server installation.  Microsoft has a lot of examples in their SQL Server 2005 Script Library.

 

When posting, please:

·         Describe briefly what the snippet does

·         Describe if this is a 2005/2008 query or just 2000

·         Provide the SQL / Script and indicate if there are any parameters or if the SQL can be run without modification

 

Here are some categories I'm looking for, but if you have something not addressed here, please post it:

 

·         Object Sizes – a list of objects in a database with their sizes

·         Missing Indexes

·         Index Utilization – all indexes

·         Index Utilization - on a specific table

·         Index Fragmentation – all indexes

·         Index Fragmentation – on a specific table

·         Index Defrag options – various with defrag, rebuild, online, offline, heap, etc.

·         SQL Performance – leveraging the 2005+ DMVs for worst performers, active statements

·         CPU and Optimization

·         Buffer Cache

·         Wait Stats

·         Deadlocks

·         Plan Guide Queries

·         SQL Trace

·         Backup History – or other backup related queries

 

Thanks in advance!

 

-Kevin

 

Published Tuesday, April 07, 2009 5:19 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

April 7, 2009 8:10 PM
 

KKline said:

Excellent, Adam, thanks!  -Kev

April 7, 2009 8:42 PM
 

Uri Dimant said:

Kevin

--Object Sizes (SQL Server 2005)

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;

April 8, 2009 4:55 AM
 

Jimmy May, Aspiring Geek said:

+1 for Adam's latest-&-greatest Who Is Active w/ *Delta Power*.

I collected five queries for this post:

 http://blogs.msdn.com/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx

Categories:

- Expensive Queries (CPU, reads, frequency, etc.)

- Wait Stats

- Virtual File Stats (including calcs for *virtual file latency*)

- Plan Cache

- Blocking (real-time)

April 8, 2009 11:22 AM
 

John Paul Cook said:

--Largest row counts

SELECT OBJECT_NAME(object_id) AS tableName, sys.dm_db_partition_stats.row_count

FROM sys.dm_db_partition_stats

WHERE index_id < 2

ORDER BY sys.dm_db_partition_stats.row_count DESC

Scripts for evaluating index usage: http://www.mssqltips.com/tip.asp?tip=1545

April 8, 2009 2:04 PM
 

Saggi Neumann said:

/*

object sizes (all dbs, tested on 2000,2005,2008, based on sp_spaceused so it's not 100% accurate if you mess with the statistics)

shows some other important details as well: number of nonclustered indexes, existence of PK and CIX, existence of text or image columns, existence of fulltext index and triggers

*/

SET NOCOUNT ON

create table #tmp1 (TableName sysname, rows bigint, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))

create table #tmp3 (TableName sysname, rows bigint, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50), DBName sysname )

create table #tmp2 (TableName sysname, HasAfterTrigger BIT, HasInsteadOfTrigger BIT, HasTextImage BIT, HasFulltextIndex BIT, HasPrimaryKey BIT, HasClustIndex BIT, DBName sysname, IndexCount INT)

DECLARE @DBName sysname

DECLARE @sql varchar(8000)

CREATE TABLE #dbs (DBName sysname PRIMARY KEY)

INSERT INTO #dbs (DBName)

SELECT name FROM master..sysdatabases WHERE name not in ('tempdb') and

status & 512 <> 512

DECLARE z CURSOR FOR

SELECT DBName FROM #dbs

open z

FETCH NEXT FROM z INTO @DBName

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @sql = '

insert into #tmp1 (TableName, rows, reserved, data, index_size, unused) exec sp_spaceused ''?'';

insert into #tmp3 (TableName, rows, reserved, data, index_size, unused, DBName)

select ''?'', rows, reserved, data, index_size, unused, ''' + @DBName + '''

from #tmp1

truncate table #tmp1

insert into #tmp2

(TableName , HasAfterTrigger , HasInsteadOfTrigger , HasTextImage , HasFulltextIndex , HasPrimaryKey , HasClustIndex, DBName,IndexCount)

select ''?'' AS TableName , objectproperty(object_id(''?''),''HasAfterTrigger'')

,objectproperty(object_id(''?''),''HasInsteadOfTrigger'')

,objectproperty(object_id(''?''),''TableHasTextImage'')

,objectproperty(object_id(''?''),''TableHasActiveFulltextIndex'')

,objectproperty(object_id(''?''),''TableHasPrimaryKey'')

,objectproperty(object_id(''?''),''TableHasClustIndex'')

, ''' + @DBName + '''

,(SELECT count(indid) IndexCount FROM dbo.sysindexes

 WHERE dbo.sysindexes.indid between 2 and 254 AND INDEXPROPERTY(id,name, ''IsStatistics'')=0

AND id = object_id(''?''))

'

SELECT @sql = 'USE ' + QUOTENAME(@DBName) + ';

exec sp_msforeachtable ''' + REPLACE(@sql,'''','''''') + ''',''?'''

exec(@sql)

FETCH NEXT FROM z INTO @DBName

END

CLOSE z

DEALLOCATE z

SET NOCOUNT OFF

select @@ServerName AS ServerName, t0.DBName, t0.TableName, Rows, convert(int,left(reserved, len(reserved)-3)) AS Reserved,

convert(int,left(data, len(data)-3)) AS Data,

convert(int,left(Index_Size, len(Index_Size)-3)) AS Index_Size,

convert(int,left(Unused, len(Unused)-3)) AS Unused,

ISNULL(t1.IndexCount,0) AS NCIndexCount,

HasPrimaryKey, HasClustIndex,

HasTextImage, HasFulltextIndex,

HasAfterTrigger, HasInsteadofTrigger, GETDATE() AS ExecDate

from #tmp3 t0 inner join #tmp2 t1 on t0.TableName = t1.TableName AND t0.DBName = t1.DBName

ORDER BY convert(int,left(data, len(data)-3))+convert(int,left(Index_Size, len(Index_Size)-3)) DESC

drop table #tmp1

drop table #tmp2

drop table #tmp3

drop table #dbs

April 9, 2009 2:32 AM
 

hajji hicham said:

After testing this bunch of code, sp_msforeachtable should be replacede by sp_MSforeachtable, Capital letters for MS is required by SQL SERVER 2005. Beside That, the code works fine and seems to be very helpfull for mining hidden (or forgotton) meta-information within SQL server DB.

Hicham Hajji

Associate Professor

Morocco

April 10, 2009 11:32 AM
 

Michelle Ufford said:

Hi Kevin!  Here's the ones I use pretty regularly:

SQL Performance, 2005/2008

Who is Active? v8.40 by Adam Machanic

http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx

Index Interrogation

sp_helpindex2 by Kimberly Tripp, 2005/2008

http://www.sqlskills.com/blogs/kimberly/post/sp_helpindex2-to-show-included-columns-(20052b)-and-filtered-indexes-(2008)-which-are-not-shown-by-sp_helpindex.aspx

I'm going to toss a few of my favorites in the mix too:

Index Fragmentation, choose either all indexes or specific table, works on both Standard and Enterprise, 2005/2008

http://sqlfool.com/2009/03/automated-index-defrag-script/

Missing Indexes, retrieves stored procs with missing indexes in the cached query plan, 2005/2008

http://sqlfool.com/2009/03/find-missing-indexes/

Missing Indexes DMV query, 2005/2008

http://sqlfool.com/2009/01/index-clean-up-scripts/

Un-Used Indexes DMV query, 2005/2008

http://sqlfool.com/2009/01/index-clean-up-scripts/

View/Disable SQL Agent Jobs, 2005/2008

http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/

April 10, 2009 1:13 PM
 

Saggi Neumann said:

Hi Hajji,

I suppose I haven't tested this script on servers with case sensitive collations - thanks!

April 11, 2009 6:06 AM
 

TiborKaraszi said:

Hi Kevin!

I have my sp_indexinfo at http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp...

April 20, 2009 2:48 PM
 

KKline said:

Anupam Mondal, a DBA in Australia, directly emailed these cool DMV queries to me. --

Here are some useful scripts which I use in our prod environment.

- Find top 30 missing indices on your server

- Script works for 2005/2008. I use reporting services to display the results

SELECT top 30

    'CREATE INDEX IDX_NCLST'

    + CASE WHEN EQUALITY_Columns IS NULL THEN LTRIM('') ELSE  REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(EQUALITY_Columns,',','_'),'[',''),']',''))),CHar(32), '_')  END

    + CASE WHEN INEQUALITY_Columns IS NULL THEN LTRIM('') ELSE REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(INEQUALITY_Columns,',','_'),'[',''),']',''))),CHAR(32), '_') END

    + cast(mid.Index_Handle as varchar)

    + ' ON ' + Statement + ' ('

    + CASE WHEN EQUALITY_Columns IS NULL THEN '' ELSE  REPLACE(REPLACE(EQUALITY_Columns,'[',''),']','') ENd

    + CASE WHEN INEQUALITY_Columns IS NULL THEN ')' ELSE '' END

    + CASE WHEN INEQUALITY_Columns IS NULL THEN '' ELSE + (CASE WHEN EQUALITY_Columns IS NULL THEN '' ELSE  ',' ENd) + INEQUALITY_Columns + ')' ENd

    + CASE WHEN INCluded_Columns IS NULL THEN '' ELSE + ' INCLUDE(' + REPLACE(REPLACE(INCluded_Columns,'[',''),']','') + ')'ENd

FROM sys.dm_db_missing_index_details mid

INNER JOIN sys.dm_db_missing_index_groups mig ON mig.Index_Handle = mid.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.Index_group_handle = migs.group_handle

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

- Find unused indices in your database

- Script works for 2005/2008. Again a reporting services report is used where the database name can be selected from a dropdown list to generate the report for the specific database.

DECLARE @sql varchar(2000) ,@DatabaseName varchar(20)

SET @DatabaseName = @dbname

SET @sql = 'USE ' + @DatabaseName

SET @sql = @sql + '; SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,

sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates

FROM sys.dm_db_index_usage_stats sis

INNER JOIN sys.indexes si             ON   sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID AND (si.is_disabled = 0 AND si.is_primary_key=0)

INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID

INNER JOIN sys.columns sc            ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID

WHERE

         sis.Database_ID = DB_ID(''' + @DatabaseName + ''')

AND       (User_Seeks = 0 AND user_scans=0 and user_lookups=0)

AND  si.type=2 AND si.is_unique = 0 AND si.is_primary_key = 0 AND is_disabled = 0

ORDER BY TableName'

EXEC (@sql)

- Index maintenance script for SQL 2005/2008

- This script either reorganises or rebuilds the indexes based on the @targetPercentFragmentation

- It also rebuilds the index online in the case where a table does not have any text, ntext, image, varchar(max) and nvarchar(max) columns

- I have 2 different versions of the script. A batch file is used in deploying the script as a SQL agent job and also based on version of SQL server, it deploys the appropriate version. Version1 is deployed to Enterprise editions only

Version1:

SET NOCOUNT ON

DECLARE         @targetPercentFragmentation          smallint

DECLARE         @SQL                                 varchar(8000)

DECLARE         @cnt                                  int

DECLARE         @maxCount                            int

CREATE TABLE #SQLcommand (rowId int identity, sqltext varchar(8000))

SELECT    

    @targetPercentFragmentation          =    20;  --CHANGE THIS VALUE IF YOU WANT TO INCREASE THE THRESHOLD FOR AVG PERCENT FRAGMENTATION

WITH CTE_IndexStats ( ObjectName, IndexName, Index_type_desc,alloc_unit_type_desc, avg_fragmentation_in_percent, avg_fragment_size_in_pages)

AS

(

select  TOP 100 PERCENT

    QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(a.object_id)) ObjectName,

    QUOTENAME(i.name) as IndexName,

    index_type_desc,

    alloc_unit_type_desc,

    avg_fragmentation_in_percent,

    avg_fragment_size_in_pages

from

    sys.dm_db_index_physical_stats(db_id(db_name()), NULL ,NULL, NULL, NULL) A

    INNER JOIN      sys.objects o   ON o.object_id = A.object_id    AND o.type='U'

    INNER JOIN      sys.indexes i   ON i.object_id = a.object_id    AND i.index_id = a.index_id

    INNER JOIN      sys.schemas s   ON s.schema_id = o.schema_id

    AND        OBJECTPROPERTY(a.object_id, 'ISMSSHIPPED') = 0

    AND        i.name IS NOT NULL

Order By 1 asc

)

INSERT #SQLcommand(SQLTEXT)

SELECT

CASE  WHEN (

          SELECT count(t.name)

          FROM sys.objects o

          INNER JOIN sys.columns c   ON   o.object_id = c.object_id AND object_id(CTE_IndexStats.objectName) = o.object_id

          INNER JOIN sys.types t          ON c.system_type_id = t.system_type_id AND (t.name IN ('text', 'image', 'ntext') OR c.max_length=-1)) > 0  

    THEN 'ALTER INDEX ' + IndexName + ' ON ' + objectName + ' REBUILD WITH ( ONLINE = OFF)' + CHAR(13)

    ELSE 'ALTER INDEX ' + IndexName + ' ON ' + objectName + ' REBUILD WITH ( ONLINE = ON)' + CHAR(13)

END as SQLTEXT

FROM CTE_IndexStats

WHERE

    alloc_unit_type_desc <> 'LOB_DATA'   AND

    avg_fragmentation_in_percent > @targetPercentFragmentation

UNION ALL

SELECT

    'ALTER INDEX ' + IndexName + ' ON ' + objectName + ' REORGANIZE' + CHAR(13)

    as SQLTEXT

FROM CTE_IndexStats

WHERE

    alloc_unit_type_desc <> 'LOB_DATA'   AND

    avg_fragmentation_in_percent BETWEEN 5 AND @targetPercentFragmentation;

SELECT @maxCount = max(RowID), @cnt=1 FROM #SQLcommand

WHILE (@cnt <= @maxCount)

BEGIN

    SELECT @SQL = SQLTEXT FROM #SQLcommand WHERE RowId = @cnt

    EXEC (@SQL)

    IF (SELECT DATABASEPROPERTY(db_name(),'ISTruncLog')) = '1'

    BEGIN

          SET @SQL = 'BACKUP LOG [' + db_name() + '] WITH NO_LOG '

          EXEC (@SQL)

    END

    SET @cnt = @cnt+1

END

DROP TABLE #SQLcommand

Version2:

SET NOCOUNT ON

DECLARE         @targetPercentFragmentation          smallint

DECLARE         @SQL                                 varchar(8000)

DECLARE         @cnt                                 int

DECLARE         @maxCount                            int

CREATE TABLE #SQLcommand (rowId int identity, sqltext varchar(8000))

SELECT    

    @targetPercentFragmentation          =    20;  --CHANGE THIS VALUE IF YOU WANT TO INCREASE THE THRESHOLD FOR AVG PERCENT FRAGMENTATION

WITH CTE_IndexStats ( ObjectName, IndexName, Index_type_desc,alloc_unit_type_desc, avg_fragmentation_in_percent, avg_fragment_size_in_pages)

AS

(

select  TOP 100 PERCENT

    QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(a.object_id)) ObjectName,

    QUOTENAME(i.name) as IndexName,

    index_type_desc,

    alloc_unit_type_desc,

    avg_fragmentation_in_percent,

    avg_fragment_size_in_pages

from

    sys.dm_db_index_physical_stats(db_id(db_name()), NULL ,NULL, NULL, NULL) A

    INNER JOIN      sys.objects o   ON o.object_id = A.object_id    AND o.type='U'

    INNER JOIN      sys.indexes i   ON i.object_id = a.object_id    AND i.index_id = a.index_id

    INNER JOIN      sys.schemas s   ON s.schema_id = o.schema_id

    AND        OBJECTPROPERTY(a.object_id, 'ISMSSHIPPED') = 0

    AND        i.name IS NOT NULL

Order By 1 asc

)

INSERT #SQLcommand(SQLTEXT)

SELECT

'ALTER INDEX ' + IndexName + ' ON ' + objectName + ' REBUILD WITH ( ONLINE = OFF)' + CHAR(13)

FROM CTE_IndexStats

WHERE

    alloc_unit_type_desc <> 'LOB_DATA'   AND

    avg_fragmentation_in_percent > @targetPercentFragmentation

UNION ALL

SELECT

'ALTER INDEX ' + IndexName + ' ON ' + objectName + ' REORGANIZE' + CHAR(13)

FROM CTE_IndexStats

WHERE

    alloc_unit_type_desc <> 'LOB_DATA'   AND

    avg_fragmentation_in_percent BETWEEN 5 AND @targetPercentFragmentation;

SELECT @maxCount = max(RowID), @cnt=1 FROM #SQLcommand

WHILE (@cnt <= @maxCount)

BEGIN

    SELECT @SQL = SQLTEXT FROM #SQLcommand WHERE RowId = @cnt

    EXEC (@SQL)

    IF (SELECT DATABASEPROPERTY(db_name(),'ISTruncLog')) = '1'

    BEGIN

          SET @SQL = 'BACKUP LOG [' + db_name() + '] WITH NO_LOG '

          EXEC (@SQL)

    END

    SET @cnt = @cnt+1

END

DROP TABLE #SQLcommand

- Getting CPU and IO usage data. I store the data in a table and generate CPU and IO trend graphs using reporting services

- SQL 2005/2008 compatible

INSERT Admin.dbo.CPUIO (dbname, usage, dateCaptured,Type, PercentShare)

SELECT

    db_name,

    sum(total_worker_time) as CPU_Usage,

    getdate() as date_captured,

    'CPU' as [Type],

    CAST(sum(total_worker_time) as numeric(25,12)) / (

          SELECT CAST(sum(total_worker_time) as numeric(25,12)) FROM

               (

                    select  total_worker_time

                    from sys.dm_exec_query_stats s1

                    cross apply sys.dm_exec_sql_text(sql_handle) as  s2

               ) as B) * 100 as PercentShare

FROM (

select  total_worker_time

,       case when db_name(dbid ) is null then 'Adhoc Queries' else  db_name(dbid) end as db_name

,       dbid

,       1 as state

,       1 as msg  

from sys.dm_exec_query_stats s1

cross apply sys.dm_exec_sql_text(sql_handle) as  s2

) as A

GROUP BY db_name

ORDER BY 2 desc

INSERT Admin.dbo.CPUIO (dbname, usage, dateCaptured,Type, PercentShare)

SELECT

    db_name,

    sum(total_io) as IO_Usage,

    getdate() as date_captured,

    'IO' as [Type],

    cast(sum(total_io) as numeric(25,12))/(select cast(sum(total_io) as numeric(25,12)) FROM

    (

          select total_logical_reads + total_logical_writes as  total_io

          from sys.dm_exec_query_stats s1

          cross apply sys.dm_exec_sql_text(sql_handle) as  s2

          ) as b)*100 as percentShare

FROM (

select total_logical_reads + total_logical_writes as  total_io

,       case when db_name(dbid) is null then 'Adhoc Queries' else  db_name(dbid) end as db_name

,       1 as severity

,       1 as state

,       1 as msg  

from sys.dm_exec_query_stats s1

cross apply sys.dm_exec_sql_text(sql_handle) as  s2

) as A

GROUP BY db_name

ORDER BY 2 desc

May 6, 2009 6:47 PM
 

Kevin Kline said:

Before I jump onto the Goals and Themeword meme started by my buddy, Thomas LaRock ( blog | twitter ),

January 5, 2010 9:43 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement