The Microsoft Azure SQL Database Data Collector collects diagnostic information. This information is useful in troubleshooting a broad class of issues that involve databases that are hosted on Microsoft Azure SQL Database.
There are different prerequisites to run diagnostic packages, depending on the operating system of the computer from which the diagnostic package is executed. The diagnostic tool will automatically check your computer for these prerequisites and then start if the prerequisites are installed. If the prerequisites are not available on the computer, you are prompted to install them.
This collector requires Windows PowerShell 2.0. If you try to run the collector from a computer on which PowerShell is not installed, or if the version of PowerShell that is installed is earlier than PowerShell 2.0, you are prompted to install PowerShell 2.0.
This manifest can be executed on a computer that is running Windows Vista, Windows Server 2008, Windows 7, Windows Server 2008 R2, Windows 8, Windows Server 2012, Windows 8.1 or Windows Server 2012 R2. This manifest does not support Windows XP or Windows Server 2003. If you try to execute the manifest on Windows XP or Windows Server 2003, you will be offered the option of creating a "portable" version of the diagnostic tool that you can execute on a computer that is running a supported operating system. If you try to execute the portable diagnostic on Windows XP or Windows Server 2003, it will fail.
Microsoft SQL Server security requirements
The Microsoft Azure SQL Database Data Collector prompts for the logical server name, database name, and SQL Authentication credentials to use. As soon as it is connected, this collector collects an initial snapshot of diagnostics data from the specified user database. Then, the collector goes to collect live data from the user database that is followed by an ending snapshot of diagnostic data. Finally, it connects to the logical master database to collect a snapshot of the Event Table Dynamic Management Views (DMV).
Database connections are made by using SQL authentication. The specified logon must have read-only access to the master database and VIEW DATABASE STATE permission in the user database for the diagnostic collection tasks to succeed.
Information that is collected
Performance statistics script
The performance statistics script runs every 10 seconds and collects a complete set of information about any active queries. For example, this script might collect the following result sets:
- The "Requests" result set. This result set contains details about currently executing requests such as host name, application name, wait information, transaction details, last query start/end time, blocking relationships, CPU, I/O, and duration. These are used by both the current query and the session.
- The "Notable Active Queries" result set. This result set shows active query input buffers and historical execution statistics for the corresponding query plans.
- The "Head Blocker Summary" result set. This result set captures any blocking.
Performance statistics script snapshot
This script captures a one-time snapshot of SQL performance-related information from the user database. <server_name
>_SQL_Azure_Perf_Stats_Snapshot_Startup.out is executed when the collector starts up, <server_name
>SQL_Azure_Perf_Stats_Snapshot_Shutdown.out is executed when the collector shuts down. The following are the result sets that this snapshot collects:
- The "Stats Date" result set. This result set contains the details about all indexes and statistics that are user-defined and auto statistics in the user database. This includes useful information such as the number of row modifications and when the statistics were last updated.
- The "Missing indexes” result set. This result set has the output from missing index DMVs. These indexes could potentially result in improved performance for your workload.
- The "Top 10 Queries" result set. This result set displays the historical resource-intensive queries that are running against the user database by CPU, reads, and duration.
Event table DMVs snapshot
This script captures a one-time snapshot of event table DMVs from the master database that is associated with your logical server. Specifically, the following are the result sets that this snapshot collects:
- The "Database Connection Stats" result set. This result set captures output from the sys.database_connection_stats DMV. This is a high-level summary of successful versus failed connections over time.
- The "Event Log" result set. This result set captures output from sys.event_log DMV. This is a detail-level view of successful versus failed connections. Additionally, this DMV captures any deadlock events for the user database together with the deadlock graphs.