One holistic way of monitoring server performance is to evaluate what the server is waiting on. SQL Server is instrumented with a wait tracking system, which monitors each running thread and logs what resources the thread is waiting on. Wait statistics are broken down into three types of waits: resource waits, queue waits, and external waits.

• Resource waits occur when a worker thread in SQL Server requests access to a resource that is currently being used by a thread. Examples of resources waits are locks, latches, and disk I/O waits.

• Queue waits occur when a worker thread is idle and waiting for work to be assigned. Example queue waits are deadlock monitoring and deleted record cleanup.

• External waits occur when SQL Server is waiting on an external process like a linked server query to complete. An example of an external wait is a network wait related to returning a large result set to a client application.

This data is aggregated in the DMV sys.dm_os_wait_stats (and in sys.dm_db_wait_stats in Azure SQL Database) and is also tracked for active sessions in sys.dm_exec_session_wait_stats. These statistics (SQL Server tracks over 900 wait types) allow the DBA to get an overview of the performance of the server, and to readily identify configuration or hardware issues. This data is persisted from the time of instance startup, but the data can be cleared as needed to observe changes.

Wait statistics are evaluated as a percentage of the total waits on the server.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-wait-stats-azure-sql-database?view=azuresqldb-current

The results of this query from sys.dm_os_wait_stats shows the wait type, and the aggregation of Percent of time waiting (Wait Percentage) and the average wait time in seconds for each wait type. In this case, the server has Always On Availability Groups in place, as indicated by the REDO_THREAD_PENDING_WORK and PARALLEL_REDO_TRAN_TURN wait types. The relatively high percentage of CXPACKET and SOS_SCHEDULER_YIELD waits indicates that this server is under some CPU pressure.

There are many wait types that relate to specific and common SQL Server performance issues:

• RESOURCE_SEMAPHORE waits—this wait type is indicative of queries waiting on memory to become available, and may indicate excessive memory grants to some queries. This problem is typically observed by long query runtimes or even time outs. These wait types can be caused by out-of-date statistics, missing indexes, and excessive query concurrency.

• LCK_M_X waits—frequent occurrences of this wait type can indicate a blocking problem, that can be solved by either changing to the READ COMMITTED SNAPSHOT isolation level, or making changes in indexing to reduce transaction times, or possibly better transaction management within T-SQL code.

• PAGEIOLATCH_SH waits—this wait type can indicate a problem with indexes (or a lack of useful indexes), where SQL Server is scanning too much data. Alternatively, if the wait count is low, but the wait time is high, it can indicate storage performance problems. You can observe this behaviour by analyzing the data in the waiting_tasks_count and the wait_time_ms in the sys.dm_os_wait_stats DMV, to calculate an average wait time for a given wait type.

• SOS_SCHEDULER_YIELD waits---this wait type can indicate high CPU utilization, which is correlated with either high number of large scans, or missing indexes, often in conjunction with high numbers of CXPACKET waits.

• CXPACKET waits—If this wait type is high it can indicate improper configuration. Prior to SQL Server 2019, the Max Degree of Parallelism (MaxDOP) default setting is to use all available CPUs for queries. Additionally, the cost threshold for parallelism (CTfP) setting defaults to 5, which can lead to small queries being executed in parallel, which can limit throughput. Lowering MaxDOP and increasing CTfP can reduce this wait type, but the CXPACKET wait type can also indicate high CPU utilization, which is typically resolved through index tuning.

• PAGEIOLATCH_UP—This wait type on data pages 2:1:1 can indicate TempDB contention on Page Free Space (PFS) data pages. Each data file has one PFS page per approximately 64MB of data. This wait is typically caused by only having one TempDB file, as prior to SQL Server 2016 the default behaviour was to use one data file for TempDB. The best practice is to use one file per CPU core up to eight files. It is also important to ensure your TempDB data files are the same size and have the same autogrowth settings to ensure they are used evenly. SQL Server 2016 and higher control the growth of TempDB data files to ensure they grow in a consistent, simultaneous fashion.

In addition to the aforementioned DMVs, the Query Store tracks waits associated with a given query. This data is not tracked at the same granularity as the data in the DMVs but can provide a nice overview of what a query is waiting on.

https://www.sqlskills.com/blogs/paul/using-the-wait-statistics-report-in-azure-data-studio/

To run queries in Azure Data Studio, it's similar to SSMS. Right-click a database or server name, and select New query.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-deploy-configure/4-exercise-verify-database

Extended events are a lightweight and very extensive diagnostic system that is built into SQL Server and Azure SQL Database and managed instance. Extended events allow you to collect additional information about the internal operations of your databases. Historically, DBAs used a tool call Profiler to trace inbound queries and gather execution plans to identity to problematic queries and their execution plans. Extended events build on the functionality of SQL Server Profiler by allowing you to trace queries and by exposing additional data (events) that you can monitor.

Some examples of issues you might troubleshoot with Extended Events include:

• Troubleshooting blocking and deadlocking performance issues

• Identifying long-running queries

• Monitoring Data Definition Language (DDL) operations

• Logging missing column statistics

• Observing Memory Pressure in your database

• Long-running physical I/O operations

The extended event framework also allows you to use filters to limit the amount of data you collect in order to reduce the overhead of data collection, and allows you to more easily identity your performance problem by targeting your focus onto specific areas.

Extended events cover the full surface area of SQL Server, and are divided into four channels, which define the audience of an event.

• Admin - Admin events are targeted for end users and administrators. The events included indicate a problem within a well-defined set of actions an administrator can take. An example of this is the generation of an XML deadlock report to help identity the root cause of the deadlock.

• Operational - Operational events are used for analysis and diagnostics or common problems. These events can be used to trigger an action or task based on an occurrence of the event. An example of an operational event would be a database in an Availability Group changing state, which would indicate a failover.

• Analytic - Analytic events are typically related to performance events and are published in high volume. Tracing stored procedure or query execution would be an example of an analytic event.

• Debug - Debug events are not necessarily fully documented and you should only use them when troubleshooting in conjunction with Microsoft support.

Events are added to sessions, which can host multiple events. Typically, multiple events are grouped together in a session to capture a related set of information.

https://docs.microsoft.com/en-us/azure/azure-sql/database/xevent-db-diff-from-svr

The Query Store provides database administrators with in-depth insights on query plans and performance metrics. By default, execution plans evolve over time due to schema changes, index modifications, or changes to the data that cause updates to the statistics. This evolution can cause queries to perform poorly as the execution plan no longer meets the demands of the given query. The Query Store in Azure SQL features a plan forcing option to always force a given execution plan.

Automatic Tuning Features

SQL Server 2017 introduced a feature called automatic tuning. Automatic tuning allows for the gathering and applying machine learning against performance metrics to provide suggested improvements or even allow for self-correction. Automatic tuning, whether on-premises or in the cloud, allows you to identify issues caused by query execution plan regression. Additionally, in Azure SQL Database you have the option to further improve query performance by index tuning. Azure SQL Database automatic tuning can identify indexes that should be added or even removed from the database to enhance query performance.

Automatic Plan Correction

Using Query Store, the database engine can identify when query execution plans have regressed in their performance. While you can manually identify a regressed plan through the user interface, the Query Store also provides the option to notify you automatically.

In the example above, you can see a check mark on Plan ID 1, which means that the plan has been forced. Once the feature has been enabled, the database engine will automatically force any recommended query execution plan where the number of errors in the previous plan is higher than the recommended plan, the estimated CPU gain is greater than 10 seconds, or a plan was forced and continues to be better than the previous one. The plan will revert back to the last known good plan after 15 executions of the query.

When plan forcing occurs automatically, the database engine will apply the last known good plan and will also continue to monitor query execution plan performance. If the forced plan does not perform better than the previous plan, it will be subsequently unforced and force a new plan to be compiled. If the forced plan continues to outperform the previously bad plan, it will remain forced until such time as a recompile occurs.

You can enable automatic plan correction via a T-SQL query, as shown below. The Query Store must be enabled and must be in Read-Write mode for the command to succeed. If either of those two criteria are not met, the ALTER statement will fail.

1. SQL
2. ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

You can examine the automatic tuning recommendations through a dynamic management view (DMV), sys.dm_db_tuning_recommendations, which is available in SQL Server 2017 or higher and is also available in Azure SQL Database solutions. This DMV provides information such as reasons as to why the recommendation was provided, the type of recommendation, the state of the recommendation, as well as others. To confirm that automatic tuning is enabled for a database, there is a DMV sys.database_automatic_tuning_options that can be queried.

https://azure.microsoft.com/en-us/blog/automatic-tuning-introduces-automatic-plan-correction-and-t-sql-management/

Ownership Chains

A concept called chaining applies to permissions, which allows users to inherit permissions from other objects. The most common example of chaining is a function or stored procedure that accesses a table during its execution. If the procedure has the same owner as the table, the stored procedure is able to be executed and access the table, even though the user does not have rights to access the table directly. This access is available because the user inherits the rights to access the table from the stored procedure, but only for the duration of the execution of the stored procedure, and only within the context of the stored procedures execution.

https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-ownership-chains-and-context-switching?view=sql-server-ver15

Columnstore archival provides the highest level of compression in SQL Server.

Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. To perform archival compression, SQL Server runs the Microsoft XPRESS compression algorithm on the data. Add or remove archival compression by using the following data compression types:

• Use COLUMNSTORE_ARCHIVE data compression to compress columnstore data with archival compression.

• Use COLUMNSTORE data compression to decompress archival compression.

The resulting data continue to be compressed with columnstore compression.

To add archival compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the REBUILD option and DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver15

Azure SQL Managed Instance is the only PaaS service that supports instance-scoped features like CLR and Service Broker. The first answer choice is also correct, but it doesn't provide the benefits that PaaS can offer.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-intro/3-deployment-options

Azure SQL Database - Hyperscale

The Hyperscale tier within Azure SQL Database (not available in Azure SQL Managed Instance) has a unique architecture for Azure SQL. The Azure SQL team rearchitected Hyperscale for the cloud, and this architecture includes a multilayer caching system that can help with both speed and scale. Scaling and other operations no longer become related to the size of data and can be completed in constant time (a matter of minutes). The use of remote storage also allows for snapshot backups.

One consideration during the deployment phase is that after you move a database to the Hyperscale tier, you can't "go back" to the General Purpose or Business Critical tier.

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale-frequently-asked-questions-faq

The Azure ecosystem offers several different methods in which to provision a new SQL Server instance on an Azure virtual machine. Each method provides different capabilities, such as the ability to have a repeatable process (via scripting languages like PowerShell) or a method to accomplish the goal without needing an understanding of programming constructs, via the Azure portal.

Deploying using Azure Resource Manager templates

Azure Resource Manager Templates have the benefit of being able to deploy a full set of resources in one single declarative template. This includes the ability to build dependencies into the templates, as well as using parameters to change specific values at deployment time. Once you have a template, there are several ways with which you can deploy it including an Azure DevOps pipeline, or through the custom deployments blade in the Azure portal. You can use the Azure portal to export a Resource Manager template to JSON for future deployment.

The image above is an example of an Azure Resource Manager template that can be used to deploy a group of Azure resources repeatedly. Templates can be deployed directly from the Azure portal, or via scripting languages that reference the templates like PowerShell, the Azure CLI, or through DevOps workflows like Azure DevOps.

1. $resourceGroupName = Read-Host -Prompt "Enter the Resource Group name"
2. $location = Read-Host -Prompt "Enter the location (i.e. centralus)"
3. New-AzResourceGroup -Name $resourceGroupName -Location $location
4. New-AzResourceGroupDeployment -ResourceGroupName $resourceGroupName `
5. -TemplateUri https://raw.githubusercontent.com/Azure/azure-quickstart-templates/master/101-storage-account-create/azuredeploy.json
6. az deployment group create --resource-group myresourcegroup `
7. --TemplateUri https://raw.githubusercontent.com/Azure/azure-quickstart-templates/master/101-sql-logical-server/azuredeploy.json

The code above highlights the PowerShell and Azure CLI code to deploy an Azure Resource Group and prompts the user for the resource group name and the target Azure region.

https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/deploy-cli

Both Azure SQL Database for SQL Server and Azure Database for PostgreSQL support configuring the server that is hosting the database to use Azure Active Directory Authentication.

This login allows the admin access to all of the databases in the server. It is a best practice to make this account an Azure Active Directory group, so access is not dependent on a single login. In the above image, that group has been called DBA team.

The Azure Active Directory Admin account grants special permissions and allows the account or group that holds that permission to have sysadmin like access to the server and all of the databases within the server. The admin account is only set using Azure Resource Manager and not at the database level. In order to change the account or group, you have to use the Azure portal, PowerShell, or CLI.

https://docs.microsoft.com/en-us/azure/active-directory/authentication/overview-authentication

Both Azure SQL Database for SQL Server and Azure Database for PostgreSQL support configuring the server that is hosting the database to use Azure Active Directory Authentication.

This login allows the admin access to all of the databases in the server. It is a best practice to make this account an Azure Active Directory group, so access is not dependent on a single login. In the above image, that group has been called DBA team.

The Azure Active Directory Admin account grants special permissions and allows the account or group that holds that permission to have sysadmin like access to the server and all of the databases within the server. The admin account is only set using Azure Resource Manager and not at the database level. In order to change the account or group, you have to use the Azure portal, PowerShell, or CLI.

https://docs.microsoft.com/en-us/azure/active-directory/authentication/overview-authentication

Connecting to Azure Database for MySQL using Azure AD

The following high-level diagram summarizes the workflow of using Azure AD authentication with Azure Database for MySQL:

https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-configure-sign-in-azure-ad-authentication