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 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?”

3 Comments

  1. Some other important facts can come out of this as you peel more layers from the onion. For example, SQL Server doesn’t log anything when you set colb = colb, but it *will* return true if you check a trigger for IF UPDATE(colb). I don’t really think this is a problem (since I learned long ago to check the values in inserted directly) or even that the behavior is wrong, but it is a nuance that you might not think would happen based on your initial investigation.

  2. Mike Fal says:

    Thanks for pointing that out. Makes sense if you think about it and I wouldn’t think that it was a problem/wrong based on how the engine operates. I’ll explore that a little more just so I understand it better.

  3. […] T-SQL #33 – What happens if….? – The host with the most for this months T-SQL Tuesday, Mike Fal (Blog|Twitter), sharing his very own trickery with us. […]

Leave a Reply to Mike Fal Cancel reply

Your email address will not be published. Required fields are marked *