Troubleshoot Performance Problems

Congratulations to Ken Selvia, a Programmer Analyst for Enterprise Products Company in Houston, Texas and Shane Dubble, a DBA for Innoviant, Inc. in Wausau, Wisconsin. Ken won first prize of $100 for the best solution to the November Reader Challenge, "Grouping Sequential Changes." Shane won second prize of $50. Here’s a recap of the problem and the solution to the November Reader Challenge.

Problem:

Steve is a systems analyst in an IT department for a midsized company. As part of a batch process, data is extracted from his production database, which logs activity into a table at periodic intervals. Each activity entry contains a flag that indicates a particular state. The batch process performs two operations in an infinite loop. As part of the logging, the process includes the time when the operation was started and a flag indicating the state. A sample schema with data is shown below.

CREATE TABLE Activity ( CreateTime smalldatetime 
  not null, Flag bit not null );
INSERT INTO Activity values( '2005-10-01 06:00', 0);
INSERT INTO Activity values( '2005-10-01 06:01', 0);
INSERT INTO Activity values( '2005-10-01 06:02', 0);
INSERT INTO Activity values( '2005-10-01 06:03', 1);
INSERT INTO Activity values( '2005-10-01 06:04', 1);
INSERT INTO Activity values( '2005-10-01 06:05', 0);
INSERT INTO Activity values( '2005-10-01 06:06', 0);
INSERT INTO Activity values( '2005-10-01 06:07', 1);
INSERT INTO Activity values( '2005-10-01 06:08', 0);
INSERT INTO Activity values( '2005-10-01 06:09', 1);
SELECT * FROM Activity;

In our example, let us assume that the Flag column is a bit data type and the values toggle from 0 to 1 and vice versa. The CreateTime column is a smalldatetime value that’s always increasing. Steve is trying to do the following:

Group changes in the Flag column in the sequence in which they occur based on the CreateTime value
Determine the start and end values of CreateTime for each group

The expected output of the query should be:

StartTime	EndTime	Flag
-------------------	-------------------	------
2005-10-01 06:00:00	2005-10-01 06:02:00	0
2005-10-01 06:03:00	2005-10-01 06:04:00	1
2005-10-01 06:05:00	2005-10-01 06:06:00	0
2005-10-01 06:07:00	2005-10-01 06:07:00	1
2005-10-01 06:08:00	2005-10-01 06:08:00	0
2005-10-01 06:09:00	2005-10-01 06:09:00	1

Help Steve write a query that will get the expected results showing the changes in the Flag column value in the order in which they occurred and the start and end times when the changes occurred.

Solution:

Steve can use the fact that the "CreateTime" value is increasing in nature to find the next "CreateTime" value for each row that has a different "Flag" value. For example, given the row where "CreateTime" equals '2005-10-01 06:00', the query to get retrieve the next "CreateTime" value where the "Flag" value equals 1 is below:

select min(t2.CreateTime)
  from Activity as t2
  where t2.CreateTime > '2005-10-01 06:00'
  and t2.Flag = 1

Similarly, the query below can be used to determine the next greater " CreateTime" value for the row where "CreateTime" equals '2005-10-01 06:03' and the flag Flag value is 0.

select min(t2.CreateTime)
  from Activity as t2
  where t2.CreateTime > '2005-10-01 06:03'
  and t2.Flag = 0

Steve’s query can now be similarily constructed with all rows in the table. The SELECT statement that incorporates the query as a correlated sub-query is shown below.

select min(t.CreateTime) as StartTime, max(t.CreateTime) as EndTime, t.Flag
  from (
      select t1.CreateTime, t1.Flag,
          isnull((select min(t2.CreateTime)
                          from Activity as t2
                    where t2.CreateTime > t1.CreateTime
                          and t2.Flag = case t1.Flag
                       when 0 then 1
                       when 1 then 0
                    end), t1.CreateTime) as GroupTime
      from Activity as t1
  ) as t
group by t.GroupTime, t.Flag
order by t.GroupTime;

The ISNULL check in the query takes care of the last row in the table or the row with the maximum "CreateTime" value.

DECEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the December Reader Challenge, "Troubleshoot Performance Problems" (below). Submit your solution in an email message to [email protected] by November 17. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Kevin is a database administrator who manages several data warehouses in his organization. All the data warehouses are stored in SQL Server 2000 with service Service pack Pack 3 (SP3) or SP4. Each data warehouse consists of a reporting database and a historical data store. There are several applications that connect to the reporting database to perform ad-hoc queries or operations. Kevin encounters blocking or performance issues periodically, and he wants to be able to troubleshoot these problems more efficiently. Help Kevin do the following:

Identify the executing Server Process ID spid(SPID), blocked status, wait type, wait resource, CPU, IO, and memory counters in a particular database.
Identify the current executing statement with the appropriate stored procedure or function name, if present.
Develop a simple query that Kevin can use to retrieve these details.

Comments

Plain text