SQL Queries

SQL Queries for everyday troubleshooting , performance monitoring and Trending Analysis

Clear Plan Cache , Wait and Latch statistics




DECLARE @pDBName sysname
SELECT @pDBName = DB_NAME() ;

-- Remove all elements from the plan cache for one database
DECLARE @intDBID INT;
SET @intDBID = ( SELECT [dbid]
FROM   master.dbo.sysdatabases
WHERE  name = @pDBName
);

-- Flush the procedure cache for one database only
IF @intDBID IS NOT NULL
BEGIN
DBCC FLUSHPROCINDB (@intDBID);
END;

GO

-- Reset wait and latch statistics.
DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

Row Count and Space Used By Tables



; WITH Table_RowCnt_SpaceUsed
AS ( SELECT object_id ,
reservedpages = SUM(reserved_page_count) ,
usedpages = SUM(used_page_count) ,
pages = SUM(CASE WHEN ( index_id < 2 )
THEN ( in_row_data_page_count
+ lob_used_page_count
+ row_overflow_used_page_count )
ELSE lob_used_page_count
+ row_overflow_used_page_count
END) ,
row_Count = SUM(CASE WHEN ( index_id < 2 ) THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats GROUP BY object_id ) 


SELECT schema_name = OBJECT_SCHEMA_NAME(object_id) 
, table_name = OBJECT_NAME(object_id) 
, row_Count 
, reserved = LTRIM(STR(reservedpages * 8, 15, 0) + ' KB') 
, data = LTRIM(STR(pages * 8, 15, 0) + ' KB') 
, index_size = LTRIM(STR(( CASE WHEN usedpages > pages
THEN ( usedpages - pages )
ELSE 0
END ) * 8, 15, 0) + ' KB') ,
unused = LTRIM(STR(( CASE WHEN reservedpages > usedpages
THEN ( reservedpages - usedpages )
ELSE 0
END ) * 8, 15, 0) + ' KB')
FROM Table_RowCnt_SpaceUsed
WHERE OBJECT_SCHEMA_NAME(object_id) IN ( 'ls','dbo' )
ORDER BY Table_RowCnt_SpaceUsed.row_Count DESC;

Memory Used By Objects



USE [DatabaseName];
GO
;
WITH    src
AS ( SELECT   [Object] = o.name ,
[Type] = o.type_desc ,
[Index] = COALESCE(i.name, '') ,
[Index_Type] = i.type_desc ,
p.[object_id] ,
p.index_id ,
au.allocation_unit_id
FROM     sys.partitions AS p
INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE    au.[type] IN ( 1, 2, 3 )
AND o.is_ms_shipped = 0
)
SELECT  src.[Object] ,
src.[Type] ,
src.[Index] ,
src.Index_Type ,
buffer_pages = COUNT_BIG(b.page_id) ,
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM    src
INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id
WHERE   b.database_id = DB_ID()
GROUP BY src.[Object] ,
src.[Type] ,
src.[Index] ,
src.Index_Type
ORDER BY buffer_pages DESC;

Failed Login Attemps



DROP TABLE IF EXISTS #temptable;
CREATE TABLE #temptable
(
    [LogDate] DATETIME,
    [ProcessInfo] NVARCHAR(12),
    [Text] NVARCHAR(3999)
);

INSERT INTO #temptable
(
    [LogDate],
    [ProcessInfo],
    [Text]
)
EXEC sys.sp_readerrorlog @p1 = 0,       -- int -- 0 = current, 1 = Archive #1, 2 = Archive
                         @p2 = 1,       -- int -- 1 or NULL = error log, 2 = SQL Agent log
                         @p3 = 'login'; -- varchar(255) -- String two we want to search to further refine the result

SELECT REVERSE(SUBSTRING(REVERSE(Text), 2, 13)) AS [IPAddress],
       COUNT(*) AS FailedAttempts
FROM #temptable
WHERE Text LIKE 'Login failed for user%'
GROUP BY REVERSE(SUBSTRING(REVERSE(Text), 2, 13));

SELECT REVERSE(SUBSTRING(REVERSE(Text), 2, 13)) AS [IPAddress],
       COUNT(*) AS SuccessAttempts
FROM #temptable
WHERE Text LIKE 'success%'
GROUP BY REVERSE(SUBSTRING(REVERSE(Text), 2, 13));



Restored Databse Backup File Source and Location Info



SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] AS [source_database_name],
[bmf].[physical_device_name] AS [backup_file_used_for_restore],
bs.server_name,
bs.machine_name,
bs.database_name,
bs.user_name
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC ;



Connection , WaitStats and Execution Counts




-- DBCC FREEPROCCACHE

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @plogin_name NVARCHAR(255) 
-- SELECT @plogin_name = 'LoginName'
-- Connection Counts

-- Get logins that are connected and how many sessions they have
SELECT login_name ,
COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH ( NOLOCK )
WHERE session_id > 50 -- filter out system SPIDs
AND login_name = ISNULL(@plogin_name , login_name)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC
OPTION ( RECOMPILE );
-- Get a count of SQL connections by IP address
SELECT ec.client_net_address ,
es.[program_name] ,
es.[host_name] ,
es.login_name ,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es WITH ( NOLOCK )
INNER JOIN sys.dm_exec_connections AS ec WITH ( NOLOCK ) ON es.session_id = ec.session_id
WHERE es.login_name = ISNULL(@plogin_name , es.login_name)
GROUP BY ec.client_net_address ,
es.[program_name] ,
es.[host_name] ,
es.login_name
ORDER BY ec.client_net_address ,
es.[program_name]
OPTION ( RECOMPILE );


-- Deadlocks

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total'

-- Execution Counts
SELECT
@@SERVERNAME ServerName,
DB_NAME() AS DatabaseName,
object_id ObjectID ,
OBJECT_NAME(object_id) StoredProcName ,
OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
cached_time AS CachedDateTime ,
execution_count ,
total_elapsed_time / ( 1000 * execution_count ) AS Avg_elapsed_time ,
min_elapsed_time / 1000 AS min_elapsed_time ,
max_elapsed_time / 1000 AS max_elapsed_time ,
total_logical_reads / execution_count AS Avg_logical_reads ,
total_physical_reads / execution_count AS Avg_physical_reads
FROM sys.dm_exec_procedure_stats WITH ( NOLOCK )
WHERE OBJECT_NAME(object_id) IS NOT NULL
AND type = 'P'
AND database_id = DB_ID()
ORDER BY execution_count DESC ;


;WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

-- Maybe uncomment these four if you have mirroring issues
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',

N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

-- Maybe uncomment these six if you have AG issues
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
N'ONDEMAND_TASK_QUEUE',
N'PREEMPTIVE_XE_GETTARGETSTATE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_RECOVERY',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold


DECLARE @ExcludedWaits Table (WaitType sysname not null primary key)

-- Waits that can be ignored
Insert Into @ExcludedWaits
Values ('CLR_SEMAPHORE'),
('SQLTRACE_BUFFER_FLUSH'),
('WAITFOR'),
('REQUEST_FOR_DEADLOCK_SEARCH'),
('XE_TIMER_EVENT'),
('BROKER_TO_FLUSH'),
('BROKER_TASK_STOP'),
('CLR_MANUAL_EVENT'),
('CLR_AUTO_EVENT'),
('FT_IFTS_SCHEDULER_IDLE_WAIT'),
('XE_DISPATCHER_WAIT'),
('XE_DISPATCHER_JOIN'),
('BROKER_RECEIVE_WAITFOR');

Select SessionID = WT.session_id,
WaitDuration_ms = WT.wait_duration_ms,
WaitType = WT.wait_type,
WaitResource = WT.resource_description,
Program = S.program_name,
QueryPlan = CP.query_plan,
SQLText = SUBSTRING(ST.text, (R.statement_start_offset/2)+1,
((Case R.statement_end_offset
When -1 Then DATALENGTH(ST.text)
Else R.statement_end_offset
End - R.statement_start_offset)/2) + 1),
DBName = DB_NAME(R.database_id),
BlocingSessionID = WT.blocking_session_id,
BlockerQueryPlan = CPBlocker.query_plan,
BlockerSQLText = SUBSTRING(STBlocker.text, (RBlocker.statement_start_offset/2)+1,
((Case RBlocker.statement_end_offset
When -1 Then DATALENGTH(STBlocker.text)
Else RBlocker.statement_end_offset
End - RBlocker.statement_start_offset)/2) + 1)
From sys.dm_os_waiting_tasks WT
Inner Join sys.dm_exec_sessions S on WT.session_id = S.session_id
Inner Join sys.dm_exec_requests R on R.session_id = WT.session_id
Outer Apply sys.dm_exec_query_plan (R.plan_handle) CP
Outer Apply sys.dm_exec_sql_text(R.sql_handle) ST
Left Join sys.dm_exec_requests RBlocker on RBlocker.session_id = WT.blocking_session_id
Outer Apply sys.dm_exec_query_plan (RBlocker.plan_handle) CPBlocker
Outer Apply sys.dm_exec_sql_text(RBlocker.sql_handle) STBlocker
Where R.status = 'suspended' -- Waiting on a resource
And S.is_user_process = 1 -- Is a used process
And R.session_id <> @@spid -- Filter out this session
And WT.wait_type Not Like '%sleep%' -- more waits to ignore
And WT.wait_type Not Like '%queue%' -- more waits to ignore
And WT.wait_type Not Like -- more waits to ignore
Case When SERVERPROPERTY('IsHadrEnabled') = 0 Then 'HADR%'
Else 'zzzz' End
And Not Exists (Select 1 From @ExcludedWaits
Where WaitType = WT.wait_type)
ORDER BY WaitDuration_ms DESC
Option(Recompile); -- Don't save query plan in plan cache


SELECT  [oswt].[session_id] ,
    [er].percent_complete,
        [oswt].[exec_context_id] ,
        [ot].[scheduler_id] ,
        [oswt].[wait_duration_ms] ,
        [oswt].[wait_type] ,
        [oswt].[blocking_session_id] ,
        [oswt].[resource_description] ,
        CASE [oswt].[wait_type]
          WHEN N'CXPACKET'
          THEN RIGHT([oswt].[resource_description],
                     CHARINDEX(N'=', REVERSE([oswt].[resource_description]))
                     - 1)
          ELSE NULL
        END AS [Node ID] ,
        [es].[program_name] ,
        [est].text ,
        [er].[database_id] ,
        [eqp].[query_plan] ,
        [er].[cpu_time]
FROM    sys.dm_os_waiting_tasks [oswt]
        INNER JOIN sys.dm_os_tasks [ot] ON [oswt].[waiting_task_address] = [ot].[task_address]
        INNER JOIN sys.dm_exec_sessions [es] ON [oswt].[session_id] = [es].[session_id]
        INNER JOIN sys.dm_exec_requests [er] ON [es].[session_id] = [er].[session_id]
        OUTER APPLY sys.dm_exec_sql_text([er].[sql_handle]) [est]
        OUTER APPLY sys.dm_exec_query_plan([er].[plan_handle]) [eqp]
WHERE   [es].[is_user_process] = 1
ORDER BY [oswt].[session_id] ,
        [oswt].[exec_context_id];
        
        
 SELECT query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
        
        
    

Topic Channel Last post
FailOver Groups
by i90runner1

Azure

i90runner1 posted

Protecting your machines and applications in Azure Security Center
by i90runner1

Azure

i90runner1 posted

Azure Outage caused by DNS
by i90runner1

Azure

i90runner1 posted

Application Gateway and Ingress Metrics
by i90runner1

Azure

i90runner1 posted

Extended Events - Import xel files to SQL Server
by i90runner1

Azure

i90runner1 posted

SQL Server Audit Logs and Power BI Integration
by i90runner1

Azure

i90runner1 posted

Securely Handle Clear Text Passwords in Powershell
by i90runner1

Powershell

i90runner1 posted

Videos Watched
by

Channel Orange

posted

MySQL Performance Metrics
by i90runner1

MySQL Queries

i90runner1 posted

MySQL Process Information
by i90runner1

MySQL Queries

i90runner1 posted

Azure SQL Migration
by

Channel Orange

posted

Monitor active connection pooling in SQL Server
by i90runner1

Performance Tuing

i90runner1 posted

Stored Procedure Execution Counts and Wait Stats
by i90runner1

Performance Tuing

i90runner1 posted

SQL Server Sessions and Connections Count by Client
by i90runner1

Performance Tuing

i90runner1 posted

Clear the Proc Cache in SQL Azure
by i90runner1

Performance Tuing

i90runner1 posted

SQL Server Worker Threads
by i90runner1

Performance Tuing

i90runner1 posted

Backup Database to Azure Blob Storage
by i90runner1

Backup-Restore

i90runner1 posted

Bulk Insert into SQL Server
by i90runner1

SQL Server

i90runner1 posted

Most Expensive Queries by CPU
by i90runner1

General Queries

i90runner1 posted

Azure Resources
by

Channel Orange

posted