Valuable SQL Server Performance Tuning tips, tutorials, how-to’s, scripts, and more for SQL Server DBAs.
I have a SQL Server 2012 installed on Windows Server 2008 R2 Enterprise Edition. The power plan of the Windows Server Operating System (OS) is left at its default power plan of Balanced. Are there any benefits or performance gain in setting the power pla
As much as I like some of the graphical tools provided by Microsoft for SQL Server or the full blown applications provided by third party vendors that can be used to help you find and tune poor performing T-SQL code. There are some of us that prefer doing
You have a bad performing query. After extensive research you find a KB article that describes the issue as a Query Optimizer bug. You download and apply the hotfix, but nothing seems to happen. In this tip we look at the possible issues and how to solve
I think that my queries would benefit by being executed with multiple parallel threads. In some cases in the past I've used the MAXDOP option in my TSQL to reduce the level of parallelism, but is there a way that I can increase the number of threads that
You need to tune a query on a non-production server. To do this, you have exported database statistics and loaded them onto a development environment. But even doing this you can't get the same results as in production, mostly because of differences on ha
It is often generalized that seeks are better than scans in terms of retrieving data from SQL Server. The index hint FORCESCAN was recently introduced so that you could coerce the optimizer to perform a scan instead of a seek. Which might lead you to wond
Over the years I've had many discussions with other database administrators as to whether or not you should have the database buffer pool loaded with data pages or empty when testing queries for performance. This tip will take a look at the effects on que
You are tuning a query and need to check the performance impact of a new index. But the table to index has millions of rows and it will take a lot of time to create an index. Keep reading and you will see how to create and test hypothetical indexes with t
Even though it might mean storing a bit of redundant data, schema denormalization can sometimes provide better query performance.
Using the DELETE CASCADE option in your foreign key constraint definitions means better performance and less code when removing records from tables that have a parent-child relationship defined.
There are few reasons why it's recommended that most if not all tables have a clustered index defined.
The order that the columns are specified in your indexes has an effect on whether or not the entire index can be used when the SQL Optimizer parses your query.
There are many different scenarios when an index can help the performance of a query and ensuring that the columns that make up your JOIN predicate is an important one.
Using a derived table in place of the IN predicate when we are aggregating data allows us to only have to process certain table records once therefore reducing the amount of resources required to execute a query.
Depending on how wilcard characters are used there could be an issue using them and performance issues could be the result.
Be careful how you use functions in the WHERE clause otherwise there could be a performance price to pay.
In almost all cases when we use the <> operator index seeks will not be performed and instead a table or index scan is required.
Learn about the potential performance impact of using functions in the SELECT list.
The order in which the tables in your queries are joined can have a dramatic effect on how the query performs.
In each section of this tutorial we will take a look at specific examples that will illustrate things that should be avoided when it comes to performance in SQL Server.
You are tasked with examining poor performance for queries on a certain table, or range of tables. Perhaps a sales report is timing out or running unacceptably slow; perhaps your web application is failing to return the result set before the application t
You are seeing evidence of an excessive number of query compilations or recompilations in SQL Server. This may be manifesting itself as high CPU load, or longer transaction execution times. You need to identify which queries are causing this behavior and
I have heard about parameterization for SQL Server queries, but what is Forced and Simple Parameterization and which one should I use for my SQL Server database? In this tip we will take a look at these two options and what they mean.
Today, one of our SQL Server instances was performing very slowly. When I logged in to the database server to do some initial checks I noticed it was memory pressure from the initial observation. Next we had to find what out was causing our instance to ha