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
Reviews
There are no reviews yet.