Binary to Decimal via STRING_SPLIT

Colin Stasiuk posed an interesting question on Twitter recently:

I started pondering it for a bit and began to wonder if I could use the new for SQL Server 2016 STRING_SPLIT function to convert a binary string to decimal. The thought process was to split the string into rows of CHAR(1) values, along with an in-string character position. Then I could take the "1" values, calculate the 2แตก value, and sum them up for a decimal value.


Dave Mason - SQL Server - WAITFOR DELAY

There are certain design patterns in T-SQL that give me pause. They may not be "code smells" per se, but when I encounter them I find myself thinking "there's got to be a more sensible way to accomplish this". WAITFOR DELAY is one example. I've used it a few times here and there, mostly in one-off scripts. If I wrote some code that used it regularly in production, I'd be paranoid about putting my SPID to sleep forever. Maybe a little bit of paranoia is a good thing. But I digress.

Dynamic SQL, NULL, and EXEC

Dave Mason - SQL Server - TSQL - EXEC

I've been a fan of dynamic T-SQL for a long time. I ran into something new, though, that threw me for a loop. It started with a stored procedure that builds a dynamic query string, based on a dozen or so input parameters that are all optional. If an input parameter is NULL, it gets excluded from the query logic. Here's a typical example with three parameters:

T-SQL Tuesday #106 - Triggers

Dave Mason T-SQL Tuesday

For T-SQL Tuesday #106, Steve Jones (b|t) asks us to write about an experience with triggers, either good or bad. I'd bet that most of you are thinking about INSERT, UPDATE, and DELETE triggers. But for my DBA sensibilities, the best trigger is a DDL trigger.

Preparing for Exam 70-767: Implementing a Data Warehouse

Dave Mason MCSE: Data Management and Analytics

After nearly a two year hiatus, I steered my learning path back towards certifications. I'm happy to tell you I recently passed Exam 70-767: Implementing a Data Warehouse using SQL. If you don't know, there's a non-disclosure agreement for Microsoft exams, so I can't divulge particulars about the exam. But as Cathrine Wilhelmsen (b | t) has done, I wanted to share my experience studying and preparing for the exam.

What's Your Learning Style?

Dave Mason - Certification - Learning Style

I see the "What's your learning style?" question posed on social media from time to time, mostly from people in the SQL Server community. I rarely think about it, but I've been giving it some thought lately. In my completely unscientific estimation, when people in technical fields discuss their preferred style, the runaway top responses are learning by doing and learning by studying.

tempdb: Are We There Yet?

Dave Mason SQL Server tempdb

As a global resource, it can be challenging to manage [tempdb]. It's a broad topic, so for this post, I'll just focus on these two scenarios that leave [tempdb] out of space:

  • Maximum file size is limited and there is no more free space to allocate new pages.
  • Files are configured for unlimited growth and have consumed all available disk space.

Queries to obtain information for either scenario are reasonably simple: there's the sys.dm_os_volume_stats dynamic management function, the FILEPROPERTY metadata function, and even the (unsupported) extended procedure xp_fixeddrives to name a few. Running queries on demand is a good place to start, but you'll probably want to have some automation, right? A tactic I have used (and that I've seen frequently) is to set up a SQL Agent Job that runs one or more queries and takes action, based on the query outcome.

Naturally, the job runs on a predefined schedule. But how frequently should we check disk/available space for [tempdb]? The temporary nature of [tempdb] makes this a difficult question: objects within aren't saved from one session of SQL Server to another, and evidence to explain runaway growth or loss of available space may be gone before an assessment can be made. Whatever schedule I decide on, I'll always wonder if it's frequent enough (or too frequent).

It's tempting to "over-schedule" a job's frequency, perhaps as much as every X seconds. Asking SQL Server "Are we out of disk space?" over and over again doesn't make a lot of sense to me, though. It reminds me of Bart and Lisa asking Homer "Are we there yet?" until he snaps. Ideally, instead of asking, I want SQL Server to *tell me* when disk/available space is running low.

Performance Condition Alerts

For [tempdb] data files that have a set maximum file size, a performance condition alert for the "Free Space in tempdb (KB)" counter can be created. It's part of the "Transactions" object. (On a side note, trying to find an appropriate counter by exploring what's available in each object, one at a time, is tedious. It's so much easier to query the sys.dm_os_performance_counters DMV.) In the example below, the alert fires if free space falls below 102400KB/100MB. The alert responds by executing a job "Log tempdb Space Usage Stats", and has a five minute delay between responses.

Dave Mason - SQL Server Agent Performance Condition Alert Dave Mason - SQL Server Agent Performance Condition Alert Dave Mason - SQL Server Agent Performance Condition Alert

This approach works well for [tempdb] files that are configured with an initial size equal to their maximum size. With no possibility for auto-growth, you'd decide on the free space threshold amount, do a little math to convert it to kilobytes, and enter the value on the General page of the Alert dialog. If [tempdb] configuration and available disk space allow for future auto-growth events, take into consideration the alert may fire more often than desired.

WMI Alerts

For [tempdb] data files configured with an unlimited Maximum File Size, a WMI alert can be configured for the DATA_FILE_AUTO_GROW event. In the previous example, the alert (or more specifically, the counter) only applies to [tempdb]. But the DATA_FILE_AUTO_GROW event applies to data files for *all* databases. In the example below, the WQL query will restrict the alert to just [tempdb] via a WHERE clause. Like the first example, the alert responds by executing the job "Log tempdb Space Usage Stats", and has a five minute delay between responses.

Dave Mason - SQL Server Agent WMI Alert Dave Mason - SQL Server Agent WMI Alert Dave Mason - SQL Server Agent WMI Alert

This approach works well for [tempdb] files that are configured with room for growth. Note the event fires once per data file growth event. With the likelihood of multiple data files, the "Delay between responses" remains important. Also, if [tempdb] data files grow enough to consume all available disk space, the last alert might occur long before [tempdb] runs out of available space internally. As an alternative to a WMI event, an Event Notification can be configured for the DATA_FILE_AUTO_GROW event.

What to Log

Depending on how [tempdb] data files are configured, it makes sense to check either available disk space or available space within [tempdb] itself (or both) when an alert fires. In addition, determining what is consuming so much space in [tempdb] makes sense too. Microsoft's documentation includes some really good queries for monitoring [tempdb] use. Sunil Agarwal also has an article I found helpful. (It's an oldie, but a goodie.) Additionally, I've found these DMVs useful:

  • sys.dm_db_file_space_usage
  • sys.dm_db_task_space_usage
  • sys.dm_db_session_space_usage
  • sys.dm_tran_session_transactions

SQL Server Features Discovery Report

Dave Mason SQL Server Features Discovery Report

I don't need to validate SQL Server installations on a regular basis. When the need arises, my preference is to run the SQL Server features discovery report. Further, I prefer to run it from the command line. After looking up command line parameters one too many times, I decided to script it out.

New For SQL Server 2017: T-SQL Function CONCAT_WS

Dave Mason SQL Server 2017 TSQL CONCAT_WS

In the last post, I looked at a new T-SQL function for SQL Server 2017. Let's continue down that path and look at CONCAT_WS(), which is also new for SQL Server 2017. Here's the definition of the function from Microsoft Docs:

New For SQL Server 2017: T-SQL Function TRIM

Dave Mason SQL Server 2017 TSQL TRIM

There's a new T-SQL function for SQL Server 2017 named TRIM(). If you're familiar with LTRIM() and/or RTRIM(), you can probably guess what it does. Here's the definition of the TRIM() function from Microsoft Docs: