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 knows...you 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 t.name as 'Table Name', COUNT(i.name) as 'Index Count'
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
group by t.name
having COUNT(i.name) = 0
order by t.name;


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 t.name as 'Table Name', COUNT(i.name) as 'Index Count'
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
group by t.name
having COUNT(i.name) > 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.