Archive | January, 2011

Winter Nerd-Cation: I’m Talking about Isolation Levels in Cleveland on February 5 (#SQLSat60)

Cerealizable: Breakfast Isolation Level

A few weeks ago I was talking with a friend about upcoming plans. Without thinking, I said:

“Oh, and I’m going to go to Cleveland to talk about Isolation Levels. And partitioning. In February.”

There was a  pause. My friend laughed. I believe she called me a nerd.

I took it as a compliment.

What Makes A Great Nerd-Cation

To me, the best, most relaxing vacation is a nerd-cation. I like to go someplace new, meet some new people, and ideally see some friends I don’t get to see often. I enjoy speaking and learning from other speakers. I like driving around and seeing an unfamiliar city. It gives my brain things to work on, but in a different way than I use it most of the time. (If I traveled for work, this might be a bit less vacation-y. Since I don’t, it’s all ice cream.)

If you’ve never tried this: you should! Make sure to add an extra day or two to the trip so you’re not in a rush. Take a copilot or go solo- it’s fun either way.

Note on terminology: I mean ‘Nerd’ in the nicest way possible. I’m proud of my inner nerd!

Why I Picked Cleveland

I submitted sessions to SQLSaturday #60 in Cleveland because I know two of the organizers, Allen White (b | t ) and Erin Stellato (b | t) are going to put on a really great event. I was really happy to be accepted to give sessions.

And although I have been to the midwest in the winter (taking my warmest hat and mittens!) I’ve never been to Cleveland.  Things I’m looking forward to trying to fit in: pierogi (oh how I love pierogi!!!), the Algebra Tea House, and the West Side Market.

Come to SQL Saturday Cleveland!

It’s going to be a blast. The schedule is full of good stuff.

My sessions are:

Table Partitioning: Evaluation, Planning, Options
Does SQL Server’s table partitioning feature offer performance improvements, manageability benefits, or both? Table partitioning is a significant investment: it’s important to understand many aspects of the feature to evaluate where it will be useful. This session will cover basic concepts of table partitioning. We’ll discuss where the feature can be applied, its benefits, and changes that partitioning may require to existing database schema. Special considerations for management of partitioned tables will be discussed, as well as potential changes to query optimization and plans

(NOLOCK) for YESFUN: Games with Isolation Levels

Understanding transaction isolation levels is critical for developing concurrent software as well as administering databases. Unfortunately most of us learn about transaction isolation early in our careers and don’t maintain an in-depth knowledge of different isolation levels. This talk will help! We’ll cover all isolation levels and discuss problems and benefits.

Check the event out on Twitter here: #sqlsat60

And get full information on SQLSaturday60 here.

Internals Matter: Why You Should Check Your Page Verify Settings, and I Should Go to Masters Immersion Training (SQL Skills Contest Entry)

This post is about two things:

1) Your Page Verification Settings are Critical

You should confirm you are running with the correct page verification settings on your SQL Server databases. It’s very important, and I’ll show you why.

2) I Should Attend the Awesome SQLSkills Master Immersion Event – Internals and Performance

My scripts to demo the importance of page verification settings are part of  my entry to win free registration to the SQLSkills Master Immersion Event on Internals and Performance in February. Read more about the training here.

Keep reading to see why I hope this helps me win.

Let’s Start with Page Verification

The default setting for the PAGE_VERIFY database option for SQL 2000 was TORN_PAGE_DETECTION. As Books Online explains here:

When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

SQL 2005 introduced a new page verify database option, CHECKSUM. This was the default for new databases, but the page verification option was not automatically changed when databases were migrated or upgraded from SQL 2000 to SQL 2005. Books Online explains that when you run with CHECKSUM…

the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

It is quite common to find databases on SQL 2005 instances and higher still using the TORN_PAGE_DETECTION setting for page verification. When a change to the setting is suggested, people naturally ask if the extra protection is worth the small amount of extra overhead of computing the checksum.

It certainly is! This post demonstrates why.

To Play Along At Home…

You may want to read my prior post on causing database corruption with a hex editor here. This post explains how to edit data in unsupported ways, and these techniques should never be used near a production server, or a database of any value at all. Make sure to wash your hands before and after using these practices, and having a change of clothes handy is also advisable.

You’ll also need to download the XVI32 hex editor by Christian Maas. No installer is necessary: download the zip file, then unzip all files to a directory and run XVI32.exe

Our Victim Database

Let’s say we have a database with entry data for a contest. The contest is, quite coincidentally, the SQLSkills Master Immersion Event on Internals and Performance in February!

Let’s create our database and schema for our contest entries.

USE master ;
IF DB_ID('CorruptMe') IS NOT NULL
    BEGIN
        ALTER DATABASE CorruptMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE CorruptMe
    END
CREATE DATABASE CorruptMe ;
GO
ALTER DATABASE CorruptMe SET PAGE_VERIFY TORN_PAGE_DETECTION ;
GO
USE CorruptMe ;
--This schema might have an issue or two ;) Just play along.
CREATE TABLE dbo.Contest (
    ContestId INT IDENTITY,
    ContestName NVARCHAR(256),
    CONSTRAINT cxContestId_Contest UNIQUE CLUSTERED (ContestId)
 )
CREATE TABLE dbo.ContestEntry (
    ContestEntryId INT IDENTITY,
    ContestId INT REFERENCES dbo.Contest ( ContestId ) ,
    FirstName NVARCHAR(256) ,
    LastName NVARCHAR(256) ,
    ContestStatus NVARCHAR(256),
    CONSTRAINT cxContestEntryId_ContestEntryId UNIQUE CLUSTERED (ContestEntryId)
)

INSERT  dbo.Contest ( ContestName)
VALUES ( '5-Day Internals and Performance class in Dallas, February 21-25')

INSERT  dbo.ContestEntry ( ContestId, FirstName, LastName, ContestStatus )
VALUES ( 1, 'Hello', 'Kitty', 'Win!' ),
	( 1, 'PePe', 'LePeu', 'Loss' ),
	( 1, 'Kendra', 'Little', 'Sigh' )

It looks like the contest has already been judged. How did everyone do?
SELECT c.ContestName, ce.FirstName, ce.LastName, ce.ContestStatus
FROM dbo.Contest c
JOIN dbo.ContestEntry ce ON c.ContestId=ce.ContestId

Well, it figures. Hello Kitty can be pretty persuasive. Congrats, Kitty.

But What If…

What if something happened to the data? The database is running with TORN_PAGE_DETECTION– let’s see what happens if we change some of the values  in the physical data (mdf) file with a hex editor.

First, take a look at the pages for dbo.ContestEntry:
DBCC IND ('CorruptMe', 'ContestEntry', 1)
On my instance, we have one data page in our clustered index (PageType1). It’s PagePID 147. Let’s see if we can make some changes on that page and if SQL Server will notice.

To do this, take the database offline, and figure out your physical file name and your offset:

USE master ;
ALTER DATABASE CorruptMe SET OFFLINE ;

SELECT physical_name FROM sys.master_files WHERE name = 'CorruptMe' ;
SELECT 147 * 8192 AS [My Offset]

Then run the XV134 Hex editor with administrative privileges, and open the file.
Use Address -> GoTo and enter your offset as calculated above. (In my case: 1204224).

Here’s the data as I found it on the page (click to view in a new window):

And here is the data after I made quite a few changes in the hex editor, just by typing in the right pane (click to view in a new window):

That couldn’t possibly work, could  it?

Let’s put the database back online and see.

ALTER DATABASE CorruptMe SET ONLINE
GO
USE CorruptMe
GO
SELECT c.ContestName, ce.FirstName, ce.LastName, ce.ContestStatus
FROM dbo.Contest c
JOIN dbo.ContestEntry ce ON c.ContestId=ce.ContestId

This returns the following. The values on the page aren’t just in a different order, they’re different than the original values:

What About When I Run the Next CheckDb?

Let’s see:
DBCC CHECKDB('CorruptMe')
This comes back clean. An excerpt from the output is:

What Would Happen if We Were Using Page Verify CHECKSUM?

You can see this by running through the demo again. This time, use CHECKSUM as your database PAGE_VERIFY option, either by default or by running this after it is created:
ALTER DATABASE CorruptMe SET PAGE_VERIFY CHECKSUM;
After editing the page as described in the hex editor, you will be able to bring the database back online successfully. However, as soon as you read the records from the dbo.ContestEntry table, you’ll get an 824 error for the page we edited, like this:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x54bc84f5; actual: 0x41298172). It occurred during a read of page (1:147) in database ID …

CheckDb will also alert you to the error the first time it is run.

The Moral of the Story Is: CHECKSUM is the Way to Go For Page Verify

This isn’t about security– this is about you knowing if changes to SQL Server’s files are happening outside of its control, whether by corruption or any other means.

It’s very easy to check if you’re using CHECKSUM. Take a look at your settings today, and make a plan to move to checksum for any databases which need it!

SELECT NAME
FROM sys.databases
WHERE page_verify_option_desc <> 'CHECKSUM'

Ideally you will include an index rebuild in your upgrade plan, or shortly thereafter– because the checksum needs to be written to each page, this doesn’t start working for a page  until it’s had a write.

For all sorts of more information, see Paul Randal’s post on Myths around Checksums.

Meanwhile, I’m Entering A Contest

Back to our contest for the SQLSkills Master Immersion Event on Internals and Performance in February (info).

My challenge was to express why I’d like to the go to the class, and why I’ll make the best use of the knowledge I gain.

I’d love to go because:

  • I’m passionate about learning about internals and performance. Hopefully I’ve demonstrated that here!
  • I still have a lot to learn, and there’s no replacement for a great classroom experience.
  • I know SQLSkills training is highly interactive and is a great place to ask questions and get real-world, in-depth answers.
  • This training will help me grow as a professional.

I’ll make the best use of the knowledge because:

  • I love using what I learn to explore more and write blog posts like this– it helps me learn more and share with the community.
  • I’m now the *only DBA* at a small, clever internet business. Every bit of expertise I can gain can help me work as part of a great team and make a real difference in our business.
  • I’m good at asking questions and helping others learn– I work hard to be a great fellow student and learn together with others.
  • I’ve clearly learned enough to be dangerous 😉

What I’d Like to Do at the Training

I would love to blog my way through my first immersion event. I certainly couldn’t (and wouldn’t) capture all the content we cover, but I’d like to capture what the experience is like. I’d also like to characterize how it  helps me think about, and work  with, SQL Server differently.

I hope whomever is selected as the winner chooses to share their experience.

Corrupting Databases for Dummies- Hex Editor Edition

Corruption is so ugly it gets a lolworm instead of a lolcat.

Let’s make one thing clear from the start:

This Post Tells You How To Corrupt a SQL Server Database with a Hex Editor in Gruesome Detail

And that’s all this post tells you. Not how to fix anything, just how to break it.

If you aren’t familiar with corruption, corruption is bad. It is no fun at all on any data, or any server, that you care about.

Where You (Possibly) Want To Do This

You only want to do this on a test database, in a land far far away from your customers, for the purpose of practicing dealing with corruption.

When things go badly, you want to  be  prepared. This post gives you the tools in a simple, step by step fashion, to create different types of corruption so that you can practice resolving them.

Big Disclaimer: Do not run this in production. Or anywhere near production, or anything important. Ever. Only use this at home, in a dark room, alone, when not connected to your workplace, or anything you’ve ever cared about. If you corrupt the wrong pages in a user database, you may not be able to bring it back online. If you corrupt a system database, you may be reinstalling SQL Server.

References, and Thanks to Paul Randal

Everything I’m doing here I learned from Paul Randal’s blog posts. It just took me a little bit to understand how to use the hex editor and make sure I was doing it properly, so I thought I’d put down the steps I used here in detail. If you’d like to go straight to the source:

First, Get Your Hex Editor

Download XVI32 by Christian Maas. No installer is necessary: download the zip file, then unzip all files to a directory and run XVI32.exe

Create a Database to Corrupt

For our adventure, our database is named CorruptMe. We’ll create a single table, insert some data, and create a clustered index and nonclustered index on it.

(Note: Data generation technique found on Stack Overflow, attributed to Itzik Ben-Gan.)

USE master;
IF db_id('CorruptMe') IS NOT NULL
BEGIN
	ALTER DATABASE CorruptMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE CorruptMe
END	

CREATE DATABASE CorruptMe;
GO

--Make sure we're using CHECKSUM as our page verify option
--I'll talk about other settings in a later post.
ALTER DATABASE CorruptMe SET PAGE_VERIFY CHECKSUM;

USE CorruptMe;

--Insert some dead birdies
CREATE TABLE dbo.DeadBirdies (
    birdId INT NOT NULL ,
    birdName NVARCHAR(256) NOT NULL,
    rowCreatedDate DATETIME2(0) NOT NULL )

;WITH
  Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1),
  Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),
  Pass2 AS (SELECT 1 AS C FROM Pass1 AS A, Pass1 AS B),
  Pass3 AS (SELECT 1 AS C FROM Pass2 AS A, Pass2 AS B),
  Pass4 AS (SELECT 1 AS C FROM Pass3 AS A, Pass3 AS B),
  Pass5 AS (SELECT 1 AS C FROM Pass4 AS A, Pass4 AS B),
  Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS NUMBER FROM Pass5)
INSERT dbo.DeadBirdies (birdId, birdName, rowCreatedDate)
SELECT NUMBER AS birdId ,
    'Tweetie' AS birdName ,
    DATEADD(mi, NUMBER, '2000-01-01')
FROM Tally
WHERE NUMBER <= 500000

--Cluster on BirdId.
CREATE UNIQUE CLUSTERED INDEX cxBirdsBirdId ON dbo.DeadBirdies(BirdId)
--Create a nonclustered index on BirdName
CREATE NONCLUSTERED INDEX ncBirds ON dbo.DeadBirdies(BirdName)
GO

Now we can take a look at the pages our table and nonclustered index got created on. I wanted to specifically corrupt a page in the nonclustered index on the DeadBirdies table. Of course if you wanted the clustered index, you could use index Id 1.
DBCC IND ('CorruptMe', 'DeadBirdies', 2)
I want to pick a data page for this nonclustered index, so I pick a PagePID where PageType=2. (The reference I use for DBCC IND is here.)

I pick PagePID 2784.

Note: If you’re following along, you may get a different PagePID if you use a different default fill factor.

Optional: Check out the page with DBCC PAGE

If you’d like to take a look at the page you’re about to corrupt, you can do so with the following command.

--Turn on a trace flag to have the output of DBCC PAGE return in management studio
--Otherwise it goes to the error log
DBCC TRACEON (3604);
GO
DBCC PAGE('CorruptMe', 1,2784,3);

Set the database offline

You must take your victim database offline to render it fully helpless accessible to your hex editor.

USE master;
ALTER DATABASE CorruptMe SET OFFLINE;

Also, get the name of your physical data file which you’ll open in your hex editor. Copy this to your clipboard.
SELECT physical_name FROM sys.master_files WHERE name='CorruptMe';
Figure out the starting offset of the page you want to corrupt. You do this simply by multiplying the page ID (PagePid) by 8192 (the number of bytes on a page).
SELECT 2784*8192 AS [My Offset]

It’s the Moment We’ve Been Waiting For: Trash That Page

Fire up your hex editor: run XVI32.exe.

Depending on your operating system, you may want to run this with elevated privileges / right click and “run as administrator”.

Open the database file by using File ? Open, and then the data file name you copied to the clipboard. (If you didn’t set the database offline, you’ll get an error that it’s in use. If you got an error that you don’t have permissions to view the file, make sure you do have permissions and that you ran XVI32.exe with elevated privileges.)

Go to the page you want to corrupt by using Address ? GoTo (or Ctrl + G), then paste in your Offset Value. You want to search for this as a decimal.

XVI43.exe will take to right to the beginning of that page.

You can see the ASCII representation of the data in the right pane. For our example, you should be able to see the word ‘Tweetie’ represented.

I like to put the cursor  in the right pane at the beginning of the word ‘Tweetie’. XVI32.exe will automatically move the cursor in the left pane, to the appropriate location.

You can corrupt the data  by editing in the right pane or left pane.

For my example, I am replacing the ASCII ‘T’ in the first occurrence of the word ‘Tweetie’ with an ‘S’. You can edit more, but a little tiny corruption goes a long way.

Save the file, and you’re done!

Admire Your Own Corruption

First, bring your database back online. If you correctly edited pages in the data, this should work just fine.

Note: If you corrupted critical system tables early in the database, this may not work! If so, go back to the steps above to identify a good page offset.
ALTER DATABASE CorruptMe SET ONLINE;
You can see the corruption in a couple of different ways. If you have checksums enabled on the database, you can see the corruption by reading the page with the data on it.

Since I corrupted a page in a nonclustered index in my example, I need to make sure I use that index. So I can see it with this query:

Use CorruptMe;
SELECT birdName FROM dbo.deadBirdies;

That returns this big scary error, which confirms I did indeed corrupt page 2784:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xb633a8e1; actual: 0xaeb39361). It occurred during a read of page (1:2784) in database ID 18 at offset 0x000000015c0000 in file ‘D:\BlahBlahBlah\CorruptMe.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

You can also see this by running a CHECKDB or CHECKTABLE command.
DBCC CHECKDB('CorruptMe')
An excerpt from its output:

Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:2784) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data), page (1:2784). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

Now Sit Back and Laugh Maniacally. And Then Fix It.

So, the whole point of this was probably to test something.

So take a moment to enjoy the fact that FOR ONCE you don’t have to panic when you see these errors, because it’s all part of your master plan.

Then go out and fix the corruption, and run your tests.

Read from the Right End of the Index: BACKWARD Scans

Optimizing queries is the most fun when you don’t need to add indexes. There’s nothing quite so nice as finding a way to make reading data faster, without slowing down writes or creating new data structures that need to be maintained.

Here’s one way you can use BACKWARD scans to do this.

The Scenario: Clustered index on an increasing integer, and you’d like recently created rows

This is a common enough situation: you have a table with a clustered index on an integer value which increases with each row. You have another column which records the date the row was created.

You’d like frequently query the most recently created rows over some period of time.

The table has very frequent inserts, so for performance reasons you want to use the minimal indexes required. (And in general, this is the best practice.)

Question: Do you need to add a nonclustered index on the column containing the date the row was created?

Answer: Maybe not!

Getting the right clustered index scan

Say we’re working with the following table, which we have filled with five million rows of Tweetie birds. (Note: This generation technique is a tally table population technique which I found on Stack Overflow, which is attributed to Itzik Ben-Gan.)

CREATE TABLE dbo.Birds (
    birdId INT NOT NULL ,
    birdName NVARCHAR(256) NOT NULL,
    rowCreatedDate DATETIME2(0) NOT NULL )
GO	

--Insert 5 million Tweetie birds
--Make them as if they were all created a minute apart.
;WITH
  Pass0 as (select 1 as C union all select 1),
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),
  Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT dbo.Birds (birdId, birdName, rowCreatedDate)
SELECT Number AS birdId ,
    'Tweetie' AS birdName ,
    DATEADD(mi, number, '2000-01-01')
FROM Tally
WHERE Number <= 5000000

--Cluster on BirdId. We won't add any other indexes.
CREATE UNIQUE CLUSTERED INDEX cxBirdsBirdId ON dbo.Birds(BirdId)

Say we would just like to see the maximum value in the rowCreatedDate column.

The most basic way to get this row is with this query:

SELECT MAX(rowCreatedDate)
FROM dbo.Birds

However, that leads to a table scan. We get lots of reads: 22,975 logical reads and 201 physical reads.

If we know we have a strong association between the BirdId column and the RowCreatedDate column, and that the highest ID in the table is the most recent row, we can rewrite the query like this:

SELECT MAX(rowCreatedDate)
FROM dbo.Birds
WHERE birdId = (SELECT MAX(birdId) FROM dbo.Birds)

This query still does a clustered index scan. But yet it does only 3 logical reads and 2 physical reads.

Looking in the execution plan, our query was able to use the extra information we provided it to scan the index backwards. It stopped when it had everything it needed, which was after a short distance– after all, it only needed recent rows, and those are all at one end of the table.

This backwards scan can be very useful, and can make using the MAX aggregate very useful.

But you usually need more than just the max value…

To see a bit more about how you extend this logic, compare these three queries:

Query A

This makes you think you need that non-clustered index: it does 22,975 logical reads, 305 physical reads, and 22968 read-ahead reads.

--Only run against a test server, not good for production
DBCC DROPCLEANBUFFERS

SELECT birdId, birdName, rowCreatedDate
FROM dbo.Birds
WHERE rowCreatedDate >= '2009-07-01 05:00:00'

Query B

We can introduce the backwards scan by adding an ORDER BY BIrdId DESC to the query. Now we get 23019 logical reads, 47 physical reads, and 22960 read-ahead reads.

--Only run against a test server, not good for production
DBCC DROPCLEANBUFFERS

SELECT birdId, birdName, rowCreatedDate
FROM dbo.Birds
WHERE rowCreatedDate >= '2009-07-01 05:00:00'
ORDER BY birdid desc

Query C

The this last query gives the optimizer extra information about using BirdId to do a BACKWARD scan to grab the maximum BirdId, and then use that to do a BACKWARD seek of the clustered index in nested loops to get the data. It does only 50 logical reads, 4 physical reads, and 817 read-ahead reads.

--Only run against a test server, not good for production
DBCC DROPCLEANBUFFERS

SELECT birdId, birdName, rowCreatedDate
FROM dbo.Birds
WHERE birdId >=
	(SELECT MAX(birdId)
	FROM dbo.Birds
	WHERE rowCreatedDate <= '2009-07-01 05:00:00')
AND rowCreatedDate >= '2009-07-01 05:00:00'
ORDER BY birdId DESC

Be Careful Out There

The examples I’m using work because there is a correlation between the integer field and the date field. Not all tables may be like this. As with all queries, you need to be familiar with your data.

Consider Your Options– Even the Ones You Don’t Think Are Great

I’m quite sure BACKWARD index reads are covered in some talks and publications on tuning.  But I learned about this by considering multiple approaches, even those I didn’t think would work at first. It pays to try things out, and you can look a lot by looking carefully at execution plans (including the properties) and your Statistics IO output.

What this means to me: it’s good to keep an open mind.

Writing a Technical Blog: Why to do it and what to write about

Recently I read Paul Randal’s ( blog | twitter ) post, “So you want to blog about SQL Server?” and it got me to thinking.

Why do I blog about SQL Server, anyway? Why do I recommend it?

Write out of selfishness. I do!

Yep. This blog is me doing something for me. And it works: it helps me a lot.

Writing blog posts isn’t easy. Most posts some require lots of thinking, putting together scripts, scratching my head when they don’t work right, trying again. There’s the time it takes to upload images, to get them aligned properly into posts, to get everything just right. It’s work. And it’s work I do all on my own time. Regularly.

But writing regularly about SQL Server has become a very valuable part of my learning process.

Writing a nerdy blog makes you smarter

Blogging about a topic helps you:

  • Slow down a little bit;
  • Think a little differently about something as you figure out how to explain it;
  • Consider issues conceptually rather than in a more narrow or applied perspective;
  • Research things more clearly, and document your research with links;
  • Be more likely to remember things later;
  • Have a good place to search when you can’t remember things later.

At the best of times, your blog brings you in touch with other people and you learn from their comments and feedback. That part of blogging is AWESOME.

But even without that part, writing your way through life as you learn things and solve problems is great for you.

Write for yourself, but pick someone else as your *audience*

Motivation and audience are different things entirely.

Your own learning process is a great justification in itself for writing a blog and picking your topics. This is your motivation.

However, it’s helpful to identify the audience for your writing. Write your blog with a specific reader in mind. This may be someone you know, or it may be someone imaginary. If you’re writing a technical blog, this is probably going to be someone pretty nerdy.

When you’re writing your post, just make sure you’re telling them something you find interesting.

Here’s what to write about

This part was very difficult for me. I hear from new bloggers that it’s hard for them. It’s not hard for me anymore. Here’s what to write about:

  1. Write about what you want to learn: Think of something you’re curious about, look into it, write a blog post.
  2. Write about what you learned last week: Take something  you didn’t know before, big or small, and write it down. Write scripts to reproduce the problem, or a scenario to describe it in a new way. Think of other ways to do it. Write about the searching  you did to help solve the problem, and what helped and why.
  3. Write about what you wished you knew a year ago.
  4. Write about problems you haven’t found a good solution to yet.
  5. Write about what you care about. That’s why I wrote this post.

Can you write about something if you’re not an expert on it? YES!

Just write about where you’re coming from, document your work so far, link to your references, and write down your questions. People may help answer your questions, or you may answer them yourself in further posts. You’ll learn a lot just by writing a careful, thoughtful blog  post about what you’re doing.

Don’t try to sound smart

Writing a technical blog helps make you less isolated. It gives the community a chance to get to know you in ways they wouldn’t otherwise. Even if people don’t subscribe and read every post, if you blog regularly and tweet a bit about it, people will find you and get a sense of who you are. (Note: Paul’s post has some great practical tips on which #hastags are appropriate to tweet about SQL Server posts on twitter. For other communities, try out hashtags and ask for feedback.)

Have you ever noticed that if you spend too much time worrying about being funny, you’re not funny? And that if you worry about sounding smart, you don’t sound smart?

The same thing is true for blogging. Don’t try to sound smart. Just be yourself. (The part of yourself that it’s OK for everyone to see in public, and at work.)

Blogging doesn’t make you cool. It’s for old people.

And you’re officially NOT too old to use Twitter.

More links on blogging

I got started out with a lot of the practical tips in Brent Ozar’s ( blogtwitter ) series on the topic. A good starting point is: “How to Start a Blog

Penelope Trunk ( blog | twitter ) has lots of great posts on blogging. A good starting point is: “How to write a blog post people love”

2011: More Nerdy Drawrings (TSQL Tuesday #14)

TSQL Tuesday, 2011 Style…

This month’s #tsql2sDay is hosted by Jen McCown ( blog | twitter ) and Jen’s question is “What techie resolutions have you been pondering, and why?

And a quick congrats to Ms McCown on her new SQL Server MVP Award!

I Resolve to Draw More Geeky Pictures

Last night I wondered, “How would I draw a picture of the Storage Engine?”

So that’s what I’m going to do this year. I’m going to draw the storage engine and all its friends, plus much more.

Not every blog post or presentation slide will have drawings, but there will be lots more than last year. (See that? That’s an achievably vague goal!)

mmmmmmmm

Uh, Why Would You Draw a Picture of the Storage Engine?

I think it helps share how I see the world.

But mostly I think it’s fun. I like to make my presentation slides colorful and interesting to look at.

I don’t think simple, creative art dumbs anything down. I think it just makes discussing complex concepts more fun, and having a playful image can make it easier to remember things.

Why Now?

I finally got a new drawing tablet, which I’ve wanted for ages. Unlike my old tiny tablet purchased long ago, this one has drivers that play nicely with my operating systems! I saved up my nickels and my family pitched in funds as my Christmas present, and now I have a lovely new Wacom Intuos tablet with which to create all sorts of madness.

I do all my drawing with The Gimp. I’ve used it for years and I love it. Thanks Gimp for being so awesome. And so free.

So, if the Storage Engine is Cake, What’s the Optimizer?

I knew  you were going to ask that. So I asked it myself.

The optimizer is clearly an octopus with many pairs of eyeglasses.

Optimizeropus

What does  it look like when an Octopus gets ahold of a bunch of cake? Keep reading this  year to find out.

Date Rounding Tactics and the Tiny Devil of SMALLDATETIME

Tiny Devils

With every new year I think a little bit about time and dates. This posts looks a little more at that in TSQL.

Rounding Dates: Which way is best?

Sometimes in TSQL you need to round a datetime value to the precision of either a day, hour, minute, or second.

I realized recently that I have a few ways I know how to do this, but I wasn’t sure which was the most efficient.

I did a little searching and didn’t find anything super conclusive. I had a little chat with Jeremiah Peschka (blog | twitter) and he told me which way he thought was fastest and why.

And so I decided to run some tests. Jeremiah has a way of being right about these things, but I had to see for myself.

I’ll go ahead and tell you: He was totally right, and I’ll show you why. But I learned a couple things along the way.

Reference: Types and Storage Sizes

To get started, let’s review some of our friends, the SQL Server datatypes. Hi friends!

Rounding to the Day

The most frequent case in which I need to round dates is to the day level. So instead of ‘1/4/2011 6:15:03.393921’, I want just ‘1/4/2011’.

SQL 2008’s date type made this a lot easier for everyone– now we can just cast a datetime or datetime2 value as a date, and we’ve got what we need. PLUS, our new value is nice and small, weighing in at 3 bytes.

I think most everyone agrees, we like this!
SELECT CAST('1/1/2010 23:59:59.000' AS DATE) AS [I'm a date!]

Rounding to Hours, Minutes, or Seconds:
Beware the tiny devil of SMALLDATETIME

This is still a bit more complicated. When you start thinking about these and different datatypes, you need to make sure you understand what you mean by rounding.

In SQL Server, our datatypes actually have some different opinions about what rounding means. Check this out:

SELECT
CAST('1/1/2010 23:59:59.000' AS DATETIME) AS [I'm a DATETIME!],
CAST('1/1/2010 23:59:59.000' AS DATETIME2(0))  AS [I'm a DATETIME2(0)!'],
CAST('1/1/2010 23:59:59.000' AS SMALLDATETIME) AS [I'm a SMALLDATETIME, and I'm very confused.],
CAST('1/1/2010 23:59:59.000' AS DATE) AS [I'm a DATE!]

This returns:

The SMALLDATETIME value rounds this up to January 2nd, instead of January 1. The Date datatype does not.

In considering whether or not to use SMALLDATETIME, you need to learn and establish whether or not to round up for minutes and date values. With a different example, if something occurred at 12:30:31 AM, would that be represented as having  happened in the 12:30 minute, or at 12:31?

Most of us actually want to round down. We want the largest minute number which is less than or equal to the datetime value. This is similar to what FLOOR does for integers. You could also call this truncating the portion of the datetime value you don’t want.  This is not, however, what SMALLDATETIME gives you, so use it with care.

So this is what I’m saying:

Like, seriously, SMALLDATETIME: you are SO messed up.

Comparing Methods of Rounding Dates

So given that warning, let’s actually round some date values, and let’s compare the efficiency of each method.

To start out with, let’s create a table and toss in a bunch of date values. We’ll run queries against these dates and measure SQL Server’s abilities to work with it.

To make up a bunch of datetime data, I’m using my trusty recursive CTE from my prior post.

--Populate a table with some data
CREATE TABLE dbo.Donuts ( DonutTime DATETIME2(7) )

DECLARE
@startDate DATETIME2(7)= '2010-12-01 00:00:00' ,
@endDate DATETIME2(7)= '2010-12-11 01:30:00' ;

WITH    MyCTE
AS ( SELECT
@startDate AS [Makin' the Donuts]
UNION ALL
SELECT
DATEADD(ms, 1225, [Makin' the Donuts])
FROM
MyCTE
WHERE
[Makin' the Donuts] < @endDate )
INSERT  dbo.Donuts
SELECT
[Makin' the Donuts]
FROM
MyCTE
OPTION
( MAXRECURSION 0 ) ;

SELECT @@ROWCOUNT
--We now have 709716 rows of DonutTime

Now let’s look at different methods to manipulate datevalues. For our examples I’ll be rounding to the minute.

Contestant 1 –
DATEPART: isolate each part of the date, then concatenate

As we learn TSQL, this is the first method that occurs to us. We know DATEPART will return part of a date (great name!), so we can chop apart the bits. However, to get them back together properly we have to turn each part into a string to clue them back together. And then if we want to treat it like a date (which we pretty much always do), we have to cast it back.

Just look at this baby. It’s pretty ugly.

SELECT
CAST(CAST(DATEPART(YY, DonutTime) AS CHAR(4)) + '-' + CAST(DATEPART(MM, DonutTime) AS NVARCHAR(2)) + '-'
+ CAST(DATEPART(DD, DonutTime) AS NVARCHAR(2)) + '  ' + CAST(DATEPART(hh, DonutTime) AS NVARCHAR(2)) + ':'
+ CAST(DATEPART(mi, DonutTime) AS NVARCHAR(2)) + ':00.000' AS DATETIME2(0)) AS [Wow, that was a lot of typing.]
FROM
dbo.Donuts

Running this (after cleaning out buffers), I got these results:

Contestant 2 –
Subtracting what you don’t want

There’s a couple of variations on contestant #2. I’ll take the one I like best, which is casting to a smaller byte size by using DATETIME2(0), which is 6 bytes rather than 8 and effectively truncates to the second. Then I’ll subtract the seconds.

SELECT
DATEADD(ss, -DATEPART(ss, DonutTime), CAST (DonutTime AS DATETIME2(0)))
FROM
dbo.Donuts

Running this one (yes, I cleaned out the buffers), I got these results:

Well now, that’s much lower CPU time there.

NB: I did test, and in all my trials it was lower CPU time to cast into DATETIME2 rather than using a nested DATEADD function to subtract milliseconds.

Contestant 3-
Convert to a shorter character string, then back to date

This contestant is near and dear to my heart. I like it because it’s easy for me to remember. You take a short trip into CHAR() with the 121 date format and set the length to chop off the parts of the date you don’t want. Then you cast or convert back to a DATETIME2(0).

I think I like this one because it feels just a little bit violent. But not in a bad way. It’s like roller derby.

SELECT
CAST(CONVERT(CHAR(16), DonutTime, 121) AS DATETIME2(0))
FROM
dbo.Donuts

Oh, sad. This one didn’t do very well. It’s definitely better than Contestant #1, at least.

Contestant 4-
Use DATEADD to calculate the minutes since a given date, then add them back

Here’s the method Jeremiah suggested to me. The way he described it was “Just figure out the number of minutes since the beginning of time, and use that.”

Being a philosophy major, I of course asked “So, when was the beginning of time?”

Being a developer, he answered, “Just call it zero.”

SELECT
DATEADD(mi, DATEDIFF(mi, 0, CAST(DonutTime AS DATETIME2(0))), 0)
FROM
dbo.Donuts

Here are the results (clean buffers, as usual):

Ooo, check out the CPU time on that one.

Note: I ran a few trials and this is faster on the CPU when you cast as DATETIME2(0) before doing your maths. I did that to make all things equal with the other contestants, who had the same benefit.

Who Won, and Why

Here’s a recap of how everyone performed:

Why did contestants 2 and 4 do so well?

Jeremiah pointed out that datetime values are stored internally as two four byte integers. (BOL reference: see “Remarks”) Performing mathematic functions on an integer value is a nice fast activity on the CPU.

Performing conversions back and forth to character based datatypes, however, is not so natural, nor so fast.

What’s the internal storage format of DateTime2?  Well, I’m not sure about that one. BOL isn’t so up-front about these things anymore. If you happen to know, please tell me in the comments. I can tell, however, that it’s something that enjoys mathematics.

Filling in Data Potholes Redux: Tally Tables vs CTEs

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.