Advanced SQL Server Performance Tuning

The deeper you delve into the internals of SQL Server the more nuances arise. All of my recommendations should be tested in each environment.

For example, many senior level SQL Server DBA add trace flags to their default builds. However, if you don't understand how the flag works or what the secondary effects are then please don't add it.

A little about me. It's the same canned BIO for all my courses so If you've read it once you can skip it here. I do highly recommend you join the LinkedIN blog. I blog there regularly and interact with some of the most skilled SQL resources on the planet.

This is one of the most used approaches to performance tuning. If showplan analysis does't give us what we are looking for then the reduction in logical reads often will. Keep in mind, you are looking for wider ranges here. If you have approach that has 50 logical reads and another one with 65 then that's not much of a difference. If you cut the logical reads in half.. that's a big difference.

This isn't a course on query plan analysis but we will look at a few. I recommend using this free tool for viewing showplans. It's superior to the native one.

This is Microsoft's learning database. It's available on codeplex and is used in many tutorials and demonstrations. We need to download it and attach or restore it to our instance we will be learning on.

This is where you download the course content. Some of the content has not been included because of it's simplicity.

NOTE: Please keep in mind simple queries and code is not included in downloads. Typing those out will help you cement them in memory.

All roads for most of our transactions in SQL Server lead to and from the buffer pool. Understanding the basics is paramount to understanding performance tuning concepts.

SQL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache

Your plan cache stores details about all the SQL statements that have been executed over time.Let's take a look at the columns we need to be familiar with for analyzing it.

Using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans.

NOTE: Many of these examples have very simple stored procedures or DBCCs. I haven't included these in the content download because of their simplicity.

Let's walk through what parameter sniffing is and why it's a problem.

Problems arise when the values queried in a table are not evenly distributed. If one parameter value returns 10 rows and another parameter value returns 10,000,000 rows, it would be best if SQL Server used different execution plans for the different values of the parameter. So... how do we fix this issue.

Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

Let's create some simple plan guides. Plan guides can be created in SSMS or via transact.

Let's review what we've covered.

If you want to compare the effectiveness of an index you recently added then this trick will give you a side by side comparison. You can screen print it to show index effectiveness.

A filtered index is an often unused tool that can have vast improvements on performance.

Sparse columns work well where a high proportion of the data in a column is NULL, because negligible storage space is used for the NULL representation. A table that is wide in theory can be made narrow in practice by implementing sparse columns, and the addition of filtered indexes provides effective searches on the sparse data. Sparse columns support the use of check constraints, as long as the constraints allow NULL values (a sparse column requirement).

In this video we significantly reduce logical reads and save a lot of space on the location of the filtered index on disk.

Trace flag 3226 simply stops SQL Server from writing backup successful messages to the error log.

Does returning more columns in your queries really matter? Are you sure?

SELECT * versus SELECT Row1, Row2, Row3.

It matters alright but not for the reason you may think it does. Why using schema qualified names in your code is a great idea.

It one of the most used "best practices" when discussing transact SQL. Does it do what you think it does and should we use it?

PS - We should use it but why?

One long DELETE or UPDATE will hold many other transactions hostage. Batching up your deletes and updates is a great practice in jobs and in code.

SQL Server does a lot of things really well. Sorting is not one of them. Sort using the language you are authoring your application in.

Most applications are littered with Implicit Conversions. It should be this way. Simply matching the columns in the table with your code helps eliminate this.

I'm not really interested in a side by side performance comparison between temp tables, table variables and CTEs, but I'd like to take the stress off of tempdb. This is one really straightforward way to accomplish that.

If you know what it does then you'll probably enable it. Let's cover what this "knob" does and why we should turn it on.

I've added some comments from SQL Server experts who help reinforce it's usage.

Congratulation!! You've completed this Advanced Course On SQL Server Performance Tuning.