Press "Enter" to skip to content

Curated SQL Posts

Modulo

Kenneth Fisher walks us through modulus division in SQL Server:

Now aside from the odd occasion when you actually need it for it’s simple purpose it’s a rather interesting way to get a rolling count. Basically you can use it to get a list back of 1,2,3,…n-1,0 where n is your divisor.

There are a few great use cases for modulo within SQL Server.  One not mentioned is building test data.  You can easily build a uniformly distributed set of randomized numeric values within a particular range using modulo math.

Comments closed

Partition Switching

James Anderson gives his favorite feature in SQL Server:

The good news is that the SWITCH command works on regular tables and in any edition. This means I can quickly transfer all of the data from one table to another in Standard Edition.

In reality, I have found few uses for the regular table to regular table switch; the trick to add the IDENTITY property to a column with existing data is the most recent. SWITCH is most useful when partitioned tables are involved. Sorry, Standard Edition users.

Partition switching is a fascinating solution for a difficult technical problem.

Comments closed

WhoIsActive For Azure

Adam Machanic has a new version of sp_whoisactive specifically for Azure SQL Database:

So I set about looking for a workaround. This week I think I’ve finally managed to get something working that approximates the number I need from that view, ms_ticks.

Attached is sp_whoisactive v11.112 — Azure Special Edition v2. Please give it a shot, and I am especially interested in feedback if you use the @get_task_info = 2 option when running sp_whoisactive. That is the main use case that’s impacted by the lack of ms_ticks information and my attempt at a workaround.

If you’re using on-prem SQL Server, this doesn’t add anything new, but if you’re on Azure SQL Database, give it a try.

Comments closed

Views Aren’t Tables

Grant Fritchey dives into the problem with nested views:

That’s a query against the XML stored in the ProductModel table. The view was created to mask the complexity of the necessary XPath code, while providing a mechanism for retrieving the data from the XML. This is a common use of views. However, when we then treat the view as a table, and join it to other tables and views, we present a problem for the optimizer. Because a view is not a table, but is instead a query, the optimizer has to resolve this query in combination with any other views or tables to arrive at an execution plan for the whole combined mess. While the optimizer is very good at what it does, because of the complexity caused by the additional unnecessary processing to figure out which parts of the view is not needed to satisfy the query, it can make poor choices. That can result in poor performance.

“Poor performance” can be an understatement.

Comments closed

Azure Status Alerts

Ron Dameron has built Outlook rules for Azure status alerts:

I do have alerts set up on the Azure portal and in Application Insights to notify me when availability or performance thresholds are violated but I also need to know if there is a global or regional issue that might affect our app so that I can respond and notify the staff when appropriate. Azure status changes are reported on the Azure Status web page.

The following will describe how to use the Azure Status page RSS feeds and Outlook rules for notification if things go sideways in Microsoft Azure.

This is a good use of Outlook’s built-in RSS reader.

Comments closed

DMVs

Andy Yun talks about his favorite SQL Server feature:

Much earlier in my SQL Server career, I never knew much about how SQL Server operated under the hood. I only had knowledge of the things I could do directly, but I never questioned what was really happening behind the scenes. Then I attended SQLskills IE1 training, which was a turning point in my career. Among other things, it was my first exposure & deep dive into SQL Server Internals. I became enamored with learning how things really worked under the hood and the DMVs/DMFs became one of my best friends.

This is a big part of SQL Server administration.  It boils down to asking the server where it hurts and understanding how it responds.  DMVs are extremely helpful in that regard.

Comments closed

SQL Agent

Steve Jones picks his favorite SQL Server feature:

I’ve seen the platform grow and expand quite a bit. I’ve spoken on a number of topics over the years, as my jobs have changed and my emphasis has wandered. Of all the features available, however, if I have to choose one, it would be…

SQL Agent.

I’m a programmer at heart. I grew up admiring the power of computers to execute code over and over again. I appreciate the ability of computers to remember things and remind me, or to handle them on their own.

SQL Agent has quirks, but a fully-featured scheduling system integrated into the database engine is extremely powerful.

Comments closed

DBCC Checking Memory-Optimized Tables

Brent Ozar shows us how to run a DBCC check against a memory-optimized table.  The answer is not trivial:

THE EASY FIX: RUN FULL NATIVE BACKUPS EVERY DAY, AND FREAK OUT WHEN THEY FAIL.

Backup failures aren’t normally a big deal, but if you use in-memory OLTP on a standalone server or a failover clustered instance, backup failures are all-out emergencies. You need to immediately find out if the backup just ran out of drive space or lost its network connection, or if you have game-over Hekaton corruption.

Note that you can’t use SAN snapshot backups here. SQL Server won’t read the In-Memory OLTP pages during a snapshot backup, which means they can still be totally corrupt.

This works fine for shops with relatively small databases, say under 500GB.

Brent also has a Connect item to fix this.  Upvote if you have memory-optimized, durable tables.

Comments closed

Message Handling With Service Broker

Colleen Morrow shows how to send and receive messages using Service Broker:

If you’ve been following along in this series, you’ll have created the necessary components of the service architecture, namely the message types, contract, queues, and services.  You will also have started a conversation between your initiator and target services.  You’re finally ready to start sending and receiving messages in your Service Broker app.  Whoohoo!  Let’s get to it!

Here’s what we’ve been waiting for, and it’s a good example going through message handling.  I also hope Colleen handles poison pill messages and other Service Broker-related traps.

Comments closed

Restraint

Andy Mallon comes out of left field with his favorite feature:

It’s really tempting to implement cool-sounding features. It’s really tempting to hyper-tune solutions to be the absolutely perfect, most-optimal solution. But it takes a real expert to realize when you’re over-engineering a solution.

Take a moment to appreciate your own restraint. Appreciate all the features that you didn’t implement because you didn’t have to. Be happy that you looked at the big picture and decided the best solution was the one you were able to support.

It’s definitely an interesting approach to the T-SQL Tuesday challenge, so full credit for that.

Comments closed