Script to
Monitor Blocking Queries:
SELECT
r.session_id,
r.blocking_session_id,
t.text AS [SQL
Text]
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE
r.blocking_session_id <> 0;
Script to
Monitor Server Performance:
SELECT
[object_name],
[counter_name],
[cntr_value]
FROM
sys.dm_os_performance_counters
WHERE
[object_name] LIKE
'%:SQLServer%'
AND [counter_name]
IN ('Batch Requests/sec', 'Transactions/sec', 'User Connections')
ORDER BY
[object_name],
[counter_name];
Script to
Identify Index Fragmentation:
SELECT
dbschemas.[name] +
'.' + dbtables.[name] AS [Table Name],
dbindexes.[name]
AS [Index Name],
indexstats.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS
indexstats
INNER JOIN
sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND
indexstats.index_id = dbindexes.index_id
WHERE
indexstats.avg_fragmentation_in_percent > 10
ORDER BY
indexstats.avg_fragmentation_in_percent DESC;
Script to Find High CPU Queries:
SELECT TOP 10
total_worker_time/execution_count AS Avg_CPU_Time,
total_worker_time
AS Total_CPU_Time,
execution_count,
creation_time,
last_execution_time,
st.text AS [SQL
Text]
FROM
sys.dm_exec_query_stats
AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) AS st
ORDER BY
total_worker_time/execution_count DESC;
Script to
Check Database Size and Space Usage:
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() AS
[Database Name],
(size * 8) AS
[Size (KB)],
FILEGROUP_NAME(data_space_id) AS [Filegroup],
name AS
[Logical Name],
(size * 8 -
FILEPROPERTY(name, ''SpaceUsed'') * 8) AS [Unused Space (KB)]
FROM
sys.master_files
WHERE
type = 0;
';
No comments:
Post a Comment