Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Cool, this video is awesome. The DB is SQL 2000. I am downloading MAP ToolKit... –  db7 Sep 15 '11 at 3:13

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.