IRL #3: SQL Server HealthCheck

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!

Kevin3NF

Leave a Comment

Sign up for our Newsletter

%d bloggers like this: