wtorek, 7 stycznia 2014

Aktualizacja kolumny będącej kluczem w indeksie

Nie wiele osób piszących zapytania SQL-owe zastanawia się nad tym co tak naprawdę SQL Server robi aby wykonać operację. Aktualizacja kolumny x w tabeli może mieć zupełnie inne skutki od aktualizacji kolumny y, która jednocześnie jest kluczem w indeksie.

Poniżej kawałek kodu, który demostruje skutki aktualizacji kolumny będącej kluczem w indeksie i takiej, która nim nie jest.
   1: USE master
   2: GO
   3:  
   4: SET NOCOUNT ON
   5: GO
   6:  
   7: --Create temp tables for various results
   8: IF OBJECT_ID('tempdb..#TransactionLog') IS NOT NULL
   9: DROP TABLE #TransactionLog
  10: GO
  11:  
  12: IF OBJECT_ID('tempdb..#IndexStats') IS NOT NULL
  13: DROP TABLE #IndexStats
  14: GO
  15:  
  16: IF OBJECT_ID('tempdb..#DataTable') IS NOT NULL
  17: DROP TABLE #DataTable
  18: GO
  19:  
  20: --Create database if it doesn't exist
  21: IF NOT EXISTS(SELECT * FROM sys.databases WHERE NAME = 'SQLTest')
  22: CREATE DATABASE SQLTest
  23: GO
  24:  
  25: --Set to simple recovery model to be able to quickly clear the transaction log
  26: ALTER DATABASE SQLTest SET RECOVERY SIMPLE WITH NO_WAIT
  27: ALTER DATABASE SQLTest SET AUTO_CREATE_STATISTICS OFF
  28: GO
  29:  
  30: USE SQLTest
  31: GO
  32:  
  33: --Create sample table if it doesn't exist
  34: IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'IndexUpdate')
  35: CREATE TABLE IndexUpdate (
  36:     COL1 INT,
  37:     COL2 INT IDENTITY,
  38:     COL3 VARCHAR(1000)
  39: )
  40: GO
  41:  
  42: --Delete all records from the table
  43: TRUNCATE TABLE INDEXUPDATE
  44: GO
  45:  
  46: --Create clustered index on COL1 with FILLFACTOR 100 to demonstrate side effects of key column modyfication
  47: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE NAME = 'IDX_INDEXUPDATE')
  48: CREATE CLUSTERED INDEX IDX_INDEXUPDATE ON INDEXUPDATE(COL1) WITH(FILLFACTOR = 100, SORT_IN_TEMPDB = ON)
  49: GO
  50:  
  51: --Insert first row to do all allocations
  52: INSERT INTO INDEXUPDATE(COL1, COL3) VALUES (1,'Do the allocations')
  53: GO
  54:  
  55: --Insert more records into the table
  56: DECLARE @I INT
  57: SET @I = 2
  58: WHILE @I <= 1000
  59: BEGIN
  60: INSERT INTO IndexUpdate(COL1,COL3)
  61: VALUES(@I, 'Another Record')
  62: SET @I = @I+1
  63: END
  64:  
  65: --Rebuild the index to remove fragmentation
  66: ALTER INDEX IDX_INDEXUPDATE ON INDEXUPDATE REBUILD
  67: GO
  68:  
  69: --Clear transaction log
  70: CHECKPOINT
  71: GO
  72:  
  73: --Update non key column
  74: UPDATE INDEXUPDATE SET COL3 = 'Modify non key column' WHERE COL1 = 2
  75: GO
  76:  
  77: --Get index statistics after update non key column operation
  78: SELECT 
  79: 'Non Key Column Update' as [Action],
  80: OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
  81: ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
  82: indexstats.avg_fragmentation_in_percent 
  83: INTO #IndexStats
  84: FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
  85: INNER JOIN sys.indexes ind  
  86: ON ind.object_id = indexstats.object_id 
  87: AND ind.index_id = indexstats.index_id 
  88: WHERE OBJECT_NAME(ind.OBJECT_ID) = 'IndexUpdate'
  89: GO
  90:  
  91: --Get transaction log details after non key column modyfication
  92: SELECT
  93: 'Non Key Column Update' as [Action],
  94: [Operation], 
  95: [Context], 
  96: [Transaction ID],
  97: [Log Record Length], 
  98: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([Page ID], 1, 4), 2)) as [File ID],
  99: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([Page ID], 6, LEN([Page ID]) - 5), 2)) as [Page ID],
 100: [Slot ID], 
 101: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([New Split Page], 1, 4), 2)) as [New Split File ID],
 102: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([New Split Page], 6, LEN([New Split Page]) - 5), 2)) as [New Split Page ID],
 103: [Rows Deleted], 
 104: [Bytes Freed], 
 105: [Description] 
 106: INTO #TransactionLog
 107: FROM fn_dblog(NULL, NULL)
 108: WHERE [Operation] NOT IN (
 109: 'LOP_BEGIN_CKPT',
 110: 'LOP_END_CKPT'
 111: )
 112: GO
 113:  
 114: --Get data including physical location of the record (SQL Server 2008+)
 115: select
 116: 'Non Key Column Update' as [Stage],
 117: I.COL1,
 118: I.COL2,
 119: I.COL3,
 120: P.FILE_ID AS [FILE ID],
 121: P.PAGE_ID AS [PAGE ID],
 122: P.SLOT_ID AS [SLOT ID]
 123: INTO #DataTable
 124: from IndexUpdate I
 125: cross apply fn_physloccracker(%%physloc%%) as P
 126:  
 127: --Clear the transaction log again
 128: CHECKPOINT
 129: GO
 130:  
 131: --Update key column (one record)
 132: UPDATE IndexUpdate SET COL1 = 2 WHERE COL1 = 1
 133: GO
 134:  
 135: --Get index statistics after key column update
 136: INSERT INTO #IndexStats
 137: SELECT
 138: 'Key Column Update' as [Action],
 139: OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
 140: ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
 141: indexstats.avg_fragmentation_in_percent 
 142: FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
 143: INNER JOIN sys.indexes ind  
 144: ON ind.object_id = indexstats.object_id 
 145: AND ind.index_id = indexstats.index_id 
 146: WHERE OBJECT_NAME(ind.OBJECT_ID) = 'IndexUpdate'
 147: GO
 148:  
 149: --Get transaction log details after key column modyfication
 150: INSERT INTO #TransactionLog
 151: SELECT
 152: 'Key Column Update' as [Action],
 153: [Operation], 
 154: [Context], 
 155: [Transaction ID], 
 156: [Log Record Length],
 157: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([Page ID], 1, 4), 2)) as [File ID],
 158: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([Page ID], 6, LEN([Page ID]) - 5), 2)) as [Page ID],
 159: [Slot ID], 
 160: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([New Split Page], 1, 4), 2)) as [New Split File ID],
 161: CONVERT(bigint, CONVERT(VARBINARY(MAX), substring([New Split Page], 6, LEN([New Split Page]) - 5), 2)) as [New Split Page ID],
 162: [Rows Deleted], 
 163: [Bytes Freed], 
 164: [Description] 
 165: FROM fn_dblog(NULL, NULL)
 166: WHERE [Operation] NOT IN (
 167: 'LOP_BEGIN_CKPT',
 168: 'LOP_END_CKPT'
 169: )
 170: GO
 171:  
 172: --Get data including physical location after key column modyfication (SQL Server 2008+)
 173: INSERT INTO #DataTable
 174: select
 175: 'Key Column Update' as [Stage],
 176: I.COL1,
 177: I.COL2,
 178: I.COL3,
 179: P.FILE_ID AS [FILE ID],
 180: P.PAGE_ID AS [PAGE ID],
 181: P.SLOT_ID AS [SLOT ID]
 182: from IndexUpdate I
 183: cross apply fn_physloccracker(%%physloc%%) as P
 184:  
 185: --Display transaction log
 186: SELECT
 187:     [Action],
 188:     [Operation],
 189:     [Context],
 190:     [Log Record Length]
 191: FROM #TransactionLog
 192: /*
 193: SELECT * FROM #IndexStats
 194: SELECT * FROM #DataTable
 195: */
 196:  
 197: --Get aggregated results for the test
 198: DECLARE @PAGE_ID INT
 199: DECLARE @ROWS1 INT
 200: DECLARE @ROWS2 INT
 201: DECLARE @ROWS3 INT
 202: DECLARE @ACTIONS1 INT
 203: DECLARE @ACTIONS2 INT
 204: DECLARE @FRAGMENTATION1 DECIMAL(18,2)
 205: DECLARE @FRAGMENTATION2 DECIMAL(18,2)
 206: DECLARE @LOGSIZE1 INT
 207: DECLARE @LOGSIZE2 INT
 208:  
 209: SELECT @PAGE_ID = [NEW SPLIT PAGE ID], @ROWS1 = [ROWS DELETED] FROM #TransactionLog WHERE [NEW SPLIT PAGE ID] IS NOT NULL
 210: SELECT @ROWS2 = COUNT(*) FROM #DataTable WHERE [Stage] = 'Non Key Column Update' AND [PAGE ID] = @PAGE_ID
 211: SELECT @ROWS3 = COUNT(*) FROM #DataTable WHERE [Stage] = 'Key Column Update' AND [PAGE ID] = @PAGE_ID    
 212: SELECT @ACTIONS1 = COUNT(*) FROM #TransactionLog WHERE [Action] = 'Non Key Column Update'
 213: SELECT @ACTIONS2 = COUNT(*) FROM #TransactionLog WHERE [Action] = 'Key Column Update'
 214: SELECT @FRAGMENTATION1 = AVG_FRAGMENTATION_IN_PERCENT FROM #IndexStats WHERE [Action] = 'Non Key Column Update'
 215: SELECT @FRAGMENTATION2 = AVG_FRAGMENTATION_IN_PERCENT FROM #IndexStats WHERE [Action] = 'Key Column Update'
 216: SELECT @LOGSIZE1 = SUM([Log Record Length]) FROM #TransactionLog WHERE [Action] = 'Non Key Column Update'
 217: SELECT @LOGSIZE2 = SUM([Log Record Length])  FROM #TransactionLog WHERE [Action] = 'Key Column Update'
 218:  
 219: SELECT 'Transaction log moved rows' as [Item], CAST(@ROWS1 AS VARCHAR(10)) as [Value] UNION ALL 
 220: SELECT 'Rows per  page ' + cast(@PAGE_ID as VARCHAR(10)) + ' before key column update', CAST(@ROWS2 AS VARCHAR(10)) UNION ALL
 221: SELECT 'Rows per  page ' + cast(@PAGE_ID as VARCHAR(10)) + ' after key column update', CAST(@ROWS3 AS VARCHAR(10)) UNION ALL
 222: SELECT 'Transaction log items for non key column update', CAST(@ACTIONS1 AS VARCHAR(10)) UNION ALL
 223: SELECT 'Transaction log items for key column update', CAST(@ACTIONS2 AS VARCHAR(10)) UNION ALL
 224: SELECT 'Transaction log size for non key column update', CAST(@LOGSIZE1 AS VARCHAR(10)) UNION ALL
 225: SELECT 'Transaction log size for key column update', CAST(@LOGSIZE2 AS VARCHAR(10)) UNION ALL
 226: SELECT 'Fragmentation after non key column update', CAST(@FRAGMENTATION1 AS VARCHAR(10)) UNION ALL
 227: SELECT 'Fragmentation after key column update', CAST(@FRAGMENTATION2 AS VARCHAR(10))
 228: GO
 229:  
 230: --Clear objects
 231: IF EXISTS(SELECT * FROM sys.tables WHERE NAME = 'IndexUpdate')
 232: DROP TABLE IndexUpdate
 233: GO
 234:  
 235: IF OBJECT_ID('tempdb..#TransactionLog') IS NOT NULL
 236: DROP TABLE #TransactionLog
 237: GO
 238:  
 239: IF OBJECT_ID('tempdb..#IndexStats') IS NOT NULL
 240: DROP TABLE #IndexStats
 241: GO
 242:  
 243: IF OBJECT_ID('tempdb..#DataTable') IS NOT NULL
 244: DROP TABLE #DataTable
 245: GO
Struktura tabeli, która jest modyfikowana jest dość specyficzna i na waszych bazach produkcyjnych aktualizacja kolmny będącej kluczem może mieć trochę inne skutki.

W powyższym przykładzie aktualizacja kolumny nie będącej kluczem nie powodowała fragmentacji indeksu, jednakże aktualizacja kolumny będącej kluczem spowodowała fragmentację na poziomie 33%.
Tak jak wspomniałem wcześniej wynika to po części ze specyfiki schematu tabeli i indeksu. Zmiana FILLFACTOR dla indeksu na 80% nie spowodowałaby żadnej fragmentacji,

Jednakże to nie fragmentacja indeksu jest największym problemem w tym przypadku. Ciekawe rzeczy dzieją się z logiem transakcyjnym.

Rezultatem aktualizacji kolumny nie będącej kluczem w indeksie jest operacja modify. Modyfikacja kolumny będącej kluczem w indeksie nie jest operacją zmiany. SQL Server w tle usuwa stare rekordy by potem dodać nowe. Dodatkowo w powyższym przykładzie FILLFACTOR ustawiony na 100 spowodował, że część rekordów została przeniesiona na inną stronę w pamięci. Wszystko są to dość kosztowne operacje.

Aktualizacja kolumny nie będącej kluczem w indeksie kosztowała 312 bajtów. Natomiast aktualizacja kolumny będącej kluczem wymagało ponad 30 razy wiecej miejsca, ponad 9 kB. Róznica znaczna.
Co ciekawe SQL Server wykonuje operacje modyfikacji (kolumna nie będąca kluczem) lub też usuniecia/wstawienia (kolumna będąca kluczem) niezależnie od tego czy zmiana nastąpiła czy nie.
   1: --Update key column (one record)
   2: UPDATE IndexUpdate SET COL1 = 2 WHERE COL1 = 1
   3: GO
   4:  
   5: --Update key column (one record)
   6: UPDATE IndexUpdate SET COL1 = 2 WHERE COL1 = 2
   7: GO
Powyższe dwa zapytania są równoważne jeżeli chodzi o to ile operacji SQL Server będzie wykonywał w kontakście logu transakcyjnego.

Warto wziąść to pod uwagę w przypadku aktualizacji większej ilości rekordów.
   1: UPDATE IndexUpdate SET COL1 = COL2
   2: GO
   3:  
   4: UPDATE IndexUpdate SET COL1 = COL2 WHERE COL1 <> COL2
   5: GO
Powyższe dwa zapytania, pomimo tego, że ich wynik będzie taki sam, będą miały zupełnie inny wpływ na wielkość loga transakcyjnego czy też procent fragmentacji indeksu.