Tag Archives: CURSOR

For Each DB… Almost

The potential problems with the undocumented system procedure sp_MSforeachdb are well known to seasoned TSQL developers. Yes, it doesn’t pickup all databases (only those that are accessible at the time of execution). Yes, under certain versions of SQL Server (such as 2005 RTM) it picks up snapshots as well. Yes, it is a cursor behind the scenes and thus doesn’t offer any performance advantage to rolling your own.

And here is one more problem: it doesn’t trap @@FETCH_STATUS = -2.

What is the significance of that? Well, it is one more way for the procedure to let you down in a way you didn’t expect. Let’s take a closer look.

The following code will use the system procedure to list every* database on the server (*within the constraints of accessibility mentioned above! ;).

-- declare and set a sql string to print the database name
DECLARE @sql NVARCHAR(2000) = N'PRINT ''?'';';

-- use sp_MSforeachdb to print the name of "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

On my DEV server, the above query generates the following result:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB
devDB
devDW

Six “system” databases plus a dev database and a dev data warehouse. So far so good. But… what if one of those databases goes offline while the procedure is running? Let’s find out!

For this example I will take the second to last database (devDB) offline during procedure execution. If you are testing at home, choose any database except a system database or the last database in your list. The system database restriction should be obvious; excluding the last database will become apparent after the demo.

-- declare and set a sql string to take the "devDB" database offline
-- and print the context database name from the MSforeachdb procedure
DECLARE @sql NVARCHAR(2000) = N'IF ''?'' = ''master''
    ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
PRINT ''?'';';

-- use sp_MSforeachdb to execute the sql against "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

On my DEV system, when I execute the previous query the following results are returned:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB

Notice anything? Not only is devDB missing from the list… but so is devDW! And yet, no execution errors reported, so I’d have no idea anything went wrong.

But, what actually did go wrong? As I mentioned, the problem is that sp_MSforeachdb does not trap @@FETCH_STATUS = -2. To demonstrate, let’s first recreate what the procedure is doing.

-- declare the cursor output variable
DECLARE @sysDBName SYSNAME;

-- declare and initialize the cursor
DECLARE crs CURSOR FOR SELECT name FROM sys.databases;

-- open the cursor
OPEN crs;

-- initialize the cursor output
FETCH NEXT FROM crs INTO @sysDBName;

-- iterate through the cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
    -- take the "devDB" offline
    IF @sysDBName = 'master'
        ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
    -- print the cursor context database
    PRINT @sysDBName;
    -- fetch the next value from the cursor
    FETCH NEXT FROM crs INTO @sysDBName;
END

-- close cursor
CLOSE crs;

-- release cursor from memory
DEALLOCATE crs;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

The result of the above query is the same as the procedure; devDW is missing and no error is returned. But now we can easily add some additional syntax to identify and report on the issue. For example, add the following immediately after the FETCH command within the WHILE loop:

-- trap for missing records
WHILE @@FETCH_STATUS = -2 BEGIN
    PRINT 'The row fetched is missing. Continuing to the next row....';
    FETCH NEXT FROM crs INTO @sysDBName;
END

Now we can see that the devDB row could not be fetched because it is missing from the cursor record set. But we can also chose to continue on to the next record, or take other action as needed. Another approach could be to loop through cursor results on @@FETCH_STATUS <> -1 instead of = 0. This example also shows why we didn’t get an error record — because, despite the unexpected results, no error actually occurred! @@FETCH_STATUS -2 is not an error, but a possibility that SQL Server anticipates and has already enumerated. See here for more info.

While we don’t need any more reasons to abandon sp_MSforeachdb, I suppose it is worth remembering that the procedure is undocumented for a reason.

Advertisements

Test Compatibility Level

Business Case: An existing application is built on a legacy version of the SQL Server database engine (such as SQL Server 2000). To take advantage of modern capabilities, the application is to be upgraded to a more recent version of the database engine.

Environment: The application is business-critical and unscheduled downtime is not acceptable. The application must be tested to confirm reliability at the higher database version, but full-function application testing is unrealistic due to the complexity of the application.

Approach: To test the application database layer, we will create a script to collect and parse the existing SQL statements under the higher database version, capturing any errors that might be generated.

Solution: To demonstrate the approach, we will simply write and test a few SQL statements. To fully-develop this solution, rather than run the test against our sample SQL table, we could run it against a filtered selection of sys.sql_modules — a system view that contains the SQL definition statement of virtually all objects in the database.

First, let’s set the database context to a database at the target version (compatibility level). We’ll also suppress statement record count reports.

-- Set the database context to a db at the target version
USE [db];
GO

-- Do not show statement record counts
SET NOCOUNT ON;

For demonstration purposes, we’ll create a table variable to store the SQL statements to be tested. In the real-world, a query from the [definition] field of sys.sql_modules would be a good place to start. In this example we’ll test three statements that are valid in SQL Server 2000 but invalid in SQL Server 2008.

-- Create the SQL input table
DECLARE @tblIn TABLE (
    ID INT IDENTITY(1,1) NOT NULL
    , SQLString NVARCHAR(MAX) NOT NULL
    )
;

-- Populate the SQL input table
INSERT INTO @tblIn (SQLString)
VALUES -- these statements are valid at level 80, invalid at level 100
    (N'SET')
    , (N'SELECT o.name FROM sys.objects AS o HOLDLOCK;')
    , (N'SELECT o.name FROM sys.objects AS o, sys.tables AS t WHERE o.[object_id] *= t.[object_id];')
;

Now we’ll create an output table to store the results of our syntax check. We’ll capture the details of our input as well as the level at which we are testing the statement and any errors that might result.

-- Create the SQL output table
DECLARE @tblOut TABLE (
    ID INT NOT NULL
    , SQLString NVARCHAR(MAX) NOT NULL
    , ValidationLevel TINYINT NOT NULL
    , ErrorNumber INT NOT NULL DEFAULT(-1)
    , ErrorMessage NVARCHAR(2000) NOT NULL DEFAULT(N'')
    )
;
GO

Next we’ll begin the automated testing phase. We’ll create a cursor to scroll through the SQL statements to be tested. Start by creating variables to hold relevant information for the cursor, then initialize the cursor definition and iteration process.

-- Declare the cursor output variable(s)
DECLARE
    @intID INT
    , @nstrSQLOrig NVARCHAR(MAX)
    , @nstrSQL NVARCHAR(MAX)
    , @intCompatLevel TINYINT = (
            SELECT [compatibility_level]
            FROM sys.databases
            WHERE database_id = DB_ID()
        )
;

-- Declare and initialize the cursor
DECLARE crs CURSOR FAST_FORWARD
FOR
    -- Define the cursor recordset
    SELECT ID, SQLString
    FROM @tblIn
;

-- Open the cursor
OPEN crs;

-- Initialize the cursor output
FETCH NEXT FROM crs INTO @intID, @nstrSQL;

With the cursor now open, let’s iterate through the records. For each SQL statement to be tested, we’ll first capture the original SQL statement. Then we’ll modify the statement by prepending turning the PARSEONLY setting on. That setting tells SQL Server to check the syntax of the statement, but do not compile it and do not execute it. If, for your situation, you’d like to test statement compilation as well, try setting NOEXEC on. Once parsed, we’ll turn PARSEONLY off so that further statements are executed. And, by wrapping the whole thing in a try-catch block and within a transaction, we can control the flow and error capture to meet our needs.

-- Iterate through the cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Capture the original SQL statement
    SET @nstrSQLOrig = @nstrSQL
    -- Attempt to parse
    BEGIN TRY
        -- Start transaction
        BEGIN TRANSACTION
            -- Prepend statement with parseonly setting 
            SET @nstrSQL = N'SET PARSEONLY ON; ' + @nstrSQL;
            EXECUTE (@nstrSQL);
            -- Turn off parseonly
            SET PARSEONLY OFF;
            -- Capture statements and validation level
            INSERT INTO @tblOut (ID, SQLString, ValidationLevel)
            VALUES (
                @intID
                , @nstrSQLOrig
                , @intCompatLevel
            ;
        -- Commit transaction
        COMMIT TRANSACTION;
    END TRY
    -- Parse failed; capture error
    BEGIN CATCH
        -- Roll back the transaction
        ROLLBACK TRANSACTION;
        -- Capture statement and error information
        INSERT INTO @tblOut (
            ID
            , SQLString
            , ValidationLevel
            , ErrorNumber
            , ErrorMessage
            )
        VALUES (
            @intID
            , @nstrSQLOrig
            , @intCompatLevel
            , ERROR_NUMBER()
            , ERROR_MESSAGE()
            )
        ;
    END CATCH
    -- Iterate the cursor output
    FETCH NEXT FROM crs INTO @intID, @nstrSQL;
END

Finally, let’s close and deallocate the curser and turn statement counts back on.

-- Close cursor
CLOSE crs;

-- Release cursor from memory
DEALLOCATE crs;

-- Show statement counts
SET NOCOUNT OFF;

What about the results? Well, let’s take a look!

-- View validation report
SELECT * FROM @tblOut;

If the preceding script is run under SQL Server 2000 (compatibility level 80), you’ll see the following output.

Test SQL at level 80

Valid syntax for SQL 2000

And, if run under SQL Server 2008 (compatibility level 100), you’ll see the following.

Test SQL at level 100

Invalid syntax for SQL 2008

Does anyone have any suggestions on a more elegant way to accomplish this type of automated compatibility testing?

Defragment Indices

As records are added to or removed from a table, indices on the table will become fragmented, thus reducing their efficiency both in terms of disk usage and performance. This fragmentation is especially severe if a database is shrunk (one reason why a database should NOT be shrunk, barring exceptional circumstances). SQL Server ships with maintenance tasks that can be configured to defragment indices. However, there are situations when running a maintenance task is not practical, or a greater degree of customization is desired beyond what is configurable within the maintenance task. In such circumstances, a stored procedure can be used to manage the defragmentation.

The example procedure below is created within a maintenance schema named “mx”. Furthermore, it uses the system stored procedure sp_MSforeachdb to span all databases on a server when looking for indices to defragment; it only looks for user tables, even though it includes the system databases; it specifies a fragmentation threshold to qualify a table for defragmentation; and it uses the degree of fragmentation to decide whether to REORGANIZE or REBUILD the index.

-- Create a stored procedure to defragment indices
CREATE PROCEDURE [mx].[uspDefragmentIndices]
AS
BEGIN
    -- Declare variables
    DECLARE
        @DBName NVARCHAR(255)
        , @TableName NVARCHAR(255)
        , @SchemaName NVARCHAR(255)
        , @IndexName NVARCHAR(255)
        , @PctFrag DECIMAL
        , @Defrag NVARCHAR(MAX)
    ;
    -- If the temporary table already exists, drop it
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    ;
    -- Create the temporary table
    CREATE TABLE #FragTables (
        DBName NVARCHAR(255)
        , TableName NVARCHAR(255)
        , SchemaName NVARCHAR(255)
        , IndexName NVARCHAR(255)
        , AvgFragment TINYINT)
    ;
    -- Insert fragmented indices into the temporary table
    EXECUTE sp_MSforeachdb
        'INSERT INTO #FragTables (
            DBName
            , TableName
            , SchemaName
            , IndexName
            , AvgFragment)
        SELECT
            ''?'' AS DBName
            , t.Name AS TableName
            , sc.Name AS SchemaName
            , i.name AS IndexName
            , s.avg_fragmentation_in_percent
        FROM
            ?.sys.dm_db_index_physical_stats(
                DB_ID(''?'')
                , NULL
                , NULL
                , NULL
                , ''Sampled'') AS s
            INNER JOIN ?.sys.indexes AS i
                ON s.Object_Id = i.Object_id
                AND s.Index_id = i.Index_id
            INNER JOIN ?.sys.tables AS t
                ON i.Object_id = t.Object_Id
            INNER JOIN ?.sys.schemas AS sc
                ON t.schema_id = sc.SCHEMA_ID
        WHERE
            s.avg_fragmentation_in_percent > 20
            AND t.TYPE = ''U''
        ;'
    ;
    -- Declare a cursor to scroll through the fragmented indices
    DECLARE cIndices CURSOR
    FOR
    SELECT
        DBName
        , TableName
        , SchemaName
        , IndexName
        , AvgFragment
    FROM #FragTables
    ;
    -- Open the cursor
    OPEN cIndices;
    -- Populate the cursor
    FETCH NEXT FROM cIndices
    INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
    ;
    -- Scroll through the fragmented indices
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Begin building the TSQL statement to defragment the index
        SET @Defrag = N'ALTER INDEX [' + @IndexName
            + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] '
        ;
        -- Set the defrag type based on the degree of fragmentation
        IF @PctFrag <= 40.0
            SET @Defrag += 'REORGANIZE';
        ELSE IF @PctFrag > 40.0
            SET @Defrag += 'REBUILD';
        -- Display the TSQL statement that was built
        PRINT @Defrag;
        -- Execute the TSQL statement
        EXECUTE sp_executesql @Defrag;
        -- Populate the next record into the cursor
        FETCH NEXT FROM cIndices
        INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
        ;
    END
    -- Close the cursor
    CLOSE cIndices;
    -- Release the cursor
    DEALLOCATE cIndices;
    -- Drop the temporary table
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    ;
END

To execute this stored procedure, simply run the following TSQL command. Note that it does not matter from which database the stored procedure is run, as the procedure itself references all databases on the server. This can be modified, of course, by removing the code that captures the indices to be defragmented from within the sp_MSforeachdb stored procedure.

-- Defragment the indices
EXECUTE DB.mx.uspDefragmentIndices;

As this procedure runs, it will display a list of TSQL statements that were executed, giving you an overview of the state of indexes on the server prior to defragmentation. It is often worth saving this list along with other maintenance and change logs.