Ahmad Yaseen

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 described by the columns that hold the attributes of the entity. For example, the customers table contains one row for each customer, and each customer is described by the table columns that hold the customer information, such as the CustomerName and CustomerAddress. The table rows have no predefined order, so that, to display the data in a specific order, you would need to specify the order that the rows will be returned in. Tables can be also used as a security boundary/mechanism, where database users can be granted permissions at the table level.

Read more »
Ayman Elnory

SQL Server Query Execution Plan for beginners – Clustered Index Operators

March 5, 2018 by

We have discussed how to created estimated execution plans and actual execution plans in various formats in my previous article SQL Server Query Execution Plan for beginners – Types and Options.

In this article we will continue discussing the various execution plan operators related to clustered indexes, and what they do, when do they appear and what happens when they do.

Read more »
Gerald Britton

Top 10 questions and answers about SQL Server Indexes

October 5, 2017 by


Without a doubt, few technologies in SQL Server cause as much confusion and the spread of misinformation than indexes. This article looks at some of the most asked questions and a few that should be asked but often are not. We’ll be using SQL Server 2016 for the examples and a free tool, for SQL Server query execution plan analysis, ApexSQL Plan, to explore the effects of indexes on a typical business problem: A table of customers.

Read more »
Ed Pollack

SQL Server reporting – SQL Server Index Utilization

August 24, 2017 by

Understanding indexing needs allows us to ensure that important processes run efficiently and that our server hardware is not being over-taxed by poorly performing queries.

Collecting metrics on SQL Server index usage and missing index needs is critical to making smart decisions. To be truly proactive, though, we need to create a framework that allows us to quickly, efficiently, and regularly report on changes in indexing needs. With a system in place that can let us know when changes are needed, we can stay ahead of the optimization game and keep our customers happy!

Read more »
Ahmad Yaseen

How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority

July 18, 2017 by

When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.

Read more »
Ed Pollack

Collecting, aggregating, and analyzing missing SQL Server Index Stats

June 27, 2017 by


Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are received can save us immense time while preventing costly performance messes.

Read more »
Daniel Tikvicki

How to monitor total SQL Server indexes size

June 12, 2017 by

SQL Server indexes overview

Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.

Read more »
Sifiso W. Ndlovu

How to use Columnstore Indexes to improve your Data Warehouse Staging Environment

May 4, 2017 by

My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the traditional B-tree index (rowstore). Whilst most of the existing documentation relating to columnstore seem to focus on the benefit of columnstore against data warehouse workloads, in this article I argue that the usage of columnstore index should not be limited to facts and dimensions instead let’s introduce it in our data warehouse staging environments too.

Read more »
Simon Liew

The impact of Residual Predicates in a SQL Server Index Seek operation

March 6, 2017 by


It is common assumption that an Index Seek operation in a query plan is optimal when returning a low number of output rows. In a scenario involving residual predicates, an Index Seek operation could be reading a lot more rows than it needs into the memory, then each row is evaluated and discarded in memory based on the residual predicate and returns low number of output rows.

This article will explain the concept and the impact of Residual Predicates in a SQL Server Index Seek operation.

Read more »
Ahmad Yaseen

Indexing SQL Server Temporary Tables

January 31, 2017 by

SQL Server Temporary tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection.

Read more »
Kimberly Killian

SQL Server Index vs Statistics a consultants woes…or rants

December 29, 2016 by

As a DBA, I am often asked why is something performing slow, what and why statistics need to be updated or what will cause them to be “off”. My initial question to clients when they pose these questions to me is what changed on your end? Did the data change significantly and did the rebuild or reorganize index job run? Before I get into the answers to these questions from my clients, let me give you some background. So, just to clarify, for most of my clients, I work as a remote part-time DBA, that being said, I manage their database from every aspect including setting up servers, backups/restore, troubleshooting, managing their index’s, etc. and again remotely. So normally, I have setup jobs that will manage their index’s ranging from a weekly rebuild or even sometimes I use one that I’ve designed that makes a choice to either rebuild or reorganize an index based on fragmentation level. The “general rule of thumb” is reorganizing the index for fragmentation from 5% to 29% and rebuild when 30% plus. Those are pretty standard numbers I did not make them up.

Read more »
Page 1 of 212