If you get an execution plan that looks like this: I wouldn't blame you for immediately thinking about query tuning. Especially if the code that generated it looks like this: SELECT soh.OrderDate, sod.OrderQty, sod.LineTotal FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80,…
I have a pre-con coming up at the PASS Summit. You can read about it here. I named it "Query Performance Tuning in SQL Server 2014" because it seemed like a good idea to bring out the aspects of 2014, and we will. But, I need to tell you, this is primarily a session about query performance tuning in SQL Server, full stop. I'm going to cover information that's applicable all the way back to SQL Server 2000 and 2005. The majority of the information will be applicable to 2008 and up. I'm going to go over the things you can do with dynamic management views to pull information about queries to tune right out of the cache. That's applicable to more than 2014. We're also going to go over…
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…
Many years ago, I was working with a great DBA. Seriously, a very smart and capable guy. He told me, "We need to put the database into source control, just like app code." And I just laughed. Not because I disagreed with him. I knew he was right, but I had tried, several times, to do just that. See, I'm not really a DBA. I'm a developer. I knew that code (and all the T-SQL that describes databases is code) needed to be versioned, sourced, tracked and audited. But great googly moogly, it was not an easy thing to do. I first tried just exporting the entire database into a script and then occasionally checking that script into source control. Yay! Mission Accomplished... Well, I had a database in source…
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…
Red Gate visited three cities this year with our SQL in the City event; Pasadena, Atlanta and Charlotte. I just wanted to give you a quick assessment of how the events went from my point of view. Overall, each and every one of these events was awesome. I can safely say that because each and every one of these events provided something special, the opportunity to network with our peers and with the developers and project managers at Red Gate (who are also our peers, but not usually available to us). I both took part in the networking and stood back and watched it happen. I love seeing a bunch of data pro's sitting (or standing) in a circle exchanging war stories, ideas, questions, thoughts or suggestions. It means you…
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.
If you asked me, prior to today, if I would type or say those words, I would have laughed right at you. But then, I saw this question on Ask SQL Server Central. It's from a college student, not yet twenty-one, who was considering paying his (assuming it's a guy since their handle is 'Eagle Scout') own way to the Summit and wondered if it would be worth it. It pains me to say that I suggested that he not do it. Don't get me wrong. I think the PASS Summit is probably the single greatest resource you have to advance your career. Where else can you go to get that broad a choice in training? Where else can you go to get that many of the leaders of our industry, specializing…
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" />…