July 26, 2012 at 2:01 pm
In Profiler I see few connections from our IIS server using Serializable Transaction Isolation. I do not know programming but got full access to the IIS server. Is there a way for me to check what IIS app(s) is using Serializable Transaction Isolation? I checked the web.config files but do not see anything with setting Transaction Isolation.
Thanks,
July 26, 2012 at 2:16 pm
The usual way I do things like that is grab the queries out of Profiler (or out of a server-side trace, either one), and have devs find those queries in their code. Normally, a search through source control for a query is pretty fast.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2012 at 3:26 pm
that look like finding needle in a haystack because we have new dev guys unfamiliar with the system :angry:
I found a stored procedure suspended in sysprocesses. When we tested the SP in SSMS , it ran quick. However when we use it in production, I see in table sysprocesses there are processes using that SP with same SPID and all processes but 1 are suspended. Like there are 10 rows with SPID = 100 and only 1 is runable, the rest are suspended. I do a quick dbcc inputbuffer (100) and see that the SP is in the EventInfo. The lastwaittype of runable proess is SOS_SCHEDULER_YIELD, the other 9 suspended have lastwaittype = CXPACKET. The server has 2 dual core CPUs; max degree of parallelism = 2; cost threshold for parallelism = 5. I am pulling my hair because the IIS server got timeout errors and no one has a clue :crazy:
July 30, 2012 at 3:23 pm
It sounds like you're approaching a situation that may become critical in a hurry, if it has not already.
How many cores per CPU? Must be at least 8 each given your comments? What you're saying about there being 10 processes for SPID 100 only computes if you have at least 10 cores and a query hint of MAXDOP 10 or higher, or MAXDOP 0, is being issued along with the query you're seeing 10 processes for.
The CXPACKET waits mean the other nine threads are waiting on the 10th to complete its current task so overall query execution can proceed. The SOS_SCHEDULER_YIELD on that 10th thread means it is waiting for CPU time. Too many of these types of waits denotes you may be experiencing CPU pressure. Have a look at your wait stats. If SOS_SCHEDULER_YIELD is in the top few then have a look at the most expensive queries by CPU and see if you can find any problem children you can easily tighten up. CPU-hungry queries can sometimes be tamed by adding an index to turn a scan into a seek.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 30, 2012 at 4:07 pm
Yes that was a critical issue but thanks god we had it resolved. The problem was a search query. We finally had the query tuned and the whole thing got better.
BTW, the system has 2 dual-core processors so the OS sees 4 CPU's.
July 30, 2012 at 4:16 pm
htt (7/30/2012)
Yes that was a critical issue but thanks god we had it resolved. The problem was a search query. We finally had the query tuned and the whole thing got better.
Happy you got it sorted.
BTW, the system has 2 dual-core processors so the OS sees 4 CPU's.
I am puzzled as to how you could have seen 10 threads for SPID 100.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 30, 2012 at 4:43 pm
opc.three (7/30/2012)
I am puzzled as to how you could have seen 10 threads for SPID 100.
Two operators running with 4 threads, 2 co-ordinator threads. They won't all be running, only 4 from one operator will be executing at most at any time.
MaxDop is per operator in a query.
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
July 30, 2012 at 4:53 pm
GilaMonster (7/30/2012)
opc.three (7/30/2012)
I am puzzled as to how you could have seen 10 threads for SPID 100.Two operators running with 4 threads, 2 co-ordinator threads. They won't all be running, only 4 from one operator will be executing at most at any time.
MaxDop is per operator in a query.
OK, this is new for me. Have any recreational reading that might help?
OP says they had 'max degree of par' set to 2. Does that change the split you mentioned?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 30, 2012 at 4:58 pm
opc.three (7/30/2012)
OP says they had 'max degree of par' set to 2. Does that change the split you mentioned?
Yes, but the specifics, not the general rule. Maxdop is per operator, not per query, it also sets the maximum number of threads that will be running at a time. Lots of resources, none of which I have links to offhand. Google.
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
July 30, 2012 at 5:00 pm
GilaMonster (7/30/2012)
opc.three (7/30/2012)
OP says they had 'max degree of par' set to 2. Does that change the split you mentioned?Yes, but the specifics, not the general rule. Maxdop is per operator, not per query. Lots of resources, none of which I have links to offhand. Google.
That's the key piece of info I think I was missing, thanks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply