Optimizing and Troubleshooting


The key task of a database administrator is to guarantee problem-free use of the database system. This includes, apart from ensuring server availability and stored data security, ensuring adequate performance of the whole system. Should the performance decrease below a given level, the database shall be actually unavailable for the users. Even though the administrator is hardly ever in the position to change the code (whether on the client application’s side or on the side of database objects), they can do more than just improve the performance by the appropriate configuration of SQL Server and database, creating adequate indexes or changing the physical table structure. They can also identify and document the reasons for its poor performance. The report they generate may be used to justify the need to expand the equipment, but primarily to negotiate the removal of detected errors with the software producer. The administrator should identify potential problem issues in advance and troubleshoot them, before their results become visible for or affect the users. Such pro-active approach involves monitoring the size of database files, the use made of system resources, the number of active users and key queries execution time. Our course prepares SQL Server administrators for pro-active monitoring and troubleshooting, with particular emphasis on performance issues. While putting together this specialized course, we had in mind not only our many years’ experience, but also our customers’ and course participants’ comments and real-life problems they report. In effect, the course gives you the knowledge and skills you need to troubleshoot typical problems effectively, as well as to detect and remove “bottlenecks”, or, in other words, the reasons for suboptimal SQL Server performance


  • Course level: 400
  • Start date: as required
  • Duration time: 4 days

Price: 4100 zł

  • Open training
  • On-site (costumer's premises)
  • Customer’s computer


The course has been designed for database administrators and all those who want to learn the principles of SQL Server and the techniques and tools for monitoring server performance, and those whose task is to diagnose the work of a database system or improve its performance. The participants will acquire the practical skills crucial for documenting the functioning of SQL Server, the analysis of real reasons behind the occurring errors and unoptimal server performance, as well as tried and tested techniques of troubleshooting these issues. The course is available in 3 versions, devoted to SQL Server 2005, 2008 and 2008 R2 and 2012 respectively.


The basic training is comprised of 11 modules. Just like all the other courses on our offer, it has been designed by our experts, therefore we can freely customize it to suit the individual needs of the participants. We would like to encourage you not only to choose modules which you find relevant for your needs, but also to feel free to contact us with your suggestions and questions you would like to hear answered throughout the course. Our trainings are unique also because we limit the time set aside for laboratory type classes customarily conducted after the completion of each module, to the benefit of exercises done with the assistance of the trainer and demonstrations. We believe in this manner we can equip you with substantially more practical information and tips, and focus on dealing with the issues directly within your scope of interest. However, each day of the training is finished with a laboratory class of approximately 1hr, so that you can do self-practice and do exercises of your choice.


Duration: Level:
Module 1

SQL Server Internals
  • SQL Server Architecture
  • Managing Requests
  • Memory Management
  • SMP vs NUMA Architecture
  • Monitoring SQL Server using DMV’s
  • Advantages of Enterprise Edition

240 minutes 400
Module 2

Transaction Log
  • Database Files Overwiew
  • Transaction Log Architecture
  • Transaction Log Content Analysis
  • Log Truncation
  • Write-Ahead Logging Protocol
  • Recovery Process
  • Database Recovery Modes
  • Minimally Logged Operations

90 minutes 400
Module 3

Server and Operating System Configuration
  • Basic Operating System Configuration
  • Load Estimation
  • Choosing Appropriate Hardware Configuration
  • Memory Settings
  • Processor Performance Evaluation
  • Input-Output Subsystem Performance Evaluation
  • Partition Alignment and Formatting

90 minutes 400
Module 4

SQL Server In Virtual Environment
  • Virtual Machines Configuration
  • Diagnosing Performance Problems
  • Memory Management
  • Input-Output System Management
  • Performance Overhead

60 minutes 300
Module 5

SQL Server Configuration
  • Installations: Tips & Tricks
  • Recommendations Concerning SQL Server Configuration
  • Tempdb
  • Recommendations Concerning Tempdb (Including File Numbers)
  • Managing SQL Server

90 minutes 400
Module 6

Databases and Data Storage Configuration
  • System Databases
  • Documenting Database Configuration
  • Recommendations Concerning Database Configuration
  • Moving Databases
  • Files and Filegroups
  • Moving Data Between Files
  • Managing Filestream Data and FileTables
  • Database Snapshots

120 minutes 400
Module 7

Data Structures and Data Types
  • Data Types
  • Storing Data In- Row (Pages and Extents)
  • Changing Table Structure
  • Sparse Attribute
  • Partitioning
  • Data Compression

90 minutes 400
Module 8

  • Tools for Index Structure Analysis and Index Functioning
  • Advantages and Disadvantages of Storing Data in Heaps
  • The Structure of B-Trees
  • Recommendations for Clustered Index Keys
  • The Ways in Which SQL Server Reads and Modifies Indexes
  • Indexes on Computed Columns and Filtered Indexes
  • Effective Index Management Strategy
  • Detecting and Creating Missing Indexes
  • Methods of Detecting and Automatic Prevention of Logical and Physical Index Fragmentation
  • Columnstore Indexes
  • iFTS

240 minutes 400
Module 9

Performace Monitoring
  • Performance Evaluation
  • Old SQL Server Tools
  • Additional Tools
  • SQL Server Wait Types
  • Identifying Bottlenecks
  • SQL Server 2012 Tools

240 minutes 400
Module 10

Managing Server Resources
  • Database Ongoing Maintenance
  • Resource Governor
  • Controlling the Work of Query Optimizer
  • Plan Caching and Recompilation

120 minutes 400
Module 11

Concurrency Control
  • Transactional Processing
  • Optimistic and Pessimistic Concurrency Models
  • Lock Monitoring
  • Identifying Deadlocks
  • Row Versioning
  • Preventing Locks by Switching the Base to a Versioning Mode

60 minutes 400


Ask Question