SQL Swiss Army Knife #11.1 - Locking, blocking and active transactions
First published on MSDN on Sep 18, 2012 Latest update: 12/2/2016 (Check change log)Download on GitHubHello all,Following the series "SQL Swiss Army Knife", here is another version of this script that I’ve been using and tweaking for years now.SQL Server 2022 for RHEL 9 and Ubuntu 22.04 is now Generally Available (GA)
We are happy to announce that Starting CU 10 release for SQL Server 2022 , SQL Server 2022 on RHEL 9 and Ubuntu 22.04 is now generally available (GA), meaning you can run production workload on SQL Server 2022 deployed on Red Hat Enterprise Linux (RHEL) 9 and Ubuntu 22.04. For SQL Server 2022 on RHEL 9, the SELinux integration that enables you to run SQL Server 2022 on RHEL 9 as confined application is also now generally available. If you've had the preview packages installed previously, we recommend that you please uninstall the preview bits and upgrade to SQL Server 2022 GA packages available at the following repos, for steps on how to update repos refer the Configure repositories for installing and upgrading SQL Server on Linux - SQL Server | Microsoft Learn documentation. For RHEL 9 use the repo: https://packages.microsoft.com/config/rhel/9/ For Ubuntu 22.04 use the repo: https://packages.microsoft.com/config/ubuntu/22.04/ For further details on getting started with the installation of SQL Server 2022 on RHEL 9 refer: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn & for SQL Server on Ubuntu 22.04 refer: Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn SQL Server 2022 container images on RHEL 9 & Ubuntu 22.04 also generally available: SQL Server 2022 on RHEL 9 and Ubuntu 22.04 are also available as container images and generally available (GA). It is easy for you to get started. Like always, you can use both podman and/or docker tool. To pull and run the production ready SQL Server 2022 images on RHEL 9 use the tag: 2022-CU10-rhel-9.1 #Pull the image podman pull mcr.microsoft.com/mssql/rhel/server:2022-CU10-rhel-9.1 #Run a container using the above image podman run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Developer' -h sql22rhel9 --name sql22rhel9 -p 1433:1433 -d mcr.microsoft.com/mssql/rhel/server:2022-CU10-rhel-9.1 To pull and run the production ready SQL Server 2022 images on Ubuntu 22.04 use the tag: 2022-CU10-ubuntu-22.04 #Pull the image docker pull mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04 #Run a container use the above image docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Developer' -h sql22ubu2204 --name sql22ubu2204 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04
SQL Server 2019 Intelligent Performance -Worker Migration
SQL Server task scheduling basics A few basic terms: Scheduler – each scheduler is mapped to an individual logical processor (DMV sys.dm_os_schedulers) Worker – each worker is mapped to a thread 1 (DMV sys.dm_os_workers) Task – each task is mapped to a series of programming instructions that form a unit of work. The task is then bound to a worker in order to execute the instructions on a CPU. (DMV sys.dm_os_tasks) Please reference SQL Server Thread and Task Architecture Guide for comprehensive explanation. 1 Unless the SQL Server instance is set to fiber mode, which is generally not recommended. The following graph is a high-level life cycle workflow of a task from the moment that it is bound to a worker to the time that it is unbound from its worker after task completion. Mechanism to balance CPU usage in previous versions of SQL Server SQL Server internally tracks the current task count as load factor for each scheduler and always assigns a newly created task to the least loaded scheduler for balanced CPU usage. This mechanism works well for typical short-running query tasks. For long-running tasks, this mechanism may not always sustain evenly distributed CPU usage across schedulers. It is possible for more than one long-running task to be assigned to the same scheduler when other schedulers have higher task loads (which are all short-running tasks) at the moment the long-running task is assigned to a scheduler. Workers are bound to the same scheduler for the life of the worker, and a task is bound to a worker for its entire execution duration. This means when other schedulers become idle or have fewer tasks, long-running tasks cannot be re-assigned to these schedulers because they are bound to their originally assigned scheduler. This can lead to an unbalanced workload with multiple competing runnable workers on a single scheduler unable to utilize available CPU cycles on other idle schedulers. With computer hardware evolution, SQL Server customers gradually switch to host machines with a higher number of CPUs. Imbalanced CPU usage in the SQL Server engine becomes a more visible problem these days. What is worker migration Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue. A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage. Availability group parallel redo tasks with worker migration In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas. Availability group parallel redo is the default redo mode starting with SQL Server 2016 RTM and later versions. For each database in an availability group secondary replica, a set of parallel redo worker tasks are created for this database in addition to its main redo task. The main redo task is dedicated to analyzing incoming log records and dispatching them to different parallel redo workers. Each parallel redo worker processes assigned log records and applies changes to data pages. Both the main redo task and parallel redo worker tasks are long-running tasks. After a database successfully starts its parallel redo worker tasks during database startup phase, these parallel redo worker tasks will stay alive until the database is shut down. The main redo task also keeps running as long as there are transaction logs to process and does not stay idle for more than 60 seconds. For a heavy transaction workload database on the availability group primary replica, the main redo task of the database on a secondary replica is generally CPU-intensive. Its parallel redo worker tasks may have high CPU demand depending on database configuration, table schema and transaction types. It is possible for one or more parallel redo worker tasks of a database to be assigned to the same scheduler as its main redo task. If more than one long-running redo task competes for CPU cycles on the same scheduler, scheduler contention may occur. When this happens, the overall transaction log redo throughput of the impacted database can drop 20-30%. When several availability group databases have heavy redo workloads during the same period, this scheduler contention pattern is more common because of the increased chance for one or more database main redo tasks to be allocated to the same scheduler with parallel redo worker tasks. Worker migration avoids this performance degradation and maintains more consistent redo throughput for a database. For multiple database concurrent redo workloads, the overall redo throughput has about 10-15% improvement compared to the situation when there is scheduler contention between some main redo tasks and parallel redo worker tasks. To monitor worker migration occurrence, a new column “worker_migration_count” is added into the existing DMV sys.dm_os_workers. It is a cumulative indicator of the number of migrations that have happened to a worker since the last SQL Server service startup. Also, for parallel redo worker task records (command=”PARALLEL REDO TASK” or “PARALLEL REDO HELP TASK”) from the output of sys.dm_exec_requests, their scheduler_id values are expected to change frequently when the corresponding database has ongoing redo workloads. T-SQL query samples: -- Query worker_migration_count column in DMV sys.dm_os_workers SELECT worker_address, worker_migration_count FROM sys.dm_os_workers ORDER BY worker_migration_count DESC -- Query parallel redo tasks in DMV sys.dm_exec_requests -- (scheduler_id values may change for some rows when re-running this query) SELECT scheduler_id, session_id, task_address, command FROM sys.dm_exec_requests WHERE command = 'PARALLEL REDO TASK' OR command = 'PARALLEL REDO HELP TASK' ORDER BY session_id Next Steps In the future, other SQL Server system long-running tasks may be evaluated for enabling worker migration to avoid scheduler contention. One such example would be the availability group main redo task, which can also be impacted by scheduler contention. When the main redo tasks of two databases are assigned to the same scheduler, or a main redo task shares the same scheduler with a long-running and CPU intensive query task, redo performance can drop 30% or more. Dong Cao Principal Software EngineerDeploy Always On Availability Groups (AG) on SQL Server RHEL based Azure VMs - The Ansible Way!
1) Setting up and Configuring pacemaker cluster on three SQL Server RHEL based Azure VMs. 2) Configuring and deploying SQL Server availability groups (AGs) on these nodes. 3) Creating the pacemaker cluster resources for the AG with fencing agent. 4) Optionally, creating the AG listener service. All of this under 8 minutes !! You want to see how? Here we go...Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY
In SQL Server 2019 CTP 3.1 a new index option was added called OPTIMIZE_FOR_SEQUENTIAL_KEY that is intended to address a common issue known as last page insert contention. This articles takes a closer look at how the technology behind this new option works and the problem it attempts to address.Enabling Azure Key Vault for SQL Server on Linux
Enhancing Security with EKM using Azure Key Vault in SQL Server on Linux: We’re excited to announce that Extensible Key Management (EKM) using Azure Key Vault in SQL Server on Linux is now generally available from SQL Server 2022 CU12 onwards, which allows you to manage encryption keys outside of SQL Server using Azure Key Vaults. In this blog post, we’ll explore how to leverage Azure Key Vault as an EKM provider for SQL Server on Linux. Azure Key Vault: The Bridge to Enhanced Security is a cloud-based service that securely stores keys, secrets, and certificates. By integrating Azure Key Vault with SQL Server, you can benefit from its scalability, high performance, and high availability. Refer Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key Vault - SQL Server | Microsoft Learn for more details. Setting Up EKM with Azure Key Vault Here’s a streamlined version of the setup process for EKM with Azure Key Vault on SQL Server for Linux: Initialize a Microsoft Entra service principal. Establish an Azure Key Vault. Set up SQL Server for EKM and register the SQL Server Connector. Finalize SQL Server configuration. The full guide for setting up AKV with SQL Server on Linux is available here Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key Vault - SQL Server | Microsoft Learn . For SQL on Linux, omit steps 3 and 4 and proceed directly to step 5. I’ve included screenshots below for your quick reference that covers the SQL Server configuration to use AKV. Run the below commands to enable EKM in SQL Server and register the SQL Server Connector as EKM provider. Please note: SQL Server requires manual rotation of the TDE certificate or asymmetric key, as it doesn’t rotate them automatically. Regular key rotation is essential for maintaining security and effective key management. Conclusion Using Azure Key Vault for EKM with SQL Server on Linux boosts security, streamlines key management, and supports compliance. With data protection being paramount, Azure Key Vault’s integration offers a robust solution. Stay tuned for more insights on SQL Server on Linux! :old_key:️:locked: Official Documentation: Extensible Key Management using Azure Key Vault - SQL Server Setup Steps for Extensible Key Management Using the Azure Key Vault Azure Key Vault Integration for SQL Server on Azure VMsSQL Server 2016/2017: Availability group secondary replica redo model and performance
First published on MSDN on Jun 01, 2018 When availability group was initially released with SQL Server 2012, the transaction log redo was handled by a single redo thread for each database in an AG secondary replica.Adaptive Index Defrag
First published on MSDN on Jul 03, 2011 Latest update: 1/20/2017 (Check change log)Download on GitHubNOTE: if using SQL Server 2012 or SQL Server 2014, mind there is a bug where the online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows.
