Use Microsoft Purview to provide at-scale access to performance data in Azure SQL and SQL Server

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

In prior articles we have covered how you can use Microsoft Purview as an inexpensive solution to grant read access to SQL’s system metadata information. You achieve this if you use Microsoft Purview as a dedicated solution for DevOps policies (see details here).

 

In a nutshell, Microsoft Purview DevOps policies are a central, cloud-based experience used to provision access at-scale to IT/DevOps personnel, so that that they can monitor the health and performance of SQL systems, but without giving them access to your data's crown jewels. DevOps policies are already available for SQL Server 2022, Azure SQL Database and soon for Azure SQL Managed Instance.

 

One of the most attractive aspects of Microsoft Purview DevOps policies is their scalability. First of all, you can grant and review all access to SQL data sources from a central place: The Purview portal, or via the API if you prefer programmatic access. Second, policies can be applied to entire subscriptions or resource groups, and by that get enforced in all underlying data sources – at scale. In other words, if you have 100 SQL servers with each containing 10 databases within one Resource Group “Project Green”, assigning a role to this Resource Group will allow the assigned AAD users to access any of the 1000 databases without requiring additional SQL permissions or Logins to be created locally. More information here.

 

Microsoft Purview DevOps policies support two roles: SQL Performance Monitor and SQL Security Auditor. When you configure a DevOps policy in Microsoft Purview by specifying one of these two roles, the subjects specified in the policy (a list of Azure AD principals), will be granted read access to an extensive number of system views and other commands.

This article provides examples of common system views and -functions (DMVs and DMFs) and other objects that are accessible for the SQL Performance Monitor role.

 

Background on SQL metadata

 

If you don’t have much experience with SQL Server, you may wonder, what these two roles, SQL Performance Monitor and SQL Security Auditor accomplish and what exactly DMVs or DMFs are.
So let’s start with some basics.

– If you already know how system metadata in SQL is organized, feel free to skip this section.

 

There are two types of system information SQL engine tracks:

• Object metadata that is persisted to the dababase, which keeps track of objects in a database (e.g., tables via sys.tables or views via sys.views), as well as classic SQL Users and permissions granted within a database. This type of information is stored in catalog views.
• Dynamic metadata, which is more volatile. (Some of it survives restarts of the service, other is completely memory-based and gets cleaned-up on restart. I.e., Wait statistics via sys.dm_os_wait_stats) This dynamic metadata is accessible via so called Dynamic Management Views (DMVs) or Dynamic Management Functions (DMFs).
  - Note that I did not say “it’s stored within”, because this data is often only generated at query-time and only then rendered into a rowset for retrieval.

 

As a general rule, all the system views starting with dm_ belong to the dynamic category – and all others are catalog views. There are some exceptions, hybrid scenarios, but generally this is sufficient as a differentiator.

 

SQL has a native hierarchical permission system that differentiates between static and dynamic metadata. (I.e., permissions with the term “DEFINITION” are required to view static catalog views vs permissions with the term “SATE” are for dynamic metadata)

The role definitions in Microsoft Purview DevOps policies cover access to both the static and dynamic metadata.
“Actions”, which are part of the Purview policy definition, serve as a “flat” permission system which the SQL Server engine uses in parallel to the native (hierarchical) SQL permission system when evaluating effective permissions.

Actions vs permissions
Here you can read more about the two permission systems and what differentiates “actions” from permissions: Revamped SQL Permission system for Principle of Least Privilege and external policies – internals

The DevOps policies that Microsoft Purview offers hide the complexity behind the scenes. Nevertheless, to make it easier to get accustomed to the “new world” of external permissions, there is typically a 1:1 relationship between the “external” actions and the SQL native permissions.

 

Alright, I hope that was not too confusing. Seeing it in action will make it easier to comprehend, I promise:

 

What does SQL Performance Monitor give access to?

 

The SQL Performance Monitor role is meant for users that require access to a SQL server or database for performance investigation purposes.

Background to performance monitoring tools
In SQL Server performance investigation is done with a variety of tools. Some are external to the SQL engine, like Windows Performance Monitor or Event Tracing for Windows, and some are internal to SQL such as the discussed Dynamic Management Views (and -Functions) and Extended Events.

The SQL Performance Monitor role allows access to 3 different kinds of objects in SQL:

 

1) System Views:

All DMVs and DMFs with the exception of those which contain security-sensitive information. (Those are instead covered by the SQL Security Auditor role.)

 

These are the actions (similar to permissions) that are used for this:

 

1. /Sqlservers/SystemViewsAndFunctions/ServerPerformanceState/Rows/Select
2. /Sqlservers/Databases/SystemViewsAndFunctions/DatabasePerformanceState/Rows/Select
3. /Sqlservers/SystemViewsAndFunctions/ServerGeneralMetadata/Rows/Select
4. /Sqlservers/Databases/SystemViewsAndFunctions/DatabaseGeneralMetadata/Rows/Select

 

For those familiar with the native SQL permission system: These actions map to the SQL permissions VIEW DATABASE PERFORMANCE STATE, VIEW SERVER PERFORMANCE STATE, VIEW PERFORMANCE DEFINITION and VIEW ANY PERFORMANCE DEFINITION respectively.

 

2) Extended Events:

In addition to these SELECT-actions for system views, the Performance Monitor role also allows
Extended Event session management (create and drop sessions, change session definition, start and stop sessions).
Since Extended Events are available at both server-scope (SQL Server and for Azure SQL Managed Instance) and database-scope (Azure SQL Database and Managed Instance), this role includes access to both scopes.

 

Included server-scope XEvent actions:

 

1. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Create
2. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Options/Alter
3. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Events/Add
4. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Events/Drop
5. Microsoft.Sql/Sqlservers/ExtendedEventSessions/State/Enable
6. Microsoft.Sql/Sqlservers/ExtendedEventSessions/State/Disable
7. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Drop
8. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Target/Add
9. Microsoft.Sql/Sqlservers/ExtendedEventSessions/Target/Drop

 

Included database-scope XEvent actions:

10. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Create
11. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Options/Alter
12. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Events/Add
13. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Events/Drop
14. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/State/Enable
15. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/State/Disable
16. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Drop
17. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Target/Add
18. Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Target/Drop

 

3) DBCC Commands

finally, this role also covers access to several DBCC-Commands that are used for system analysis:

 

19. Microsoft.Sql/Sqlservers/Databases/DBCCs/ViewDatabasePerformanceState/Execute
20. Microsoft.Sql/Sqlservers/DBCCs/ViewServerPerformanceState/Execute

 

Currently the following DBCC commands are covered by these 2 actions:

 

• DBCC SQLPERF(NETSTATS)
• DBCC SQLPERF(LOGSPACE)
• DBCC INPUTBUFFER 
• DBCC OPENTRAN

 

Practical examples

 

Let’s look at some specific examples of what a user assigned to the SQL Performance Monitor role can do.

 

Hint
If you are not familiar with Performance analysis using DMVs, please have a look here for a start:

Monitor and Tune for Performance - SQL Server | Microsoft Learn

Monitor performance using DMVs - Azure SQL Database | Microsoft Learn

 

In essence, the Performance Monitor role should suffice for all typical queries against DMVs for performance troubleshooting.

 

For example, with this policy assigned one can…

 

…query the system parameters to understand your system:

 

SELECT * FROM sys.configurations SELECT * FROM sys.dm_os_sys_info

 

objects used for these queries:

• sys.configurations (Transact-SQL)
• sys.dm_os_sys_info (Transact-SQL)

 

… do Wait-Stats analysis to detect bottlenecks:

 

SELECT TOP 50 -- adjust to your needs wait_type , wait_time_ms/ 1000.0 AS wait_time_s , (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_time_s , signal_wait_time_ms / 1000.0 AS signal_wait_time_s , waiting_tasks_count FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN (N'SLEEP_TASK') -- here you should filter out irrelevant wait-types ORDER BY wait_time_s DESC

 

objects used for this query:

• sys.dm_os_wait_stats (Transact-SQL)

                                                                                            

… see which queries are currently running on your system (including those which are blocked or rolling back):

 

SELECT dm_exec_sql_text.text AS QueryText , dm_exec_requests.* FROM sys.dm_exec_requests OUTER APPLY sys.dm_exec_sql_text(plan_handle) AS dm_exec_sql_text WHERE status IN ('running', 'suspended', 'rollback')

 

objects used for this query:

• sys.dm_exec_requests (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)

 

… analyze current blocking:

 

SELECT dm_tran_locks.resource_type , DB_NAME(dm_tran_locks.resource_database_id) AS [database] , dm_tran_locks.resource_associated_entity_id , dm_tran_locks.request_mode , dm_tran_locks.request_session_id , dm_os_waiting_tasks.blocking_session_id , dm_os_waiting_tasks.wait_duration_ms / 1000 AS [wait time in seconds] , dm_exec_sql_text.text AS blocked_statement FROM sys.dm_tran_locks AS dm_tran_locks INNER JOIN sys.dm_os_waiting_tasks AS dm_os_waiting_tasks ON dm_tran_locks.lock_owner_address = dm_os_waiting_tasks.resource_address INNER JOIN sys.dm_exec_requests AS dm_exec_requests ON dm_tran_locks.request_session_id = dm_exec_requests.session_id CROSS APPLY sys.dm_exec_sql_text(dm_exec_requests.[sql_handle]) AS dm_exec_sql_text

 

objects used for this query:

• sys.dm_tran_locks (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)

 

Hint
A more complete analysis can be done with a script that Bob Ward provides in his Azure workshop, here:

sqlworkshops-azuresqlworkshop/find_blocking.sql at master · microsoft/sqlworkshops-azuresqlworkshop (github.com)

 

 

… analyze memory usage:

 

SELECT TOP(25) [type] AS [Memory Clerk Type] , pages_kb / 1024.0 AS pages_mb , name , memory_node_id FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

 

objects used for this query:

• sys.dm_os_memory_clerks (Transact-SQL)

 

… analyze file-usage and -performance (SQL Server and Azure SQL Managed Instance):

 

SELECT DB_NAME(master_files.database_id) AS DatabaseName , master_files.name AS FileName , master_files.type_desc , master_files.size , master_files.growth , dm_io_virtual_file_stats.* FROM sys.master_files AS master_files INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS dm_io_virtual_file_stats ON master_files.database_id = dm_io_virtual_file_stats.database_id AND master_files.file_id = dm_io_virtual_file_stats.file_id

 

objects used for this query:

• sys.master_files (Transact-SQL)
• sys.dm_io_virtual_file_stats (Transact-SQL)

 

… analyze index-usage and -fragmentation:

 

-- Index-usage statistics SELECT DB_NAME(database_id) AS DatabaseName , * FROM sys.dm_db_index_usage_stats

 

 

 

-- Index fragmentation -- this query needs to be run per database: SELECT DB_NAME() AS DatabaseName --, OBJECT_NAME(object_id) AS ObjectName --, indexes.name AS IndexName --, indexes.type_desc , dm_db_index_physical_stats.* FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS dm_db_index_physical_stats INNER JOIN sys.indexes AS indexes ON dm_db_index_physical_stats.object_id = indexes.object_id AND dm_db_index_physical_stats.index_id = indexes.index_id

 

objects used for these queries:

• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_index_physical_stats (Transact-SQL)
• sys.indexes (Transact-SQL)

 

… use the Query Store to analyze historical query runtime information:

 

• Although the Query Store node in Object explorer currently is not visible for Users with only external policy-based permissions, the query store DMVs are accessible just as any other DMV.

Here is a query that combines information about query plans, the query details and query-texts for a start:

 

SELECT * FROM sys.query_store_plan AS query_store_plan INNER JOIN sys.query_store_query AS query_store_query ON query_store_plan.query_id = query_store_query.query_id INNER JOIN sys.query_store_query_text AS query_store_query_text ON query_store_query.query_text_id = query_store_query_text.query_text_id;

 

objects used for this query:

• sys.query_store_plan (Transact-SQL)
• sys.query_store_query_text (Transact-SQL)
• sys.query_store_query (Transact-SQL)

 

For an overview of the Query Store system views that can be used for analysis and how to go about it, have a look here:

• Query Store catalog views (Transact-SQL) - SQL Server | Microsoft Learn
• Monitor performance by using the Query Store - SQL Server | Microsoft Learn

 

All the example queries can be downloaded at the end of this article.

 

 Currently approximately 700 DMVs and Catalog Views are available for the SQL Performance Monitor role.

 

Extended Event sessions are very task-specific and besides the built-in system sessions differ very much between systems. Therefore, I am not including an example here.

If you are on SQL Server 2022 on Arc what you can test immediately once you have been granted access to via the Purview role SQL Performance monitor, is using the built-in XEvent sessions “Standard” and “TSQL” which show up in the Object Explorer on bottom as you can see in the following screenshot.

 

 

If you are not familiar with Extended Events, please start here:

• XEvents overview - SQL Server, Azure SQL Database, and Azure SQL Managed Instance - SQL Server | Microsoft Learn
• Extended events - Azure SQL Database | Microsoft Learn
• Quickstart: Extended Events in SQL Server - SQL Server | Microsoft Learn

 

Current Limitations of the SQL Performance Monitor

 

In all fairness I should point out the current gaps, that may or may not matter to you:

 

• Currently we only support tables and views with Purview policies. That means that no stored procedures can be executed based on external policies. Be it system stored procedures or user-created procedures.
• Access to msdb-specific system tables and -views is not supported yet.
• In SQL Server and SQL Managed Instance that is where backup information and of course SQL Agent Job-information is stored and applies. Azure SQL Database does not have the msdb database.
• XEvents: since currently Purview policies do not include actions for Credentials (which store information to access files), the use of the file-target is not possible in Azure SQL Database (and MI) via policies alone.
  All memory-targets (ring buffer, event counter, event pairing, histogram) are usable though.

 

Third-part tooling-support

 

Most vendor-tools require access to more than just DMVs. Some unfortunately still use SQL Profiler (the deprecated predecessor of XEvents – which is why it is not supported by Puirview actions at all), some even require sysadmin-permissions.
As soon as more actions are added, most vendors should be able to support Purview based policies to support access at scale for your whole SQL Environment, without the micro-management of Logins.

 

Examples of script-tools fully runnable with access provided solely via RBAC roles

 

Performance-Analysis does not always require a sophisticated software. In fact, for most of my consultant-work I have relied on my own scripts to extract the necessary data as either there was no tool available, or the chosen tool was not providing the information that I required.

 

Here are some scripts that I have tested using Purview-backed access via the SQL Performance Monitor role exclusively:

 

MVP Glenn Berry provides a large set of queries that cover SQL Server internals from A to Z at https://glennsqlperformance.com/resources/

 

Note
The Azure SQL Database Diagnostic Information Queries run without any errors against a Purview enabled Azure SQL Database.

The SQL Server 2022 Diagnostic Information Queries do throw a couple of errors which are attributed to accessing the msdb database, the SQL Server errorlog and the registry via extended stored procedures. Those are the only 3 types of queries that fail with a permission error. All other queries return proper results.

 

 

Also, the Microsoft SQL Tiger team (Toolbox repository for Tiger team (github.com)) has published a couple of scripts which you can run right away from a connection that is solely granted via RBAC:

 

• tigertoolbox/Waits-and-Latches · microsoft/tigertoolbox (github.com)
• tigertoolbox/Index-Information · microsoft/tigertoolbox (github.com)
• tigertoolbox/Query-Performance · microsoft/tigertoolbox (github.com)

 

 

Obviously, all the listed gaps are on our radar. But do let us know if anything else appears to be missing.

 

Hopefully this article is useful for those testing access to SQL via Purview policies.

 

Happy database-monitoring

 

Andreas and Vlad

 

 

Thank you for reviewing:

Dimitri Furman, Principal Program Manager