SQL Server MAXDOP and parallelism

At our company, we have a 4 core blade running SQL Server 2008 Enterprise Edition.
This is a very, very busy SQL Server and is running on HP EVA hardware, so the I/O is not an issue.
We have over 50 warehouses all running SQL Server 2008 and replicating data to/from this primary database.
This database is also used by our website, webservices and import/export applications. The only thing this database does not serve is our SSRS reports, which are hosted on another blade.

What is an issue, however, are the CPU's. They were very busy. To help with this we can play around with two settings on SQL Server:
  • MAXDOP (Max degree of parallelism)
  • Cost threshold for parallelism



Our DBA and I had a few discussions around this recently. Read plenty of articles on it, such as Adam Machanic

On a normal system, leaving these settings alone are fine, but on a very very busy system you need to change them.

  1. MAXDOP=1. What this does is to always use 1 core for the query.
  2. MAXDOP=0, Cost = 20. This will use 1 core for simple queries, and for complex queries (cost > 20)  will be allowed to use parallel queries.
What we found is setting MAXDOP = 1 is optimal.

This is because running complex queries faster made the replication suffer. By using MAXDOP=1 we save CPU time by not having to join up parallel queries at the end. Save time by stopping the "thread thrashing" that can occur on many queries all running in parallel.

Because the SQL box is very busy fielding many queries at once, using MAXDOP=1still utilises all cores due to running 4 or more queries at the same time.
Also replication is a lot happier now.

Popular posts from this blog

Execution of user code in the .NET Framework is disabled

I was testing our new SQL Server 2008 enterprise cluster today and managed to get the following SqlException running a C# application: "Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. The statement has been terminated." The solution is to run the following: EXEC sp_configure 'show advanced options' , '1' ; go RECONFIGURE ; go EXEC sp_configure 'clr enabled' , '1' go RECONFIGURE ; -- Turn advanced options back off -- EXEC sp_configure 'show advanced options' , '0'; GO
Read more

Service Broker sys.transmission_queue clean up

If you’re developing a Service Broker application and you misspell your services, forget to create a master key, or do any of the many things that result in your messages not being delivered, then your transmission queue will fill up. Due to the nature of service broker, errors don't occur at the client but rather your messages go in to Service Brokers sys.transmission_queue and are then subsequently processed. If they fail, they stay in the queue. Some errors can be resolved, i.e. missing services. However, others can't, or are just plain difficult. I.e. You've misspelt something, or not used the correct security. To clear your transmission queue you need to end the conversations, this is a handy little script for doing this. Be aware this ends ALL conversations in the queue and so should NEVER be used on a production system. To see how many messages are in your transmission queue, use this SELECT   COUNT ( 1 )   FROM   sys . transmission_queue On my system i7, D...
Read more

What do I use to write software?

I started off with Dbase III, then Borland Turbo C for about 5 years, then when Microsoft Visual Studio v1.0 came out, I moved to that. I've been using Visual Studio ever since. Coding in C++ and have since moved to C# when that came into existance. Do I use component? Yes. Which ones then? Telerik http://www.telerik.com/ Dundas http://www.dundas.com/ Rebex http://www.rebex.net/ I've also used DevExpress reports, but they peg the CPU to 100% on the webservers when exporting an excel spreadsheet with a lot of data in them, and more often than not, cause a timeout on the ASPX page. So I don't like to use them anymore. My essential toolkit consists of: SVN - Subversion source control http://subversion.tigris.org/ Winrar http://www.rarlab.com/ Colour Schemer (web design) http://www.colorschemer.com/online.html SQL Prompt http://www.red-gate.com/ ANTS Profiler http://www.red-gate.com/ NUnit http://www.nunit.org/ Microsoft Visual Studio http://msdn2.microsoft.com/en-gb/vstudio/de...
Read more