Hands-On-Lab: SQL Server Performance Tuning using Trace Flags

Hello Friends,

Here is another popular lab from Hands-On-Lab library: SQL Server Performance Tuning using Trace Flags.

Installation of SQL Server without any change comes with various default settings. Sometimes due to the variety of data or schema of user created objects, the default settings not worked properly. This starts impacting the performance of the workload or statement(s). To overcome from such kind of problems, Microsoft has been provided a list of trace flags. These trace flags are very useful in various situations. Trace flag activates an extra section of statement(s) to make the change in the default behavior of the SQL Server. In this lab, we will look into different well known problems and their solutions using trace flags. In the first exercise, we will learn about performance improvement using trace flag for the queries using table variables. In the second exercise, we will learn the solution of a very well-known problem – Parameter Sniffing using trace flag. In the third exercise, we will learn performance tuning using trace flag to make the change in threshold of auto update statistics.

Exercise 1: Correct Cardinality Estimation for Table Variables using Trace Flag 2453
Exercise 2: Disable parameter sniffing using trace flag 4136
Exercise 3: Changes to auto update threshold using trace flag 2371

SQLMaestros Hands-On-Labs enables the practical way of learning. Each lab document consists of multiple exercises and each exercise consists of multiple tasks. Apart from step-by-step instructions, the HOL document contains images, explanations, observations, notes, tips & tricks to ensure a great learning experience. With each lab, you can practice a specific concept step-by-step. With our HOL documents, you will get hands-on with hundreds of topics in SQL Server & Microsoft Data Platform. Learn More.