•  Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.

Once you know all the tools that you have – here is a real life scenario.

How do you approach or troubleshoot performance problems in SQL Server?

There are different ways of troubleshooting performance issues and there is no right or wrong way of troubleshooting performance issues, it is just my way of approaching in identifying the problem and then taking corrective actions.

•  First try to understand more details about the performance issues. Normally performance problems are reported by Developers or Client as per feedback from end users or some monitoring tools reporting specific performance issue on the server.
• Ask Developers or Client questions to understand what they mean by performance problems and gather mode details to pinpoint issue to a specific application or a specific functionality of the application.
•  Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query.
•  Check the overall hardware resource usage like, CPU usage on the server, Memory usage on the server, I/O usage on the server and Network usage to see if there are any spikes in any of their usage. If yes, then drill further down in that direction, if everything looks normal, then will proceed with checking at SQL Server level.