SQL Server with Mr. Denny


July 10, 2013  7:00 AM

Preventing Locking, Blocking and Deadlocks in the vCenter database

Denny Cherry Denny Cherry Profile: Denny Cherry

As our VMware environments become larger and larger with more and more hosts and guests more thought needs to be given to the vCenter database that is typically running within a SQL Server database.

With the vCenter database running within Microsoft SQL Server (which is the default) their will be lots of locking and blocking happening as the queries which the vCenter server runs aggregates the data into the summary tables.  The larger the environment the more data that needs to be aggregated every 5 minutes, hours, daily, etc.

Then problem here is that in order for these aggregations to run the source and destination tables have to be locked.  This is normal data integrity within the SQL Server database engine.

Thankfully there is a way to get out of this situation.  That is to enable a setting called Snapshot Isolation level for the vCenter database.  This setting changes the way that SQL Server handles concurrency by allowing people to write to the database while at the same time allowing people to read the old versions of the data pages therefor preventing locks.  The SQL Server does this by making a copy of the data page when it is being modified and putting that copy into the tempdb database.  Any user that attempts to run queries against the original page will instead be given the old version from the tempdb database.

If you’ve seen problems with the vCenter client locking up and not returning performance data when the aggregation jobs are running, this will make these problems go away.

Turning this feature on is pretty simple.  In SQL Server Management Studio simply right click on the vCenter database and find the “Allow Snapshot Isolation” setting on the options tab.  Change the setting from False to True and click OK (this is the AdventureWorks2012 database, but you’ll get the idea).

image

If you’d rather change the settings via T-SQL it’s done via the ALTER DATABASE command shown below.

ALTER DATABASE [vCenter] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Hopefully this will help fix some performance problems within the vCenter database.

Denny

July 5, 2013  5:02 PM

Recommended reading from mrdenny for July 05, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


July 3, 2013  7:00 AM

Backups & the Buffer Pool

Denny Cherry Denny Cherry Profile: Denny Cherry

    As we know with Microsoft SQL Server everything is processed from disk and loaded into the buffer pool for processing by the query engine.  So what happens to the buffer pool when backups are taken?

    The answer is that nothing happens to the buffer pool.

    When SQL Server is backing up data from the disk, SQL Server simply takes the data from the data files and writes it to the backup file.  During the backup process the dirty pages are written to the disk by the checkpoint process being triggered by the backup database process.

    Because the backup process simply reads the data files and writes them to the backup location there’s no need to cache the data in the buffer pool as this data isn’t being queried by a normal SQL query.

    Denny


June 26, 2013  2:00 PM

Performance Tuning a Spotlight for SQL Server Query

Denny Cherry Denny Cherry Profile: Denny Cherry

The other day I was looking at parallel query plans on a customers system and I noticed that the bulk of the parallel queries on the system where coming from Spotlight for SQL Server.

The query in question is used by spotlight to figure out when the most recent full, differential and log database backups were taken on the server.  The query itself is pretty short, but it was showing a query cost of 140 on this system.  A quick index created within the MSDB database solved this problem reducing the cost of the query down to 14.  The query cost was reduced because a clustered index scan of the backupset table was changed into a nonclustered index scan of a much smaller index.

The index I created was:

CREATE INDEX mrdenny_databasename_type_backupfinishdate on backupset
(database_name, type, backup_finish_date)
with (fillfactor=70, online=on, data_compression=page)

Now if you aren’t running Enterprise edition you’ll want to turn the online index building off, and you may need to turn the data compression off depending on the edition and version of SQL Server that you are running.

If you are running SpotLight for SQL Server I’d recommend adding this index as this will fix the performance of one of the queries which SpotLight for SQL Server is running against the database engine pretty frequently.  I’d recommend adding this index to all the SQL Server’s which SpotLight for SQL Server monitors.

Denny


June 19, 2013  5:00 PM

A week of using my Surfii (when you have more than one Microsoft Surface)

Denny Cherry Denny Cherry Profile: Denny Cherry

So while at the TechEd North America conference Microsoft gave the attendees, speakers and booth staff the ability to purchase some Microsoft Surfii (surf-I) at some major discounts.  The Surface RT was available for $99 US and the Surface Pro was just $399 US.  Needless to say I purchased both of them.

I’ve been using them for about a week now and I’ve got to say that I’m really liking them for the most part.  The devices are pretty light even with the keyboard attached.  I spent the entire flight home from my layover in Houston to San Diego using just the Pro to do some writing on via the type cover and while it was a little cramped and took a little getting used to, within about 30 minutes of using it I was basically adjusted to using it.  The only thing that really kept screwing me up is the lack of a right click key on the keyboard, which I’m very used to using as I’m not big on using the mouse to right click when I’m writing a book or article.  Using the device on the flight I was using it with the kickstand and I found that it was at a really good angle for me to see everything on the screen without any eye strain or potential problems like that.

Another annoyance that I found was that when the type cover isn’t flat on a hard surface and you try and right click with it you’ll end up left clicking.  In the airport I was sitting with the surface on my lap with the kickstand out and about 1/2 the time when I used the right click I got a left click instead.  Very annoying.

After getting home with the device I didn’t do much heavy work with it besides getting some software installed.  I was really impressed with the quality of the WiFi antenna in it.  Using it downstairs and in my back yard there’s great WiFi signal when only one of my two laptop works well downstairs in the same stop in the living room.

Recently I used the Surface Pro as a notebook with the pen that comes with it.  I needed to figure out which sprinklers in my yard went with which zone (there’s 15 zones in the timer and 11 controllers and no documentation) in our new house.  I grabbed my Surface Pro, grabbed the pen (which docks nicely to the power port when you aren’t charging the Pro) and fired up OneNote.  I just wrote in it just like I would if I had a piece of paper, but in this case it’s a never ending piece of paper because it just keeps going instead of making you flip the page.  Because I’ve got OneNote configured to sync everything to the cloud, everything just syncs up to OneNote on my laptops and desktop so all my notes were instantly available on my desktop when I sat down.  Now I can’t say anything about the quality of the hand writing, but that’s all me not the device (it isn’t any better on paper).

Because the Surface Pro is just Windows 8 I installed Cubby on it and configured it to sync the My Documents folder to all my other machines.  This gives me all my scripts, documents, articles, presentations, etc. on the Surface Pro just like on my desktop and laptops.  I’ve also got all the normal VPN applications installed as well as SQL Server Management Studio to that I can do basically whatever client work I need to from this little device.

Now the Surface RT isn’t a full blown copy of Windows 8.  To me it is more of an iPad replacement than anything else.  So on that guy I’ve installed the same stuff that is on my iPad, games.  Free ones whenever possible.  For a platform for playing those kinds of games it is working pretty well.  I can’t comment on using it for actual work type things as I’ve got no plans for doing that on the device.  One thing that I will say is that I wish that the pen worked on the Surface RT as well as the Surface Pro, but it appears that what ever the pen talks to only works on the Surface Pro.

For the prices I paid, these items were a steal.  If I was paying full price I’d have to think more about purchasing them (which should be obvious as I didn’t buy them until I got them for a damn good price at TechEd).

Denny


June 10, 2013  12:00 PM

SQL Server 2014 Standard Edition High Availability

Denny Cherry Denny Cherry Profile: Denny Cherry

With all the announcements about SQL Server 2014 this last week there have been a lot of questions about what’s going to happen for SQL Server 2014 and the non-shared storage High Availability options as we are now one step closer to database mirroring being removed from the product.  You’ll see several blog posts coming out this morning from a variety of people all with their own opinions about what should be included.  These opinions are mine and mine alone.

In SQL Server 2005 Standard Edition and up we had database mirroring which supported having a single mirror on site which was synchronous mirroring only with asynchronous mirroring being an Enterprise Edition feature.  I would like to see this same feature set moved into the SQL Server 2014 Standard Edition product as well.  How I would see this working would be the normal AlwaysOn Availability Group configuration that we have today but only supporting a single replica.  I can see synchronous data movement being the only data movement option which would allow for a local onsite HA without giving you the ability for geographically distributed disaster recovery as that requires asynchronous data movement.

If Microsoft wanted to do something really nice for their Standard Edition customers they could allow for a second replica which would be an Azure database replica and that would allow for Disaster Recovery within Standard Edition while pushing Azure (which we all know is a big deal for Microsoft these days).

So there you have it, that’s what I would like to see in the SQL Server 2014 Standard Edition version of the product.  Do I expect to see it, honestly I’m really not sure.  Microsoft has been very tight lipped about what is coming in the Standard Edition, mostly because these decisions haven’t been made yet.  Once they are someone people will be happy, others wont be, but that’ll be what we have to deal with until we do this all over again in a couple more years when the next version of SQL Server is released.

Denny


June 5, 2013  2:00 PM

What does the RCSI overhead mean?

Denny Cherry Denny Cherry Profile: Denny Cherry

Earlier I posted a blog post which talked about the fact that when you turn on readable secondary replicas there are an additional 14 bytes of overhead which are added to each row.

Overall the thought here is that the impact of this is probably pretty minimal. After all this is the same overhead for RCSI.  However where this becomes a problem is due to the page splitting that I mention in the other article and the fact that these 14 bytes don’t survive after an index rebuild but they do an index reorg.

I can see major page splitting issues happening on clustered indexes which are using the default fill factor of 100% full, which most clustered indexes use as that’s the correct fill factor to be using for a key column which is always growing.  But now as rows need to be changes within the clustered index that’s going to cause our clustered indexes, which never used to have to worry about page splits to suddenly start to split.

The solution here is that when using the readable secondary feature clustered indexes will need to account for this by using a fill factor other than 100% (or 0%) for the fill factor so that the page splits within the clustered index can be avoided.

Additionally we need to think long and hard about using index rebuilds or index reorgs when doing index maintenance.  If we have a table where the records are updated for a while then never updated again index rebuilds probably makes sense instead of index reorgs.  If however we are only doing index reorgs we are now storing an additional 14 bytes of data per row, eventually for most if not all of our rows for ever.  When talking about large tables that’s suddenly some actual space that needs to be accounted for.  For a table with 1 Billion rows that’s an additional 13 Gigs of additional space.  All in all not all that much space.  But if your 1 Billion row table has a row width of just 35 bytes your table should be about 33 Gigs, so an additional 13 Gigs of space is quite a lot.

I guess where I going here is that if you are going to be using AlwaysOn Availability Groups to protect your data and you are going to be using the readable secondary feature then there are going to be some other things within the database that you want to take into account.


May 28, 2013  4:27 PM

Extra Bytes Per Row With AlwaysOn Availability Groups

Denny Cherry Denny Cherry Profile: Denny Cherry

One of things to keep in mind with SQL Server 2012 AlwaysOn Availability Groups is that when the availability group has readable secondary replicas any rows that are changed will have an additional 14 bytes added to each row.  These 14 bytes are used by the readable secondary to handle the read committed snapshot isolation level (RCSI) so that the readable secondary replicas work correctly.

To look at what’s going on lets create a sample new table in a table and take a peak at the data.  To setup this test we create a new database and setup that database for use with AlwaysOn Availability Groups.  The availability group is setup with no readable secondary replicas.

create table MyTest (c1 int identity(1,1),
c2 int,
c3 varchar(100))
GO
insert into MyTest
(c2, c3)
values
(1, ‘test’)
GO 400

This creates a new table in the database with 400 rows in it.  Looking at the output from DBCC IND we can see that this table takes up 2 data pages with a root page (this will become important later on).

Looking at the data for the first data page we can see the following information.

Slot 0 Offset 0×60 Length 23

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x000000003CACA060

0000000000000000:   30000c00 01000000 01000000 03000001 00170074  0………………t
0000000000000014:   657374                                        est

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 1

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

When we update this row changing the value of c2 to equal 2 nothing really changes which we can see from DBCC PAGE again.

Slot 0 Offset 0×60 Length 23

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x000000003EECA060

0000000000000000:   30000c00 01000000 02000000 03000001 00170074  0………………t
0000000000000014:   657374                                        est

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 2

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Next I’ve changed the settings for the availability group to support readable secondary replicas.  Once that change has been made we change the value of c2 for the same row to equal the value of 3.  Again we can look at this with DBCC PAGE.

Slot 0 Offset 0x1d4e Length 37

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 37
Memory Dump @0x000000003AEEBD4E

0000000000000000:   70000c00 01000000 03000000 03000001 00170074  p………………t
0000000000000014:   65737400 00000000 0000000f 06000000 00        est…………..

Version Information =
Transaction Timestamp: 1551
Version Pointer: Null

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 3

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Looking at these two outputs from DBCC PAGE we can see a couple of differences.  First we see an additional value in the “Record Attributes” field which adds in VERSIONING_INFO to the value.  We also see that the record size has changed from 32 to 37.  Additionally we see that the Version Information has been added.

Looking at the DBCC PAGE output on one of the replicas for the same page as before we see some different information.

Slot 0 Offset 0x1d4e Length 37

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 37
Memory Dump @0x000000003893BD4E

0000000000000000:   70000c00 01000000 03000000 03000001 00170074  p………………t
0000000000000014:   65737440 01000001 0000000f 06000000 00        est@………….

Version Information =
Transaction Timestamp: 1551
Version Pointer: (file 1 page 320 currentSlotId 0)

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 3

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Specifically at this point we see that the Version Pointer now has a value in it. This tells us that the SQL Server has put a copy of the original page into the tempdb database so that we can read it.

A question comes up as to what happens to the page when this row information is added.  Specifically does the page split because of this additional 14 bytes of new data per row.  The answer to this question is “it depends”.  In my testing that I did when I updated a single row the page didn’t split, mostly this would be because there was some free space in the database page.  When I updated rows 2-40 and looked at DBCC IND I saw that in fact the page had split.

Looking at the values within the page there are now 159 rows in the page which was the original database page when there were 322 rows within the database page.  The remainder of the rows were copied into a new database page.

Now that we’ve identified that SQL Server is going to be page splitting older database pages, potentially like crazy what can we do about it?  The answer to that question is to just deal with it and to decrease the fill factor as needed so that page splits happen as little as possible.

To make matters worse when we rebuild the index on the table and look at the output from DBCC PAGE again we can see that the additional flag has been removed from row 1 (seen below).  This tells us that no only will this problem come up the first time that data is modified, it’ll come up every time that index rebuilds are done when the data is changed for the first time after the rebuild.

Slot 0 Offset 0×60 Length 23

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x000000003E0CA060

0000000000000000:   30000c00 01000000 03000000 03000001 00170074  0………………t
0000000000000014:   657374                                        est

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 3

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Changing the data again, this time changing the first 40 rows (id values 1-40) the new flag comes into place as expected.  If we reorganize the index instead of doing a rebuild this time the flags are left in place.

This tells us that the better option for doing index maintenance on databases which are being protected by AlwaysOn Availability Groups is going to be to use reorganize commands instead of rebuild commands.  This way the 14 byte pointer isn’t removed from the rows so that when they are modified the additional 14 bytes of data doesn’t need to be added.

If you’ve got rows which are changed all the time then this will be a way to handle it.  If the rows never change after the data is reorged then it may or may not be something worth worrying about.

Hopefully this helps answer the questions of what these extra bytes are for and how we can deal with them.

Denny


May 24, 2013  5:04 PM

Recommended reading from mrdenny for May 24, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


May 22, 2013  2:00 PM

The Optimizer Isn’t As Smart As You Might Want It To Be

Denny Cherry Denny Cherry Profile: Denny Cherry

A little while back I got one of those phone calls.  You know the one, the lovely 6am phone call about random performance problems.  There were two problems that night.  One which I’ll talk about later in another post, the second one which I want to talk about today.

The query that was having problems is a dynamically generated query which comes from a stored procedure.  The basic query which was being run looked a lot like this.

SELECT /*A bunch of columns*/
FROM answer a
JOIN session s ON a.SessionID = s.SessionID
WHERE a.SessionID IN (4857385,5269932,5682479,6095026)

Most of the time that this query was being run everything was just fine, however there were a some times when it was timing out. Looking into the execution plan for a normal run of the query everything looked just fine. However when this was being run sometimes there were 1.2M rows being pulled from the session table even though there were 4 specific IDs being passed in.

Looking at the properties of the index scan which was being performed against the session table I could see that the SQL Server turned the query to WHERE s.SessionID >= 4857385 AND s.SessionID <= 6095026. This was a problem as for some of these queries as like with this query there were 1.2M rows being returned from the session table instead of the 4 rows that should have been returned.

The fix in this case was to simply change there where clause from “WHERE a.SessionID” to “WHERE s.SessionID”. Now I’m not sure why this worked from the internals point of view but I do know that it worked. The next time the stored procedure ran it run in milliseconds instead of timing out at 30 seconds.

In this case the server in question was SQL Server 2008 R2 (10.50.2796). This may or may not apply to other builds of SQL Server. I’m pretty sure this is going to be a your mileage may vary sort of thing.

This is officially the least amount of work that I’ve ever done tuning a query as I only made a single change to a single letter of the query.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: