次の方法で共有

O caso da tabela VAZIA de 70 Gb

  • [アーティクル]

Novamente estou aqui para contar um caso que aconteceu em um cliente.

Sintomas: Uma consulta a uma tabela de fila está lenta, chegando a dar timeout. Detalhe é a quantidade de linhas normalmente varia entre 0 e 30, com muitos INSERTs e DELETEs. Mesmo com o processamento da fila parado um select simples continuava lento.

  • Select COUNT(*) =0.
  • Sp_spaceused
    Rows = 0 / Data = 76.101.144KB ???    Hein ¯\_(ツ)_/¯  ...  Como ???

 


 
 
  
Deve ter algo desatualizado…

  • EXEC sp_spaceused @updateusage = N'TRUE';
  • “When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.”. Ref https://msdn.microsoft.com/en-us/library/ms188776.aspx

 

  • NADA :(
  • Após alguma pesquisa cheguei em alguns problemas relacionados a heaps, ainda achei que não, pois jurava ter visto uma chave primária de relance. Mas ao olhar com mais detalhes verifico que é uma PK (Non-Clustered)


 

 

Agora vamos a simulação e ver se o problema é reproduzível.

Iniciando por um teste com índice cluster.

 USE SANDBOX GO SET NOCOUNT ON  GO IF OBJECT_ID('TABLE_CLUSTER') IS NOT NULL DROP TABLE TABLE_CLUSTER GO  CREATE TABLE TABLE_CLUSTER ( ID INT IDENTITY(1,1) PRIMARY KEY ,COL VARCHAR(8000) ) GO ---------------------------------------------------------------------------------------------- --INSERT 1 ROW ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_CLUSTER VALUES (REPLICATE('A',8000)) GO SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_CLUSTER') ) GO EXEC sp_spaceused 'TABLE_CLUSTER' GO ---------------------------------------------------------------------------------------------- --INSERT +1000 ROWs ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_CLUSTER VALUES (REPLICATE('B',8000)) GO 5000  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_CLUSTER') ) GO EXEC sp_spaceused 'TABLE_CLUSTER' GO  ---------------------------------------------------------------------------------------------- --DELETE ROWS 1 by 1 ---------------------------------------------------------------------------------------------- DELETE TOP (1) FROM TABLE_CLUSTER GO 5001  WAITFOR DELAY '00:00:05' -- WAITING FOR ASYNC CLEAN UP (*GHOST CLEAN UP) GO  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_CLUSTER') ) GO EXEC sp_spaceused 'TABLE_CLUSTER' GO

 


 
  

OK. Tudo normal.

 

 USE SANDBOX GO SET NOCOUNT ON  GO IF OBJECT_ID('TABLE_HEAP') IS NOT NULL DROP TABLE TABLE_HEAP GO  CREATE TABLE TABLE_HEAP ( ID INT IDENTITY(1,1)-- PRIMARY KEY ,COL VARCHAR(8000) ) GO ---------------------------------------------------------------------------------------------- --INSERT 1 ROW ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_HEAP VALUES (REPLICATE('A',8000)) GO SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') ) GO EXEC sp_spaceused 'TABLE_HEAP' GO ---------------------------------------------------------------------------------------------- --INSERT +1000 ROW ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_HEAP VALUES (REPLICATE('B',8000)) GO 5000  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') ) GO EXEC sp_spaceused 'TABLE_HEAP' GO  ---------------------------------------------------------------------------------------------- --DELETE ROWS 1 by 1 ---------------------------------------------------------------------------------------------- DELETE TOP (1) FROM TABLE_HEAP GO 5001  WAITFOR DELAY '00:00:05' -- (*THERE IS NO GHOST CLEAN UP FOR HEAP) LET IT HERE JUST TO BE LIKE CLUSTER SCRIPT GO  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') ) GO EXEC sp_spaceused 'TABLE_HEAP' GO

 

 


 
 
 
 
Como assim 0 linhas e 40.008Kb.

 

Após várias pesquisas acabei chegando na resposta.

DELETE (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms189835.aspx

 

Locking Behavior

By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. For more information, see Table Hints (Transact-SQL).

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

To delete rows in a heap and deallocate pages, use one of the following methods.

•Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).•Use TRUNCATE TABLE if all rows are to be deleted from the table.•Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.

 

 

Segue também um KB

Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server

https://support.microsoft.com/en-us/kb/913399

 

No caso a solução a mais longo prazo é CRIAR UM INDICE CLUSTER e deixar ele lá. Mas uma solução que não está na documentação é fazer um REBUILD. (Recomendei a alteração na documentação e agora esta lá)

 ------------------------------------------------------------------------------------------------SOLUTION---------------------------------------------------------------------------------------------- ALTER TABLE TABLE_HEAP REBUILD  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') )  GO  EXEC sp_spaceused 'TABLE_HEAP'  GO

 

 

 Espero que tenham gostado e que seja útil para mais alguém, até a próxima!

 

*[UPDATE] Só para complementar, este problema aconteceu no SQL 2008 R2, provavelmente nas anteriores também, mas o mesmo comportamento se mantem até SQL 2014.

*[UPDATE 2] Books Online alterado com REBUILD (https://msdn.microsoft.com/en-us/library/ms189835.aspx)

 

TESTE_SCRIPT.sql

Comments

  • Anonymous
    June 10, 2015
    Excelente post. Parabéns.

  • Anonymous
    June 10, 2015
    Mto bom Sergio!

  • Anonymous
    June 10, 2015
    Bem legal a forma como o bug foi apresentado neste post. Parabéns!

  • Anonymous
    June 10, 2015
    Ótimo post Sergio, parabéns. Executei os mesmos scripts no SQL16 CTP2 e no Azure SQL Database e tiveram os mesmos comportamentos. Abs

  • Anonymous
    June 11, 2015
    The comment has been removed

  • Anonymous
    June 11, 2015
    The comment has been removed

  • Anonymous
    June 27, 2015
    Excelente!

  • Anonymous
    August 06, 2015
    Bom artigo!!!