SQLDBA- SQL Server Wait Events

 

To solve slowness of SQL Server database, you should find which wait events exists in the database.

You can find wait events of database with following query.

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 (COALESCE (wait_time_ms,1)) 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'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
select
w1.wait_type as waittype, 
cast (w1.waits as decimal(14, 2)) wait_s,
cast (w1.resources as decimal(14, 2)) resource_s,
cast (w1.signals as decimal(14, 2)) signal_s,
w1.waitcount wait_count,
cast (w1.percentage as decimal(4, 2)) percentage,
cast ((w1.waits / w1.waitcount) as decimal (14, 4)) avgWait_s,
cast ((w1.resources / w1.waitcount) as decimal (14, 4)) avgResource_s,
cast ((w1.signals / w1.waitcount) as decimal (14, 4)) avgSignal_s
from waits as w1
inner join waits as w2 on w2.rownum <= w1.rownum
group by w1.rownum, w1.wait_type, w1.waits, w1.resources, w1.signals, w1.waitcount, w1.percentage
having sum (w2.percentage) - w1.percentage < 95; -- percentage threshold

You can get list and status of currently waiting tasks with following script.

select wt.session_id, wt.exec_context_id, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_address, wt.resource_description, s.program_name, st.text, sp.query_plan, s.cpu_time cpu_time_ms, s.memory_usage*8 memory_usage_kb
from sys.dm_os_waiting_tasks wt
join sys.dm_exec_sessions s on s.session_id=wt.session_id
join sys.dm_exec_requests r on r.session_id=s.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_query_plan(r.plan_handle) sp
where s.is_user_process=1
order by wt.session_id, wt.exec_context_id

You can find wait events of database with following query.

SELECT getdate() as 'Run_Time' --script running time
    , wait_type --wait type
    ,waiting_tasks_count
, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamaný
, CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye oraný
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR',
      'BROKER_TRANSMITTER','CHECKPOINT_QUEUE','CHKPT,CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP',
      'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
      'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK',
      'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE','WAIT_FOR_RESULTS','WAITFOR_TASKSHUTDOWN',
       'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR')
ORDER BY 4 DESC

SQLDBA- Generate the ALTER script for set compatibility for all databases

 

-- Alter  script set  compatibility y to sql server  2022

DECLARE @DBName SYSNAME;

DECLARE @SQL NVARCHAR(MAX);

-- Table variable to hold database names

DECLARE @DBList TABLE (DBName SYSNAME);

-- Insert all user databases except system ones

INSERT INTO @DBList (DBName)

SELECT name FROM sys.databases

WHERE database_id > 4 -- Exclude system DBs

AND state_desc = 'ONLINE';

-- Initialize loop

WHILE EXISTS (SELECT 1 FROM @DBList)

BEGIN

    -- Get one database name

    SELECT TOP 1 @DBName = DBName FROM @DBList;

    -- Build and execute the ALTER statement

    SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 160;';

    PRINT 'Updating compatibility for: ' + @DBName;

PRINT @SQL

    --EXEC sp_executesql @SQL;

    -- Remove processed DB

    DELETE FROM @DBList WHERE DBName = @DBName;

END

--Set  compatability  to  SQL server  2022

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = 160

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

-- Roll back script

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = '+CONVERT(VARCHAR(5),compatibility_level) +'

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

SQLDBA - Script to Loop through all the transaction log file and restore them automatically.

 

Script to Loop through all the transaction log file and restore them automatically.

Below script is generating the restoring the  transactional log backups.  Initially  we have inserted transaction files into  temp table and then we have generating restoring the tractional log backup.

Method1:

USE master;

DECLARE @t AS TABLE (fname VARCHAR(max));

INSERT @t EXEC xp_cmdshell 'dir /b /S E:\dirname\*.trn';

SELECT 'RESTORE LOG [DBNAME] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL

Method2:

Powershell  Script;

To automatically loop through and restore all SQL Server transaction log files (.trn), you can use a PowerShell script or T-SQL script depending on your environment and preferences. Here's a PowerShell script that reads all .trn files from a folder and restores them in sequence.

# Define variables
$serverName = "YourSQLServerInstance"  
$databaseName = "YourDatabase"  
$backupFolder = "C:\Backups\Logs"
$restoreDatabase = $false  # Set to $true if you want to restore the full DB first

# Optional: Restore full backup if needed
if ($restoreDatabase) {
    $fullBackupFile = "C:\Backups\YourDatabase.bak"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE DATABASE [$databaseName]
        FROM DISK = N'$fullBackupFile'
        WITH NORECOVERY, REPLACE"
}

# Get all .trn files sorted by creation time
$logFiles = Get-ChildItem -Path $backupFolder -Filter *.trn | Sort-Object LastWriteTime

# Loop through and restore each log file
foreach ($file in $logFiles) {
    Write-Host "Restoring log file: $($file.Name)"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE LOG [$databaseName]
        FROM DISK = N'$($file.FullName)'
        WITH NORECOVERY"
}

# Finally, recover the database
Invoke-Sqlcmd -ServerInstance $serverName -Query "
    RESTORE DATABASE [$databaseName] WITH RECOVERY"

Write-Host "Transaction log restore completed."

Method3:

# Define parameters
$serverInstance = "localhost\SQLEXPRESS"  # Change to your SQL Server instance
$databaseName = "YourDatabaseName"        # Change to your target database
$logBackupFolder = "C:\LogBackups"        # Folder containing .trn files

# Load SQL Server SMO
Add-Type -AssemblyName "Microsoft.SqlServer.SMO"
Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended"

# Connect to SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance

# Get all .trn files sorted by name (assuming naming reflects order)
$logFiles = Get-ChildItem -Path $logBackupFolder -Filter *.trn | Sort-Object Name

foreach ($logFile in $logFiles) {
    Write-Host "Restoring log file: $($logFile.Name)"

    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $restore.Action = "Log"
    $restore.Database = $databaseName
    $restore.Devices.AddDevice($logFile.FullName, "File")
    $restore.NoRecovery = $true  # Keep in restoring state for next log

    try {
        $restore.SqlRestore($server)
        Write-Host "Successfully restored: $($logFile.Name)"
    } catch {
        Write-Host "Failed to restore: $($logFile.Name)"
        Write-Host $_.Exception.Message
    }
}

Write-Host "All log files processed."

SQL DBA - Find the job names with user name with running as job owner.

 

-- Find the job names with user name with running as job owner. 

SELECT j.name AS JobName,

       j.owner_sid,

       s.name AS JobOwner,

       a.start_execution_date

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobactivity AS a

    ON j.job_id = a.job_id

INNER JOIN sys.server_principals AS s

    ON j.owner_sid = s.sid

WHERE a.run_requested_date IS NOT NULL

  AND a.stop_execution_date IS NULL  -- Job is still running

  AND s.name LIKE  '%SVC_%';       -- Replace with the specific user

-- Find the job name, with command context . 

SELECT j.name AS JobName,

       js.step_id,

       js.step_name,

       js.database_name,

       js.command,

       js.subsystem

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobsteps AS js

    ON j.job_id = js.job_id

WHERE js.command LIKE '%SVC%';