February 2007 - Posts

About us (and some technical data about SQL Server configuration)

Few words about the Premier Field Engineers team:

Microsoft’s Premier Field Engineers deliver value to our Premier customers through services targeted at Productive Use and Adoption of Microsoft technologies within the IT life cycle. Here in the UK, there are PFE teams for SQL Server, Exchange server, BizTalk, Windows Servers, etc. There are currently twelve PFE in the SQL Server team.

 

Engineers work onsite to give proactive and reactive, dedicated and designated services to Premier customers and our Partners. These services are delivered by highly skilled engineers with deep technical expertise to meet global delivery accreditations standards.

Proactive work with our customers allows us to deliver specialized preventative services such as Health Checks and Workshops. Reactive engagements (on break – fix support incidents) are also included in the service delivery of PFE engineers.

 

Few words about the Blog

Q. does the world really needs another blog about SQL Server?!?

A. There are many good blogs out there about SQL Server, Analysis Services, Reporting services and DTS / Integration Services. Yet, we believe it is very important for the SQL Server users in the UK to know who are the people in Microsoft which can help them solving various problems. Here you could also read about interesting problems and get some inspiration for your solutions.  The technical subjects we’ll write about are the most common ones in Microsoft’s support team.

 

Let’s talk about SQL Server Configuration

Max Degree of Parallelism

One of the common problems related to SQL Server performance is the parameter Max degree of Parallelism. Let’s see what is the best practice for servers with more than one CPU and how Hyper Threading affect the recommendations.

Remember! Solving performance issues is some kind of black magic, no one really understands what you just did, sometimes it work and sometimes it doesn’t. I’m sure the best practice I’m going to talk about won’t help in 100% and I’ll be happy to hear from the DBA who tried it and didn’t see any improvement. 

To see the current value, run the procedure sp_configure.

exec sp_configure

If you can’t see it on the list run the script:

exec sp_configure 'show advanced options', 1

RECONFIGURE

 

The defualt value of the parameter is 0 which means use all avilabele CPU. For a single core CPU the best practise is using half of the number of CPU. If you enabled Hyper Threading set the parameter to the number of CPU. For ex. You have an 8 way machine and HT is enabled. The SQL Server and the O/S see 16 CPU. Set Max Degree of Parallelism to 8 (when you think about it, again use the rule of half the number of CPU).

 

Note – The SQL Server might use less than the number of CPU of the machine. This is set by affinity mask. Again, set the parameter to half of the number of CPU the SQL Serever can use.

 

Maximum Worker Thread

Running many queries in parallel take many Worker threads. However, it is recommanded to leave this parameter on its default value = 255.

 

cost threshold for parallelism

As you know each query has a cost which is, in very general speaking, a combination of CPU and IO operations. To see the cost of a query run it in the Query Analyzer or in the Managemnet Studio and “include Actual Query Plan” under the query menu.

By default the minimum cost for considering parallelism is 5. If most of the batches are simple short queries and transactions, probably the SQL Server doesn’t use Parallelism most of the time. In this case, changing the Max Degree of Parallelism won’t help.

 

 

 

Kind Regards,

 

Itay Braun

Premier Field Engineer - SQL Server

Microsoft Serives - UK

E-Mail: itayb@microsoft.com

Mobile: +44-796-928-9996

Veni         Vidi         Fixit

Search

Go

This Blog

Tags

Syndication