SP; search Query plan cache for issues, like table scans, missing indexes or stats, or any other command
DBA blog
zondag 2 maart 2014
What is the Used, Free and Reserved space per DB / LOG
What is the Used, Free and Reserved space per DB / LOG
zaterdag 1 maart 2014
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]
[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]
[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]
[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]
[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]
[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]
Abonneren op:
Posts (Atom)