13Feb 2017 by Grant Fritchey 2 Comments

Export All Plans From Cache to a .SQLPLAN File

PowerShell
I was asked if there was an easy way to export all the plans from cache to a .SQLPLAN file. My immediate answer was, "I'm sure there's a PowerShell script out there somewhere." However, rather than do a Gingle search, I figured I'd quickly knock up an example. The Script I've gone minimal on the script. I'm creating a connection to the local instance, defining a command, and returning the data into a data set. From there, since the data set consists of a single column, I'm walking through them all to export out to a file: $Query = 'SELECT deqp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp WHERE deqp.query_plan IS NOT NULL;' $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = 'Server=ServerX\DOJO;Database=master;trusted_connection=true' $PlanQuery = new-object System.Data.SqlClient.SqlCommand $PlanQuery.CommandText = $Query $PlanQuery.Connection…
Read More
07Feb 2017 by Grant Fritchey 4 Comments

Does Query Store Pre-Allocate Space

SQL Server 2016, T-SQL
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
06Feb 2017 by Grant Fritchey No Comments

Still Grateful to Rodney Landrum

Professional Development
So, my blog got hacked. They edited the page where I thanked Rodney for all that he did. I had to remove that post as a part of the cleanup (still ongoing). However, I couldn't let that stand. I've used caching on the internet to track down the original post because that must live on. Here you go Rodney. Thanks again. I recently was honored to take part in the SQL Sons of Beaches Tour. It was a whirlwind of five user groups across the entire state of Florida in five days. I was joined on the tour by Denny Cherry. He and I did most of the presentations (except Tampa where we also had Buck Woody). We were driven from place to place by Karla & Rodney Landrum. I…
Read More
23Jan 2017 by Grant Fritchey 4 Comments

Data About Execution Plans

SQL Server, SQL Server 2016
If you look at the Properties for the first operator of a graphical execution plan, you get all sorts of great information. I've talked about the data available there and how important it is in this older post. Checking out the properties of a plan you're working on is a fundamental part of tuning that plan. What happens when you don't know which plan you should be working on? What do you do, for example, if you want to see all the plans that are currently using ARITHABORT=FALSE or some other plan affecting setting? The "easy" answer to this question is to run an XQuery against the XML of the query plan itself. You can identify these properties and retrieve the appropriate values from within the plan. However, XQuery consumes quite a…
Read More
18Jan 2017 by Grant Fritchey 7 Comments

Scheduling Statistics Maintenance in Azure SQL Data Warehouse

Azure
The power and capabilities of Azure SQL Data Warehouse are pretty amazing. However, it's still basically SQL Server under the covers. SQL Server still needs maintenance and one type of maintenance is keeping statistics up to date. Statistics maintenance is doubly important within Azure SQL Data Warehouse because statistics are not created automatically, nor are they maintained automatically. It's up to you to ensure your statistics are up to date. Statistics in Azure SQL Data Warehouse It's worth re-emphasizing the point that there is no automatic creation of statistics in Azure SQL Data Warehouse. This means you need to plan to add statistics to your tables. Microsoft recommends an easy method is to add them to every column. While that does make things easy, it's not like statistics are free. If you…
Read More
10Jan 2017 by Grant Fritchey 3 Comments

Query Optimizer and Data Definition Language Queries

SQL Server, SQL Server 2016, T-SQL
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
20Dec 2016 by Grant Fritchey 1 Comment

Database Configuration

Azure, SQL Server 2016
It's amazing just how much the landscape changed with the release of SQL Server 2016 SP1. For example, I just found out that you can disable parameter sniffing at the database level using the database configuration. Not only does this work for SQL Server 2016 SP1, but it's enabled for Azure SQL Database. How Database Configuration Works The syntax is very simple and documented here. So, if I want to disable parameter sniffing for a single database, I can do this: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; That's it. Done. It works from within the database and doesn't require rebooting or anything else. Changing this setting does flush the cache of all the execution plans for that database. No other actions are necessary. You can control parameter sniffing at the…
Read More
19Dec 2016 by Grant Fritchey No Comments

PASS Board: Year-End Wrap-Up

PASS
This month concludes my second full year on the PASS Board of Directors and my first full year on the Executive Committee. This year has been exciting, challenging, educational, and, I truly believe, useful to the organization. We've accomplished a lot. We will do more. Year In Review I have two primary responsibilities as the Executive Vice President – Finance and Governance. Firstly, I am responsible for PASS’ budget.  Secondly, I am responsible to oversee the governance of PASS. Here's how the year went on these two topics. Finance At the beginning of the year, our primary focus is to ensure that the various directors in charge of different portfolios are getting ready for the annual budget. Our fiscal year runs from July to June. A significant amount of work is spent…
Read More
12Dec 2016 by Grant Fritchey 7 Comments

OPTIMIZE FOR Hints When Parameter Sniffing is Turned Off

Azure, SQL Server, SQL Server 2016, T-SQL
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