Expensive Queries with Program_name its belong

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 2012 Forums

SQL Server Administration (2012)

Expensive Queries with Program_name its belong

Author

Topic

pkrana1980
Starting Member

3 Posts

Posted - 2014-09-05 : 12:30:39

We need to find expensive queries along with "Program_name" it's belong.

We can easily find expensive queries using DMVs (sys.dm_exec_query_stats, sys.dm_exec_sql_text) but I am not able to link it with "Program_name" they belong, where as I have setup Data Collection and there I can see Program_name(see in green color), please help me to find the same without setup the Data Collection as it add load to the server.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-05 : 12:56:58

Program name is not stored with the trace.
You can investigate it by adding "Application Name=XYZ" in your connection string, but there is NO way for the database to tell which application did what request.
Then you have to correlate the connection made by the application and storing the SPID for the connection. When the connection is dropped, any other application can reuse the same SPID number.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-09-08 : 07:38:50

For accurate details about costly query you have to use sys.dm_exec_query_stats but that does have have session_id column to join it to sys.dm_exec_session dmv. I came out with this simple query

select db_name(er.database_id) as Database_Name,
er.wait_type,
er.command,
er.wait_time,
er.cpu_time,
er.total_elapsed_time ,
es.program_name
from
sys.dm_exec_requests er
join
sys.dm_exec_sessions es
on
es.session_id=er.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
where es.is_user_process=1
--group by es.program_name
order by er.total_elapsed_time desc

Hope this would help

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

pkrana1980
Starting Member

3 Posts

Posted - 2014-09-08 : 16:16:48

Thanks Shanky, it will shows data for current active session only as task complete the same session_ID could be assign to different process and so on..
I want to fetch TOP 100 expensive queries for yesterday with Program_name they belong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources