Data About Execution Plans

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
If you look at the Properties for the first operator of a graphical execution plan, you get all sorts of great information. I've talked about the data available there and how important it is in this older post. Checking out the properties of a plan you're working on is a fundamental part of tuning that plan. What happens when you don't know which plan you should be working on? What do you do, for example, if you want to see all the plans that are currently using ARITHABORT=FALSE or some other plan affecting setting? The "easy" answer to this question is to run an XQuery against the XML of the query plan itself. You can identify these properties and retrieve the appropriate values from within the plan. However, XQuery consumes quite a…
Read More

Query Store and What Happened Earlier On Your Server

SQL Server 2016, TSQL
Here's a great question I received: We had a problem at 9:02 AM this morning, but we're not sure what happened. Can Query Store tell us? My first blush response is, no. Not really. Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can't tell you what happened with an individual call at 9:02 AM... Well, not entirely true. The aggregations that Query Store keeps are actually broken up into intervals that you can control. The default interval is 60 minutes. This means that the information stored in the DMV covers sets of intervals. This means that if, at 9:02AM, you had a query, or queries, that ran considerably longer than normal, you may be able to take a look…
Read More

Customizing Your Azure Portal

Azure
Not all of us are going to use Azure in the same way. That's just common sense. Therefore, not all of us are going to want it to look the same. Here's a default view of the portal for a login I created within my own domain: You can contrast that with this login to my portal that I use for most of my testing and training (this isn't my company's production or development portal, which looks still more different): Clicking on either of these will open them so you can look at details. Not only are the color schemes different, but you'll note that the selection lists on the left are different as are the grids on the dashboard. All this can be customized for each login, and, within…
Read More

Monitoring for Timeouts

SQL Server 2012, SQL Server 2014, TSQL
The question came up at SQL Rally, "Can you use Extended Events to monitor for query timeouts?" My immediate response was yes... and then I stood there trying to think of exactly how I'd do it. Nothing came quickly to mind. So, I promised to track down the answer and post it to the blog. My first thought is to use the Causality Tracking feature to find all the places where you have a sql_batch_starting without a sql_batch_completed (or the same thing with rpc calls). And you know what, that would work. But, before I got too deep in trying to write the query that would find all the mismatched attach_activity_id values that have a sequence of 1, but not one of 2, I did some additional reading. Seems there's…
Read More

More Azure Goodies

Azure
Microsoft keeps sneaking little things under the door for Windows Azure SQL Database. This time it's a couple of new views, a system view and a Dynamic Management View (DMV); sys.resource_stats and sys.dm_operation_stats. But, I also learned another fun fact, not all this stuff rolls out at the same speed. For example, if I run sys.resource_stats on a database on a server located in the North Central US data center, the output looks like this: But, if I run the same query against the same view with a database in a data center in East Asia (I experiment with where I put things), it looks like this (click on it to expand it, probably want to do that into a second tab or window so you can refer to it…
Read More

Monitoring Structure Changes

Azure
Most everyone I know works with environments that are carefully controlled and structured. All changes go through rigorous testing and full documentation. Absolutely nothing happens in a production environment that hasn't been thought through, discussed, planned for and written down. But, there are a few, a very few, who work in a slightly different kind of environment that they refer to as "the real world." In this "real world" changes to a production environment can happen without approval, planning or testing. Scary, right? There's good news for these poor benighted souls. Red Gate is testing out a new piece of software called SQL Lighthouse. It's meant to monitor your systems for changes so that you know what has happened and when in case you don't have a good source control…
Read More

How to Tell Your Windows Azure SQL Database Moved

Azure
The very concept of the Windows Azure SQL Database (WASD) is predicated on the up-time created by having three active copies of your database. Should there be a hardware or software failure that would cause the primary replica to go down, your database gets moved to one of the secondary replicas which has been maintaining a copy of the data from the primary replica. In theory, the worst thing that happens is that you have to retry a query. In fact, building that sort of resilience into your code is a pretty fundamental aspect of working with WASD. I was asked the question, how do you tell if your database has been moved between replicas. I didn't have a complete answer, so I set out to find one. The first, more obvious…
Read More

Deadlock Monitoring

SQL Server 2008, SQL Server 2012, SQLServerPedia Syndication, TSQL
There are four different ways you can get information about deadlocks in your system. These are: traceflag 1204 traceflag 1222 trace events extended events For years I’ve been pushing traceflag 1222 as the best of the lot. Well, that’s over. I’ve been learning more and more about extended events and I’m currently in love with xml_deadlock_report event. Why? Simple, it has everything that traceflag 1222 has, but there are two glorious things about it. First, it’s not going to be filling up my error log with, for the error log, noise. Seriously. As much as I liked the information displayed from traceflag 1222, I didn’t like what it did to the log, but I saw it as a necessary evil. Second, it’s XML baby! That means you can set up…
Read More

Extended Events Data

SQL Server 2012, SQLServerPedia Syndication
I’ve been working quite a bit over the last week or so with extended events in Denali. The sheer magnitude of what you can do with extended events is just becoming clear to me. The interesting thing though is how much the basics are similar to trace. Similar mind you, not the same. For example, the best way to gather trace data is to output it to a file and then read the file into a table for later querying. It’s the same with extended events. There’s even a function that acts as a table: [sourcecode language="sql"]SELECT * FROM sys.fn_xe_file_target_read_file ('C:\APath\Query Performance Tuning*.xel', NULL, NULL, NULL);[/sourcecode] This can take advantage of roll-over files just like the old function used for traces. You can also provide offsets to read a sub-set…
Read More