If you’re like me, the first thing you do when they put you in front of a SQL Server instance you have never worked on before is looking for misconfigurations or simple put: let’s see how they messed up…
I keep running into the following bad practices:
- Auto-Shrink and Shrink jobs
Seriously? Probably the holy grail of bad SQL Server practices.
Read more about it.
- The world wide web service is running.
Why? Give me a reason why you would use the www service on a dedicated SQL Server instance in a production environment?
Yes, there might be a short list of reasons but let’s be honest, how many instances are out there running this service? Indeed…
- Internet access.
Give me one good reason why you need to have Internet access? Do you check your mail on the production SQL Server? You play Angry Birds during lunch?
- RDP usage.
Yes, you know I’m talking about you. Why do you keep using RDP on the SQL Server instances?
As a database administrator, I can’t think of any tasks you won’t be able to execute from a remote machine.
It’s a waste of resources you’re using on a production machine.
- Client tools installed on the server.
Yes go ahead, install SSMS and BIDS on the server so the guys in China can do their development using RDP…
- Service accounts.
Yes, install all instances using the same account so if one breaks down, all services break down.
Use a dedicated domain account with no rights to run the services instead.
- Service accounts with sysadmin privileges.
Why? An ordinary domain account should do it. It’s a security threat.
- Power plan set to balanced.
Sure, let’s save some power running this very important heavily queried instance.
Setting the power plan to high performance actually boosts the performance of the instance.
- Dedicated drives.
It’s improving but I still see a lot of production instances with only one drive.
If it’s up to me, I would even use dedicated drives for indexes.
Use dedicated disks for mdf, ldf and TempDB. Don’t forget to put the local backups on a dedicated drive as well. Taking backups is a write process and you don’t want to query (read) data from the same disk which is writing to a backup.
- Disk space.
I try to maintain a 15% free disk space on all drives.
Disk space is filled up starting at the center of the disk. A longer distance from the center of the disk means a lower speed.
There are plenty more of those but 10 is a nice number.