Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bad performance. EXECSYNC wait type? Expand / Collapse
Author
Message
Posted Thursday, August 25, 2011 11:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:06 PM
Points: 317, Visits: 876
Can anyone shed some more light on the EXECSYNC wait type? I've seen it appear in WAITSTATS on other systems that use parallelism, but it's usually pretty far down on the list (with CXPACKET on top). But I've migrated my database to a very beefy SQL 2008 R2 box and I'm seeing slower performance with long EXECSYNC waits on large queries. I've done some googling, but there doesn't appear to be much out there.

For reference, the new system is, as I said, SQL 2008 R2, running on Windows 2008 R2 64-bit. The server is an IBM System x3850 X5 with 40 logical processors (2 10-core) and 128GB of RAM. And the backend is a new EMC VNX 5700. I've run update stats on the entire database with fullscan, so query plans should be optimal. MAXDOP is set to 10.

On paper, this server should fly, but it's slower than the same database running on a virtual server with 8 processors, 32GB memory, and our old SAN. Something's not adding up.

Thoughts?




Colleen M. Morrow
Cleveland DBA
Post #1165607
Posted Thursday, August 25, 2011 11:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
That's the definition I can get from MS, not sure if it actually helps.

Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such area are bitmaps, large binary objects (BLOBs) and the spool iterator. LOBs can frequently use this wait state. Bitmap and spool use should not cause contention.


Post #1165617
Posted Thursday, August 25, 2011 11:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:06 PM
Points: 317, Visits: 876
Thanks. I saw that, but it didn't really clarify anything for me.

We're not using LOBs in this database. Can you explain what, exactly, that first sentence means?




Colleen M. Morrow
Cleveland DBA
Post #1165627
Posted Thursday, August 25, 2011 11:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
Colleen M. Morrow (8/25/2011)
Thanks. I saw that, but it didn't really clarify anything for me.

We're not using LOBs in this database. Can you explain what, exactly, that first sentence means?


Nope, you need Paul White or another internals expert for that one.
Post #1165630
Posted Thursday, May 1, 2014 9:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 13, 2014 3:42 AM
Points: 10, Visits: 175
For example, this happens when you start a import/export wizard to copy data to a table. it starts two process, one process selects the data and other one inserts the data. This is parallel query/process.

When you stop and exit your import/export wizard for some reason, the insert(bulk insert) goes away, but the parallel query select does not go away, which you can see using sp_who2 command.

This process will take its own time to go away, but if you kill this select process deliberately, then the EXECSYNC happens.

Post #1566911
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse