SQL Server Monitoring
[A] – Database object
last used details
[B] – Memory usage - Memory pages loaded
for each database
[C1] – Memory usage - Memory pages
loaded for each object in the current DB
[C2] – Queries using most I/O
[D] - Find the size of all Indexes in a database
[E1] – Find unused jobs
[E2] – Jobs details
[F] – Long running SQL Agent jobs
[G] – Object modified in last 10 days
[H] – Drive space monitoring
[I] – Failed Job monitoring
[J] – List of Tables – Size – No of rows
[K] – SQL Process details with Query Text
[L] – Sp_Who5
[E1] – Find unused jobs
[E2] – Jobs details
[F] – Long running SQL Agent jobs
[G] – Object modified in last 10 days
[H] – Drive space monitoring
[I] – Failed Job monitoring
[J] – List of Tables – Size – No of rows
[K] – SQL Process details with Query Text
[L] – Sp_Who5
[A] – Database object last used details
Basically it's a DBA’s responsibility to maintain the
database server cleanup. Most probably a developer or some application owner
will ask to restore the UAT database on the production server for some reason, forgetting
to drop the same. When the audit starts our auditor asks why the unwanted
database or objects are present on production server. In that case it is very
difficult to determine which database is used or not from potentially hundreds
of databases.
To resolve such type of issue I have implemented following
stored procedure which, provides the object wise last used date of all
databases which are available in single instance.
How to Use:
Create a “Database_Object_Last_used_details” store procedure
on your master database
And execute the same whenever you want to fetch details
Create procedure [dbo].[Database_Object_Last_used_details]
As
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnusedObjectlist]') AND type in (N'U'))
DROP TABLE [dbo].[UnusedObjectlist]
CREATE TABLE [dbo].[UnusedObjectlist](
ObjectName varchar(50),
ObjectType varchar(25),
ServerName varchar(20),
DatabaseName varchar(20),
CreatedDate Datetime,
ModifyDate datetime,
last_usedDate datetime
)
declare
@CountDataBase as int
set
@CountDataBase = 0
set
@CountDataBase = (SELECT count(name) FROM master..sysdatabases where
name not in ('master','tempdb','msdb','mode'))
Declare
@GetDataBase as table ([Id] [bigint] IDENTITY(1,1) NOT NULL,DBName varchar(20))
insert into @GetDataBase (DBName) (SELECT name FROM master..sysdatabases where
name not in ('master','tempdb','msdb','mode'))
declare
@StartCounter as int
set
@StartCounter = 1
while(@StartCounter
<= @CountDataBase)
begin
declare @DBName as varchar(20)
set @DBName = ''
set @DBName = (select DBName from @GetDataBase where
Id = @StartCounter)
declare @Query1 nvarchar(Max)
set @Query1 = 'insert into
UnusedObjectlist
select
distinct(name),type_desc,' + '''' + @@SERVERNAME + '''' + ',' + '''' + @DBName + '''' + ',create_date,modify_date,max(last_user_update)
as last_used
from ' + @DBName + '.sys.objects a left outer join ' + @DBName + '.sys.dm_db_index_usage_stats b
on a.object_id
= b.object_id
where type_desc IN
(' + '''' + 'USER_TABLE' + '''' + ',' + '''' + 'VIEW' + '''' + ',' + '''' + 'SQL_SCALAR_FUNCTION'
+ '''' + ',' + '''' + 'SQL_STORED_PROCEDURE'
+ '''' + ',' + '''' + 'SQL_SCALAR_FUNCTION' +
'''' + ')
group by
name,create_date,modify_date,type_desc
order by 4 desc '
exec (@Query1)
declare @Query2 nvarchar(Max)
set @Query2 =
'
update
UnusedObjectlist set last_usedDate = b.last_execution_time
FROM ' + @DBName + '.sys.objects a left outer join ' + @DBName + '.sys.dm_exec_procedure_stats b
on a.object_id = b.object_id
join
UnusedObjectlist c on a.name collate SQL_Latin1_General_CP1_CI_AS =
c.ObjectName
where
a.type_desc=' + ''''
+ 'SQL_STORED_PROCEDURE'
+ '''' +
' and
b.last_execution_time is not null
and
c.last_usedDate is null '
exec(@Query2)
set
@StartCounter = @StartCounter + 1
end
END
select * from
UnusedObjectlist
GO
[B] – Memory usage -
Memory pages loaded for each database
SELECT COUNT(*)AS cached_pages_count
,CASE database_id
WHEN
32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
[C1] – Memory usage -
Memory pages loaded for each object in the current DB
SELECT COUNT(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS
bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id
,allocation_unit_id
FROM sys.allocation_units AS
au
INNER
JOIN sys.partitions AS p
ON
au.container_id =
p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS
au
INNER
JOIN sys.partitions AS p
ON
au.container_id =
p.partition_id
AND
au.type = 2
) AS obj
ON bd.allocation_unit_id =
obj.allocation_unit_id
WHERE
database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;
[C2] – Queries using most I/O
/*
The query here
selects the 10 queries that use the most I/O, across all databases on the
server. The query calculates the Total IO by summing the columns
total_logical_reads and total_logical_writes. Similarly, the Average IO is
calculated by dividing the sum of the total_logical_reads and
total_logical_writes by the number of times the query has executed
(execution_count).The other column outputs are DatabaseName, Individual Query,
Parent Query,and the query’s cached plan (query_plan). The output is sorted by
Total IO in descending order.
*/
SELECT TOP 10
[Total IO] = (qs.total_logical_reads
+ qs.total_logical_writes)
, [Average
IO] = (qs.total_logical_reads
+ qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((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) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC
[D] - Find the size of all Indexes in a database
OR NextRun < GETDATE()
[E1] – Find unused jobs
SELECT @@SERVERNAME
SvrName, J.Name, J.[Enabled], JA.LastRun, JA.NextRun, JV.[description], JC.Name JobCategory
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobs_view JV
ON J.Job_ID = JV.Job_ID
JOIN
(
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
FROM msdb.dbo.sysjobactivity
GROUP BY Job_Id
) JA
ON J.Job_ID = JA.Job_ID
JOIN msdb.dbo.syscategories JC
ON J.Category_ID = JC.category_id
WHERE DATEDIFF(m, ISNULL(LastRun, '1900-01-01'), GETDATE()) > 12
OR NextRun < GETDATE()
[E2] – Jobs details
USE msdb
GO
SELECT distinct
s.[name]
,s.[description]
,dbo.SQLAGENT_SUSER_SNAME (s.owner_sid) AS 'Job Owner'
--,sp.NAME AS
'JobOwner'
,s.[enabled]
,s.date_created
,s.date_modified
--,sjs.next_run_date
,
JobFrequency = CASE
freq_type
WHEN 1 THEN 'One time only'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to
freq_interval'
WHEN 64 THEN 'Runs when the SQL
Server Agent service starts'
WHEN 128 THEN 'Runs when the computer
is idle'
ELSE 'Unknown'
END
,JobInterval
= CASE
WHEN
freq_type = 8 THEN
CASE
WHEN
freq_interval = 1 THEN
'Sunday'
WHEN
freq_interval = 2 THEN
'Monday'
WHEN
freq_interval = 4 THEN
'Tuesday'
WHEN
freq_interval = 8 THEN
'Wednesday'
WHEN
freq_interval = 16 THEN
'Thursday'
WHEN
freq_interval = 32 THEN
'Friday'
WHEN
freq_interval = 64 THEN
'Saturday'
ELSE
CASE
WHEN
freq_type = 32 THEN
CASE
WHEN freq_interval = 1 THEN 'Sunday'
WHEN freq_interval = 2 THEN 'Monday'
WHEN freq_interval = 3 THEN 'Tuesday'
WHEN freq_interval = 4 THEN 'Wednesday'
WHEN freq_interval = 5 THEN 'Thursday'
WHEN freq_interval = 6 THEN 'Friday'
WHEN freq_interval = 7 THEN 'Saturday'
WHEN
freq_interval = 8 THEN
'Day'
WHEN freq_interval = 9 THEN 'Weekday'
WHEN freq_interval = 10 THEN 'Weekend day'
ELSE
CASE
WHEN
freq_type = 1 THEN
'Once'
ELSE
CASE
WHEN
freq_type = 4 THEN
'Everyday'
ELSE 'Unknown'
END
END
END
END
END
END
FROM dbo.sysjobs s
LEFT JOIN dbo.sysjobschedules
sjs ON s.job_id
= sjs.job_id
LEFT JOIN MASTER.sys.server_principals
sp ON s.owner_sid
= sp.sid
LEFT JOIN sysschedules ss on
sjs.schedule_id =
ss.schedule_id
WHERE description NOT LIKE 'This job is owned by a
report server%'
[F] – Long running SQL Agent jobs
//*
This script will
list long running SQL agent jobs and output the results into a HTML formatted
email. I have this set up as a SQL Agent job which runs on every Monday morning
against our major severs. The results
are then emailed to me.
To use script a
number of local variables will need to be set-up (ie email server info, search
window size, duration time, etc.). Please refer to the comments in the code for
further details.
*//
USE [MSDB]
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
-----------------------------------------------------------------------------
--
Description:List long runng SQL agent Jobs/steps
-- Please note, the following local
variables need to be set...
-- @NumberOfDaysToGoBack
-- @EmailList
-- @EmailFormat
-- @EmailProfile
-- @DurationTime
-- Amendments:
------------------------------------------------------------------------------------------------------------
-- Locals
------------------------------------------------------------------------------------------------------------
DECLARE
@GroundZeroDate INT;
DECLARE @XML NVARCHAR(MAX);
DECLARE @Body NVARCHAR(MAX);
DECLARE @Subject NVARCHAR(MAX);
DECLARE @EmailList
NVARCHAR(MAX);
DECLARE
@EmailFormat NVARCHAR(32);
DECLARE
@NumberOfDaysToGoBack SMALLINT;
DECLARE
@EmailProfile NVARCHAR(256);
DECLARE
@DurationTime INT;
DECLARE
@PreviousDate DATETIME;
------------------------------------------------------------------------------------------------------------
-- Initialize
------------------------------------------------------------------------------------------------------------
SET
@NumberOfDaysToGoBack = 7; -- Set accordingly.
SET @EmailList
= ‘abc.xyz@pqr_mail.com’;-- Set accordingly.
SET
@EmailFormat = 'HTML';-- Set accordingly.
SET
@EmailProfile = 'Apps_Dev';-- Set accordingly.
SET
@DurationTime = 1000;-- Set accordingly. NOTE, 1000 translates 10:00 minutes
SET
@PreviousDate = GETDATE() -
@NumberOfDaysToGoBack;
SET
@GroundZeroDate = (YEAR(@PreviousDate) * 100 + MONTH(@PreviousDate)) * 100 + DAY(@PreviousDate);
------------------------------------------------------------------------------------------------------------
-- Find long
running job based on the above criteria and save in XML style
------------------------------------------------------------------------------------------------------------
SET @XML = CAST((
SELECT
DISTINCT REPLACE(CASE
WHEN LEN(CAST(h.run_duration AS NVARCHAR(256))) > 5
THEN ''
WHEN LEN(CAST(h.run_duration AS NVARCHAR(256))) = 5
THEN '0'
ELSE '00'
END + REVERSE(SUBSTRING(REVERSE(CAST(h.run_duration AS NVARCHAR(256))), 5, LEN(CAST(h.run_duration AS NVARCHAR(256))))) + ':' + REVERSE(SUBSTRING(REVERSE(CAST(h.run_duration AS NVARCHAR(256))), 3, 2)) + ':' + REVERSE(SUBSTRING(REVERSE(CAST(h.run_duration AS NVARCHAR(256))), 1, 2)), '::', ':00:') AS 'td'
,''
,j.[name] AS 'td'
,''
,h.run_date AS 'td'
,''
,REPLACE(CASE
WHEN LEN(CAST(h.run_time AS NVARCHAR(256))) > 5
THEN ''
WHEN LEN(CAST(h.run_time AS NVARCHAR(256))) = 5
THEN '0'
ELSE '00'
END + REVERSE(SUBSTRING(REVERSE(CAST(h.run_time AS NVARCHAR(256))), 5, LEN(CAST(h.run_time AS NVARCHAR(256))))) + ':' + REVERSE(SUBSTRING(REVERSE(CAST(h.run_time AS NVARCHAR(256))), 3, 2)) + ':' + REVERSE(SUBSTRING(REVERSE(CAST(h.run_time AS NVARCHAR(256))), 1, 2)), '::', ':00:') AS 'td'
,''
,h.[message] AS 'td'
FROM
msdb.dbo.sysjobhistory
h
INNER
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER
JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
WHERE
h.run_date >=
@GroundZeroDate
AND
h.run_duration >=
@DurationTime
AND
h.step_id = 0
ORDER
BY 1 DESC
FOR
XML PATH('tr')
,ELEMENTS
) AS NVARCHAR(MAX));
------------------------------------------------------------------------------------------------------------
-- Generate email
body XML
------------------------------------------------------------------------------------------------------------
SET @Body = '<html><body><H3>Long
Running SQL Agent Jobs</H3>
<table border =
1>
<tr>
<th>
Duration </th> <th> Job Name </th> <th> Run Date
</th> <th> Run Time </th>
<th> Message </th>
</tr>'
SET @Body = @Body + @XML + '</table></body></html>';
------------------------------------------------------------------------------------------------------------
-- Send the email
------------------------------------------------------------------------------------------------------------
SET @Subject = @@ServerName + ' ' + 'SQL Agent Jobs That Took A
Long Time To Run';
EXEC msdb.dbo.sp_send_dbmail @profile_name
= @EmailProfile
,@recipients
= @EmailList
,@subject = @Subject
,@Body = @Body
,@Body_format =
@EmailFormat;
[G] – Object modified in last 10 days
Create Table #a
(
DB1 varchar(100),
Obj_Name Varchar(100),
[TYPE] Varchar(100),
Cr_Dt1 datetime,
md_Dt1 datetime
)
EXEC SP_MSFOREACHDB
'USE [?]IF ''?''
NOT IN (''MASTER'', ''MODEL'', ''MSDB'', ''TEMPDB'', ''ADVENTUREWORKS'' )
BEGIN
Insert Into
Tempdb..#a(DB1,Obj_Name,[TYPE],Cr_Dt1,md_Dt1)
SELECT
''?'',name,[TYPE],create_date,modify_date FROM sys.objects
WHERE TYPE
In(''U'',''V'',''FN'',''P'') AND DATEDIFF(D,modify_date, GETDATE()) < 10
END '
--Drop table #a
Select * from #a
[H] – Drive space monitoring
[I] – Failed Job monitoring
[J] – List of Tables – Size – No of rows
Single database
SELECT
SCHEMA_NAME(o.schema_id) + '','' + OBJECT_NAME(p.object_id) AS name,
reserved_page_count * 8 as space_used_kb,
row_count
FROM
sys.dm_db_partition_stats
AS p
JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped
= 0
ORDER BY
SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id)
Multiple database
DECLARE @command varchar(8000)
SELECT @command = 'USE [?]
SELECT DB_NAME()
As DBName,
SCHEMA_NAME(o.schema_id) + '','' +
OBJECT_NAME(p.object_id) AS name,
reserved_page_count * 8 as space_used_kb,
row_count
FROM
sys.dm_db_partition_stats AS p
JOIN sys.all_objects AS o ON
p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
ORDER BY
SCHEMA_NAME(o.schema_id) + '','' +
OBJECT_NAME(p.object_id) '
EXEC sp_MSforeachdb @command
[K] – SQL Process details with Query Text
SELECT
s.spid,s.dbid,s.login_time,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cmd,s.blocked,s.cpu,s.memusage,s.physical_io,s.status,s.net_library,s.sql_handle
,e.text as SQL
FROM
sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e
[L] – Sp_Who5
Create proc [dbo].[SP_Who5]
As
SELECT SPID = er.session_id
, ot.Threads
,
RunningThreads = coalesce(rsp.RunningThreads,0)
, Pct_Comp = er.percent_complete
,
Est_Comp_Time = CASE
er.estimated_completion_time WHEN 0 THEN NULL ELSE dateadd(ms, er.estimated_completion_time, getdate()) END
, er.status
, er.command
,
database_name = sd.name
, BlockedBy
= wt.blocking_session_id
,
HeadBlocker = coalesce(hb5.session_id, hb4.session_id, hb3.session_id, hb2.session_id, hb1.session_id)
, wait_type
= coalesce(CASE er.wait_type WHEN 'CXPACKET' THEN 'CXPACKET - ' + sp.lastwaittype1 ELSE
sp.lastwaittype1 END, lower(er.last_wait_type)) --Lowercase denotes it's
not currently waiting, also noted by a wait time of 0.
,
Wait_Time_Sec = Cast(er.wait_time/1000.0 as DEC(20,3))
, er.wait_resource
,
Duration_Sec = Cast(DATEDIFF(s, er.start_time, GETDATE()) as DEC(20,0))
, CPU_Sec = Cast(er.cpu_time/1000.0 as DEC(20,3))
, Reads_K = Cast(er.reads/1000.0 as DEC(20,3))
, Writes_K = Cast(er.writes/1000.0 as DEC(20,3))
,
[Statement] = SUBSTRING (st.text, er.statement_start_offset/2,
abs(CASE WHEN er.statement_end_offset
= -1
THEN
LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE
er.statement_end_offset END - er.statement_start_offset)/2)
, st.text as Query
, es.login_time
, es.host_name
, program_name = CASE LEFT(es.program_name, 29)
WHEN
'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj
WHERE substring(es.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(es.program_name, 67, len(es.program_name)-67)
ELSE
es.program_name
END
, es.client_interface_name
, es.login_name
, es.status
, es.total_scheduled_time
, es.total_elapsed_time
, er.start_time
, es.last_request_start_time
, es.last_request_end_time
, er.database_id
--,
qp.query_plan
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_Sessions es
on er.session_id=es.session_id
LEFT JOIN sys.databases sd on er.database_id=sd.database_id
INNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks GROUP BY session_id) ot on er.session_id=ot.session_id
LEFT JOIN (SELECT spid, LastWaitType1 = MIN(lastwaittype), LastWaitType2 = MAX(lastwaittype) FROM sysprocesses sp WHERE
waittime > 0 AND
lastwaittype <> 'cxpacket'
GROUP BY spid) sp ON er.session_id = sp.spid
LEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM sysprocesses sp WHERE
waittime = 0 GROUP
BY spid) rsp ON er.session_id = rsp.spid
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt WHERE
wt.blocking_session_id <>
wt.session_id GROUP
BY session_id)
wt ON er.session_id
= wt.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb1 ON wt.blocking_session_id
= hb1.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb2 ON hb1.blocking_session_id
= hb2.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb3 ON hb2.blocking_session_id
= hb3.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb4 ON hb3.blocking_session_id
= hb4.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id)
blocking_session_id FROM sys.dm_os_waiting_tasks wt GROUP
BY session_id)
hb5 ON hb4.blocking_session_id
= hb5.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
--CROSS APPLY
sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID
--AND
es.host_name like '%%'
--AND
er.session_id = 2702
ORDER BY er.percent_complete
DESC, er.cpu_time DESC, er.session_id
GO
Database Growth Monitor
[A] – Table Schema
to be needed
CREATE TABLE
[dbo].[_DBSpaceMonitor](
[Rowid] [int] IDENTITY(1,1) NOT NULL Primary Key,
[DB_Name]
[varchar](100) NULL,
[Logical_name]
[varchar](100) NULL,
[File_Name]
[varchar](500) NULL,
[DB_Size_in_MB]
[float] NULL,
[DT_Stamp]
[datetime] DEFAULT (getdate())
) ON [PRIMARY]
[B] – SQL Job with Daily
frequency
INSERT INTO
[_DBSpaceMonitor]([DB_Name],[Logical_name],[File_Name],[DB_Size_in_MB])
SELECT B.NAME
DB_NAME, A.NAME LOGICAL_NAME, A.FILENAME FILE_NAME,
CAST((A.SIZE * 8.00) / 1024 AS NUMERIC(12,2)) AS DB_SIZE_IN_MB
FROM master..SYSALTFILES A
JOIN master..SYSDATABASES B ON A.DBID = B.DBID
WHERE DATABASEPROPERTYEX(B.NAME, 'STATUS') = 'ONLINE'
ORDER BY
B.NAME
No comments:
Post a Comment