Friday, January 30, 2009

Free Webinar Series


Please register to join our webinar series, great for anybody who ever does any T-SQL programming, free to anybody who finds my blog, email to register:

About the Accelerated SQL Performance Series
Noted author, lecturer and consultant Jeffrey Garbus is hosting a 6 week webinar series to help you improve SQL performance. Jeff has spent 20 years helping organizations of all sizes and industries maximize database performance. If you saw him speak at the conference, you already know that your time investment for these sessions will reap rich dividends.

These 30 minute sessions will directly help you address performance issues and give you the tools to get even greater performance out of your design.

Beginning on Tuesday February 10, 2009, at 12:15 PM, the series will run weekly for 6 consecutive weeks at lunchtime.

The series will include:
1) Clustered vs. non-clustered indexes
2) Choosing indexes in a single-table query environment
3) Choosing indexes for join optimization
4) Stored procedures & performance
5) Writing characteristic & determinate functions
6) Table design & performance

Space is limited so register today.

Monday, January 26, 2009


One of my favorite quotations from a fictional character comes from “Atlas Shrugged” by Ayn Rand: There is no substitute for competence.

One of the most important things that we need to do as IT professionals is to use our own experience and competence even when taking advice from peers, or those you deem as experts. Even my own advice is sometimes not correct, when taken out of context (You will note if you follow these blog entries that I will attempt a lot of “tongue-in-cheek” humor, please take it that way).

As a public speaker (Users Groups, conventions, SQL Saturdays, etc.) I continually try to challenge those attendees at my talks to rethink some of the decisions they’ve made, based upon facts I give them based upon my own experiences. Frequently, they challenge me back (for me, this is part of the fun).

At a recent talk I gave on indexes, I said something along these lines:
“You may be able to come with some theoretical exceptions to this rule, but from a practical perspective, you should have a clustered index on every table. Without a clustered index, you are creating a hot spot at the tail end of your table’s page chain, (meaning contention when you have multiple insertions, and/or updates which cause rows to remove). In addition, updates which cause rows to move will cause data to be unbalanced towards the tail end of the table’s page chain (as well).”

A point that has come up (on at least two recent occasions) from an audience member was along the following lines:
“ I was reading a paper by {no idea who wrote the paper, if anybody reading this does, please let me know so that I can credit the author), and in it he/she stated that benchmarking has shown that insertions are fastest with NO clustered index on the table because of some benefits of page allocations …”

The first time I heard that, I responded that I’d have to read the article to see what it had to say before responding, but that my recommendations from own extensive practical experience stood. The second time I heard that, something clicked, and I responded with, “Was that a single process performing a data load of some sort, or was that a thousand concurrent processes trying to gain access to a table?”


If you are trying to load data into a table, your issue isn’t necessarily contention, it is the speed of your disk & corresponding page allocations. If you are trying to give many users access to the table, your issue is going to be both page and index contention, at update time, page split time, etc.

Over time, I’m going to be offering a variety of recommendations, every one of which will be based upon my personal experiences in 20 years of tuning SQL Servers.

Yet, I caution you: everything needs to be taken in context. Test my assertions; make sure that the environments I’m describing actually match your own.