Press "Enter" to skip to content

Day: April 15, 2016

Heaps

Published 2016-04-15 by Kevin Feasel

Derik Hammer walks us through heap tables as part of his indexing series:

A heap is a grouping of unsorted pages which are not linked. Page anatomy is out of scope for this series since all types of indexed and non-indexed tables use the same page structure but I do encourage you to check out here and here to learn more.

A heap is comprised of one or more index allocation map (IAM) pages which point to the data pages which make up the heap. The only exception to this is when you have a row which has been updated and could not fit in its page anymore. In that case, you get a forwarding pointer to the row which has been moved to an existing page with space or a new page. It is possible for you to produce a chain of forwarding records if the row continues to need relocation by further operations.

This is a good introduction to heaps and will feed into later work on how different types of indexes work.

Comments closed

Against DBCC Commands

Published 2016-04-15 by Kevin Feasel

Erik Darling doesn’t like (most) DBCC commands:

Not what they do, just that the syntax isn’t consistent (do I need quotes around this string or not?), the results are a distraction to get into a usable table, and you need to write absurd loops to perform object-at-a-time data gathering. I’m not talking about running DBCC CHECKDB (necessarily), or turning on Trace Flags, or any cache-clearing commands — you know, things that perform actions — I mean things that spit tabular results at you.

I completely agree.  One of the nicest things about SQL is that I can use the same syntax to read metadata that I can data.  DBCC commands are a jarring difference.

Comments closed

Modulo

Published 2016-04-15 by Kevin Feasel

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

Published 2016-04-15 by Kevin Feasel

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

Published 2016-04-15 by Kevin Feasel

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

Published 2016-04-15 by Kevin Feasel

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

Published 2016-04-15 by Kevin Feasel

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