czwartek, 9 lutego 2012

Rozmiar tabel i indeksów w bazie danych

Ostatnio zajmowałem się projektem strony, która hostowana jest na serwerze, gdzie nie było pełnej kontroli nad serwerem baz danych. Jednym z wymagań była możliwość monitorowania wielkości bazy danych. SQL Server umożliwia wykonanie tego zadania poprzez odpytywanie widoków systemowych. Wszystkie dane jakie były potrzebe do wykonania tego zadania znajdują się w 4 widokach.
Poniżej znajduję się skrypt i trochę przykładowych danych do testowania.
   1: create database MyTest   
   2: go 

Baza danych lotnisk

Tutaj możecie pobrać bazę danych lotnisk. Skrypt SQL został stworzony na podstawie bazy, którą można pobrac z tej lokalizacji. Lista nie jest aktualna i nie ma np. na niej lotniska w Warszawa-Modlin.

Baza danych państw

Tutaj możecie pobrać bazę danych państw. Została ona stworzona na podstawie tego arykułu.

Po odpaleniu wcześniej wspomnianych skryptów w bazie danych pojawią się dwie nowe tabele.

Sprawzdanie rozmiaru tabel i indeksów

I teraz odpalając poniższy skrypt uzyskamy rozmiar obiektów w bazie.
   1: DECLARE @TABLENAME VARCHAR(126)   
   2: SET @TABLENAME = NULL   
   3:  
   4: SELECT
   5:     TABLE_NAME,
   6:     INDEX_NAME,
   7:     TYPE_DESC,
   8:     INDEX_ID,
   9:     PARTITION_NUMBER,
  10:     RESERVED_PAGES * 8 AS RESERVED_SPACE,
  11:     USED_PAGES * 8 AS USED_SPACE,
  12:     DATA_PAGES * 8 AS DATA,
  13:     ROWS,
  14:     SUM(RESERVED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) AS TOTAL_SPACE_RESERVED,
  15:     SUM(RESERVED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) - SUM(USED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) AS TOTAL_SPACE_UNUSED
  16: FROM (
  17:     SELECT
  18:         T.NAME AS TABLE_NAME,
  19:         I.NAME AS INDEX_NAME,
  20:         I.TYPE_DESC,
  21:         I.INDEX_ID,
  22:         P.PARTITION_NUMBER,
  23:         SUM(PS.RESERVED_PAGE_COUNT) AS RESERVED_PAGES,
  24:         SUM(PS.USED_PAGE_COUNT) AS USED_PAGES,
  25:         SUM(CASE
  26:             WHEN (PS.INDEX_ID < 2) THEN (IN_ROW_DATA_PAGE_COUNT + LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT)
  27:             ELSE LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT
  28:         END) AS DATA_PAGES,
  29:         SUM (CASE
  30:                 WHEN (PS.INDEX_ID < 2) THEN PS.ROW_COUNT
  31:                 ELSE 0
  32:             END) AS ROWS
  33:         FROM sys.indexes AS I   
  34:         INNER JOIN sys.tables T
  35:         ON T.OBJECT_ID = I.OBJECT_ID
  36:         INNER join sys.partitions AS P
  37:             ON I.OBJECT_ID = P.OBJECT_ID
  38:             AND I.INDEX_ID = P.INDEX_ID
  39:         INNER JOIN sys.dm_db_partition_stats AS PS
  40:             ON P.PARTITION_ID = PS.PARTITION_ID
  41:         GROUP BY T.NAME, I.NAME, I.TYPE_DESC, I.INDEX_ID, P.PARTITION_NUMBER
  42: ) AS stats
  43: WHERE ISNULL(@TABLENAME, TABLE_NAME) = TABLE_NAME

Modyfikując zmienną @TABLENAME uzyskane wyniki będą dotyczyły tylko jednej tabeli.

Prześlij komentarz