DallasDBAs.com

SQL Server Database Consulting

SQL

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.

Degrees and Trade Schools

by Leave a Comment

Can we normalize a couple of things?

1 – Trade Schools. Back in the 80’s trade classes in high school and post high school were frowned upon, or looked down upon. Is that still a thing? It shouldn’t be.

I’ve had to rely on the expert skills of roofers, a plumber, HVAC experts, an electrician and auto mechanics all in the last 3 months. Every one of them was expert level in the trade as well as customer service.

2 – Not requiring degrees for non-degree level work.

Case in point, DBA work. If you have technical aptitude and a desire to learn, you can be a great DBA. I have three people on my team that came into IT as DBAs. Not the normal route, but its working just fine.

I get that degrees are a resume filter…but you miss out on amazing people that way.

Let me hear your thoughts!

 

–Kevin

3rd Party Applications Have Issues

by Leave a Comment

As a SQL DBA, what do you do when a vendor application has performance problems that are code related?

Server settings don’t generally seem to be an issue.

Queries and vendor code…total hands off. I just point at code and say “There’s a great choice for optimizing in your next update!”

Indexes are the “Sticky Bits” in between client data and vendor code.

Seems like we get 30 indexes, or zero. Neither is usually right.

I get it…how do you tune an app for every possible use case for any client that may install it?

I always have my client get the official rules from the vendor. We don’t want to break an upgrade, or void a support agreement.

In some cases we’ve had to “Don’t Ask, Don’t Tell” our way into a solution with undo/redo scripts for very necessary indexes.

What do you do when tuning a 3rd party app?

 

— Kevin

Set-Based vs Row-Based Database Code

by Leave a Comment

In SQL Server, the terms “set-based” and “row-based” refer to different approaches or styles of writing SQL code to manipulate data. These styles have implications for performance, readability, and the way queries are processed. Let’s explore the differences between set-based and row-based code:

  1. Set-Based Code:
    • Operations on Sets: Set-based code focuses on performing operations on entire sets of data rather than individual rows. This is aligned with the relational nature of databases, where data is stored in tables.
    • Declarative: Set-based code is often more declarative, meaning you specify what you want to achieve without explicitly instructing how to do it. SQL is designed to work with sets of data, and set-based operations leverage this design.
    • Performance: Set-based operations are generally more efficient because they allow the database engine to optimize the query execution plan. The database engine can decide the most efficient way to retrieve and process the data.
  2. Row-Based Code:
    • Operations on Individual Rows: Row-based code, on the other hand, involves operations that are performed on individual rows one at a time. This style of coding is more procedural and might resemble how you would approach data manipulation in a programming language like a procedural language (e.g., C# or Java).
    • Imperative: Row-based code is often more imperative, specifying the step-by-step instructions to achieve a particular result. It may involve using cursors or loops to iterate through individual rows.
    • Performance Concerns: Row-based operations can be less efficient, especially when dealing with large datasets, as they might lead to more I/O operations and additional processing overhead.

Example:

Consider an example where you want to update the prices of all products in a certain category. A set-based approach would involve a single UPDATE statement that modifies all relevant rows at once:

UPDATE Products
SET Price = Price * 1.1
WHERE CategoryID = 2;

In contrast, a row-based approach might involve using a cursor to loop through each row and update it individually, which could be less efficient:

DECLARE @ProductID INT, @Price DECIMAL;

DECLARE ProductCursor CURSOR FOR
SELECT ProductID, Price
FROM Products
WHERE CategoryID = 2;

OPEN ProductCursor;

FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Price = @Price * 1.1;

    UPDATE Products
    SET Price = @Price
    WHERE ProductID = @ProductID;

    FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;
END;

CLOSE ProductCursor;
DEALLOCATE ProductCursor;

In general, it’s recommended to use set-based operations whenever possible in SQL Server as they are usually more efficient and better aligned with the relational database paradigm. However, there may be cases where row-based operations are necessary or more practical, such as when working with procedural code or handling complex business logic that is not easily expressed in a set-based manner.

Thanks for reading!

Kevin

Home Blog About Privacy Policy