March 2008 - Posts

Configuring x64 SQL Server

I often read a number of forum posts concerning performance / configuration issues with x64 SQL Server. I've also found some interesting views on how much memory you can allocate to SQL Server on a x64 install ( I don't have any experience of ia64 - sorry ).

Sort of cross purposes I've been trying to identify paging in x32 systems and I found this KB about x64. There's a note that this KB is currently being rewritten so you might want to keep track of it.

I've also got to add that it appears patching the o/s, which seems to be neglected so often, can help, anyway have a good read of this KB if you're using x64 and performance isn't quite as you might expect.

http://support.microsoft.com/kb/918483

 

Do you really need those services?

 As a continuation of my series of posts which look at Windows Server rather than SQL Server here's one about services. 
  • o   When your SQL Server is under pressure stopping or disabling unnecessary services may free up resource.
  • o   This is especially true of x32 systems where bottom memory allocation is critical.
  • o   Just because x64 has a flat memory model there is still no reason to not pay attention to services, some of which may actually be weak spots in your defenses. 

 

  • o   Important:  Do not just disable services and hope for the best, it could be you may be using some of them.
  • o   I’m assuming this is for a dedicated SQL Server and is based upon Windows 2003 SP2.
  • o   Disabled services can stop service pack installation, always set to manual if you are unsure 
  • o   IIS requires a special mention, usually this isn’t installed but if you want to run SSRS then it has to be installed.
  • o   I have seen this installed purely to enable smtp forwarding, use a custom proc rather than installing and running IIS.

 

  • o   Telephony  -  this cannot be stopped whilst Remote Access Connection Manager and Remote Access Auto Connection are running.
  • o   Windows Firewall – Probably not wanted on a Server ( ** )
  • o   Server Service –  ( not listed ) you can stop this but you lose file shares part of the process to harden a SQL Server.
  • o   Windows Installer – ideally this shouldn’t be running but be careful of disabling it. 
  • o   Depending on your build and install you may have other services installed or running. I frequently see Full Text Service  and Analysis Services running although no use is being made of them, don’t just install everything.
  • o   Same applies to Indexing Service.  
Windows Service Setting

Application Management

Disabled

Alerter

Disabled

Automatic Updates

Disabled

Background Intelligent Transfer Service

Manual

Clipbook

Disabled

Computer Browser

Disabled

Distributed File System

Disabled

Distributed link tracking client

Disabled

Distributed transaction coordinator

Manual

Error Reporting Service

Disabled

Fax Service

Disabled

File Replication

Disabled

Help and Support

Disabled

HTTP SSL

Disabled

License Logging

Disabled

Logical Disk Manager

Manual

Messenger

Disabled

Portable Media Serial Number Service

Manual

Print Spooler

Disabled

Remote Access Connection Manager

Manual

Remote Access Auto Connection

Manual

Remote Registry

Manual

Secondary Logon

Manual

Shell Hardware Detection

Disabled

Task Scheduler 

Disabled

TCP/IP NetBIOS Helper

Manual

Telephony

Manual

Windows Audio

Disabled

Windows Firewall

Disabled **

Wireless Configuration

Disabled

 

© grumpy old dba   march 2008

  

 

Bug in UK SSUG 2008

It was discovered earlier today that SSUG 2008 was unable to distinguish a Thursday when it fell before a bank holiday friday and as a consequence SSUG would be unable to function on March 20th.

It's not thought that other Thursdays that fall before a Friday bank holiday will cause similar problems, mainly as they only occur every six years or so, Christmas, and the date of Christmas remains static at 25th December, unlike Good Friday whose due date can only be calculated by getting up at night and observing the phase of the moon.

A spokesman said the the 20th of March would be moved to another more convenient date when SSUG 2008 would be functioning correctly.

Things that may not be right on your SQL server.

Ø  When performance is under the spotlight most often the finger is pointed at SQL Server, here’s a short list of things that may impact your server performance.

 

Disclaimer : This is my personal hit list and in no particular order.

 

Out of date drivers

Look mainly for HBA, NIC and storage controllers

Incorrect NIC settings

Your NIC(s) should have the speed and duplex fixed. Cards set to AUTO are bad news. Make sure your NIC is running full duplex

HBA Buffer/Queue depth

This is tricky to define exactly, just make sure the value is not under 32

dns ( and/or wins )

You might want to try flushing the dns cache on your NIC’s

Teaming and Multi path

If you’re using these make sure they’ve been set up correctly

NTFS fragmentation

You don’t want any.

Shared LUNs on the SAN

Check your io completion times vs load to expose contention from shared LUNs

Raid 5

Unless your database is read only.

Large System Cache

Advanced properties, optimise for system cache vs programs. It needs to be PROGRAMS.

Processor scheduling

Advanced properties, optimise for background processes vs programs. It needs to be BACKGROUND PROCESSES.

Visual effects ( why we have this on a server I never know )

Advanced properties, set to PERFORMANCE.

Page file

Make this fixed. Don’t put it on the same physical spindles as your o/s.  For x64 consider removing the page file.

3GB switch

This can have some serious detrimental effects due to memory starving which may manifest as increased paging.

Too many cores / hyperthreading

The number of cores ( HT’s count as cores to SQL ) can drain considerable memory , SQL Server allocates worker threads according to cores, worker threads take resource. X64 has larger amounts so a 4 way quad core with HT may not be a very sensible idea as your next box. The o/s also takes resource based upon cores.

Windows services

If you don’t need the services stop them.

Anti virus

Keep it away from all SQL Server files.

Paging

There shouldn’t be any.

Monitoring tools

Ask if the slew of server and monitoring tools actually add value. From a prod server there are 22 processes running from the corporate monitoring solution taking 230Mb of memory. The av has 7 processes consuming 87mb and the server hardware manufacturer has 4 processes taking 28mb – on a x32 box that’s 17% of total memory.

Power Saving

Make sure this isn’t on. I’ve found NICs on Servers with this set.