SQL Server

Check Fragmentation stats

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.alloc_unit_type_desc,

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

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.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

Rebuild Indexes

DECLARE @tsql NVARCHAR(MAX) 

DECLARE @fillfactor INT

SET @fillfactor = 90

SELECT @tsql =

  STUFF(( SELECT DISTINCT

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM

           sysobjects o

          INNER JOIN sysindexes i

           ON o.id = i.id

          WHERE

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql         

EXEC sp_executesql @tsql

Top most time consuming queries 1

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time

Top most time consuming queries 2

SELECT s.session_id,

r.status,

r.blocking_session_id 'Blk by',

r.wait_type,

wait_resource,

r.wait_time / (1000 * 60) 'Wait M',

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

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) AS statement_text,

Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +

Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r

ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time desc

Top most time consuming queries 3

--Query is to identify which queries are demanding a lot of CPU time. The below query is useful for this purpose (note, in its current form, it only shows the top 10 records).

SELECT TOP 10 st.text

               ,st.dbid

               ,st.objectid

               ,qs.total_worker_time

               ,qs.last_worker_time

               ,qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC

DB back-up script

BACKUP DATABASE [DB name]

TO DISK = '<back-up path>\<DB name>.bak'

DB restore script

USE Master

GO

RESTORE DATABASE [DB name where to restore]

FROM DISK = '<DB path>\<Back-up DB name>.bak'

WITH MOVE 'Data file name' TO '<MDF file path>\<file name>.MDF',

MOVE 'Log file name' TO '<LDF file path>\<file name>.ldf',REPLACE,STATS=10

--Execute below to get logical / physical file name

USE [DB name]

sp_helpfile

Determining Current Memory Allocation

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

Determining Current Memory Allocation

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

Delete Data in Batches

deleteMore:

DELETE TOP (10000) [dbo].[Table_Name] WHERE Created_Date < '2021-04-25'

IF @@ROWCOUNT != 0

    goto deleteMore

Get SQL Server Drive Usage Stats

IF OBJECT_ID('master.sys.dm_os_volume_stats') IS NOT NULL

    SELECT vs.volume_mount_point AS Drive, vs.file_system_type AS [Type]

        ,vs.logical_volume_name AS LogicalName

        ,MAX(CAST(  1.0*vs.total_bytes  / 1073741824 AS DECIMAL(18,2)))AS[Drive Size (GB)]

        ,CAST(SUM(  1.0*size) / 128 / 1000 AS DECIMAL(18,2)) AS [SQL Size (GB)]

        ,MAX(CAST(  1.0*vs.available_bytes/1073741824 AS DECIMAL(18,2)))AS[Free Space (GB)]

        ,MIN(CAST(100.0*vs.available_bytes/vs.total_bytes AS DECIMAL(5,1)))AS[Free Space (%)]

    FROM master.sys.master_files AS f WITH (NOLOCK)

    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

    GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name

    ORDER BY 1

    OPTION (RECOMPILE);

SELECT Drive

    ,ISNULL([ROWS], 0) + ISNULL([LOG], 0)

        + ISNULL([TempROWS], 0) + ISNULL([TempLOG], 0)

        AS 'TotalUsed (MB)'

    ,[ROWS] AS 'Data (MB)'

    ,[LOG] AS 'Logs (MB)'

    ,[TempROWS] AS 'TempData (MB)'

    ,[TempLOG] AS 'TempLogs (MB)'

FROM (

    SELECT LEFT(Physical_Name, 3) 'Drive'

        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc 'FileType'

        ,SUM(size) / 128 'SizeMB'

    FROM master.sys.master_files

    GROUP BY LEFT(Physical_Name, 3)

        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc

    ) Results

PIVOT(SUM(SizeMB) FOR FileType IN ([ROWS], [LOG], [TempROWS], [TempLOG])) pvt

ORDER BY 1

OPTION (RECOMPILE);

Get Individual Database Stats

/* Get Individual Database Stats */

SELECT mf.database_id 'DB_ID'

    ,DB_NAME(mf.database_id) 'DBName'

    ,d.state_desc 'DBState'

    ,d.recovery_model_desc AS RecoveryModel

    ,CASE WHEN Log_ReUse_Wait_Desc = 'NOTHING' THEN ''

          ELSE Log_ReUse_Wait_Desc END AS LogReUseWait

    ,mf.[File_ID]

    ,mf.NAME 'LogicalName'

    ,mf.type_desc 'Type'

    ,mf.Physical_Name

    ,mf.state_desc 'FileState'

    ,CAST(size / 128.0 + 0.5 AS INT) AS SizeMB

    ,CAST(max_size / 128.0 + 0.5 AS INT) AS MaxSizeMB

    ,CASE is_percent_growth

        WHEN 0 THEN CAST(growth / 128 AS VARCHAR(10)) + ' MB'

        ELSE CAST(growth AS VARCHAR(10)) + ' %' END AS 'AutoGrowth'

    ,CASE 

        WHEN d.STATE <> 6 /* 6 = OFFLINE */

        AND mf.type_desc = 'ROWS'

        AND mf.database_id <> 2 /* not TempDB */

            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))

               + ';   DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))

               + ',1,TRUNCATEONLY);

GO'

        ELSE '' END AS 'ShrinkTruncateOnlyCommand'

FROM [master].sys.master_files mf

LEFT JOIN [master].sys.databases d ON d.database_id = mf.database_id

WHERE 1 = 1

    AND d.STATE <> 6 /* 6 = OFFLINE */

    AND mf.database_id > 4 --user DBs only

    --AND mf.database_id <= 4 --system DBs only

ORDER BY SizeMB DESC, DB_NAME(mf.database_id), [file_id]

OPTION (RECOMPILE);

SQL High CPU troubleshooting checklist