Shaun J Stuart

Just another SQL Server weblog

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:

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

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

Example1

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:

MouseOverExample1

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:

SELECT *
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:

Example2

But the mouseover text now has some differences:

MouseOverExample2

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:

ExampleData1

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:

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

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

Example3

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

MouseOverExample3

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.

 

Share

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:

RESTORE VERIFYONLY
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,
		status,
		percent_complete,
		command,
		wait_type,
		wait_time,
		CAST(estimated_completion_time AS DECIMAL(20,3))/1000.0/60.0 AS EstimatedCompletionTimeInMinutes,
		last_wait_type
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

2005

Nvarchar(32)

RESTORE VERIFYON

2008 / 2008 R2

Nvarchar(16)

RESTORE HEADERON

2012

Nvarchar(32)

RESTORE HEADERONLY

Share

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:

SSRSSubTable

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:

SSRSUsersTable

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 http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx

 THIS ASSUMES ACCOUNTS ARE IN ONE DOMAIN!

*/

DECLARE @SQLString VARCHAR(500)
DECLARE @SubOwner VARCHAR(260)
DECLARE @OldSubOwner VARCHAR(260)
DECLARE @NewSubOwner VARCHAR(260)
DECLARE @OldOwnerID UNIQUEIDENTIFIER
DECLARE @NewOwnerID UNIQUEIDENTIFIER

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
   BEGIN
         DROP TABLE ##DisabledAccounts
   END

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

IF OBJECT_ID('tempdb..##InAD') IS NOT NULL
   BEGIN
         DROP TABLE ##InAD
   END

CREATE 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
   BEGIN
         DROP TABLE ##SubscriptionOwners
   END

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

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

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

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

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

/* Start processing */

/* Get list of distinct subscription Owners */

INSERT  INTO ##SubscriptionOwners
        (SubOwner)
        SELECT	DISTINCT
                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'
   ,1
RECONFIGURE

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

IF EXISTS ( SELECT  run_value
            FROM    ##AdHocDistributedQueries
            WHERE   run_value = 0 )
   BEGIN
         EXEC sp_configure
            'ad hoc distributed queries'
           ,1
         RECONFIGURE
   END

DECLARE SubscriptionOwnersCursor CURSOR
FOR
        SELECT  subOwner
        FROM    ##SubscriptionOwners

OPEN SubscriptionOwnersCursor
FETCH NEXT FROM SubscriptionOwnersCursor
INTO @SubOwner

WHILE @@fetch_status = 0
      BEGIN
/* 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
      END

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
        (SubOwner)
        SELECT  name
        FROM    ##DisabledAccounts

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

/* Now do the actual updating of subscriptions */

DECLARE SubOwnersToChangeCursor CURSOR
FOR
        SELECT  SubOwner
        FROM    ##SubOwnersToChange

OPEN SubOwnersToChangeCursor
FETCH NEXT FROM SubOwnersToChangeCursor
INTO @OldSubOwner

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

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

            INSERT  INTO ReportSubscriptionOwnerChangeLog
                    (SubscriptionID
                    ,OldSubOwner
                    ,NewSubOwner
                    ,OldSubOwnerID
                    ,NewSubOwnerID)
                    SELECT  SubscriptionID
                           ,@OldSubOwner
                           ,@NewSubOwner
                           ,@OldOwnerID
                           ,@NewOwnerID
                    FROM    Subscriptions
                    WHERE   OwnerID = @OldOwnerID

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

            FETCH NEXT FROM SubOwnersToChangeCursor
			INTO @OldSubOwner

      END
CLOSE SubOwnersToChangeCursor
DEALLOCATE SubOwnersToChangeCursor

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

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

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

/* Clean up after ourselves */

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

/* Maintain log table */

DELETE  FROM ReportSubscriptionOwnerChangeLog
WHERE   ChangeDate < DATEADD(m, -3, CURRENT_TIMESTAMP)

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.

Share

With the release of SP2 for SQL 2012, SQL Server Management Studio got a bug fix that results in a nice quality of life improvement. Since the initial release of SQL 2012, plain text in SSMS always showed up in teal, no matter what you set it to via the Tools --> Options --> Environment --> Fonts And Colors setting. This bug was filed in a Microsoft Connect item over two years ago. With SP2, it has finally been fixed. This isn't a big deal, but it does make things easier on my tired old eyes.

SSMS 2012 prior to SP2

SSMS 2012 prior to SP2

SSMS 2012 post SP2

SSMS 2012 post SP2

Share

I'm offering a summer school discount of 50% on my Udemy online SQL Server courses!

SQL101

Microsoft SQL Server 101

SQLCompression

​Understanding Data​ Compression In SQL Server

sql server maintenance plans

​SQL Server Maintenance Plans​

 

Share