Stay Ahead of Performance Issues

TL; DR: REGULAR maintenance.

I frequently compare SQL Servers to cars. Both are expensive, and both come with a requirement to do regular maintenance on them. Or they’ll crash. Or at least slow down dramatically.

At least cars are decent enough to have service guidelines in the owner’s manual for recommended maintenance intervals for oil changes, transmission flush, etc.

You may not know how to do these things, but at least you know when to do them. Hopefully you will even know where to take the car when the time comes!

If SQL Server DID come with a manual (yes, I know Books Online exists), here are some of the items you would see (If I wrote it):

• Backup your databases (Full): Daily or weekly, depending on size and storage availability

• Backup your databases (Differential): 6 days a week if the Full backup is happening on the other day

• Transaction Log backups: every 5 minutes. This varies based on your RPO/RTO requirements

• Update Statistics: Every day. (What are statistics?)

• Reorganize or Rebuild your indexes: As needed if your drives are SSD and you have sufficient RAM allocated to SQL Server

• Check for Corruption: As often as you can. Corruption usually means data loss. Catch it early.

• Check Database and Instance level settings: At least weekly. You’re looking for unexpected changes here

• Look for missing and unused indexes: workloads change, schemas change, queries change…indexes may need to change to accommodate.

• Query performance: check the Query Store for your “worst” or Regressed queries. Version 2016 and later.

• Disk IO stalls: keep a record of these over time, especially for your critical databases and tempdb. If things are getting worse, find out why.

• Memory usage: look for excessive memory grants, tempdb spills, etc. When things change, start digging.

• Errorlogs: Review these every day. If you see something new that you cannot explain, start digging.

• Deadlocks: Look for them…unless you have retry logic, something failed in a deadlock event.

• Waits: Is your SQL Server sitting around twiddling its thumbs? Or is it waiting for resources?

There’s more! But I’ll be nice and stop here.

If you need help understanding these items, or why I’ve put them on the list, please ask. Don’t just Google them…some of the articles you will find are horrendously out of date. Best practices in the “spinning disk/HDD/fidget spinner” days may not apply at all to SSD storage.

Of course, this is the kind of service I’m happy to do for you if you are already overwhelmed.

The Bottom Line:

Regular maintenance can keep you ahead of the game, rather than having to wildly throw CPU and RAM at a SQL Server as a band-aid.

Tell me in the comments the things YOU look at to keep your SQL Server performing at its best.

Brent Ozar makes some of the best training for SQL Server available on the internet.

A LOT of the above information is covered.

Every November, he runs a Black Friday Sale with DEEP discounts. Get your budget approvals now!

If you know you need to do the maintenance items above but don’t have the skill or the budget, check out Brent’s Constant Care offering.

(These are affiliate links. I will receive payment if you buy using them)

My Recent LinkedIn Posts

Unfortunate Typo of the day

I've been asked if we could do some 2-hour sessions...

Hello LinkedIn "Lurkers"!

Interesting Stuff I Read This Week

Walmart will start delivering prescriptions as CVS, Walgreens struggle (cnbc.com)

Boeing Company - Boeing Reports Third Quarter Results – Is there any hope for Boeing?

SQL tidBITs:

Tracking down deadlocks? Use sp_blitzlock from the Ozar First Responder Kit.

Or, this code from Microsoft:

-- Find Deadlock events captured by the system_health session ring buffer:
-- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
       xdr.query('.') AS [Event_Data]
FROM (SELECT CAST ([target_data] AS XML) AS Target_Data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
      CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

Share with your Oracle DBA friends…they’ve been stuck in CLI far too long!