Blog

<h3>Understanding HOBT_ID in SQL Server </h3><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-selected="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Overview</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">The HOBT_ID field in SQL Server represents the Heap or B-Tree ID. It uniquely identifies the allocation unit associated with a specific table or index. This field is primarily used for storage management and query optimization by the SQL Server engine.</td></tr></tbody></table><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Definition and Purpose</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;"><ul><li>HOBT_ID is a BigInt value that uniquely identifies a Heap or B-Tree structure within a database.</li><li>It is used to track and manage the physical storage of tables and indexes.</li><li>Each table or index has a corresponding HOBT_ID that SQL Server uses internally for performance and storage operations.</li></ul></td></tr></tbody></table><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Usage in System Views</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">Several system views expose the HOBT_ID for analysis and troubleshooting:<ol><li>sys.partitions</li></ol><ul><li>Contains one row per partition of a table or index.</li><li>Column: hobt_id – Identifies the Heap or B-Tree for the partition.</li><li>Example Query:</li></ul>SELECT TableName = o.name&nbsp; &nbsp; &nbsp; &nbsp;, IndexName = i.name&nbsp; &nbsp; &nbsp; &nbsp;, SchemaName = SCHEMA_NAME(o.schema_id)&nbsp; FROM sys.partitions p &nbsp; &nbsp; &nbsp;JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID&nbsp; &nbsp; &nbsp; &nbsp;JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND p.index_id = i.index_id&nbsp; WHERE p.hobt_id = 72057594040811520&nbsp;2. sys.allocation_units<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100.004%;" data-mce-style="width: 100.004%;"><ul><li>Maps allocation units to their corresponding storage structures.</li><li>Column: container_id (which is equal to hobt_id for IN_ROW_DATA allocations).</li><li>Example Query:</li></ul>SELECT au.allocation_unit_id &nbsp; &nbsp; &nbsp;, au.type_desc &nbsp; &nbsp; &nbsp;, au.container_id &nbsp; &nbsp; &nbsp;, p.hobt_id FROM sys.allocation_units au &nbsp; &nbsp; &nbsp;JOIN sys.partitions p ON au.container_id = p.hobt_id WHERE p.object_id = OBJECT_ID('YourTableName'); </td></tr></tbody></table>&nbsp;3. sys.dm_db_index_physical_stats<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100.004%;" data-mce-style="width: 100.004%;"><ul><li>Provides physical statistics for indexes and tables.</li><li>Column: hobt_id – Used to join with sys.partitions for further analysis.</li><li>Example Query:</li></ul>SELECT index_id &nbsp; &nbsp; &nbsp;, index_type_desc &nbsp; &nbsp; &nbsp;, partition_number &nbsp; &nbsp; &nbsp;, hobt_id FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, 'DETAILED');</td></tr></tbody></table><h3>Relationship with Tables and Indexes</h3></td></tr></tbody></table><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-selected="1"><tbody><tr><td style="width: 100%;"><ul><li>For Heap Tables (tables without a clustered index), hobt_id maps directly to the table's heap storage structure.</li><li>For Clustered Indexes, hobt_id represents the clustered index's B-Tree structure.</li><li>For Non-Clustered Indexes, each index has its own unique hobt_id corresponding to its B-Tree structure.</li></ul></td></tr><tr><td style="width: 100%;"><h3>Practical Applications</h3></td></tr><tr><td style="width: 100%;"><ul><li>Storage Analysis: Identifying how data is allocated and stored in the database.</li><li>Index Fragmentation Checks: Using sys.dm_db_index_physical_stats in conjunction with hobt_id.</li><li>Performance Tuning: Understanding how indexes and partitions are being utilized.</li><li>Troubleshooting Corruption Issues: Correlating allocation units with table partitions.</li></ul></td></tr><tr><td style="width: 100%;"><h3>Conclusion</h3></td></tr><tr><td style="width: 100%;">The HOBT_ID field is an essential part of SQL Server's internal architecture, providing a means to track and manage storage structures efficiently. Understanding its usage can help in performance tuning, storage analysis, and troubleshooting of database objects.</td></tr></tbody></table> <figure id="figure-8hujl1bdmdm85275p9"><img id="8hujl1bdmdm85275p9" src="https://media.gettyimages.com/id/1485069425/photo/6g-technology-concept-data-center-and-shots-multiple-rows-of-fully-operational-server-racks.jpg?b=1&amp;s=2048x2048&amp;w=0&amp;k=20&amp;c=gtcptdJujvZ-DtxIoE8Mz9eo45OhXP0qTDVu12-2IJ0=" alt="sql server optimization" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://media.gettyimages.com/id/1485069425/photo/6g-technology-concept-data-center-and-shots-multiple-rows-of-fully-operational-server-racks.jpg?b=1&amp;s=2048x2048&amp;w=0&amp;k=20&amp;c=gtcptdJujvZ-DtxIoE8Mz9eo45OhXP0qTDVu12-2IJ0=" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Indexing for Improved Query Performance</h3>One of the most effective ways to optimize your SQL Server is through indexing. Indexes are data structures that improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space. By creating appropriate indexes, you can dramatically reduce query response times and enhance overall performance.When designing indexes, consider using clustered indexes for columns that are frequently searched, as they determine the physical order of data in a table. Additionally, non-clustered indexes can be useful for columns that are often included in search conditions or join operations.<h3>Optimizing Queries for Better Performance</h3>Writing efficient queries is crucial for optimizing SQL Server performance. Poorly written queries can lead to slow response times and increased resource consumption. To optimize your queries, start by analyzing query execution plans to identify bottlenecks and areas for improvement.<figure id="figure-28i6nmkc0im85275p9"><img id="28i6nmkc0im85275p9" src="https://media.gettyimages.com/id/652179644/photo/light-bulb-business-chalkboard-background.jpg?b=1&amp;s=2048x2048&amp;w=0&amp;k=20&amp;c=Q0lUZLqSMPaOtdwrBB0VcNlDQzSRJzBHQSETQ9Yc_Ng=" alt="query optimization" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://media.gettyimages.com/id/652179644/photo/light-bulb-business-chalkboard-background.jpg?b=1&amp;s=2048x2048&amp;w=0&amp;k=20&amp;c=Q0lUZLqSMPaOtdwrBB0VcNlDQzSRJzBHQSETQ9Yc_Ng=" data-mce-style="aspect-ratio: 16/9;"></figure>Avoid using SELECT * statements as they retrieve all columns, which can be resource-intensive. Instead, specify only the columns you need. Additionally, leverage subqueries and joins wisely to ensure they don't unnecessarily complicate or slow down your operations.<h3>Data Compression Techniques</h3>Data compression can significantly reduce storage requirements and improve query performance by decreasing I/O operations. SQL Server offers several compression options, such as row-level and page-level compression. These techniques can help you save space without sacrificing performance.Implementing compression can lead to substantial cost savings, particularly for businesses dealing with large databases. However, it's important to evaluate the trade-offs between compression efficiency and CPU usage to ensure that performance gains are maximized.<figure id="figure-8nclf6zmj8m85275p9"><img id="8nclf6zmj8m85275p9" src="https://media.gettyimages.com/id/1434069732/photo/abstract-gravity-wave-background.jpg?b=1&amp;s=2048x2048&amp;w=0&amp;k=20&amp;c=p869Upavf9X2l_kIvQ_fc2RmpS9VK110L5LeHjRPTBQ=" alt="data compression" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://media.gettyimages.com/id/1434069732/photo/abstract-gravity-wave-background.jpg?b=1&amp;s=2048x2048&amp;w=0&amp;k=20&amp;c=p869Upavf9X2l_kIvQ_fc2RmpS9VK110L5LeHjRPTBQ=" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Regular Database Maintenance</h3>Regular maintenance is essential for keeping your SQL Server running optimally. This includes tasks such as updating statistics, rebuilding fragmented indexes, and backing up data. Keeping your database well-maintained ensures smooth operations and minimizes downtime.Automating these maintenance tasks with SQL Server Agent can help you save time and reduce the risk of human error. Regular monitoring and adjustments based on performance metrics will further enhance your database's efficiency.<h2>Conclusion: Unlocking the Full Potential of SQL Server</h2>By implementing these data optimization techniques, businesses can unlock the full potential of their SQL Server databases. Whether it's through effective indexing, query optimization, data compression, or regular maintenance, each strategy contributes to improved performance and cost efficiency.Continually evaluating and adjusting your optimization strategies will ensure your SQL Server remains a powerful asset in managing your business's data needs effectively. Embrace these techniques and watch your business thrive in the competitive data landscape.

SQL DBA Tools: HOBT_ID Associated ObjectID

Here's a t-sql query one can use to get a list of all table index recommendations in either SQL Server or Azure SQL.&nbsp; The improvement measure column provides how much of an improvement that specific index will have on your database.&nbsp; The user seeks and last_user_seek column provide details on the amount of and last use scenario of the particular index was supplied.SELECT &nbsp; &nbsp; &nbsp;improvement_measure =&nbsp; migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) &nbsp; &nbsp; &nbsp;,&nbsp; create_index_statement = 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ' ON ' + mid.statement &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ' (' + ISNULL (mid.equality_columns,'') &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ISNULL (mid.inequality_columns, '') &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ')' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ISNULL (' INCLUDE (' + mid.included_columns + ')', '')&nbsp; &nbsp; &nbsp;, migs.*, mid.database_id, mid.[object_id]FROM sys.dm_db_missing_index_groups mig &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handleWHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) &gt; 10ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

SQL DBA Tools: Index Recommendations

Laptop monitoring a cloud computing server room

Here's a simple query you can run within the master database on any Azure SQL Server to view all server connections that failed.&nbsp; The details about the connection failure isn't too specific but a DBA can use it to generally find the root cause of a connection failure.<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;" data-mce-selected="1"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;">SELECT database_name &nbsp; &nbsp; &nbsp;, start_time &nbsp; &nbsp; &nbsp;, end_time &nbsp; &nbsp; &nbsp;, event_category &nbsp; &nbsp; &nbsp;, event_type &nbsp; &nbsp; &nbsp;, event_subtype &nbsp; &nbsp; &nbsp;, event_subtype_desc &nbsp; &nbsp; &nbsp;, severity &nbsp; &nbsp; &nbsp;, event_count &nbsp; &nbsp; &nbsp;, description FROM master.sys.event_log WHERE event_type = 'connection_failed'</td></tr></tbody></table>

Azure SQL DBA Tools: Connection Failure Logs

<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;<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-selected="1"><tbody><tr><td style="width: 100%;"><h3>Columnstore Index Optimization</h3></td></tr><tr><td style="width: 100%;">Run the below query to create a view on the SQL Pool. &nbsp;The last column on this view generates a SQL statement that can be used to rebuild your indexes:CREATE VIEW dbo.vColumnstoreDensity AS SELECT &nbsp; &nbsp; &nbsp; &nbsp; GETDATE() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [execution_date] , &nbsp; &nbsp; &nbsp; DB_Name() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [database_name] , &nbsp; &nbsp; &nbsp; s.name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS [schema_name] , &nbsp; &nbsp; &nbsp; t.name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS [table_name] , &nbsp; &nbsp; &nbsp; MAX(p.partition_number) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [table_partition_count] , &nbsp; &nbsp; &nbsp; SUM(rg.[total_rows]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS [row_count_total] , &nbsp; &nbsp; &nbsp; SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [row_count_per_distribution_MAX] , &nbsp; &nbsp; &nbsp; CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 0 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 1 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [OPEN_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 2 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [CLOSED_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 3 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_DELETED] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; 'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [Rebuild_Index_SQL] FROM &nbsp; &nbsp;sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg JOIN &nbsp; &nbsp;sys.[pdw_nodes_tables] nt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON &nbsp;rg.[object_id] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= nt.[object_id] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND rg.[pdw_node_id] &nbsp; &nbsp; &nbsp; &nbsp;= nt.[pdw_node_id] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND rg.[distribution_id] &nbsp; &nbsp;= nt.[distribution_id] JOIN &nbsp; &nbsp;sys.[pdw_permanent_table_mappings] mp &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON &nbsp;nt.[name] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = mp.[physical_name] JOIN &nbsp; &nbsp;sys.[tables] t &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON &nbsp;mp.[object_id] &nbsp;= t.[object_id] JOIN &nbsp; &nbsp;sys.[schemas] s &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON t.[schema_id] &nbsp; &nbsp;= s.[schema_id] JOIN &nbsp; &nbsp;sys.[partitions] p &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON P.object_id &nbsp; &nbsp; &nbsp;= t.object_id GROUP BY &nbsp; &nbsp; &nbsp; &nbsp; s.[name] , &nbsp; &nbsp; &nbsp; t.[name];</td></tr><tr><td style="width: 100%;">Now that you have created the view, run this query to identify tables with row groups with less than 100-K rows. You may want to increase the threshold of 100 K if you're looking for more optimal segment quality.SELECT &nbsp; &nbsp;* FROM &nbsp; &nbsp;[dbo].[vColumnstoreDensity] WHERE &nbsp; &nbsp;COMPRESSED_rowgroup_rows_AVG &lt; 100000 &nbsp; &nbsp; &nbsp; &nbsp; OR INVISIBLE_rowgroup_rows_AVG &lt; 100000;Once you have run the query, you can begin to look at the data and analyze your results. This table explains what to look for in your row group analysis.</td></tr></tbody></table>

Azure SQL DBA Tools: Manage 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.

Blog

SQL DBA Tools: HOBT_ID Associated ObjectID

SQL DBA Tools: Index Recommendations

Azure SQL DBA Tools: Connection Failure Logs

Azure SQL DBA Tools: Manage Azure Synapse Dedicated SQL Pool

SQL Server DBA Tools: List All Connections and Activities

SQL Server DBA Tools: Database Backups