Consultoria DBA SQL Server

Saiba como aumentar o espaço livre no seu banco de dados SQL Server.

Aumentar o espaço livre no seu banco de dados SQL Server sempre foi um grande desafio para vários DBA’s.

Recentemente eu publiquei um post que pode ajudar alguns DBA’s SQL Server a aumentar o espaço livre no seu banco de dados no arquivo .mdf, encontrando os índices duplicados no SQL Server isso já ajudava muito.

Mas isso não é o bastante, existem outras formas de você encontrar mais coisas desnecessárias no banco de dados SQL Server e aqui eu vou deixar abaixo para vocês algumas dicas.

1. Encontrando tabelas de backup e tabelas temporárias:

SELECT TOP 30 'DROP TABLE '+OBJECT_NAME(ID) AS [COMANDO],
				OBJECT_NAME(ID) AS NOMEDATABELA,
              INDID, 
              ROWCNT AS LINHAS, 
              RESERVED * 8 AS RESERVADO_KB, 
              DPAGES * 8 AS DADOS_KB, 
              (SUM(USED) * 8) - (DPAGES * 8) AS TAMANHO_INDICE_KB, 
              (SUM(RESERVED) * 8) - (SUM(USED) * 8) AS NAO_UTILIZADO_KB 
         FROM SYSINDEXES 
        WHERE INDID                             IN (0,1) 
          AND OBJECTPROPERTY(ID, 'ISUSERTABLE') = 1
          AND ROWCNT                            > 0
          AND OBJECT_NAME(ID)                   IN (SELECT T.NAME
                                                      FROM SYS.TABLES T
                                                     WHERE T.NAME LIKE '%BKP%'
					                      OR T.NAME LIKE '%ANTES%'
										  OR T.NAME LIKE '%OLD%'
										  OR T.NAME LIKE '%TEMP%'
										  OR T.NAME LIKE '%[0-9]%')
        GROUP BY ID,
                 ROWCNT, 
                 RESERVED, 
                 DPAGES, 
                 INDID 
        ORDER BY 4 DESC 
       GO

2. Encontrando índices que não são utilizados no SQL Server:

SELECT TOP 10 'DROP INDEX ['+ISNULL(I.NAME,'HEAP')+'] ON ['+OBJECT_NAME(I.OBJECT_ID)+']' AS [COMANDO],
 OBJECT_NAME(I.OBJECT_ID) AS NOME_DA_TABELA,
 ISNULL(I.NAME,'HEAP') AS [INDICE_NAO_UTILIZADO],
 S.USER_SEEKS,
 CAST((((8 * SUM(A.USED_PAGES)) / 1024.00) / 1024.00) AS NUMERIC(15,4)) AS 'INDEXSIZE(GB)'
 FROM SYS.INDEXES I
 LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS S
 ON S.OBJECT_ID = I.OBJECT_ID
 AND I.INDEX_ID = S.INDEX_ID
 AND S.DATABASE_ID = DB_ID()
 JOIN SYS.PARTITIONS AS P
 ON P.OBJECT_ID = I.OBJECT_ID
 AND P.INDEX_ID = I.INDEX_ID
 JOIN SYS.ALLOCATION_UNITS AS A
 ON A.CONTAINER_ID = P.PARTITION_ID
 WHERE OBJECTPROPERTY(I.OBJECT_ID, 'ISINDEXABLE') = 1
 AND OBJECTPROPERTY(I.OBJECT_ID, 'ISINDEXED') = 1
 AND I.IS_PRIMARY_KEY = 0
 AND (S.INDEX_ID IS NULL OR (S.USER_UPDATES > 0 AND S.USER_SEEKS < 50 ))
 GROUP BY I.OBJECT_ID,
 I.NAME,
 S.USER_SEEKS
 ORDER BY 5 DESC
 GO

3. Índices que possuem muitas colunas:

SELECT OBJECT_NAME(I.OBJECT_ID) AS TABELA,
              I.NAME AS INDICE,
              COUNT(*) AS QTDE_COLUMNS_IDX,
	          (SELECT COUNT(1)
	             FROM SYS.COLUMNS C
	       	 WHERE C.OBJECT_ID = I.OBJECT_ID) AS QTDE_COLUMNS_TBL
         FROM SYS.INDEXES I
              INNER JOIN SYS.INDEX_COLUMNS IC
                         INNER JOIN SYS.COLUMNS C
                                 ON C.OBJECT_ID = IC.OBJECT_ID
                                AND C.COLUMN_ID = IC.COLUMN_ID
                      ON I.INDEX_ID  = IC.INDEX_ID
                     AND I.OBJECT_ID = IC.OBJECT_ID
        GROUP BY I.NAME,
                 I.OBJECT_ID
        ORDER BY 3 DESC
       GO

Os scripts que estou compartilhando foram fornecidos por um colega de trabalho Marcus Vinicius que recentemente trabalhou em um projeto para aumentar o espaço livre no banco de dados SQL Server e deixou que eu compartilhasse com vocês.

Aproveitem!

Grande abraço!