Oct 11

sp_betterhelpindex: A better sp_helpindex

Microsoft’s system sprocs are nice and all, but they’re rather basic. I have written an improvement to sp_helpindex which also includes key columns, included columns, reads and writes, fragmentation, and freshness of statistics. Like sp_helpindex, it goes in master but can be called from any database. Download the script with proper tabbing.

USE master
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'sp_betterhelpindex')
   DROP PROCEDURE sp_betterhelpindex
GO

CREATE PROC dbo.sp_betterhelpindex @tableName VARCHAR(255) AS

DECLARE @sql VARCHAR(MAX)

SET @sql ='
SET NOCOUNT ON

USE ' + DB_NAME() + '

DECLARE @object_id INT, @index_id INT, @indexName VARCHAR(500)
DECLARE @keyColumns VARCHAR(2000), @includedColumns VARCHAR(2000)

SET @object_id = OBJECT_ID(''' + @tableName + ''')

DECLARE @index TABLE (
   index_id INT,
   keyColumns VARCHAR(2000),
   includedColumns VARCHAR(2000)
)

DECLARE sp_betterhelpindex_indexes CURSOR FAST_FORWARD FOR
   SELECT index_id
   FROM sys.indexes
   WHERE object_id = @object_id

OPEN sp_betterhelpindex_indexes
FETCH sp_betterhelpindex_indexes INTO @index_id
WHILE @@fetch_status = 0
BEGIN
   SELECT @keyColumns = NULL, @includedColumns = NULL

   SELECT @keyColumns = ISNULL(@keyColumns + '', '', '''') +
   columns.name + (CASE WHEN is_descending_key = 1 THEN ''(-)'' ELSE '''' END)
   FROM caV2.sys.indexes indexes
   INNER JOIN caV2.sys.index_columns index_columns ON indexes.object_id = index_columns.object_id
   AND indexes.index_id = index_columns.index_id
   INNER JOIN caV2.sys.columns columns ON index_columns.object_id = columns.object_id
   AND index_columns.column_id = columns.column_id
   WHERE indexes.object_id = @object_id AND indexes.index_id = @index_id AND is_included_column = 0
   ORDER BY index_column_id

   SELECT @includedColumns = ISNULL(@includedColumns + '', '', '''') +
   columns.name + (CASE WHEN is_descending_key = 1 THEN ''(-)'' ELSE '''' END)
   FROM caV2.sys.indexes indexes
   INNER JOIN caV2.sys.index_columns index_columns ON indexes.object_id = index_columns.object_id
   AND indexes.index_id = index_columns.index_id
   INNER JOIN caV2.sys.columns columns ON index_columns.object_id = columns.object_id
   AND index_columns.column_id = columns.column_id
   WHERE indexes.object_id = @object_id AND indexes.index_id = @index_id AND is_included_column = 1
   ORDER BY index_column_id

   INSERT INTO @index VALUES (@index_id, @keyColumns, ISNULL(@includedColumns, ''''))

   FETCH sp_betterhelpindex_indexes INTO @index_id
END
CLOSE sp_betterhelpindex_indexes
DEALLOCATE sp_betterhelpindex_indexes

SELECT sys.indexes.name,
   (CASE WHEN is_primary_key = 1 THEN ''primary key, ''
   WHEN is_unique = 1 THEN ''unique, ''
   ELSE '''' END) + LOWER(sys.indexes.type_desc) AS type,
   keyColumns, includedColumns,
   sys.filegroups.name AS [filegroup],
   user_seeks + user_scans + user_lookups AS readsByQueries, user_updates AS updates,
   CAST(physicalStats.avg_fragmentation_in_percent AS INT) AS [fragmentation %],
   STATS_DATE (@object_id , i.index_id) AS statisticsUpdated, i.index_id
FROM @index i
INNER JOIN sys.indexes ON sys.indexes.object_id = @object_id AND i.index_id = sys.indexes.index_id
INNER JOIN sys.filegroups ON sys.indexes.data_space_id = sys.filegroups.data_space_id
INNER JOIN sys.dm_db_index_usage_stats usage ON usage.object_id = @object_id
   AND i.index_id = usage.index_id
   AND usage.database_id = DB_ID()
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), @object_id, NULL, NULL, NULL) physicalStats
   ON i.index_id = physicalStats.index_id AND alloc_unit_type_desc != ''LOB_DATA''
ORDER BY sys.indexes.type_desc, keyColumns, includedColumns
'

EXEC(@sql)

Attached File sp_betterhelpindex.sql_.txt

Oct 09

Storing Variable-Length Decimal Data

If you have lots (and I mean LOTS — we have 175 GB of school-related data, and our biggest per-table savings would have been 1.5 MB) of nulls in decimal/numeric columns with large precision, you may be able to save noticeable space with vardecimal, which requires only 2-4 bytes per column when the column is 0 or null.

Oct 08

Spinlocks

A spinlock is a very lightweight lock used for accessing things which must be synchronized, like caches. They don’t show up in the queryable wait indicators. When there is contention for them, you’ll see lots of runnable (waiting to run) processes. This is more likely to happen on x64 systems because the caches can get bigger.