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;
