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. |