This index operation requires 8192 KB of memory per DOP

• Abu Dina

  SSChampion

  Points: 14155

  More actions

  March 8, 2013 at 8:52 am

  #272089

  One of my sproc has the following bit of SQL:

  IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'idxMatchKey1' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

  EXEC('ALTER TABLE dbo.' + @SourceTable + '_keys_ add idxMatchKey1 VARBINARY(8000)')

  EXEC('UPDATE dbo.' + @SourceTable + '_keys_

  SET idxMatchKey1 = HASHBYTES(''SHA2_512'', mkPostOut+mkPostIn+mkName1)

  WHERE NULLIF(mkPostOut, '''') IS NOT NULL

  AND NULLIF(mkPostIn, '''') IS NOT NULL

  AND NULLIF(mkName1, '''') IS NOT NULL')

  IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_MatchKey1' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

  EXEC ('CREATE INDEX idx_MatchKey1 ON dbo.' + @SourceTable + '_keys_(idxMatchKey1)')

  This has been running fine. The only thing that has changed is that I've created additional tempdb files, moved temdb to a different drive and restarted SQL Server.

  This is the full error message:

  Warning! The maximum key length is 900 bytes. The index 'idx_MatchKey1' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

  The statement has been terminated.

  Msg 8606, Level 17, State 1, Line 1

  This index operation requires 8192 KB of memory per DOP. The total requirement of 131336 KB for DOP of 16 is greater than the sp_configure value of 8192 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.

  Please advise?

  ---------------------------------------------------------

  It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
  David Edwards - Media lens[/url]

  Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
  Howard Zinn