In-Memory storage technology finally make their debut appearance on the SQL Server 2014′s BI stack, with the creation of a proper memory optimized tables and stored procedures, unlike the Columnstore feature which offers a read-only memory optimized solution, that does not work overly well in a true transactional environment.

In this post I hope to dissect the new In-Memory tables feature of SQL Server 2014, providing an overview of how the technology works, how to create in-memory tables, maintain them and any pitfalls to watch out from. Mainly though, I am writing this as a reminder to myself of the latest articles I have been reading about this cool new feature.

Read more →

Hashing can be a very useful technique when dealing with the storage and look up of large text fields (say a table of URLs or Search Keywords), these fields will incur high resource utilization on any database engine if used directly in DML statements, in which they are either filtered by or aggregated on. Any index built on these fields is costly to maintain, if it is at all possible given that SQL Server limits index size to 900 bytes.

Using hashing functions we can facilitate the handling of large textual data in the relational engine, leading to improved performance when these fields are being compared to satisfy a query, hashing can also be used to build unique and non-unique indexes that are easier to manage than directly using the text fields in the index definition. In this post we will discuss a few options for hashing large text data using functions native within SQL Server, as well as provide other external  hashing algorithms that we can integrate into Microsoft’s SQL Server (or any RDBMS for that matter) that might provide a better practical performance. 

Read more →

SQL Server uses two methods to ensure transactional consistency and protects the data that is being accessed, these are Locks and Row-Versioning, these methods ensure that you manage your data concurrency effectively by specifying the level of access other transactions have to the data being processed, the game here is to balance either resources or data integrity against concurrency.

Read more →

Sometimes one might like to identify a particular SQL Server resource using the Page ID this resource is under, you might notice a Page ID sometimes on the Activity Monitor page under the Wait Resource column, if a certain query is waiting on a particular resource, some DMVs will reference Page IDs as well as the SQL Server log files, I find it handy to be able to see exactly what SQL Server Object falls under this Page ID, in order to diagnose any issues with access to that particular resource.

Read more →

There are many ways to obtain the execution plan of a SQL Server query, each of which serves a particular purpose, for example if you can run a query easily, then the best way to get an execution plan is to simply enable the execution plan (either through SSMS or SETting the correct session option), but if the query takes a long time to run, or you simply cannot run the query any other reason, then you might decide to profile the query when it is naturally running (perhaps on the live environment) through SQL Profiler.

In this post I will go through a simple SQL query to extract the execution plan from SQL’s own plan cache. There are many reasons why this might not be an accurate way to extract a query, but it is certainly helpful in situation were you have good understanding of your DB setup.

Read more →

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 →

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 →

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 →