August 24, 2012 at 6:25 am
Anyone got a query that returns all spids who break out to parellel spids?
August 24, 2012 at 7:25 am
Don't they all run within the context of that one spid? (Assuming you mean SQL paralellism rather than from an application that is multi-threaded).
August 24, 2012 at 7:46 am
I've got processes who do not have maxdop set therefore on occasion the same spid is breaking out to 10- 20 parellel processes. I'm trying to identify them or better yet track them. I see i can run the below to get them. Now i'm trying to add into this the ability of what's actually running. The sql text.
select spid, COUNT(1) as Counter
from master.dbo.sysprocesses
group by spid
HAVING COUNT(1) >1
August 24, 2012 at 7:51 am
got it.
SELECT *from (
select spid,sql_handle, COUNT(*) as Counter
from master.dbo.sysprocesses
group by spid,sql_handle
HAVING COUNT(1) >1
) dup
CROSS APPLY sys.Dm_exec_sql_text(sql_handle) sp
August 24, 2012 at 7:53 am
I use something like this, any good to you?
Cheers
USE master
GO
SELECT sp.kpid as thread,
r.session_id, s.login_name, s.nt_user_name, s.host_name, r.open_transaction_count,
r.start_time, r.status, r.command, DB_NAME(r.database_id) AS [Database], r.blocking_session_id, r.wait_type, r.wait_time,
r.total_elapsed_time, r.cpu_time, r.reads, r.logical_reads, r.writes, r.row_count,
r.percent_complete, r.estimated_completion_time, r.estimated_completion_time/60000 as MinutesLeft,
SUBSTRING(st.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS statement_text,
st.text AS Full_Statement
FROM sys.dm_exec_requests r
outer APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
left join sysprocesses sp on sp.spid = r.session_id
left join sys.dm_exec_sessions s on s.session_id = r.session_id
WHERE r.session_id <> @@SPID
order by s.session_id, sp.kpid;
August 24, 2012 at 7:57 am
looking it over....thanks
August 24, 2012 at 8:05 am
No problem. Hadn't realised there was a sql_handle field in sysprocesses as well. Probably comes from the same place under the covers in any case!
August 24, 2012 at 9:11 am
changed it slightly. changed the status to ISNULL(sp.status,r.status). and added a left join to the sys.dm_exec_procedure_stats view so i can get the proc name and it's status seems to be more realistic. One thing i dont understand and it may be because im joining to the sys.dm_exec_procedure_stats on sql_handle. But how come i get some records who are obviously running a stored procedure as u can tell by the Full_statement column yet they're not getting returned from the sys.dm_exec_procedure_stats view
SELECT Object_name(ps.object_id) ,
sp.spid,sp.kpid as thread,
r.session_id, s.login_name, s.nt_user_name, s.host_name, r.open_transaction_count,
r.start_time, ISNULL(sp.status,r.status) status, r.command, DB_NAME(r.database_id) AS [Database], r.blocking_session_id, r.wait_type, r.wait_time,
r.total_elapsed_time, r.cpu_time, r.reads, r.logical_reads, r.writes, r.row_count,
r.percent_complete, r.estimated_completion_time, r.estimated_completion_time/60000 as MinutesLeft,
SUBSTRING(st.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS statement_text,
st.text AS Full_Statement
FROM sys.dm_exec_requests r
OUTER APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
LEFT JOIN MASTER.dbo.sysprocesses sp
ON sp.spid = r.session_id
LEFT JOIN sys.dm_exec_sessions s
ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_procedure_stats ps
ON r.sql_handle = ps.sql_handle
WHERE r.session_id <> @@SPID
AND Db_name(r.database_id) = 'VOS12000000'
AND ISNULL(sp.status,r.status) <>'background'
ORDER BY sp.spid
,s.session_id
,sp.kpid;
August 24, 2012 at 9:16 am
also, is there a way i can get the actual proc call w/ the input params and all if it exists?
August 24, 2012 at 9:41 am
BaldingLoopMan (8/24/2012)
But how come i get some records who are obviously running a stored procedure as u can tell by the Full_statement column yet they're not getting returned from the sys.dm_exec_procedure_stats view
At a guess, the proc's execution plan isn't cached - sys.dm_exec_procedure_stats only holds info for procs in the cache.
is there a way i can get the actual proc call w/ the input params and all if it exists?
I don't know of a way directly in the query. You might be able to get the info from DBCC INPUTBUFFER. Otherwise, you may need to go to Profiler to get what you need.
August 24, 2012 at 10:01 am
Now we're cookin.
One of my issues is i'm getting execution plans on occation going really really bad and i'm trying to monitor when it happens. So here's what i got nd tell me if my logic is sound. Below will return all procs called in last 5 second sorted by reads. it also returns the cached_time, and size of cached plan in bytes. Theoretically if a plan is cached badly for whatever reason one day, is it safe to assume that the size of cached plan in bytes will grow relative to when it had a good optimized exec plan?
SELECT Db_name(ps.database_id) DatabaseName
,Object_name(ps.object_id) ProcName
,ps.last_logical_reads
,ps.last_physical_reads
,cached_time
,last_execution_time
, size_in_bytes
FROM sys.dm_exec_procedure_stats ps
outer APPLY sys.Dm_exec_sql_text(sql_handle) s
LEFT JOIN (SELECT sp.name procname
,*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.Dm_exec_sql_text(plan_handle) a
LEFT JOIN sys.procedures sp
ON sp.object_id = a.objectid
WHERE cp.cacheobjtype = N'Compiled Plan'
) tt
ON tt.procname = Object_name(ps.object_id)
WHERE Object_name(ps.object_id) IS NOT NULL
AND last_execution_time >= Dateadd(mi, -5, Getdate())
ORDER BY ps.last_logical_reads DESC
August 24, 2012 at 10:33 am
BaldingLoopMan (8/24/2012)
Theoretically if a plan is cached badly for whatever reason one day, is it safe to assume that the size of cached plan in bytes will grow relative to when it had a good optimized exec plan?
Well, it's not an assumption I'd rely on to be honest.
I think you might be better off using Profiler here - looking for the SP:Recompile and SP:CacheMiss events would be a good start.
Of course, if what you've got is working for you, don't let me stop you 🙂
August 24, 2012 at 11:06 am
BaldingLoopMan (8/24/2012)
Theoretically if a plan is cached badly for whatever reason one day, is it safe to assume that the size of cached plan in bytes will grow relative to when it had a good optimized exec plan?
No. The size of the plan in bytes has no relation at all to how efficient the plan is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2012 at 11:43 am
Is there a way to querry the estimated reads from a cached exec plan?
Also, why would there be multiple records for a given proc name in th below. Are there multiple cached plans for a given proc? I show one w siz_in_bytes = 560000 and user count = 1 and the same proc again in there w/ size_in_bytes = 5,750,000 and used count = 1508. Whats that all about?
select *
from sys.dm_exec_cached_plans
August 24, 2012 at 12:09 pm
sys.dm_exec_query_stats.
Not estimated, aggregated actual performance statistics. There would be no point in estimated reads.
There shouldn't be multiple plans for a procedure, unless it gets called from apps with different set options (some set options are plan cache keys, some aren't).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply