Chasing down a hard to find error. The SQL Server Engine continues to use an unhelpful index.
Unfortunately, the system is based on SQL Server v2005 and so unable to use filtered index.
Review Table’s Storage Allocation
Let us look at the index sizes and see whether it is choosing the awful index simply because it is much smaller.
declare @object sysname set @object = 'dbo.CSRNotes' select [schema] = tblS.[name] , [object] = tblO.name , [index] = tblI.name , [indexID] = tblI.index_id , tblAU.type_desc , [fileGroup] = tblFG.name , [totalPages] = tblAU.total_pages , [usedPages] = tblAU.used_pages from sys.system_internals_allocation_units tblAU join sys.system_internals_partitions tblIP on tblAU.container_id = tblIP.partition_id Join sys.partitions As tblP On tblIP.object_id = tblP.object_id And tblIP.index_id = tblP.index_id inner join sys.indexes As tblI On tblP.object_id = tblI.object_id And tblP.index_id = tblI.index_id inner join sys.objects As tblO On tblP.object_id = tblO.object_id inner join sys.schemas As tblS On tblO.schema_id = tblS.schema_id inner join sys.filegroups as tblFG On tblAU.filegroup_id = tblFG.data_space_id where tblP.object_id = object_id(@object) order by tblI.index_id
- It is not more narrower, but also noticed an index bearing ROW_OVERFLOW_DATA moniker.
- ROW_OVERFLOW_DATA ?
- It is worth noting that there no pages allocated nor used by the ROW_OVERFLOW_DATA item
Investigate Data Length
Let us investigate our actual record column’s data length and see whether we really need the over-sized column definition.
Query Data – Ordering by Length
Column – Notes
select top 10 [notes_length] = datalength(notes) , [resolution_length] = datalength([resolution]) from [dbo].[CSRNotes] order by isNull(datalength(notes), 0) desc
Column – Resolution
select top 10 [notes_length] = datalength(notes) , [resolution_length] = datalength([resolution]) from [dbo].[CSRNotes] order by isNull(datalength(resolution), 0) desc
- As our biggest column is less than 4000, we can reduce the column’s length from varchar(8000) to varchar(4000).
- For Column 2, Resolution, our largest row is less than 1000, and we can change our definition from 4000 to 2000.
Change Tables Column Max Data length
Using Alter Table \ Alter Column, change table’s column data length
alter table [dbo].[CSRNotes] alter column [notes] varchar(4000) null alter table [dbo].[CSRNotes] alter column [resolution] varchar(2000) null
Harden changes by re-writing data
update [dbo].[CSRNotes] set [notes] = [notes] ; update [dbo].[CSRNotes] set [resolution] = [resolution] ;
Rebuild Clustered Index
ALTER INDEX [PK_CSRNotes] ON [dbo].[CSRNotes] REBUILD
Revisit Table’s Storage Allocation
We no longer have the the Row OverFlowData record.
Review Table’s Fragmentation
Another area we took a quick look at is the fragmentation level of the table and indexes.
Here is the code:
declare @object sysname declare @objectID int set @object = 'dbo.CSRNotes' set @objectID = object_id(@object) SELECT [schema] = object_schema_name(ctePS.object_id) , [object] =object_name(ctePS.object_id) , [indexName] = tblSI.name , [indexID] = tblSI.index_id , [indexType] = tblSI.type_desc , [pageCount] = ctePS.[page_count] , [storageInMB] = cast ( (ctePS.[page_count] *1.0/128) as decimal(10,2) ) , [fragmentation%] = cast ( ctePS.avg_fragmentation_in_percent as decimal(10,2) ) FROM sys.dm_db_index_physical_stats ( db_id() , @objectID , NULL , NULL , 'LIMITED' ) ctePS inner join sys.indexes tblSI on ctePS.[object_id] = tblSI.object_id and ctePS.index_id = tblSI.index_id where index_level = 0
We can see that there is a bit of fragmentation that we should likely address.
The change did not help us achieve the better index, and so our problem is likely a bug with the SQL Server Engine’s Optimizer.
- Looking into SQL Server page types