Things that may not be right on your SQL server.

搜集-3S科技来源-网络

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.

--------------------------------------------------------------------------------