I have many small in size SQL servers that we are considering migrating onto one server, for cost savings.
Before migrating these onto one box, I thought it best to do some performance metrics over time to make sure I can combine all of these smaller db's onto one server without performance issues.
What would be the best way for me to insure that I can migrate sucessfully?
I would use stored procedure “sp_monitor” to verify on each instance where those database reside if the instances are read intensive (use total_read) or write intensive (use total_write). Hopefully you will have a mix of read-intensive with write-intensive databases.
Use system monitor to measure the following counters on all those instances:
SQLServer:Locks -> Average Wait Time (ms)
SQLServer:Databases -> Transactions/sec
Network Interface -> Bytes Total/sec
SQLServer:General Statistics -> User Connections
SQLServer:Access Methods -> Page Splits/sec
The following query will let you know the amount of RAM each database uses:
SELECT CASE database_id
WHEN 32767 THEN 'ResourceDb'
END AS 'Database Name',
COUNT(*) AS 'Cached Pages Count',
COUNT(*) * 8. / 1024 AS MB
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY MB DESC
Hope this helps.
2010年10月20日 13:04thanks alberto!
Performance is actually one of the easier problems to solve when consolidating databases. With the computing capacity you can get out of a commodity server today, a 2 socket pizza box can give you as much computing power as an enterprise class server as recent as 4 years ago.
The one area in performance that hasn't gotten easier to deal with is storage so pay careful attention to the IOPS and MB/s requirements of the databases. HBAs are expensive little critters if you're using a SAN and if you're using only local storage, you run a real risk of running out of storage capacity (IOPS and MB/s, not space) long before you hit your server's ceiling on CPU or memory.
There are papers on consolidation on www.sqlcat.com and msdn/technet. Harder problems to solve are security requirements, namespace clashes, availability SLAs, event maintenance jobs. If you have access to TechEd or PASS summit conference materials, there are presentations there too that will be useful.
No great genius has ever existed without some touch of madness. - Aristotle