May 7, 2002 at 8:50 am
Problem Description: sp_who is showing the status as 'runnable' and cmd as 'select' still when I run dbcc inputbuffer for the spid's it's returning null into eventinfo.
Is there any way to find out what is the command get fired by the spid.
Even that eventinfo gives only part of the command fires(truncated to some length) is there is a way to get the full command as is shown in profiler.
Thanks,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 7, 2002 at 11:01 am
Not sure how Profiler fully does this but there is no particular command that I know of of that will let you do this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 12:19 pm
Profiler snags an event, I believe when the process starts. The last command, should be in DBCC InputBuffer. Are you sure the spid is still active? It might have timed out. Also, are you refreshing EM? It doesn't do this automatically.
Steve Jones
May 7, 2002 at 11:16 pm
Now after running for 36 hours that tasks finished. The concern was wait time was shown as 98767000 miliseconds for some threads while for other threads are showing 51000 miliseconds.
For this application(DC) it is usual that it took almost 24 hours and four dc are running parallely. Each DC opens two connections one does the select on the database and populate some variables and then second connection read those variables and insert data into a table, which is used in the end for output in standard format for olap applications(EssBase - Measures & Hierarchy)
Now only one DC is running for the firsty spid :
ecid are total 57(threads),
status 0 as runnable and others are sleeping.
Open tran 0,
waittype for 0 is 0x0800 and for others 0x0208,
waittime varies from 12641 to 36204063 for different threads.
cmd is select,
lastwaittype is NETWORKIO for ecid 0 and
CXPACKET for other ecid.
Physical i/o 9280 for all threads
Cpu 449031 for ecid 0
memusage 86
as usual dbcc inputbuffer is showing null for eventinfo
Is there is anything else should looked for further analysis.......
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 8, 2002 at 7:57 am
See related thread http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4109&FORUM_ID=65&CAT_ID=1&Topic_Title=Case+Study%3A++Expansion+plan+of+450+GB+database&Forum_Title=Performance+Tuning for my post.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply