Art of the DBA Rotating Header Image

August, 2012:

T-SQL Tuesday #33 Wrap-up (#tsql2sday)

Last week I had the opportunity to host this month’s SQL blog party, T-SQL Tuesday.  I hope folks enjoyed the topic (it seemed like it), because the idea of experimenting with something is very much a core part of the learning process.  SQL Server is so deep and has a number of nooks and crannies.  To become proficient at it (let alone an “expert”), we have to keep pushing our boundaries and asking “What happens when….?”

I want to again thank everyone who participated.  Here’s a quick round up of the posts:

Rob Farley(b|t) peels back the cover on recursive Common Table Expressions (CTEs).  If you haven’t worked with them yet, CTEs are an incredibly useful tool in the SQL arsenal.  Using them recursively is just one example of how powerful they can be to a developer.

Jeffrey Verheul (b|t) chimes in with a big picture discussion on using cursors versus set based operations.  While we database folks prefer set based operations, sometimes it’s to our advantage to use a cursor.

Jim McLeod (b|t) jumps in with a great little way to use SQL Profiler to gain some insight into the workings of SQL Server by tracing Management Studio.  Not a lot of people are aware that almost everything that happens in Management Studio is a query that can be scripted out, and profiler lets us see that.

Oliver Asmus (b|t) shares some of his knowledge gained from putting together preventative maintenance tasks (a vital yet often overlooked part of being a DBA).

Aaron Bertrand (b|t) provides a cool trick to swap tables around using schemas.  A lot of SQL DBAs overlook the power of schemas in databases, but Aaron’s trick really gives as an efficient way to handle offline updates and other resource intensive tasks.

Nigel Sammy (b|t) pokes at the Transaction log with DBCC SQLPERF.  It’s important for DBAs to really understand their transaction log usage, and Nigel’s trick can give a DBA some real insight.

Tracy McKibben (b|t) gives us a trick using one of my favorite system views, sys.partitions.  There are times I lament not being able to see how long a query or process will take (I’d LOVE to have something like Oracle’s V$SESSION_LONGOPS view), but Tracy shows us how to use sys.partitions for insight into index rebuilds.

Chris Shaw (b|t) gives us some information on cluster failovers. While we want our operations to appear seamless to our users, as DBAs it’s important that we’re always aware of where our instances are living within our environment.

Rob Volk (b|t) really gets down the rabbit hole, using one of the Sysinternals tools to crack open SQL Server.  I’ve seen some folks do some really cool and powerful stuff with Sysinternals, so Rob’s post is a great way to introduce people to this awesome tool suite.  (Plus, how many guys even get to use the word “hekaton”?!?)

Jennifer Salvo (b|t) adds a little business intelligence to our topic, giving us a cool SSAS trick to manage currency conversions.  Businesses will ALWAYS care about their money, so having a good way to manage multiple currencies is a must.

Finally, first timer Jose Rivera (b|t) brings us full circle with his own post on using recursive CTEs to solve a tricky problem for drug costs.

(Oh, and look at this post if you want to see my contribution.  :D  )

I really appreciate everyone’s participation.  This is such a great part of the SQL Family and I hope that if you’re reading this you’ll consider participating and, perhaps, hosting a T-SQL Tuesday in the future.

T-SQL #33 – What happens if….? (#tsql2sday)

Sometimes a trick shot is the result of asking “What happens?” What happens if I draw off this ball, can I sink this other ball? What happens if I hit these two balls that are touching each other? The premise is that you look at a situation and explore the result.

For my SQL “trick”, I asked “what happens if you update a value to the same value?” It’s a question that gets asked by a lot of folks, but I wanted to explore the tools in SQL Server to answer that question for me. We can read about someone else figuring this out, but I think my best learning is done when I puzzle out a problem on my own.

I created two simple scenarios: an update of a column to itself and an update of a column to itself plus one. Fairly simple stuff:

create table test
(cola int not null identity (1,1),
colb int not null default 0,
colc varchar(100),
constraint pk_test primary key clustered (cola))

insert into test(colc)
select top 1000 a.name
from sys.objects a,sys.objects b

--Reset, write all pages out from buffers
checkpoint
dbcc dropcleanbuffers

--These tests need to be run separately!

--Test 1
update test
set colb = colb

--Test 2
update test
set colb = colb+1

(Note, the checkpoint and dbcc dropcleanbuffers is to make sure I’ve cleaned out all my log activity before I run either test.)

My first actual stop was to use DBCC PAGE to see if I could see if looking at the raw page would show if the pages had been modified. Unfortunately, this information wasn’t there, though I did get a lot of exposure to reading the output of the command. Not sure where to go next, I reached out to the Twitterverse on #sqlhelp. I was directed towards two items:

  • sys.fn_dblog – Undocumented function that shows all the individual actions in your transaction log.
  • sys.dm_os_buffer_descriptors – DMV that lists information on all the pages in your buffer pool

Querying both of these tools gave me the answer to “What happens?”, but in two different ways. When I looked at sys.fn_dblog, I could see no actual transactions when I updated the column to itself, but a transaction for each update when the value was actually modified:

--check for log activity
select [Current LSN],Operation,Context,[Transaction ID]
from sys.fn_dblog(null,null)

Sys.dm_os_buffer_descriptors has a field labeled ‘is_modified’ which is set to 1 if the page has been changed. When I queried for pages with is_modified equal to 1, I saw further evidence of what fn_dblog showed me:

--List pages in the pool that are modified
select * from sys.dm_os_buffer_descriptors
where is_modified = 1 and database_id = db_id('junk')

In both cases, I was able to see clearly what was going on under the covers and that SQL Server is actually intelligent enough to save some work when no work is actually being done.

Of course, the funny thing about trick shots is someone usually can do it better. Shortly after I figured this all out on my own, I found this fantastic blog post by Paul White (b|t) showing all this with a little more detail. However, what’s important is what you can learn by doing things yourself. It may take you time to reinvent the wheel (something you might not be able to afford), but when you do it yourself you learn a lot about how a wheel works.

Thanks for joining me this month, where I not only get to contribute but also host T-SQL Tuesday. I’m excited to see what other things our peers can teach us about the things they’ve learned by playing around with SQL Server, trying to see “What happens?”

Invitation to T-SQL Tuesday #33 – Trick Shots (#tsql2sday)

For a while, I was in an amateur pool league.  No, not the one involving water and swimming, but where you try to sink balls into pockets.  It was a lot of fun and is a challenge both for your motor skills as well as your strategy.  I still shoot from time to time, as well as hang out with my old pool buddies.

One thing guys would get into is trick shots.  Two and three rail bank shots, masse shots, or jumping the cue ball to hit the target.  Most of these shots weren’t tournament legal, but they were fun to try and nice to impress the ladies.  More than that, they were a tool to teach you the physics of your pool game.  You could see how throw and English could affect your shot, or how balls would behave after impact.

Just like so many other things I do in my life, the trick shot lessons translate over to SQL Server.  How many times have we built something neat or puzzled out a particular bit of logic that, while it may not have been particularly useful, taught us about how SQL Server behaves.  This month’s T-SQL Tuesday is all about this and the assignment is two-fold:

  1. Show us a cool trick or process you developed, maybe a DMV you used or some reporting logic you created.  It doesn’t have to be useful, just something that you thought was pretty neat.
  2. Tell us what you learned from this trick.  Is it something about an oddity in SSRS?  Maybe with the query processor?  Whatever you did, tell us how it gave you insight in to how SQL Server works.

Now with that out of the way, keep in mind the ground rules for T-SQL Tuesday:

  • Posts must be made between August 14, 2012 00:00:00 GMT and August 15, 2012 00:00:00 GMT.
  • Your post should contain the T-SQL Tuesday logo and a link back to this post.
  • If you don’t see your post in trackbacks (give me ~30 minutes to approve), post a link to your post in the comments of this post.

If you’re on Twitter, you can follow T-SQL Tuesday at #tsql2sday.  Follow it, it’s a great way to see what everyone is writing about!  And if you’re interested, Adam Machanic(b|t) handles the T-SQL Tuesday hosting list, contact him if you have an idea for one.