Runtime Constants Sniffing in SQL Server

April 23, 2018 by

Most of the people know about the so-called “Parameter Sniffing”. This topic was discussed in many aspects in a number of great articles. It is interesting that not only parameters might be “sniffed” during the first execution, but also a runtime constant functions. Let’s look at the example. Test Data I will use a test […]

Few Outer Rows Optimization in SQL Server

April 20, 2018 by

In this blog post, we will look at one more Nested Loops (NL) Join Post Optimization Rewrite. This time we will talk about parallel NL and Few Outer Rows Optimization. For the demonstration purposes, I will use the enlarged version of AdventureWorks2014. In the sample query, I will also use the trace flag (TF) 8649 – this TF […]

SQL Server index operations

April 19, 2018 by

In the previous articles of this series, we described the structure of the SQL Server tables and indexes, the main concepts that are used to describe the index and the basics and guidelines that are used to design the proper index. In this article, we will go through the operations that can be performed on […]

SQL Server NOLOCK and Top Optimization

April 12, 2018 by

Sometimes people use nolock hint as a “turbo” button for their queries, assuming that not taking locks will speed up the query execution. There are many good articles describing all the dangerous moments of this approach, because of the read uncommitted isolation level. However, the focus of this article is a performance problem that you […]

SQL Server index design basics and guidelines

April 5, 2018 by

In the previous article of this series, SQL Server Index Structure and Concepts, we described, in detail, the anatomy of QL Server indexes, the B-Tree structure, the properties of the indexes, the main types of indexes and the advantages of using the indexes for performance tuning.

Sequence Objects in SQL Server

March 22, 2018 by

Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012. Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. […]

SQL Server index structure and concepts

March 19, 2018 by

In my previous article, SQL Server Table Structure Overview, we described, in detail, the difference between Heap table structure, in which the data pages are not sorted in any ordering criteria and the pages itself are not sorted or linked between each other, and Clustered tables, in which the data is sorted within the data […]

Reporting and alerting on job failure in SQL Server

March 12, 2018 by

SQL Server Agent can be used to run a wide variety of tasks within SQL Server. The built-in monitoring tools, though, are not well-suited for environments with many servers or many databases. Removing reliance on default notifications and building our own processes can allow for greater flexibility, less alerting noise, and the ability to track […]

Replace bridge tables in a Data Warehouse with SQL Server 2017 graph database

March 8, 2018 by

Just like in Santa’s Bag of Goodies, every release of SQL Server often has something for everyone – be it enhancements to DMVs for the DBAs, new functions for T-SQL developers or new SSIS control tasks for ETL developers. Likewise, the ability to effectively support many-to-many relationships type in SQL Graph has ensured that there […]

SQL Server table structure overview

March 7, 2018 by

Microsoft SQL Server is a relational database management systems (RDBMS) that, at its fundamental level, stores the data in tables. The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format. Each row is considered as an entity that is […]

SQL Server table hints – WITH (NOLOCK) best practices

February 14, 2018 by

SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used […]

How to quickly generate a large number of dimension tables for reporting applications

January 19, 2018 by

Description When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI. Creating this schema by hand is time-consuming […]