DB Admin Q22: How can I help identify the worst performing queries in my SQL Server database application?
In SQL Server 7.0 and SQL Server 2000, this is an easy task using the SQL Server Profiler. In the SQL Server 7.0 Profiler, you can use the “Create Trace Wizard” to choose the “Find the worst performing queries” profile trace. You can specify some number, in milliseconds, to determine which queries you want to view. For example, if you only want to identify those queries that take longer than 10 seconds to perform, then you can enter 10,000 milliseconds as the cutoff time, and only those queries that take longer than 10 seconds will be captured for your analysis. In SQL Server 2000, which doesn’t have a “Create Trace Wizard,” you need to create your own template using this configuration: Select These Event Classes SQL:BatchCompleted Select These Data Columns Groups Duration Columns EventClass TextData CPU Application Name LoginName NTUserName SPID Select These Filters (as needed) Application Name DatabaseName Duration The output of this Profiler trace is not as easy to read as the one p
