Stalking the Statements

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:

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:

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 masterGOIF object_id( 'sp_badstmts' ) IS NOT NULL   DROP PROCEDURE sp_badstmtsGOCREATE PROCEDURE sp_badstmtsASBEGIN/****************************************************//* 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 intDECLARE @TraceID intDECLARE @maxfilesize bigintSET @maxfilesize = 5EXEC @rc = sp_trace_create @TraceID output, 2, N'\valhalla-mddo7hc$TempBadStmts.trc', @maxfilesize, NULLIF (@rc != 0) GOTO error...-- Display trace ID for future reference.SELECT TraceID=@TraceIDGOTO finisherror:SELECT ErrorCode=@rcfinish:ENDGO

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', 1RECONFIGURE WITH overrideGOEXEC sp_configure 'scan for startup procs', 1RECONFIGURE WITH OVERRIDEGOThe following is the complete script that uses Profiler-generated codeto automate the trace scheduling:USE masterGOIF object_id( 'sp_badstmts' ) IS NOT NULL   DROP PROCEDRE sp_badstmtsGOCREATE PROCEDURE sp_badstmtsASBEGIN/****************************************************//* The following statements contain the SQL Server Profiler-generated *//* script to create the trace with the required events and data columns. *//****************************************************/-- Create a queueDECLARE @rc intDECLARE @TraceID intDECLARE @maxfilesize bigintSET @maxfilesize = 5EXEC @rc = sp_trace_create @TraceID output, 2,N'\valhalla-mddo7hc$TempBadStmts.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 bitSET @on = 1EXEC sp_trace_setevent @TraceID, 10, 1, @onEXEC sp_trace_setevent @TraceID, 10, 8, @onEXEC sp_trace_setevent @TraceID, 10, 11, @onEXEC sp_trace_setevent @TraceID, 10, 12, @onEXEC sp_trace_setevent @TraceID, 10, 13, @onEXEC sp_trace_setevent @TraceID, 12, 1, @onEXEC sp_trace_setevent @TraceID, 12, 8, @onEXEC sp_trace_setevent @TraceID, 12, 11, @onEXEC sp_trace_setevent @TraceID, 12, 12, @onEXEC sp_trace_setevent @TraceID, 12, 13, @onEXEC sp_trace_setevent @TraceID, 43, 1, @onEXEC sp_trace_setevent @TraceID, 43, 8, @onEXEC sp_trace_setevent @TraceID, 43, 11, @onEXEC sp_trace_setevent @TraceID, 43, 12, @onEXEC sp_trace_setevent @TraceID, 43, 13, @on-- Set the filters.DECLARE @intfilter intDECLARE @bigintfilter bigintEXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'SET @bigintfilter = 60000EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilterSET @intfilter = 100EXEC sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter-- Set the trace status to start.EXEC sp_trace_setstatus @TraceID, 1GOTO finisherror:SELECT ErrorCode=@rcfinish:ENDGO-- 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 storedprocedures at server level.EXEC sp_configure 'show advanced options', 1RECONFIGURE WITH overrideGOEXEC sp_configure 'scan for startup procs', 1RECONFIGURE WITH OVERRIDEGO

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 [email protected] 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 #tDROP 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.