Finding Queries That Kill Your System

The following is a short article on how to locate poor queries on a JD Edwards/PeopleSoft/Siebel system. I will be following it up with a more comprehensive version taking into account debugging batch jobs and more in depth techniques such as using Excel to drill into issues. But for the moment, I wanted to get this something short and simple posted ASAP.

Introduction

The most difficult issue in finding performance issues usually revolves around just defining what the problem is in the first place. This article will focus on how to use Profiler to capture the necessary data and comb through it to define where the issues are and then using the Database Tuning Advisor (DTA) to solve them.

There are two ways to find long running/resource intensive queries on a system. The first is to use dynamic management views (DMVs) provided in SQL Server 2005, the second is to use Profiler.

In SQL Server 2005, there are a variety of new DMVs that provide insight into how/why your system is running slow. The main drawback to them are the fact that they provide a "macro" view of the system and can lack the ability to pinpoint exactly where issues are at. For example, a DMV cannot be initialized at the beginning of a job to "reset" the counters for a baseline or that the data collected in the DMV may reflect changes since yesterday or a week ago depending on the available memory to store the information.

While DMVs are great tools, they are still not a substitute for SQL Server Profiler. Profiler allows you to capture data over a user defined period of time to nail down a specific job or user. This flexibility lets the DBA get a more granular picture of the situation or, if the trace is run for a day, a macro view of your entire system.

This paper offers a simple step by step method for prioritizing and attacking problems.

Please look for this paper to be posted by February 9, 2008 at www.computationpress.com