Return Max or Min Value of a Group of Columns as a Single Column

Recently, I needed a query to identify tables that developers had create as point-in-time backups of tables that were never used again (turns out, there’s quite a lot of them in this database). They are characterized by having _bak or _ appended to the end of them. I wanted to provide a list of the tables to the development team and give them the opportunity to say that any of the tables should not be deleted. I wanted to provide,
read more

AG Worker Thread Blocked by Log Backup

We have an internal monitoring query that checks for system threads that are blocked. Recently, we received an alert email stating that system thread with session ID 44 had been blocked for at least 599 seconds (10 minutes, basically). This was in the middle of the night, and I looked into the issue once the work day began. Our 3rd party monitoring solution collects blocking information, but not for system threads. There was no additional information available for this blocking
read more

Attendee Questions from My #24HoP Presentation

I want to once again thank everyone who attended my 24 Hours of PASS session last week. I especially appreciate the questions that were asked. We did not have time for all questions, so I want to answer all of the questions here. Some of these were answered in the session, but not all. Q1: I see that the tables are sys.query_store_query_text, and sys.query_store_query, and not sys.dm_*, which leads to question that the query store is persistent between instance restarts,
read more

What has 2 Thumbs and Deleted a LUN with the Log File of a Production DB on it?

That’s right, I did it. I was deleting unused LUNs and the focus somehow jumped from the LUNs for the one database onto a LUN for another database. I blame an errant mouse-click. As soon as I deleted the LUN, I realized the name was wrong. The LUN held the log file for a production database. Fortunately for me, the database was in an Availability Group so after recreating the LUN and attaching it, I restored the database from the
read more

What is the max/min size of a decimal data type?

Recently, I wrote a maintenance script to check every table in every database on our servers at work nightly and email a report of identity columns that are approaching the limits of their data type. The minimum and maximum values for most numerical data types are documented in Books online, but for decimal/numeric data types it is not documented and varies based on the values provided for the precision and scale. Most people know that precision is the total number
read more

My 24 Hours of PASS Session Files

I was honored to be selected to join many other great speakers in presenting for the recent 24 Hours of PASS: Evolution of the Data Platform event. My presentation was called New Paradigm for Performance Tuning in SQL Server 2016 and covers two new tools for performance tuning: Query Store and the Plan Compare tool. You can download my slide deck (in pdf form) and demo code from the page for my session on the event site. The session was
read more

Why I'm not attending PASS Summit 2016

I know there has been a lot of drama in the community recently, much of it centered around what people are or are not going to do in conjunction to this year’s SQL PASS Summit. Well this isn’t one of those posts. I promise you that this post is drama free. I felt compelled to share my reasons for not attending to make sure it was clear that in no way is this related to new rules or any of
read more

T-SQL Tuesday #74: Be the Change Round-up

Thanks to everybody that participated in this month’s T-SQL Tuesday. A big thanks to everyone who wrote a participating blog post, and a really huge thanks to everyone who read the posts shared by this month’s participants. If you follow one of the links on this round-up page, I will kindly ask that you leave a comment on a blog post that you read if it teaches you something, gives you a new perspective, or makes you think. A blog
read more

T-SQL Tuesday #74: Ch-ch-changes

Welcome to my contribution for T-SQL Tuesday 74 being hosted by me. So special thanks to me for hosting it. Good job me, I’ll buy me a beer next time I see me to thank me properly. But enough about me, my post is about using Query Store in SQL Server 2016 to identify queries or plans that have changed. Visit the blog party page to take part in this month’s T-SQL Tuesday event or to read other blog posts
read more

T-SQL Tuesday #74 Invitation: Be the Change

Happy New Year and welcome to another T-SQL Tuesday (number 74 to be exact). This time of the year, everyone thinks about making resolutions. Stop doing something or start doing something, etc. You know the drill. Well, I’m not doing that, but this month’s topic is inspired by resolutions. The topic for T-SQL Tuesday #74 is Be the Change. More specifically, data changes. How do you track changing data? How do you do your ETL? How do you clean or
read more