Troubleshooting is an important step in every database platform because RDBMS applications are self-tuned to some extent. Over a period of time, the data will be inserted and manipulated, then the performance will be degraded causing a slow response and sometimes unavailability of data. In such cases, we must ensure to build up processes and procedures to monitor SQL Server performance that can help optimize the required database objects, which lead to troubleshooting practices implementation.

In this chapter, we will go through highly-essential and most-required techniques using SQL Server 2008 and specific SQL Server 2008 R2 features. The troubleshooting will begin with monitoring methods and various available features such as filtered indexes, table hints, query hints, and perform parallel query processing methods by taking advantage of hardware and system resources.