I have heard many times that the quickest way to improve the performance of a Sql Server instance is to throw more hardware at it. You would think this makes sense, but I have seen doubling the number of CPUs from (8 to 16) decreased the servers performance !
The reason is, under the default server settings, Sql Server will try to execute queries across all the CPUs in parallel. Depending on the type of the workload (OLTP or OLAP), the queries may take much longer to execute as splitting the query plan and combining the result again could take a lot loner than executing the query serially or with less number of CPUs.
Generally you will notice the parallelism issue with servers having more than 8 CPUs.
A quick way to check if your server is suffering from excessive parallelism is to look at the CXPACKET wait times.
This query will give you the top 10 wait types.
select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc
If you find CXPACKET value at the top of the list and you have more than 8 CPUs, you will probably benefit from lowering the degree of parallelism.
Also note that completely disabling parallelism (setting value to 1) may adversely affect large queries (OLAP). So you need to find the best value for your environment.
You can set the MAXDOP for individual queries which will override the server settings.
In order to change the degree of parallelism from the default (0) which means use all available CPUs to (say 4)
-- set to number of physical processors
EXEC sys.sp_configure N'max degree of parallelism', 4
GO
RECONFIGURE WITH OVERRIDE
GO
This will take effect immediately and no service restart is required.
Another option that is related to the degree of parallelism is the threshold (in seconds) that sql server will check before deciding to use parallelism.
The default value is 5 (i.e. Sql server will only use parallelism if the cost of executing a query serially is more than 5 seconds)
The following statement will modify this setting so that, sql server will only use parallelism for queries that take longer than 10 seconds to execute.
-- number of seconds elapsed
EXEC sys.sp_configure N'cost threshold for parallelism', N'10'
GO
The pictures below shows the affect of lowering MAXDOP on one of my servers on the processor queue length.
The reason is, under the default server settings, Sql Server will try to execute queries across all the CPUs in parallel. Depending on the type of the workload (OLTP or OLAP), the queries may take much longer to execute as splitting the query plan and combining the result again could take a lot loner than executing the query serially or with less number of CPUs.
Generally you will notice the parallelism issue with servers having more than 8 CPUs.
A quick way to check if your server is suffering from excessive parallelism is to look at the CXPACKET wait times.
This query will give you the top 10 wait types.
select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc
If you find CXPACKET value at the top of the list and you have more than 8 CPUs, you will probably benefit from lowering the degree of parallelism.
Also note that completely disabling parallelism (setting value to 1) may adversely affect large queries (OLAP). So you need to find the best value for your environment.
You can set the MAXDOP for individual queries which will override the server settings.
In order to change the degree of parallelism from the default (0) which means use all available CPUs to (say 4)
-- set to number of physical processors
EXEC sys.sp_configure N'max degree of parallelism', 4
GO
RECONFIGURE WITH OVERRIDE
GO
This will take effect immediately and no service restart is required.
Another option that is related to the degree of parallelism is the threshold (in seconds) that sql server will check before deciding to use parallelism.
The default value is 5 (i.e. Sql server will only use parallelism if the cost of executing a query serially is more than 5 seconds)
The following statement will modify this setting so that, sql server will only use parallelism for queries that take longer than 10 seconds to execute.
-- number of seconds elapsed
EXEC sys.sp_configure N'cost threshold for parallelism', N'10'
GO
The pictures below shows the affect of lowering MAXDOP on one of my servers on the processor queue length.
There is no rule of what value you should use, it all depends on your specific environment. But here are a few guidelines. Create a baseline of your server so that you can compare the performance of your server before and after the change.
No comments:
Post a Comment