This video is a follow up to CXPACKET video (Part 1). You will see that SQL Server Query Execution Engine still assign max number of threads (as per the number of cores (up to a max of 64)) even when the DB engine is under severe pressure. In today’s note, I wish to pen down a few more thoughts about SQL Server parallelism and the CXPACKET wait type. Now, this is my observation in general and I am not 100% sure that irrespective of the stress SQL Server engine is under, it will always assign the number of threads equivalent to the number of virtual processors for a parallel query under default affinity. Let me elaborate. Let’s say the CPU affinity is set to default, which means that SQL Server will use all the CPUs available to it. Now, let’s say the engine is under heavy stress. The box has 8 virtual processors and there are more than 1000 user requests at any point in time. Overall CPU is choking at 100% since 8 processors have to serve so many threads. Clearly, the engine is under a heavy workload. Now, you fire a simple, inexpensive query for which the query optimizer decides a parallel plan, and during execution, it gets 8 threads even if the query would have just run fine with 4 threads. I mean, the engine is already reeling under heavy stress and it adds more to it. Do you see the same observation? Or have you witnessed that the execution engine takes an informed decision to assign an appropriate number of threads based on the current stressful condition of the server? To know more watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.