DallasDBAs.com

SQL Server Database Consulting

SQL

The Cost of Not Having a DBA

by Leave a Comment

Does skipping a DBA save money? Wait until your system grinds to a halt, your backups fail, or your cloud bill skyrockets.

The “DBA? We Don’t Need No Stinkin’ DBA” Mindset

Common Excuses That Lead to Expensive Problems:

  • “Our system runs fine,”
  • “The cloud takes care of it”
  • “Our sysadmin is really smart”
  • “DBAs are expensive”

 

SQL Server is an Enterprise level database product with so many facets to it you could make 5 different careers developing and supporting it (DBA, Developer, BI Stack, Analyst and more). Terabytes of data, thousands of transactions per second.

I have seen repeated instances of someone not fully trained as a DBA reaching out to Google (faster than vendor support) for answers only to find a 15-year-old blog post and start throwing ancient answers at the server. More than once, a client has reached out to me while on hold with the vendor!

The Hidden Costs of Hiring a Junior DBA

Junior DBAs are generally wonderful people. I don’t bash people, pretty much ever. Almost all of them come from other parts of the overall IT ecosystem. The strongest DBA skills come from a LOT of study and a LOT of practice. You don’t get that in 2 years.

Your shiny new DBA (if you have one) might not realize the nuances of the following critical items:

  • Index design at the table level – too many, not enough, duplicates, etc.
  • Query tuning – When is a cursor fine, and when is it bad? What’s killing tempdb? What the heck is RBAR?
  • Inefficient server level configurations. As I typed this, I found 2 major systems at a new client with some horrible default settings that are causing them very high CPU utilization. They are paying monthly for Enterprise licenses they shouldn’t need!
  • Security – Poorly managed permissions = open door for ransomware you may never fully recover from.
  • A Junior DBA may not be test restoring your databases. You MUST know that your backups are valid if you want to meet management expectations.
  • I did this early in my SQL career – ASK FOR HELP sooner rather than later! Junior “anything” people will frequently try too hard to figure something out on their own before seeking assistance.

 

How Not Having a DBA Hurts Your Business

  • Longer downtimes – lets face reality – having a seasoned veteran on speed-dial or in the office means a faster response and resolution. Opening a ticket with your vendor or hunting for a consultant available “now” is difficult, at best.
  • Slow performance leads to lost productivity, angry employees, and customers buying elsewhere. When they STOP complaining, it has gone way too far.
  • Compliance failures if security and audits aren’t handled properly can be costly in fines or remediation expenses.
  • Expensive cloud bills due to inefficient configurations. The cloud isn’t free, or even cheap. Don’t take that nonsense from the cloud vendors. It’s far worse if you are paying for double the hardware of what you really need. Having access to DBA skills can help you right-size!

 

What’s the Right Balance?

A full-time mid to senior DBA makes a lot of sense when there are multiple SQL Servers (25+), various projects happening, tight RPO/RTO requirements, etc.

I ran a poll on LinkedIn asking where the line is drawn on how many SQL Servers a 5-7 year DBA can support:

With a small number of servers, a full-time Sr. DBA is probably overkill. They are going to get bored. I spent a year baby-sitting 4 servers for one of the Big 4 accounting firms. I worked 30 minutes a day and drank a lot of coffee. Until I couldn’t take it anymore and started Dallas DBAs.

With a limited footprint, you are probably better off outsourcing. You might look for a Senior DBA that freelances on the side (availability might be an issue), or a firm that offers a mix of maintenance and off-hours. Dallas DBAs is the best in the world of course (😊):

The Bottom Line: What Should You Do Next?

Get a SQL Server Health Check done. Periodically.
Look for signs of database neglect: Slow queries, long downtimes, too many people with sysadmin rights.
Don’t wait until you’re in emergency mode! Get ahead of the chaos today.

Thanks for reading!

Kevin

My Newsletter (full version)

New Pocket DBA® clients get the first month FREE!

Join my ‘Accidental SQL DBA‘ group here

Get a Free SQL Server Health Check!

SQL Server Source Control on a $0 Budget

by Leave a Comment

The Source Control Dilemma

Every DBA has been there. Trying to keep track of database schema changes while developers have their fancy Git repositories and version control workflows. Meanwhile, database changes are often handled through a mix of manual scripts, backups, and maybe a SharePoint folder full of “final_final_v2.sql” files.

Did you know that SQL Server already has a built-in tool that can help you track your database schema changes, without spending a dime on third-party tools? Enter DBCC CloneDatabase, the secret weapon for DBAs who want version control without the price tag.

  • What it does: Creates a schema-only copy of a database.
  • Why it’s great for source control: Captures object definitions, indexes, statistics, etc. all without data.
  • No third-party tools, no extra licensing costs.

How to Use DBCC CloneDatabase

  • Step 1: Run DBCC CloneDatabase (SourceDB, CloneDB)
  • Step 2: Script out the cloned database.
  • Step 3: Commit the script to Git (or another VCS).
  • Step 4: Or, skip steps 2 and 3, and let the clone sit there. But put that db in read-only.

Benefits Over Other Methods

  • No need for expensive 3rd party software licensing.
  • Fast, lightweight, and built into SQL Server.
  • Keeps a clean history of schema changes, assuming you keep the script and/or backups elsewhere

Bonus Tips

  • Automate the process: Set up a job to create clones regularly and back them up
  • Use in disaster recovery scenarios to validate schema integrity. Compare the clone to the prod database using SQL Server Data tools.

The Bottom Line: SQL Source Control Without the Price Tag

DBAs can manage versions like developers, without breaking the budget. Simple, effective, and already included in SQL Server.

Look how easy this is:

Thanks for reading!

–Kevin

Get a Free Health Check for your most important SQL Server today from Dallas DBAs.

Our SQL Server is Slow! What Do I Do First?

by Leave a Comment

Don’t Panic!

It’s a vague but common complaint, frequently with no additional details. Before blindly restarting SQL Server, let’s walk through quick checks that don’t require deep DBA skills.

Step 1: Is the Server Itself Struggling?

Before blaming SQL, check system-level resources

  • Check:
    • Is CPU pinned at 100%? SQL Server could be a victim of something else on the box
      • Task Manager is your friend for CPU…less so for memory
    • Is SQL Server using 100% of the memory, and starving windows? It should be capped to maybe 90%
    • Are disk latencies/usage high? Use Resource Monitor to check this
  • Quick Fixes:
    • If CPU is the problem, find the process consuming it. SQL Server? Another app?
    • If memory is an issue, see if SQL is configured to take everything (default setting is 2 PB).
    • Low disk space? It won’t typically cause slow performance but it can cause errors.

Step 2: What Queries Are Running Right Now?

  • Use sp_whoisactive. This free tool has helped solve thousands of “slow” complaints over many years
    • Look for:
      • Long-running queries
      • Blocked Processes
      • High CPU usage queries
      • Locking issues
  • SQL Server has a bunch of reports at the instance level, as well as a Performance Dashboard:

  • Quick Fixes:
    • If a query has been running for hours and shouldn’t be, investigate before killing it.
    • If blocking is an issue, find the lead blocker. It’s frequently the real problem. There are “lead blocker” scripts all over the internet
      • I don’t recall where I got this one, but it works: 
        • SELECT
    spid
    ,sp.STATUS
    ,loginame   = SUBSTRING(loginame, 1, 12)
    ,hostname   = SUBSTRING(hostname, 1, 12)
    ,blk        = CONVERT(CHAR(3), blocked)
    ,open_tran
    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10)
    ,cmd
    ,waittype
    ,waittime
    ,last_batch
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
    ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

           

Step 3: Are There Index Problems?

  • SQL Server needs good indexing. Missing or fragmented indexes = bad performance.
    • Check for missing indexes:
      • SELECT * FROM sys.dm_db_missing_index_details
    • Check for fragmentation:
      • SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’)
        • If you are on SSDs, this is very unlikely to be the issue.
  • Quick Fixes:
    • If missing indexes show up, don’t blindly create them—validate first.
    • Rebuilding/reorganizing indexes might help, but don’t do it blindly on huge tables.

Step 4: Is TempDB a Bottleneck?

  • If TempDB is under stress, everything suffers.
  • Check for open/hung transactions:
  • DBCC OPENTRAN
    • Long-running transactions can fill up tempdb and transaction log files.
  • Quick Fixes:
    • Verify you have proper number of TempDB data files (this is a specific fix for allocation performance issues).
    • Restarting SQL clears TempDB, but that’s a temporary fix.

Step 5: Query Store

  • If Query Store is enabled, use it to find consistently bad queries.
  • Look for:
    • High resource-consuming queries
    • Execution plans that suddenly changed
  • Quick Fix:
    • For unstable plans, forcing a known good plan might be a short-term fix.

The Bottom Line: Don’t Panic! Measure First

  • Gather stats before you restart – outside of Query Store, SQL Server logs almost nothing to help you troubleshoot.
  • Most slowdowns have a root cause rather than “just SQL being slow.”
  • Use these steps to find where the problem is before making changes.
  • If these checks don’t point to a clear fix? That’s when you bring in a DBA.

SQL Server Performance – What IT Leaders Need to Know

by Leave a Comment

IT leaders have a lot on their plates! Budgets, staffing, security, uptime, and keeping everything running smoothly.

When SQL Server performance becomes an issue, a common reaction is often to throw money (or Google) at the problem: more CPUs, bigger servers, or Enterprise licensing. But before you go visit the CFO, let’s review some things that really impact SQL Server performance.

More CPUs Might Not Be the Solution

Adding more CPUs sounds like an easy fix, but SQL Server performance issues are often tied to blocking, inefficient indexing, or poor query design rather than CPU bottlenecks. SQL Server licensing costs are per core, so scaling up can drain your budget without improving performance. A SQL Server health check can pinpoint the real bottlenecks before you commit to more hardware. Your DBA team (staff or outside contractors) should know how to quickly identify what is causing the issue as it happens and over time. A CPU bump in a cloud environment as a stop-gap is a valid step to consider, but let the DBA gather the SQL stats first. Restarts wipe out most of the performance meta-data.

Enterprise Licenses Might Not Be the Solution

Enterprise Edition unlocks powerful features and more CPU/memory capacity but if your server is struggling, the root cause may not be the edition. More often than not it’s the workload or configuration. Before upgrading, assess indexing strategies, query optimization, and memory settings. Standard Edition can handle a lot if configured properly, and sometimes the right tuning eliminates the need for an expensive upgrade. $7500 per Enterprise core is a costly guess.

Monitoring SQL Server Is Different from Monitoring Other Servers

Traditional server monitoring focuses on CPU, RAM, and disk usage, but SQL Server needs specialized monitoring. Wait stats, deadlocks, blocking, missing indexes, etc. are all potential indicators of SQL Server health. General-purpose server monitoring tools miss some of these details, leading to wasted troubleshooting time. SQL-specific monitoring is critical. Most non-SQL Server folks don’t realize that 90%+ memory utilization is common and expected for a SQL Server. 100% by the SQL Server process is probably a configuration item.

RPO and RTO Goals Impact Performance

Your Recovery Point Objective (RPO) and Recovery Time Objective (RTO) dictate how quickly and accurately you can restore your data. Frequent transaction log backups improve RPO but can add overhead and can reduce Virtual Log File count. A tight RTO might require high-availability solutions like Always-On Availability Groups, which introduce additional performance considerations. Performance and recoverability must be balanced, not treated as separate issues.

Virtualization Adds Complexity

SQL Server generally runs well in a virtualized environment, but it requires tuning. Overcommitted CPU resources, shared storage contention, and improper memory allocation are common performance killers. Your DBA (or consultant) should be involved in virtualization decisions to avoid costly mistakes. Does your virtualization layer offer multiple paths to the storage (virtual controllers)? Are you taking advantage of that?

Indexing Strategies Matter More Than You Think

Poor indexing is one of the biggest performance killers. Without the right indexes, SQL Server scans entire tables instead of seeking just the rows it needs—slowing everything down. Worse, adding too many indexes can cause excessive writes and degrade performance. Finding the right balance is key. Index tuning is both art and science, and should be reviewed periodically as workload changes.

Maintenance Plans Aren’t Just a Checkbox

SQL Server needs regular maintenance: index rebuilds (it depends), statistics updates (it does not depend), and proper backup strategies. Many IT teams assume that setting up a default maintenance plan is enough. It’s not. A neglected database will slow down over time, impacting performance in ways that hardware upgrades won’t fix.

The Bottom Line

SQL Server performance resolution isn’t just about throwing more resources at the problem. Tt’s about understanding how SQL Server works. If your team doesn’t have a dedicated DBA, getting an expert to assess your setup can save you a lot of time, money, and frustration.

Get a free Health Check for your most important SQL Server today from Dallas DBAs.

Mistakes IT Shops Without a DBA Make

by Leave a Comment

We get it.

There’s no budget for a DBA on staff, or even a contract support firm like Dallas DBAs.

You are not alone! There are thousands of firms running a vendor-supplied or home-grown application with SQL Server as the back end. The vendor may be the only support option you have. Many of those vendors don’t have a DBA either!

So, you throw hardware and Google at it.

We get it.

If this is you, please check out this list of things you need to be aware of.

Skipping Backups:

SQL Server is not automatically backing up your databases for you. You must tell it this most basic concept. Both system databases (master, model, msdb) and user databases. You can use the built-in Maintenance Plans (a little cumbersome, but they work), a 3rd party product like Red Gate’s SQL Backup Pro or Idera’s SQL Safe Backup. There are also free “community scripts” such as Ola Hallengren’s Maintenance Solution.

I’ll preach this until I retire and beyond…part of a backup plan is to test the resulting file to make sure you can restore it! Use Test-DbaLastBackup  from dbatools.io to do this. It’s free.

Corruption Checking:

SQL Server is only doing the lightest level of corruption checking, and that is only if your databases and backups are set to Checksum:

Database Options:

 

 

 

SQL Server level, Database Settings page:

 

 

 

 

 

 

IF these are enabled, SQL Server might throw an error into the SQL Server Errorlog and maybe even into msdb.dbo.suspect_pages

You will want to enable some alerts, set up Database Mail, and send emails to someone on your team to deal with this. Corruption won’t fix itself. Many 3rd party monitoring tools will monitor this for you.

Index and Stats Maintenance

Indexes and Statistics are separate items but tied closely together.

Index fragmentation is not as big of a problem with the advent of solid-state drives (SSD), but it can still be an issue.  Non-clustered indexes store their data in 8K pages, which must be pulled into memory just like data pages. The more fragmentation, the more pages in memory. If you are running a low memory system (relative to data size), you may consider more frequent defragmentation of your busiest tables.

Statistics are generated on indexes and unindexed columns that are part of a query. These stats are critical to getting an optimized query plan from the system. Updating them gives you the best chance of queries running efficiently, along with query code, proper hardware layout, etc.

My default setup for most production servers is to defragment indexes weekly (at most), and update stats nightly.

My defrag parameters: 0-30% fragmentation, ignore. 30-50%, reorganize. 50% or higher, rebuild. Minimum number of pages, 5000.

I use the default algorithm for stats updates unless I have evidence that I need a higher sample size.

How to do these? Again – 3rd party tools, built-in maintenance plans, Ola Hallengren scripts or (yuck) re-invent the wheel and roll your own T-SQL code.

 

The Bottom Line:

If you don’t do any maintenance on your SQL Server, you are risking data loss and performance issues. And you’ll probably be calling me with a 2TB log file.

Want to dig deeper? Check out my Getting Started with SQL Server Maintenance course on Pluralsight. It’s retired, but still relevant. You’ll need a subscription.

SQL Server Time Bombs

by Leave a Comment

Common Reasons for Emergency SQL calls

If you are a production DBA (or Accidental prod DBA) you’ve gotten that frantic call in the middle of the night. Or maybe during little Suzy’s soccer game? Something broke, alerts are firing, nobody can order your widgets and the help desk tech has no idea where to start.

I’ve been on call since 2006. These are the most common things I’ve been called for at the worst times:

Tempdb growth

When tempdb grows out of control, it can easily fill a drive and stop the SQL Server in its tracks. Every database (potentially) uses the same tempdb to do its sorting, aggregating, maintenance and spilling of large transactions. That is the purpose of it.

Nothing in the system will stop a really bad query from filling up the tempdb data files to consume the entire drive. Once it fails the system should be ok/online, but you still have a full drive firing alerts at the sysadmins.

How to prevent: cap the tempdb files to maximum size that will keep alerts from firing for disk space. Also, set up alerts for errors 1105 (data file is full) and 9002 (log file is full). These go to the DBA.

You cannot stop bad queries if you allow users to run Ad Hoc, but you can limit the blast radius.

Disk Space

I don’t have enough toes to count the number of times I’ve been hit with an on-call because disk space was causing issues in SQL Server. Most of the time I search for all files, sort by size descending and look at the biggest files. VERY frequently someone dumped a backup file there, or created a new DB on the wrong drive.

How to prevent: Log the output of exec master..xp_fixeddrives and keep an eye on it. Set permissions for just admins and SQL Service accounts to limit Joe’s ability to break things.

Backup failures

Tend to these quickly, as your RPO might be in jeopardy if backups are missing. Easily 80% of the backup failures I see are related to disk space (see above). 19% are network related. 1% is everything else.

How to Prevent: see Disk Space above. For T-log backups I set the job step to retry once before failing. Weird network blips shouldn’t cause someone to wake me up.

Patching
This is probably the hardest to deal with, but it comes up often. If you are not mostly current on Cumulative Updates and Security Patches, expect this call. Especially during a monthly Windows patch cycle – which doesn’t include SQL Server patches.

How to Prevent: see Disk Space above. For T-log backups I set the job step to retry once before failing. Weird network blips shouldn’t cause someone to wake me up.

The Bottom Line:
An ounce of prevention prevents your phone from ringing. Pay close attention to the setup and maintenance of your SQL Server to stop these issues before they start!

Home Blog About Privacy Policy