Monday, December 17, 2012

A Reboot Ate My Log Shipping

I was recently contacted to troubleshoot some Log Shipping issues that arose after some SAN and server maintenance. The Server and SAN teams were working on the server; performing some maintenance on the MPIO drivers. After the work was finished and the server was rebooted, log shipping started to get out of sync on this server.

First, Get a High-Level View, Then Dive Into the Details

The best place to get a high-level view of Log Shipping is by using the built-in reports from SQL Server. In SSMS, right-click on the server node, select Reports, and then choose the Transaction Log Shipping Status report. Do this on both the primary and the secondary server in your log shipping configuration.

Log Shipping Report - Primary Server

The Secondary Server's report is a bit more informative in my opinion...

Log Shipping Report - Secondary Server

By viewing the report, I could see at a glance that the log backups were running fine, but the copy job was having trouble. Next I looked at the copy job history and saw the following error message:
  • The network name cannot be found.
The name of the share can be found in two locations, the error log, or the Log Shipping Setup Properties. In the error log, look for this text in one of the substeps:
  • Backup Source Directory
  • Backup Destination Directory

Log Shipping Errors

Or, in the Log Shipping Setup Properties look for this:

Log Shipping Backup Share

From there, I logged onto the primary server and verified that the share was indeed missing. I also verified that the LOG backups were being written properly on the primary server, but missing from the secondary server.

To fix the situation, all that was necessary was to re-create the share on the primary server and grant read-only permissions to the secondary server's service account.

After that, I manually ran the copy job to start the log backups flowing to the secondary server once again. Then, I ran the restore job and watched the secondary server get back in sync.

But Don't Rush In

Often, when confronted with a Log Shipping sync problem, a person's first reaction is to remove Log Shipping, and then reconfigure it. In this case, nothing would have been solved since the LOG share would have still been missing.

Instead, take the time to troubleshoot the problem and try to find the root cause. Log Shipping is a very simple and stable technology and not much will break it. I have never had to remove and reconfigure Log Shipping to get it to work again.

Give Yourself Some Cushion

Another key to success with Log Shipping is to keep the LOGs around long enough so that you don't break the LSN chain in case of a problem. You want to make sure you keep enough LOGs on hand to cover a long weekend or holiday break in case your DBAs or other staff are not available to respond immediately.

Log Shipping Retention

My preference is to keep seven days worth of LOGs available. If you can't get approval for that much space, walk it back a bit, but hold firm at three days worth. Think about the weekend/holiday factor. If management pushes back, remind them how long it took to initialize the secondary server with the FULL backup. Ask them which is worse, a little extra space, or a long delay while you get a 1TB FULL backup copied from one coast to another.

But What About the Root Cause

Oh yeah, so why did the share disappear to begin with? This server had been migrated from one SAN to another and ended up with two different vendor's MPIO drivers present. So, the old one was slated to be removed. After the work was completed and the server rebooted, all of the shares went missing.

It turns out this is a known issue with the iSCSI Initiator if it is not configured correctly. Basically, the Server Service needs to have a dependency set on the iSCSI Initiator Service. Take a look at kb870964 for some more details on how to prevent this from happening to you.

Monday, December 10, 2012

Where are your indexes located?

When taking over a database or looking at it for the first time, one area I like to focus on is the indexes. There are the standard things, such as index fragmentation, usage, etc. However, one area that is often overlooked is where the indexes are.

But aren't they in the database?

Yes, but where in the database. If your database only has one filegroup, PRIMARY, then that is where your indexes are. However, some databases have multiple filegroups. They could be for archiving purposes, security segregation, performance, or any number of reasons.

A fairly common setup to encounter is to have a separate filegroup for non-clustered indexes. This is not as common a technique as it used to be, and there is some debate as to whether this helps performance. The point here is not to debate the wisdom of index filegroups, but rather to get a sense of how your database is laid out.

The following script will give you a high-level breakdown of your database and show you which filegroup your clustered indexes, non-clustered indexes, and heaps are living in.


-- index location aggregations
select as 'FG Name',
i.type_desc as 'Index Type',
count(*) as 'Index Count'
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
where o.type_desc = 'USER_TABLE'
-- and i.type_desc = 'CLUSTERED'
-- and i.type_desc = 'NONCLUSTERED'
-- and i.type_desc = 'HEAP'
-- and = 'PRIMARY'
-- and = 'MyDataFG'
-- and = 'MyIndexFG'
group by, i.type_desc
order by 'FG Name', 'Index Type'


Index Filegroup Aggregations

What catches my eye in this example, aside from all the heaps, are any clustered indexes that are not in the data filegroup, and any non-clustered indexes that are not in the index filegroup. So I might flag those indexes to be moved to the appropriate filegroup during a future maintenance window.

The next part of the script will give you a detailed list of each index or heap and where it lives.


-- index location details
select as 'FG Name',
object_name(i.object_id) as 'Table Name', as 'Index Name',
i.type_desc as 'Index Type'
-- , o.*
-- , i.*
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
where o.type_desc = 'USER_TABLE'
-- and i.type_desc = 'CLUSTERED'
-- and i.type_desc = 'NONCLUSTERED'
-- and i.type_desc = 'HEAP'
-- and = 'PRIMARY'
-- and = 'MyDataFG'
-- and = 'MyIndexFG'
order by 'FG Name', 'Table Name', 'Index Name', 'Index Type'


Index Filegroup Details

You can modify the where clause to filter the results based on the type of index or the specific filegroup you are interested in.

Go forth and analyze...

Thursday, November 22, 2012

If you can't eat a turkey, then eat a duck!

Here's hoping you and your family are having a safe and fun Thanksgiving.

And if you can't eat a turkey, then eat a duck!

Eat a Duck!

Monday, November 19, 2012

SQL in Seattle

This past week, I went to Seattle to attend the SQL PASS Summit. I had a great week learning about SQL Server, exploring Seattle, and networking with my fellow SQL Server professionals.

Pikes Place Market
You can't talk about Seattle without mentioning the weather. Personally, I found the weather to be fairly nice. Recently, I had purchased a new rain jacket for the Austin City Limits Festival, and was looking forward to putting it to use in Seattle. Funny, I left Texas only to have it rain while I was away. In Seattle, it only rained once, any only for a few hours. Not really that cold either, a fleece was all you needed.


SQL Server 2012 Service Pack 1 has been released. It's time to update your servers. If you've been sitting on the sidelines, no more excuses.

Heckaton, as in you have a heck-o-ton of data and need things to be quick. This is an in-memory database technology that promises to greatly improve performance.

Column Store Indexes will now be updateable and able to be the clustered index. This will help performance in Data Warehouses. Nice.

And perhaps the biggest announcement...

Business Analytics PASS, aka BI PASS

There will be a BI-focused Data Analytics Conference in Chicago next year. I think this is a good thing. I like BI, but it is has grown to the point where it needs its own conference. However, I would like to see PASS retain 10-20% of BI sessions at the Summit. At the same time, BI PASS should also retain a similar percentage of DBA content.

SQL Family

One of the best parts of PASS is reconnecting with old friends and making new ones. I especially enjoyed seeing my old SQLskills classmates and SQL Cruise shipmates. Because of the intimate nature of these two courses, you build a solid relationship with your peers. This was very evident seeing each other at PASS over a year later and picking up right where we left off. It seemed everywhere I turned, I was running into someone from one or both of these two classes.

I met so many new people from countries all around the world. One interesting person I got to spend some time with was Benjamin Nevarez. If you haven't checked out his book, Inside the Query Optimizer, go grab a copy and prepare for your brain to melt.

Session Highlights

There were so many cool sessions, but these were some of my favorites...

Klaus Aschenbrenner had a pre-con where he took you through his Trouble Shooting Methodology. I enjoyed how he showed us some of his performance tuning techniques and a few different ways to deal with SQL problems.

Got Security?
Andreas Wolter is a security guru and he did not disappoint. In this breakneck session we took us through security basics, a few encryption gotchas, and got into some advanced topics. This was a lot to squeeze into a seventy-five minute session, so I hope to see him in a half-day session in the future.

Both Andreas and Klaus were in my SQLskills rotation last Summer. So when I saw their names on the schedule, they were some of the first sessions I picked. Both are top-notch SQL guys. If you get the opportunity to see one of them, go for it!

Adam Machanic took us deep into parallel execution plans and showed us how to tell what was going on and why. Even if you don't have parallelism issues, everything we learned can be applied to 'normal' query plans and tuning, as well. This was my favorite session of the week.

Waddayamean the server failed over?

Brent Ozar had a couple of nice sessions this year. In one, he discussed some of the projects where he has used SQL Server 2012 Availability Groups. He spoke about the need to test your DR plans befre disaster strikes so you will know if they work. We were able to discuss some real world examples involving the Stack Exchange sites and Hurricane Sandy.

Renaissance Festival Anyone?

Later in the week, Brent demoed a new version of sp_blitz which adds Plan Cache Analysis to the tool. I enjoyed that he actually went behind the scenes and explained what the code was doing instead of just showing us the finished product.

Jason Strate had an excellent session where he showed us how to dig into the Plan Cache using XML Queries. I love using the Plan Cache, and feel it is an often overlooked area of performance tuning. He took it to the next level showing us how to do this more efficiently using XML Queries. I've been chatting with Jason for a while over Yammer, so it was great to finally meet him face to face.

Suffering from Success

I'm starting to think PASS Summit is suffering from its own success. This year is the largest PASS Summit yet, but I think it has gotten a bit unmanageable. The attendance is large enough that the session rooms are spread out on opposite ends of the convention center.

Many of the sessions I tried to attend were filled to capacity and I was not able to get in. In one case, I had to go to my 4th choice session. For some of the popular sessions, people were lining up 30-45 minutes early. So if you wanted to guarantee you had a chance to see some of these sessions, then you either had to leave the previous session early, or skip it altogether.

To make matter worse, several times there were popular, filled sessions in smaller rooms; while the large room 6E was empty. I would understand the occasional miss, but this seemed to happen quite a few times.

Furthermore, with so many people it was difficult to navigate the crowds or find time to meet new people. Everyone seemed to be rushing to their next session so they could get a seat.

When I consider all of this, I think having a dedicated BI-focused conference will help alleviate the crowding problems that I experienced this year.

See You Next Year in Charlotte

Gimme the IOPS and no one gets hurt!
Next year, Pass will be held in Charlotte, North Carolina October 15-18. I think it will be cool to check out a new city for PASS, but I am a little bummed about the dates. It is coming right on the heels of the Austin City Limits Festival so it might be difficult for me to attend. We shall see...

Tuesday, November 13, 2012

SQL Server 2012 Service Pack 1

Last Wednesday, Microsoft announced the release of Service Pack 1 for SQL Server 2012. For those of you who were waiting for the first service pack before moving to SQL Server 2012, now here's your chance.

Download, and code on!

Monday, October 22, 2012

Disabling Time Sync in VirtualBox

Have you ever need to control the date or time on one of your VirtualBox Guests? Typically, the Guest OS gets the date and time from the Host.

Time Synced

Usually, this is fine. However, there are times when you may wish to control how the Guest operates. This could be for a variety of testing reasons, particularly with Edge-Case Testing.

For example, Active Directory Authentication, Mirroring and Witnesses, Replication, Clustering, etc. In each of these applications, if the servers involved get beyond five minutes from one another, they may cease to operate normally.

By disabling time synchronization between the Host and the Guest, you will be able to move each servers' time backwards and forwards while you observe how they behave. Will your application continue to function? Will you be able to trap error messages and inform the user?

VBoxManage Utility

VirtualBox includes a command-line utility, VBoxManage, that will allow you to control every aspect of your Guest VMs.

On a Windows host you will find this in the following directory: C:\Program Files\Oracle\VirtualBox

VirtualBox Manage List VMs

This example lists all the VMs that you have defined on your VirtualBox installation.

.vbox Configuration Files

Before we dive into this utility, another piece we need to look at are the VirtualBox Machine Definition Files. These are XML file which control how your Guest VMs operate. Basically, when you use the GUI to setup and control your VM, all the various settings are stored here.

On my Host, these files are stored in C:\Users\<USERNAME>VirtualBox VMs

You will see one directory for each VM that you have defined. Within that folder you will see a file with the .vbox extension. Before we go too much further, go ahead and make a copy of that file. This way, if something happens while you are working with it, you can restore your VM's configuration.

Now, use Notepad to open the .vbox file and poke around a bit. Just be careful not to modify anything yet, that comes later. You should see something like this...

VirtualBox Configuration File

This is an XML file that contains all the configuration information for your VM.

Let's go take a look at a couple of sections and see what they do.


As you might guess, this section contains 'extra' data about your Guest VM.

Extra Data Section Before


And this section contains information on how the virtual BIOS operates.

BIOS Section Before

OK, now that we've seen that, it's time to get to work.

Disable Time Synchronization

First, let's turn off Time Synchronization.

VBoxManage SetExtraData "YOURVMNAME" "VBoxInternal/Devices/VMMDev/0/Config/GetHostTimeDisabled" "1"

Note, if your VM has spaces in its name, then surround it with quotes.

If you want to turn Time Synchronization back on, use a '0'.

VBoxManage SetExtraData "YOURVMNAME" "VBoxInternal/Devices/VMMDev/0/Config/GetHostTimeDisabled" "0"

If you don't remember the names of all your VMs, try this first.

OK, let get down to business.  To turn off Time Synchronization, use this command:

Disable Time Sync Command

Now, the Extra Data section of your .vbox file should look like this.

Extra Data Section After

So now when you start your VM, you will be able to change the Date and Time. Presumably, one could simply edit the .vbox file to make this change.

Time Offset

OK, but what if you would like your VM to start with a different time. That's easy to do, as well. For this step, there isn't a command-line option, so we need to edit the .vbox file manually.

Find the BIOS section, then edit the TimeOffset line with something similar...

<TimeOffset value="-123456789"/>

It should look like this when done.

BIOS Section After

The number represents milliseconds so adjust as necessary for your needs. Also, the number can be positive or negative.

Et voila! Your Guest VM now has a completely different time from your Host.

Monday, October 8, 2012

SQL in the City - Austin

Last week I had the pleasure of attending Red Gate's SQL in the City conference as it rolled through Austin. I was excited when I first learned about this event as it generated good buzz in other cities. They have marketed it a little like a touring rock band; with 'tour stops' and concert Tees. If you are interested in attending, there are still some more stops. To find out, go visit the SQL in the City website.

What is SQL in the City?

SQL in the City is Red Gate's version of community SQL Server training. I remember learning during SQL Rally that Red Gate would no longer be participating in SQL Saturdays. At first, I was disappointed, but after I heard their reasoning, I totally understand where they are coming from.

In a nutshell, there are too many SQL Saturday events and only so many, pounds they have to spare for sponsoring community events. Consider most of their staff is located in England, so travel costs are a serious consideration. So they decided to create their own event as a way to get a better return for their time and money.

I have to admit, I was a little skeptical at attending a vendor-specific training event. However, I was pleasantly surprised at the quality of the content. Red Gate has a wide range of products that span just about every angle of SQL Server you could imagine. The keynote by CEO Simon Galbraith gave us an nice overview and short history of Red Gate and their mission.

Keynote Speech
While some of the sessions did include references to Red Gate Tools, I didn't find it to be overwhelmingly so. Out of the five sessions I attended, only one felt like a bit of a sales pitch. But even so, I was excited to learn about the features it had and was instantly thinking of how I could use it at work. Also, there was a reasonable mix of non-Red Gate presentations available.


Steve Jones
I was particularly excited about seeing Steve Jones speak. I met him briefly at SQL Saturday Dallas last year, but missed his session due to scheduling conflicts. He gave two very nice presentations about Database Maintenance and Disaster Planning. It was great listening to his stories about DBA glory and adventure. I always love listening to how people approach the human side of the job. Also, he revealed that you can always sell him your unused ACL tickets.

Grant Fritchey

Grant Fritchey gave a nice talk about using SQL Virtual Restore as part of your development sandbox environment. While I was aware of this product, I never knew exactly how it worked. I was impressed at what all you could do with it. At a high level, it works similarly to SQL Database Snapshots, but using a backup file as the basis. So you are able to make edits and updates against your virtual database and the changes are kept track of in a diff file of sorts, and the base data is read from the backup file. And, multiple people can have multiple copies of the database going at the same time. Very nice!

To finish out the day, I attended sessions from some hometown heroes. Namely, Jim Murphy and Wes Brown.

Wes Brown & Jim Murphy

Jim took us through an interactive demo of SQL 2012's Always On Mirroring. He showed us how to get the basic setup running. Then, he had a live application running against the principal database as well as the mirror. From there, he simulated a failover and showed how Always On handles transferring the database back and forth. This is some cool stuff.

To finish the day, I checked out Wes' presentation on all things storage. While I have seen a couple of his other presentations, I always come away learning something new. For me, this was how to look for clues to see if your IO performance problem lies with SQL Server, the SAN, or the Network connections between the two. Time to go chat with my SAN Admin...

Substanence, Schwag, and Shiner

Like any good conference, SQL in the City provided us with a tasty breakfast, plenty of snacks, and a surprisingly good lunch. After the conference was over, they had free books, T shirts, and most important of all, cold Shiner Bock!

Mingle Like You Mean It

The happy hour was a nice treat. It gave attendees a chance to mingle with one another, hunt down a presenter for one last pesky question, or to pick the brains of the Red Gaters for some more insight into their tools.

Check the Tour Dates

SQL in the City is a good community event and provides some great training for a nice price. If you see them coming to your town, sign up and tell your friends. Yes, this is a vendor-sponsored event so there is a certain focus on Red Gate's tools. However, I felt I got an in-depth introduction to the breadth of their offerings and how they can be applied to your SQL problems.

Tuesday, April 24, 2012

SQL Saturday #107 Houston Post Mortem

SQL Satuday #107 Houston Schwag

This past weekend I attended SQL Saturday #107 in Houston. This was a special treat for me, since Houston last year was the first SQL Saturday I ever attended. As a double bonus, I was selected to present my session on troubleshooting memory pressure, SQL Server Amnesia. I've uploaded my slides and scripts for your amusement.

No one threw tomatoes at me, and I even had a few folks stay after to ask me questions. So I consider that a victory. I even brought my own feedback forms and received some good comments to try to improve it for the next time.


This year, there was an all day pre-con. When I saw the speaker, Kalen Delaney, I signed up immediately. She presented an in-depth all-day session on Query Tuning and Plan Cache management. I enjoy performance tuning, but I'm certainly no expert. So I jumped at the chance to learn from one of the best. In my opinion, this was some of the best training I have had.

Saturday Sessions

Mike Hotek gave a great session on how to recovery from disaster. He covered the basics, and showed us how to recover your data in some unusual situations. I had tried to see him at a previous SQL Saturday, but my own speaking slot prevented me from doing so. I was glad to be able to catch him this time.

One of my favorite sessions was given by my buddy, Argenis Fernandez. He showed us some great demos on how to use the SysInternals tools to troubleshoot SQL Server problems. I've been using SysInternals for a while but I know I've only scratched the surface of what you can do with it. So this session was right up my alley.

To finish out the day, I caught Kalen Delaney's session on the Cost of Concurrency. This is an area I have been studying lately for the MCM so I figured this was a perfect session for me. She did a great job taking us through the different isolation levels and how they affect your application.


This year the conference was held at a combination of the Houston Airport Hilton and the YES Prep Public School in Aldine. I felt the hotel was great for the pre-con and I was impressed with the discount rate we got through PASS.

The school was a cool place to hold the Saturday sessions. We got to see some of the students there on the weekend working on their activities or projects.

We had some great BBQ from Hines for lunch. Brisket, sausage, chicken, I loved it! I'll strt that diet next week.


One things many people may not realize is that SQL Saturday would not exist without generous support from the sponsors. The great folks at Idera, Redgate, HP, TMS, Joes2Pros, New Horizons, Confio, PASS, Insource, Fusion-IO, Cozyroc, and SQL Watchmen all provided various levels of support.

Each company had a drawing for some cool prizes: Amazon gift card, iPad, Kindle Fire, Training Vouchers, etc.

While all the vendors were awesome, I really must give a special shout out to Idera, as they underwrote a huge portion of the Pre-Con and made it possible to get some awesome training from Kalen Delaney.

SQL Family

One of the best parts of SQL Saturday is getting to reconnect with all you SQL Peeps. It's a lot of fun catching up and trading stories, 'you won't believe what my users tried last week...'  :-)

Exchanging war stories and tips from the field is an education in itself. I got to meet some great folks, book authors, and a few SQL Legends. I love how down to earth everyone is.

I especially enjoyed picking Robert Davis' brain about the MCM program and hearing tales of his SQL Greyhound Adventure.

Next Time It's Your Turn

Keep an eye out on the SQL Saturday website and see when they'll be rolling through your neck of the woods. Check out some sessions, meet some of your fellow DBAs, trade scripts, and who may be the next one to present a session.

Monday, April 23, 2012

Zero Index Tables

When doing index analysis, another area I like to explore is how many tables have no indexes whatsoever. While there are legitimate reasons for having a table with no indexes, they are few and far between.

Here is a query to help you ferret out the zero index tables.


-- Steven Ormrod
-- 6/4/2011
-- index counts

-- tables with 0 indexes
select as 'Table Name', COUNT( as 'Index Count'
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
group by
having COUNT( = 0
order by;


We Don't Need No Stinking Indexes

Time for a Chat

Once you have identified your zero index tables, take the list to your development team for review. It may be that they are unaware of the situation. Or they may have a good reason for this setup. Either way, your application will thank you.

Wednesday, April 18, 2012

CACTUSS April 2012

Last night I presented at the local SQL Server user group, CACTUSS. Late last week I got a call asking if I could present this week. I was more than happy to jump at the chance. Since I'm presenting at SQL Saturday #107 next weekend in Houston I figured this was a great opportunity to practice my presentation.

SQL Server Amnesia

My topic last night was troubleshooting memory pressure. I covered the basics of internal and external memory pressure. Then I walked through various methods for checking on how SQL Server is using its memory.

I received what I consider to be two excellent comments. First, several folks were furiously taking notes while I rambled on. And second, at the end I got asked to share my scripts. Much better than people throwing tomatoes at me. :-)


So the CACTUSS user group is starting to grow up. We have started formalizing our structure and had our first election for President and the board members. I'm happy to say I was voted on to be a board member. Now I guess I gotta start doing some work...

Let's see...for my reign of terror I promise to drop all constraints, use only heaps, and grant sa to all developers.


Oh, and yes, I'm going to post my slides and scripts after SQL Saturday next week.

Monday, April 16, 2012

Too Many Indexes

When troubleshooting a SQL Server, I invariably look at the indexing on the database. As with most troubleshooting efforts, I try to start with a high-level view, and then drill-down to a detailed level.

One of the first things I look for are tables that have too many indexes. How many is too many? Well, it depends...

Index Counts

As a general rule, the fewer indexes the better. Typically, I screen for any tables that have more than five indexes. Now, this doesn't mean that these are bad. This is just a way for me to quickly find which tables I should spend some more time on analyzing their indexes.

By querying a couple of DMVs, sys.tables and sys.indexes, we can do a quick check of our index counts.

Number Five is Alive


-- Steven Ormrod
-- 6/4/2011
-- index counts

-- tables with N or more indexes
select as 'Table Name', COUNT( as 'Index Count'
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
group by
having COUNT( > 5
order by 'Index Count' desc;


Here is an example of a database that appears to have several tables with way too many indexes. These would be the first tables I look at when reviewing the indexing strategy of the database.

Way Too Many Indexes

Here is another example of a database with only a couple of tables with six indexes. I probably wouldn't worry about these.

Indexes on the Borderline

Get Cookin'

Indexing is a critical, but often overlooked, aspect of performance tuning. One of the first steps is identifying how many indexes you have. Once you find some fish to fry, get cooking.

Monday, April 9, 2012

SQL Server Start Time

Have you ever needed to determine when SQL Server Services were started? There are two very simple methods depending on which version of SQL you are running.
SQL 2005 - TempDB Tattle Tale

TempDB is recreated each time SQL is started or restarted. So this is a nice way to determine what time SQL Server services started.

-- Steven Ormrod
-- 5/31/2011
-- SQL Server Start Time

-- sql 2005
select create_date from sys.databases
where database_id = 2


SQL Start Time from TempDB

SQL 2008 - DMVs Delight

The DMV sys.dm_os_sys_info will give you some basic info about the underlying operating system. For example, it will tell you about the CPU cores, physical and virtual memory, buffer pool, and os workers and schedulers.

SQL 2008 introduced a new column, sqlserver_start_time, to this DMV that returns when SQL Server last started.


-- sql 2008, 2012
select sqlserver_start_time from sys.dm_os_sys_info


SQL Start Time from DMVs

Collecting DMV Stats

The main reason I am curious to know when SQL Server last started is when trouble shooting performance problems. Several of the DMVs collect stats and information about SQL Server. However, this information is wiped clean when SQL Server is restarted.

So if the stats have only been collecting for a short time, then I may not be able to draw a strong conclusion from them. Generally speaking, I like to see stats collected for at least one business cycle. For some situations, one day may be enough, or you may need a month or more. Each situation will be different.

Monday, April 2, 2012

SQL Saturday #107 Houston

SQL Saturday #107 Houston

I'm really looking forward to the upcoming Houston SQL Satruday in April. Houston is the first SQL Saturday that I ever attended. This year, I decided to submit some sessions as a presenter. Great news, one of my sessions got accepted. I will be presenting on SQL Server Memory Usage and Pressure.

Of course, I'd be lying if I said I wasn't a little nervous. When I look at the list of speakers there are so many talented people who are also presenting. Oh well, you gotta break some eggs if you want to make an omlette. Now that doesn't mean I want anyone to throw eggs at me.

One thing I'm really excited about is the pre-con session. Kalen Delany is doing a full day session on query plans and performance tuning. When I saw this announcement, I registered immediately. I think I was the third one.

If you need a place to stay, there is a block of rooms reserved at the Airport Hilton for only $79. Normally, this hotel can run into the hundreds, so this is a pretty good deal. The pre-con is being held at the hotel. And, the main conference is close by. Conveinence and a bargain; that's win win.

It constantly amazes me how awesome the SQL Community is. There are loads of great presenters and (mostly) free training availble to all who come. If you have never attended a SQL Saturday, or even if you have, come on out and check out Houston.

Monday, January 23, 2012

Non-Clustered Heaps

When I am investigating a slow-running query or looking over a database for the first time, one of the areas I tend to dive into is the Table and Index structure.  Having an understanding of how the Database is laid out at that level can help you understand what is going on at the Query and Execution Plan level.

Within that realm, I take a keen interest in any Heaps that I find.  How many are there? How are they being used?  Do they have any Non-Clustered Indexes on them?  Is the slow query issuing a lot of Reads or Writes against a Heap? etc.

I never cease to be amazed at how much I find...

A Table Taxonomy

When analyzing tables, I typically break them up into two groups: Heaps and Clustered Indexes.

Simply put, a Clustered Index helps to structure and organize the Table.  This helps you get better performance out of your database.  A full discussion of the benefits of a Clustered Index are outside the scope of this article.

A Heap is a Table that does not have a Clustered Index.  Heaps are unstructured and unordered chunks of data that are scattered around your database.  As you issue Writes against your Heaps, they get even more scattered and messy.  When you issues Reads against your Heaps, they tend to be inefficient since the Database has to look all over to find the data.  Generally speaking, heaps are bad.

Laundry Night

Think of this like your laundry.  When the dryer gets done, you have two basic options:

Option One

Time To Fold The Laundry

Grab everything as it is and shove it all into one drawer.  Socks, underwear, shirts, slacks; everything all together and not folded.  Not only it take you longer to get dressed in the morning, your clothes will be wrinkly, and your significant other will likely not appreciate it one bit.

Option Two

Sort the clothes into different stacks, fold them, and place them orderly into a few different drawers.  Now, your morning routine is much simplified.  It's easier for you to get dressed for work or that big date.  You'll look better, and the ladies (or guys) will love you for it.

Which one do you want?

Back to Databases

I'm about to paint with a broad stroke here so prepare yourself...

Generally speaking, I would say that 90% (or more) of tables need a Clustered Index.  Of those Clustered Indexes, 90% (or more) of them should be narrow, fixed-width, ever-increasing, static, and unique.  For most of those cases, 90% (or more), the best, simplest choice is an Identity column.

To restate this more simply, I would say that by default, any table that is created ought to have a Clustered Index, and that Index ought to be an Identity column.

Now don't get me wrong, there could be plenty of reasons for there being an exception to this rule.  Just have a reason and be prepared to discuss it.  Special cases are just that, special cases.  Remember, I'm speaking in general terms here...

Heaping Hierarchies

Similar to how I classify Tables, I have a method for categorizing Heaps.  It is either a Pure Heaps and Non-Clustered Heaps.

A Pure Heaps is a table with no indexes.  None, zero, nada, zip.

A Non-Clustered Heap is a Heap that has Non-Clustered Indexes on it.

Simple enough?

What's a Heap Good For

One of the common scenarios where I see Heaps being used are for Staging Tables. Perhaps you are loading in a Flat File or CSV and just need the data temporarily.  Then, another process will come along and suck the data out from that Heap into another Table with some Indexes.

Even then, you might be better off with a custom Clustered Index that is designed to help your Data Load.  Then, you might drop it and / or create a new Index that helps with your Data Migration.  And when you're done, Truncate or Drop the Table.

However, if you are keeping the data around long enough or manipulate it through large amounts of Reads or Writes, then I would argue that you are invalidating the very argument for making it a Heap in the first place.

In other words, if you are using this Table in a non-temporary manner to the point where you feel the need to create Non-Clustered Indexes on it, then what is your argument for not creating a Clustered Index on the table?

Why Is He Still Talking

OK, so now you're probably wondering what prompted this tirade?  Well, lemme tell ya...

Recently, we brought a new Software and Database package into my shop.  This is a BlackBox App from a Third-Party Vendor.  Since this was my first time to get my hands on a strange Database, I started going through some of my standard checks; starting at a higher level and then working my way down.

Since I've been burned by them so many times in the past, one of the first things I look for are Heaps.  How many, what percentage of the tables, etc.

Another check I commonly do is check for any table with more than five Indexes.  Now, five is not a hard limit.  But, if I see Tables with a high number of Indexes, then I might want to take a closer look at those tables.  Additionally, I like to check for any Tables that have Zero Indexes.

Well, what I found was an interesting intersection of indexing information.

When I analyzed the Heap count, I was a bit shocked.  What I found was that in a Database with over 5,000 Tables, over 90% of them were Heaps.

Percentage of Heaps

Only a relatively small number of the Tables, less than 200, had Zero Indexes.

What that means, by process of elimination, that almost all of the Heaps had Non-Clustered Indexes on them.  Holy Forwarded Record Batman!

Finding the Non-Clustered Heaps

In a previous post, I discussed how to obtain the overall Heap count and percentage, as well as the listing of the individual Heaps.

Now, I'll show you how to dig a little deeper and find all the Non-Clustered Heaps.


-- Steven Ormrod,
-- 1/21/12
-- Non-Clustered Heaps

-- now get the NCL counts for those Heaps
select count(*) as 'NCL Count',
OBJECT_NAME(object_id) as 'Non-Clustered Heaps'
from sys.indexes
where type_desc = 'NONCLUSTERED'
and object_id in (

-- get the Heap IDs
select i.object_id
from sys.indexes i
join sys.tables t
on i.object_id = t.object_id
where i.type_desc = 'HEAP'


group by object_id
order by 'NCL Count' desc,
'Non-Clustered Heaps';


Which gives you a result similar to this:

Non-Clustered Heaps

In this case, there were 4522 Non-Clustered Heaps, or over 95%.  Wow.

Analyzing the Non-Clustered Heaps

Once you have those names, then you still need to do a little digging.  Likely, you'll want to examine the underlying Table structure and see the Index definitions.

To make life easier, I worked up a script that will generate the relevant sp_help and sp_helpindex commands.


-- Steven Ormrod,
-- 1/21/12
-- generates the sp_helpindex and sp_help commands for Non-Clustered Heaps

-- now generate the sp_commands to help me analyze the tables and indexes
select bad.[NCL Count], + '.' + as 'Non-Clustered Heap',
'exec sp_help ''' + + '.' + + '''' as 'sp_help command',
'exec sp_SQLskills_SQL2008_helpindex ''' + + '.' + + '''' as 'sp_helpindex command'

from sys.schemas s
join sys.tables t
on s.schema_id = t.schema_id
join (

-- now get the NCL counts for those Heaps
select object_id,
count(*) as 'NCL Count'
from sys.indexes
where type_desc = 'NONCLUSTERED'
and object_id in (

-- get the Heap IDs
select i.object_id
from sys.indexes i
join sys.tables t
on i.object_id = t.object_id
where i.type_desc = 'HEAP'


group by object_id

) as bad

on t.object_id = bad.object_id

order by bad.[NCL Count] desc,
'Non-Clustered Heap';


Which gives you a result similar to this:

Non-Clustered Heaps sp_commands

Regarding sp_helpindex, I actually use Kimberly Tripp's rewrite of this command.  It shows you tons more information than the default command.  It is very easy to install, and I highly recommend it.

Happy Heap Hunting!