Wednesday, September 26, 2007

Are we missing any? (indexes)

One of the really greath things about SQL Server 2005 are the DM's - Dynamic Management views. These generally provide a good insight into the servers current state and can be used to monitor the health of the server as well as tune or diagnose any problems.

A key issue in tuning any database is tuning the indexes. Not only maintaining those that are already there, but also finding out if we are missing any, or if any existing ones are not being used.

Finding the missing indexes can be done by using a group of dm views, the sys.dm_db_missing_index_xx group. This group of dm views record information about index usage from the query optimizer. For each statement the optimizer generates an optimal plan, but if the indexes does not exist for this plan, it generates another less optimal plan for the statement. The missing index is then recorded in the missing_index_detail view.

If the optimal index for the statement is present the usage of this index is recorded in the sys.dm_db_index_usage stats which can the be used to remove used indexes.

An examble of the use of the missing_index_detail view:
Use AdventureWorks

Select Firstname, Lastname
from Person.Contact
Where Lastname='Smith'

Select *
from sys.dm_db_missing_index_details

OR if you want the result for the missing index, column by column

SELECT mig.*, statement AS table_name, column_id, column_name, column_usageFROM sys.dm_db_missing_index_details AS midCROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handleORDER BY mig.index_group_handle, mig.index_handle, column_id

Please note that this example also proves the point that the missing_index views are limited in their recordings. In this examble it would be the most optimal solution also to include the Firstname column in the index. But the missing_index views has a number of limitations and should therefore be used as a guide towards the tables and columns that are missing indexes - not as a tool for fine tuning.

Further readings on subject

Tuesday, September 25, 2007

Converting column values to comma delimited list (csv)

Once again I recently met the challenge of how to convert a list of rows to one comma delimited field. (And NOT use cursors)


Converted to Once,upon,a,time

The solution surprisingly enough lies in the build in sql function COALESCE.

Declare @CSVField varchar(100)
Select @CSVField = COALESCE(@CSVField + ',','') + ColumnToCsv
From SomeTable

Select @CSVField


The Coalesce function returns the first not null value in the list of expressions. Which in this case is the ColumnToCsv value. And in this case it does it for each row in the select resultset.