Blog

<h2>Azure synapse | Dedicated SQL Pool | Data Warehouse</h2>Here are some of the query tools that can be used to gain better insight into the current activities of your Azure synapse, Dedicated SQL&nbsp; Pool, or Data Warehouse.&nbsp; Whichever terminology you used, it all points to an Analysis Data Warehouse database in Azure.<h2>Insight Query Tools</h2><h3>Sessions</h3>-- Active ConnectionsSELECT * FROM sys.dm_pdw_exec_sessions where status &lt;&gt; 'Closed' and session_id &lt;&gt; session_id();SELECT session_id()-- Kill Connection, remember to use single quotesKILL '[SID...]'<h3>Monitor</h3>-- Monitor active queriesSELECT * FROM sys.dm_pdw_exec_requests WHERE status NOT IN ('Completed', 'Failed', 'Cancelled') ORDER BY submit_time DESC;<h3>Blocking</h3>-- Collect the top blockingSELECT TOP 500 &nbsp; &nbsp; waiting.request_id AS WaitingRequestId, &nbsp; &nbsp; waiting.object_type AS LockRequestType, &nbsp; &nbsp; waiting.object_name AS ObjectLockRequestName, &nbsp; &nbsp; waiting.request_time AS ObjectLockRequestTime, &nbsp; &nbsp; blocking.session_id AS BlockingSessionId, &nbsp; &nbsp; blocking.request_id AS BlockingRequestId FROM sys.dm_pdw_waits waiting INNER JOIN sys.dm_pdw_waits blocking &nbsp; &nbsp; ON waiting.object_type = blocking.object_type &nbsp; &nbsp; AND waiting.object_name = blocking.object_name WHERE waiting.state = 'Queued' &nbsp; &nbsp; AND blocking.state = 'Granted' ORDER BY ObjectLockRequestTime ASC;<h3>Longest Running</h3>-- Find top 10 queries longest running queriesSELECT TOP 10 * FROM sys.dm_pdw_exec_requests ORDER BY total_elapsed_time DESC;<h3>Distributed Queries</h3>--Find the distributed query plan steps for a specific query.SELECT * FROM sys.dm_pdw_request_steps&nbsp; WHERE request_id = 'QID####'&nbsp; ORDER BY step_index;-- Retrieve the distribution run times for a specific SQL step. -- Substitute request_id and step_index with the respective values from Steps 1 and 3.SELECT * FROM sys.dm_pdw_sql_requests WHERE request_id = 'QID####' AND step_index = 2;-- Retrieve the SQL Server execution plan for a query executing on a designated SQL pool or control node. -- Replace distribution_id and spid with the values obtained from the preceding query.DBCC PDW_SHOWEXECUTIONPLAN(1, 78);-- Retrieve information about all workers involved in a Data Movement Step. -- Substitute request_id and step_index with the values obtained from Steps 1 and 3.SELECT * FROM sys.dm_pdw_dms_workers&nbsp; WHERE request_id = 'QID####' AND step_index = 2; -- Find the SQL Server estimated plan for a query executing on a designated SQL pool Compute or Control node. -- Replace distribution_id and spid with the respective values obtained from the preceding query.DBCC PDW_SHOWEXECUTIONPLAN(55, 238); -- Find queries-- Replace request_id with value from Step 1.SELECT waits.session_id, &nbsp; &nbsp; &nbsp; &nbsp;waits.request_id, &nbsp; &nbsp; &nbsp; &nbsp;requests.command, &nbsp; &nbsp; &nbsp; &nbsp;requests.status, &nbsp; &nbsp; &nbsp; &nbsp;requests.start_time, &nbsp; &nbsp; &nbsp; &nbsp;waits.type, &nbsp; &nbsp; &nbsp; &nbsp;waits.state, &nbsp; &nbsp; &nbsp; &nbsp;waits.object_type, &nbsp; &nbsp; &nbsp; &nbsp;waits.object_name FROM sys.dm_pdw_waits AS waits JOIN sys.dm_pdw_exec_requests AS requests ON waits.request_id = requests.request_id WHERE waits.request_id = 'QID####' ORDER BY waits.object_name, waits.object_type, waits.state;<h3>TempDB</h3>-- Monitor tempdbSELECT &nbsp; &nbsp; sr.request_id, &nbsp; &nbsp; ssu.session_id, &nbsp; &nbsp; ssu.pdw_node_id, &nbsp; &nbsp; sr.command, &nbsp; &nbsp; sr.total_elapsed_time, &nbsp; &nbsp; exs.login_name AS 'LoginName', &nbsp; &nbsp; DB_NAME(ssu.database_id) AS 'DatabaseName', &nbsp; &nbsp; (es.memory_usage * 8) AS 'MemoryUsage (in KB)', &nbsp; &nbsp; (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)', &nbsp; &nbsp; (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)', &nbsp; &nbsp; (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)', &nbsp; &nbsp; (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)', &nbsp; &nbsp; CASE es.is_user_process &nbsp; &nbsp; &nbsp; &nbsp; WHEN 1 THEN 'User Session' &nbsp; &nbsp; &nbsp; &nbsp; WHEN 0 THEN 'System Session' &nbsp; &nbsp; END AS 'SessionType', &nbsp; &nbsp; es.row_count AS 'RowCount' FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu &nbsp; &nbsp; INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es &nbsp; &nbsp; &nbsp; &nbsp; ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id &nbsp; &nbsp; INNER JOIN sys.dm_pdw_nodes_exec_connections AS er &nbsp; &nbsp; &nbsp; &nbsp; ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id &nbsp; &nbsp; INNER JOIN microsoft.vw_sql_requests AS sr &nbsp; &nbsp; &nbsp; &nbsp; ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id &nbsp; &nbsp; LEFT JOIN sys.dm_pdw_exec_requests exr &nbsp; &nbsp; &nbsp; &nbsp; ON exr.request_id = sr.request_id &nbsp; &nbsp; LEFT JOIN sys.dm_pdw_exec_sessions exs &nbsp; &nbsp; &nbsp; &nbsp; ON exr.session_id = exs.session_id WHERE DB_NAME(ssu.database_id) = 'tempdb' &nbsp; &nbsp; AND es.session_id &lt;&gt; @@SPID &nbsp; &nbsp; AND es.login_name &lt;&gt; 'sa' ORDER BY sr.request_id;<h3>Memory Consumption</h3>-- Memory consumptionSELECT &nbsp; pc1.cntr_value AS Curr_Mem_KB, &nbsp; pc1.cntr_value / 1024.0 AS Curr_Mem_MB, &nbsp; pc1.cntr_value / 1048576.0 AS Curr_Mem_GB, &nbsp; pc2.cntr_value AS Max_Mem_KB, &nbsp; pc2.cntr_value / 1024.0 AS Max_Mem_MB, &nbsp; pc2.cntr_value / 1048576.0 AS Max_Mem_GB, &nbsp; pc1.cntr_value * 100.0 / pc2.cntr_value AS Memory_Utilization_Percentage, &nbsp; pc1.pdw_node_id FROM &nbsp; sys.dm_pdw_nodes_os_performance_counters AS pc1 -- pc1: current memory JOIN &nbsp; sys.dm_pdw_nodes_os_performance_counters AS pc2 -- pc2: total memory allowed for this SQL instance ON &nbsp; pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id WHERE &nbsp; pc1.counter_name = 'Total Server Memory (KB)' &nbsp; AND pc2.counter_name = 'Target Server Memory (KB)'<h3>Transaction Log Size</h3>-- Transaction log sizeSELECT &nbsp; instance_name AS distribution_db, &nbsp; cntr_value / 1048576.0 AS log_file_size_used_GB, &nbsp; pdw_node_id FROM sys.dm_pdw_nodes_os_performance_counters WHERE &nbsp; instance_name LIKE 'Distribution_%' &nbsp; AND counter_name = 'Log File(s) Used Size (KB)'<h3>Miscellaneous</h3>-- Monitor rollbackSELECT &nbsp; &nbsp; SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END) AS 'TransactionCount', &nbsp; &nbsp; t.pdw_node_id, &nbsp; &nbsp; nod.[type] FROM sys.dm_pdw_nodes_tran_database_transactions t JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id GROUP BY t.pdw_node_id, nod.[type] -- To track bytes and filesSELECT &nbsp; &nbsp; r.command, &nbsp; &nbsp; s.request_id, &nbsp; &nbsp; r.status, &nbsp; &nbsp; COUNT(DISTINCT input_name) AS nbr_files, &nbsp; &nbsp; SUM(s.bytes_processed) / 1024 / 1024 / 1024 AS gb_processed FROM &nbsp; &nbsp; sys.dm_pdw_exec_requests r &nbsp; &nbsp; INNER JOIN sys.dm_pdw_dms_external_work s &nbsp; &nbsp; &nbsp; &nbsp; ON r.request_id = s.request_id GROUP BY &nbsp; &nbsp; r.command, &nbsp; &nbsp; s.request_id, &nbsp; &nbsp; r.status ORDER BY &nbsp; &nbsp; nbr_files DESC, &nbsp; &nbsp; gb_processed DESC;--Collect the top blockingSELECT TOP 500 &nbsp; &nbsp; waiting.request_id AS WaitingRequestId, &nbsp; &nbsp; waiting.object_type AS LockRequestType, &nbsp; &nbsp; waiting.object_name AS ObjectLockRequestName, &nbsp; &nbsp; waiting.request_time AS ObjectLockRequestTime, &nbsp; &nbsp; blocking.session_id AS BlockingSessionId, &nbsp; &nbsp; blocking.request_id AS BlockingRequestId FROM sys.dm_pdw_waits waiting INNER JOIN sys.dm_pdw_waits blocking &nbsp; &nbsp; ON waiting.object_type = blocking.object_type &nbsp; &nbsp; AND waiting.object_name = blocking.object_name WHERE waiting.state = 'Queued' &nbsp; &nbsp; AND blocking.state = 'Granted' ORDER BY ObjectLockRequestTime ASC;-- To retrieve query text from waiting and blocking queriesSELECT waiting.session_id AS WaitingSessionId, &nbsp; &nbsp; &nbsp; &nbsp;waiting.request_id AS WaitingRequestId, &nbsp; &nbsp; &nbsp; &nbsp;COALESCE(waiting_exec_request.command, waiting_exec_request.command2) AS WaitingExecRequestText, &nbsp; &nbsp; &nbsp; &nbsp;blocking.session_id AS BlockingSessionId, &nbsp; &nbsp; &nbsp; &nbsp;blocking.request_id AS BlockingRequestId, &nbsp; &nbsp; &nbsp; &nbsp;COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS BlockingExecRequestText, &nbsp; &nbsp; &nbsp; &nbsp;waiting.object_name AS Blocking_Object_Name, &nbsp; &nbsp; &nbsp; &nbsp;waiting.object_type AS Blocking_Object_Type, &nbsp; &nbsp; &nbsp; &nbsp;waiting.type AS Lock_Type, &nbsp; &nbsp; &nbsp; &nbsp;waiting.request_time AS Lock_Request_Time, &nbsp; &nbsp; &nbsp; &nbsp;DATEDIFF(ms, waiting.request_time, GETDATE()) / 1000.0 AS Blocking_Time_sec FROM sys.dm_pdw_waits waiting &nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_pdw_waits blocking &nbsp; &nbsp; &nbsp; &nbsp;ON waiting.object_type = blocking.object_type &nbsp; &nbsp; &nbsp; &nbsp;AND waiting.object_name = blocking.object_name &nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request &nbsp; &nbsp; &nbsp; &nbsp;ON blocking.request_id = blocking_exec_request.request_id &nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request &nbsp; &nbsp; &nbsp; &nbsp;ON waiting.request_id = waiting_exec_request.request_id WHERE waiting.state = 'Queued' &nbsp; &nbsp; &nbsp; &nbsp;AND blocking.state = 'Granted' ORDER BY Lock_Request_Time DESC;

Tools for Azure Synapse Dedicated SQL Pool

<h2>Server Processes</h2>Here's a straight forward query that every SQL Server DBA should have in their toolbox. This query lists all connections as well as blockings for a SQL Server.&nbsp; It's very similar to the SQL Activity Monitor found in SSMS but more versatile and customizable.--sp_who2SELECT a1.SPID, &nbsp; &nbsp; &nbsp; &nbsp;[Database] = DB_NAME(a1.dbid), &nbsp; &nbsp; &nbsp; &nbsp;[User] = a1.loginame, &nbsp; &nbsp; &nbsp; &nbsp;Host = a1.hostname, &nbsp; &nbsp; &nbsp; &nbsp;a1.Status, &nbsp; &nbsp; &nbsp; &nbsp;OpenTransaction = a1.Open_Tran, &nbsp; &nbsp; &nbsp; &nbsp;Command = CASE WHEN a1.cmd = 'AWAITING COMMAND' THEN '' ELSE a1.cmd END, &nbsp; &nbsp; &nbsp; &nbsp;[Individual Query] = SUBSTRING(a2.[text], a4.statement_start_offset/2, (CASE WHEN a4.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), a2.[text])) * 2 ELSE a4.statement_end_offset END - a4.statement_start_offset)/2), &nbsp; &nbsp; &nbsp; &nbsp;CommandText = a2.[text], &nbsp; &nbsp; &nbsp; &nbsp;Application = a1.program_name, &nbsp; &nbsp; &nbsp; &nbsp;BlockedBy = CASE WHEN ISNULL(a4.blocking_session_id, 0) &gt; 0 THEN a4.blocking_session_id END, &nbsp; &nbsp; &nbsp; &nbsp;Blocking = CASE WHEN a5.blocking_session_id IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END, &nbsp; &nbsp; &nbsp; &nbsp;a5.BlockingCount, &nbsp; &nbsp; &nbsp; &nbsp;a1.WaitTime, &nbsp; &nbsp; &nbsp; &nbsp;WaitType = CASE WHEN a1.lastwaittype = 'MISCELLANEOUS' THEN '' ELSE a1.lastwaittype END, &nbsp; &nbsp; &nbsp; &nbsp;a1.WaitResource, &nbsp; &nbsp; &nbsp; &nbsp;a1.CPU, &nbsp; &nbsp; &nbsp; &nbsp;CPU_DELTA = ISNULL(a4.cpu_time, 0), &nbsp; &nbsp; &nbsp; &nbsp;a1.PHYSICAL_IO, &nbsp; &nbsp; &nbsp; &nbsp;a3.READS, &nbsp; &nbsp; &nbsp; &nbsp;a3.WRITES, &nbsp; &nbsp; &nbsp; &nbsp;a1.MEMUSAGE, &nbsp; &nbsp; &nbsp; &nbsp;a1.Login_Time, &nbsp; &nbsp; &nbsp; &nbsp;a1.Last_Batch FROM sysprocesses a1 JOIN sys.dm_exec_sessions a3 ON a1.spid = a3.session_id LEFT JOIN sys.dm_exec_requests a4 ON a3.session_id = a4.session_id LEFT JOIN ( &nbsp; &nbsp; SELECT blocking_session_id, BlockingCount = COUNT(1) &nbsp; &nbsp; FROM sys.dm_exec_requests &nbsp; &nbsp; WHERE blocking_session_id &gt; 0 &nbsp; &nbsp; GROUP BY blocking_session_id ) a5 ON a1.spid = a5.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(a1.sql_handle) a2 --WHERE command is not null ORDER BY command DESC

SQL Server DBA Tools:  List All Connections and Activities

Backing up your SQL Server databases is a critical task to ensure the safety and integrity of your data. However, with various backup options and strategies available, it can be overwhelming to determine the best practices for your specific needs. In this post, we will demystify SQL Server backups and provide you with essential tips and strategies to help you streamline your backup process.<h3>Query to List Last Backup Date for Every Database in a Server</h3>declare @space table ( &nbsp; &nbsp; &nbsp; &nbsp;DatabaseID int &nbsp; &nbsp; &nbsp; &nbsp;, DataSize float &nbsp; &nbsp; &nbsp; &nbsp;, [DataUsedPct] float &nbsp; &nbsp; &nbsp; &nbsp;, LogSize float &nbsp; &nbsp; &nbsp; &nbsp;, [LogUsedPct] float)declare @sql nvarchar(2560) declare @id intwhile isnull(@id,1) &lt;= ( select max(database_id) from sys.databases ) begin &nbsp; &nbsp; &nbsp; &nbsp;if exists (select 1 from sys.databases where database_id = isnull(@id,1) and has_dbaccess(name) = 1 and state_desc='ONLINE' ) &nbsp; &nbsp; &nbsp; &nbsp;begin &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set @sql = N'USE [' + ( select name from sys.databases where database_id = isnull(@id,1) ) + ']; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select ' + convert(nvarchar,isnull(@id,1)) + ' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, totalsizedata = sum( case when type = 0 then size/128 else 0 end) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, totalsizedatausedpct = convert(decimal(4,1), sum( case when type = 0 then spaceused else 0 end) * 100.0 / sum(case when type = 0 then size else 0 end) ) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, totalsizelog = sum( case when type = 1 then size/128 else 0 end) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, totalsizelogusedpct = convert(decimal(4,1), sum( case when type = 1 then spaceused else 0 end) * 100.0 / sum(case when type = 1 then size else 0 end) ) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from ( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select spaceused = fileproperty(name, ''spaceused''), * &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from sys.database_files ) a1'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; insert into @space &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; exec (@sql) &nbsp; &nbsp; &nbsp; &nbsp;end&nbsp; &nbsp; &nbsp; &nbsp;set @id = isnull(@id,1) + 1 endselect [DatabaseID] = d.database_id &nbsp; &nbsp; &nbsp; &nbsp;, [Database] = d.[Name] &nbsp; &nbsp; &nbsp; &nbsp;, [Status] = d.state_desc &nbsp; &nbsp; &nbsp; &nbsp;, [CreateDate] = convert(char(10),d.create_date,101) + right(convert(char(19),d.create_date,100),8) &nbsp; &nbsp; &nbsp; &nbsp;, [Recovery] = d.Recovery_Model_Desc &nbsp; &nbsp; &nbsp; &nbsp;, [LastFullBackup] = ( select convert(char(10),max(backup_finish_date),101) + right(convert(char(19),max(backup_finish_date),100),8) from msdb.dbo.backupset where database_name = d.name and type = 'D' ) &nbsp; &nbsp; &nbsp; &nbsp;, [LastDiffBackup] = ( select convert(char(10),max(backup_finish_date),101) + right(convert(char(19),max(backup_finish_date),100),8) from msdb.dbo.backupset where database_name = d.name and type = 'I' ) &nbsp; &nbsp; &nbsp; &nbsp;, [LastLogBackup] = ( select convert(char(10),max(backup_finish_date),101) + right(convert(char(19),max(backup_finish_date),100),8) from msdb.dbo.backupset where database_name = d.name and type = 'L' ) &nbsp; &nbsp; &nbsp; &nbsp;, [DataSize] = case when st.Datasize/1024/1024 &gt;= 1 then convert(varchar,convert(decimal(8,1),st.DataSize/1024/1024.0)) + ' TB' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; when st.Datasize/1024 &gt;= 1 then convert(varchar,convert(decimal(8,1),st.DataSize/1024.0)) + ' GB' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else cast(st.DataSize as varchar) + ' MB' end &nbsp; &nbsp; &nbsp; &nbsp;, [DataUsedPct] &nbsp; &nbsp; &nbsp; &nbsp;, [LogSize] = case when st.Logsize/1024/1024 &gt;= 1 then convert(varchar,convert(decimal(8,1),st.Logsize/1024/1024.0)) + ' TB' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; when st.Logsize/1024 &gt;= 1 then convert(varchar,convert(decimal(8,1),st.Logsize/1024.0)) + ' GB' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else cast(st.Logsize as varchar) + ' MB' end &nbsp; &nbsp; &nbsp; &nbsp;, [LogUsedPct] from sys.databases d &nbsp; &nbsp; &nbsp; &nbsp;left join @space st on d.database_id = st.databaseid order by d.[Name]<h3>Query to List Backup History for a Database</h3>SELECT &nbsp; &nbsp; &nbsp; &nbsp;s.database_name, &nbsp; &nbsp; &nbsp; &nbsp;m.physical_device_name, &nbsp; &nbsp; &nbsp; &nbsp;CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, &nbsp; &nbsp; &nbsp; &nbsp;CAST(DATEDIFF(second, s.backup_start_date, &nbsp; &nbsp; &nbsp; &nbsp;s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, &nbsp; &nbsp; &nbsp; &nbsp;s.backup_start_date, &nbsp; &nbsp; &nbsp; &nbsp;CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, &nbsp; &nbsp; &nbsp; &nbsp;CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, &nbsp; &nbsp; &nbsp; &nbsp;CASE s.[type] WHEN 'D' THEN 'Full' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN 'I' THEN 'Differential' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN 'L' THEN 'Transaction Log' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END AS BackupType, &nbsp; &nbsp; &nbsp; &nbsp;s.server_name, &nbsp; &nbsp; &nbsp; &nbsp;s.recovery_model FROM msdb.dbo.backupset s &nbsp; &nbsp; &nbsp; &nbsp;JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = DB_NAME() -- Remove this line for all the database &nbsp; &nbsp; &nbsp; &nbsp;AND s.[type] &lt;&gt; 'L' ORDER BY 1,2, backup_start_date DESC, backup_finish_date<h2>Types of SQL Server Backups</h2>There are several types of backups you can perform in SQL Server, each serving a different purpose:<ul><li>Full Backup: This type of backup creates a complete copy of the database, including all data and objects. &nbsp; &nbsp; &nbsp;For backing up to local drive: &nbsp; &nbsp; &nbsp;backup database [master] to disk = 'u:\Backup\master_20240308_FULL.bak' with STATS, CHECKSUM, COMPRESSION; &nbsp; &nbsp; &nbsp;For backing up to azure storage using key: &nbsp; &nbsp; &nbsp;backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_FULL.bak' with CREDENTIAL = 'SQLAzureStorage', STATS, CHECKSUM, COMPRESSION; &nbsp; &nbsp; &nbsp;For backing up to azure storage using SAS: &nbsp; &nbsp; &nbsp;backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_FULL.bak' with STATS, CHECKSUM, COMPRESSION; &nbsp; &nbsp; &nbsp;For backing up to azure fileshare: &nbsp; &nbsp; &nbsp;backup database [master] to disk = 'u:\Backup\master_20240308_FULL.bak' with STATS, CHECKSUM, COMPRESSION; <ul><li>Differential Backup: This backup captures only the changes made since the last full backup, reducing the backup size and time. &nbsp; &nbsp; &nbsp;For backing up to local drive: &nbsp; &nbsp; &nbsp;backup database [master] to disk = 'u:\Backup\master_20240308_DIFF.bak' with STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;&nbsp; &nbsp; &nbsp;For backing up to azure storage using key: &nbsp; &nbsp; &nbsp;backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_DIFF.bak' with CREDENTIAL = 'SQLAzureStorage', STATS, CHECKSUM,&nbsp;COMPRESSION, DIFFERENTIAL; &nbsp; &nbsp; &nbsp;For backing up to azure storage using SAS: &nbsp; &nbsp; &nbsp;backup database [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_DIFF.bak' with STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;&nbsp; &nbsp; &nbsp;For backing up to azure fileshare: &nbsp; &nbsp; &nbsp;backup database [master] to disk = 'u:\Backup\master_20240308_DIFF.bak' with STATS, CHECKSUM, COMPRESSION, DIFFERENTIAL;</li><li>Transaction Log Backup: This backup captures all transactions since the last transaction log backup, allowing for point-in-time recovery. &nbsp; &nbsp; &nbsp;For backing up to local drive: &nbsp; &nbsp; &nbsp;backup log [master] to disk = 'u:\Backup\master_20240308_0315.trn' with FORMAT, STATS, CHECKSUM, COMPRESSION;&nbsp; &nbsp; &nbsp;For backing up to azure storage using key: &nbsp; &nbsp; &nbsp;backup log [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_0315.trn' with CREDENTIAL = 'SQLAzureStorage', FORMAT, STATS, CHECKSUM, COMPRESSION; &nbsp; &nbsp; &nbsp;For backing up to azure storage using SAS: &nbsp; &nbsp; &nbsp;backup log [master] to URL = 'https://[storage account].blob.core.windows.net/[container name]/master_20240308_0315.trn' with FORMAT, STATS, CHECKSUM, COMPRESSION;&nbsp; &nbsp; &nbsp;For backing up to azure fileshare: &nbsp; &nbsp; &nbsp;backup log[master] to disk = 'u:\Backup\master_20240308_0315.trn' with STATS, CHECKSUM, COMPRESSION;</li></ul></li></ul>Choosing the right combination of these backup types is crucial for a robust backup strategy.<h2>Backup Frequency and Retention</h2>Deciding how often to perform backups and how long to retain them is a key aspect of backup planning. Consider the following factors:<ul><li>Frequency of data changes</li><li>Recovery point objectives (RPO)</li><li>Compliance and regulatory requirements</li></ul>Establishing a backup schedule and retention policy that aligns with your business needs is essential to ensure data availability and minimize data loss.<h2>Backup Storage and Security</h2>Storing your backups securely is as crucial as creating them. Consider the following best practices:<ul><li>Store backups on separate disks or network locations to prevent data loss in case of hardware failure.</li><li>Implement encryption to protect sensitive data in backups.</li><li>Regularly test your backup and restore processes to ensure data recoverability.</li></ul>By following these practices, you can enhance the security and reliability of your backup infrastructure.Create SQL Credential -&nbsp;Use this credential to backup to an Azure storage account with a storage key. CREATE CREDENTIAL [Any credential name] WITH IDENTITY = '[Azure storage account name]'&nbsp; &nbsp; &nbsp; &nbsp;, SECRET = '[Azure storage account key]'Shared Access Signature - Safer than a storage key since permissions are more granular and you can set an expiration date.&nbsp; Use this to back up to an Azure storage account. USE master CREATE CREDENTIAL [https://&lt;storage account name&gt;.blob.core.windows.net/&lt;container name&gt;] &nbsp; &nbsp; WITH IDENTITY='SHARED ACCESS SIGNATURE' -- ***this is a mandatory string and do not change it. &nbsp; &nbsp; , SECRET = 'sharedaccesssignature' -- ***this is the shared access signature token generated from azure storage account GO *** The SHARED ACCESS SIGNATURE secret should not have the leading ?.<h2>Automating Backup Tasks</h2>Automating backup tasks can help streamline the backup process and reduce the risk of human error. SQL Server provides built-in tools like SQL Server Agent for scheduling backups and maintenance tasks.<figure id="figure-7m3bfy9eqlltahjw4i"><img id="7m3bfy9eqlltahjw4i" src="https://images.unsplash.com/photo-1633412802994-5c058f151b66?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwxOHx8c3FsJTIwc2VydmVyJTIwYmFja3Vwc3xlbnwwfHx8fDE3MDk0MDgyNzZ8MA&amp;ixlib=rb-4.0.3&amp;q=80&amp;w=1080" alt="sql server backups" class="object-cover bg-gray-100 rounded-2xl md:rounded-3xl lg:rounded-4xl" style="aspect-ratio: 16/9;" data-mce-src="https://images.unsplash.com/photo-1633412802994-5c058f151b66?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwxOHx8c3FsJTIwc2VydmVyJTIwYmFja3Vwc3xlbnwwfHx8fDE3MDk0MDgyNzZ8MA&amp;ixlib=rb-4.0.3&amp;q=80&amp;w=1080" data-mce-style="aspect-ratio: 16/9;"></figure>By setting up automated backup jobs, you can ensure that backups are performed consistently and according to your defined schedule.<h2>Monitoring and Alerts</h2>Regularly monitoring your backup processes and setting up alerts for backup failures are essential steps to maintain the integrity of your backups. Utilize SQL Server's monitoring tools to track backup status and receive notifications in case of failures.<h2>Conclusion</h2>Implementing a robust backup strategy is crucial for safeguarding your SQL Server databases against data loss and ensuring business continuity. By understanding the different types of backups, establishing a backup schedule, securing your backups, automating backup tasks, and monitoring the process, you can effectively manage your backup operations and protect your valuable data.

SQL Server DBA Tools: Database Backups

Deadlocks can be a common issue in database management, causing delays and disruptions in your operations. In Azure SQL Database, detecting and resolving deadlocks is crucial to ensure the smooth functioning of your database. In this post, we will explore some strategies to help you identify and resolve deadlocks efficiently.<h2>Understanding Deadlocks</h2>Deadlocks occur when two or more processes or transactions are waiting for each other to release locks on resources, resulting in a deadlock situation where none of the processes can proceed. This can lead to performance degradation and even system crashes if not resolved promptly.<h3>Detecting Deadlocks</h3>Below are steps to detect and notify about deadlocks in Azure SQL Database and to monitor the system for deadlock graphs using tools like SQL Server Management Studio or Azure Portal. The output of those detections are XML data and Deadlock graphs that provide valuable information about the processes involved, the resources they are waiting for, and the deadlock victim.1. Set up Extended EventsCREATE EVENT SESSION [deadlocks] ON DATABASE&nbsp; ADD EVENT sqlserver.database_xml_deadlock_report ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO2. Create Azure Deadlock Alert Rule Notification<figure id="figure-43g6ystnlrlt0eaojx"><img id="43g6ystnlrlt0eaojx" src="https://cdn.durable.co/blocks/35q2CBJ5snrHHaPsgkUDJoLkl7oIxF7VpzbEMjrTXifgtpp6sywDbg9UOzyn5waD.png" alt="" class="object-cover bg-gray-100 " style="aspect-ratio: 1/1; object-position: 45% 45%;" data-corners="no-rounded" data-aspect="1:1" data-caption="" data-positionx="45%" data-positiony="45%" data-fullwidth="false" data-mce-src="https://cdn.durable.co/blocks/35q2CBJ5snrHHaPsgkUDJoLkl7oIxF7VpzbEMjrTXifgtpp6sywDbg9UOzyn5waD.png" data-mce-style="aspect-ratio: 1/1; object-position: 45% 45%;"></figure>3. Query the extended eventsDECLARE @tracename sysname = N'deadlocks';WITH ring_buffer AS ( &nbsp; &nbsp; SELECT CAST(target_data AS XML) as rb &nbsp; &nbsp; FROM sys.dm_xe_database_sessions AS s&nbsp; &nbsp; &nbsp; JOIN sys.dm_xe_database_session_targets AS t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8)) &nbsp; &nbsp; WHERE s.name = @tracename and &nbsp; &nbsp; t.target_name = N'ring_buffer' ), dx AS ( &nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dxdr.evtdata.query('.') as deadlock_xml_deadlock_report &nbsp; &nbsp; FROM ring_buffer &nbsp; &nbsp; CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata) )&nbsp; SELECT&nbsp; &nbsp; &nbsp; d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id], &nbsp; &nbsp; d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp], &nbsp; &nbsp; d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name], &nbsp; &nbsp; d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml, &nbsp; &nbsp; LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text FROM dx CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d) ORDER BY [deadlock_timestamp] DESC; GO<figure id="figure-niwilgwu9ult0etctq" data-full-width=""><img id="niwilgwu9ult0etctq" src="https://cdn.durable.co/blocks/36zuXBq4czP73FokATyYLrRrE7dGJXIlSunkJSCkwBuXZb2c0JlXRY4JWO2JELNV.png" alt="" class="object-cover bg-gray-100 " style="aspect-ratio: 16/9; object-position: 40% 50%;" data-corners="no-rounded" data-aspect="16:9" data-caption="" data-positionx="40%" data-positiony="50%" data-fullwidth="true" data-mce-src="https://cdn.durable.co/blocks/36zuXBq4czP73FokATyYLrRrE7dGJXIlSunkJSCkwBuXZb2c0JlXRY4JWO2JELNV.png" data-mce-style="aspect-ratio: 16/9; object-position: 40% 50%;"></figure>4. Display the Deadlock graphClick the Deadlock_XML and SQL Server Management Studio will open a new tab.&nbsp; Save that as an .xdl file and open that .xdl file in SSMS.&nbsp; From there you'll see the deadlock graph.<figure id="figure-r8k60y8woltagw5wi" data-full-width=""><img id="r8k60y8woltagw5wi" src="https://cdn.durable.co/blocks/9c3ipEnZ4KAjYGPbkbmSlkH28fSrkI8DCzMupAkLANq3qjDXClVuogM4pRNMiWQM.png" alt="" class="object-cover bg-gray-100 " style="aspect-ratio: 16/9; object-position: undefined undefined;" data-corners="no-rounded" data-aspect="16:9" data-caption="" data-positionx="undefined" data-positiony="undefined" data-fullwidth="true" data-mce-src="https://cdn.durable.co/blocks/9c3ipEnZ4KAjYGPbkbmSlkH28fSrkI8DCzMupAkLANq3qjDXClVuogM4pRNMiWQM.png" data-mce-style="aspect-ratio: 16/9; object-position: undefined undefined;"></figure> <h3>Resolving Deadlocks</h3>When it comes to resolving deadlocks, there are several approaches you can take:<ol><li>Optimizing Queries: Review and optimize your SQL queries to reduce the likelihood of deadlocks.</li><li>Use Indexes: Proper indexing can help minimize locking contention and reduce the chances of deadlocks.</li><li>Implement Retry Logic: Implement retry logic in your applications to handle deadlock situations gracefully.</li></ol><figure id="figure-62uzycs2yslt0dt4ps"><img id="62uzycs2yslt0dt4ps" src="https://images.unsplash.com/photo-1607827447187-5c09a69ae0b1?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwyfHxkZWFkbG9jayUyMHJlc29sdXRpb258ZW58MHx8fHwxNzA4Nzk3MzI3fDA&amp;ixlib=rb-4.0.3&amp;q=80&amp;w=1080" alt="deadlock resolution" class="object-cover bg-gray-100 rounded-2xl md:rounded-3xl lg:rounded-4xl" style="aspect-ratio: 16/9;" data-mce-src="https://images.unsplash.com/photo-1607827447187-5c09a69ae0b1?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwyfHxkZWFkbG9jayUyMHJlc29sdXRpb258ZW58MHx8fHwxNzA4Nzk3MzI3fDA&amp;ixlib=rb-4.0.3&amp;q=80&amp;w=1080" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Monitoring Performance</h3>Regularly monitoring the performance of your Azure SQL Database can help you identify potential deadlock issues before they escalate. Utilize tools like Azure Monitor to track performance metrics and set up alerts for any unusual spikes or patterns.<h2>Automating Deadlock Detection</h2>Consider automating the detection of deadlocks in your Azure SQL Database by setting up alerting mechanisms that notify you when a deadlock occurs. This proactive approach can help you address deadlocks promptly and minimize their impact on your operations.<h3>Consulting with Experts</h3>If you are facing persistent deadlock issues in your Azure SQL Database, don't hesitate to seek assistance from Datazip. They can provide valuable insights and recommendations to help you resolve complex deadlock scenarios.<figure id="figure-bc8mmgk7ilt0dt6y9"><img id="bc8mmgk7ilt0dt6y9" src="https://images.unsplash.com/photo-1626624338641-b99e0d32c958?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwyMXx8ZGF0YWJhc2UlMjBkZWFkbG9ja3xlbnwwfHx8fDE3MDg3OTcxNDV8MA&amp;ixlib=rb-4.0.3&amp;q=80&amp;w=1080" alt="database deadlock" class="object-cover bg-gray-100 rounded-2xl md:rounded-3xl lg:rounded-4xl" style="aspect-ratio: 16/9;" data-mce-src="https://images.unsplash.com/photo-1626624338641-b99e0d32c958?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwyMXx8ZGF0YWJhc2UlMjBkZWFkbG9ja3xlbnwwfHx8fDE3MDg3OTcxNDV8MA&amp;ixlib=rb-4.0.3&amp;q=80&amp;w=1080" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Conclusion</h3>Deadlocks can be a challenging issue to deal with in Azure SQL Database, but with the right strategies and tools, you can effectively detect and resolve them. By understanding the root causes of deadlocks, optimizing your queries, and implementing proactive monitoring, you can minimize the impact of deadlocks on your database performance.```

Blog

Tools for Azure Synapse Dedicated SQL Pool

SQL Server DBA Tools: List All Connections and Activities

SQL Server DBA Tools: Database Backups

Azure SQL DBA Tools: Deadlocks