Performance slowdown is a common occurrence in any RDBMS platform and SQL Server is not an exception. The root cause can range from inadequate database design that is, improper configuration, to handling the application workload. The DBA must be able to identify the root cause in a pro-active manner to minimize the problem. Also, the essential task is to monitor when a simple query execution causes intermittent issues and take corrective measures to fix the problem. The Windows operating system and SQL Server have a flurry of tools and utilities to monitor the system activity and collect the required data, such as PERFMON (SYSMON), SQL Server Profiler, DBCC statements, Dynamic Management Views (DMVs), and Data Collector. From SQL Server 2008 version onwards, another diagnostic tool is added, which is SQL Server Extended Events (XEvents).
In this recipe, we will go through the methods on how to manage events captured in SQL Server troubleshooting...