asked
how to identify cpu load and physical disk io and memory usage of each database
how to identify cpu load and physical disk io and memory usage of each database in sqlserver 2008
Identifying this for individual databases is going to be difficult. The database itself doesn't use CPU, etc. It's the queries that are calling that database that use the CPU. So, the best thing you could do is gather metrics on the queries, either using a server-side trace or extended events, and then aggregate those results by database.
thanks.my lovely boss wanted those statistics data.if i use the profile to literally record the query,the cost will be huge,now i believe that a rough statistics data can satisfy him, so i'm gonna run a aggregate statement on sysprocesses into a table per 10 minutes,at last aggregate those data.
Extended events are excellent and are light weighted. I am using the synchronous counter as target to collect every second the page life expectancy stays below 300 and the impact is minimal. You can find the script at my company website (
Www.sqlservice.se ) it's not a solution for you problem but it can give you an idea about the power of extended events, but if you want to learn extended events, you should take a look at "an extended event a day" from Jonathan kehayas
If you use Profiler to record the info, you might take a hit. That's why you should use a server-side trace. They are extremely light weight. Although as Hakan has said, extended events are even better.
This might be a good time to pitch the idea of some monitoring tools from a third party to your boss. They do a lot of the leg work for you and aren't to expensive. Heck there are even several good free ones.
File IO information for each database is relatively straightforward - monitor the output from this: select * from ::fn_virtualfilestats(-1,-1) Run it regularly, say every 10 minutes, and use this to build up a picture of which database files are being most heavily used. Note that these values are cumulative, and get reset when the server is reset...
