T-SQL Tuesday #004 – A Roundup

If I am counting right, it looks like 26 separate blog posts were sent in for this month’s T-SQL Tuesday “IO, IO, It’s Off To Disk We Go”

I am still in the process of digesting them all but wanted to at least start the roundup post and at least do a quick scan of each entry, if not read it all. So far it is looking like a lot of great posts that all are worth re-reading. I might even try printing with the option to print all linked articles (never used it before) and have a closer look at these posts on IO.

Thanks for participating! If I somehow missed a post, please leave a comment and I’ll update the roundup and tweet the update out. My late night double check looked like none were missed.

First Time Participants

In no particular order give a big welcome to the T-SQL Tuesday party to the following bloggers who (as near as I can tell) contributed to their first T-SQL Tuesday:

Paul Randal talked about the behind the scenes goings on of a DMV we should all, hopefully, be familiar with – sys.dm_db_index_physical_stats. He relives his “younger” days and talks about where the “WITH FAST” option came from in the older DBCC SHOWCONTIG (Guess who wrote it?)

Michael Zilberstein explores perfmon, io counters and RAID levels in capacity planning. A good read with a good reminder at the end.

Glenn Allen Berry shared some scripts from his impressive (and well known) arsenal of T-SQL scripts to help us detect our IO pressure in SQL Server 2008. Great queries to help see if you are suffering from IO issues or not.

Robert Davis showed us a way to tell if our temporary objects are being stored on disk or in memory. I haven’t had a chance to play with the scripts he shares but it is on my short list of things to play with this week .

Kendal Van Dyke blogged about a topic I was fortunate enough to watch him present on this past weekend at SQL Saturday #33 –> Solid State Drives. This is the start of a new series he is starting, so I suggest you subscribe to his blog in your favorite reader to keep up with his posts. Solid states are here, getting better and we’ll be using the more and more each year.

Jeremiah Peschka went someplace else 🙂 Great forray into a different Io altogether. Check it out and you’ll see what I mean.

Brent Ozar graced T-SQL Tuesday with his presence through a post about his findings with some Fusion IO drives he got his hands on for testing. Neat stuff but be sure to read about his caveats at the end. The technology is still being ironed out and he discusses some of the risks I have heard from others when it comes to some of the SSD technologies. Still may very well apply to some of your workloads and environments.

Gethyn Ellis took a walk down recent memory lane and gave us some good reminders from his walk. Sometimes it takes an “oops” to learn some key lessons and it’s even better when those lessons are on a test server as Gethyn’s was. I’ll be adding his blog to my feed reader (which in turn means my large blog roll) and I’ll be checking out more posts from him.

Remus Rusanu fit a post about large Service Broker Queues into the theme. He talked about some of the issues affecting them and reminds us that good database maintenance must include queue maintenance in environments with Service Broker.

Brian Henderson works for EMC and likes to blog about storage topics. He posts a link to a document a colleague of his wrote about SQL Server consolidation options. Interesting perspective.

Nicholas Cain Reminded us about the benefit of splitting files and filegroups onto separate spindles. Shows a good way to see what the hogs are with the DMVs. I will be adding this blogger to my blogroll also.

Merrill Aldrich shares some great lessons he picked up while doing a really large storage implementation. He worked with a new EMC symmetrix SAN with some SSDs on board. He got some nice results and his lessons should serve as reminders to all of us.

Stefan Bauer Gave us a blog post as part of an ongoing series he had on Disk Performance. In this post he covers some concepts around some of the performance monitor counters one could look at when analyzing SQL Performance. He also created a poll asking about the type of IO folks use (SAN, Local Disk, iSCSI, etc).

Pinal Dave Joined the T-SQL Tuesday fray (yay!) with a post about Covering Indexes and talks about where and how they can be helpful. He includes a good reminder in his disclaimer – covering indexes are not a one stop shop for all problems. You need to consider all of your workload when looking at indexes.

Denny Cherry is one of the people (like Paul Randal and Kendal Van Dyke, among others) who would make this their first T-SQL Tuesday with this topic. I wasn’t let down. Denny is just as much into storage as he is SQL (maybe even more, Denny?). This post helps show us how to find out where our disk bottlenecks are and how to go about troubleshooting at each area (SQL, Windows, Array)

Experienced T-SQL Tuesday Contributors

Again, in no particular order (just the order I clicked on them in the browser tabs I opened to read all the posts), here are the contributions from the folks who have participated in prior T-SQL Tuesdays:

Brad Schulz – If you’ve read a T-SQL Tuesday roundup before, I don’t have to say anything else. So I lied, I said no particular order but I had to put this one first. It is a long winding read but worth every minute. He takes us on a recursive journey through some CTEs.

.sETC emos hguorht yenruoj evisrucer a no su sekat eH .etunim yreve htrow tub daer gnidniw gnol a si tI .tsrif eno siht tup ot dah I tub redro ralucitrap on dias I ,deil I oS .esle gnihtyna yas ot evah t’nod I ,erofeb pudnuor yadseuT LQS-T a daer ev’uoy fI – zluhcS darB

Jason Brimhall reminds us that reducing the IO cost of a query is a worthwhile exercise. He uses a fun example to illustrate this to us. Check out his post and go hunt for the higher IO queries in your own environment. You can do it!

Sankar Reddy started off with a post about log blocks and log block checksums. Not to be outdone (by himself?) he posted a second post about a very important concept, Forwarded Records. Are you suffering from the ill effects of forwarded records? Check out his post and find out.

Aaron Nelson eeked in a bit past the deadline but I let him in with the couple hours of grace period. He was busy the past couple weeks preparing for his multiple sessions at SQL Saturday 33, I’m sure. He combined two themes and posted about an interesting troubleshooting journey and some lessons he learned along the way.

Kalen Delaney agrees with some of the other posters that looking at IO Statistics is a useful tool. Yet with one convincing and simple example, she shows us that they aren’t so useful in isolation these days. I use IO Statistics often when tuning but I also look at the big picture. If you tune to IO only, you can get caught up in trying to tune to lower reads at the cost of CPU or Duration – don’t 🙂

Michael Swart goes to the other side of the coin a bit. He talks about his own experiences and that tuning to IO is one of the best ways he has seen to improve query performance. I think if you take his advice, cross apply some of Kalen’s and Rob’s advice you’ll do just fine. I agree with Michael, in the majority of cases I come across, reducing the reads reduces the duration. Just be cautious like Kalen said and don’t go overboard like Rob said.

Rob Farley also weighed in on the whole query tuning to IO question. He showed an example involving string manipulation where more IOs did not equal less duration. In fact the opposite happened. Rob was also the host of T-SQL 003 on Relationships. Check out his roundup to see the posts for that one.

Michael Cole’s article should give you pause if you are presuming your use of Transparent Data Encryption is encrypting your Filestream data. Read his post and see why it isn’t, the picture makes it clear.

Aaron Bertrand has had a great post series on Bad Habits to kick. He added a smorgasbord of bad IO practices to avoid in this installment. He starts off with his thoughts on the points raised by Kalen, Michael and Rob above when he reminds us that Ignoring IO in queries is a bad habit to kick.

Oops! Apparently I missed a post from a twitter friend and SQL geek, Jorge Segarra! Now, technically speaking, it is up to the blogger to ensure that a comment appears on the invite post and post a manual comment if not but I’ll post it because it is a great read. So please, check out his post. He blogged about his experience with a NetApp SAN and he drew important conclusions to bring back to anything you do, really.

Phew!

That was a lot of summarizing. 🙂 Some really great posts and ensuing discussions from this one. There are actually a few that I’ll be printing out for coworkers to peruse. Some made me laugh, none made me cry. Thanks again to Adam Machanic for thinking up and organizing this concept! It is a lot of fun and I am looking forward to finding out who is hosting next month’s! If you want to participate I suggest you look at the #tsql2sday twitter search stream. If the next organizer doesn’t tweet, I am sure others will when they find out about the topic and dates. You don’t even need twitter to search for the stream. Just click the link. Even better? Just subscribe to the RSS feed for that one search tag here. You will only see new tweets that mention #tsql2sday.

I didn’t get a chance to finish my draft and submit it but it was heading out along the same lines of tune to reads so it was done by some great folks already anyway 🙂

Thanks again for participating, feel free to leave any comments about any of the posts or my comments below or on the original posts (or both).

Updated – Removed a post which was potentially plagiarized. See Denny Cherry’s post about this here.