October 2005 - Posts

DMV of the Day - sys.dm_db_missing_index_details

Dynamic Management Views (DMVs) are one of the nicest new DBA manageability feature in SQL Server 2005. The feature exposes much of what was hidden or unavailable in SQL Server 2000. Let me start the series with one of my favorite DMVs for the lazy DBA like myself: sys.dm_db_missing_index_details. Well, I won't start by saying I'm lazy perse, but this does give you a nice starting point to investigate.

The sys.dm_db_missing_index_details DMV gives you hints on what indexes the database engine thinks you need on a given database. It gives you the object name where the index would be useful and the columns that it would recommend. Use the object_name(object_id) function to translate the object id to it's real logical name. Where this is going to be extremely useful is when you go into a new environment for the first time and need a general gist of where the weaknesses are. Then, you can go logically through the system to make sure this gist is correct.

There are a few important notes with DMVs. DMVs are only in scope until you stop and start SQL Server. After that, the DMVs are all flushed and it may take time to get the true statistics again. The other thing to keep in mind with the sys.dm_db_missing_index_details in particular is the recommendations it gives you are not the law. Much like the Databaes Engine Tuning Advisor, it may recommend indexes that are 16 columns wide, which is probably not practical in your environment. Use its recommendations with a bit of untrustingness.

Sarbanes-Oxley Hell

<rant>

I, like many of my IT cohorts have been suffering through Sarbanes-Oxley (SOX) hell for the past 2 years. The goal of SOX is to make CEOs accountable for their entering false financial statements and rigging a stock. Somehow, this has blown out of control from a financial reporting regulation to IT regulation with very wide variants of interpretation. For example, some companies interpret it as a SOX gap that a DBA can update data in production without audit while others don’t care. So, some small public companies must purchase expensive tools to audit for such activities.

The viability of SOX as an enforceable law it seems is in question even in the latest ruling by a jury in the HealthSouth case. The HealthSouth case was the first court case where a CEO was convicted and ultimately let go free. The thing that gets me is if we can’t enforce the core of SOX (financial accountability), what’s the point into IT trying to comply to the extent we do today.

Let’s not get crazy here people. One company told me that building a server now takes more than 60 pages of documentation and about 800 hours of cumulative work because of SOX. This is going to kill America’s innovation and efficiency in the long run. It also parallelizes an organization when you know it’s going to take that much work to put a server into production, some people will think, “What’s the point?”.

As an IT professional, don’t forget to ask how you’re bringing value to your company and customer. SOX does very little of that and while I really appreciate the reason for it, reasonable minds must prevail.

</rant>