You are here: Home > Sample Code
Archive | Sample Code RSS feed for this section

Filling in Data Potholes Redux: Tally Tables vs CTEs

by on January 4, 2011

In A Previous Installment

Re-Ducks

… our heroine (that’s me) rediscovered CTEs, specifically in the recursive style. That was in my post “Filling in Data Potholes with Recursive CTEs.”

To recap: I was working on a problem with gaps in temporal data. The basic scenario was:

Imagine that you are writing a script that looks at data grouped by the minute. You notice that there are no rows for some minutes, and you’d like to display a value when that is the case, probably showing a count of zero.

For the particular problem I was looking at, I was using small datasets and generating a list of all the valid dates with a recursive CTE performed well for me.

From the Comments

The best thing about blogging is not really sharing what you know: it’s getting to learn more. You get to learn from the process of writing the blog, and also if you are lucky enough to get comments from readers.

I had a couple of comments that mentioned performance, and Brad Shulz ( blog | sadly not on Twitter ) also linked me up to this most interesting post he did on recursion.

CTEs vs Tally Tables

Brad’s comment and post inspired me to do a little rewriting to put together a tally table style solution to the example I gave, scale it out in a few different ways, and collect and publish performance comparisons.

Why? Well, it’s interesting.

Creating Some Data

I didn’t do anything fancy for this. I took the same query I had previously to generate some data with gaps, and just modified it a bit so I could add in a small amount of data three months out, six months out, one year out, and two years out. This created datasets that were mostly “gap” for these future periods, but still made the query return a lot of rows.

--Run this with @monthOffset values: 0, 3, 6, 12, 24
--Run a trial each time.
DECLARE @monthOffset TINYINT = 0

--Let's create some data with some gaps

DECLARE @startDate DATETIME2(0)= DATEADD(MM, @monthOffset, '1/1/2011')

IF OBJECT_ID('dbo.MyImperfectData', 'U') IS NOT NULL
    AND @monthOffset = 0
    BEGIN
        DROP TABLE dbo.MyImperfectData
    END

IF OBJECT_ID('dbo.MyImperfectData', 'U') IS NULL
    CREATE TABLE dbo.MyImperfectData
        (
          ItemDate DATETIME2(0) ,
          ItemCount SMALLINT ,
          CONSTRAINT cx_ItemDate_MyImperfectData UNIQUE CLUSTERED ( ItemDate )
        )

INSERT  dbo.MyImperfectData
        ( ItemDate, ItemCount )
VALUES  ( DATEADD(mi, 0, @startDate), 12 ),
        ( DATEADD(mi, 1, @startDate), 3 ),
        ( DATEADD(mi, 2, @startDate), 6 ),
        ( DATEADD(mi, 3, @startDate), 12 ),
        ( DATEADD(mi, 4, @startDate), 24 ),
        ( DATEADD(mi, 5, @startDate), 1 ),
		-- Gap where 6 would be
        ( DATEADD(mi, 7, @startDate), 122 ),
        ( DATEADD(mi, 8, @startDate), 1 ),
        ( DATEADD(mi, 9, @startDate), 1244 ),
        ( DATEADD(mi, 10, @startDate), 23 ),
        ( DATEADD(mi, 11, @startDate), 12 ),
        ( DATEADD(mi, 12, @startDate), 24 ),
        ( DATEADD(mi, 13, @startDate), 27 ),
        ( DATEADD(mi, 14, @startDate), 28 ),
		--Gap where 15, 16, 17 would be
        ( DATEADD(mi, 18, @startDate), 34 ),
        ( DATEADD(mi, 19, @startDate), 93 ),
        ( DATEADD(mi, 20, @startDate), 33 ),
        ( DATEADD(mi, 21, @startDate), 65 ),
        ( DATEADD(mi, 22, @startDate), 7 ),
        ( DATEADD(mi, 23, @startDate), 5 ),
		--Gap where 24 would be
        ( DATEADD(mi, 25, @startDate), 4 ),
        ( DATEADD(mi, 26, @startDate), 6 ),
        ( DATEADD(mi, 27, @startDate), 7 ),
        ( DATEADD(mi, 28, @startDate), 77 ),
        ( DATEADD(mi, 29, @startDate), 94 ) ;

The Tally Table Approach

For this test I created a temporary tally table with two million rows. I used the method to populate the table which Jeff Moden recommends in his article, The ‘Numbers’ or ‘Tally’ Table: What it is and how it Replaces a Loop:

IF OBJECT_ID('tempdb..#tally') IS NOT NULL
    DROP TABLE #tally
BEGIN
    SELECT TOP 2000000
            IDENTITY( INT,1,1 ) AS N
    INTO    #Tally
    FROM    Master.dbo.SysColumns sc1 ,
            Master.dbo.SysColumns sc2

    CREATE UNIQUE CLUSTERED INDEX cx_Tally_N ON #Tally(N)
END

One thing to note with the tally table approach: you need to make sure you have enough numbers in the tally table to support your needs, or you’ll be missing rows in results. For production usage, you probably want to go well above 2 million if you’re using this for any scale.

To create the tally table on the fly with this number of rows, it took 4,438 ms of CPU time.

For fun, I used a CTE when I wrote my query to display the data for the Tally table, also. In this case, it’s just not a recursive CTE. A derived table could have been used just as well, but I think it’s more readable with the CTE.

WITH    TallyCTE
          AS ( SELECT   DATEADD(mi, t.N, limits.MinDate) AS dayDate
               FROM     #Tally t
               JOIN     ( SELECT    MIN(ItemDate) AS MinDate ,
                                    MAX(ItemDate) AS MaxDate
                          FROM      dbo.MyImperfectData AS imd
                        ) limits ON t.N <= DATEDIFF(mi, MinDate, MaxDate)
             )
    SELECT  tcte.dayDate ,
            CASE WHEN Itemcount IS NULL THEN '[Missing Row]'
                 ELSE ''
            END AS ColumnDescription ,
            COALESCE(ItemCount, 0) AS ItemCount
    FROM    TallyCTE tcte
    LEFT OUTER JOIN dbo.MyImperfectData AS d ON tcte.dayDate = d.ItemDate
    ORDER BY tcte.dayDate

The Recursive CTE Approach

This was the same as last time– just recapping it here for anyone playing along at home:

DECLARE @startDate DATETIME2(0) ,
    @endDate DATETIME2(0) ;

SELECT  @startdate = MIN(ItemDate), @endDate = MAX(ItemDate)
FROM    dbo.MyImperfectData ;

WITH    MyCTE
          AS ( SELECT   @startDate AS MyCTEDate
               UNION ALL
               SELECT   DATEADD(mi, 1, MyCTEDate)
               FROM     MyCTE
               WHERE    MyCTEDate < DATEADD(mi, -1, @endDate) )
    SELECT  MyCTEDate, CASE WHEN Itemcount IS NULL THEN '[Missing Row]'
                            ELSE ''
                       END AS ColumnDescription,
            COALESCE(ItemCount, 0) AS ItemCount
    FROM    MyCTE
            LEFT OUTER JOIN dbo.MyImperfectData ld
                ON MyCTE.MyCTEDate = ld.ItemDate
    ORDER BY MyCTEDate
OPTION  ( MAXRECURSION 0 ) ;

Notes on Method

I tested this on a virtual machine on my laptop, which is (definitely) not a production environment! However, I wasn’t doing much else on the laptop at the time, and I ran each test a couple of times to make sure results were all clearly in the right ballpark.
I cleaned out the buffer pool (DBCC DROPCLEANBUFFERS) and the procedure cache (DBCC FREEPROCCACHE) before each query.

The Results

Here is what I saw:

Don’t forget that I was seeing 4,438 ms of CPU time to create my tally table itself– if you truly couldn’t persist objects, this might be a concern for you. However, the numbers in this table really justify creating persisting the tally object permanently.

CPU time is 8 times faster on average with the tally method when we get above 125K rows. The minimal logical reads, and the ability for read-ahead reads to be used, is also convincing.

What about the Execution Plans

For the most part, I’m just going to link yet again to Brad’s post. Read about the Stack Spool operator!

But I will note this: when I looked at execution plans for this, I noticed that subtree cost estimates for the CTE query were .0148, and estimates for the tally table approach were 61.848. (This is from the two year batch.) The statistical estimates from the tally table approach were just far more accurate. This makes perfect sense, as the optimizer had statistics available to it to help it out: they were on the Tally table itself.

In Summary

I can’t really think of a situation in which I’ll use the recursive CTE for this specific issue. And after thinking about the stack spool operator, I’ll try to think differently about how to construct queries in general.

However, I really enjoyed writing these posts.

Comments { 6 }

Filling in Data Potholes with Recursive CTEs

by on December 23, 2010

Data: it can break your foot.

Imagine that you are writing a script that looks at data grouped by the minute. You notice that there are no rows for some minutes, and you’d like to display a value when that is the case, probably showing a count of zero.

In thinking about this problem this week, I spent some time getting to know CTEs (Common Table Expressions) again. And I came to the conclusion that I should spend much more time with them. Maybe I won’t end up using them all the time, but I should be looking at them regularly as options when I’m writing queries.

Here’s the story of a handy way I found to work with this.

Let’s create some data

Our story starts with some data. It’s been lovingly scripted out, but it has a few holes.

CREATE TABLE dbo.MyImperfectData (
ItemDate DATETIME2(0) ,
ItemCount SMALLINT )
GO

INSERT  dbo.MyImperfectData ( ItemDate, ItemCount )
VALUES  ( '2010-12-01 00:00:00', 12 ),
( '2010-12-01 00:01:00', 3 ),
( '2010-12-01 00:02:00', 6 ),
( '2010-12-01 00:03:00', 12 ),
( '2010-12-01 00:04:00', 24 ),
( '2010-12-01 00:05:00', 1 ),
-- Gap where 6 would be
( '2010-12-01 00:07:00', 122 ),
( '2010-12-01 00:08:00', 1 ),
( '2010-12-01 00:09:00', 1244 ),
( '2010-12-01 00:10:00', 23 ),
( '2010-12-01 00:11:00', 12 ),
( '2010-12-01 00:12:00', 24 ),
( '2010-12-01 00:13:00', 27 ),
( '2010-12-01 00:14:00', 28 ),
--Gap where 15, 16, 17 would be
( '2010-12-01 00:18:00', 34 ),
( '2010-12-01 00:19:00', 93 ),
( '2010-12-01 00:20:00', 33 ),
( '2010-12-01 00:21:00', 65 ),
( '2010-12-01 00:22:00', 7 ),
( '2010-12-01 00:23:00', 5 ),
--Gap where 24 would be
( '2010-12-01 00:25:00', 4 ),
( '2010-12-01 00:26:00', 6 ),
( '2010-12-01 00:27:00', 7 ),
( '2010-12-01 00:28:00', 77 ),
( '2010-12-01 00:29:00', 94 )

CREATE UNIQUE CLUSTERED INDEX cxMyCTE ON dbo.MyImperfectData(ItemDate)

The data is at the minute level. We’re missing data for five minutes in this period– one three minute chunk, and two other minutes.

What’s the quickest way to show the missing rows?

At first I thought about querying the data itself to find what’s missing. This made my head hurt a bit, and seemed pretty expensive.

I thought about the fact that many data warehouse databases have calendar tables, where all sorts of information about months, days, years, hours, and minutes are normalized out into tables.

However, I didn’t have those types of tables around. For the scope of my problem I was dealing with short date ranges (and by short, I mean 3 hours) , and ideally I would not need to create a bunch of ancillary objects to fill in the gaps.

After some thinking, I realized that we can create a date time table at the minute level on the fly by using a recursive CTE.

Here’s a sample that counts out a few minutes:

WITH    MyCTE
AS ( SELECT   CAST('2010-12-01 00:00:00' AS DATETIME2(0)) AS [I can count!]
UNION ALL
SELECT   DATEADD(mi, 1, [I can count!])
FROM     MyCTE
WHERE    [I can count!] < DATEADD(mi, -1,
CAST('2010-12-01 00:10:00' AS DATETIME2(0))) )
SELECT  [I can count!]
FROM    MyCTE
OPTION  ( MAXRECURSION 0 ) ;

Our results:

Putting it all together

Taking the format of this CTE, we can change it to create a table with every minute in our time range.

We can then select from it and use a LEFT OUTER JOIN to our table with data, and use the CTE dates to fill in the gaps.

DECLARE @startDate DATETIME2(0) ,
@endDate DATETIME2(0) ;

SELECT  @startdate = MIN(ItemDate), @endDate = MAX(ItemDate)
FROM    dbo.MyImperfectData ;

WITH    MyCTE
AS ( SELECT   @startDate AS MyCTEDate
UNION ALL
SELECT   DATEADD(mi, 1, MyCTEDate)
FROM     MyCTE
WHERE    MyCTEDate < DATEADD(mi, -1, @endDate) )
SELECT  MyCTEDate, CASE WHEN Itemcount IS NULL THEN '[Missing Row]'
ELSE ''
END AS ColumnDescription,
COALESCE(ItemCount, 0) AS ItemCount
FROM    MyCTE
LEFT OUTER JOIN dbo.MyImperfectData ld
ON MyCTE.MyCTEDate = ld.ItemDate
ORDER BY MyCTEDate
OPTION  ( MAXRECURSION 0 ) ;

And there we have it! No gaps:

No gaps allowed.

Use Cases

Check out the comments! In my initial posting, I didn’t say enough about where this is best applied, and how this scales.

I think this is mostly a party trick, but it’s also a nice simple example of recursion that got me thinking about CTEs.  And while there are some situations where it can come in useful, it doesn’t scale up to large date ranges. (Check out Brad Schulz’ post on recursive CTEs here.)

So in other words, this may be helpful in some ad-hoc situations.

However, looking at the “pseudo-recursive” parts of Brad’s post, I really feel a follow-up post or two coming on.

Comments { 6 }

Select * from dbo.FAIL: invalid metadata in views and user defined functions

by on December 16, 2010

This week a question on the Twitter #sqlhelp hash tag reminded me of a detail of SQL Server that I learned the hard way, but forgot to blog about. The question was:

SQLHelp in action

For those without images enabled (you anarchic luddites), that’s “Is there a good reason to have a SQL view that is just select * from a table with no filtering?”

Why Shouldn’t You Do That?

There are things you should know about how SQL Server handles metadata. When you update the schema for an underlying object (such as adding or removing a column in a table), the metadata for referencing objects is not automatically updated.

This can impact you whether or not you are using SELECT *. However it is far more likely to impact you, and to impact you more widely, when you have a practice of  using SELECT * in your object definitions for views, functions, etc.

Once Upon a Time…

There was a team of people working on optimizing their schema. Columns were being added and removed from several tables. The changes were checked in, tested, and handed off to operations for deployment. The operations DBA verified which changes were to replicated tables and validated that changes were approved by owners of subscriber systems. The change was put through pre-production and the change management system  appropriately.

Thirty minutes after the change went through to production, users of tools on a downstream system began to complain about large volumes of errors in the tool. It was discovered that many databases on the instance with the replication subscriber  had views and table valued functions referencing the article, using the “Select * syntax”. With the removal of the columns, literally hundreds of these views were returning 5402 errors, even though the users didn’t specifically need the columns which had been removed.

After the issue was identified, the operations team was able to resolve the incident by updating the metadata on the views and functions with the sp_refreshsqlmodule stored procedures.

For future schema changes to replicated tables, a script looping through all views and functions was used. This tended to be a bit problematic occasionally, as many users had longrunning queries using the impacted views and functions.

Learn More

To learn more, check out my sample script below, and also these MSDN articles on the stored procedures you use to update metadata:

Note that sp_refreshsqlmodule can also be used for views, so you likely want to just standardize with it.

Remember:

  • Check your replication subscrbiers: If you’re changing the schema of replicated objects, you may need to update metadata for referencing objects in your subscriber databases, and in other databases on all instances where objects may be referencing the tables by three part names.
  • Prepare to be blocked: Your request to update metadata can and will be blocked by running processes using those objects. Make sure to plan for this and determine if and whether you should kill running processes or not to update the metadata.

Why Many People Don’t Know About This

If you’re used to controlling  your data access with stored procedures or ORM tools such as nHibernate, it’s easy to never quite learn this detail, or to forget it.

Stored procedures won’t usually have this issues because they recompile after the schema is changed on the underlying object, which automatically refreshes their metadata.

Alternatives

If you need to reference an object by a different name, create a synonym!

Example Script: views, functions, sprocs, synonyms, and metadata.

Here’s some sql code to walk through some basic examples of how this all behaves.

This script shows a simple example of how schema updates aren’t reflected in the metadata for views and table valued functions unless they are explicitly edited, and it also slows a basic example of a 5402 error.

--Create a base table for testing
create table dbo.LookAtMe (
	i int identity primary key,
	Msg nvarchar(128)
)
GO

--Add a row
insert dbo.LookAtMe (Msg) VALUES('Hi, how are you?')
GO

--Create a view looking at the table using select *
create view dbo.vImLookinAtYou AS
	SELECT *
	FROM dbo.LookAtMe
GO

--Create a table valued function looking at the table with select *
create function dbo.tvfImLookinAtYou ()
RETURNS TABLE
AS
RETURN
(
	SELECT *
	FROM dbo.LookAtMe
)
GO

--Create a procedure looking at the table with select *
create procedure dbo.prcImLookinAtYou
AS
	SELECT *
	FROM dbo.LookAtMe
GO

--create a synonym for dbo.LookAtMe
create synonym dbo.synImLookinAtYou
FOR dbo.LookAtMe

--Our view, function, and proc each return two columns, i and Msg
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO
exec dbo.prcImLookinAtYou
GO
select * from dbo.synImLookinAtYou
GO

--Now, let's add a column
alter table dbo.LookAtMe
add lookTime datetime
GO

--Our view and functions still work
--but they aren't returning the new column!
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO
--Our procedure, however does return the new column!
exec dbo.prcImLookinAtYou
GO
--Our synonym also lets us see the new column.
select * from dbo.synImLookinAtYou
GO

--Let's refresh the metadata for the view and function
exec sp_refreshview 'dbo.vImLookinAtYou'
GO
exec sp_refreshsqlmodule 'dbo.tvfImLookinAtYou'
GO

--Now we can see the new column in the view and function
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO

--Now let's remove a column from the table
alter table dbo.LookAtMe
drop column lookTime
GO

--Oh no!
--Our view and function both return error 4502:
--&quot;View or function ... has more column names specified than columns defined.&quot;
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO
--Our procedure is fine, though
exec dbo.prcImLookinAtYou
GO
--Our synonym is also fine. It's just a pointer.
select * from dbo.synImLookinAtYou
GO

--Let's refresh the metadata for the view and function.
exec sp_refreshview 'dbo.vImLookinAtYou'
GO
exec sp_refreshsqlmodule 'dbo.tvfImLookinAtYou'
GO

--Now they work again.
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO

--Clean up our objects
drop table dbo.LookAtMe
drop view dbo.vImLookinAtYou
drop procedure dbo.prcImLookinAtYou
drop function dbo.tvfImLookinAtYou
drop synonym dbo.synImLookinAtYou
GO
Comments { 1 }

Automation: Granting Read Perms for Developers

by on August 16, 2010

Yeah, you heard me.

Do you like to read?

“But Kendra, why would we want to grant developers read  permissions? And why would we automate  it? And at what point did you lose your mind?”

Well, Virginia, there may or may not be a Santa Claus, but there are a lot of developers and good reasons to give them read access on many SQL Server instances. In my world, it’s every instance in the pre-production environment, which is a couple  hundred and growing. And it ain’t just read they’re needing, they should be empowered with all of:

  • In master: View server state– so they can look at all those pesky spids they can block, and which may be causing problems they’re investigating.
  • In msdb:
    • db_datareader — so they can query things not easily seen through the GUI
    • SQLAgentReaderRole — so they can look at currently executing jobs and history through the GUI
  • In each user database:
    • db_datareader — for troubleshooting, troublemaking, and general read-i-ness
    • view definition — so they can see what they’re dealing with
    • showplan — so there ain’t no excuse if they’re causing issues, and also to help further with the troubleshooting

And of course a user needs to be created in msdb and the user databases for this all to work out. Things to look out for: SQL Server will let you create a login and apply database level permissions with T-SQL without the user being created, but then you’ll find it doesn’t work.

When you’re dealing with a large pre-production environment and a large group  of developers (> 75 total) who periodically change roles, this permission set can be a bit complicated to manage.

How I Rigged this Up

In my implementation, this script is in a job on an MSX master server, and our server build steps for pre-production servers include configuring  the instance as a TSX subscriber and adding the job. The job is scheduled to run daily, so if new databases are created, restored, or dropped and recreated, permissions will automatically be re-applied. I like using the MSX  job for the implementation because it’s easy for our DBA team to see the job on the instance and see exactly what it does, and if there’s any reason to exempt an instance then that’s very simple to do.

So with this solution, I don’t have to worry about the mechanics of applying read permissions. I just have to worry about making sure the right people are in the active directory group, and I can pass that task on to their managers.

Important: Because of how our active directory and firewalls are configured, I do not have to worry about  this job being accidentally deployed to a production server and granting read where it should not. Something to think  about! Don’t automate yourself into security violations. This was another reason I chose the relatively “transparent” solution of an MSX subscriber job rather  than other solutions.

The Script

Disclaimer: This script was tossed together quickly. If you read the story above, you know I wasn’t even working on it for production. It supports windows groups only  in its current form. (Please don’t use sql authentication to grant read. Domain groups are so much more auditable and controllable!) It grants permissions directly to a login and does not create a role in each database. This is for a good reason in my world, but the best practice is to create a role, so keep that in mind.

As always, test the stuffing out of this and edit as needed if you’d like to make use  of it. Continue Reading →

Comments { 2 }

SQL 2008 Agent Jobs – Tokens work in PowerShell!

by on December 2, 2009

The Joy of Tokens and PoSH

I have been working away building out servers in our new prod test environment, and automating as much as possible along the way with PowerShell. I  have to say that it’s been really fun and PoSH has brought back that loving feeling that I always had for Perl.  If a programming language can be friendly, PowerShell manages it. 

One thing I had the chance to test out yesterday was using SQL Agent tokens in PowerShell type SQL Agent steps. I am very happy to find that the tokens work just like I was  hoping they would. In other words, this works:

$instanceName = &quot;$(ESCAPE_SQUOTE(SRVR))&quot;
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

The SRVR token will return the connection name of the sql server with the instance name. This works on standalone and named instances, and for clustered instances you get netname\instancename.  So this is perfect for creating a sql server object and connecting to it.

Why would you want to do this? Why not just use TSQL?

The primary reason I currently have to use a PoSH step is to execute commands against the operating system. In this case, I needed to connect to my sql  instance and get some information about it to then use in the OS level commands I needed to run. I  have a specific example of this I’ll post in the coming days.

How Do I Test This Out?

If you’re new to PowerShell, find yourself a test box to play around on. It needs to have PowerShell and SQL 2005 or 2008 (the client tools/SSMS are enough, they provide the SMO assemblies). Open a PowerShell command prompt as administrator. To get yourself connected to a SQL Server Instance, it’s as simple as this:

#This loads up the SMO module which will be used to connect to SQL
[System.Reflection.Assembly]::LoadWithPartialName(&quot;Microsoft.SqlServer.SMO&quot;) | out-null

$instanceName = 'NETNAME\INSTANCE'; # or just &quot;COMPUTERNAME&quot; for the default instance

#this passes in the name you used above to create a new object
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

#this will show you a table of all the properties of your SQL Server.
$srv.Properties | format-table -auto

#this will show you a table of all the properties of tempdb on your SQL Server.
$srv.Databases['tempdb'].Properties | format-table -auto

More Information

Comments { 0 }

Automating SQL Local Security Policy Rights: PoSH and NTRights

by on November 12, 2009

The Basics on Local Security Policy Rights and SQL Server…

There are a couple of local security policy rights that are not granted by default in SQL Server setup that I’ve been setting manually for a few years now:

  • Lock Pages In Memory
    • Allows large page allocation
    • Prevents the SQL Server process from being paged out
  • Perform Volume Maintenance Tasks
    • Instant initialization on data files

It’s a fairly click-heavy process to add the permissions for these through the Local Security Policy GUI. I prefer that these permissions be granted to the local security group for sql server that’s created in Windows, and that really requires a lot of clicks, unless you can remember and enter a group name like this without any typos:

SQLServerMSSQLUser$servername$MSSQLSERVER  or SQLServerMSSQLUser$servername$INSTANCENAME

By the way, what is up with using the $ in the group names?  If someone can tell me, I would love to know.

Why Not Just Grant these Rights to the Domain Service Account?

If your SQL Server service is running under a domain account, you can grant these privileges to that account and it will work. However, I prefer to grant it to the local group for SQL Services for two main reasons.

  1. If you change the account for SQL Server services, everything will still work. You can go to another domain account, network service, whatever, you won’t have to remember to check these permissions. (And you won’t remember until you’ve been bitten by it about 5 times.)
  2. It’s consistent with the way SQL Server grants permissions itself. (See the rights granted to the local groups here).

What about Clusters?

Well, so much for consistency– clusters do not use the local groups! For clustered instances, we grant the permission to the domain account.

Still, I prefer to be consistent with the way that the product grants the perms on standalone instances, and I have found instances where the service account was changed and the admins either were not aware they needed to grant these permissions or forgot.

Anyway, it Should be Easy!

I always figured there was an easy way to do this. And lo and behold, there is.

I have a project to build out some new pre-production servers right now, so as I’m walking through the build steps I am looking for ways to further automate our configuration. For these two rights, I did some searching and found that using NtRights.exe seems to be the easiest way to grant these permissions. Whenever people ask about editing local security policy with powershell, the response seems to be, “Why aren’t you using a GPO?”  But that wouldn’t work for this case.

NtRights.exe, which is a W2K3 resource kit utility (available here) was created for just these tasks, and I can call it from a PowerShell script anyhoo.

So here is the super-quick, super-dirty 1.0 version of granting these privileges. This version assumes you only have one sql instance installed on the box (which is how we roll in my current environment– otherwise you could pull them into an array and loop).  You can also use NTRights against remote machines, by the way, but you’d have to get the name of the Sql Server group a bit differently in that case. Or just use your domain account, I’m not going to judge you. ;)

In my case this is designed to be run post-setup when we’re logged into the box anyway, and the operator reviews the output. NTRights prints what it is granting and the outcome.

The Script

#copy ntrights.exe
Robocopy \\deployserver\shareWhereNtRightsLivesl\ d:\installDir ntrights.exe
D:
cd D:\installDir

#get the name of the sql server local group
$sqlgroup= net localgroup|findstr SQLServerMSSQLUser

#if we haven't found a group with this name, default to our service account (should be a cluster)
if (!$sqlgroup) {$sqlgroup=&quot;DOMAIN\SERVICEACCOUNT&quot;}

$sqlgroup=$sqlgroup.Replace(&quot;*&quot;,&quot;&quot;)

.\Ntrights -u $sqlgroup +r SeLockMemoryPrivilege
.\Ntrights -u $sqlgroup +r SeManageVolumePrivilege
Comments { 2 }

Who's Using All that Space in TempDb, and What's their Plan?

by on August 27, 2009

Whatcha Doing in My TempDb???

This post contains a script that I adapted from the fantastic SQL Server Storage Engine Blog.

It comes in handy in my job all the time! Sometimes tempdb is filling up, but sometimes I just want to monitor the amount of tempdb and check out execution plans of heavy tempdb users while watching performance on a server. It just really comes in handy more frequently than I would have thought before I started using it.

Note: This script returns space used in tempdb only, regardless of the db context it’s run in, and it only works for tempdb.

Continue Reading →

Comments { 0 }

Using Last Backup Date to Make Sure You get a Full when You need one, and a Differential when you Don't

by on August 17, 2009

Today I was glancing at once of my servers and noticed the backup job was running later than normal. I haven’t been working with this server for long, so I glanced to check where the backup was writing to and checked the output directory. I found that a differential backup was being written, and that the differential backup from the day before was much larger than normal.

Having quick backup times on weekdays on this instance and also saving space on the backup server where possible are both priorities, so the backup job for this server was set to do a full backup on Saturday, and a differential backup every other day of the week.

However, there was a production incident on this instance on Saturday, and the backup job was canceled by the person on call. I didn’t receive a monitoring ticket (since it was a cancellation it didn’t throw an alert), and the person on call must not have checked the backup schedule or thought to mention it.

So today I thought about it a bit, and then recalled that there’s a way to check the last backup time. I wanted to use this so I could have the job do a check as to when the last successful full backup time was, and if it was long enough, to do a full backup no matter what. On this particular database, after more than a week the differentials get a bit out of hand and we need to get in a full– ideally the job would be smart enough to realize this on Sunday if the run on Saturday has an issue.

I’ve even written a script that checked last backup time before! But I thought, “Oh, this must be easy, I’ll just check the usual places for the last full backup date.”

And so I did. Since it shows on the database properties in management studio, it seemed like it must be someplace obvious. Here are some places that last backup time is not listed:

  • sys.databases
  • the databasepropertyfunction
  • the databasepropertyex function

Where is it? Well I finally dug it out of my existing script, and my memory. It’s in msdb, in the backupset table. And once I looked at the existing script, I was glad I did. I think I may brush it up and use it pretty soon– it does a check for last successfull backup time across multiple servers. That can be pretty handy to audit, particularly when you’re taking on responsibility for new servers and want to do a quick high level inventory. (Of course, for a quick check you could also write a query to loop through databases on an instance and use the multi-server execution feature in SSMS 10.)

So here’s the query I’m using to find the last full backup, complete with some pseudo-code explaining a bit about how it could be used…

Continue Reading →

Comments { 0 }
← Older posts
Newer posts →