Workload analysis before migration

Question

Does anybody have a recommendation on which performance counters to collect and/or templates to use on the profiler before migrating SQL databases? The purpose is to evaluate the workload and make decision on which cluster to put the databases under. Thank you for all your help!

Answer 1

If it's SQL Server 2005 or later, I'd initially look at file stats, which shows you how much disc IO each database uses, together with memory stats:

Memory Breakdown:

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];
GO

Microsoft provide an Assessment and Planning Toolkit which you can point to your SQL Servers, and it will poll every 5 minutes to gather useful information such as IOPS.

For a consolidation/migration project, Bob Duffy's (MCM) SQL Bits presentation video is full of tips.

Answer 2

I'd suggest following a similar pattern of analysis to this answer. The question in that case was how to fit more databases on to an instance, similar to what you are trying to achieve.