I finally get to cross an item off my back burner project list. Last February, I was at a client assessing their entire SQL Server database footprint for migration to the cloud. Not having done this kind of work before, I quickly cobbled together scripts that gave me a basic overview of each database. I then combined the results into a spreadsheet.
Wouldn’t it have been nice to get this information in a single script? You could use this script to not only get a better understanding of resource usage in your SQL Server instance, but also use it to determine needed resources for migrating to either other hardware or the cloud.
I have already produced a lengthy health check script (here) that gives quite a bit of information. It did not, however, take measurements of CPU usage and IOPS values over a period of time. My new SQL Server assessment script not only gathers a smaller but more critical set of information than my health check script, but also takes hourly snapshots of CPU usage and IOPS values including individual read and write values over the course of one day. Execute this script during a normal working day or during a period of end-of-month processing, if it exists.
Here is the script in its latest form. To adjust the CPU and IOPS snapshots taken and the interval between snapshots, edit these two lines:
while (@cntr) < 24 – This is the number of snapshots to take before results are produced.
waitfor delay ’01:00:00′ – This is the time delay between snapshots. It is currently set to one hour.
--sql_server_assessment.sql --Lists the resources and their usage for a single SQL Server instance --Intended to act as a tool for migrations to other hardware or the cloud. use master GO ----------------------------------------------------------Node name SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]; ----------------------------------------------------------Product Version, level, and Edition SELECT CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' ELSE 'unknown' END AS MajorVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS ProductVersion ----------------------------------------------------------License information print N'License information'; SELECT SERVERPROPERTY('LicenseType') AS LICENSE_TYPE, ISNULL(SERVERPROPERTY('NumLicenses'),0) AS NUM_LICENCES; ----------------------------------------------------------Database listing print N'Database list with Status and Recovery Model'; ----------------------------------------------------------Logical CPUs select cpu_count as logical_cpu_count from sys.dm_os_sys_info; ----------------------------------------------------------Physical Memory --For SQL Server version 2008, 2008R2 select physical_memory_in_bytes/1024/1024/1024 as GB from sys.dm_os_sys_info; --For SQL Server version > 2008 select physical_memory_kb/1024/1024 as GB from sys.dm_os_sys_info; ----------------------------------------------------------Memory usage per database print N'Memory Usage per User Database'; SELECT substring(DB_NAME(database_id),1,40) AS [Database Name] ,COUNT(*) * 8/1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WHERE database_id > 4 AND database_id <> 32767 AND db_name(database_id) <> 'SSISDB' GROUP BY DB_NAME(database_id) ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE); ----------------------------------------------------------Database file size and growth settings print N'Size and Growth'; select substring(b.name,1,40) AS DB_Name, substring(a.name,1,40) AS Logical_name, substring(a.filename,1,100) AS File_Name, cast((a.size * 8.00) / 1024 as numeric(12,2)) as DB_Size_in_MB, case when a.growth > 100 then 'In MB' else 'In Percentage' end File_Growth, cast(case when a.growth > 100 then (a.growth * 8.00) / 1024 else (((a.size * a.growth) / 100) * 8.00) / 1024 end as numeric(12,2)) File_Growth_Size_in_MB, case when ( maxsize = -1 or maxsize=268435456 ) then 'AutoGrowth Not Restricted' else 'AutoGrowth Restricted' end AutoGrowth_Status from sysaltfiles a join sysdatabases b on a.dbid = b.dbid where DATABASEPROPERTYEX(b.name, 'status') = 'ONLINE' order by b.name; ----------------------------------------------------------Database data and log file total size SELECT SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) + SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS Total_GB FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id WHERE DB.source_database_id is null; ----------------------------------------------------------License feature usage IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL DROP TABLE ##enterprise_features CREATE TABLE ##enterprise_features ( dbname SYSNAME, feature_name VARCHAR(100), feature_id INT ) EXEC sp_msforeachdb N' USE [?] IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0 BEGIN INSERT INTO ##enterprise_features SELECT dbname=DB_NAME(),feature_name,feature_id FROM sys.dm_db_persisted_sku_features END ' SELECT * FROM ##enterprise_features; ----------------------------------------------------------CPU and IOPS for 24 hours (avg. work day or end of month) create table #CPU (snap_time varchar(30), row_num int, db_name varchar(50), cpu_ms int, cpu_pct int) create table #IOPS (snap_time varchar(30), db_id int, db_name varchar(50), reads int, writes int, total_io int) declare @cntr int set @cntr = 0 while (@cntr) < 24 --set number of 1-hour loops to perform here. begin; WITH DB_CPU_Stats AS ( SELECT convert(varchar, getdate(), 120) AS ts, DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms] FROM sys.dm_exec_query_stats AS qs CROSS APPLY ( SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID ) insert into #CPU (row_num,snap_time,db_name,cpu_ms,cpu_pct) SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num], ts, DatabaseName, [CPU_Time_Ms], CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent] FROM DB_CPU_Stats --WHERE DatabaseID > 4 -- system databases --AND DatabaseID <> 32767 -- ResourceDB ORDER BY row_num OPTION (RECOMPILE); WITH D AS( SELECT convert(varchar, getdate(), 120) AS ts, database_id,DB_NAME(database_id) AS Name, SUM(num_of_reads) AS IO_Reads, SUM(num_of_writes) AS IO_Writes, SUM(num_of_reads + num_of_writes) AS Total_IO FROM sys.dm_io_virtual_file_stats(NULL, NULL) GROUP BY database_id,DB_NAME(database_id)) insert into #IOPS (snap_time,db_id,db_name,reads,writes,total_io) SELECT D.ts, D.database_id, D.Name, SUM(D.IO_Reads) - SUM(L.num_of_reads) AS Data_Reads, SUM(D.IO_Writes) - SUM(L.num_of_writes) AS Data_Writes, SUM(D.IO_Reads+D.IO_Writes) - SUM(L.num_of_reads+L.num_of_writes) AS Total_IO FROM D JOIN sys.dm_io_virtual_file_stats(NULL, 2) L ON D.database_id = L.database_id GROUP BY D.ts,D.database_id, D.Name ORDER BY D.database_id; waitfor delay '01:00:00' --change this to determine the interval between snapshots set @cntr = @cntr + 1 end select db_name, avg(cpu_pct) as AVG_CPU from #CPU where db_name is not null group by db_name order by db_name; select DB_NAME, snap_time, MAX(cpu_pct) as MAX_CPU from #CPU group by db_name, snap_time having MAX(cpu_pct) > 0 order by MAX(cpu_pct) desc select db_name, min(total_io) AS MIN_IO, max(total_io) AS MAX_IO, avg(total_io) AS AVG_IO from #IOPS group by db_name order by db_name; select snap_time, db_name, reads, writes, total_io from #IOPS where total_io = (select max(total_io) from #IOPS); drop table #CPU; drop table #IOPS;