February 8, 2016 at 9:26 am
What would be a good way to monitor how much server resource a user is using over a period of time?
I would be looking to capture over 30 days, how much % of the CPU used by SQL was used by the user, the average memory consumed, the total tempdb usage, and possible the time they spent blocking other connections with their queries (if possible). Or any other good metrics. This would be for just one particular user only. It would only be queries run on one particular database on a server.
Would this be something Extended Events could do, or would using something like sp_whoisactive be better? I could potentially write the info into a separate table to hold the months data and then aggregate it after the 30 days.
Any pointers would be great.
Thanks.
February 8, 2016 at 10:31 am
I haven't done this myself, but if I were going to do it, I'd use extended events. The trick is going to be filtering by a particular user. If you use application logins instead of individual logins for connecting to the database, I'm not sure how to identify the user. However, tracking for a particular login and capturing their query metrics and those queries cpu & io metrics, is all pretty straightforward using extended events. You'll have to capture rpc_completed and sql_batch_completed events. Then add a filter on them for the login you're interested in. After that, aggregate the 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
February 9, 2016 at 9:45 am
Perfect, thank you.
I'll have a dig around and see what I can come up with.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply