September 27, 2012 at 7:29 am
I have a SQL 2008 cluster that hosts about 150 databases, corresponding to about 40 applications. How can I get a view of what percentage of server resources (cpu. memory, disk i/o) that each database is using on the server? I would like to be able to chart highest to lowest use. Could be daily average but an hourly average would be better to identify peak times. Possible? Third party tools?
Thanks in advance!
September 27, 2012 at 8:35 am
You sort of can't. Not really. There's no way to break down memory, cpu or I/O by database in that manner.
However, what you can do is capture the query metrics, which include I/O and CPU use. You can then aggregate those values and generate a report that will do most of what you're looking for. I'd suggest using extended events as the most lightweight way to capture query metrics. Just be prepared for capturing large amounts of data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2012 at 11:53 pm
Thanks, Grant. After thinking about it more last night, I realize that it would have to be something like that using profiler to intercept all the calls to the database. I might have a look in that direction but not sure if I have the time to commit to learning/doing it that way.
Maybe a question with a different approach: for those with large SQL servers that consolidate many application databases, how do you go about planning for new databases on your server? It's never quite clear how much impact any application database might have on a server until it's there. We can have a general idea given the number of users, size, and type of db operations performed, but even then you don't know what the actual impact is until it's implemented. Especially for applications such as citrix, vmware, mcafee, which are not used directly by users but are the back end to some infrastructure application.
Ideas?
September 28, 2012 at 3:55 am
When I've done it in the past, we were usually doing server consolidation, so we looked at the load on the server. We didn't break it down much by database unless we knew we had a bad actor or two. Then, it was using, at the time, server-side traces (not profiler) that did it. Now, I'd use extended events because they have a lighter footprint on the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply