Niedawno zacząłem pracować z SQL Server 2008 jako stażysta DBA. Muszę obliczyć rozmiar bazy danych, ale także oszacować jej wzrost w ostatnich miesiącach i przewidywany wzrost na następne 12 miesięcy.
Mogę użyć instrukcji sp_spaceused do obliczenia rzeczywistego rozmiaru, ale jak obliczyć wszystko inne?
Odpowiedzi:
Pozostałe odpowiedzi są technicznie poprawne, ale nie są prawdziwe. Oto, o co musisz zapytać firmę:
W jakim horyzoncie czasowym zamierzam? W twoim przypadku szukasz numeru na 12 miesięcy.
Czy w tym czasie będziemy archiwizować dane, czy przechowywać wszystkie dane? W niektórych firmach możesz (lub musisz) przechowywać tylko określoną ilość danych, na przykład z ostatnich 12 miesięcy. W takim przypadku musisz ustalić wzrost danych (na który odpowiedzą kolejne pytania), ale następnie powrócić do ostatnich 12 miesięcy. Nie można tak po prostu powiedzieć: „W tej chwili ilość danych wynosi 100 GB”, ponieważ jeśli ilość danych rośnie, to także ostatnie 12 miesięcy. Czas może być stały, ale dane nie.
Czy będziemy dodawać kolejnych użytkowników? Na przykład firma może rozwijać się na nowych terytoriach lub pozyskiwać nowych klientów. Jeśli podwoją bazę użytkowników, w niektórych przypadkach dane również zaczną się podwajać.
Czy oczekujemy wzrostu wolumenu biznesu? Jeśli na przykład śledzisz sprzedaż na stronie internetowej i zaczynasz wyświetlać reklamy Super Bowl lub Pucharu Świata, ilość danych może trafić w krzywą wzrostu kija hokejowego.
Czy dodamy dodatkowe funkcje w aplikacji? Jeśli aplikacja nagle zacznie przechowywać obrazy, wpłynie to znacząco na rozmiar bazy danych.
Czy będziemy dodawać dane z innego źródła, czy rejestrować nowe dane? Jeśli zaczniesz rejestrować kliknięcia witryny lub w hurtowni danych, dodając dodatkowe źródła, ilość danych wzrośnie.
Czy programiści lub DBA będą indeksami dostrajania wydajności? Jeśli chcesz, aby ludzie tworzyli indeksy, możesz łatwo podwoić (lub potroić lub poczwórnie) rozmiar swoich danych w zależności od ich nadgorliwości.
I tak długo, jak zadajesz te pytania, powinieneś również zapytać, czy oczekuje się, że wydajność pozostanie taka sama, obniży się lub poprawi. Lubię mapować prognozowany wzrost na wykresie liniowym, a następnie porównywać inwestycje w sprzęt i szkolenia personelu w tym samym czasie.
Nie można dokładnie przewidzieć przyszłego wzrostu bez historii poprzedniego wzrostu. Możesz jednak oszukiwać i uzyskiwać ostry trend, korzystając z historii tworzenia kopii zapasowych, zgodnie z opisem Erin Stellato w Trending Database Growth From Backup .
Wydrukuj dane wyjściowe następującego zapytania w programie Excel:
SELECT
[Database] = [database_name]
, [Month] = DATEPART(month,[backup_start_date])
, [Backup Size MB] = AVG([backup_size]/1024/1024)
, [Compressed Backup Size MB] = AVG([compressed_backup_size]/1024/1024)
, [Compression Ratio] = AVG([backup_size]/[compressed_backup_size])
FROM
msdb.dbo.backupset
WHERE
[database_name] = N'YourDatabaseName'
AND [type] = 'D'
GROUP BY
[database_name]
, DATEPART(mm, [backup_start_date]);Istnieje wiele sposobów planowania pojemności bazy danych.
Historia kopii zapasowej msdb, jeśli zostanie regularnie przycięta, nie będziesz mieć dużo danych do analizy
Jak zauważył Mark, można to zrobić przy użyciu metody opisanej przez Erin - trend wzrostu bazy danych po utworzeniu kopii zapasowej.
Możesz nawet użyć PIVOT, aby dowiedzieć się o rozwoju bazy danych w okresie 12 miesięcy z historii kopii zapasowych, jak poniżej:
DECLARE @startDate DATETIME;
SET @startDate = GetDate();
SELECT PVT.DatabaseName
,PVT.[0]
,PVT.[-1]
,PVT.[-2]
,PVT.[-3]
,PVT.[-4]
,PVT.[-5]
,PVT.[-6]
,PVT.[-7]
,PVT.[-8]
,PVT.[-9]
,PVT.[-10]
,PVT.[-11]
,PVT.[-12]
FROM (
SELECT BS.database_name AS DatabaseName
,DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo
,CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
FROM msdb.dbo.backupset AS BS
INNER JOIN msdb.dbo.backupfile AS BF ON BS.backup_set_id = BF.backup_set_id
WHERE BS.database_name NOT IN (
'master'
,'msdb'
,'model'
,'tempdb'
)
AND BS.database_name IN (
SELECT db_name(database_id)
FROM master.SYS.DATABASES
WHERE state_desc = 'ONLINE'
)
AND BF.[file_type] = 'D'
AND BS.backup_start_date BETWEEN DATEADD(yy, - 1, @startDate)
AND @startDate
GROUP BY BS.database_name
,DATEDIFF(mm, @startDate, BS.backup_start_date)
) AS BCKSTAT
PIVOT(SUM(BCKSTAT.AvgSizeMB) FOR BCKSTAT.MonthsAgo IN (
[0]
,[-1]
,[-2]
,[-3]
,[-4]
,[-5]
,[-6]
,[-7]
,[-8]
,[-9]
,[-10]
,[-11]
,[-12]
)) AS PVT
ORDER BY PVT.DatabaseName;Jest jeszcze inny sposób, który okaże się naprawdę przydatny, jak doskonale opisał Chad Miller w SSC - Planowanie pojemności bazy danych . Skupia się również na tym, days remainingco jest bardzo przydatne.
Istnieje inna metoda obejmująca obliczenia matematyczne, która dałaby dokładne wyniki. Jak już wskazano, kopie zapasowe najlepiej odnosić się do wzrostu danych, ponieważ powiedziałeś, że musisz obliczyć i przewidzieć rozmiar bazy danych poniżej linków Microsoft
Mam nadzieję, że ten kod pomoże:
Działa w oparciu o historię wielkości kopii zapasowych (w MB), podaje miesiąc po miesiącu min MB, śr. MB, maks. MB i różnicę w stosunku do innych miesięcy w MB.
Wyświetla listę wszystkich baz danych z kopiami zapasowymi, z wyjątkiem systemowych baz danych.
-- T-SQL script - Analyses database growth using backup information (Last (12) months in that case
-- Looks only to FULL backups information
-- Parameters: Date GetDate() and nr of months to analyse
SET NOCOUNT ON
DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate(); -- Data atual
SET @months = 12; -- Nr. de meses a analisar
;WITH HIST AS
(SELECT BS.database_name AS DatabaseName
,YEAR(BS.backup_start_date) * 100
+ MONTH(BS.backup_start_date) AS YearMonth
,CONVERT(numeric(10, 1), MIN(BS.backup_size / 1048576.0)) AS MinSizeMB
,CONVERT(numeric(10, 1), MAX(BS.backup_size / 1048576.0)) AS MaxSizeMB
,CONVERT(numeric(10, 1), AVG(BS.backup_size / 1048576.0)) AS AvgSizeMB
FROM msdb.dbo.backupset as BS
WHERE NOT BS.database_name IN
('master', 'msdb', 'model', 'tempdb')
AND BS.type = 'D'
AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
GROUP BY BS.database_name
,YEAR(BS.backup_start_date)
,MONTH(BS.backup_start_date))
SELECT @@SERVERNAME
,MAIN.DatabaseName
,MAIN.YearMonth
,MAIN.MinSizeMB
,MAIN.MaxSizeMB
,MAIN.AvgSizeMB
,MAIN.AvgSizeMB
- (SELECT TOP 1 SUB.AvgSizeMB
FROM HIST AS SUB
WHERE SUB.DatabaseName = MAIN.DatabaseName
AND SUB.YearMonth < MAIN.YearMonth
ORDER BY SUB.YearMonth DESC) AS GrowthMB
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
,MAIN.YearMonthMyślę, że post Brenta Ozara jest na miejscu. Byłem w bardzo rozdętym projekcie DB i miałem dokładnie ten sam problem, co tutaj, i to nie jest takie proste.
Ponieważ lepiej jest przynajmniej coś zrobić - nawet jeśli nie jest to tak dokładne - skonfigurowałbym wymagane tabele i zadanie (lub dowolną inną metodę, cokolwiek, by tylko zapytać o rozmiary i przechowywać je gdzieś niezawodnie) do śledzenia wiersze i przestrzeń używane dla DB i wszystkich jej tabel co tydzień i używaj tego do projekcji najbardziej prawdopodobnej krzywej wzrostu. Korzystanie z historii kopii zapasowych jest również świetnym pomysłem. Ale niezależnie od metody potrzebujesz czasu na uzyskanie nawet zdalnie wiarygodnych danych.
Poza tym naprawdę zależy to od twojej sytuacji. Być może% wykorzystania twojego DB jest teraz tylko ułamkiem tego, co będzie w ciągu najbliższych 6 miesięcy, na przykład, gdy twoje oprogramowanie zyska na popularności, co uniemożliwi przewidywanie gwałtownego wzrostu, który nadchodzi. Być może istnieją masowe transfery danych rocznie, które podwoją rozmiar DB, ale dowiesz się o tej masie dopiero po tym fakcie.
Ale jak powiedziano, jeśli wzrost jest problemem, to absolutnie powinieneś zrobić coś, aby to śledzić. Ostatnią rzeczą, którą chcesz, to znaleźć się za 6 miesięcy z DB dwa razy większą niż jego pierwotna projekcja na całe życie, musisz wyjaśnić klientowi, jak to się stało i dlaczego, nie wspominając nawet o tym, że musisz zacząć zgadywać, ile jeszcze wzrośnie w ciągu najbliższych 6 miesięcy. Istnieją również pewne bardzo oczywiste korzyści z wiedzy o tym, gdzie poszły nowe dane i jaki jest względny wzrost każdej tabeli w danym czasie, ponieważ może dostarczyć cennych informacji na temat różnych trendów, potencjalnych problemów z oprogramowaniem itp., Wszystko to przy stosunkowo niewielkim wysiłku .