Andrew Kelly : Best Paractices

Backup File Naming Convention

Andrew Kelly — Fri, 05 Oct 2012 19:41:00 GMT

I have been asked this many times before and again just recently so I figured why not blog about it. None of this information outlined here is rocket science or even new but it is an area that I don’t think people put enough thought into before implementing....(read more)

Speaking in Raleigh NC 9-20-2011

Andrew Kelly — Thu, 15 Sep 2011 13:54:05 GMT

If you are in the area I will be speaking at the Triangle SQL Server Users Group meeting on Tuesday the 20th of September 2011. The session is on TempDB Best Practices and all of the details for the topic, directions etc. can be found here . ...(read more)

SQL Server 2008 R2 Analysis Services Operations Guide

Andrew Kelly — Wed, 01 Jun 2011 22:03:00 GMT

Some members of the SQL CAT Team just released an Operations Guide for SQL 2008 R2 Analysis Services that can be found here . While I am not a SSAS person by any means this looks pretty cool and worth while for anyone who does work with it so have a look....(read more)

Practical Performance Monitoring and Tuning

Andrew Kelly — Sat, 09 Apr 2011 20:54:33 GMT

My next performance class will be in San Francisco, CA on May 9th thru the 11th. I haven’t been to San Francisco in about 10 years and I am looking forward to getting back there. The material is a bit refined over the first class and I added a...(read more)

System Center Advisor Collection Details

Andrew Kelly — Thu, 07 Apr 2011 03:39:26 GMT

The System Center Advisor team (formally Project Atlanta) now lists some pre-release documentation on exactly what information they collect and how you can view it before it gets uploaded. So if you are wondering what they collect you can have...(read more)

Project Atlanta Has a New Name

Andrew Kelly — Tue, 22 Mar 2011 17:57:23 GMT

Brad Anderson just completed a keynote in Las Vegas at the Microsoft Management Summit 2011 and announced the new name for what was originally called Project Atlanta. Dot da da dah… And the new name is: System Center Advisor . You can...(read more)

Practical Performance Monitoring and Tuning Event

Andrew Kelly — Sat, 11 Dec 2010 14:30:21 GMT

For any of you who may be interested or know of someone in the market for a performance Monitoring and Tuning class I have just the ticket for you. It’s a 3 day event that will be held in Atlanta Ga. on January 25th to the 27th 2011. For those...(read more)

Updates for Some 2008 Tools

Andrew Kelly — Sun, 20 Jun 2010 03:03:00 GMT

There have been a few releases over the last few days for some SQL Server 2008 tools that you may be interested in. The first one is the 2008 R2 Best Practices Analyzer found here: http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/06/19/sql-server-2008-r2-best-practices-analyzer-is-now-available.aspx...(read more)

Speaking in Raleigh NC June 15th

Andrew Kelly — Wed, 02 Jun 2010 20:53:00 GMT

Just a heads up to those in the area that I will be speaking at the (TriPASS) Raleigh SQL Server user group on the 15th of June 2010. The topic is Storage & I/O Best Practices. The abstract is listed below: SQL Server relies heavily on a well configured...(read more)

Speaking at Best Practices in DC Next Month

Andrew Kelly — Fri, 10 Jul 2009 02:34:50 GMT

While I have been speaking about Best Practices for years this is the first time that I will speak at an actual Best Practices conference :). I have 3 sessions entitled: SQL Server Configuration Best Practices Storage and I/O Best Practices for...(read more)

Cleaning up Un-Named Defaults

Andrew Kelly — Fri, 26 Jun 2009 16:07:00 GMT

I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write...(read more)

How Dirty are your Reads?

Andrew Kelly — Fri, 10 Apr 2009 23:08:11 GMT

As a SQL Server consultant I get to see a lot of different sets of code written by all sorts of database developers from all over the world. And I have to say that one thing that is clear is most developers make abundant use of the NOLOCK query hint or the READ UNCOMMITTED isolation level. Now don’t get me wrong, I am NOT trying to say this is bad and people should not do that. There is a place and time for all the features and hints SQL Server has to offer and these can be quite handy indeed. The question is “Are these methods overused or even abused” by the average shop these days? I had a recent conversation with someone close to the SQL Server dev team who stated that some of the members of the dev team would be surprised to hear that. Personally I am surprised to hear that they would be surprised to hear that :). So I ask the rest of the known universe out there what you think. Are dirty reads abused more than they should be? Technically if they weren’t doing anything wrong at all it wouldn’t be abuse now would it? But I think you get the point :).

But before you answer that question let me talk a little about what the dangers of dirty reads using either NOLOCK hints or setting the isolation level to READ UNCOMMITTED are. None of this information is new and lots of people have already written about the pitfalls many times. Itzik Ben-Gan for one, publically speaks on this subject often. But I find developers on a regular basis that fail to realize all of the implications. Lets touch on the more common ones here:

Dirty Reads:

This sounds like an obvious one but many people simply don’t understand that using NOLOCK as a hint doesn’t just prevent you from blocking other users, it allows you to potentially read dirty data. You never know when or if you have read dirty data so unless the data is read-only or static so you may be making decisions based on incorrect values. After all this is the main purpose of locking in the first place right.

Read Errors:

What happens if you are in the process of reading a row or page that someone deletes or reading a page that is in the process of splitting? You can and most likely will get an error. This is a fatal error and most applications are not set to properly handle these types of errors.

Reading Rows Twice:

If you are in the process of doing an IAM scan and read a page that gets split after you just read it you can actually read up to half the rows from that page again since the split page will go to the end of the IAM chain.

Missing Rows:

This is the opposite of the scenario above. You can actually miss rows if a page is moved back in the IAM chain before you read it.

So the bottom line is that if your application depends on accuracy and data integrity then you probably shouldn’t be using this isolation level for that part of the applications queries. Yes as I mentioned earlier there are certainly good uses for this and it can speed up certain queries and reduce the number of locks generated. But as with any technology there are always two sides to consider as nothing is for free. Are you writing an app that is using NOLOCK and calculating my checking account balance? I certainly hope not. Unless you want to credit me with some extra cash in the process :). But if your app doesn’t take into account all of the things we just discussed I say you are abusing it. If it costs you accuracy / integrity or stability when it wasn’t intended then yes you must seek help as you are an abuser :). So what do you think, is the world full of abusers?

Getting back to the basics with I/O

Andrew Kelly — Sun, 13 Jan 2008 15:25:13 GMT

One of the most common trends that I see related to performance & scalability with SQL Server is a poorly configured or implemented storage subsystem. There is a ton of information out there on this subject but in my opinion that is part of the problem. Too much data is not always a good thing and there is a lot of misinformation out there as well. I also see a lot of systems that were configured based on the advice someone gave them or they read about which may have been great for that other system but not necessarily for theirs.

So I think it is time we got back to the Basics and Best Practices when it comes to I/O in SQL Server. Again in my opinion and my experience from seeing systems all over the world this list should get people off on the right foot if they are not sure what they need or how they should approach a proper I/O configuration. The first article hits the nail right on the head and is a great place to start. The next two give a very good understanding of what actually goes on when SQL Server makes I/O requests and explains the terminology so that everyone can talk the same language. The 4th link is a relatively new white paper to most that should be sort of a bible and gone over long before you deploy or even buy the equipment for your next SQL Server. And finally there is a link that everyone should be aware of that gives you access to a whole host of white papers that should be read as needed.

SQL Server Storage TOP 10 Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

SQL Server 2000 I/O Basics

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

I/O part 2 for updates to SQl2000 SP4 and SQL2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

SQL Server I/O Pre-Deployment Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Overall SQL Server Best Practices

http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx