PowerShell to Change the Service Tier or Performance Level

PowerShell can be utilized to configure the service tier or performance Level for a SQL database. Listing 10-1 changes the service tier of an existing database to Standard Edition, S0 performance level.

Listing 10-1. PowerShell Script to Change the Service Tier and Performance Level for an Azure SQL Database

#login into the Azure Account
Add-AzureRmAccount

#Select the Subscription
$subscriptions = Get-AzureRmSubscription
$SubscriptionId = $subscriptions[0].SubscriptionId
Select-AzureRmSubscription -SubscriptionId $SubscriptionId

#Select the Resource Group
$ResourceGroupName = Get-AzureRmResourceGroup | Where-Object {$_.ResourceGroupName -notlike "Default*"}
$ResourceGroup= $ResourceGroupName[1].ResourceGroupName

#Select the Azure SQL Server and Database Name
$ServerName = (Get-AzureRmSqlServer -ResourceGroupName $ResourceGroup)[1].ServerName
$DatabaseName = (Get-AzureRmSqlDatabase -ServerName $ServerName -ResourceGroupName $ResourceGroup | Where-Object {$_.DatabaseName -ne "Master"}).DatabaseName

#select the new Service Tier and Performance Level
$NewEdition = "Standard"              
$NewPerformanceLevel = "S0"              

# Change the Service Tier/Performance Level
$ScaleRequest = Set-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroup -Edition $NewEdition -RequestedServiceObjectiveName $NewPerformanceLevel

Using Azure Portal to Change the Service Tier or Performance Level

Azure Management Portal provides a single-click mechanism to change the service tiers or performance levels for your database. Figures 10-1 and 10-2 show how the service tier and the performance level of an Azure SQL Database can be changed using the Azure Management Portal.

Figure 10-1. Changing database service tiers using the Azure Portal

Figure 10-2. Selecting the new performance level

In-Memory Optimizations

In-Memory Optimization features present in Azure SQL Database are very similar to the ones in Microsoft SQL Server 2014 (and the upcoming SQL 2016). In-memory optimization (in-memory tables and natively compiled SPs) and ColumnStore indexes can be used to increase the performance of both OLTP and analytics workloads. Moreover, the combination of the two can be used to provides near real-time analytics.

SQL Database Index Advisor

Azure SQL Database Index Advisor provides recommendations for indexes to be created (only non-clustered indexes) or dropped (only duplicate indexes at the time of writing) on the SQL Database. Index Advisor can be configured to automatically apply the index recommendations to the database. If the recommendations do not help performance, they can be easily rolled back. Automating index recommendations requires that query store be enabled on the Azure SQL Database.

Index recommendations can be accessed on the Azure Portal, as shown in Figure 10-3.

Figure 10-3. Access the Index Advisor on Azure Portal

To get index recommendations, a database needs a substantial amount of consistent usage and activity . Index Advisor provides better recommendations when there is consistent activity rather than inconsistent one-time burst activities. As shown in Figure 10-4, advisor settings can be configured to automatically create or drop the indexes suggested. Note that this would be an online operation and may impact queries running against the database.

Figure 10-4. Configuring Index Advisor settings

If there are index recommendations available, they will be displayed on the page. If there are none, the advisor provides a reason as to why that’s so. For example, in Figure 10-4, the reason for not having any recommendations is the absence of activity on the database.

SQL Database Query Performance Insights

Query Performance Insights provide a very simplified way to monitor and troubleshoot performance of an Azure SQL Database. Query Performance Insights requires query store to function and can provide detailed information about query performance and DTU consumption.

Query Performance Insights can be accessed from the Settings page of the Azure SQL Database, as illustrated in Figure 10-5.

Figure 10-5. Query Performance Insights

Query Performance Insights can be used to determine the overall DTU utilization and the utilization per query. Execution details of each query can be accessed from the portal. For example, as illustrated in Figure 10-6, you can see the query text and the corresponding CPU and DTU usage by the query. Query Performance Insights settings can be tweaked to display statistics for different time periods and for different number of queries, as illustrated in Figure 10-7.

Figure 10-6. Detailed Query Performance details

Figure 10-7. Query Performance Insights settings

Query Performance Insights are based on query store, and it’s possible that query store can run out of storage space. When query store runs out of storage space, it will go into a read-only mode, thereby not allowing storage of any more query performance data. It is important to set up correct retention and clean-up policies for query store. Clean-up can be set to AUTO (SQL will run cleanup whenever the max size limit is reached) or a time-based retention.

ALTER DATABASE [YourDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

ALTER DATABASE [YourDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

Query Performance Insights provides a great method to figure out problematic queries and optimize them. For example, you can get the top CPU consuming queries and tune them before they have any major performance impacts on the database.

Using Azure Portal

Azure Portal provides a very convenient way to monitor resource utilization for SQL Database. Counters like CPU percentage, DTU percentage, and so on, can be monitored from the Azure Portal. This information can be utilized to ensure optimal health of the database. The monitoring tab on the Azure Portal (under the database details) can be utilized to monitor the resource utilization for a database (see Figure 10-8). Also, the metrics and the time period settings can be configured using the Management Portal, as per the requirements (see Figure 10-9).

Figure 10-8. Monitoring SQL Database resource utilization

Figure 10-9. Configuring monitoring metrics

The monitoring graphs can be edited to add multiple other counters, as shown in Figure 10-9.

Once the metrics have been configured, the resource utilization can be monitored on the Azure Portal, as illustrated in Figure 10-10.

Figure 10-10. Monitoring metrics on theportal

Azure Portal also allows users to configure alerts against any of the available metrics (see Figure 10-11). For example, you can set up an e-mail based alert to indicate if the database crosses 80% of the maximum allowed size for the performance level, or if the DTU utilization percentage crosses 80% of the DTUs for that service tier. This information can be used to determine if the database performance level needs to be changed.

Figure 10-11. Configure alerts for resource utilization metrics

The alerts can be configured with the desired threshold values and can be configured to send e-mails to admins or other users. These settings can be changed using the Add an Alert Rule page (see Figure 10-12) .

Figure 10-12. Configuring alert properties

Using DMV and Catalog Views

DMVs and catalog views exposed for both the database and logical servers can be used to report and/or monitor the performance, resource utilization, database object details, query execution information, and so on, of a SQL database and the logical server.

Running queries against the catalog view and DMVs requires View Server State and View Database State permissions. Some of the most commonly used DMVs are mentioned next.

Using Extended Events

Extended Events or (XEvents) are not new. They have been available with Microsoft SQL Server since the SQL Server 2008 days. There has been a lot of optimization and improvements done to this feature over the last several years. The amount of information available through XEvents is enormous, which can be determined by the fact that the upcoming release of SQL Server, SQL Server 2016, has almost 1,200 events exposed.

Extended Events available on Azure SQL Database is only a subset of the feature available with SQL Server. These extended events are scoped to a single Azure SQL database, meaning that an extended event running against one Azure SQL database cannot be used to monitor events against another database on the same logical server.

Listing 10-2 provides a sample script that can be used to determine the events/actions available with Extended Events on Azure SQL Database.

Listing 10-2. T-SQL Script to Determine the Events or Actions Available with Azure SQL Database

SELECT
        o.object_type,
        p.name         AS [package_name],
        o.name         AS [db_object_name],
        o.description  AS [db_obj_description]
    FROM
                   sys.dm_xe_objects  AS o
        INNER JOIN sys.dm_xe_packages AS p  ON p.guid = o.package_guid
    WHERE
        o.object_type in
            (
            'event','action'
            )
    ORDER BY
        o.object_type,
        p.name,
        o.name;                                                                       

The easiest way to configure Extended Events for Azure SQL Database is to use SQL Server Management Studio, as illustrated in Figure 10-13. In SSMs, Extended Events can be accessed by expanding the database node.

Figure 10-13. Accessing Extended Events

A new session can be configured by right-clicking on the session node and choosing either New Session Wizard or New Session. Either of the options are fairly straightforward to use.

Following is a sample T-SQL script to create an XEvent to monitor blocking and deadlock:

CREATE EVENT SESSION [Sample_XEvents] ON DATABASE

ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.database_xml:deadlock_report(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.lock_deadlock(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text))

ADD TARGET package0.event_file(
                 SET filename=N'https://YourStorageAccountName.blob.core.windows.net/ContainerName/OutputFile.xel'),
ADD TARGET package0.ring_buffer

WITH (STARTUP_STATE=ON)
                    GO                                                    

Similar sessions can be created to monitor other events on the SQL Database.

Control permission is required to create an Extended Events. This permission is available by default to the DBO user.

Information about currently running XEvent sessions and other information regarding the events, actions, targets, etc. exposed through XEvents can be derived from the several DMVs and catalog views exposed by Azure SQL Database. Following are some of the most commonly used DMVs and catalog views :

• sys.dm_xe_database_session_events. Exposes information about the events configured for the currently active sessions.

• sys.dm_xe_database_sessions. Exposes information about the currently active Extended Event sessions.

• sys.database_event_sessions. Exposes information about all the XEvents sessions configured on the SQL database.

• sys.dm_xe_objects. Provides information about the events, actions, targets, etc. exposed by XEvents on Azure SQL Database. This DMV is similar to the one available with Microsoft SQL Server.

Extended Events can be useful in determining performance issues or other issues with the Azure SQL Database. But take care not to overload the database with too many sessions or events being captured. Doing so can cause over-committing memory and ultimately lead to database performance issues.