SP; search Query plan cache for issues, like table scans, missingindexes or stats

SP; search Query plan cache for issues, like table scans, missing indexes or stats, or any other command

What is the Used, Free and Reserved space per DB / LOG

What is the Used, Free and Reserved space per DB / LOG

Relative load per DB in CPU, IO and CLR

What is the relative load per DB in CPU, IO and CLR?

[sql]
--****************************************************************************************
-- What is the relative load per DB in CPU, IO and CLR
--****************************************************************************************
-- Versie: 1.0
-- Author: Theo Ekelmans
-- Date: 2009-06-11
--
--****************************************************************************************
USE master
SELECT a.[value] AS [dbid]
, ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource') AS [DB Name]
, SUM(qs.[execution_count]) AS [Counts]
, SUM(qs.[total_worker_time]) / 1000 AS [Total Worker Time (mSecs)]
, SUM(qs.[total_physical_reads]) AS [Total Physical Reads]
, SUM(qs.[total_logical_reads]) AS [Total Logical Reads]
, SUM(qs.[total_logical_writes]) AS [Total Logical Writes]
, SUM(qs.[total_clr_time]) / 1000 AS [Total CLR Time (mSecs)]
, SUM(qs.[total_elapsed_time]) / 1000 AS [Total Elapsed Time (mSecs)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.Plan_handle) AS a
WHERE a.[attribute] = 'dbid'
GROUP BY [value], ISNULL(DB_NAME(CONVERT(INT,a.[value])),'Resource')
ORDER BY [Total Worker Time (mSecs)] DESC
[/sql]

Actual (sparse) file size on disk of a snapshot

What is the actual (sparse) file size on disk of a snapshot?

[sql]
--****************************************************************************************
-- What is the actual (sparse) file size on disk of a snapshot?
--****************************************************************************************
-- Versie: 1.0
-- Author: Theo Ekelmans
-- Date: 2008-11-01
--
--****************************************************************************************
SELECT
DB_NAME(mf.database_id) AS Database_Name
, mf.name AS Logical_Name
, mf.Physical_Name
, mf.FILE_ID
, CAST((vfs.size_on_disk_bytes)/(1024.00*1024) AS NUMERIC(18,2)) AS Size_on_Disk_MB
, CAST(mf.size/1024.00 AS NUMERIC(18,2)) AS Size_in_MB
, mf.max_size
, CAST(mf.growth/1024.00 AS NUMERIC(18,2)) AS Growth_in_MB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id AND mf.FILE_ID=vfs.FILE_ID
WHERE mf.database_id = DB_ID('DbName_snapshot')
[/sql]

Determine the page life expectancy per NUMA node

Determine the page life expectancy per NUMA node (is it balanced?)

[sql]
--****************************************************************************************
-- Determine the page life expectancy per NUMA node (is it balanced?)
--****************************************************************************************
-- Versie: 1.0
-- Author: Theo Ekelmans
-- Date: 2014-01-22
--
--****************************************************************************************

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT ple.[NUMA]
, LTRIM(STR([PageLife_Sec] / 3600)) + ':' + REPLACE(STR([PageLife_Sec] % 3600 / 60, 2), SPACE(1), '0') + ':' + REPLACE(STR([PageLife_Sec] % 60, 2), SPACE(1), '0') AS 'PageLife'
, CONVERT(decimal(15, 0), opc.[DatabasePages] * 0.0078125) AS 'BufferPool_MB'
, CONVERT(decimal(15, 0), opc.[DatabasePages] * 0.0078125 / [PageLife_Sec]) AS 'BufferPool_Delta_MB_Sec'
FROM (
SELECT [instance_name] AS 'NUMA'
, [cntr_value] AS 'PageLife_Sec'
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Page life expectancy') AS ple
INNER JOIN (
SELECT [instance_name] AS 'NUMA'
, [cntr_value] AS 'DatabasePages'
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Database pages')AS opc
ON ple.[NUMA] = opc.[NUMA]

[/sql]

Disk usage per DB per Driveletter pivot

Displays the IO in bytes per database per drive

[table id=3 /]


[sql]
/*
--****************************************************************************************
-- procedure spReportDiskUsagePerDBPerDriveLetter
--****************************************************************************************
-- reports the diskusage per driveletter in a pitvotted form
--****************************************************************************************
-- Versie: 1.0
-- Author: Theo Ekelmans
-- Date: 2010-03-22
--
--****************************************************************************************
IF OBJECT_ID('tempdb..#tmpThEk') IS NOT NULL DROP TABLE #tmpThEk;

create table #tmpThEk (Dname sysname null, DrvName sysname null, RW Bigint null);

With g as
( select db_name(mf.database_id) as database_name,
left(mf.physical_name, 1) as drive_letter,
coalesce(vfs.num_of_bytes_read + vfs.num_of_bytes_written, 0) as BYTES
from sys.master_files mf
inner join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
where mf.database_id > 4 -- With or withoiut the system DB's
)
insert into #tmpThEk (Dname,DrvName,RW)
select database_name,
drive_letter,
BYTES
-- Percentage = BYTES*100.0/(SELECT SUM(BYTES) FROM g) -- If you so desite you can use overall percentage
from g

-- Find all permutations of DrvName
DECLARE @DrvNames AS TABLE(DrvName sysname NOT NULL PRIMARY KEY)
INSERT INTO @DrvNames SELECT DISTINCT DrvName FROM #tmpThEk


-- Build a dynamic IN clause based on @DrvNames
DECLARE @SelCols AS nvarchar(MAX),
@InCols AS nvarchar(MAX),
@DrvName AS nvarchar(10)
SET @DrvName = (SELECT MIN(DrvName) FROM @DrvNames)
SET @SelCols = N''
SET @InCols = N''
WHILE @DrvName IS NOT NULL
BEGIN
SET @SelCols = @SelCols + N',coalesce(['+@DrvName+N'], 0) as '+@DrvName+N' '
SET @InCols = @InCols + N',['+@DrvName+N']'
SET @DrvName = (SELECT MIN(DrvName) FROM @DrvNames WHERE DrvName > @DrvName)
END
SET @SelCols = SUBSTRING(@SelCols, 2, LEN(@SelCols))
SET @InCols = SUBSTRING(@InCols, 2, LEN(@InCols))

-- Dynamic build of the pivot statement.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N' SELECT Dname, ' + @SelCols + N'
FROM (SELECT Dname, DrvName, RW FROM #tmpThEk) AS D
PIVOT(sum(RW) FOR DrvName IN(' + @InCols + N')) AS P'

-- Show me the good stuff :)
EXEC sp_executesql @sql

[/sql]

Diskspace Free and Total overview

Displays the free space,free space percentage plus total drive size for a server

[table id=2 /]


[sql]
/*
Description : Displays the free space,free space percentage plus total drive size for a server
Author : Theo Ekelmans
Version/Date : 1.0 2013-08-23

--Preparation
exec sp_configure 'show advanced options', 1
reconfigure

exec sp_configure 'Ole Automation Procedures', 1
reconfigure

*/
SET NOCOUNT ON

DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive

DROP TABLE #drives

[/sql]

CPU history (256 minutes)

Show the CPU load in last 256 minutes

[sql]
/*
Description : Show the CPU load in last 256 minutes
Note : For SQL 2012 replace "cpu_ticks / 1000" by "cpu_ticks_in_ms / 1000"
Author : Theo Ekelmans
Version/Date : 1.0 2013-03-23
*/


SELECT Dateadd(ms, -1 * ( cpu_ticks / ( cpu_ticks / ms_ticks ) - [timestamp] ), Getdate()) AS DT
,CAST(( 1.0 * ms_ticks / ( 24 * 60 * 60 * 1000 ) ) AS DECIMAL(10, 2)) AS system_uptime_days
,cpu_count
,cpu_ticks / 1000 AS cpu_MHz
,hyperthread_ratio
,SQLProcessUtilization
,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
,SystemIdle
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
,timestamp
FROM (SELECT
--top 1
timestamp
,CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
--order by timestamp desc
) AS x) AS y,
sys.dm_os_sys_info
ORDER BY timestamp DESC


[/sql]

Plan cache per DB

How much memory each DB is using for sql plan cache?

[sql]
/*
Description : How much memory each DB is using for sql plan cache?
Author : Theo Ekelmans
Version/Date : 1.0 2011-03-23
*/

SELECT name = DB_NAME(t.[dbid])
, plan_size_MB = CONVERT(decimal(12, 2), SUM(CAST(p.size_in_bytes AS bigint)) / 1024.0 / 1024.0)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)AS t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY plan_size_MB DESC;
[/sql]

Buffer cache per DB (Overview & Detailed)

Ever wondered how much memory each DB is using for buffering data?

[sql]
/*
Description : Ever wondered how much memory each DB is using for buffering data?
Author : Theo Ekelmans
Version/Date : 1.0 2011-03-23
*/

select coalesce(db_name(database_id), 'System') as 'DbName'
,sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) as 'MBClean'
,sum(case dirty when 1 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) as 'MBDirty'
from (
select database_id
,[is_modified] as 'Dirty'
,count(page_id) as 'pages'
from sys.dm_os_buffer_descriptors
group by database_id
,[is_modified]
) as t
group by database_id
order by sum(case dirty when 0 then cast((pages * 8192.0 ) / 1024.0 / 1024.0 as numeric (17,2)) else 0 end) desc
[/sql]

And in detail

[sql]
declare @nRows int
set @nRows = 25

set nocount on

declare @qs_results table
( database_id int
, objectname sysname null
, indexname sysname null
, cache_kb bigint
, free_bytes bigint
, percentage_cache numeric(10,5) null
, size_kb bigint null
, percentage_object numeric(10,5) null
, filegroup sysname null
, indid int null
, dirty_kb bigint null
, percentage_dirty numeric(10,5) null
, schema_name sysname null
, user_name sysname null
)

declare @qs_db table
( database_id int
, name sysname null
, id int identity
)

insert into @qs_db (database_id, name)
select database_id, name
from sys.databases
where user_access <> 1 -- NOT SINGLE USER
and state = 0 -- ONLINE
and has_dbaccess(name) <> 0 -- Have Access.


declare @nBufferSize int

select @nBufferSize = count(*)
from sys.dm_os_buffer_descriptors with (readpast)

declare @sql nvarchar(max)
declare @n int
set @n = 1
declare @db int
set @db = 0

while 1=1
begin
set @db = null
select @db = database_id from @qs_db where id = @n
set @n = @n + 1

if @db is null --We're done
break

if @db = 0x7FFF -- Skip this one.
continue

set @sql= 'use ' + quotename(db_name(@db)) + '
select db_id() database_id
, isnull(o.name,''<in-memory-resource>'') object_name
, isnull(i.name,'''') index_name
, 8.0*sum(b.cache_pages) cache_kb
, sum(b.free_bytes) free_bytes
, 8.0*sum(a.total_pages) used_kb
, (select top 1 name from sys.filegroups fg with (readpast) where fg.data_space_id = a.data_space_id) filegroup
, min(i.index_id) indid
, 8.0*sum(b.dirty_pages) dirty_kb
, min(s.name) schema_name
, min(u.name) user_name
from (
select a.database_id
, allocation_unit_id
, count(*) cache_pages
, sum(free_space_in_bytes) free_bytes
, sum(case when is_modified=1 then 1 else 0 end) dirty_pages
from sys.dm_os_buffer_descriptors a with (readpast)
where a.database_id = db_id()
group by a.database_id,allocation_unit_id
) b
left outer join sys.allocation_units a with (readpast) on b.allocation_unit_id = a.allocation_unit_id
left outer join sys.partitions p with (readpast) on (a.container_id = p.hobt_id and a.type in (1,3) )
or (a.container_id = p.partition_id and a.type = 2 )
left outer join sys.objects o with (readpast) on p.object_id = o.object_id
left outer join sys.indexes i with (readpast) on p.object_id = i.object_id and p.index_id = i.index_id
left outer join sys.schemas s with (readpast) on o.schema_id = s.schema_id
left outer join sys.database_principals u with (readpast) on s.principal_id = u.principal_id
where database_id = db_id()
and a.data_space_id is not null
group by a.data_space_id, isnull(o.name,''<in-memory-resource>''), isnull(i.name,'''')
option (keepfixed plan)'
insert into @qs_results
( database_id
, objectname
, indexname
, cache_kb
, free_bytes
, size_kb
, filegroup
, indid
, dirty_kb
, schema_name
, user_name
)
exec(@sql)
end

insert into @qs_results (database_id, schema_name, user_name, cache_kb, free_bytes, dirty_kb)
select a.database_id
, 'system'
, 'system'
, 8.0*count(*) cache_pages
, sum(free_space_in_bytes) free_bytes
, sum(case when is_modified=1 then 1 else 0 end) dirty_pages
from sys.dm_os_buffer_descriptors a with (readpast)
where a.database_id = 0x7FFF
group by a.database_id,allocation_unit_id
option (keepfixed plan)

set rowcount @nRows
set nocount off

select DBName
, TBOwner
, TBName
, IXName
, SizeInCacheKB
--, Pinned
--, PinnedSizeKB
, case
when PercentageOfCache > 100 then 100
when PercentageOfCache < 0 then 0
else PercentageOfCache
end PercentageOfCache
, ObjectSizeKB
, case
when PercentageOfObject > 100 then 100
when PercentageOfObject < 0 then 0
else PercentageOfObject
end PercentageOfObject
, FileGroup
, indid
, DirtyKB
, case
when PercentageObjectDirty > 100 then 100
when PercentageObjectDirty < 0 then 0
else PercentageObjectDirty
end PercentageObjectDirty
, AllocateCacheUnusedKB
from (
select case when database_id = 0x7FFF then 'mssqlsystemresource' else db_name(database_id) end DBName
, isnull(user_name,'system') TBOwner
, objectname TBName
, indexname IXName
, cache_kb SizeInCacheKB
--, null Pinned
--, null PinnedSizeKB
, case when @nBufferSize = 0 then 0.0 else 100.0*(cache_kb/8.0)/@nBufferSize end PercentageOfCache
, size_kb ObjectSizeKB
, case when size_kb = 0 then 0.0 else ((cache_kb*1.0)*100.0)/size_kb end PercentageOfObject
, filegroup FileGroup
, indid
, dirty_kb DirtyKB
, case when size_kb = 0 then 0.0 else dirty_kb*100.0/size_kb end PercentageObjectDirty
, free_bytes/1024.0 AllocateCacheUnusedKB -- NEWCOlumn
from @qs_results
) x
order by PercentageOfCache desc
[/sql]

Yet another new SQL script Blog?

Hi there,

Welcome to my SQL blog, and yes, I know..... there are many blogs posting SQL scripts but this one is mine. It is focused on the handy scripts and snippets that DBA's can use on a daily basis for maintaining SQL servers, gathering info, performance problem solving, etc.

The scipts on this blog are the ones that i actually used. Some are my own, some are based on snippets i have found out on the net, and some are just plane reposts.

I presume that you have been a DBA for some time,  therefor the scripts don't come with much documentation, but with an open invitation to comment or email me if you want clarification on something in a post.

Have fun browsing!

Theo