DallasDBAs.com

SQL Server Database Consulting

Kevin3NF

T-SQL Tuesday #118: Fantasy SQL Feature

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kevin Chant (b|t), SQL dude and fellow cyclist!  Kevin wants to know what our “Fantasy SQL Feature” is.

I asked my LinkedIn connections a very similar question in my “Question of the Week” there.  Hit the link to see a lot of different responses.

One of the recurring themes and possibly the most common was:

Load Balanced Writes.  Unless I’ve completely missed something, there is nothing in SQL Server natively that will allow you to write to different copies of the database (leaving out Merge and Bi-Directional Transactional Replication, because they suck and don’t scale).

Don’t get me wrong…I love read-only replicas in an Availability Group, replication for reporting, or even delayed Log Shipping.  They are wonderful for taking the read traffic off an OLTP box, but if you are dying under heavy load in a very optimized setup…oh well.

Bonus Fantasy: Give me a button in the Log Shipping Setup GUI (Database Properties>>Log Shipping) that just says “Re-initialize”.  Sometimes LS just falls apart and its easier to tear it down and start over.  Even easier is to just back up the db, restore over the Secondary db and go.  But, for the small business using LS for poor-man’s DR and no DBA on staff…give me a button.  Ideally that button calls sp_ReInit_Log_Shipping @DBName = ‘MyDB’.  But someone will have to write that first.  I am not that someone.  This may already be in the DBATools.io set of toys, but I haven’t looked, and again…small customers need a button in a GUI.

Thanks for reading!

Kevin3NF

IRL #5: Massive T-Log File

by Leave a Comment

Problem:

I had a customer call in this week after struggling to resolve an issue for a couple of days,  Their Transaction Log file had grown to 400+GB and was filling the drive to the point they had to keep adding space to the drive to keep operations online.  This was for a core internal system.  There are no staff DBAs here, but plenty of very sharp sysadmins, developers, network folks, etc.

Note: I have written about the most common cause of this issue here, but this was not the resolution in this case. The embedded video is far and away number one in hits of all my SQL videos, showing just how common log file growth issues are.

Background:

  • Current SQL Server version
  • Data file around 90GB
  • Log backups enabled, occurring and verified (250MB or so every 30 minutes)
  • Replication not in play.

Investigation:

  1. Run DBCC OpenTran to see if some process was left hanging
  2. Check master.sys.databases.Log_reuse_wait_Desc for the database in question. See the “Wrapping Up” section for a proper query.
  3. Review Disk Usage report for the database – verify if the log file is full or just big and empty

Findings:

  1. Found an open transaction, sitting there since 2.5 days ago.
  2. sys.databases showed ‘Active_Transaction’ for our database, confirming Finding 1. Other possible values for log_reuse_wait_desc here
  3. Disk usage report showed the T-log was 99% used

Actions Taken:

  1. Received permission from application team to kill the SPID identified in DBCC Opentran
  2. Sys.Databases description changed to Log_Backup as expected
  3. Disk usage report now shows 99% unused.
  4. Fairly lengthy cycle of running Checkpoint, Log Backups and Shrink File attempts got us back to a 2GB .LDF file. The log file Autogrow was 10MB….we had 32K+ VLFs once the root problem was resolved by killing the spid
  5. Changed the Autogrow to 512MB in case this happens again to reduce the VLF count

Results:

  • Application performance immediately improved
  • Reclaimed 400GB of disk space
  • Educated my client on the basics of log file maintenance, how they are written to, etc.
  • Everyone slept well that night – no on call wakeups
  • Total time: 1 hour

Conclusion:

  • Most of the time this issue comes from a lack of log backups…but not always.  Be ready with the tools for deeper research
  • I was fully prepared to have them install sp_whoisactive if necessary for deeper digging
  • It pays to have a DBA in your Pocket if you are not going to (or don’t need to) staff one

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

Thanks for reading!

Kevin3NF

Free SQL Server DBA training – Dallas 2019

by Leave a Comment

Save the Date!

September 18, 2019 – Dallas, TX

8am – 5pm, lunch provided (probably pizza or Jason’s Deli)

Thanks to the awesome folks at Matrix Resources for donating their training room, we are able to offer a one-day DBA Fundamentals training class at NO charge!

This class is designed to be an overview of SQL Server for non-DBAs.  System Admins, developers, Oracle DBAs, Managers and anyone else that wants or needs to know more about how SQL Server works, We have presented this day of training at multiple SQL Saturday events as a pre-conference session.

Previous attendees said:

  • “Very Good excitement & interest in topic”
  • “Very engaging, knowledgeable, friendly.  Good session”
  • “Very informative and gave real life experiences.”
  • “I enjoyed the broad explanations helpful to beginners as well as those who are already DBA’s.”
  • “Kevin/Instructor was very knowledgeable and the information shared in the presentation was clear; presented well. I like the fact that it included to bases for those that were new to SQL”
  • “Perfectly fits the entry level need for understanding SQL Server.”
  • “Easy to understand.”
  • “First 7 or 8 hour presentation that I have ever been to where I did not drift off from the presentation mentally. This presentation kept me 100% engaged every minute.”

Check out this promo video to get an idea of what we will be learning:

Or, the non-video version:

I will teach you SQL Server fundamentals so that you will have confidence in:

• What makes up a SQL Server

• How a database is structured

• How to install and what to look out for

• Finding what you need in Management Studio

• SQL Security Basics

• How to create and manage backups

• How to create a database

• Picking a Disaster Recovery option for your environment

* topics may change on the fly depending on what the class wants

These are taught from a foundational standpoint, in simple to understand terms so that you can build on them with confidence. My teaching is heavy on interaction, with examples from years of experience/mistakes. There will be a mix of slides and demos. I’ll skip many advanced features that are out of scope of a beginner class, as I don’t want your head to explode! Join me for the day and you will walk out with a strong grasp of SQL Admin basics!

A word of caution – if you are already a SQL Server DBA with even a year of experience, you may be very bored in this class…

A special thank you to Pam Gates for helping reserve this training room at no charge to Dallas DBAs, so that we can offer this class for free!

Sign up here! There are only 24 spots available, due to the size of the TV I will be projecting onto.  I might open up more once I go in person and see if the farthest seats can see the screen clearly.

Thanks, and we look forward to meeting you!

Kevin3NF and the Dallas DBAs team

 

 

 

 

IRL #4: Instance Tuning

by Leave a Comment

IRL #4 – tuning a server instance in very small increments

Problem:

Main production server is behaving “sluggishly” and additional load makes it worse.  Additionally, deadlocks are a problem, at 880 per day on average.  Client wants to implement changes in small, tightly controlled steps.  Dev, QA, then Prod.

Background:

  • SQL Server 2012 (RTM) Enterprise
  • 8 cores, 58 GB RAM (32 allocated to SQL Server)
  • 1 TempDB file (default setting)
  • Cost Threshold for Parallelism – 5 (default setting)
  • CXPACKET waits – 57.2%
  • LATCH_EX waits – 37.0%

Investigation:

  • Multiple queries that run thousands of times per hour or day have a cost at or near 5.  Many in the 15-20 range.
  • Write stalls on tempdb data file avg 87ms, resides on same drive as primary database.  4.3M writes/day avg.

Recommended Steps:

  • Upgrade to current SP and CU
  • Move tempdb to dedicated drive
  • Increase Cost Threshold for Parallelism to 25

Results:

Query Performance (click to enlarge):

TempDB:

  • Before: avg Read stalls 2ms, avg Write Stalls 87ms
  • After: avg Read stalls 1ms, avg Write Stalls 275ms
    • Investigating drive config (HDD/SSD, RAID level)

Other:

  • Deadlocks dropped from 880/day to 380/day – 56.8% improvement without touching any code
  • CXPACKET waits dropped to 45.5% – 20% improvement
  • LATCH_EX waits dropped to 24.1% – 35% improvement

Conclusions:

  • Our methodology of working from Instance to Database to Index to Query continues to prove successful.
  • Verify all drive info before moving files around (tempdb, log files, etc.)

 

Other posts in the IRL – In Real Life series:

https://dallasdbas.com/category/sql/irl/

Thanks for reading!

Kevin3NF

SQL Saturday Austin 2019

by Leave a Comment

Saturday! Saturday! Saturday!

 

Come to SQL Saturday in Austin, TX on July 13 for a full day of FREE SQL Server/Data Platform training!

I will be delivering one of my favorite sessions: “Your SQL Servers are Mi$ConFiguReDed” currently scheduled for 1:15PM.

Nope…I’m not at all worried that you might go to Joe Celko’s session instead 🙂

I will also be at the Dallas DBAs GOLD sponsor booth/table all day to talk to you about your SQL Server DBA needs.  Or family.  Maybe sportsball…you decide.  We’re kind of chill around here…

There will be a SQL Server Saturday Super Summer Special Savings offer…make sure to ask! Spoiler: its a discount on our Pocket DBA™ service.

Thanks for reading!

Kevin3NF

Video: SQL Server HA/DR Overview

by Leave a Comment

This is meant to be a high-level overview, not an exhaustive setup guide.  I skip over some relevant bits so the main points don’t get buried.

In my DBA Fundamentals pre-conference, we spend a good hour going over the various High Availability and Disaster Recovery options available in SQL Server.  From simple Backup/Restore up to Distributed Availability Groups.

This video is a more succinct version of that conversation.   Enjoy!

If you have questions or comments, please add them in the YouTube comments, so I’ll see them more quickly.

Thanks for reading!

Kevin3NF

Home Blog About Privacy Policy