IRL #3 – Slow Server
Problem:
I was recently approached by a firm outside the US for some help with his SQL Server which was “running slow.”
Background:
They don’t have a DBA, and I didn’t support international customers at the time so I told him I would donate 30 minutes of my time out of pure curiosity.
Action Taken:
I sent my HealthCheck tool along with instructions to run it once the server has been up at least 7 days.
Results:
After reviewing the gathered info for 30 minutes, these are the results I sent back as recommendations to look into:
- Backups and Data on the same drive
- Other databases never backed up
- No CheckDB since 2011, if ever
- Max Server Memory: 24,000 MB of 32GB installed
- Memory used –3.4GB
- Windows Server 2008
- Auto-Shrink enabled on prod databases
- No alerts when bad things happen
- No Operators
- SQL Agent offline
- Balanced Power Plan
- Priority Boost enabled
- TempDB on C drive, only 1 data file
- 10 years of backup history
- PLE – 292 seconds
- 32-bit SQL Server 2014 (unsupported build)
- I didn’t even get back any index information, as those queries timed out.
Solution:
- Immediate:
- Upgrade to 64-bit O/S (assuming 32 bit here) and SQL Server
- Patch to supported build
- Turn off Priority boost
- Set up proper alerts and maintenance
- High performance power plan
- Next
- Address everything else
Lessons:
Start at the outside and work your way in. The SQL version can be the biggest issue. Start at Hardware and O/S, then Instance settings, then indexing, then queries (in most cases).
All of this took less than 30 minutes to find. I can do similar (but much more thorough) work for you.
Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/
Thanks for reading!
