Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor

Imagine that you have set up your production SQL server that is hosting several databases used by different applications. Initially your SQL server responds smoothly, but as the number of query requests increases, becoming larger day by day, and when it reaches several hundred per second, you notice that queries take a little longer to execute and your SQL server CPU usage is higher than what you expected.

As you may know, there can be a number of reasons for SQL server to respond poorly, such as:

• Your physical database design and database file placement is not optimized

• Databases are missing proper indexes

• Queries are not optimized and are poorly written

• Statistics are out-of-date, and query optimizer is not able to generate an optimum plan

• Queries face blocking issues

• You need to upgrade the CPU or increase the number of CPU cores

• The server does not have enough memory

• There is a problem with...