PAGEIOLATCH_SH: Occurswhen a task is waiting on a latch for a buffer that is in an I/Orequest. The latch request is in Shared mode. PAGEIOLATCH_SH waits isvery brief as they are only held on a page during IO operations.
You can also identify I/O bottlenecks by examining the latch waits.These latch waits account for the physical I/O waits when a page isaccessed for reading or writing and the page is not available in thebuffer pool. When the page is not found in the buffer pool, anasynchronous I/O is posted and then the status of the I/O is checked.If I/O has already completed, the worker proceeds normally. Otherwise,it waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the typeof request. The following DMV query can be used to find I/O latch waitstatistics.
Select wait_type,
waiting_tasks_count,
wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
More detail information on performance probblems in SQL Server 2005, please navigate to the following article.
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
posted @ 2008-03-20 11:46 Rickie 阅读(34) | 评论 (0) | 编辑
2008年3月18日 #
SQL Server 2005: Wait Type - CXPACKET
Wait type - CXPACKET
Itmeans SQL Query is involved in parallel query execution. This waittypeindicates that the SPID is waiting on a parallel process to complete orstart.
CXPACKET: Occurs when trying to synchronize the queryprocessor exchange iterator. You may consider lowering the degree ofparallelism if contention on this wait type becomes a problem.
******
How to configure cost threshold for parallelism Option
Use the cost threshold for parallelism option to specifythe threshold at which Microsoft SQL Server creates and runs parallelplans for queries. SQL Server creates and runs a parallel plan for aquery only when the estimated cost to run a serial plan for the samequery is higher than the value set in cost threshold for parallelism.The cost refers to an estimated elapsed time in seconds required to runthe serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors.
Longer queries usually benefit from parallel plans; the performanceadvantage negates the additional time required to initialize,synchronize, and terminate parallel plans. The cost threshold for parallelism optionis actively used when a mix of short and longer queries is run. Theshort queries run serial plans, whereas the longer queries use parallelplans. The value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans.
In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelismvalue. This can happen because the decision to use a parallel or serialplan is based on a cost estimate provided before the full optimizationis complete.
The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.
SQL Server ignores the cost threshold for parallelism value under the following conditions:
The following example sets the cost threshold for parallelism to 10 seconds.
sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 10;
GO
reconfigure;
GO
You can also identify I/O bottlenecks by examining the latch waits.These latch waits account for the physical I/O waits when a page isaccessed for reading or writing and the page is not available in thebuffer pool. When the page is not found in the buffer pool, anasynchronous I/O is posted and then the status of the I/O is checked.If I/O has already completed, the worker proceeds normally. Otherwise,it waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the typeof request. The following DMV query can be used to find I/O latch waitstatistics.
Select wait_type,
waiting_tasks_count,
wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
More detail information on performance probblems in SQL Server 2005, please navigate to the following article.
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
posted @ 2008-03-20 11:46 Rickie 阅读(34) | 评论 (0) | 编辑
2008年3月18日 #
SQL Server 2005: Wait Type - CXPACKET
Wait type - CXPACKET
Itmeans SQL Query is involved in parallel query execution. This waittypeindicates that the SPID is waiting on a parallel process to complete orstart.
CXPACKET: Occurs when trying to synchronize the queryprocessor exchange iterator. You may consider lowering the degree ofparallelism if contention on this wait type becomes a problem.
******
How to configure cost threshold for parallelism Option
Use the cost threshold for parallelism option to specifythe threshold at which Microsoft SQL Server creates and runs parallelplans for queries. SQL Server creates and runs a parallel plan for aquery only when the estimated cost to run a serial plan for the samequery is higher than the value set in cost threshold for parallelism.The cost refers to an estimated elapsed time in seconds required to runthe serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors.
Longer queries usually benefit from parallel plans; the performanceadvantage negates the additional time required to initialize,synchronize, and terminate parallel plans. The cost threshold for parallelism optionis actively used when a mix of short and longer queries is run. Theshort queries run serial plans, whereas the longer queries use parallelplans. The value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans.
In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelismvalue. This can happen because the decision to use a parallel or serialplan is based on a cost estimate provided before the full optimizationis complete.
The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.
SQL Server ignores the cost threshold for parallelism value under the following conditions:
- Your computer has only one processor.
- Only a single CPU is available to SQL Server because of the affinity mask configuration option.
- The max degree of parallelismoption is set to 1.
The following example sets the cost threshold for parallelism to 10 seconds.
sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 10;
GO
reconfigure;
GO
添加至收藏夹