20Jul 2015 by Grant Fritchey 5 Comments

SQL Server Management Studio – Footloose and Fancy Free

Azure, SQL Server
That's right. There's been a divorce. SQL Server Management Studio (SSMS) has been divorced from the server product. In fact, Microsoft is inviting you to the new SSMS coming out party. I'm pretty excited about this. While I'm very comfortable in SSMS, to a large degree, it's like that old pair of jeans that you've worn for the last 10 years. They're comfortable too. Well, maybe a little tight when you pull them on out of the wash. One of the knees is gone. The legs are frayed so much it almost looks intentional. You just noticed a hole in the bottom. The zipper is acting up... Yeah, OK. These jeans have had it. So has SSMS. The plan from Microsoft is to upgrade SSMS independently from the boxed product. In fact, since one of…
Read More
06Jul 2015 by Grant Fritchey 4 Comments

But I Don’t Have a Test Server…

Database Lifecycle Management, DevOps, SQL Server, T-SQL
I frequently see statements on forums along the lines of "I don't have a test server, so I'm going to do something I've never done before directly on my production servers..." This is usually followed by questions along the lines of: But that's OK and I won't get fired, right? So how do I know if it worked? Is that dangerous? The replies are: No. You should be fired You won't By All the Gods! Yes! It's stupidly, insanely, I don't really like my production server, my data, my boss, or my job, dangerous. Yes. I understand. You're working for a not-for-profit, so you can't afford tons of new servers. You're looking at a 50tb production server of which, you can't make a copy. Your bosses just don't want to spend the…
Read More
26May 2015 by Grant Fritchey 11 Comments

Database Engine Tuning Advisor

Azure, SQL Server, T-SQL
I would love to see the Database Engine Tuning Advisor (DTA) pulled from the product. Completely. Heck, I feel bad that I included a chapter on it in my query tuning book (all updated for SQL Server 2014 by the way). Let me tell you why we need to pull this tool. First, I understand its purpose. It's supposed to be a fast and easy way to get some performance tuning done for people who just don't have the time or knowledge to go through the full process of gathering metrics, evaluating poor performers, understanding root causes and applying indexes to fix those causes. I also readily acknowledge that it actually is an amazing piece of software. If you don't agree with that, go read this white paper. With those acknowledgements…
Read More
21Apr 2015 by Grant Fritchey 5 Comments

Query Hash Formula Changes Between Versions

SQL Server
The question came up on SQL Server Central that someone wanted to track queries on their 2008R2 instance and on their 2014 instance in order to validate performance after an upgrade. The plan was to use query hash values. I was curious, so I set up a quick test. First, I spun up two VMs in Azure, both A1, both Enterprise, but one for 2008R2 and one for 2014. Then, I grabbed a generic query that runs against DMVs: SELECT TOP 10 SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) ELSE deqs.statement_end_offset - deqs.statement_start_offset END) / 2 + 1) AS querystatement, deqp.query_plan, deqs.query_hash, deqs.execution_count, deqs.last_elapsed_time, deqs.last_logical_reads, deqs.last_logical_writes, deqs.last_worker_time, deqs.max_elapsed_time, deqs.max_logical_reads, deqs.max_logical_writes, deqs.max_worker_time, deqs.total_elapsed_time, deqs.total_logical_reads, deqs.total_logical_writes, deqs.total_worker_time FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY…
Read More
06Apr 2015 by Grant Fritchey 8 Comments

Constraints and SELECT Statements

Azure, SQL Server, T-SQL
I've posted previously about how a foreign key constraint can change how a SELECT query behaves. Logically that just makes sense. But other types of constraints don't affect execution plans do they? Yes. Let's take this constraint as an example: ALTER TABLE Sales.SalesOrderDetail WITH CHECK ADD  CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK  ((UnitPrice>=(0.00))) That will ensure that no values less than zero can slip in there. We can even validate it: INSERT Sales.SalesOrderDetail (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate ) VALUES (60176, -- SalesOrderID - int N'XYZ123', -- CarrierTrackingNumber - nvarchar(25) 1, -- OrderQty - smallint 873, -- ProductID - int 1, -- SpecialOfferID - int -22, -- UnitPrice - money 0.0, -- UnitPriceDiscount - money NEWID(), -- rowguid - uniqueidentifier GETDATE() -- ModifiedDate - datetime ); Will give me…
Read More
24Mar 2015 by Grant Fritchey 3 Comments

Simple Parameterization and Data Types

SQL Server, T-SQL
Simple paramaterization occurs when the optimizer determines that a query would benefit from a reusable plan, so it takes the hard coded values and converts them to a parameter. Great stuff. But... Let's take this example. Here's a very simple query: SELECT ct.* FROM Person.ContactType AS ct WHERE ct.ContactTypeID = 7; This query results in simple parameterization and we can see it in the SELECT operator of the execution plan: We can also see the parameter that was defined in use in the predicate of the seek operation: Hang on. Who the heck put the wrong data type in there that's causing an implicit conversion? The query optimizer did it. Yeah. Fun stuff. If I change the predicate value to 7000 or 700000 I'll get two more plans and I…
Read More
19Jan 2015 by Grant Fritchey 17 Comments

Understand the True Source of Problems

SQL Server, T-SQL
There's an old joke that goes, "Doctor, doctor, it hurts when I do this." While the person in question swings their arm over their head. The doctor's response is, "Don't do that." Problem solved, right? Well, maybe not. Let's take a quick example from life. I do crossfit (yeah, I'm one of those, pull up a chair I'll tell you all about my clean & jerk progress... kidding). I've been experiencing pain in my shoulder. "It hurts when I do this." But, I'm not going to stop. I've been working with my coach to identify where the pain is and what stretches and warm-ups I can do to get around it (assuming it's not a real injury, and it isn't). In short, we're identifying the root cause and addressing the…
Read More
19Dec 2014 by Grant Fritchey 20 Comments

“Pretty Plans vs. Performance” or “Grant Gets Pwned”

SQL Server
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,…
Read More
16Oct 2014 by Grant Fritchey No Comments

Query Performance Tuning in SQL Server

SQL Server
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…
Read More
23Sep 2014 by Grant Fritchey 8 Comments

SQL Server Query Performance Tuning

SQL Server, T-SQL
The latest update to my book, SQL Server Query Performance Tuning was released last week. This is the fourth edition of the book, and the third edition that I've been responsible for. At the urging of my editor, Jonathan Gennick, I have completely restructured the book for this release. The chapters have been broken up and rearranged so that they're smaller, more easily consumed. Yes, I've worked with my technical editor, Joe Sack, to add lots of new information and to ensure that the existing information is more accurate and more useful. But, we've also added new chapters on topics that weren't given enough attention in the previous versions of the book, such as parameter sniffing. It can certainly seem like these book releases are just some incremental changes on top of…
Read More