Parallelism on OLTP often kills performance (so turn it off)...
One of my customers is currently converting to SQL Server 2005 Ent Edn 64bit & was disappointed today to observe a query which had been running well on SQL 2000 perform extremely badly on SQL 2005 in the test lab. So I asked for the 2000 / 2005 execution plans be sent over & identified that SQL 2005 was parallising the query where SQL 2000 had been running the query serially.
Given that this customer's system performs an exclusively OLTP workload, I recommended that they test turning off query parallelism (by setting 'Max degree of parallelism' via sp_configure). They're presently testing as I'm writing this, but I'll report on the outcome when they get back to me.
The main point of this blog is to point out that, in most cases, the rationale for adding additional CPU resources to an OLTP SQL Server is to provide more parallel processing power to service more concurrent user requests, rather than to provide for parallelism within individual queries.
Transactions within OLTP systems tend to have considerably smaller resource requirements than those of Decision Support Systems (DSS) & therefore they typically run fairly well serially. Very often, when SQL Server decides to parallelise queries that comprise OLTP transactions, it does so for the wrong reason - eg out of date index statistics influence the cost based optimiser to scan rather than seek into an index & hence the decision to try & parallelise the over-estimated work-load.
Decision Support Systems are different of course, because it's handy to divide the work of monster data warehouse queries accross multiple CPUs, but I'm talking about OLTP systems specifically here.
So, consider turning off parallelism on OLTP systems if you're seeing parallel execution plans in poorly performing queries. It's usually possible to do this during periods of relatively lower activity & doesn't require a SQL Server restart or machine boot:
To turn parallelism off, you set the 'max degree of parallelsim' configuration to 1, denoting that queries can only use 1 CPU eg:
exec sp_configure 'max degree of parallelism', 1
go
reconfigure
go
The default setting is 0, which denotes that any query can use all available CPUs. If you decide that turning off parallelism didn't achieve a noticable improvement in performance, you can simply turn it back on by setting it back to 0, eg:
exec sp_configure 'max degree of parallelism', 0
go
reconfigure
go
You can also set it to a specific number, based on however many CPUs you want to limit query parallelism to, but I've found this to be a relatively rare requirement.
Another option is to limit specific queries by adding the MAXDOP option, eg:
select * from pubs..authors
option (maxdop 1)
This technique has the advantage of limiting specific poorly performing queries, without turning off parallelism for the entire server but the downside is that you've got to specifically add the extra option hint SQL code, which often isn't possible with 3rd party vendor apps or internal apps with tightly managed source control.
Poor OLTP performance resulting from parallelism is a fairly common scenario I see in my performance tuning travels so today's episode prompted me to blog about it for posterity (c:
Cheers,
Greg Linwood