I'm excited to able to say that I've been given the opportunity to put on a full day workshop at SQL Connections on Friday, September 19th, 2014. The title is "Query Performance Tuning in SQL Server 2014", but I assure you we're going to cover things that are applicable if you're still working on SQL Server 2005. We'll start the day covering the different mechanisms you have to capture query metrics. We'll go over dynamic management objects and extended events that are incredibly important to you in understanding which queries you need to tune. We'll get an introduction into how the optimizer works and the importance that statistics, indexes and constraints play in helping the optimizer make the choices it makes. I promise, execution plans will be covered throughout the…
The combination of in-memory tables and natively compiled procedures in SQL Server 2014 makes for some seriously screaming fast performance. Add in all the cool functionality around optimistic locking, hash indexes and all the rest, and we're talking about a fundamental shift in behavior. But... Ah, you knew that was coming. But, you can still write bad T-SQL or your statistics can get out of date or you can choose the wrong index, or any of the other standard problems that come up that can negatively impact all those lovely performance enhancements. Then what? Well, same as before, take a look at the execution plan to understand how the optimizer has resolved your queries. But... Yeah, another one. But, things are a little different with the natively compiled procedures and…
It's kind of fun to see Azure development artifacts on display. I've posted about them before, a couple of times. I'm starting to finally get systematized about the whole thing, just so I can see stuff as it changes rather than discover them by accident or get told about them by someone else. Here's a little query I'm running to see when system views were last modified: SELECT av.name, av.create_date, av.modify_date FROM sys.all_views AS av ORDER BY av.modify_date DESC; The most recent stack of changes are here: I'll keep an eye on them to see what I can spot about interesting new functionality. I also compared the listing of all views in Azure to those on a SQL Server 2012 instance and came up with a list of differences. These…
I am excited to be able to tell you about an all day seminar that I'll be putting on prior to the Dallas SQL Saturday #255. The seminar will be on November 1, 2013. It's called Query Performance Tuning in SQL Server. We're going to cover the topic from an understanding of the optimizer to collecting data using extended events to reading execution plans and then on to lots of standard problems and their solutions. If you sign up before September 21st you can get a substantial early-bird discount, so I'd jump on it. Also, seats are limited, so don't wait too long. Let's get together and talk query tuning.
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: CREATE PROCEDURE DL2e WITH ENCRYPTION AS BEGIN TRANSACTION UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 2 WHERE ProductID = 448 AND PurchaseOrderID = 1255; Then I’ll execute things in the following order. From one connection this query: UPDATE Purchasing.PurchaseOrderHeader SET Freight = Freight * 0.9 --9% discount on shipping WHERE PurchaseOrderID = 1255; From a second connection, my stored procedure: EXEC dbo.dl2e; Then, back on the first connection, this query: UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 4 WHERE ProductID = 448 AND PurchaseOrderID = 1255; That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: <deadlock> <victim-list> <victimProcess id="process472310928" />…
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…
Microsoft is supporting an effort by PragmaticWorks targeted at supporting technical training for returning veterans. I can’t think of a single better cause to throw some support behind. Not one. They’re going to donate money based on posts about #sqlfamily. Well done to Brian Knight (blog|twitter) and all the team at PragmaticWorks. I knew you were great people, I just didn’t know how great. Thanks to Microsoft and the SQL Server Team for their support of Brian. Oh, and for all the work you guys do with SQL Server. I may bitch about you guys more than you’d like, but it’s only because I live inside your software, constantly. I wouldn’t be there all the time if you didn’t do great work. Keep it up. We can talk about this…
I only half listened to the key notes. The party's the preceding night may have had something to do with it. First session was with Kalen Delaney on Plan Guides. She didn't really do plan guides though. Instead she talked about guiding plans. She is such a great presenter. I like her use of the language and precise definitions. Lubor Kollar (sp?) was in the room and made a point of standing up & addressing some of Kalen's info. My best take away was that plan guides (she covered those too) do not reduce compile time and can in fact increase compile time. Second best was the use of plan guides as a mechanism for testing since you can apply the guide, enable & disable, without rewriting the proc. Sessions…
Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced: dm_db_stats_histogram. We've always been able to query the statistics using DBCC SHOW_STATISTICS. However, the output, three different result sets with three different structures, made automating access to statistics information a pain. Now, we have more capability through dm_db_stats_histogram. dm_db_stats_histogram To access the information in dm_db_stats_histogram, you just have to pass in the object_id and the statistics_id values for the statistics you're interested in like this: SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('HumanResources.Employee'), 1) AS ddsh; It's very straight forward to use. The results look like this: Handy right? Now you can query the histogram directly. Yeah, I hear a few of you saying... and this helps me... how? Here's an example. This query…
[caption id="attachment_2827" align="alignleft" width="150"] This is me on the last cruise[/caption] In just a few more weeks I'll be setting sail on the first of two SQL Cruise events this year. I'm honored beyond my ability to appropriately express it to be included as one of the Technical Leads for both the cruise in February (7-14, 2015, I think there's a seat or two left) to the Caribbean and the one in June (14-21, 2015, definitely a couple of seats left) to the Mediterranean. Lest you think that this is just an excuse to hang out and drink, you ought to know a little about how sessions are presented on the cruise and the sessions I'm presenting. Don't mistake sessions on the boat for your typical one hour session at…