Tips from the SQL Server MVPs, September 2001

Editor’s Note: Welcome to SQL Server Magazine’s monthly, Web-only series of SQL Server tips brought to you by the Microsoft SQL Server Most Valuable Professionals (MVPs). Microsoft introduced the MVP program in the 1990s to recognize members of the general public who donate their time and considerable computing skills to help users in various Microsoft-hosted newsgroups. MVPs are nominated by Microsoft support engineers, team managers, and other MVPs who notice a participant's consistent and accurate technical answers in various electronic forums and other peer-to-peer venues. For more information about the MVP program, go to http://support.microsoft.com/support/mvp. The MVPs donate their SQL Server Magazine author fees for these tips to the World Food Programme. To donate free food to a hungry person today, visit thehungersite.org.

How can I programmatically detect whether a given connection is blocked?

A connection is blocked when it requires an object that another connection has a lock on. You can use the system stored procedure sp_lock to retrieve information about the current locks in SQL Server, and you can use the server process ID (SPID) to filter the information that sp_lock returns.

To determine whether a given process is waiting for the release of a locked resource, you can execute the sp_GetBlockInfo procedure that Listing 1 shows (you must execute the procedure before the timeout). The sp_GetBlockInfo procedure tells you which SPID is the root blocker (in the case of a blocking chain), the lock mode, and the locked resource’s database and object names. If the process isn’t blocked, sp_GetBlockInfo returns an empty recordset.

You can also detect blocks by checking for error 1222 Lock request time out period exceeded. The LOCK_TIMEOUT setting controls how long a process will wait for locks to be released before timing out. When the lock timeout occurs, SQL Server sends error 1222 to the application. In SQL Server 7.0, this error aborts the statement but doesn’t cause the batch to roll back, so you can look for the T-SQL system variable @@error and determine where locks exist. For details about @@error, see the "Customizing the Lock Time-out" section in SQL Server Books Online (BOL). SQL Server 2000 documentation describes SQL Server 7.0’s error 1222 behavior; however, in SQL Server 2000 release to manufacturing (RTM), error 1222 causes the entire batch to roll back and abort, thus eliminating the @@error variable. Microsoft has acknowledged this problem and provided a hotfix, available in SQL Server 2000 Service Pack 1 (SP1). The hotfix provides a method to revert SQL Server 2000 to the SQL Server 7.0 behavior. For more information about the hotfix, see the Microsoft article "FIX: LOCK_TIMEOUT Causes Transaction to Roll Back and @@error Does Not Capture Error 1222".

I get different results when I use the sp_depends system stored procedure to display my database object dependencies instead of using Enterprise Manager’s Display Dependencies window. The difference is especially apparent when I clear the Show first level dependency only check box. Why?

Enterprise Manager executes the undocumented stored procedure sp_MSdependencies, which is a procedure that is more advanced than the documented sp_depends stored procedure. In addition, sp_MSdependencies isn’t restricted to first-level dependencies.

You can obtain a full explanation of sp_MSdependencies’ supported parameters by running the following statement:

EXEC sp_MSdependencies '?'

Listing 2 and Listing 3 show some simple examples of code that Enterprise Manager generates and that SQL Server Profiler captures. For comparison, the command you use with sp_depends is

EXEC sp_depends N'[dbo].[Order Details Extended]'

How can I use SQL Server to manage resultset paging?

You often need to display a resultset one page at a time and support navigation between those pages, especially when you’re working with a website. Although you can page through resultsets by using the ADO Recordset object, that solution isn’t scalable.

To solve the problem of scalability, you need to include a unique ID column, such as a primary key, in your table. Listing 4 shows a simple example that uses two stored procedures to navigate between pages. This example, based on the Pubs database, lets you page through the authors table. To return the first two rows on the first page, you use spGetNextPage with the following parameters:

EXEC spGetNextPage @id=' 0', @rows=2

The spGetNextPage procedure returns the first two authors in the table:

172-32-1176Johnson WhiteMarjorie Green

To return the next two authors, pass the last row’s ID to spGetNextPage:

EXEC spGetNextPage @id='213-46-8915', @rows=2

The resulting page shows

238-95-7766Cheryl CarsonMichael O'Leary

To move to the previous page, call spGetPrevPage with the first row’s ID:

EXEC spGetPrevPage @id='238-95-7766', @rows=2

The result shows the first page, which you saw earlier. One drawback of this method is that the unique ID column dictates the order of the results. In this case, the au_id field comes before the author name.

Does T-SQL have functions that are equivalent to Oracle’s LPAD() and RPAD() functions?

Oracle uses the LPAD() and RPAD() functions to "pad" a string. These functions are the opposite of the LTRIM() and RTRIM() functions in such languages as Oracle, Visual Basic (VB), and T-SQL. With LPAD() and RPAD(), you get the string that you pass as the first parameter after padding the string (either to the left or the right) to the total length that the second parameter specifies and using the character or characters that the third parameter specifies. For more information about LPAD() and RPAD(), see John C. Lennon’s white paper "An Introduction to PL/SQL".

No equivalent LPAD() or RPAD() functions exist in T-SQL, but you can create a SQL Server 2000 user-defined function (UDF) that performs padding. Listing 5 shows a code example that performs the same functions as LPAD(). You can easily adapt the example to create an RPAD()-like function.

In Oracle, the third parameter (the padding character) is optional, but you must supply all arguments to a SQL Server UDF. However, you can use the DEFAULT keyword instead of supplying an explicit value for this third parameter.

What benefits can I get from using SQL Server 2000 Enterprise Edition?

SQL Server 2000 Enterprise Edition offers several unique features, including the ability to access more than 2GB of memory, support for failover clustering, and distributed partitioned views (aka federated database servers). However, even if you don't need any of these features, some performance optimizations are available only in the enterprise edition. For example, SQL Server 2000 Enterprise Edition dynamically adjusts the maximum number of read-ahead pages based on the amount of memory present; the number of read-ahead pages is fixed in all other editions of SQL Server 2000. And only SQL Server 2000 Enterprise Edition lets multiple tasks share full-table scans.

Neither of these two performance optimizations is likely to provide an order-of-magnitude improvement in response time. However, if you need to squeeze every millisecond out of your application, you’ll be glad that these two features kick in automatically when you install the enterprise edition. You don’t need to manually configure them.

For more information about SQL Server 2000 Enterprise Edition features, see the Microsoft articles "Features Supported by the Editions of SQL Server 2000" and "Reading Pages". SQL Server 2000 Books Online (BOL) incorrectly states that you can create indexed views only in SQL Server 2000 Enterprise Edition. Microsoft provides a full correction in the article "PRB: Indexed Views Can Be Created on All Versions of SQL Server 2000".

How can I prevent SQL Server from escalating locks?

SQL Server uses locks to maintain transactional integrity and consistency within a database. SQL Server supports several types of locks of differing granularities. For example, a row lock is a fine-grained lock; a page lock is a coarser lock. SQL Server escalates locks from a large number of fine-grained locks to fewer coarse-grained locks, which reduces system overhead but might result in unforeseen blocking within an application.

Escalation occurs automatically when the number of locks exceeds the lock-escalation threshold. This threshold is an internal setting designed to protect system resources. No configuration option exists for this threshold, but you can disable lock escalation if you start SQL Server with the undocumented trace flag 1211.

Be warned that if you disable lock escalation, the responsibility for managing the system resources is on you. If SQL Server runs out of memory, for example, the system can crash. A better approach is to execute shorter transactions if row-level locks are absolutely needed.

When manipulating DTS task objects, how can I obtain step and task names?

You can use an ActiveX script in Data Transformation Services (DTS) to dynamically manipulate your package at runtime. For a simple example of this technique, see Tips from the SQL Server MVPs, "How can I dynamically change a connection’s filename in a DTS package?"

To efficiently reference an object within a package, you can retrieve the object from the relevant collection by name, as the following code shows:

Set oStep = oPkg.Steps("DTSStep_DTSActiveScriptTask_1")Set oTask = oPkg.Tasks("DTSTask_DTSActiveScriptTask_1")

The difficulty here is in getting the task because the Properties sheet for a task doesn’t show the name. If you’re using SQL Server 2000, you can get this information and more from the new Disconnected Edit feature. You can access this feature from the Package menu in the DTS Designer by right-clicking a blank area of the design sheet.

If you’re using SQL Server 7.0, you’ll find the step name is in the Workflow Properties window for each task. Right-click the task and select Workflow, Workflow Properties. (You select only Workflow Properties for the DataPump task.) The step name is at the top of the Option dialog box.

Unfortunately, task names are not visible in the designer. Don’t be confused by the description at the top of a task’s properties sheet. A simple workaround that is 99 percent effective is to replace the word Step with Task in the step name.

You can confirm this workaround by retrieving the task name from the object model, as the code in Listing 6 shows. Paste this code into an ActiveX Script task, and set the step name variable. You can use some temporary workflow tasks to prevent execution of the remainder of your package if required. Remember that multiple workflow tasks follow the logical AND behavior.