DallasDBAs.com

SQL Server Database Consulting

Beginner

“SQL Server is Slow” Part 3 of 4

by Leave a Comment

In parts 1 and 2 of this series, we’ve gathered info and done the triage just like anyone in almost any industry does

At this point you’ve:

  • Defined what “slow” means and built a timeline (Part 1).
  • Checked things outside SQL Server like network, storage, and VM noise (Part 2).

Now it’s time to open the hood on SQL Server itself.

Step 1: Check Active Sessions

Run a quick session (sp_whoisactive is a favorite):

  • Who’s running queries right now?
  • What queries have been running the longest? Is that normal?
  • Any blocking chains?
  • Are any queries hogging resources?

At this stage, you’re only identifying potential offenders. Next issue, we’ll dig into queries and indexes more deeply.

 

Step 2: Look at Wait Stats

Wait stats tell you what SQL Server has really been waiting for (everything in SQL Server is waiting for something else):

  • PAGEIOLATCH: slow storage reads.
  • LCK_M_X: blocking/locking.
  • CXPACKET/CXCONSUMER: parallelism info.
  • THREADPOOL: CPU threads
  • RESOURCE_SEMAPHORE: memory
  • ASYNC_NETWORK_IO: Probably more of a client side problem than SQL Side
    • The most comprehensive list of wait types and explanations from my friends at SQL Skills

This isn’t about solving yet – it’s about categorizing where SQL feels the pain.

 

Step 3: Review Agent Jobs & Error Logs

SQL may already be waving red flags:

  • Overlapping or stuck Agent jobs. A long running purge job or index rebuild can cause all sorts of issues during the day.
  • Failed backups or CHECKDB runs. A failed CHECKDB could mean corruption. Read this
  • Errors or memory dumps tied to patching or system instability. Look in the same folder as your ERRORLOG location

 

Step 4: Don’t Forget the “Gotchas”

Other less obvious issues can cause system-wide drag:

  • High VLF count (often from failed or missing log backups).
  • Database compatibility or config changes – check SSMS reports like:
  • Recent patching issues (especially if tied to errors or dump files).

These aren’t everyday culprits, but when they show up, they can cripple performance.

 

Step 5: Compare Against Your Baseline

Today’s “slow” may be tomorrow’s “normal.”

  • Track batch requests/sec, CPU Utilization, wait stats, I/O latency, and log file size/VLF count.
  • Without this baseline, every slowdown feels like a brand-new mystery.

If you don’t already have a baseline, NOW is the time to start, while the server is healthy.

  • Collect I/O stats and wait stats regularly.
  • Run sp_Blitz for a full health snapshot (free tool from Brent Ozar)
  • Capture DMV performance counters (sys.dm_os_performance_counters) on a schedule.

A baseline doesn’t need to be fancy, it just needs to exist so you know what “normal” looks like before things go sideways.

The Bottom Line

Part 3 is about categorizing slowness inside SQL Server: sessions, waits, jobs, error logs, and configuration gotchas. Don’t jump straight into query rewrites yet. You’re still isolating the nature of the slowdown. Having a consistent process for this reduces panic and anxiety.

In Part 4, we’ll cover what to do when the culprit is truly inside SQL Server: look at queries, indexes, and design choices.

 

Thanks for reading!

SQL Server Index Primer

by Leave a Comment

Indexes 101: What, Why, and When?

“What Is an Index?”
I get this question a lot, especially from developers and sysadmins who’ve been handed a SQL Server and told, “Keep it running fast.” No pressure.

At a basic level, an index is a roadmap. Instead of scanning every house on the street to find the one you want, you check a list. Indexes help SQL Server find data faster – less scanning, more targeted seeking. All those pages at the back of a technical book? Those are indexes. The same idea applies to tables.

Now, not all indexes are created equal. Some are used every day. Others are…let’s say, special occasion. Let’s walk through them in order from most to least common (based on my experience).

Clustered Indexes

What it is: Defines how the data is stored on disk. SQL Server physically orders the table based on this. One per table.
Why it matters: Every seek operation relies on this index. If you don’t create one, SQL Server makes a hidden “heap”, which adds time to the query.
When to use: Pretty much always, unless you’re loading massive amounts of data fast and don’t care about retrieval speed such as an ETL/ELT process.
Pro tip: Pick a column (or set) that is narrow, static, and unique (like an identity column). Even better, use a column that is frequently used to retrieve sets of data such as date for “Last month’s sales” types of reports.

Non-Clustered Indexes

What it is: Think of these like side tables (they do add to the database size) with pointers back to the main data. You can have many per table, but too many can be an issue.
Why it matters: Great for covering queries. Especially helpful if they include only a few columns.
When to use: When the same query pattern happens often and doesn’t match the clustered key.
Watch out for: Too many non-clustered indexes = slow inserts and updates. Balance is key.

…Included Columns…

What it is: Bonus data tacked onto a non-clustered index. Doesn’t affect sort order but helps avoid expensive trips to the table to find more data.
Why it matters: Lets you “cover” more queries without bloating the main index definition.
When to use: If the query needs extra columns but they don’t need to be sorted.

Filtered Indexes

What it is: Indexes with WHERE clauses.
Why it matters: Small, fast, and efficient for predictable filters.
When to use: Perfect for things like IsActive = 1 or Status = ‘Pending’.

Columnstore Indexes

What it is: Think column-based storage, not row-based. Optimized for analytics.
Why it matters: Game-changing for reporting queries across millions of rows.
When to use: Data warehouses, not your OLTP system.
Bonus: Clustered columnstore = high compression. Great for archiving.

XML Indexes

What it is: Indexes specifically for querying XML data types.
Why it matters: Without them, XML queries crawl.
When to use: If you’re unlucky enough to be storing structured data as XML blobs.

Spatial Indexes

What it is: Indexes that help with geography and geometry data types.
Why it matters: SQL Server can’t efficiently process spatial queries without these.
When to use: Mapping, geolocation apps, routing engines—very niche but powerful. I’ve only seen a spatial index in production once in my career.

The Bottom Line:

Indexing is both art and science. You want just enough to make reads fly and not too many that writes crawl. Think of it like your car with just the right tire pressure—too little and you drag, too much and you lose control.

— Thanks for reading!

Kevin

 

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.

Server Review Essentials for Accidental and Junior DBAs—Daily Server Checks (Part 1)

by Leave a Comment

Welcome back to the series “Server Review Essentials for Accidental and Junior DBAs.” So far in this series we’ve taken a look at how to set up your work environment, best practices for onboarding new servers and clients, and prepping your servers for daily reviews.

What You’ve Accomplished Thus Far

After all of our work and prep during the last several posts in this series, it’s finally time to get down to the “real” work of a DBA.

Wait, that’s not quite right. While it may seem as if everything covered so far in this series has been pretty basic and production servers have barely been touched, don’t get the idea that the work done so far isn’t of high value. Think about some of the things you’ve already learned about your servers during this preparation process:

  • You’ve gathered all of the necessary credentials needed to log into VPNs and individual servers, verifying the accuracy of those credentials.
  • You’ve set up a stable work environment.
  • You’ve built a starting personal script library that will grow and evolve over time.
  • You’ve collected “biographical” information about every server you’ll be reviewing, so you have a baseline to see how everything stands before you begin reviews.
  • You’ve learned the expected RPO and RTO for all of your databases.
  • You’ve built a Server Inventory List that shows the status and use of every server for which you will be responsible for administrating.
  • You’ve discovered the current patch level of all the servers within your purview, which will give you vital knowledge as you move forward.
  • You’ve learned what maintenance jobs are already active on your servers.
  • You’ve learned what the current backups look like and whether or not you’re currently meeting RPO and RTO.
  • And you’ve created a DBA Tools database on all of your servers so that you’re prepared for regular server reviews.

Why is all of the above so vital? If I’ve learned one thing in my two years of database administration it’s that there seems to be a large number of folks who really have no clear understanding of what’s going on in their server environments. Their approach to preserving their data is more seat of the pants and reactionary than proactive monitoring.

As outside consultants, these are the types of folks who typically end up calling us to say, “my server is on fire!” Many of the issues we see on an “emergency” basis could have been prevented with regular reviews.

A Proactive Approach

At Dallas DBAs, we much prefer a proactive approach to database maintenance and performance. When we take on a new group of servers, the first thing we do is run a health check on the boxes and search for any issues that may need immediate remediation or any server settings that can be adjusted for immediate benefit.

While these topics are not covered in this series, we’re often looking for common issues related to things such as Cost Threshold for Parallelism, Instant File Initialization, MAXDOP, max server memory, the current state of backups, TempDB configuration, and more.

This gives us a launchpad to implement all of the prep work mentioned in this series so far, and better guide the people who are depending upon the data to run their business or otherwise get work done.

By taking a proactive approach, we often know about problems days or hours before our clients do. We can see if backups have failed, ensure index maintenance is occurring regularly, and keep an eye on job failures and errors.

We often prefer to do a quick 15 minute or less check of all servers daily, running a handful of scripts that I’ll begin sharing in the next few posts. These scripts quickly show us the state of the backups, reveal any job failures or errors we need to investigate, and find alerts we may need to be aware of.

This proactive approach also helps us get to know what is expected from the servers. As we report job failures and errors, we may learn that certain issues are common knowledge and are not necessarily causing the end-users any pain.

We learn how our data users feel about deadlocks. They may not care about them if they don’t “feel” them. A job that runs every 10 minutes and is the victim of a deadlock once or twice a day likely isn’t going to be an issue. Sometimes job failures are simply random “network blips” that happen periodically, so all you can do is name a note, shrug your shoulders, and move on.

That’s all for now. Next time we’ll take a deeper dive into looking at investigating your server’s backup history.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.



Server Review Essentials for Accidental and Junior DBAs—Prepping for Server Reviews

by Leave a Comment

Welcome back to our series, Server Review Essentials for Accidental and Junior DBAs, where we’ve been taking an in-depth look at how to get ready to perform regular SQL server reviews for your company or clients. So far, we’ve covered how to get your working environment set up (Part 1 | Part 2) setting up biographical information about your environments, collecting server information, building a maintenance job list, and backup frequency list, as well as providing sample biographies and server reports.

Now it’s time to move forward and put all your preparation to use and perform regular server checks…almost. We’ve got one more round of prep work to do before your environment is completely ready to go.

Verify RPO

Before you go any deeper into this process, now is the time to ensure all of the servers you’ve been entrusted with are meeting the stated Recovery Point Objectives. My advice at this point is to not worry over the Recovery Time Objective until you’ve ensured you’re meeting RPO. Your company or clients won’t care how fast their data can be recovered if they can’t recover the data they actually need.

Use the backup frequency list you created in an earlier step and take the time to look through each server to see if the backup schedule meets the stated RPO in terms of FULL and LOG backup frequency (and DIFFs if relevant). We’re not confirming the existence of the backups yet, we’ll do that in a later step. For now, just make sure the backup jobs are scheduled properly to meet your RPO requirements.

If you find that your backup jobs are not scheduled properly, get with your senior DBA or the relevant person in authority and correct the backup job schedules accordingly.

Create Your Own DBA Tools Database

Though not necessary, I’d suggest creating your own DBA Tools database on each of the servers for which you are responsible. Most companies and clients won’t have any problem adding such a database since it is lightweight, provides tools beneficial to your server health, and creates a sandbox in which those tools can reside separately from the data.

This is a common method for us here at Dallas DBAs. Whenever we’re given the responsibility for administrating a new server or environment, one of the very first things we do is evaluate the current state of the server, implement some best practices, and create a new database like this to install our preferred scripts.

We’re not going to cover the steps to creating a new database for your tools. If you don’t know how to do that, check out the Microsoft Docs to learn how to make a new database. Having said that, we feel it’s always best to put the database in SIMPLE recovery model and make sure the database owner is “sa”. Otherwise, you can pretty much use the defaults in creating your new DBA Tools database.

What scripts do we install and use regularly? In the following posts in this series we’ll showcase some of the scripts we use, but below you’ll find a list of scripts and stored procedures we like to implement on the servers we touch.

Keep in mind the purpose of this post is not to explain how to install or use any of the scripts and tools listed below, only to show you some of the tools we like to use.

Get Ready for Proactivity

Now your servers are primed for regular reviews, which means you will often know about potential issues long before they become catastrophic failures. Having done all of this work doesn’t guarantee your servers will be problem-free, but it does mean your servers will be less likely to suffer common issues and you’ll be better equipped to handle issues as they arise.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.



Server Review Essentials for Accidental and Junior DBAs—Client Onboarding (Part 4)

by Leave a Comment

Let’s take a look at the final few steps of your initial server intake process and get your workflow up and running as you push toward performing regular server reviews. These last few steps are relatively easy and quick to set up.

(Also see Part 1, Part 2, and Part 3 of this series)

Build a Backup Frequency List

Now that you’ve set up your Maintenance Job list, you should be able to see how backups are scheduled for your servers. This is the point where you begin pushing from simply gathering info on your servers towards ensuring your servers are set up correctly.

Depending on your situation and environment, you may not need to create an actual list in a document or spreadsheet. Sometimes simply having a familiarity with what databases are backed up and their frequency is enough, but it may be best to create an actual list in the beginning.

You’ll need to be able to determine what types of backups are happening—FULLs, DIFFs, LOGs—and when those backups occur. Once you’ve collated this information then you’re ready to see if the current backup scheme meets the expected Recovery Point Objective and Recovery Time Objective. If not, then getting your backups whipped into shape should be the first task you assign yourself for your new servers.

I’m personally at the point where I have enough servers spread across different environments that I need to create a new writeup of backup schedules for those environments. There are currently too many environments to keep straight in my head, which means it’s far too easy to miss whether or not a particular server group’s backups are on schedule and up-to-date.

How so? Some of the server groups I check are scheduled to create Full backups daily, while others are only creating these backups weekly. I have some server groups in which there’s a mix of scheduling, and others that have either native SQL Server backups, virtual device “snapshot” backups, or sometimes both. One client in particular has two server groups that I check regularly, one of which is using native SQL Server backups while the second group is primarily using virtual device backups.

So, if you decide to write up an actual list, what do you include? Here’s a not-so-comprehensive list of items you may want to include in your document:

  • Database name
  • Recovery model
  • Backup types (FULL, DIFF, LOG)
  • Backup frequency for each type (weekly, daily, hourly, etc.)
  • Schedule/frequency of each backup type
  • Backup path/device
  • Backup retention period
  • Backup compression yes or no
  • Native SQL Server backup, virtual device backup, or other
  • Maintenance Plan, Ola scripts, or other

The above is simply a sampler of items you may choose to include. Again, depending on your environment and SLAs, your list might look somewhat different.

Example Server Review Report

Now that you’ve gotten all of your server’s baselines gathered you’re ready to start implementing regular checks. How often you perform these server reviews will depend on your employer’s dictates or current SLAs. For me, I currently have several server groups I check daily, one server group that is checked twice per week, and one server group that is only checked once per week.

What’s included in my regular reports? In the coming weeks I’ll be stepping through how I get my reviews done, but for now I’m including a sample report below so you can get a sense of the sorts of items you’ll be looking over as you check your servers.

ABC Server Review 03-22-2021

Servers:

     Unable to connect to ABC-SQL-03. Upon ping "Ping request could not find host ABC-SQL-03.". 

     Recommend applying CU22 for SQL Server 2017 and Security update for SQL Server 2017 CU22: January 12, 2021 [CVE-2021-1636] to ABC-SQL-01. 

Third Party Monitor:

     If there are any third party monitoring products in use—such as Solarwinds DPA or Redgate Monitor—any current alerts get notated here.


Replication:

     ABC2020_Pub5 is reporting Critical performance.

     All other publications are reporting Excellent performance.


Jobs:

     The job 'Write Stuff to the Table' failed at step 'Load Stuff' on ABC-SQL-01 on 03-21-21 at 4:00 AM. This job is scheduled to run hourly and failed previously on 03-17-21. The job has executed successfully since 03-21-21 at 5:00 AM.
          The job was the deadlock victim.

Backups:

     All backups are up to date.

Errorlogs:

     Login failure for user 'ABC\lcage' on ABC-SQL-02 on 03-21-21 at 7:36 AM. Reason: Could not find a login matching the name provided. [CLIENT: 100.10.10.1]

     66 deadlocks on ABC-SQL-02 on 03-21-21 through 03-22-21.

Alerts:

     No alerts to report.

AG Health

     All Availability Groups reporting Healthy.

Other:

Your own reports may look significantly different than what you see above. Obviously, if your servers are not using features such as replication or Availability Groups then there’s no need to include them in your report. Depending on your employer’s or client’s expectations—and your SLAs—your report may be more or less detailed. You may even have some sort of ticketing system in place.

In my case, I send my reports to my senior, Kevin, as an attachment to an email for his review. I’ll note any items I believe are critical and need immediate attention in the body of the email and highlight them in red text. There are certain issues which are appropriate for someone at my level that I will rectify on my own, otherwise Kevin and I will address issues together, or he will contact the necessary personnel who oversee particular aspects of the servers as needed. This is the workflow that works for us, but your practices and procedures may look different.

Diving Deeper

At this point, you may need to go even deeper and to build a full server inventory which is more comprehensive than what I’ve laid out in the last few posts.

This may also be the point where you need to do a full Health Check on your servers and look for any best practices that need to be implemented and note any problems you find that need to be addressed. Create a list of these items and your suggested recommendations for remediation, as well as a potential schedule of when and how it might be best to address these issues.

Stay tuned! In the next group of posts we’ll start an overview of implementing server monitoring and active reviews.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.



Home Blog About Privacy Policy