Parallel Query Recommendations
Microsoft® SQL Server™ can execute queries in parallel automatically. This optimizes the query execution in multi-processor computers. Rather than using one OS thread to execute one query, work is broken down into multiple threads (subject to the availability of threads and memory), and complex queries are completed faster and more efficiently.
The optimizer generates the plan for the query and decides when a query will be executed in parallel. It considers the following when making the decision:
- Does the computer have multiple processors?
- Is there enough memory available to execute the query in parallel?
- What is the CPU load on the server?
- What type of query is being run?
When allowing SQL Server to run parallel operations like DBCC and index creation in parallel, the server resources become stressed, and you might see warning messages when heavy parallel operations are occurring. If warning messages about insufficient resources appear frequently in the server error log, consider using Performance Monitor to investigate what resources are available, such as memory, CPU usage, and I/O usage.
Do not run heavy queries that are executed in parallel when there are active users on the server.
Try executing maintenance jobs such as DBCC and INDEX creation during offload times. These jobs can be executed in parallel.
Monitor the disk I/O performance. Observe the disk queue length in Performance Monitor to make decisions about upgrading your hard disks or redistributing your databases onto different disks.
Upgrade or add more processors if the CPU usage is very high.
Configuration Settings that Influence Parallel Queries
The following server configurations can affect parallel execution of the queries:
- Cost threshold for parallelism
- Maximum degree of parallelism
- Maximum worker threads
- Query governor cost limit