Does Query Store Pre-Allocate Space

SQL Server 2016, TSQL
I love the questions I get while I'm presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I'm not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn't going to be pre-allocated in any way. The space will just get used as and when it's needed, just like any other system table. However, don't take my word for it, let's prove that. The Test Testing whether or not enabling Query Store is straight forward. Here's a query that should give us information rather quickly: CREATE DATABASE QSTest; GO USE…
Read More

Query Optimizer and Data Definition Language Queries

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
Data Definition Language queries don't go through the optimizer, right? While normally, my short answer to this question in the past would have been, yes. However, with testing comes knowledge and I want to be sure about the statement. I'm working with a team of people to completely rewrite the SQL Server Execution Plans book. We'll probably be published in April or May. It's going to be amazing. The structure will be completely different and the book will represent five years of additional knowledge in how execution plans work and how to read and interpret them since the last book was written. However, enough on that. Let's answer the question about Data Definition Language. First of all, we need to quickly define our terms. Data Definition Language (DDL) represents the syntax for queries that build…
Read More

OPTIMIZE FOR Hints When Parameter Sniffing is Turned Off

Azure, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled? This is my favorite kind of question because the answer is simple: I don't know. Parameter Sniffing For those who don't know, parameter sniffing is when SQL Server uses the precise values passed into a query as a parameter (this means stored procedures or prepared statements) to generate an execution plan from the statistics using the value from the parameter. Most of the time, parameter sniffing is either helping you, or is not hurting you. Sometimes, parameter sniffing turns bad and hurts you quite severely. Usually, but not always, this is because you either have severely skewed data (some data is very different than the rest, lots…
Read More

Database Clone

SQL Server 2014, SQL Server 2016, TSQL
There are a bunch of ways you could create a database clone. Backup and restore is one method. Export/Import is another. There are even third party tools that will help with that. However, each of these has a problem. It's moving all the data, not just once, but twice. You move the data when you export it and you move it again when import it (same thing with backup and restore). That makes these methods slow for larger databases. How can you create a database clone without moving the data multiple times? Don't Move the Data At All New with SQL Server 2016, expanded in SP1, and added to SQL Server 2014 SP2 is a new command, DBCC CLONEDATABASE. This is like a dream come true. The use is extremely…
Read More

PowerShell to Test a Query

DevOps, PowerShell, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
So you want to do some tuning, but you're not sure how to test a query on it's performance. Not a problem. Here's a very rough script that I use to do some recent testing. This script to test a query is post #11 of the #enterylevel #iwanttohelp effort started by Tim Ford (b|t). Read about it here. The Script The goal here is to load a bunch of parameter values from one table and then use those values to run a query to test it. To do this I connect up to my SQL Server instance, naturally. Then I retrieve the values I'm interested in. I set up the query I want to test. Finally a loop through the data set, calling the query once for each value. [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #…
Read More

Query Data Store Data

SQL Server 2016, TSQL
The data in the Query Data Store is what makes all the magic happen. From collecting the aggregate performance metrics of a query to the various plans in use by that query to being able to force a plan, it's all controlled by the data within the Query Data Store system tables. The Question When I was presenting on this topic at the PASS Summit a few weeks ago, one great question came up (great question = answer is "I don't know"), well, I defaulted to an "I don't know" answer, but my guess was, "No." The question was: can you take a plan from one server, let's say a test server, export it in some way, and then import it to production? In this manner, you ensure that a…
Read More

sp_executesql Is Not Faster Than an Ad Hoc Query

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
This requires an immediate caveat. You should absolutely be using sp_executesql over any type of non-parameterized execution of T-SQL. You must parameterize your T-SQL because the lack of parameters in building up and executing strings is a classic SQL Injection attack vector. Using straight ad hoc T-SQL is an extremely poor coding choice because of SQL Injection, not because there is something that makes one method faster than the other. Yet, I see in performance checklists that you should be using sp_executesql over straight ad hoc T-SQL because it will perform faster. That statement is incorrect. Some Discussion Let me reiterate the caveat before we continue. I 100% advocate for the use of sp_executesql. This function is preferred over ad hoc SQL because, used properly (and isn't that usually one of the main problems,…
Read More

Reinforcing the Importance of Statistics on Row Estimate

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
I recently wrote an introductory post about the importance of statistics. I just received a reinforcement on how important they are during my own work. Bad Estimate I hit a weird problem while I was setting up a query to illustrate a point (blog to be published next week). Let's take the basis of the problem and explain it. I wanted data with distribution skew, so I ran this query to find out if there was a wide disparity between the top and bottom of the range: SELECT i.BillToCustomerID, COUNT(i.BillToCustomerID) AS TestCount FROM Sales.Invoices AS i GROUP BY i.BillToCustomerID ORDER BY TestCount ASC; Sure enough, the bottom of the range returned three (3) rows and the top returned 21,551. If I then run a query to retrieve just a few rows…
Read More

Stored Procedures Are Not Faster Than Views

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
A performance tuning tip I saw recently said, "Views don't perform as well as stored procedures." <sigh> Let's break this down, just a little. Definitions A view is nothing but a query. The definition given by Microsoft is that it's a virtual table that's defined by a query. It's a query that is used to mask data or perform a complex join or similar behaviors. Views are queries that get stored in the database. Views can be easily referred to as if they were a tables. That's it. I've written in the past about views, including how they can possibly perform poorly. A stored procedure is also a query, or a series of queries, or, a whole lot more. Microsoft's definition of a stored procedure basically defines it as programming object that can accept input through…
Read More

A Sub-Query Does Not Hurt Performance

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
The things you read on the internet, for example, "don't use a sub-query because that hurts performance." Truly? Where do people get these things? Let's Test It I've written before about the concept of cargo cult data professionals. They see one issue, one time, and consequently extrapolate that to all issues, all the time. It's the best explanation I have for why someone would suggest that a sub-query is flat out wrong and will hurt performance. Let me put a caveat up front (which I will reiterate in the conclusion, just so we're clear), there's nothing magically good about sub-queries just like there is nothing magically evil about sub-queries. You can absolutely write a sub-query that performs horribly, does horrible things, runs badly, and therefore absolutely screws up your system. Just as…
Read More