Multiple processes with the same ID
-
Hi, I currently have a site in ASP.NET connecting to SQL Server 2005, what we have picked up is that there are a lot of processes with the same process id. i.e. 64 appears 7 times in the activity monitor, half of which is either sleeping or suspended, the other 3 is runnable. How is this happening? is it possible to have more than one process with the same ID? and a lot of these processed has a wait type of CXPacket. Just want to state that I'm no sql guru so its a process of head bumping at this stage. :doh: Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
-
Hi, I currently have a site in ASP.NET connecting to SQL Server 2005, what we have picked up is that there are a lot of processes with the same process id. i.e. 64 appears 7 times in the activity monitor, half of which is either sleeping or suspended, the other 3 is runnable. How is this happening? is it possible to have more than one process with the same ID? and a lot of these processed has a wait type of CXPacket. Just want to state that I'm no sql guru so its a process of head bumping at this stage. :doh: Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
Hi, You are seeing parallel execution of statements which is basically a good thing. However it can also be a problem (depending on the application) in which case you should reduce the amount of parallelism or even disable it. Usually if this is the case you also see very high (~100%) CPU load and actually your CPU becomes the bottleneck. Mika
-
Hi, You are seeing parallel execution of statements which is basically a good thing. However it can also be a problem (depending on the application) in which case you should reduce the amount of parallelism or even disable it. Usually if this is the case you also see very high (~100%) CPU load and actually your CPU becomes the bottleneck. Mika
The problem at this stage is that it is causing the database to slow to a snail pace which is pulling the site down completely. Is there a way that I can disable parallelism on only one database or do I have to do it on the entire sql server?
No matter how long he who laughs last laughs, he who laughs first has a head start!
-
The problem at this stage is that it is causing the database to slow to a snail pace which is pulling the site down completely. Is there a way that I can disable parallelism on only one database or do I have to do it on the entire sql server?
No matter how long he who laughs last laughs, he who laughs first has a head start!
Unfortunately you have to configure parallelism for the whole SQL Server. However you can configure parallel query so that it will not happen so often: 1. set
cost threshold for parallelismfor a higher value. For example if the value for this is 5, it means that if the operation is expected to take more than 5 seconds, parallelism shall be used. You can increase this value so that only 'long' operations use parallelism (say set theshold to 30 seconds or whatever is suitable). 2. don't let the database use all processors. If you have 4 processors, you can setmax degree of parallelismto 2 which means that only 2 processors are used simultaneously. Value 0 means that all processors are used To list your configuration use either Management studio or by T-SQL by configuring to see advanced options and then listing all option:sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure;
GOHope that this helps, Mika
-
Unfortunately you have to configure parallelism for the whole SQL Server. However you can configure parallel query so that it will not happen so often: 1. set
cost threshold for parallelismfor a higher value. For example if the value for this is 5, it means that if the operation is expected to take more than 5 seconds, parallelism shall be used. You can increase this value so that only 'long' operations use parallelism (say set theshold to 30 seconds or whatever is suitable). 2. don't let the database use all processors. If you have 4 processors, you can setmax degree of parallelismto 2 which means that only 2 processors are used simultaneously. Value 0 means that all processors are used To list your configuration use either Management studio or by T-SQL by configuring to see advanced options and then listing all option:sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure;
GOHope that this helps, Mika
-
Thanks a mil, I'll give it a bash and hopefully it sorts the problem
No matter how long he who laughs last laughs, he who laughs first has a head start!