SQL Queries

; 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;

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;

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));

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 ;

-- 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;