Optimizing SQL Server’s Wait Types can be a daunting task, there are so many interdependencies to account for, and most of the time optimising a Wait Type could mean sacrificing other system resources (such as disk space) or SQL tasks (such as INSERTs on the underlying table through index creation).

This post will give you a quick reference and overview on all the SQL Server Wait Types, as well a quick way to optimize each single or class of Wait Types (such as optimizing PAGEIOLATCH_EX, ASYNC_NETWORK_IO or WRITELOG etc.).

Read more →

This is a short post to get you started with Microsoft’s new PowerPivot technology, which is part of the new SQL Server 2012 Self-Service BI.

Read more →

Merge Join can be a very fast join operation, although it has some underlying behavior that could dramatically increase the over-all cost this operator has on the query execution plan.

In this post I will go through how SQL Server utilizes the Merge Join operator for performing logical join operations, as well as the different types of Merge Join and how to optimize a Merge Join operation.

Read more →

Nested Loops Join operator (represented by an inner and an outer looping arrows in SQL Server’s Graphical Execution Plan) is used by SQL Server to join together two tables or indexes, this is generally an efficient operator, and superior to Hash or Merge Joins when operating on small data-sets.

This post will goes through why the Nested Loops operator is chosen by SQL Server’s Database Engine, the different types of Nested Loop Joins, and finally how to try and optimize a costly Nest Loops Join operation.

Read more →

Hash Match is a strategy used by SQL Server to join two tables together using the Hash Bucket and Hashing Algorithm approach, this approach is taken when joining tables or aggregating rows.

In this post we will go through what is a Hash Match, what are the types of Hash Match, why does SQL Server Execution Plan decides to go for a Hash Match, and also how to go about optimizing a Hash Match join or aggregation.

Read more →

Bayesian Networks, particularly in its Naive (or Idiotic, as some angry physicist might call it), is an absolutely amazing and intuitive way for reasoning with a Probabilistic Network model. The Bayesian model has been heavily used across a wide array of industries, even though the Naive model is very much a simplistic view of what an actual Bayesian model might looks like, it is still a very practical approximation that has gained a lot of popularity in fields such as classifications and segmentations. This post introduces a client library for running reasoning patterns on a custom-built Bayesian Network.

Read more →

This post goes through the SQL Server DBCC command which deals with displaying the actual underlying statistics to a particular SQL Server index.

Read more →

There has been a lot of buzz going around about the level of integration Microsoft will offer between SQL Server and the Hadoop distributed processing framework. Ever since the public announcement during the SQL Server 2012 virtual product launch event, SQL Server developers have been rushing to try our Hadoop, and understand how it fits within the data management eco-system SQL Server has created.

Read more →

I was just adding a small Ad-Hoc maintenance plan on our shiny new SQL Server 2012 test boxes, when I received the following error message:

Adding tasks to the maintenance plan failed: Object reference not set to an instance of an object.

This is a short post that goes through some reasons as to why this error message might appear.

Read more →

The Data-Mining Excel Plugin for SQL Server 2008 is one of the more awesome tools in the Microsoft BI tool-set, although might require some configuration before deployment into the business.

While I was trying to roll-out this solution (to a test group), I ran into the following error message:
Error (data mining) Session Mining objects (including a special data source view used to process data mining dimensions) cannot be created on this instance

This posts goes through how to solve this issue and get your Data-Mining Excel Plugin talking with the back-end SSAS instance.

Read more →