Sams Teach Yourself Transact-SQL in 21:Procedures and Performance

Handling Debugging Messages

One common method of debugging any software—and particularly software in which the debugging environment is so weak—is to print messages between steps. You can use these methods to report on progress or to track the performance of elements of your code, but you need to be aware of some wrinkles in printing status messages.

Try this (but don’t panic when nothing comes back instantly):

print   “Starting test now”
waitfor    delay  “00:00:15”
print   “Ending test  now”

Starting  test  now
Ending  test  now

If you watch this closely, you will notice that both the “starting” and the “ending” messages come out all at once when the query is finished. SQL Server builds a 512-byte network packet of return data for your query. When that packet is full, it sends the whole packet back to the client. When the query is finished, any remaining information is forwarded to your connection. That means that debugging messages will not help you observe the behavior of your procedure while it’s running.

Although you can increase the size of a network packet, you can’t affect how the server treats batched information. Try the same query again, but separate the statements with GO:

print  “Starting  test  now”
go
waitfor   delay   “00:00:15”
print  “Ending test   now”

This time, SQL Server returns the “starting” message immediately because the batch is complete.

Unfortunately, you can’t insert GO in a stored procedure, so all of your return messages will pile up on the server until the batch is complete.

Do......Don’t

DO start debugging in batch mode.

DO insert debugging messages in your code.

DO use two connections: one to write the connection and one to execute it.

DO test individual data modification statements one at a time.

DO fix the first problem first; then test again.

DO use comments to describe each important step in the code.

DON’T assume that queries run problem-free every time.

DON’T write a 50-page procedure and then test it! (Take small bites!)

Examining Procedure Performance

Most stored procedures are run repeatedly, often by many users at one time, all day long. That’s why their performance is so critical. In this section, we’ll look at methods of tracking, understanding, and improving procedure performance.

Tracking Times

If you have a procedure that is taking a long time to run, the first thing you need to do is isolate the steps and determine how much time each takes to run. To print the time from a batch, the easiest thing is to use the RAISERROR statement because it can take a string argument. Here’s a batch that returns a starting and ending time.

declare @time varchar(30)
select @time = convert(varchar(30), getdate(), 109)
raiserror (“starting now %s”, 0,1, @time )
select @time = convert(varchar(30), getdate(), 109)
raiserror (“ending now %s”, 0,1, @time )

starting now Jan  4 1997  8:58:02:926AM
ending now Jan  4 1997  8:58:02:936AM

The variable @time must be a character data type so that it can be used as an argument for the RAISERROR statement. The CONVERT() function forces the style of 109, which includes a 4-digit year and displays seconds and milliseconds.

The RAISERROR statement itself uses a severity of 0 to suppress the error component. This error message is treated exactly like a PRINT statement.

Remember that the output from these debugging statements will not appear until the batch is complete.

Table of Contents

Используются технологии uCoz