Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different.
I was doing some normal activities on one of my Azure SQL Databases, I went to make a cup of tea and returned to the following message:
The statement has been terminated. Msg 40544, Level 17, State 12, Line 15 The database ‘TestDB’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.
Imagine this scenario, you are a database professional at your place of business and you look after a mountain of SQL Server databases and it is a battle just to keep the lights on. If this sounds like you then you need all the help you can get. You can find this help from https://www.BrentOzar.com/first-aid/ where Brent, his team and community members (found here https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/graphs/contributors) have developed something known as the First Responder Kit and let me tell you, it will make your life “easier”. (In the long term)
T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests hosted by Brent Ozar.
I did not want to miss out on this one because I want to mention a connect item that I logged last year which is still active.
This is an enhancement request for SQL Server where I got the idea from SQL database (Azure).
Wait Stats is my “go-to” thing, when you want to dig into performance issues everyone knows you will probably end up using sys.dm_os_wait_stats. You cannot use this in the Azure world, you have to use a DMV that is scoped to the database level. I think this would be a nice idea to have with the “earth” based SQL Servers – the ability to return information about all the waits encountered by threads that executed at the database level.
The connect item can be found at this link: https://connect.microsoft.com/SQLServer/feedback/details/2647332/sys-dm-db-wait-stats-dmv?tduid=(262281c4c73a682498780643b77e80d1)(256380)(2459594)(TnL5HPStwNw-KjxCoz0pGWobbq7q1MQTIw)()
An example of using the DMV that I would like:
WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_db_wait_stats ) SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 99
It currently has 8 votes, would be nice to get some more.
All About Data 