July 17, 2007 at 10:44 am
Can anyone suggest some solution for the below problem.
I want to see what all queries are running against my database. Is this something possible without having a sql profiler turned on as that would degrade the performance much more. What I am trying to see is are there any sqls which when run against the database slows down/creates block in that database. Is there any other means by which we can get this details. Any help on this will be greatly appeciated.
TIA
July 18, 2007 at 12:10 am
Hi
there are some DMV's (dynamic management views) which show the processes running against the database and give blocking information. one example would be sys.sysprocesses. you can search BOL for more info.
"Keep Trying"
July 18, 2007 at 12:55 am
There are basically two DMV's you can use.
sys.dm_exec_requests gives you real time information
SELECT
st.text, r.*
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE
r.session_id = 112
sys.dm_exec_query_stats gives you historical information about all queries run since the last time SQL Server started. The forllowing query gives you the 10 queries which used the most CPU time.
SELECT
TOP (10)
st.text AS QueryText
,last_execution_time
,execution_count
,total_worker_time/execution_count AS Avg_CPU_Time
,max_worker_time AS Max_CPU_Time
,max_elapsed_time AS Max_Duration
,(total_elapsed_time/execution_count) AS Avg_Duration
FROM
sys.dm_exec_query_stats
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE st.text IS NOT NULL
Order by Max_CPU_Time desc
You might also want to check out this link http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
Hope this helps
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply