Category Archives: Miscellany

Query Optimizer FAIL! 001

The query optimizer is tasked with converting a SQL statement into a list of commands that return a logical and consistent result. The optimizer part comes in as the database server attempts to determine the most efficient execution plan, looking for a balance between identifying the best plan and making a decision quickly. Much has been written elsewhere about the query optimizer, so I won’t review that information here. While the query optimizer is very impressive in its function, it has become a popular pursuit for database developers to find ways to trip-up the optimizer and have it chose an inefficient plan, or actually give up and not be able to find any execution plan at all.

For this post, let’s walk through an example of the latter, more extreme case — when the optimizer completely gives up and returns error Msg 8624: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

As usual, we’ll start by setting context to a development database. In this case, I’m using a database called “devDB”, running on SQL Server 2008 R2, Service Pack 2.

-- specify the database context
USE devDB;
GO

Next we’ll create a reflexive linked server. Using a linked server is a contributing factor in tripping up the optimizer, but we need not actually be talking to a second server. Thus we’ll create a linked server that refers back to itself, allowing us to demonstrate the issue on a single server. A reflexive linked server is part of my “default” server build script as it comes in handy now and then. For other uses of a reflexive linked server, see my previous post on selecting from stored procedures.

-- create a reflexive linked server;
IF NOT EXISTS(SELECT * FROM [master]..sysservers WHERE srvname = N'mirror')
EXECUTE sp_addlinkedserver
    @server = N'mirror'
    , @srvproduct = N''
    , @provider = N'SQLOLEDB'
    , @datasrc = @@SERVERNAME --current server is data source for linked server
    ;
GO

Now let’s create a few tables to use in a query. Nothing fancy required here. We’ll create table “A” that has an ID field and a foreign key to reference a second table, table “B”. The “B” table will simply be a list of a few numbers in integer and string form. While we intend to have a relationship between the tables, there is no need to define the foreign key. You can if you want, but it will not affect the result. I have left it out because it is not necessary to reproduce the optimizer fail.

-- if table "A" already exists, drop it
IF OBJECT_ID(N'tblA', N'U') IS NOT NULL DROP TABLE tblA;
GO

-- create table "A"
CREATE TABLE tblA (
    ID INT
    , FK INT
    );

-- populate table "A"
INSERT INTO tblA (ID, FK)
VALUES
    (1, 1)
    ,(2, 1)
    ,(3, 2)
    ,(4, 3)
    ,(5, 4)
    ;

-- if table "B" already exists, drop it
IF OBJECT_ID(N'tblB', N'U') IS NOT NULL DROP TABLE tblB;
GO

-- create table "B"
CREATE TABLE tblB (
    ID INT
    , String VARCHAR(10)
    );

-- populate table "B"
INSERT INTO tblB (ID, String)
VALUES
    (1, 'One')
    ,(2, 'Two')
    ,(3, 'Three')
    ,(4, 'Four')
    ;
GO

With our base tables in place, let’s now define and populate a temporary table with a data set based on the joined base tables.

-- if the temp table already exists, drop it
IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
GO

-- create a temp table to use in an update query
CREATE TABLE #t(
    RecordType INT
    , AID INT
    , BString VARCHAR(10)
    );

-- initialize the temp table
INSERT INTO #t(RecordType, AID)
VALUES
    (1, 1)
    ,(1, 2)
    ,(2, NULL)
    ;
GO

And now, just to double-check, let’s confirm the content of the temp table.

-- confirm content of temp table
SELECT * FROM #t;
GO

So far, so good! Next, let’s update the temp table. This will cause a couple things to happen in the background. We’ll talk about them later, but, for now, let’s just do the update.

-- update the temp table
UPDATE t
SET BString (
        SELECT TOP 1 String
        FROM mirror.devDB.dbo.tblB AS b
        WHERE b.ID = a.FK
        )
FROM
    #t AS t
    INNER JOIN mirror.devDB.dbo.tblA AS a
        ON t.AID = a.ID
WHERE t.RecordType = 1;
GO

Now, before anyone gets all excited, I am well aware there are more efficient ways to write the update statement. You forget the point! We’re not trying to write an efficient query… we are trying to write a query with which the optimizer doesn’t know what to do. 🙂

Once more, let’s confirm the content of the table to see that the update was successful.

-- confirm content of temp table
SELECT * FROM #t;
GO

Now comes the fun part. Let’s run the exact same query again and see what happens.

-- update the temp table
UPDATE t
SET BString (
        SELECT TOP 1 String
        FROM mirror.devDB.dbo.tblB AS b
        WHERE b.ID = a.FK
        )
FROM
    #t AS t
    INNER JOIN mirror.devDB.dbo.tblA AS a
        ON t.AID = a.ID
WHERE t.RecordType = 1;
GO

Pow! You’ll see that SQL Server was unable to build a query plan and thus could not execute the statement.

Msg 8624, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

So, what the heck happened? To be honest, I don’t know. Whatever is going on inside the query optimizer, it couldn’t deal with the combination of factors. But why did the first update succeed and the second one fail? Statistics. Specifically, when the table was created and populated, SQL Server did not have any useful information about what data was inside the table. Thus, when it attempted to run the update, it just put the pieces together and did it. However, in so doing, it scanned the data in the tables and created statistics on the values in each column in both tables. When the update statement runs the second time, the statistics are available to the optimizer, but for whatever reason the statistics actually confound the optimizer and it is not able to come to a solution.

Having said that, let me be clear: the statistics are not the problem. At least they are not the only problem. To see what I mean, try a few of the following; any one of which is enough of a difference to allow the optimizer to figure out what is going on, create a query plan, and execute the update.

  1. Delete the statistics from tblA. With no statistics, the update succeeds. The statistics on tblB do not matter in this case.
  2. Remove the WHERE clause from the update statement. The clause is actually irrelevant because the INNER JOIN does effectively the same thing: limits the update to records with a non-null value in the “AID” column and a matching value in tblB.
  3. Do not use either or both of the linked server paths (replace them with a “normal”, local path instead). While the same table is referenced regardless of using the linked server or not, going through the linked server connection affects what visibility the query optimizer has to the target data.
  4. Move the sub-query from the SET statement into the FROM clause. That would be a more efficient query anyway, but, again, that wasn’t the point. That little bit of “poor query writing”, in combination with the other factors mentioned here, is sufficient to stump the optimizer.

Changing any one of the four factors above will result in the update query succeeding.

If you know what is going on inside the optimizer that causes this to fail, or if you know of a SQL Server update that resolves this “bug”, please post a comment and let us know.

Advertisements

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.

Prevent TRUNCATE TABLE

The ability to truncate a table is a powerful tool. Truncation removes all data from a table, and, because the operation is minimally logged, it happens almost instantly. However, being minimally logged means that individual record deletions are not recorded in the transaction log. There are other potential pitfalls from truncation as well. Can someone suggest a few?

To avoid some of these pitfalls, we can implement a safeguard to prevent table truncation. As BOL states, truncating is not allowed on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  • Participate in an indexed view.
  • Are published by using transactional replication or merge replication.

In addition to these three conditions, it is also true that when a table is enabled for Change Data Capture (CDC) it cannot be truncated. Why? Because CDC reads the transaction log to monitor data changes, and, as mentioned above, truncation does not write record deletes to the log. An attempt to truncate a table enabled for CDC will result in message 4711:

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'tbl' because it is published for replication or enabled for Change Data Capture.

So, which of these four options should we use? Enabling CDC seems like a heavy solution simply to prevent truncation, plus it is only available at Enterprise Edition. Enabling replication also feels like an out-sized answer; plus, tables can be truncated within replication operations, so that does not provide reliable protection for the data.

That leaves two options: create a foreign key constraint, or include the table in an indexed view. If the table has a logical relationship with another table, then enabling a foreign key between them is probably the best answer as there is virtually no cost involved. However, if no logical relationship exists, we could create an empty table simply for the purpose of creating a foreign key.

Let’s demonstrate that approach first.

-- Set the database context
USE [db];

-- If the table already exists, drop it
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

-- Create a table
CREATE TABLE dbo.tbl (
     ID INT IDENTITY(1,1) NOT NULL
     , Value VARCHAR(20) NOT NULL
     , CONSTRAINT PK_tbl PRIMARY KEY CLUSTERED (ID)
     )
;
GO

-- If the dummy foreign key table already exists, drop it
IF OBJECT_ID(N'dbo.tblFK', N'U') IS NOT NULL DROP TABLE dbo.tblFK;

-- Create the dummy foreign key table
CREATE TABLE dbo.tblFK (
     ID INT IDENTITY(1,1) NOT NULL
     , IDFK INT NOT NULL
     , CONSTRAINT FK_tbl_tblFK FOREIGN KEY (IDFK) REFERENCES dbo.tbl(ID)
     )
;
GO

-- Attempt truncation
TRUNCATE TABLE tbl;

Truncation will fail with error 4712:

Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'tbl' because it is being referenced by a FOREIGN KEY constraint.

We could also create an indexed view of the table. This approach is as simple as the previous, and it has the same drawback of creating a “dummy” object in the database. However, it also has the disadvantage of consuming more disk space. Why? Because the index has to be stored! But that may not be a bad thing… if you have a secondary use for the view.

Let’s demonstrate this approach as well, re-using the sample table we created previously.

-- If the dummy indexed view already exists, drop it
IF OBJECT_ID(N'dbo.vwTbl', N'V') IS NOT NULL DROP VIEW dbo.vwTbl;
GO

-- Create a view with schemabinding (necessary for indexing)
CREATE VIEW dbo.vwTbl
WITH SCHEMABINDING
AS
     SELECT ID, Value
     FROM dbo.tbl;
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX UIX_vwTbl
ON dbo.vwTbl(ID);
GO

-- Drop the dummy foreign key table
IF OBJECT_ID(N'dbo.tblFK', N'U') IS NOT NULL DROP TABLE dbo.tblFK;

-- Truncation will fail because the table is referenced by an indexed view
TRUNCATE TABLE tbl;

Truncation will fail with error 3729:

Msg 3729, Level 16, State 2, Line 2
Cannot TRUNCATE TABLE 'tbl' because it is being referenced by object 'vwTbl'.

So, we just walked through a few options to prevent table truncation. What about using a DDL trigger on the database? Well, for whatever reason, there is no TRUNCATE TABLE event defined in SQL Server. And, while a truncation is effectively a drop and recreate of the table, it does not fire the DROP TABLE event. Thus, a DDL trigger will not work.

Does anyone have any other ideas or real-world experience with preventing TRUNCATE TABLE operations? Please send them in via a comment.

As ever, don’t forget to clean-up after yourself!

-- Drop the demonstration objects 
IF OBJECT_ID(N'dbo.vwTbl', N'V') IS NOT NULL DROP VIEW dbo.vwTbl;
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

Who Started That Job?

While a SQL Server job is running, details on its status are limited. The job activity monitor will show which job step is currently being processed, but it is completely opaque as to whether a transaction in that step is executing or rolling back or just waiting.

Even simple information such as “who started this job” is not available until AFTER the job completes, when the user who called the job will be written into the message field of msdb.dbo.sysjobhistory.

So what? Well, normally that is not really a problem… except when the job takes hours (or days!) to complete. Or, what if it is a continuous task such as a replication monitor? For whatever reason, the job might need to be stopped… but you might not know with whom to confirm unless you know who started the job in the first place. Of course, some jobs start with the SQL Server or based on a schedule — but for those jobs there is rarely a question as to why the job is running.

Execution details are well and truly hidden during a job run, but, thankfully, at least the job initiator is not. And Microsoft even gave us a blueprint on how to find that information. Take a look at the definition of the stored procedure msdb.dbo.sp_get_composite_job_info to see how the system execution procedure xp_sqlagent_enum_jobs is used to return some very useful job status information.

The following example breaks out the relevant bits for our purpose.

-- Create a table to store job information
DECLARE @tbl TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    , last_run_date INT NOT NULL
    , last_run_time INT NOT NULL
    , next_run_date INT NOT NULL
    , next_run_time INT NOT NULL
    , next_run_schedule_id INT NOT NULL
    , requested_to_run INT NOT NULL -- boolean
    , request_source INT NOT NULL
    , request_source_id SYSNAME COLLATE database_default NULL
    , running INT NOT NULL -- boolean
    , current_step INT NOT NULL
    , current_retry_attempt INT NOT NULL
    , job_state INT NOT NULL)
;

-- Declare and initialize variables
DECLARE
    @is_sysadmin INT = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
    , @job_owner SYSNAME = N'sa'
    /* To filter on a single job, uncomment the lines below */
    --, @job_id UNIQUEIDENTIFIER = (
    -- SELECT job_id
    -- FROM msdb.dbo.sysjobs
    -- WHERE name = N'<job_name SYSNAME>')
;

-- Select the job information
INSERT INTO @tbl
EXECUTE xp_sqlagent_enum_jobs
    @is_sysadmin
    , @job_owner
    /* To filter on a single job, uncomment the line below */
    --, @job_id
;

-- Select the running jobs that were started by a user
SELECT
    'JobName' = jobs.name
    , 'JobStartedBy' = t1.request_source_id
    , 'JobStarted' = acts.start_execution_date
    , 'CurrentTime' = GETDATE()
    , 'CurrentRunTimeSeconds' =
        DATEDIFF(SECOND, acts.start_execution_date, GETDATE())
    , 'CurrentRunTimeMinutes' =
        DATEDIFF(MINUTE, acts.start_execution_date, GETDATE())
    , 'CurrentRunTimeHours' =
        DATEDIFF(HOUR, acts.start_execution_date, GETDATE())
    , 'CurrentRunTimeDays' =
        DATEDIFF(DAY, acts.start_execution_date, GETDATE())
FROM
    @tbl AS t1
    INNER JOIN msdb.dbo.sysjobs AS jobs
        ON t1.job_id = jobs.job_id
    INNER JOIN msdb.dbo.sysjobactivity AS acts
        ON t1.job_id = acts.job_id
WHERE
    t1.running = 1 -- running jobs only
    AND t1.request_source = 4 -- started by a user
;

Please note that there is small delay between when a job starts and when SQL Server populates information into sysjobactivity. Thus, if you are testing this with simple (read: quick-running) jobs, be sure to add a few WAITFOR DELAY commands in there to give SQL time to work. A delay of just a few seconds is sufficient.

SQL 2000 — Argh!

Dang you, SQL Server 2000! Yes, it was a big step forward from SQL Server 7, but jeez there is a lot of functionality that is just so convenient since SQL Server 2005. Here’s another example, this time using the system functions in combination with metadata functions.

-- Set the context to a compatibility level 80 (SQL Server 2000) database
USE [db80];
GO

-- Try using the DB_ID() function within a system table-valued function
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
;

While the previous statement works fine with compatibility level 90 (SQL Server 2005) or greater, it fails for level 80. You’ll receive a syntax error around the “(“. And yet, both the DB_ID() function and the index physical stats function work by themselves at level 80.

-- Both the system function and the metadata function work
SELECT *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS ips
WHERE ips.database_id = DB_ID()
;

So, what’s the big deal? Well, if you’re in an environment that still supports SQL Server 2000 databases (maybe even side-by-side with newer databases on the same SQL server?), watch your step. Don’t expect to be able to develop for 2005 (not to mention 2008, R2, or 2012) and easily track down and fix the errors you get when running commands at the server level.

Calculate Prior Monday

It is a common business requirement to aggregate values by week and display the aggregate under the heading of either the first day of the week or the last day of the week. Calculating the first day of the week based on a calendar date is rather easy to do. We simply subtract a number of days from the input date based on the day of the week of the input date.

For example, to calculate the prior Monday of any date, the following formula works by first taking the day of the week of the input date, adding five to it, then dividing by seven and taking the remainder; we then subtract the resulting number of days from the input date. Test it out with your “dates” table, view, or table-valued function. (Don’t have a record set of dates? Get one here.)

-- Display information about a date, including the week starting date
SELECT
    N'Date' = CalendarDate
    , N'DayOfWeek' = DATEPART(WEEKDAY, CalendarDate)
    , N'NameOfWeekday' = DATENAME(WEEKDAY, CalendarDate)
    , N'PriorMonday?' = DATEADD(
        DAY
        , - ((DATEPART(WEEKDAY, CalendarDate) + 5) % 7)
        , CalendarDate)
FROM vwDates
WHERE CalendarDate BETWEEN '2012-07-01' AND '2012-07-31'
;

So, that is all well and good… but wait! Random configuration trap.

Why is it that we added five to the weekday? Five was the magic number because, by default, SQL Server is configured to start the calendar week on a Sunday. That means that Monday is considered the second day of the week. Seven days in a week minus two for Monday (which is our target day) leaves five; hence the modifier we used.

But, what if your server is configured differently and your week starts on Monday (or any day other than Sunday)? If so, you should have already noticed that the above script did not work. To correct this our script needs to be dynamic based on server configuration. Thankfully, SQL Server exposes the week day configuration via the global variable @@DATEFIRST. Furthermore, you can change the setting via a simple TSQL command. Let’s take a look:

-- Check to see your server configuration
--   Take note of this value; you might need it later
SELECT N'DATEFIRST' = @@DATEFIRST;

-- Change the first day of the week to Monday
SET DATEFIRST 1;

-- Display previous information, but add a new
--   calculation for the week starting date

SELECT
    N'Date' = CalendarDate
    , N'DayOfWeek' = DATEPART(WEEKDAY, CalendarDate)
    , N'NameOfWeekday' = DATENAME(WEEKDAY, CalendarDate)
    , N'PriorMonday?' = DATEADD(
        DAY
        , - ((DATEPART(WEEKDAY, CalendarDate) + 5) % 7)
        , CalendarDate)
    , N'PriorMonday' = DATEADD(
        DAY
        , - ((@@DATEFIRST + DATEPART(WEEKDAY, CalendarDate) - 2) % 7)
        , CalendarDate)
FROM vwDates
WHERE CalendarDate BETWEEN '2012-07-01' AND '2012-07-31'
;

This time, you should see that the “PriorMonday?” column is incorrect but the “PriorMonday” (no question mark) column is, indeed, accurate.

The skeptical ready might wonder, especially after the previous rigmarole about adding five, why should we now have to subtract two days from the weekday when the @@DATEFIRST variable is included? The answer is that when SQL Server defaults to Sunday being the first day of the week, that configuration is identified with a @@DATEFIRST value of seven (7).  As the @@DATEFIRST configuration changes, so does the DATEPART(WEEKDAY, [date]) function result. For whatever reason, the configuration value is related to the datepart/weekday result at an offset of two (2). That offset could have been anything; it just happens to be two (2). Deal with it.

Of course, it should be noted that if your organization has a custom fiscal calendar you might not need to (or even be able to)  calculate week starting/ending dates because there might not be a deterministic rule set that could be applied to a calendar date to calculate the week into which it belongs.

One last thing… don’t forget to change your @@DATEFIRST value back to its original value. No problem, really, as you were not working in your production environment. 😉

-- Change the first day of the week to Sunday (default)
--   If you had a different configuration, reset it accordingly
SET DATEFIRST 7;

Random Configuration Trap 001

Here is a funny little configuration anomaly to be aware of: the “allow updates” server configuration option is not only deprecated in SQL Server 2008 R2, but it could also break your application if not set to 0 (zero : FALSE).

In a nutshell, if “allow updates” is set to 1 (TRUE), then you cannot issue the RECONFIGURE statement. It will fail with:

Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

But could that really break your application? Yes. For example, if you run a DBCC CHECKDB command in a routine or on-demand maintenance task, part of the CHECKDB automated process runs a RECONFIGURE. Net effect: FAIL.

The skeptical reader may ask: “if you can’t reconfigure when ‘allow updates’ is set to TRUE, how could ‘allow updates’ get changed from FALSE to TRUE in the first place?” Well, I’m glad you asked. Here’s how: first, run the following commands to check the current configuration and then configure the “allow updates” option:

-- Show advanced options, so we can see the setting on "allow updates"
EXECUTE sp_configure 'show advanced options', 1;
GO

-- Reconfigure the server to apply the change we just made
RECONFIGURE;
GO

-- Change the setting for "allow updates" to TRUE
EXECUTE sp_configure 'allow updates', 1;
GO

-- View the current configuration settings
--   Compare the "config value" to the "run value" for "allow updates"
EXECUTE sp_configure;
GO

-- If you want to test, reconfigure and see the error
RECONFIGURE;
GO

Then, in SSMS, right-click your server, select Properties, go to the Memory page, modify the “Minimum server memory (in MB)” property (or any property, in fact), and click ok.

Voilà; the “allow updates” change has been applied. Check the configuration and see:

-- Compare the "config value" to the "run value" for "allow updates"
EXECUTE sp_configure;
GO

And confirm that RECONFIGURE no longer works:

-- Reconfigure to see the failure message
RECONFIGURE;
GO

In the message section you should see the error detailed at the beginning of this post.

Since “allow updates” has been deprecated for a while now, in theory no one should be poking around and flicking that switch anyway. But, you know how the saying goes: “In theory there is no difference between theory and practice. In practice, there is.” (Incidentally, was that said by Yogi Berra or by van de Snepsheut? I lean toward the latter….)

Yes, while a careful reading of books online will tell you not to change “allow updates”, I’d still guess that, like most folks, I tend to experience these pitfalls in the wild before I read about them in BOL.

I’m intrepid that way. 😉

By the way, be sure to reset your “allow updates” back to FALSE. It’s not a big deal, though, because I’m sure you were working in a test environment, right?!

-- Change the setting for "allow updates" to FALSE
EXECUTE sp_configure 'allow updates', 0;
GO

-- Hide advanced options
EXECUTE sp_configure 'show advanced options', 0;
GO

-- Reconfigure the server to apply the changes
RECONFIGURE;
GO

Random Security Trap 001

The downside of having been around for a while is that you (hopefully) have learned something. When new features are released it can be easy to stumble right over the obvious. Here is a tale of one such bloodied nose. 😉

The Setup: There is a physical server (Box 1) running Windows Server 2008 R2. On Box 1 is installed SQL Server 2008 R2 (SQL 1) as a default instance. A second physical server (Box 2) is also running Windows Server 2008 R2; Box 2 also has installed SQL Server 2008 R2 (SQL 2) as a default instance. Both boxes are on the same domain. There is a Windows user account configured as a Windows Domain Administrator (User).

The Twist: User can log into Box 1 and can log into SQL 2 from Box 1, but cannot log into SQL 1 from Box 1. User can log into Box 2 and can log into SQL 1 from Box 2, but cannot log into SQL 2 from Box 2. Furthermore, when connected to SQL, no databases are accessible and very few SQL artifacts are visible, despite User being a domain administrator.

The Solution: It turns out that this unexpected circumstance was the result of the overlap of two “new” features; user account control (UAC) in Windows and the default security model in SQL Server.

Issue 1 – UAC: Windows Vista and Windows Server 2008 were the first Windows operating systems to introduce UAC, a rule set that, simply put, prevents administrative privileges from being inherited by default. Windows Server 2008 R2 also includes UAC. Thus, even though User is logged into Box 1 as an administrator, when launching SQL Server Management Studio (SSMS) normally, it runs with the permissions of a normal user. Hence, trying to login to SQL 1 from Box 1 will not work because User does not have a defined SQL login (recall that User is connected to Box 1 as a member of the Windows Domain Administrators group). To work within the UAC constraints, User simply needs to explicitly run SSMS as an administrator and thus specify that administrative permissions are inherited by SSMS. This will enable SQL login… but User will still only have access as a public user — not as a member of the sysadmin role. (Note that User can connect to SQL 2 from Box 1 without explicitly running SSMS as an administrator because credentials are passed externally despite UAC; credentials are only restricted by default on the machine into which the user is connected.)

Issue 2 – default SQL security: Beginning with SQL Server 2008, the BUILTIN\Administrators group is no longer added as a SQL sysadmin by default. This change is part of an intentional effort by Microsoft to separate SQL administrative privilege from Windows administrative privilege. Thus, if a Windows administrator attempts to login to SQL, they will be granted access as a public user only. To address this issue, create a SQL login for Windows administrators and assign it the sysadmin role (or whichever role is desired). Keep in mind that a Windows administrator SQL sysadmin login was not created by default for a reason; consider the security implications carefully before creating such a login.

To enable User to have the expected experience requires addressing both issues. Issue two without one will expand visibility when logged in to SQL, but will not allow connection to the local SQL instance; issue one without two will allow connection to the local SQL instance, but User will still have very little SQL access as a public user.

The Lesson: Stay current on application features by version. It is almost always the case that subsequent software versions offer a superset of features over the prior versions; but here we have a case where two separate applications had a “regression” of privilege to enhance security, and the experience resulting from the application overlap was unusual-looking indeed.

List all Databases on a Server

Occasionally it is useful to have a record set of all the databases on a server. Thankfully, there is an undocumented system stored procedure that can be used to return just that. The procedure has one required parameter: the command to execute for each database. The procedure also accepts a substitution element as a placeholder for the database name; by default the placeholder is a question mark (?) and can be used as such within the command text.

To demonstrate this stored procedure, execute the following TSQL statement from any connection on your SQL server. It will display the name of each database on the server.

-- Display the name of every database on the server
EXECUTE sp_msforeachdb 'PRINT ''?'';';

This example can easily be extended to create a table and populate it with database names. To do so, create the table in which you would like to store the database names, then replace the PRINT command with an INSERT INTO command referencing the table. To see that usage in action, refer to defragment indices. Also, why not replace the PRINT command with a BACKUP DATABASE command? See it in action here.

Select from Stored Procedure

Stored procedures are often used to encapsulate business rules, especially for reporting purposes. However, it frequently happens that there is a need to use those encapsulated business rules (rather, the record sets that they produce) as input to a separate query. In order to do this, we need to be able to SELECT from the stored procedure… which cannot be done natively.

If the output structure of the stored procedure is known and stable, we can create a temporary table with the same structure as the output from the stored procedure and then INSERT INTO the temporary table. To demonstrate this, first we will define a stored procedure that returns a record set. We will use the utfNumbers function to populate our result set.

-- Create a stored procedure that returns a result set
CREATE PROCEDURE dbo.uspReturnRecords
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
    ;

    -- Select the records to return
    SELECT n
    FROM DB.dbo.utfNumbers(10)
    ;
END

Given this stored procedure with a known structure, we now create a temporary table to hold the records.

-- Create a temporary table of the known structure
CREATE TABLE #TEMPTABLE (
    n BIGINT)
;

-- Insert into a temporary table
INSERT INTO #TEMPTABLE
EXECUTE DB.dbo.uspReturnRecords
;

Now, we simply select from the temporary table. (And drop it when finished. Though not strictly necessary, it’s always good practice to clean up after yourself!)

-- Select from the temporary table
SELECT *
FROM #TEMPTABLE
;

-- Drop the temporary table
DROP TABLE #TEMPTABLE
;

So far, so good. But what if the structure of the stored procedure result set is complex, variable, or entirely unknown? Given those options the preceding method is impractical.

To address those potential challenges, we can simply use the OPENQUERY functionality. The only complication with this approach is that OPENQUERY takes as its first parameter the name of a server (or linked server). Thus, we will first define a reflexive linked server and then use it to query our stored procedure via OPENQUERY.

-- If a linked server of the same name already exists, drop it
IF EXISTS(SELECT * FROM master..sysservers WHERE srvname = N'mirror')
    EXECUTE sp_dropserver N'mirror'
;
GO

-- Create a reflexive linked server
EXECUTE sp_addlinkedserver
    @server = N'mirror'
    , @srvproduct = N''
    , @provider = N'SQLOLEDB'
    , @datasrc = @@SERVERNAME --current server is data source for linked server
;
GO

-- Select the stored procedure into a temporary table
SELECT *
INTO #TEMPTABLE
FROM OPENQUERY(mirror, 'EXECUTE DB.dbo.uspReturnRecords;')
;

-- Select from the temporary table
SELECT *
FROM #TEMPTABLE
;

-- Clean up by dropping the temporary table
DROP TABLE #TEMPTABLE
;

While we could also drop the reflexive linked server as part of the clean-up, it’s handy to have around, so why not leave it? Better yet, add it to your development server and production server build sheet so it’s always there when you need it. 🙂