Press "Enter" to skip to content

Month: April 2016

T-SQL Tuesday Roundup

Published 2016-04-18 by Kevin Feasel

Jens Vestergaard has his round-up of favorite SQL Server features:

This week, I have been looking forward to the time where I got to read through all the contributions to my#TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).

So, in no particular order, I give you the round-up:

I like T-SQL Tuesday for several reasons; one of the more selfish reasons is that each month, I get to expand my blogroll a little bit further.  This was a particularly good one, so check out the entrants.

Comments closed

Add And Remove Identity Columns

Published 2016-04-18 by Kevin Feasel

Dan Guzman shows how to use partition switching to add or remove identity columns from existing tables:

All tables are partitioned from a database storage engine perspective since SQL Server 2005, although multiple partitions require Enterprise Edition. The implication of this storage architecture is that one can use ALTER TABLE…SWITCH regardless of SQL Server edition to move the entire contents of one table to another without physically moving rows as long as the source and target tables have an identical schema and are physically aligned (indexes and tables on same filegroup). SWITCH performs fast storage meta-data changes so the operation typically takes less than a second regardless of table size. The target table must be empty before SWITCH. After SWITCH, the target table will contain the data and the source table will be empty (similar to TRUNCATE).

Since the identical schema restriction does not include the IDENTITY column property, SWITCH is a handy technique to add or remove IDENTITY from an existing column of a non-empty table. For an empty table, a simple drop and create is easier and more efficient.

This is one of the few really good uses of the SWITCH operator in standard edition, but if ever you do need it, you’ll be glad it’s there.

Comments closed

Fast Queries Can Hurt Performance

Published 2016-04-18 by Kevin Feasel

Andy Mallon diagnoses a fast query which was hurting performance:

This code is coming from an ORM, which is parameterizing the filters, but not the (unnecessary & arbitrary) TOP value. The DMVs all viewed these are separate queries, so it was not aggregating the stats. It also wasn’t reusing the plan (thus chewing up even more CPU from frequent compiles). If the TOP had not been there, or it had been passed as a parameter, my initial query of sys.dm_exec_query_stats should have found it.

There are a couple of issues Andy works through, and his advice is good:  just because something runs quickly doesn’t mean it can’t (in aggregate) have a negative effect on your server.

Comments closed

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