Art of the DBA Rotating Header Image


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
from sys.objects a,sys.objects b

--Reset, write all pages out from buffers
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?”

Dumb Questions

“Can I ask a dumb question?”

Anyone who works with me for a short period of time will hear this tumble out of my mouth.  I can’t tell you the number of times someone launches into a 5-10 minute explanation of a really cool concept and giving me that “I have a cunning plan” look when they finish.  However, because I didn’t have that one thing in my head they figured was obvious, I end up having to ask a couple questions to get myself on the same page.  It’s never a big deal to fill in those gaps and the opposite party is usually happy to provide the context, but I do have to ask those questions.

Assumption: The Mother of all Screwups

The problem is with assumptions.  Whenever we communicate with each other, we have to make an assumption about what the other person knows.  Unfortunately, a lot of times those assumptions are wrong.  Remember the ill-fated Mars orbiter where there was a mixup between the imperial and metric measurement systems?  While we all smack our forehead and say “D’OH”, it’s pretty obvious that this is the result of one side making assumptions about the other side’s knowledge.  If someone had questioned the basic assumptions of both sides, they might have saved that $125 million orbiter, but instead, the two teams ran off on different tracks because they didn’t want to appear dumb.  (How how do they look now, right?)

I know we all get frustrated when the Level 1 support tech asks us if our network cable is plugged in or that we haven’t kicked out the power cable, but these are exactly the kind of questions we need to ask when troubleshooting database problems or building requirements for a project.  The reason for this is because asking the dumb questions helps establish a baseline starting point for figuring out the problem.  After all, when someone comes to us saying “the system is slow”, we don’t want to bash our heads with IO stats or memory pressure when all we had to do was deal with one blocking query.  We wouldn’t know about that unless we asked the right questions to start our troubleshooting.

So tell me a little about yourself

The best approach is to dealing with this is to teach yourself to reduce the number of assumptions you make.  Easier said than done, right?  What really helped me was an exercise a former boss/mentor had me do early on in my career.  It’s pretty simple:  In one paragraph (no real limit on sentences or words), describe your work to someone who has no idea what you do.

Seems easy at first, but think about it for a second.  Let’s break down the following sentence that I might use:
“I administer and maintain SQL Server databases.”

Already in this sentence we have several assumptions about the listener.  First, that they know what a database is, and they know what maintenance and administration is for SQL Server.  Chances are most folks don’t, so we would then have to explain those terms to them in a way they would understand.  We don’t need to get overly complex, but provide the listener a basic grasp of what we’re talking about.

The end result is that this exercise puts us in the mindset of someone who may not have the same knowledge or experience as we have, thus getting us to restructure our thinking and presentation in a way that covers the entire context of whatever idea we’re presenting.  It also gets us to start evaluating our assumptions based on our audience.  We’re always going to make assumptions, but what we want to do is make them in the context of our audience.  Speaking to a group of DBAs?  Yeah, leave out that stuff about relational theory.  Talking with IT infrastructure dudes?  Chat with them a bit about database file layouts and managing I/O contention.  Oh, monkeys from Kenya?  Throw ’em a couple of bananas.


So whether we’re asking ourselves or someone talking to us, it’s always good to get those assumptions straight.  Sure, we all pride ourselves on figuring things out.   That’s why we’re in this business, for the joy of solving that process that no one else could.  But it never helps us when we get lost on that wild goose chase because we made the wrong assumption.  Save yourself some time and a lot of headache, make sure you’re on the same page with those you work with, and ask some “dumb” questions.  It never hurts.