A View Will Not Make Your Query Faster

SQL Server 2017
Twice recently, one on a blog post, and one in a forum post, I've seen people state, unequivocally, without reservation or hint of a caveat, that, "Oh, just put that query into a view. It will run faster." To quote the kids these days... Time for a rant. But First... Frequently when I post something that says, "Query Y runs faster than Query Red", I get responses from people saying, "Yeah, but if you run Query Red more than once..." or "Query Red was experiencing blocking..." or "You can't say Query Y is ALWAYS faster..." So, before we go down that road, a quick note on methodology. First, I'll be using Adventureworks because, reasons. Second, I won't run any of the following queries once. When doing something like this, I'll…
Read More

Automating Automatic Indexing in Azure SQL Database

Azure
I've been in love with the concept of a database as a service ever since I first laid eyes on Azure SQL Database. It just makes sense to me. Take away the mechanics of server management and database management. Focus on the guts of your database. Backups, consistency checks, these easily automated aspects can just be taken care of. Same thing goes with some, not all, but some, index management. Azure SQL Database can manage your indexes for you. I call it weaponizing Query Store. Anyway, I needed a way to automate this for the book I'm writing. I couldn't find any good examples online, so I built my own. Queries in Need of Automatic Indexing Because I want this to be as simple and repeatable as possible, I'm using…
Read More

Every Single Execution Plan is an Estimated Plan

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017
All the execution plans are estimated plans. All of them. There fundamentally isn't any such thing as an "Actual" plan. Where Do You Get Execution Plans? There are a lot of sources for execution plans. You can capture them using extended events (or, if you must, trace). You can capture them through the Management Studio gui. You can also capture them from the SQL Operations Studio gui. You can query the cache through the DMVs and pull them in that way. You can look at plans in query store. All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they're all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This…
Read More

Precons in Richmond, Philadelphia and New York

Professional Development
I love when I get the opportunity to present at SQLSaturday events. Even more than that, I love when I get the opportunity to do a precon at a SQLSaturday event. Well, I've got three coming up. All three are an all day session entitled "SQL Server Tools for Query Tuning." Seating at all the events is limited, so please register early. First, in Richmond, on March 23, 2018, you can register here. Then, I'll be in Philadelphia on April 20, 2018. You can sign up here for that event. I will be in New York, NY, my old stomping grounds, May 18, 2018. Go here to register for that event. I hope to see you at one of these events where I'll do my best to share as much…
Read More

Wait Statistics on a Query

SQL Server 2012, SQL Server 2014, 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

Runtime Metrics In Execution Plans

SQL Server 2016
Capturing query execution metrics is much easier now that you can see the runtime metrics in execution plans when you're using SQL Server 2016 SP1 or better in combination with SQL Server Management Studio 2017. When you capture an actual plan (using any method), you get the query execution time on the server as well as wait statistics and I/O for the query. Fundamentally, this changes how we can go about query tuning. Runtime Metrics To see these runtime metrics in action, let's start with a query: SELECT p.LastName, pp.PhoneNumber, pnt.Name FROM Person.Person AS p JOIN Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID JOIN Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID WHERE pnt.PhoneNumberTypeID = 3; We'll run this query and capture the Actual Execution Plan using SSMS 2017. The changes…
Read More

Bad Parameter Sniffing Decision Flow Chart

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
Lots of people are confused by how to deal with bad parameter sniffing when it occurs. In an effort to help with this, I'm going to try to make a decision flow chart to walk you through the process. This is a rough, quite rough, first draft. I would love to hear any input. For this draft, I won't address the things I think I've left out. I want to see what you think of the decision flow and what you think might need to be included. Click on it to embiggen. Thanks to the attendees at my SQLSaturday Louisville pre-con for the great questions and the inspiration to get this done. Thank you in advance for any and all feedback.
Read More

IT/Dev Connections 2017

Database Lifecycle Management, DevOps, SQL Server 2016
I'm very honored to be able to announce that I am going to be speaking at IT/Dev Connections in San Francisco. I'm not just speaking there, I'm presenting an all day seminar on the tools needed for query tuning. The title does say SQL Server 2016, but most of the tools I'll cover can be used be used from SQL Server 2012 to SQL Server 2017. I'll also throw in a few SQL Server 2017 tools just to spice things up. If you're looking for a lot of information about how to get your query tuning done, I'm here to help. I'm also going to be talking about two other favorite topics of mine, DevOps and Monitoring. Please check it out and join me at this event.
Read More

I Love Entity Framework

DevOps
I love Entity Framework. I also like (not love) nHibernate. That's right, as a DBA and data professional, I'm telling you I love Object/Relational Mapping tools (ORM). I think this is a technology set that the DBA needs to more tightly embrace. Let me tell you why. Most of the Queries I know that the biggest pushback against Entity Framework (EF) and it's fellow ORM tools is that they generate crap code. I know this to be true. I've seen it. ORM tools can, and do, generate seriously poor T-SQL. That's not to mention the N+1 problem and a few others. However, as you see from the article in that link, these problems and how to avoid them are very well defined. You don't have to suffer from the issues.…
Read More