Finding the top offending statements for a specific workload can be hard if you are not using the best tool in your toolbox. On SQL Server 2012 and going forward you are able to leverage extended events which are included inside of SQL Server Manager Studio (SSMS) to build your first extended event to capture your workload.
The secret sauce to the Extended Event tools build into SQL Server Management Studio (SSMS) are the features you can use once your workload is captured. You can group your top offenders by the statements unique identifier also known as the query hash. Once your captured data is grouped by the unique identifier (query hash) you can also aggregate the captured workload so you can find your top offender statements in your workload by duration, writes, reads or CPU. This is all done without writing a single line of code.
In this five minute video you will learn how to build an extended event to find the top offenders for your workload and you will learn how to group and aggregate your workload to find your workloads top offenders.