Congratulations to Corey Tracey, senior developer for SHIPER Project at Sutter Health in California, and Emmanuel Nanchen, IT analyst and programmer at Manpower HR SA in Geneva, Switzerland. Corey won first prize of $100 for the best solution to the December Reader Challenge, "Stalking the Statements." Emmanuel won second prize of $50. Here’s a recap of the problem and the solution to the December Reader Challenge.

Problem


Here’s the challenge: Ryan is the DBA for several SQL Server 2000 installations. Lately he’s noticed that queries and statements coming in from his company’s Web servers to in-house SQL Server instances are executing more slowly. He wants to devise an automated method of flagging the worst-performing statements and queries without user intervention so that he can tune the queries. Help Ryan take the following actions:

  • Set up a process to identify statements and queries that take longer than 1 minute to execute
  • Automate the scheduling of this process in a way that avoids SQL Server restarts.

Solution


Ryan can use SQL Server Profiler to run a trace that flags the worst-performing statements and queries. Profiler will help him define the events he needs to trace, the data he wants to capture, and the filter he needs to apply. Ryan can insert the trace definition into a SQL script that SQL Server can execute conveniently with few modifications on any SQL Server 2000 machine. Then, to automate the trace process, he can define the trace by using the following trace properties in Profiler:

Property Value
Trace Name sp_BadStmts
Save to File UNC path for trace file location and Server processes
Events RPC: Completed, SP:Completed, and TSQL-SQL:StmtCompleted
Data Column EventClass, TestData, Duration, SPID, LoginName, HostName
Filters Duration> 6000

Next, Ryan can script out the trace definition to a file called sp_BadStmts.sql, which he can use to create the trace on any SQL Server installation and to modify additional parameters. To automate scheduling of the trace process, Ryan can first modify the sp_BadStmts.sql script to enclose a batch of statements in a CREATE PROCEDURE statement, part of which follows:

USE master
GO
IF object_id( 'sp_badstmts' ) IS NOT NULL
   DROP PROCEDURE sp_badstmts
GO
CREATE PROCEDURE sp_badstmts
AS
BEGIN

/****************************************************/
/* The following statements contain the SQL Server Profiler-generated */
/* script to create the trace with the required events and data columns. */
/****************************************************/
-- Create a queue.
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
SET @maxfilesize = 5
EXEC @rc = sp_trace_create @TraceID output, 2,
N'\\valhalla-mddo7h\c$\Temp\BadStmts.
trc', @maxfilesize, NULL
IF (@rc != 0) GOTO error
.
.
.
-- Display trace ID for future reference.
SELECT TraceID=@TraceID
GOTO finish

error:
SELECT ErrorCode=@rc

finish:
END
GO

Then, he can modify the trace file path to include the SQL Server name and mark the stored procedure for startup by using the sp_procoption system stored procedure.

By marking the stored procedure for startup and enabling the server configuration to "scan for startup procs", Ryan instructs SQL Server to automatically check for stored procedures marked for startup and to execute the stored procedures when the service starts. The code to configure these options follows:

-- Mark stored procedure to start automatically when service starts.
EXEC sp_procoption 'sp_badstmts', 'startup', 'true'
GO

-- Enable search for startup stored procedures at server level.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH override
GO
EXEC sp_configure 'scan for startup procs', 1
RECONFIGURE WITH OVERRIDE
GO

The following is the complete script that uses Profiler-generated code
to automate the trace scheduling:

USE master
GO
IF object_id( 'sp_badstmts' ) IS NOT NULL
   DROP PROCEDRE sp_badstmts
GO
CREATE PROCEDURE sp_badstmts
AS
BEGIN
/****************************************************/
/* The following statements contain the SQL Server Profiler-generated */
/* script to create the trace with the required events and data columns. */
/****************************************************/
-- Create a queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
SET @maxfilesize = 5
EXEC @rc = sp_trace_create @TraceID output, 2,
N'\\valhalla-mddo7h\c$\Temp\BadStmts.trc',
@maxfilesize, NULL
@maxfilesize, NULL
IF(@rc != 0) GOTO error

-- You can't script the client-side file and table.
-- Set the events.
DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 8, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 8, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 43, 1, @on
EXEC sp_trace_setevent @TraceID, 43, 8, @on
EXEC sp_trace_setevent @TraceID, 43, 11, @on
EXEC sp_trace_setevent @TraceID, 43, 12, @on
EXEC sp_trace_setevent @TraceID, 43, 13, @on

-- Set the filters.
DECLARE @intfilter int
DECLARE @bigintfilter bigint

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
SET @bigintfilter = 60000
EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

SET @intfilter = 100
EXEC sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

-- Set the trace status to start.
EXEC sp_trace_setstatus @TraceID, 1

GOTO finish

error:
SELECT ErrorCode=@rc

finish:
END
GO

-- Mark stored procedure to start automatically when service starts.
EXEC sp_procoption 'sp_badstmts', 'startup', 'true'
GO

-- Enable advanced options, then enable search for startup stored
procedures at server level.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH override
GO

EXEC sp_configure 'scan for startup procs', 1
RECONFIGURE WITH OVERRIDE
GO

JANUARY READER CHALLENGE:


Now, test your SQL Server savvy in the January Reader Challenge, "Corrupted Characters" (below). Submit your solution in an email message to challenge@sqlmag.com by December 19. SQL Server MVP 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.

Cindy is the systems architect for a company that specializes in data warehousing technology. She needs to automate the execution of several T-SQL script files that are in Unicode format. These scripts populate tables in the warehouse. For example, Cindy needs to populate a Country dimension table with data based on the ISO 3166 codes and localization information for reporting purposes. The following shows a sample script file:

CREATE TABLE #t ( c VARCHAR(30) NOT NULL )
INSERT INTO #t values( 'Tëst' )
SELECT c FROM #t
DROP TABLE #t

Cindy is automating the execution of the scripts against several SQL Server 2000 databases by using the OSQL command-line utility. But when she uses OSQL she notices that the extended characters in the scripts are corrupted upon import. Extended characters, such as the German umlaut (ä), aren’t in the standard ASCII character set. Help Cindy identify the cause of the problem and provide her with an efficient solution for executing the scripts successfully, without any data corruption or loss.