Microsoft has long given us DBA tools and suggestions to help tune databases, to fix queries with indexes, and many times they were of questionable merit. We have all seen the dreaded dta_index names in a database and rolled our eyes knowing that sometimes these indexes are not what is needed. While the Database Tuning Advisor, has gotten better over time it is still not a tool more senior DBAs use. Microsoft made big steps in helping DBAs with performance with the introduction of Query Store in both Azure SQL Database and SQL Server 2016. Query Store collects execution and run-time performance information. Microsoft has taken an additional leap with…
-
-
Microsoft gives you two purchasing models for your Azure SQL Databases (SQL DB), a DTU and a vCore based model (logical CPU’s). Both options allow you to scale up or down based on your compute and storage requirements. Regardless of which model you choose performance tuning can save you money using SQL DB. DTU (s) (Database Transaction Units) measure “a blended measure of CPU, memory, and data I/O and transaction log I/O” that Microsoft uses to help estimate which DTU based Service Tier you should be using. By reducing your I/O, memory or CPU consumption you utilize less DTUs or cores and thus can step down service tiers. Stepping down service…
-
Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database–theoretically this makes a database much easier to move between servers (you’ll note the absence of SQL Agent jobs from this post, that’s a difference problem.). One of the biggest benefit is that it allows database level authentication, so there no need to have user logins on server level. Contained database now enables us to make a database more portable. I can backup and restore to any instance of…
-
A few weeks ago, I wrote Part 1, 2 and 3 of this series, which was a beginner’s guide to Always Encrypted. In part 4, I am going to talk about the changes that are coming in vNext of SQL Server. As I discussed in part 3 there are many roads blocks the can stop the implementation of Always Encrypted (AE). In the current available versions of SQL Server 2016 and 2017, along with Azure SQL Database, the cost of using AE was way too high for many companies. There are so many code changes needed to implement AE that moving to it is not cost effective for them. Microsoft recognizes this and has…
-
Following up on my last post about the Cardinality Estimator let’s talk about column statistics and how they work and play a part in execution plans. The cardinality estimator relies heavily on statistics to get the answer to selectivity (the ratio of distinct values to the total number of values) questions and calculate a cost estimate. This hopefully gives us the best possible execution plans for queries. In this post, I will show you where to find information about what your statistics contain and information regarding each of those fields. Then we will look at the impact of over and under estimations caused by stale or missing statistics (or even data…
-
Sometimes people speak “SQL” and expect you know all the terminology. In recent a conversation about query plans I was ranting on and on about how the predicates were written when the person stopped me and asked a very simple question. Like a light bulb going off, it reminded me that not everyone knows what the other person is talking about when terminology is mentioned without clarification. So, this post will answer that persons very valid simple question. What is a SQL Predicate? Predicates are expressions that evaluate to TRUE, FALSE, UNKNOWN. There are two types of predicates Filtered Predicates and Join Predicates. Filtered Predicates cover your WHERE or HAVING…
-
In SQL Server the query optimizer uses a cardinality estimator to answer data SELECTIVITY questions like the ones below. The optimizer uses this cardinality data, which relies heavy on statistics to get the answers and calculate a cost estimate. It takes the #of Rows to Satisfy a Predicate/Total # of Input Rows. How many rows will satisfy a single filter predicate? Multiple? WHERE How many rows will satisfy a join predicate between two tables? JOIN How many distinct values do we expect from a specific column? DISTINCT GROUP BY From this estimate the optimizer is able to find an efficient execution plan that fulfills a query request. You will note that…
-
When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have negative impact to some queries. Microsoft actively avoids making breaking changes to its software. To avoid any negative performance impacts, Microsoft has hidden optimizer fixes behind a trace flag, giving admins the option to enable…
-
A few weeks ago, I wrote Part 1 and 2 of this series, which was a beginner’s guide to Always Encrypted. In part 3, I am going to go over what road blocks I ran into when trying to implement this solution for a client. The goal of the project was to prevent the DBA from being able to view salary information, while still allowing the application to function without issue. We were able to encrypt the data easily, but the entire process was not without issues. We had to remove default constraint values to get it to implemented. Always Encrypted does not currently support constraints. Fortunately, the defaulted zero…
-
In this post we will pick up where we left off in Part 1, if you haven’t read that please go back and do so. Now that we have encrypted our columns, it’s time to take a look at how we decrypt them inside SQL Server Management Studio or through our applications. You’ll be surprised to see how easy it is. Verify Your Setup First, let’s verify that the table is still encrypted, and nothing changed after you ran through the Part 1 examples. To confirm, simply query sys.columns, script out the table, or query the data to check that the Birthdate column is still encrypted. USE [AdventureWorks2016CTP3] GO CREATE…
