Performance tuning the other day, I was stumped by a query plan I was looking at. Even though I had constructed a covering index, I was still getting a Key Lookup operator in my query plan. What I usually do when that happens is to check the operator’s properties to see what its output columns are, so I can include those columns in my covering index.
Here’s the interesting thing: there weren’t any output columns. What happened?
The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.
But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:
Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backup–restore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).
But when you try to attach the database, you might get something like
Msg 5120, Level 16, State 101, Line 3 Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf". Operating system error 5: "5(Access is denied.)".
The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.
A very common challenge in T-SQL development is filtering a result so it only shows the last row in each group (partition, in this context). Typically, you’ll see these types of queries for SCD 2 dimension tables, where you only want the most recent version for each dimension member. With the introduction of windowed functions in SQL Server, there are a number of ways to do this, and you’ll see that performance can vary considerably.
The SQL Server query optimizer can find interesting ways to tackle seemingly simple operations that can be hard to optimize. Consider the following query on a table with two indexes, one on (a), the other on (b):
SELECT a, b FROM #data WHERE a<=10 OR b<=10000;
The basic problem is that we would really want to use both indexes in a single query.
In this post, we’re going to take a look at a few examples of how this type of query would be optimized, as well as how statistics can affect the query plan, and finally, we’ll take a look at a slightly rare plan operator called “Merge Join (Concatenation)”.
Here’s a quick tip: When you’re evaluating query strategies, you may want to consider how your query will scale when the volume in your database goes up. This does not neccessarily mean that you have to start filling your tables with gigabytes on gigabytes of data.
You can move or copy a database from Enterprise Edition (or Developer Edition, which supports more or less the same feature set) to Standard Edition. The simplest way is to take a backup of the database and restore that on the new server. However, if there are any Enterprise Edition features left in the database, the restored database won’t start up, and you’ll get this error, or something similar:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore of database ‘databaseName’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended) ------------------------------ Database 'databaseName' cannot be started in this edition of SQL Server because part or all of object 'myTableName' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Database 'databaseName' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)
Few things deserve the attention of a long rant as much as unneccessarily complicated syntaxes. When you want to achieve something that is clearly defined and supported, but you have to look up the the syntax. PIVOT and UNPIVOT are examples of such features, and in this case, I’ll even show you a more well-performing alternative.
For windowed functions, SQL Server introduces two new operators in the execution plan; Segment and Sequence Project. If you’ve tried looking them up in the documentation, you’ll know that it’s not exactly perfectly obvious how they work. Here’s my stab at clarifying what they actually do.
In the two previous parts of this series, we’ve looked at how parallelism works, how you can control it, and how it affects your query (and server) performance in different environments. In this, the third part, we’re going to take a more technical look at how the different Parallelism operators work.