Some database operations can be performed in distinctly different manners, with different impacts on query performance. One important example of such an operation is calculating an aggregate. In this article, we’ll take a look at how aggregates can be “blocking” or “non-blocking”, how it affects memory allocation, and ultimately, what impact this has on your query.
Category: By difficulty
Datawarehouse modelling: Inmon vs Kimball
If you’re into business intelligence, data warehousing and analytics, you will have heard an endless number of references to Bill Inmon and Ralph Kimball. These two figureheads in datawarehousing architecture have produced an immense number of books, articles, training seminars, etc. While many of their strategies and modelling approaches are similar, they have near-opposite views on other aspects.
HASH JOIN deep-dive
Among the three different types of join operators used by SQL Server, the HASH JOIN does some of the hardest work. It scales pretty well and is very suitable for parallel processing. As such, it can be very powerful in many applications, but hash joins can potentially consume quite a bit of memory, so seeing on in your query plan could be an indicator of a performance tuning issue in your query or data.
Directed acyclic graphs vs parent-child hierarchies
We’ve recently looked at ways to work with parent-child hierarchies, particularly in reporting scenarios. Regular parent-child hierarchies are great when working with dimensions that are ragged, but they have a critical limitation – any given node in the tree can only have a single parent node. A great solution to this problem is a DAG – directed acyclic graph.
An introduction to filtered indexes
Filtered indexes are an excellent performance tuning feature found on SQL Server 2008 and later. Basically, by adding a WHERE clause to a CREATE INDEX statement, you can make the index smaller, more specific and faster.
Changes in Management Studio keyboard shortcuts
A few keyboard shortcuts have changed in the code editor of the SQL Server 2014 Management Studio. Personally, I found this incredibly annoying because I’ve been using Ctrl+E for many years to execute a query, but now, all I got was
(Ctrl+E) was pressed. Waiting for the second key of chord...
.. in the status bar.
Late-arriving dimension members
In datawarehousing, a common scenario you may have encountered is the phenomenon of late-arriving dimension members. Imagine a situation where you get dimension members and fact data from two entirely different data sources, which may or may not provide data at the same time. Some fact data will probably be ready to load before its corresponding dimension member(s) arrive. How do you tag your fact data, and what consequence does that have for the integrity of your data warehouse?
Minimally logged operations
Today, we’re going to look at which database operations can be performed with what’s known as “minimal logging”, which can result in a considerable performance boost. Included is a quick overview of transaction logging and what it’s good for.
Introduction to partitioning
Partitioning is not only a great way to improve performance in large tables, but you can also use it to manage the physical storage of different chunks of data without keeping it in different tables.
Error handling using TRY-CATCH
As of SQL Server 2005, you can handle errors using a TRY-CATCH block, similar to “real” programming languages. This enables you to trap most common errors and handle them, instead of having your entire batch or procedure fail with an error message.