Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-17 : 07:58:15

Is there any query or dmv which would gives the list of users(windows and sqlserver logins) who is consuming more cpu/IO on the server.

Thanks for your help in advance.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-17 : 08:15:58

sys.dm_exec_requests?

--
Gail Shaw
SQL Server MVP

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-11-17 : 10:42:08

Try joining these 2:

select * from master..sysprocesses


SELECT r.session_id, r.status, r.start_time, r.command, s.text, 
     SUBSTRING(text, (statement_start_offset/2) + 1,      
     ((CASE statement_end_offset           WHEN -1 THEN DATALENGTH(text)          ELSE statement_end_offset END
               - statement_start_offset)/2) + 1) AS statement_text,  
  blocking_session_ID, *
FROM 
  sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
order by 
  R.Start_Time

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-17 : 10:57:46

Thanks a lot..

How can i convert query to get the highest cpu usage (percentage) wise on user logins.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-17 : 11:05:31

Those DMVs just show current logins. If you want historical, you need to either trace or store the contents of those DMVs over time so that you can aggregate.

--
Gail Shaw
SQL Server MVP

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-17 : 11:10:22

I would like to store historical information for a week then generate reports.

Can you suggest to design for the table for these aggrerate usage report.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-17 : 12:47:35

You're the one who knows what he wants to see. The design should be based off what you want to report on.

Bear in mind that you either need traces running for a solid week (which will generate huge amounts of data) or you'll need to very frequently poll those table and deal with rows that you see multiple times (and accept that you'll miss fast queries). The trace is likely easier, but the data volumes could be a problem (I have databases that generate 500MB of trace data an hour)

--
Gail Shaw
SQL Server MVP

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-17 : 22:38:53

Does below query correct to get the visibility of users with highest database usage at any given point of time.

select loginame,cpu,login_time,memusage from master..sysprocesses
where cpu > 0

Thanks for your help in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources