1

I've been tasked with migrating a database and I want to figure out how much resource in terms of CPU and IO does that database need before migrating it. The target server to which the database is planned to be moved to is critical and I want to figure out if the target server can handle the load that the new db brings. Things which I am considering

  1. Use plan cache to find the IO and CPU, but I am not sure how to translate to real CPU and IO usage on the server.
  2. Use profiler/extended events to track activities on the DB. NOt sure how to translate it to real CPU / IO usage

What is the normal things to do in such situation?

Cheers

Community
  • 1
sercurity
  • 73
  • 3
  • 7

1 Answers1

0

"SQLServer:Databases" perfmon counter will tell you Log activity.

If you can classify your connections (sql login user_a goes to database app_a, or connections from server B goes to database app_b, etc) then you can use a resource governor classifier function to assign connections to different workload groups. After that, you can use the "SQLServer:Workload Group Stats" perfmon counter to see CPU usage.

Glenn Berry has DMV queries for CPU USage, IO Usage and Total Buffer Usage By Database. https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-january-2018/

StrayCatDBA
  • 2,173
  • 16
  • 23