Archive for December, 2014

Problem: Table size much larger than expected

December 30, 2014

Problem: 80GB table should only be around 10GB

Solution: Identified excessive fragmentation using dm_db_index_physical_stats. rebuilt the table reduced fragmentation and reduced the table size down to a more appropriate size. Fragmentation likely caused by frequent updates on a table. In this case the table was a heap so we performed an alter table.

On an indexed table we can use alter index – reorganize for a similar effect.

eg

select OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(10,NULL,NULL,NULL,’LIMITED’) ORDER BY fragment_count DESC

ALTER INDEX ALL ON schema.tab;e REORGANIZE

ALTER TABLE schema.table REBUILD

Advertisements

memory grant percentage – resource governor

December 2, 2014

the memory grant percentage value in resource governor workload group relates to the percentage of the target memory that any process in the resource group can take.

to find this value: SELECT * FROM sys.dm_exec_query_resource_semaphores WHERE resource_semaphore_id = 0