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 2016 – sys.dm_db_incremental_stats_properties for Incremental Stats

Hello Friends,

During various troubleshooting scenarios, we generally check the statistics information on the objects. These statistics information helps us a lot to detect the root cause. There is a DMF that we use to check the statistics information i.e. sys.dm_db_stats_properties. This DMF requires the object id and stats id to return the details.

SQL Server […]

By |April 7th, 2017|Categories: Database Administration, Database Development, Indexes & Statistics|Tags: , |Comments Off on SQL Server 2016 – sys.dm_db_incremental_stats_properties for Incremental Stats

Trace Flag 634 – Disable Background Columnstore Compression

Hi Friends,

In Columnstore Technology, we knows that Column data stores in segments and segments groups together to form the rowgroup. One of the main reason of using Columnstore technology is compression of rowgroups. By default, SQL Server perform compression of closed delta stores. This tasks is done by tuple mover. Which invokes after a […]

By |December 26th, 2016|Categories: Compression, Database Administration, Indexes & Statistics, Performance|Tags: , |Comments Off on Trace Flag 634 – Disable Background Columnstore Compression

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 […]

By |September 17th, 2016|Categories: Indexes & Statistics, Transact-SQL|Tags: , , |Comments Off on SQL Server– Unique Constraint VS Unique Index

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 […]

By |September 10th, 2016|Categories: Indexes & Statistics, Query Tuning, SQL Server|Tags: , , , , |Comments Off on Column order and equality operator on Index

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 […]

By |September 5th, 2016|Categories: Database Administration, Indexes & Statistics|Tags: |Comments Off on DBCC CLONEDATABASE – Another DBCC command in the List

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 […]

By |July 30th, 2016|Categories: Indexes & Statistics, SQL Server|Tags: , , , |Comments Off on Index Selection Fundamental

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 […]

By |June 16th, 2016|Categories: Indexes & Statistics, Performance Tuning, Programming, Query Tuning, SQL Server|Tags: , , , |Comments Off on Clustered Index seek doesn’t guarantee optimal Query plan

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 […]

By |May 21st, 2016|Categories: Concurrency & Transaction, Database Engine, Database Maintenance, Indexes & Statistics, Performance, Waits, Locks and Latches|Tags: , |Comments Off on SQL Server 2016 CTP – Alter Column Online Operation

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 […]

By |March 7th, 2016|Categories: Database Administration, Database Development, Database Engine, Indexes & Statistics, Performance, Query Tuning|Tags: , |Comments Off on The correct cardinality estimation for table variable using trace flag 2453

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 […]

Newsletter_Footer