DallasDBAs.com

SQL Server Database Consulting

SQL

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!

Top 10 Careers in Data

by Leave a Comment

Would you re-order these?

Machine Learning Engineer $$$$$

  • Develop and deploy AI models

  • Optimize machine learning algorithms for efficiency

  • Work with big data frameworks to process large datasets

Data Scientist $$$$$

  • Build predictive models and machine learning algorithms

  • Analyze large datasets to identify trends and insights

  • Communicate findings to stakeholders and guide business decisions

Data Engineer $$$$

  • Design, build, and maintain data pipelines

  • Optimize databases and data warehouses for performance

  • Ensure data integrity, security, and accessibility

Data Architect $$$$

  • Design enterprise data infrastructure

  • Ensure scalability and security of data systems

  • Define best practices for data management

Database Developer $$$$

  • Design, develop, and optimize database structures (tables, indexes, constraints)

  • Write and tune SQL queries, stored procedures, and functions

  • Integrate databases with applications and ETL processes

Database Administrator (DBA) $$$

  • Manage, maintain, and secure databases

  • Optimize performance and ensure high availability

  • Handle backups, disaster recovery, and troubleshooting

Business Intelligence (BI) Developer $$$

  • Design and maintain BI tools and dashboards

  • Integrate data from multiple sources for reporting

  • Optimize data queries for performance

Data Analyst $$$

  • Interpret and visualize data for reporting

  • Query and clean datasets for analysis

  • Provide business insights based on data trends

Data Governance Specialist $$$

  • Ensure data compliance with regulations (GDPR, HIPAA, etc.)

  • Define and enforce data policies and standards

  • Monitor and improve data quality

ETL Developer $$$

  • Build and manage Extract, Transform, Load (ETL) pipelines

  • Optimize data movement between systems

  • Ensure data quality in integration processes

 Check out Brent Ozar’s 2025 Salary Survey Results here

 The Bottom Line:

The world revolves around data, from your banking info to crypto to your Fortnite stats and Spotify playlists. There will always be jobs available to wrangle that data.

Home Blog About Privacy Policy