THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Getting back to the basics with I/O

 

     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

Published Sunday, January 13, 2008 11:25 AM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AspiringGeek said:

I agree, mastering the fundamentals are critical to success.  Your citations are among the best which are available and I refer my customers to them frequently.  And what better way to monitor your disk I/O subsystem than persisting virtual file stats in your article "Getting to Know Virtual File Stats" in the September 2007 edition of SQL Server Magazine, available online to subscribers at

http://www.sqlmag.com/Article/ArticleID/96513/sql_server_96513.html

January 13, 2008 10:47 AM
 

Linchi Shea said:

Andrew Kelly in a recent post here advised visiting/revisiting the SQL Server I/O basics, and I completely

January 19, 2008 2:17 AM
 

TheSQLGuru said:

I have yet to have a single client that has done SQL Server I/O anywhere near best practice!  And it can be a real PITA to put things right after there are hundreds of databases in use or terabytes of data in place.  It isn't that the things that need to be done are even that difficult.  I think it is the world-wide lack of emphasis on using SQL Server optimally - no one spends much effort on that, at least until their throwing gobs of hardware at performance problems is proven to not work.  I likewise haven't been at a client yet that couldn't have saved lots of net dollars by having an expert work with them from start to finish on their database systems.

January 19, 2008 1:43 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement