Trigger Happy Rotating Header Image

Session Materials from Prairie DevCon West 2012

I had a great time presenting two sessions in Calgary last week.  For those who attended the sessions and for the folks in Saskatoon where I did the SQL 2012 session, here are the slides and demos.

Email me or leave a comment to let me know what you think!

 

QueryPlans

SQL2012WhatsNew

SQL Server 2012 Launch

Hey everybody – the next version of SQL server is going to be available on April 1.  Despite being April fool’s day, this release is no joke.  With features like AlwaysOn Availability groups and Columnstore indexes, this is a major release.  My favourite new thing is the enhancements to intellisense, but I can be strange that way…

There is a whole lot to get up to speed on for this release, and an excellent, fun and potentially rewarding way to do that would be to register for and log into the SQL Server 2012 launch event – it kicks off with live keynotes tomorrow (Wednesday, March 7).  Learn stuff, ask live experts questions and win prizes.

Enjoy!

Finding the nth Weekday of a given month

[Edited from original – my colleague pointed out that the method I was using for getting the weekday returned different results depending on the DATEFIRST setting – the new solution is actually cleaner!]

A colleague of mine asked me for a utility to find the nth weekday of the month given a date, a number and a word such as “Wednesday”. He further challenged me to see if I could do it in one statement.

So for example, a call such as this:

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Wednesday',3)

When run on any day in July, 2011, will return the following:

-----------------------
2011-07-20 00:00:00.000

(1 row(s) affected)

Which is the 3rd Wednesday of this month.  The date parameter serves only to identify the month (and year), so any day or time information can be ignored.  It is a fairly straightforward problem, and when thought of iteratively, straightforward solutions start coming to mind – but iterative thinking is not going to solve this in a single statement.

From a set-based perspective, you think of the set of dates that make up the month in question, then simply choosing the right one – so the first item of business is to get a set of all the dates for the month in question.  As with many solutions, this will require a numbers or tally table (simply a table with nothing but numbers in it).  Many people have such a table at their disposal, but if you don’t (and you don’t need very many numbers – here we’ll only need a maximum of 31) you can use master..spt_values as discussed in a previous post.  So starting with the first day of the month, if we add the numbers from our tally table in days, we end up with a list of days of the month (we’re clearly violating the one-statement rule, but stay tuned – we’ll bring it all together):

DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

--This will take off any time portion and set the value to the beginning of the month
SET @dt = DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0);

SELECT DATEADD(DAY,number-1,@dt) AS MonthDate
FROM MASTER..spt_values
WHERE number > 0
   
AND TYPE = 'P'
   
AND DATEADD(DAY,number-1,@dt) >= @dt
   
AND DATEADD(DAY,number-1,@dt) < DATEADD(MONTH,1,@dt)

MonthDate
-----------------------
2011-07-01 00:00:00.000
2011
-07-02 00:00:00.000
2011
-07-03 00:00:00.000
2011
-07-04 00:00:00.000
2011
-07-05 00:00:00.000
2011
-07-06 00:00:00.000
2011
-07-07 00:00:00.000
2011
-07-08 00:00:00.000
2011
-07-09 00:00:00.000
2011
-07-10 00:00:00.000
2011
-07-11 00:00:00.000
2011
-07-12 00:00:00.000
2011
-07-13 00:00:00.000
2011
-07-14 00:00:00.000
2011
-07-15 00:00:00.000
2011
-07-16 00:00:00.000
2011
-07-17 00:00:00.000
2011
-07-18 00:00:00.000
2011
-07-19 00:00:00.000
2011
-07-20 00:00:00.000
2011
-07-21 00:00:00.000
2011
-07-22 00:00:00.000
2011
-07-23 00:00:00.000
2011
-07-24 00:00:00.000
2011
-07-25 00:00:00.000
2011
-07-26 00:00:00.000
2011
-07-27 00:00:00.000
2011
-07-28 00:00:00.000
2011
-07-29 00:00:00.000
2011
-07-30 00:00:00.000
2011
-07-31 00:00:00.000

(31 row(s) affected)

So considering this set, we need only pull out the Wednesdays (or whatever) and count them.  We can pull out the “Wednesdays” one of two ways:  by using a filter with either DATENAME or DATEPART – in this case we’re expressing the weekday in “name” format so DATENAME makes the most sense – also, the number DATEPART returns depends on the DATEFIRST setting.  Once we find the weekday, we can index them with a row number:


DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

--This will take off any time portion and set the value to the beginning of the month
SET @dt = DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0);

SELECT DATEADD(DAY,number-1,@dt) AS MonthDate,
      
ROW_NUMBER() OVER(ORDER BY number) AS DayIndex
FROM MASTER..spt_values
WHERE number > 0
   
AND TYPE = 'P'
   
AND DATEADD(DAY,number-1,@dt) >= @dt
   
AND DATEADD(DAY,number-1,@dt) < DATEADD(MONTH,1,@dt)
    AND
DATENAME(dw,DATEADD(DAY,number-1,@dt)) LIKE @dow + '%'

MonthDate               DayIndex
----------------------- --------------------
2011-07-06 00:00:00.000 1
2011
-07-13 00:00:00.000 2
2011
-07-20 00:00:00.000 3
2011
-07-27 00:00:00.000 4

(4 row(s) affected)

We now have only the Wednesdays of the month, appropriately indexed.  All we have to do now is filter for the 3rd Wednesday.  Since we cannot use a windowing function in a where clause, we have to wrap this query in a CTE or some structure in order to be able to apply our filter:

DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

--This will take off any time portion and set the value to the beginning of the month
SET @dt = DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0);

WITH MonthDays AS
(
  
SELECT  DATEADD(DAY,number-1,@dt) AS MonthDate,
          
ROW_NUMBER() OVER(ORDER BY number) AS DayIndex
  
FROM MASTER..spt_values
  
WHERE number > 0
      
AND TYPE = 'P'
      
AND DATEADD(DAY,number-1,@dt) >= @dt
      
AND DATEADD(DAY,number-1,@dt) < DATEADD(MONTH,1,@dt)
       AND
DATENAME(dw,DATEADD(DAY,number-1,@dt)) LIKE @dow + '%'
)
SELECT MonthDate
FROM MonthDays
WHERE DayIndex = @num

MonthDate
-----------------------
2011-07-20 00:00:00.000

(1 row(s) affected)

So we’re done, right?  Well, there’s still the matter of getting it into one statement – it’s two statements now, not counting the declaration/assignments – you can factor the part that strips off the time and normalizes the root date into the statement, but it looks pretty messy…

DECLARE @dt DATETIME = GETDATE();
DECLARE @dow VARCHAR(100) = 'Wednesday';
DECLARE @num INT = 3;

WITH MonthDays AS
(
  
SELECT  DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) AS MonthDate,
          
ROW_NUMBER() OVER(ORDER BY number) AS DayIndex
  
FROM MASTER..spt_values
  
WHERE number > 0
      
AND TYPE = 'P'
      
AND DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) >= DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)
       AND
DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) < DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))
       AND
DATENAME(dw,DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))) LIKE @dow + '%'
)
SELECT MonthDate
FROM MonthDays
WHERE DayIndex = @num

And voila! – the answer in one statement.  Here is how I would do it in the form of a function.  I use a second CTE here to clean up some of the mess while still finding the answer in a single statement (there is also an alternate way of filtering for the current month here – normally you shouldn’t apply a function to the “left” side of a search argument, but a) we know that this is a small set and b) we have to apply DATENAME anyway, so we will definitely be scanning):

CREATE FUNCTION nth_weekday_of_month_fn (
  
@dt DATETIME,
  
@dow VARCHAR(10),
  
@num INT
)
RETURNS DATETIME
AS
BEGIN

DECLARE @RetDate DATETIME;

WITH MonthDays AS
(
  
SELECT DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) AS MonthDate
  
FROM MASTER..spt_values
  
WHERE number > 0
      
AND TYPE = 'P'
)
,
WeekDays AS
(
  
SELECT *, ROW_NUMBER() OVER(ORDER BY MonthDate) DayIndex
  
FROM MonthDays
  
WHERE YEAR(@dt) = YEAR(MonthDate)
       AND
MONTH(@dt) = MONTH(MonthDate)
       AND
DATENAME(dw,MonthDate) LIKE @dow + '%'
)
SELECT @RetDate = MonthDate
FROM WeekDays
WHERE DayIndex = @num RETURN (@RetDate) END
GO

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Wednesday',3)

Deadlock Scripts SDEC 2010

Here are the Scripts and slides for the presentation on deadlocks at SDEC 2010.  Enjoy!

DeadlocksSDEC2010

Index Review Scripts SDEC 2010

Welcome to SDEC 2010.  I hope you enjoy(ed) my session on how to do an index review.  Here are the scripts and slides.

IndexReviewSDEC2010

How to attach a database in Standby

It’s been awhile since I’ve written, but with summer coming to a close, I’m feeling the itch – and what better to start the season with than a problem that does not seem to have a clear solution:

Let’s say you want to move the physical files in a log shipping secondary that is in Standby mode.  You might think that a quick detach, move the files, reattach and it’s coffee time, right?  Well, no…  You’ll start by doing the detach, which SQL server will let you do, you’ll get everything where you want it, then for the big finish you’ll run attach – and you’ll probably be quite surprised to get:

Msg 5120, Level 16, State 101, Line 1

 

Unable to open the physical file “<your MDF file>”. Operating system error 5: “5(Access is denied.)”.

 

 

And you might be quite befuddled – and rightly so – you’ll see that the permissions on your files have been reset, so you correct them, but now you get:

Msg 1824, Level 16, State 1, Line 1

 

Cannot attach a database that was being restored.

 

And there doesn’t really seem to be anything you can do about that – panic might set in as you realize your DR database now appears to be a large, unusable pile of bits and you need to start log shipping all over again from your 3TB backup.

Note that if your database was in NORECOVERY mode (not standby), the UI would not give you the option to detach, but sp_detach_db will work just fine – as will the create for attach, but recovery will run and you will no longer be able to restore logs – you’ll definitely have to start with a full backup if you do this.

All is not lost, however.  The database can be “hacked back in” by creating a placeholder database that is in the same standby state, then switching the files while the DB is offline.  This is not exactly outlined in BOL, and it’s called “hacking in” because this is a hack – this is likely not a supported process, but I’ve seen some very well respected industry experts suggest it on an as-necessary basis – see Paul Randal’s post on it here.  Note that Paul recommends that the dummy database files be roughly the same size as the database you are hacking in – I’ve seen it work without this, but Paul knows much more than I do… The steps are as follows:

1) You should have at least 3 files: the database file (MDF), the log file (LDF) and the undo/standby file (BAK by default).  Since you’ve already detached them, you’ll have to find them by whatever means you have at your disposal.  If you can’t find them, then perhaps you’re the wrong person to be doing this…  Place them where you want them to be, but rename them.

2) Restore a database (any user database) with standby – the only requirement (Edit:  see Ben’s comment below about file ids) is that it have the same number and type of files as your main database (again, Paul suggest that they be of similar size as well) – the logical names don’t even have to match – you’ll need to name the database itself what you want your attached database to be named, and place the log, data and undo files wherever you want them to be when you’re done. It doesn’t matter what the contents of this database are as they will be overwritten – if you don’t have any small backups handy, either take a copy-only backup of a small database or create an empty database and take a backup of it

3) Other than the contents of course, this database should now look exactly like you want your attached database to look – it should be in standby mode with all the files in the right place and named the right names

4) Take the database offline – note that this works for standby databases – you don’t have to affect the availability of the rest of your server – but for NORECOVERY databases, you’ll have to bring down the service

5) Rename the files and put your files from step 1 in their place.  Check the file permissions as they may have been reset in the detach

6) Bring the database online

7) Assuming everything works, delete the renamed files from the placeholder database

That should do it.  I’ve tested it a number of ways on SQL 2008, and in a limited fashion on SQL 2005.  I highly recommend doing this with some scratch databases to familiarize yourself with the process before trying it on production.

Note that if all you want to do is get a copy of the database so that you can do something else with it and you don’t actually want to move it, you can simply set it offline, make copies of the files, bring it back online and hack the copies into another location.

Hope this helps.

Query Tuning and Index Review June 2010

Here are the scripts and slides for my session on Query Tuning and Index Reviews.  I hope you enjoy(ed) the session.

QueryTuningAndIndexReview

Bullet Dodging Scripts Prairie Devcon 2010

Here are the scripts for my bullet-dodging and spoon-bending session at Prairie DevCon Regina 2010.  Enjoy!

BulletSpoon

Do your T-SQL variables have the right values?

For some of us who are bewildered by even the code we write ourselves, this might be a question we ask ourselves in more circumstances than we’d like to admit, but what I’m talking about here is assigning values to a variable with a SELECT statement, and expecting the value to be null if the query didn’t find anything.  This can be particularly nefarious in cursor loops as I will show.  First, to set up the scenario, run the following script.  It creates two temp tables – one with 10 rows and one with 5:

IF OBJECT_ID('tempdb..#one') IS NOT NULL DROP TABLE #one
IF OBJECT_ID('tempdb..#two') IS NOT NULL DROP TABLE #two
CREATE TABLE #one (number INT IDENTITY (1,1))
CREATE TABLE #two (number INT IDENTITY (1,1))
GO
INSERT #one DEFAULT VALUES
GO 10
INSERT #two DEFAULT VALUES
GO 5
SELECT * FROM #one
SELECT * FROM #two

number
-----------
1
2
3
4
5
6
7
8
9
10
(10 row(s) affected) number
-----------
1
2
3
4
5
(5 row(s) affected)

Now we will use a cursor loop to go through the first (10-record) table and look for matching values in the second table – we should obviously only find 5 matching values – and display our findings.  This is what it *should* look like:

DECLARE @CR CURSOR, @Number INT, @Found INT
SET
@CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT
number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR
('Looking for number %i...',0,0,@Number)
  
SET @Found =(SELECT number FROM #two WHERE number = @Number)
  
IF @Found IS NOT NULL
      
RAISERROR('Found: %i',0,0,@Found)
  
FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking
for number 2...
Found: 2
Looking
for number 3...
Found: 3
Looking
for number 4...
Found: 4
Looking
for number 5...
Found: 5
Looking
for number 6...
Looking
for number 7...
Looking
for number 8...
Looking
for number 9...
Looking
for number 10...

Of course, it only found the five (by the way, please don’t take this as a recommendation to use cursors for this type of thing – I’m illustrating an unrelated point here…) How about the following, though – there is a subtle difference – do you see it right away?

DECLARE @CR CURSOR, @Number int, @Found int
SET
@CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT
number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR
('Looking for number %i...',0,0,@Number)
  
SELECT @Found = number FROM #two WHERE number = @Number
  
IF @Found IS NOT NULL
      
RAISERROR('Found: %i',0,0,@Found)
  
FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking
for number 2...
Found: 2
Looking
for number 3...
Found: 3
Looking
for number 4...
Found: 4
Looking
for number 5...
Found: 5
Looking
for number 6...
Found: 5
Looking
for number 7...
Found: 5
Looking
for number 8...
Found: 5
Looking
for number 9...
Found: 5
Looking
for number 10...
Found: 5

So “SELECT @Var =” and “SET @Var =” are the same thing, right? Well, it appears not.  Using the SELECT method, the variable is not resetting to NULL.  There is a simple explanation for this.  The SET method is setting the variable to the output of a query – that query did not return anything which is a missing value – the very definition of NULL – but the SELECT method is changing the variable as part of the query.  Since the query returns no rows, the variable assignment never takes place so the value of the variable does not change.

You may want the variable to change to NULL and you may not – so choose your method accordingly, but I have seen code that uses the SELECT method followed by a test for NULL clearly expecting the variable to be nulled if no record is found.

Exercise:  What happens if you use an aggregate like MAX() with the SELECT method – do you know what it will do before you try it?  Try it and see if you’re right (hint:  aggregate queries always return at least one row).

Be careful out there – the devil is in the details.


Query Tuning Scripts Prairie DevCon 2010

Here are the scripts from the session.  I hope you enjoyed it.  Sorry that I got a bit carried away and didn’t quite finish.  At the end of the demo script you’ll see that we were going to actually optimize a query…  But the tools, and what to look for are the most important piece anyway.  Feel free to post a comment or send me mail if you have any questions.  Enjoy the rest of the conference.

IntroQueryTuning