Parallel Query Run and steps to Fix I/O Bottlenecks in Sql Server

In this post let me give you a brief description of problems of parallel query run and counters and other statistics that help us find the performance problems of parallel query run .Finally i will tell you 6 single line steps to fix I/O bottlenecks of performance in SQL Server.
Parallel Query Run:
If the query’s cost exceeds the value specified in the cost threshold for parallelism option then the optimizer attempts to generate a plan that can be run in parallel. A parallel query plan uses multiple threads to process the query. The maximum degree of parallelism can be limited server wide using the MAXDOP option.
The decision on the actual degree of parallelism (DOP) used for execution—a measure of how many threads will do a given operation in parallel—is deferred until execution time. A parallel query typically uses a similar but slightly higher amount of CPU time as compared to the corresponding serial execution plan, but it does so in a shorter duration of elapsed time. As long as there are no other bottlenecks, such as waits for physical I/O, parallel plans generally should use 100% of the CPU across all of the processors.
Look at the SQL Server: SQL Statistics – Batch Requests/sec counter.Because a query must have an estimated cost that exceeds the cost threshold for the parallelism configuration setting (which defaults to 5) before it is considered for a parallel plan, the more batches a server is processing per second the less likely it is that the batches are running with parallel plans.The plan can be retrieved using sys.dm_exec_cached_plan.We may also search for plans that are eligible to run in parallel by searching the cached plans to see if a relational operator has its Parallel attribute as a nonzero value.
Steps to fix I/O Bottlenecks
1) Check the memory configuration of SQL Server. If SQL Server has been configured with insufficient memory, it will incur more I/O overhead.
2) Buffer Cache hit ratio
3) Page Life Expectancy
4) Checkpoint pages/sec
5) Lazy writes/sec
6) Increase I/O bandwidth.

We will discuss these 6 steps in detail in the next post and let me research on them by that.Bye for Now.