19Mar 2018 by Grant Fritchey 1 Comment

Query Monitoring and the GDPR

DevOps
I've been reading the General Data Protection Regulation (GDPR) and discussing the ramifications of the beginning of enforcement with lots of people. The implications of it all are fascinating. The real serious issues remain primarily a business problem, with business defined solutions. However, there are technology issues that we need to think about. For example, performance metrics are going to be impacted by the GDPR. Private Data and Monitoring Queries First and foremost, let me say something I've said before. The vast majority of the focus around GDPR has to come from your business. Second, the bulk of your work and focus must be on ensuring core functionality in support of the GDPR. Third, the attack vectors and leaks for GDPR are not going to primarily be around something like…
Read More
05Feb 2018 by Grant Fritchey 3 Comments

Wait Statistics on a Query

SQL Server, SQL Server 2016, SQL Server 2017
Wait statistics are a vital part of understanding what is causing your system to run slowly. Capturing them can be done through a variety of mechanisms from sys.dm_os_wait_stats (use this query for that) to sys.dm_db_wait_stats in Azure SQL Database. Those cover the system and the database, however, what about capturing query wait statistics on a specific query? Query Wait Statistics There was a time when this was actually kind of difficult. However, now we have a lot of different tools to capture query wait statistics. First up, and really, one of the best and easiest ways to deal with this, is to use the wait statistics captured by the Query Store. The only drawback to this method is that it is an aggregation of query wait statistics for the given…
Read More
20Nov 2017 by Grant Fritchey 3 Comments

Get That Profiler Feel in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017
I know. You love Profiler. I hear you. You're wrong, but that's OK. Kidding... mostly. Unfortunately though, I think a lot of what passes for issues and problems with Extended Events is actually a lack of knowledge about how they work. Let's take an example and run with it. No Grid in Extended Events One of the pushbacks I hear about using Extended Events is that the Live Data GUI just doesn't have that neat Profiler grid output. Instead you see a list of events in the top pane and then you have to look at the details in the bottom pane. It looks like this out of the gate: You're right. That's a royal pain. That's it. Toss Extended Events. Back to Profiler. Well, hang on a second. Let's…
Read More
07Nov 2017 by Grant Fritchey 19 Comments

Extended Events and Profiler: XE Profiler

SQL Server 2017
There's a war on in the SQL Server world. On the one side is Profiler (although, really, everyone uses Trace Events). On the other, the "new" (they came out in 2008 with a full GUI in 2012, so...) Extended Events. Lots of people have picked sides on this, including Microsoft. New Trace Events There are none. All the new functionality of every sort from Availability Groups to Query Store to R & Python, have Extended Events created for them. Trace Events, and the technologies supporting them in the form of Profiler, are a dead end. Don't fear. While Trace is on the deprecation list, there doesn't appear to be any fear of that technology being removed completely. At least it won't be removed in the foreseeable future. A future which,…
Read More
24Feb 2016 by Grant Fritchey 6 Comments

Query Store and Recompile

Azure, SQL Server 2016, T-SQL
One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; If this procedure is called with the value of 'Mentor' you get…
Read More
12Mar 2015 by Grant Fritchey 13 Comments

Monitoring for Timeouts

T-SQL
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
23Jan 2012 by Grant Fritchey 6 Comments

Deadlock Monitoring

T-SQL
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
17Oct 2011 by Grant Fritchey 1 Comment

Extended Events Data

Uncategorized
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: SELECT * FROM sys.fn_xe_file_target_read_file ('C:\APath\Query Performance Tuning*.xel', NULL, NULL, NULL); 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 of…
Read More
05Oct 2011 by Grant Fritchey 1 Comment

Extended Events and Performance Tuning Knowledge

Uncategorized
I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
Read More