Month: June 2021

Useful queries to find out who is using my TempDB

;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

Query Performance Insight for Azure SQL Database

Query Performance Insight provides intelligent query analysis for single and pooled databases. It helps identify the top resource consuming and long-running queries in your workload. This helps you find the queries to optimize to improve overall workload performance and efficiently use the resource that you are paying for. Query Performance Insight helps you spend less time troubleshooting database performance by providing:

  • Deeper insight into your databases resource (DTU) consumption
  • Details on top database queries by CPU, duration, and execution count (potential tuning candidates for performance improvements)
  • The ability to drill down into details of a query, to view the query text and history of resource utilization.

Query Performance Insight requires that Query Store is active on your database. It’s automatically enabled for all databases in Azure SQL Database by default. If Query Store is not running, the Azure portal will prompt you to enable it.

Elastic Database vs Azure Pass Database

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price.

Azure SQL Database has two similar flavors – Managed Instance and Elastic pools. Both flavors enables placing multiple databases that share the same resources and in both cases can be changed cpu/storage/DTU for entire group of database within the instance/pool. 

Azure SQL Database Elastic Pool is a shared resource model for Single Azure SQL PaaS databases to achieve higher resource utilization efficiency, and all the databases within an elastic pool share predefined resources within the same elastic pool.

Lease Timeouts and Health Checks in AG

In always on availability group

Suppose you have an availability group configured for automatic failover. In this synchronized availability group, we can have the following types of failover.

  • Automatic
  • Manual
  • You can do forced fail over with possible data loss

In the case of automatic failover, the availability group fails over to a synchronized secondary replica without any data loss. However, an automatic failover requires the satisfied conditions defined in the flexible failover policy.

The resource host acts as an interface between the cluster resource and service. SQL Server uses resource DLLs for the resource monitor. These DLLs are responsible for health monitoring and offline-online resource management.

In the below image, we see two levels of monitoring in an AG configuration.

  • Looks-Alive
  • Is-Alive

simple words : Windows failover cluster in Cluster resource DLL check the Look Alive SQL server service status and lease validity IS ALIVE check the SQL server health

In a Windows failover cluster, the cluster service continuously transmits heartbeat to other cluster nodes and waits for the acknowledgment. If it does not receive a heartbeat acknowledgment after a series of heartbeats, it assumes that the corresponding node is down. Subsequently, it broadcasts a message to other cluster nodes, and a node takes over the cluster ownership based on the quorum votes and majority configuration. If due to any reason, the cluster could not determine the quorum majority, all nodes in a cluster go into the resolving state and all cluster resources are taken offline. This process is known as Looks-Alive and runs every 5 seconds. The Windows failover cluster uses the Looks-Alive mechanism for an integration between the cluster resource host and SQL process. On the primary replica, SQL Server uses a dedicated lease worker thread.

  • Lease timeout:
    • Default value: 20000 milliseconds, i.e. 20 seconds
    • It is required to prevent a split-brain scenario in the Windows failover cluster
    • It can trigger an AG failover or offline-online
    • It is used in both Is-Alive and the Looks-alive mechanism
  • Session Timeout:
    • It is used to safeguard against soft errors between AG replicas
    • default value: 10000 milliseconds or 10 seconds
    • It is not part of the Is-Alive or the Looks-alive mechanism
    • Secondary replica becomes DISCONNECTED status due to session timeout
    • You can configure the session timeouts in the availability group properties from the primary replica instance
  • Health Check timeout
    • Default value: 30000 milliseconds, i.e. 30 seconds
    • It is the timeout if the sp_server_diagnostics does not return any data or reports errors in the health check
    • It is used in both Is-Alive and the Looks-alive mechanism
    • It also depends on the failover condition levels defined from 1-5, as explained earlier. The default configuration is level 3

Deadlock Priority

By default, SQL Server chooses as the deadlock victim the transaction that is least expensive to rollback. In simple terms, a transaction that makes the fewest changes to the database is considered the least expensive.

However, users can set custom priorities for a particular transaction using the SET DEADLOCK_PRIORITY statement. The process with the lowest deadlock priority will then be the one chosen as the deadlock victim.

Example: SET DEADLOCK_PRIORITY NORMAL.

Rules:

SQL Server selects deadlock victim following these rules:

  1. The process with the lowest deadlock priority is set as deadlock victim.
  2. If the deadlock priority of all the processes involved in deadlock is same, then the process that is least expensive to rollback is selected as deadlock victim.
  3. If both the deadlock priority and cost of processes involved in deadlock is same, then the process a process is selected randomly as deadlock victim.
  • 1204 – this provides information about the nodes involved in the deadlock
  • 1222 – returns deadlock information in an XML format

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

You can find the system_health extended event session in SQL Server. It is like a black box recorder that captures useful information and helps to troubleshoot the issues in SQL Server