I am sure that everyone who reads this book had to deal with a situation where a stored procedure or query suddenly started to perform poorly. That means, performance was good in the past, and it was working regularly up to some point in time, but the same procedure or query does not perform well anymore: either you got a timeout when you executed it, or the execution time has been significantly increased. Usually you need to fix it as soon as possible, especially when this happens in an important application module and/or during non-working or peak hours.
How do you proceed with this? What is the first step you take when you start such troubleshooting? By gathering information such as system information, query stats and plans, execution parameters, and so on, right? When a query or stored procedure is slow, you want to see its execution plan. Therefore, the first thing is to check the execution plan in the server cache. You can use this query to return the execution plan...