SQL server database capacity planning TSQL Script

To perform capacity planning for a SQL Server database using T-SQL, you can utilize system views and functions to gather information about your database and workload. Here’s an example of T-SQL queries you can use:

1. Determine Current Database Size:

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size * 8 / 1024) AS DatabaseSizeMB
FROM sys.master_files
WHERE type = 0 AND DB_NAME(database_id) = DB_NAME()
GROUP BY database_id;

2. Estimate Growth Rate:

SELECT 
    DB_NAME() AS DatabaseName,
    (SUM(size * 8 / 1024) - MAX(size * 8 / 1024)) / COUNT(*) AS GrowthRateMBPerDay
FROM sys.master_files
WHERE type = 0 AND DB_NAME(database_id) = DB_NAME()
GROUP BY database_id;

3. Calculate CPU Usage:

SELECT 
    DB_NAME() AS DatabaseName,
    MAX(total_worker_time) / DATEDIFF(second, GETDATE(), GETDATE() + 1) AS CPUUsagePercent
FROM sys.dm_exec_query_stats
WHERE DB_NAME(dbid) = DB_NAME()
GROUP BY dbid;

4. Estimate Memory Requirements:

SELECT 
    DB_NAME() AS DatabaseName,
    COUNT(*) * 8 AS BufferPoolSizeMB
FROM sys.dm_os_buffer_descriptors
WHERE DB_NAME(database_id) = DB_NAME();

5. Assess Storage Requirements:

SELECT 
    DB_NAME() AS DatabaseName,
    SUM(size * 8 / 1024) AS TotalDataSizeMB,
    SUM(CASE WHEN type = 0 THEN size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN type = 1 THEN size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = DB_NAME()
GROUP BY database_id;

6. Analyze Query Performance:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS AvgCPUTime,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_logical_writes / qs.execution_count AS AvgLogicalWrites,
    t.text AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE DB_NAME(t.dbid) = DB_NAME()
ORDER BY AvgCPUTime DESC;

By running these queries and analyzing the results, you can gain insights into the current database size, growth rate, CPU usage, memory requirements, storage needs, and query performance. This information can help you in estimating the capacity and making informed decisions for capacity planning in your SQL Server database.