SQL Server Performance Forum – Threads Archive
Multiple NT threads on one SPIDAfter our application has crashed, I am trying to trace a problem.
I run sp_lock and it shows me there is severe locks on two tables (120,000 row locks with occasional page locks) being held on the database for a specific SPID (127)
When I run sp_who2 it shows the same SPID occuring three times, and on querying the sysprocesses to get more details I notice that it says the same SPID has three seperate NT Threads. Why does this occur, would it cause any detrimental problems, could it cause SQL server to stop serving ADO requests (all ADO requests timeout). This is a production problem and therefore any suggestions no matter how trivial are more than welcome, Thanks
Have this table good indexes?
May be there is a Scan Table for this spid.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
This is because of parallel execution. This will happen if the statement is being executed by multiple threads. _________
furthermore, parallel execution plans may on occasions cause severe locking problems, i would consider examining each parallel plan’s run time compared with same query with OPTION (MAXDOP 1). if there is no good reason for the parallel plan, consider disabling it
Thanks for suggestions.
I thought it might be parallelism as the box is an 8 CPU box, with 4GB of memory.
Is there anyone of determining the threshold for paralellism i.e. ratio of memory to CPUs etc i.e. Lock escalation happens at a threshold of about 25% of total SQL Memory, is there some equivalent value for parallelism?
What are values set for max degree of parallelism & cost threshold for parallelism ? Use SQL Profiler to monitor the degree of parallelism for individual statements. Use the Degree Of Parallelism event class in the Performance event category. _________
It uses all available processors and the cost value is 5 (default)