The Only Constant Is Change

8044372740_3ddac1850d_mMonitoring the health of your SQL Servers is a standard part of a DBA’s job. One of the most important parts, to tell you the truth. It’s tempting to think of databases as unchanging. You can check them once and, if they are healthy, your job is done. There is no need to check it again. But, just like people, their health can change over time.

I run my own health check scripts on my SQL Servers every quarter. I am amazed at what I find. I typically spent 2 to 3 weeks going through all my servers and correcting all the issues the script turns up. But what is even more amazing is how often I find problems cropping up again the next time I perform the health check.

One of the more common things I find are untrusted or disabled foreign keys and check constraints. Each quarter, these pop up in my checks and, each quarter, I re-enable them. They always seem to get disabled somewhere down the line. Sometimes it’s an in-house process that disables them. In that case, I can talk to the developers and correct the problem at the source. Sometimes, it’s a third party app that I have no control over.

I also find a lot of issues regarding jobs. One common one is jobs without error notifications. Often times, third party products install SQL jobs and they almost never set up a failure notification. I also find vendor jobs that perform index maintenance. Because I have my own index maintenance routines on my servers, I disable these jobs in favor of my own (after verifying my own will run as frequently as the vendor’s did). Because I don’t always know when new software is installed or upgraded, these issues come up each time I run my health checks, even if I have corrected the issue the last time I ran the check.

Bottom line: your SQL Servers are living things and they change over time. Even if you have them configured and running exactly they way you want them to right now, over time, they will change. Monitor them on a regular basis.

FYI, my health check script is a variation on Brent Ozar Unlimited’s sp_Blitz script. If you don’t have your own script to use, theirs is a great one to start with.


Using PowerShell For More Readable Log Files

Most of the scheduled jobs on my SQL Servers write some sort of output to a log file. Most of the time, it’s just the output of a SQL statement. However, for some of my longer, more complicated routines, the procedures I write include PRINT statements to output various statuses or progress messages while the procedure is running.

When these are running on SQL Server Management Studio, they appear in the Message tab of the results pane. If the procedure is run as a scheduled job and the option to output the results to a file is enabled, the text output by the PRINT statement gets written to the text file. But it’s not pretty.

If you’ve ever tried this, you’ll quickly discover that SQL Server appends the text [SQLSTATE 01000] to the end of each line output by a PRINT statement. I find this really annoying. It renders the log file much harder to read.

There was a Microsoft Connect item created to change this, but Microsoft has stated they will not change this behavior. Apparently, their code cannot tell the difference between a PRINT statement and an error and SQLSTATE is a vital piece of information that needs to be included in error messages.

Enter Powershell7551668880_4446c12657_m

Because SQL Agent offers a PowerShell step type, it’s very easy to use PowerShell to remove these unwanted additions to your wondrous PRINT statement messages. For my jobs that output verbose log files populated by such messages, I just add an additional step at the end of the job that calls a PowerShell script to replace the string “[SQLSTATE 01000]” with a carriage return and line feed.

Here’s the script:

Get-Content $file | ForEach-Object { $_ -replace "\[SQLSTATE 01000\]", "`r`n" } | Set-Content ($file+".tmp")
Remove-Item $file
Rename-Item ($file+".tmp") $file

What the script does is export the contents of your file to a temp file, replacing the offending string with a carriage return-line feed as it does so, deletes the old file, and renames the temp file back to your original file name. The script needs to escape the brackets, as they are special characters in PowerShell. Additionally, the `r`n combinations are PowerShell special characters that represent a carriage return and a line feed.  The script works well if the filename the job writes to is always the same. If it changes, you’ll need to add logic to set the $file variable correctly.


Implicit Conversions Can Be An Unseen Danger

I got an email from one of my developers regarding a process they had developed in-house. The process has been taking longer and longer to run and it had reached the point where the application was timing out while waiting for a response from the database. Some investigating revealed that the process was timing out after 30 seconds. This process was developed in .NET and the default timeout for .NET applications is 30 seconds, so at least we identified why the application was timing out.

The next step then was to look at the code that was being run and determine why the database was taking more than 30 seconds to return data. To make a long story short, there were stored procedures calling stored procedures calling views and the whole thing was basically a mess. But the root cause, believe it or not, was implicit conversions, mainly with dates. The views were joining several tables and the stored procedures were referencing several of the columns in WHERE clauses. In some of the tables, dates were stored as date datatypes, but in others they were stored as varchar datatypes. As a result, virtually every join required an implicit conversion and almost no WHERE clauses were SARGable.

A common way of troubleshooting performance issues is to look at the execution plan using SSMS. Unfortunately, implicit conversions are easily overlooked in an execution plan because they don’t have their own little symbol – you have to mouse over a symbol and read the pop up text to see them.

Let’s look at a simplified example. We have the following table:

Implicit Conversion Table

For demonstration purposes, I’m not going to join to any other tables, but the concept is the same. Let’s look at this SELECT statement:

FROM    [dmPCOMBDaysNoHolidays]
WHERE	CONVERT(VARCHAR(5), YEAR(calendarDate)) = '2014'

If we execute this statement and include the execution plan, we get the following:


Nothing exciting there. Standard stuff.  But let’s hover the mouse over the clustered index scan icon and look at the text that pops up:


You can see two conversions – let’s look at them in reverse. The second is the implicit conversion that changes the varchar column from the table into a date datatype to feed into the YEAR function.  Because the YEAR function returns an integer, we explicitly convert this into a varchar(5) to compare to ‘2014’, which is the first conversion listed. (Don’t ask me why it’s varchar(5) and not varchar(4). That’s just another example of how bad this code is.)

Note the subtree cost. We’ll come back to that.

Now let’s re-write this query and try to eliminate that explicit conversion. We can use this:

FROM    [dmPCOMBDaysNoHolidays]
WHERE	YEAR(calendarDate) = 2014

Because the YEAR function returns an integer, we can compare the result to the integer value of 2014, instead of the string value ‘2014’. Our execution plan looks exactly the same:


But the mouseover text now has some differences:


Our explicit conversion is gone – as expected since we were the ones who got rid of it. We still have the implicit conversion of the column data to a date datatype for use in the YEAR function. So we’re doing one less conversion, which means SQL Server has less work to do. You would expect the estimate subtree cost would drop.  But, surprisingly, it did not. Hmm.

Now let’s make one more change. If we actually look at the data, we see it looks like this:


The data values all are in the format YYYY-MM-DD. Knowing this, we can now write our query as follows, keeping everything as character data:

FROM    [dmPCOMBDaysNoHolidays]
WHERE	LEFT(calendarDate,4) = '2014'

Again, the resulting execution plan looks identical to our other two:


Looking at the mouseover text, we see we no longer have any conversions at all. Yet, the subtree cost remains the same.


In summary, our original query did the following:

  1. Convert varchar datatype to a date datatype
  2. Run this value through the YEAR function, which returns an integer datatype
  3. Convert the integer back into a varchar for the comparison to the criteria

Because this is a non-SARGable query, these three steps have to be done for each and every row in the table.

Our re-written query does the following:

  1. Compares the leftmost 4 characters of the calendarDate field with the criteria

We’ve cut the work down by 66%. That’s an improvement, but it’s a rather small one. Unfortunately, without changing the database schema, it’s all we can do.

Now in these examples, the table only has a few thousand rows, so we’re not seeing changes in cost or noticeable changes in performance. However, in the actual application I was troubleshooting, these conversions were happening for multiple tables with hundreds of thousands of rows, in multiple places, in WHERE clauses, and on joining columns. It all added up to very slow performance.

Unfortunately, for situations like these, there is very little a DBA can do to dramatically improve performance. All of the WHERE clauses in these examples are non-SARGable and will require an index scan of all rows rather than an index seek of a subset of rows. To fix performance issues caused by this type of problem, the only answer is a code re-write, schema re-design, or, more likely, both

As a test, I reproduced the tables in the process using correct and consistent datatypes, created the same indexes on them, loaded the exact same data, and ran the queries (modified only to not perform all the no-longer necessary datatype conversions) against the new schema. Performance was improved by an order of magnitude. If you only looked at execution plans, it might look like the changes did not make any difference, but in reality, the difference was significant.



SYS.DM_EXEC_REQUESTS Changes Between Versions Of SQL Server

I was recently doing some work with a backup verification routine that is meant to run on various versions of SQL Server and came across some variations in the way the RESTORE VERIFYONLY command is reported by sys.dm_exec_requests between SQL Server 2005, 2008 R2, and 2012. I haven’t seen this mentioned anywhere, so I thought I’d document it here. This is useful information to have if you are doing any type of programming based on the output of sys.dm_exec_requests while using the RESTORE VERIFYONLY command.

The table below notes the differences. Note the change of field length in SQL 2008 / 2008 R2, which results in what appears to be the truncation of the command text. I have not run this test on SQL Server 2014.

The command run was:

FROM DISK = N'<backup path and file here>'

And the code to view sys.dm_exec_requests while the above is running is:

SELECT	session_id,
		CAST(estimated_completion_time AS DECIMAL(20,3))/1000.0/60.0 AS EstimatedCompletionTimeInMinutes,
FROM	sys.dm_exec_requests
WHERE session_id >50
order by percent_complete desc

SQL Server Version

Sys.dm_exec_requests.Command data type

Command text reported




2008 / 2008 R2







Fixing Broken SSRS Subscriptions

SQL Server Reporting Services provides functionality to automatically generate a report on a recurring basis. These reports can then be emailed or written to a file. The method for doing this is called a subscription. It’s common for a developer to create a report and then create a subscription that sends that report to the business group or resource that requested it.

Problems come about, however, when the developer leaves the company. When this happens, most likely, his or her Active Directory account will be disabled. As soon as that happens, all subscriptions that were created by the developer stop working. This is because the developer was the owner of the subscription. Since the account is disabled, the subscription no longer has access to the database and SSRS server. To make matters worse, currently there is no way to change the owner of a subscription using the SSRS user interface. That means to fix this, DBAs are stuck manually deleting the subscriptions and recreating them using an account that won’t be disabled in the future.

But there is a better way. We can go into the tables of the report server database and change the owner of the subscription. This information is stored in the OwnerID field of the Subscriptions table in the ReportServer database. Here are the first two columns of that table:


For our purposes, these are the only columns we need to worry about. You’ll notice both the SubscriptionID and OwnerID columns are uniqueidentifier datatypes. SubscriptionID is the primary key for the table and OwnerID is a foreign key to the Users table. The User table looks like this:


So to change the owner of a subscription, we just need to change the value in the Subscription.OwnerID field to a UserID value from the Users table that corresponds to an Active Directory account that is not disabled.

As an infrequently performed task, this isn’t too bad to do manually. However, if you are at a large company that has many SSRS developers, lots of subscriptions, and a normal to high employee turnover rate, this could become quite a hassle.

So, like any good DBA, I looked for a way to automate this process. I came up with this methodology:

  • Weekly, scan the Subscriptions table and get a list of the OwnerIDs for all subscriptions.
  • Cross-reference that list to the Users table to find the Active Directory accounts of those owners (stored in the Users.UserName field)
  • Query Active Directory to see if those accounts are still valid.
  • If they are not, update the Subscriptions.OwnerID field to use a valid Active Directory account.
  • Log the original and replacement values of any data that changed, as well as the date of the change.

Querying Active Directory via T-SQL can be done by using a linked server. Details can be found here. Note that in order to use this method, your server must be configured to allow ad hoc distributed queries.

Before I get to the code that implements this, there are a couple of assumptions that I need to point out. If your environment does not share these assumptions, you’ll need to tweak the code.

  1. In our Active Directory environment, accounts are disabled in two ways:
    1. If the account belonged to an employee who left, the account is moved to an AD organization unit (ou) called “Disabled Accounts”
    2. If the account belonged to a temporary employee, the account is deleted, meaning it is no longer in AD at all.
  2. We created a dedicated AD account that we use solely for a subscription owner. This is the account that will become the replacement subscription owner. You could use the account of another developer or DBA, or any other active AD account you want, however.
  3. This script assumes a single domain. If your environment utilizes multiple domains, you’ll need to modify the script.
  4. You’ll need to modify the @SQLString variable in two places to refer to your Active Directory environment in the OPENROWSET command.
  5. Our database servers are case-insensitive. I have not tested this script on a case-sensitive server. I tried to be consistent in my typing, but I make no guarantees.
  6. Replace items in <>’s with values appropriate to your environment.

Some notes on the data in the tables: As mentioned, we will be replacing the disabled owner account with a different account in the Users table. In order to exist in this table, that account will need to access SSRS and create a report or subscription first. (The report or subscription can then be deleted. The account just needs to create something first for SSRS to create an entry in the Users table.)  Also, the UserName field of the Users table stores the AD account in the format “Domain\Account”. When we query Active Directory via a linked server, we’ll need to strip off the domain prefix.

Finally, here is the code. The high-level overview of how it works is:

  • Check the status of the allow ad hoc distributed queries option on the server. The code will make a note of the setting when it first runs and will return it to the original value when complete. If this option is disabled, it enables it.
  • The account that will be used as the new subscription owner is hard coded. You should make sure this account has an entry in the ReportServer.dbo.Users table. Enter the account in the script in the format “Domain\Account” and assign it to the @NewSubOwner variable.
  • A list is made of all subscription owners.
  • The entries in that list are checked to see if they are in the “Disabled Accounts” Active Directory ou. Subcriptions owned by those accounts will be changed to the new subscription owner.
  • The entries in the list are checked to see if they are in Active Directory at all.  Subscriptions owned by accounts that are not will be changed to the new subscription owner.
  • Log the subscription IDs and old and new subscription owner IDs to a log table.
  • Update the Subscriptions table.
  • Reset the allow ad hoc distributed queries server option to what it was before we started.
  • Perform log table maintenance by deleting entries older than 3 months.

(All the single quotes and backslashes appear to be too much for my text coloring plug-in to handle, so the text coloring is off on the blog. But if you paste the code into SSMS, you will be ok.)

USE ReportServer

Procedure to check for SSRS subscriptions owned by AD accounts that are no longer active.
(If account is no longer active, subscriptions will stop.) This will change those
subscriptions to a new user.

	 SJS 7-21-14	Initial release


 First, check status of ad hoc distributed queries. If disabled, enable it.

 All changes are logged to ReportSubscriptionOwnerChangeLog and
	retained for 3 months

 Reset status of ad hoc distributed queries to what it was before this ran

 Based on




SET @NewSubOwner = '<Domain>\<Account>'

SELECT  @NewOwnerID = UserID
FROM    Users
WHERE   UserName = @NewSubOwner

/* Tables used:
		##SubscriptionOwners - accounts that own report subscriptions
		##DisabledAccounts - subscription owners that are in the AD 'Disabled Accounts' ou
		##InAD - subscription owners that are in AD (will contain records in above table too)
		##AdHocDistributedQueries - used to track status of SQL Server configuration so it can
			be reset to original value after execution
		##SubOwnersToChange - list of subscription owners that we need to change

/* House keeping - set up our tables */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
   DROP TABLE #Results

IF OBJECT_ID('tempdb..##DisabledAccounts') IS NOT NULL
         DROP TABLE ##DisabledAccounts

CREATE TABLE ##DisabledAccounts
        name VARCHAR(500)
       ,displayname VARCHAR(500)
       ,givenname VARCHAR(500)
       ,distinguishedname VARCHAR(500)
       ,samaccountname VARCHAR(100)

         DROP TABLE ##InAD

        name VARCHAR(500)
       ,displayname VARCHAR(500)
       ,givenname VARCHAR(500)
       ,distinguishedname VARCHAR(500)
       ,samaccountname VARCHAR(100)

IF OBJECT_ID('tempdb..##SubscriptionOwners') IS NOT NULL
         DROP TABLE ##SubscriptionOwners

CREATE TABLE ##SubscriptionOwners (SubOwner VARCHAR(100))

IF OBJECT_ID('tempdb..##AdHocDistributedQueries') IS NOT NULL
         DROP TABLE ##AdHocDistributedQueries

CREATE TABLE ##AdHocDistributedQueries
        name VARCHAR(200)
       ,minimum TINYINT
       ,maximum TINYINT
       ,config_value TINYINT
       ,run_value TINYINT

IF OBJECT_ID('tempdb..##SubOwnersToChange') IS NOT NULL
         DROP TABLE ##SubOwnersToChange

CREATE TABLE ##SubOwnersToChange (SubOwner VARCHAR(260))

/* Start processing */

/* Get list of distinct subscription Owners */

INSERT  INTO ##SubscriptionOwners
                REPLACE(u.[UserName], '<Domain>\', '')
        FROM    Users u
               ,subscriptions s
        WHERE   u.UserID = s.OwnerID

/* Check value of Ad hoc distributed queries so we can reset it to its
	original state when done */

/* Make sure we can see advanced options */

EXEC sp_configure
    'show advanced options'

INSERT  INTO ##AdHocDistributedQueries
        EXEC sp_configure
            'ad hoc distributed queries'

IF EXISTS ( SELECT  run_value
            FROM    ##AdHocDistributedQueries
            WHERE   run_value = 0 )
         EXEC sp_configure
            'ad hoc distributed queries'

DECLARE SubscriptionOwnersCursor CURSOR
        SELECT  subOwner
        FROM    ##SubscriptionOwners

OPEN SubscriptionOwnersCursor
FETCH NEXT FROM SubscriptionOwnersCursor
INTO @SubOwner

WHILE @@fetch_status = 0
/* See if any of those subscriptions owners are in the disabled accounts
	Active Directory ou. This handles people who were employees	but left */

            SET @SQLString = 'INSERT INTO ##DisabledAccounts
				SELECT name, displayname, givenname, distinguishedname, samaccountname
				FROM OPENROWSET(''ADSDSOObject'',''adsdatasource'';''<AD Account>'';''<password>'',
				''SELECT name, displayname, givenname, distinguishedname, samaccountname
				FROM ''''LDAP://<fqdn>/ou=Disabled accounts,dc=<your dc>,dc=<your dc>,dc=<your dc>''''
				WHERE samaccountname = ''''' + @SubOwner
                + ''''''')'

			--SELECT @SQLString
            EXEC (@SQLString)

/* Holy crap, that is a lot of single quotes.
   Resulting string should look like:
		'SELECT name, displayname, givenname, distinguishedname, samaccountname
		FROM ''LDAP://<fqdn>/dc=<your dc>,dc=<your dc>,dc=<your dc>''
		WHERE samaccountname = ''<account name>''')

/* See if any of those subscriptions owners are in Active Directory
	at all. This handles people who were never full time employees.
	This will find users that are _in_ AD and later we will exclude them
	from our list of users to change, leaving us with users _not_ in AD.
            SET @SQLString = 'INSERT INTO ##InAD
				SELECT name, displayname, givenname, distinguishedname, samaccountname
				FROM OPENROWSET(''ADSDSOObject'',''adsdatasource'';''<AD Account>'';''<password>'',
				''SELECT name, displayname, givenname, distinguishedname, samaccountname
				FROM ''''LDAP://<fqdn>/dc=<your dc>,dc=<your dc>,dc=<your dc>''''
				WHERE samaccountname = ''''' + @SubOwner
                + ''''''')'

            EXEC (@SQLString)

            FETCH NEXT FROM SubscriptionOwnersCursor INTO @SubOwner

CLOSE	SubscriptionOwnersCursor
DEALLOCATE SubscriptionOwnersCursor

/* Now look at the data we gathered from AD and translate that into
	subscription owners we need to change */

/* Users that left */
INSERT  INTO ##SubOwnersToChange
        SELECT  name
        FROM    ##DisabledAccounts

/*Users that never were FTEs */
INSERT  INTO ##SubOwnersToChange
        SELECT  SubOwner
        FROM    ##SubscriptionOwners
        WHERE   SubOwner NOT IN (SELECT samaccountname
                                 FROM   ##InAD)

/* Now do the actual updating of subscriptions */

DECLARE SubOwnersToChangeCursor CURSOR
        SELECT  SubOwner
        FROM    ##SubOwnersToChange

OPEN SubOwnersToChangeCursor
FETCH NEXT FROM SubOwnersToChangeCursor
INTO @OldSubOwner

WHILE @@fetch_status = 0
            SET @OldSubOwner = '<Domain>\' + @OldSubOwner

            SELECT  @OldOwnerID = UserID
            FROM    dbo.Users
            WHERE   UserName = @OldSubOwner

            INSERT  INTO ReportSubscriptionOwnerChangeLog
                    SELECT  SubscriptionID
                    FROM    Subscriptions
                    WHERE   OwnerID = @OldOwnerID

            UPDATE  Subscriptions
            SET     OwnerID = @NewOwnerID
            WHERE   OwnerID = @OldOwnerID

            FETCH NEXT FROM SubOwnersToChangeCursor
			INTO @OldSubOwner

CLOSE SubOwnersToChangeCursor
DEALLOCATE SubOwnersToChangeCursor

/* Set ad hoc distributed queries back to original value */

IF EXISTS ( SELECT  run_value
            FROM    ##AdHocDistributedQueries
            WHERE   config_value = 0 )
         EXEC sp_configure
            'ad hoc distributed queries'

/* For testing
SELECT * FROM ##DisabledAccounts
SELECT * FROM ##SubscriptionOwners
SELECT * FROM ##AdHocDistributedQueries
SELECT * FROM ##SubOwnersToChange

/* Clean up after ourselves */

DROP TABLE	##SubscriptionOwners
DROP TABLE	##DisabledAccounts
DROP TABLE	##AdHocDistributedQueries
DROP TABLE	##SubOwnersToChange

/* Maintain log table */

DELETE  FROM ReportSubscriptionOwnerChangeLog

Note: I am by no means an expert on querying Active Directory with the OPENROWSET command. There are several limitations and things to watch out for when doing this. Refer to the bottom of this post for more info.