asked
"How Much of the Server" is a specific database using?
We have a single (default) instance of SQL 2012 running several medium volume OLTP databases and using availability groups for high availability. This is a decently sized virtual machine, and current performance of all databases is just fine. However, due to some security concerns and network segmentation requirements, we need to move one of these databases to run on another physical (well, virtual) server instance. I can handle that part moving the database) just fine, but I need to try and figure out in advance how to size the new virtual machine so it will properly handle the database I am moving onto it. I need to decide how much memory to allocate, mainly, and also get an idea of how many CPU cores to provision in Hyper-V so performance of the new database instance doesn't suffer. Can anyone suggest a simple approach I can use on the current server to determine approximately "how much of the current server" (memory, CPU etc.) is in use by this specific database? I mean, is this database using 50% of the server's resources during normal operations, or only 10%? Any suggestions (DBCC commands, example scripts, etc.) would be much appreciated.
There are specific database perfmon counters that you can look at, such as Transactions/Sec, which could give an indication of the amount of activity comapred to the total value. select * from sys.dm_os_performance_counters where [object_name] = 'SQLServer:Databases' and counter_name = 'Transactions/sec' Also it is possible to get how much buffer pool is being used by each database - [see this answer from Paul Randal][1] : 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 CPU is not quite clear cut. [This answer from Brent Ozar][2] might help get a rough idea though: SELECT total_worker_time/execution_count AS AvgCPU , total_worker_time AS TotalCPU , total_elapsed_time/execution_count AS AvgDuration , total_elapsed_time AS TotalDuration , (total_logical_reads+total_physical_reads)/execution_count AS AvgReads , (total_logical_reads+total_physical_reads) AS TotalReads , execution_count , SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt , query_plan FROM sys.dm_exec_query_stats AS qs cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp ORDER BY 1 DESC [1]:
http://serverfault.com/questions/24003/find-out-which-database-in-sql-server-2005-uses-how-much-ram [2]:
http://stackoverflow.com/questions/28952/cpu-utilization-by-database
These are snapshot figures, rather than a historical view. I would recommend capturing this information on a schedule, and looking at the patterns behind the figures as well.
Oh yes, these figures also get reset when the server gets reset, so there may be a settling-down period.
Beautiful, your answers (and the ones you linked to) were able to get me the answers I needed!
14 People are following this question.
