Best way to get a stat - 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

General SQL Server Forums

New to SQL Server Administration

Best way to get a stat

Author

Topic

hanker
Starting Member

1 Post

Posted - 2011-05-02 : 22:19:56

Hi all,

What would be the best way to get the most frequently executed sp and functions?

I was thinking system monitor but couldnt find the right counter. Maybe dynamic management view?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-02 : 22:25:05

lookup books on line for sys.dm_exec_query_Stats. they also have some sample queries towards the end of the article..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-03 : 06:13:20

SELECT ST.TEXT,SP.* FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

you can use this query to find the most executed query at a time of execution of this query,you can filter more as

SELECT ST.TEXT,SP.* FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources