Performance Tuning

In this tip we look at how to force SQL Server to use a parallel execution plan using two different methods.

Check out the SQL Server performance comparison of the TOP vs ROW_NUMBER operators with various numbers of rows.

Read this tip to learn how to use R to automatically generate Windows performance counter graphs using T-SQL and R.

In this tip we look at different ways to flush the SQL Server cache.

Learn how to improve SQL Server table variable performance using Trace Flag 2453.

Microsoft SQL Server 2016 recently launched SQL Server 2016 SP1 and in this tip we will explore the enhancements to DBCC CLONEDATABASE.

I am updating my table statistics nightly, but I am still seeing occasional poor performance with some of the queries being executed against my database. We will look at whether statistics could still be an issue.

In this tip learn how to find out what trace flags were enabled for a SQL Server query by looking at the execution plan.

SQL Server has many features that assist with providing the best query performance and one of these features is read ahead reads or prefetching. In this tip we look at how this relates to SQL Server query performance and the impact to the query.

Read this tip to learn how to use SQL Server query plans to view cached plan parameters and possible parameter sniffing issues.

A rarely known feature of SQL Server Enterprise Edition is the Advanced Scan (aka Merry-Go-Round Scan). In this tip we will discuss the Advance Scan feature and demonstrate its characteristics.

Microsoft has recently released SQL server 2014 SP2 in which a new DBCC command - DBCC CLONEDATABASE is introduced. I found this command very useful and interesting too. In this tip we will explore the functionality of DBCC CLONEDATABASE.

Learn how to use the Logman tool to collection performance monitor counters to help troubleshoot performance issues for your SQL Server instances.

During your work as SQL Server DBA, you might need to create a copy of a production database to simulate testing scenarios on the production schema and statistics. Read this tip to learn about how to use DBCC CLONEDATABASE to make a database copy with jus

Read this tip to learn about sargability and how to improve SQL Server query performance.

As part of a performance optimization project, I changed multiple indexes and wanted to assess performance improvement from these changes. In this tip I'll show how you can build a custom performance collection tool to measure improvements.

Read this tip to learn how to build a centralized SQL Server performance collection system.

You are tuning a SQL Server query and you think that it will do better if you disable an optimizer rule. In this tip I show how to use the undocumented QUERYRULEOFF hint to help tune a query.

I have a SQL Server stored procedure that sometimes completes in under a second and other times takes over 15 seconds. In this tip we look at whether parameter sniffing is the issue.

Understand Memory Grants and Parallelism in SQL Server to optimize performance and manage resource contention effectively.

In this tip I will show you how you can use transaction log related wait types to perform a diagnosis for SQL Server transaction log issues.

At some point you are going to experience issues with database performance. You need to have a process to follow and certain tasks that you should perform. In this tip I provide steps to help you build a standard troubleshooting guide.

Hash indexes, used for SQL Server in-memory OLTP, are not ordered by the index keys and scans on hash indexes can significantly hinder performance. In this tip we will look at how to find these issues and also walk through an example.