SQL Server Magazine

No successful entries were submitted for the December Reader Challenge, "Troubleshoot Performance Problems." Here’s a recap of the problem and the solution to the December Reader Challenge.

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 Pack 3 or 4. 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:

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

Solution:
Kevin can use the master.dbo.sysprocesses system table to get the executing spid, blocked status, wait columns and counters in a particular database. The query to obtain this information for all user connections to a particular database is shown below:

use northwind
go
select p.spid, p.blocked, p.waittype, p.waittime, p.lastwaittype, p.waitresource
     ,p.cmd, p.status, p.cpu, p.physical_io, p.memusage, p.login_time, p.last_batch,
p.program_name
  from master.dbo.sysprocesses as p
  where p.spid >= 51
   and p.dbid = db_id()
go

SQL Server 2000 with Service Pack 3 and above contains a system function fn_get_sql that can be used to retrieve the text of a particular SQL handle representing a cached plan. Additionally, the sysprocesses system table also contains the offsets to the currently executing statement in the text represented by the plan. The relevant columns in sysprocesses that provide this information are sql_handle, stmt_start and stmt_end. The stmt_start and stmt_end columns are zero-based, representing the offset to the statement in bytes. The text returned by fn_get_sql is a Unicode string but is returned as an ASCII string.

Kevin can now use this information to write a T-SQL user-defined function that retrieves the current executing statement using the fn_get_sql system function, given the SQL handle and statement offsets. The definition of the T-SQL user-defined function is shown below:

use northwind
go
if object_id('GetCurrentSqlStmt') is not null
    drop function GetCurrentSqlStmt
go
create function GetCurrentSqlStmt (@sql_handle binary(20), @stmt_start int, @stmt_end int)
returns varchar(8000)
as
begin
    return (
            select coalesce(quotename(object_name(s.objectid)) + ':', ')
                 + cast(substring(s.text, (@stmt_start/2) + 1
                               , (((case @stmt_end when -1 then datalength(s.text) else @stmt_end 
end) - @stmt_start)/2) + 1) as varchar(8000))
            from ::fn_get_sql(@sql_handle) as s
    )
end
go

Modifying the query on sysprocesses to include the T-SQL user-defined function GetCurrentSQLStmt, provides Kevin with the information necessary to troubleshoot the performance issue. The modified query is shown below:

use northwind
go
select p.spid, p.blocked, p.waittype, p.waittime, p.lastwaittype, p.waitresource
     , dbo.GetCurrentSqlStmt(sql_handle, stmt_start, stmt_end) as sql_text, p.cmd, p.status
     , p.cpu, p.physical_io, p.memusage, p.login_time, p.last_batch, p.program_name
  from master.dbo.sysprocesses as p
 where p.spid >= 51
   and p.dbid = db_id()
go

An example of a stored procedure used to simulate a long-running operation is shown below. Run the stored procedure from a Query Analyzer window, and use the query above to see the current executing statement.

use northwind
go
if object_id('LongRunningProc') is not null
    drop procedure LongRunningProc
go
create procedure LongRunningProc
as
begin
    declare @start datetime, @count int, @wait_time char(8)
    set @start = current_timestamp
    while(datediff(minute, @start, current_timestamp) <= 1)
    begin
        select o.CustomerID, o.ShipCity, o.ShipRegion, o.ShipCountry, p.ProductName, 
s.CompanyName, p.CategoryID, sum(od.Quantity) as TotalQuantity
          into #o
          from Orders as o
          join [Order Details] as od
            on od.OrderID = o.OrderID
          join Products as p
            on p.ProductID = od.ProductID
          join Suppliers as s
            on s.SupplierID = p.SupplierID
      group by o.CustomerID, o.ShipCity, o.ShipRegion, o.ShipCountry, p.ProductName, 
s.CompanyName, p.CategoryID
     with cube
        
        set @wait_time = '00:00:00.' + cast(cast(rand()*500 as int) + 1 as char(1))
        waitfor delay @wait_time
        
        drop table #o
    end
end
go
exec LongRunningProc
go

JANUARY READER CHALLENGE:
Now, test your SQL Server savvy in the January Reader Challenge, "Deploying a Startup Parameter on All Servers"(below). Submit your solution in an email message to challenge@sqlmag.com by December 20. 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:
Charlie is a database administrator who manages a combination of more than 50 installations of SQL Server 7.0 and SQL Server 2000. He wants to add a new trace flag as a startup parameter on all the servers to generate a report in the event of a SQL Server deadlock. For this problem, assume that the trace flag that generates this deadlock report is 1204. How can Charlie quickly deploy this new startup parameter to all the servers?

End of Article