Log in | Register

Indexes & Statistics Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education Indexes & Statistics Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education

Indexes & Statistics

SQLServerGeeks Annual Summit 2016 IS HAPPENING IN BANGALORE. HAVE YOU REGISTERED?

SQL Server– Unique Constraint VS Unique Index

Both  enforces uniqueness of the column. When we define an Unique constraint, SQL server creates an unique index (Unique non clustered index) on the column where Unique constraint is defined. Even though both of them can be used for same purpose but consider below points before you decide which one to use.
1 –  From SQL server […]

Column order and equality operator on Index

Does Column order  in where clause matters?

Whenever I have any doubt, I always play around with it to clarify my doubt. While doing experiment with index operation, I learnt something and here I’m demonstrating what I learnt?

Let’s do some experiment,

In below experiment I’m using the AdevntureWorks database, which can be downloadable from CodePlex.

Demo – 1 […]

DBCC CLONEDATABASE – Another DBCC command in the List

Hello Friends,

As the part of SQL Server DBA world, we have used many DBCC commands in our workplace like DBCC CHECKDB(), DBCC TRACEON(), DBCC SQLPERF() etc. All these commands make DBA life easier and help us to troubleshooting issues, alerting etc. In the same row, with the announcement of SQL Server 2014 service pack […]

Index Selection Fundamental

Index is one of most widely discussed topic among developers and DBAs. During query optimization Index is one of the important aspects. I came across multiple instances where Developer/ DBA creates Index but they are not sure whether that index is used by the optimizer or not. Also I received emails from developer asking […]

Clustered Index seek doesn’t guarantee optimal Query plan

I was working on removing duplicate indexes from a table. Before removing an index usually I check whether that index is used by any query or not, is there any index hint is used, looking at index usage statistics and many other factors.

If you search in web you will get many ways to identify […]

The correct cardinality estimation for table variable using trace flag 2453

Hi Friends,

My today’s blog post is focused on the correct cardinality estimation for table variable using trace flag 2453. In one of my previous blog post, we have seen the use of OPTION (RECOMPILE) query hint for correct cardinality estimation in case of table variable. Now the question is, Is there any way to […]

SQL Server 2016 CTP – Alter Column Online Operation

Hi Friends,

Various new features and improvements are coming with SQL Server 2016. Various performance improvements are also added to the CTP versions. Today, I’ll show you one of them i.e. Alter Table … Alter Column online operation.

Blocking is one of the major problems when you work on performance tuning for SQL Server. We knows […]

The correct cardinality estimation using table variable

Hi Friends,

In my previous blog post, we have seen that SQL Sever estimates number of rows in a table variable was 1. Now the question is, Is there any way to make the correct estimation for the number of rows while using table variable?

My today’s blog post is focused on the correct cardinality estimation […]

Nested Loop Join – Temp Table versus Table variable

Hi Friends,

In my previous blog post, I have shown you the working of nested loop join in SQL Server. Today, I am going to show you the Nested Loop Join – Temp Table versus Table variable.

Before executing a query, SQL Server generates an estimated execution plan on the basis of various server settings and […]

Forward and Backward Scan

Hi Friends,

First of all wish you all a very Happy New Year 2016. I hope this new year will bring lots of joy and happiness in your life. Today, I’ll show you the Forward and Backward Scan.

We all knew that whenever we  create an index (Clustered or Non-Clustered) on a table in SQL Server […]

Newsletter_Footer