May 8, 2008 at 9:37 am
I'm running a resource-intensive job.
I've noticed in the sp_who2 output that multiple threads/rows (10 or more) are spawned for that same SPID (most of those threads are sleeping).
What could be the cause of that?
MAXDOP is set to 3.
Actually in sysprocesses I see the following output:
LastWaitType: CXPACKET (indicates that I need to reduce MAXDOP)
Cmd: OPEN CURSOR
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 8, 2008 at 9:54 am
Are there triggers involved? I saw something reported in the MVP groups about something similar, multiple copies of the same SPID.
Are you using SQLClient? Any cursor activity?
May 8, 2008 at 10:53 am
Marios Philippopoulos (5/8/2008)
I'm running a resource-intensive job.I've noticed in the sp_who2 output that multiple threads/rows (10 or more) are spawned for that same SPID (most of those threads are sleeping).
What could be the cause of that?
MAXDOP is set to 3.
Actually in sysprocesses I see the following output:
LastWaitType: CXPACKET (indicates that I need to reduce MAXDOP)
Cmd: OPEN CURSOR
Hmm... let's review that for a second...:
Cmd: OPEN CURSOR
Can't help but think - "well THERE's your cuplrit right there."...
All right - I will go retreat to my cursor-hating cave now....:cool:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 8, 2008 at 11:08 am
I seem to recall reading somewhere that MAXDOP limited the number of running threads, not the total number of threads overall that a query could spawn.
If that's correct, then with a maxdop of 3, you can have 3 threads spawned to read the data, 3 more to do a join, three more to do an aggregate and finally 1 to retrun the data.
Only 3 will be running at a time though. the rest, sleeping.
I wish I remembered where I read that....
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
May 8, 2008 at 11:30 am
Thanks all for your responses.
Steve, yes, there are triggers running.
Matt, I hate cursors too... 🙂
Gail, thanks for the info, I've wondering about this for quite some time...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 8, 2008 at 11:35 am
Marios Philippopoulos (5/8/2008)
Gail, thanks for the info, I've wondering about this for quite some time...
Don't take it as authoritative, since I can't remember the source.
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
May 8, 2008 at 1:42 pm
When ever I have high I/O operation on queries, I get multiple threads for single SPID (Ofcourse it shows CXPacket as the wait type). One thing I also noticed is that, when you have multiple UNION ALL, it spawns quite a bit of thread as well.
The server I am seeing is on a 16 CPU, 32 GIG RAM Database server with raid 5 config.
-Roy
May 8, 2008 at 1:48 pm
Roy Ernest (5/8/2008)
When ever I have high I/O operation on queries, I get multiple threads for single SPID (Ofcourse it shows CXPacket as the wait type). One thing I also noticed is that, when you have multiple UNION ALL, it spawns quite a bit of thread as well.The server I am seeing is on a 16 CPU, 32 GIG RAM Database server with raid 5 config.
Make sure your MAXDOP is not set to 0. If it is, you are allowing your processes the potential to use all your cpus at any given point, a "no-no" in an OLTP environment.
Set your MAXDOP to 2 or 3 max, if yours is a busy OLTP system.
It looks like I need to reduce my MAXDOP as well...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 8, 2008 at 2:57 pm
Ah No..It is on our replicated box. We just run queries of it... No Transactions. And this server is an experimental one. We want to see how the CPU and High Mem is utilized by the SQL Server.
-Roy
May 8, 2008 at 3:08 pm
Marios Philippopoulos (5/8/2008)
Thanks all for your responses.Steve, yes, there are triggers running.
Matt, I hate cursors too... 🙂
Gail, thanks for the info, I've wondering about this for quite some time...
Keep in mind that your "need to reduce parallelism" makes sense when dealing with cursors. Cursors will ONLY use a single processor/serial processing unless they're specifically declared as KEYSET or STATIC, so trying to get that to use a parallel plan/multiple procs or threads is, well a waste of good resources. Then again - that could be said about damn near every cursor I've had the pleasure of dispatching.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply