Description

Analyze And Optimize Your SQL Server by Uwe Ricken

Abstract: The day-to-day business of a DBA as well as consultants who are on the move in terms of “”Microsoft SQL Server”” include careful analysis of SQL Server systems when the business or the customer reports performance problems. In the “”Analysis of SQL Server”” workshop, a specific example is used to test a Microsoft SQL Server for various problem cases that lead to bottlenecks if they are not set correctly.

In the workshop, the various problem analyzes are demonstrated by examples with explanations of the possible effects. At the same time, a solution plan is drawn up in which the participant can work through the correct settings in the operating system to the configuration settings in Microsoft SQL Server. Finally, solutions are shown if the performance within a single database is assessed as insufficient. All possible performance bottlenecks are simulated and evaluated on an installed Microsoft SQL Server using scripts. The following analysis steps are covered during the workshop:

1. Check and evaluate settings in the operating system
2. Analysis of the databases for configuration, usage and system utilization
3. Configuration and analysis of TEMPDB
4. Waits and Latches: What is Microsoft SQL Server waiting for and what influence do the various waiting processes have on the performance of the applications
5. Index maintenance – how are indexes used, which indexes are missing, how is the physical condition of indexes to be assessed

At the end of the evaluations, a template is presented in which the results of the investigation.

Modules:

1. Understanding the process route of Microsoft SQL Server
2. Important settings for the OS
3. Important settings for SQL Server
4. Analyse the database configuration
5. Analyze Wait Stats
6. Best practice configuration
7. Reporting of found issues