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

 

Sample Page

This is an example page. It's different from a blog post because it will stay in one place and will show up in your site navigation (in most themes). Most people start with an About page that introduces them to potential site visitors. It might say something like this:

Hi there! I'm a bike messenger by day, aspiring actor by night, and this is my blog. I live in Los Angeles, have a great dog named Jack, and I like piña coladas. (And gettin' caught in the rain.)

...or something like this:

The XYZ Doohickey Company was founded in 1971, and has been providing quality doohickies to the public ever since. Located in Gotham City, XYZ employs over 2,000 people and does all kinds of awesome things for the Gotham community.

As a new WordPress user, you should go to your dashboard to delete this page and create new pages for your content. Have fun!

Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!