Performance Tuning

In this tip we will look at some different options that should reduce SQL Server PAGELATCH_EX waits specifically as they relate to UPDATE statements.

In this article we look at how to interpret and use the results of STATISTICS IO when tuning SQL Server queries.

In this article we look at an example of a poorly performing SQL Server query and how this can be made to run faster using execution plans and missing index information.

In this article learn how to use statistics time and statistics IO settings to help you tune your SQL Server queries.

In this article we look at how to capture duration metrics for SQL Server code to determine which code variation runs the fastest.

SQL Server set statistics time statement displays number of milliseconds to parse compile and execute a T-SQL query statement.

Learn about how DMV sys.dm_exec_session_wait_stats demonstrates how delayed durability could be beneficial for certain SQL Server workloads.

In this tip we look at how to check for memory pressure in SQL Server and using trace flag 8032 to increase the plan cache size.

In this article we look at why a SQL Server batch process runs so much faster in TempDB versus running in a user database.

In this tip we look at how you can find potential SQL Server query bottlenecks without having to review execution plans.

In this tip we look at a simple stored procedure you can create to determine which data or log files on your SQL Server instance are experiencing latency issues.

In this tip we look at some scripts to help you identify currently running queries that using up SQL Server transaction log space.

Learn how to create a process dump for SQL Server using the SqlDumper.exe tool for SQL Server troubleshooting.

In this tip we look at doing updates for a large SQL Server table using a row by row approach, one big batch and several smaller batches to see the difference in time to do the updates.

In this tip we look at how we can use the SQL Server Database Engine Tuning Advisor to tune queries and improve performance

In this tip we look at a script that can be used to output SQL Server internal query cardinality estimator information for a query.

In this tip we take a close look at the use of table variables in SQL Server and the impact on performance.

In this tip we look at different ways to move SQL Server data from one table to another table as well as the results I saw with each approach.

In this tip we look at the new database option OPTIMIZE_FOR_AD_HOC_WORKLOADS and how this can impact the plan cache.

Learn about the good, the bad and the ugly for CXPACKET and CXCONSUMER wait types in SQL Server. See how SQL Server parallelism settings impact CXPACKET and CXCONSUMER wait type results. Check out the real world CXPACKET and CXCONSUMER wait types example

Learn about the SQL Server performance impact of querying unnecessary columns with two examples outlining the differences and impact to your application.

In this tip we look at using Delayed Durability to reduce the latency for transactions when there are WRITELOG waits.

With SQL Server 2016 SP1 comes a direct way to get wait statistics information about a specific query from the query execution plan.

In this tip we look at how to use SSMS and T-SQL to find the parameter values that were used to create the query plan.