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

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

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

Presentations for SQL Server Beginners

Azure, PASS, PowerShell, Professional Development, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
[caption id="attachment_2548" align="alignleft" width="300"] Tired from all those blog posts[/caption] For my final blog post in the #gettingstarted, #iwanttohelp series, I decided to curate a set of presentations from the PASS Virtual Chapters. This content is available online. It's free. Most importantly for the person just getting started as a SQL Server data pro, it's good. I'm going to marry each of the presentations with my eleven blog posts in this series. The Importance of a Full Backup in SQL Server For this one I'm going to recommend Tim Radney's session Understanding SQL Server Backup and Restore. I know Tim personally and guarantee this is a good session. Why Is The Server Slow Jes Borland is a very close personal friend and an absolutely outstanding presenter (and person). She has…
Read More

Azure Data Platform Learning Resources

Azure
I know that more and more of you are moving more and more resources into the Azure Data Platform. In an effort to help you learn about all that Azure offers I created a GitHub repository of blogs, teachers, courses, etc. It's open to the community to update. New material has been recently added and there will be more and more all the time. Check it out. If you know of resources that should be listed there, please issue a branch pull and add them right in. This is your resource.
Read More

Azure Data Platform Resources

Azure
A few months ago I created a GitHub repository for the purpose of building and maintaining a list of resources for Azure Data Platform training. You can see it here. My goal in putting this into GitHub instead of just running it on my blog is to make it a community resource. I want all of you to maintain it. If you're teaching a class (one hour or one week, I don't care), I'd like you to add yourself to the instructors list. If you have a blog where you post Azure content, please, add your blog. Are you a PowerBI monster? Get on the list. Please, help me create and grow this list so that people have a central, public, resource for this information. More and more of you are…
Read More

The Clustered Index Is Vital To Your Database Design

Azure, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
This is post #9 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here. You get one clustered index per table. That bears repeating, you get one clustered index per table. Choosing a clustered index is an extremely important and fundamental aspect of all your SQL Server work. The one clustered index that you get determines how the data in your table is stored. Because the clustered index determines how your data is stored, it also determines how your data is retrieved. Much of SQL Server is engineered around the clustered index because it is such a foundational object for the rest of all behavior. Without a clustered index, the data in your table is stored in what is called a heap. It is essentially a pile, a heap, of data,…
Read More

Azure SQL Data Warehouse Execution Plans

Azure
Azure SQL Data Warehouse can sometimes feel like it's completely different from SQL Server, but under the covers, it is still (mostly) SQL Server and it does have execution plans. Let's take a look at one. I've created a new SQL Data Warehouse using the sample database available on the portal, AdventureWorksDW. Here's a query against that database: SELECT dd.FullDateAlternateKey AS OrderDate, dc.LastName, SUM(fis.SalesAmount) AS SumSalesAmount FROM dbo.FactInternetSales AS fis JOIN dbo.DimDate AS dd ON fis.OrderDateKey = dd.DateKey JOIN dbo.DimCustomer AS dc ON dc.CustomerKey = fis.CustomerKey GROUP BY dd.FullDateAlternateKey, dc.LastName HAVING SUM(fis.SalesAmount) > 5000.0 ORDER BY OrderDate DESC; If I attempt to capture an execution plan using the SQL Server Management Studio GUI, nothing happens. If I try to use T-SQL commands, I get an error that those commands are…
Read More

Updates to Azure Data Platform Learning Resources

Azure
I'm acting as the curator to a list of Azure Data Platform Learning Resources. It's speakers and teachers, blogs, articles and more, all focused around the Azure Data Platform. I have it hosted on GitHub so that anyone can contribute. There have been several updates recently that you may want to take a look at. If you're looking for where and how to get started in the Azure Data Platform, this is a great resource to get you going. If you're teaching the Azure Data Platform, anything from PowerBI to DocumentDB to Azure SQL Data Warehouse and all the stuff in between, please add your information so that this list is up to date.
Read More

Query Store, Force Plan and Dropped Objects

Azure, SQL Server 2016
I love the Query Store. Seriously. It’s a huge leap forward in the capabilities of Azure SQL Database and SQL Server in support of performance monitoring and query optimization. One of my favorite aspects of the Query Store is the ability to force plans. Frankly though, it’s also the scariest part of the Query Store. I do believe that plan forcing will be one of the most ill-used functions in SQL Server since the multi-statement table-valued user-defined function (don’t get me started). However, unlike the UDF, this ill-use will be because of poor understanding on the part of the user, not a fundamental design issue. No, plan forcing and the Query Store are very well constructed. Let me give you an example of just how well constructed they are. Let’s…
Read More