Analyzing the indexes using DMVs

Hi,

This article is about assessing the index, regarding which one is needed and which one is not.  By the way it willbe  helpful for intermediate level guys on SQL Server

SQL Server has no magic to improve the performance of your query, it is the correct utilization of indexes which will do the magic for you. We are going to do our assessment based on Dynamic Management View(DMV)  which is  the out of box feature from SQL Server 2005 on wards.

We should know one common fact regarding DMVs, the informations obtained on a DMV will last upto the restart of that instance. So it is the best practice to store those information on a table periodically.

Here we go, we are going to do our assessment based on below DMV

sys.dm_db_index_usage_stats

SELECT db.name AS [database] ,

ob.name [table],

ix.name [index],

dm_ix.user_seeks ,

dm_ix.user_scans ,

dm_ix.user_lookups

FROM sys.dm_db_index_usage_stats dm_ix

INNER JOIN sys.databases db ON db.database_id = dm_ix.database_id

INNER JOIN sys.indexes ix ON ix.object_id = dm_ix.object_id

INNER JOIN sys.objects ob ON ob.object_id = dm_ix.object_id

WHERE ob.type = 'U'

Now we are done, the above SQL will show you the database name, table name, and what are all the index exist etc.  Now we should concentrate on below threee columns

• user_seeks
• user_scans
• user_lookups

user_seeks :-  A seek is when the index is used, as an index, to retrieve data. The key values and the b-tree that make up the index are referenced to find exact values and locations within the index.

user_scans:- A scan is when the index is used, well, like a stack of papers. They’re gone through one at a time to find the interesting values. Needless to say, in most situations, seeks are preferable to scans.

user_lookups:- Number of bookmark lookups by user queries.

You can filter the results of one database by using database id on the where class.

Feel free to pass your comments and feedback.

Performance Improvements in SQL 2014(2/4)

Haja Batcha

Hi dears, on my previous blog we saw about a enhancement regarding a update statistics on SQL 2014. Will see yet another enhancement on SQL Server 2014 which is non clustered indexes on temporary table, and which can also be called inline indexes.

“Non clustered indexes on table variable”

First we will see ‘Which is not possible in previous edition, which is now possible in 2014′ secondly we will see ‘Which is possible in previous edition, which can be coded very easily’  

The table variables are one the wonderful feature which was introduced on SQL Server 2000. Because with table variable excessive recompilation will be avoided, it doesn’t need statistics when you create them. And the one big disadvantage was, you cannot create non clustered index on them, that too resolved from SQL Server 2014 CTP1 .

With SQL Server 2014 CTP1 , now it is possible to create Non Clustered Index on table variable.

Performance Improvements in SQL 2014(1/4)

Hi dears, it is always nice to know something new. Would like to start with a wisdom i like “Learning brains will stay younger”. It is always advised by many performance tuning expertise,

“Update Statistics before you take any other steps to tune performance”.

SQL server 2014 has new feature called incremental statistics. Really it was a nice a feature, if you are having your tables spread into many partitions. Tables on single partition won’t be benefited by this feature.  Before we know about this feature, will have a glance about Update statistics, if you did’t update statistics on the database. The query optimized will struggle to give you best execution plan, which will ruin the performance of your query for sure.

Sales Data Partitions January Partition1 February Partition2 March Partition3

Now we will see about the new feature, imagine you have a table exist over partitions. which will fill the data monthly on every partitions. For example, sales data will filled into the Sales table as given below.

Now the tables will populate the data as given above, in the middle of march you would like to make update statistics on this table.Given below is normal update statistics before SQL Server 2014.

Walk inside DMV Stacks

Hi dears, we all know well about the power of SQL Server DMVs in so many aspects. That was the reason, we have decided to walk through this series of blogs.

Today we will see about  How do I know which stored procedures are using a particular table?

I always prefer to write something about the problem i have faced and how it was resolved. Recently we have recommended our developer teams to replace a function, which generates a sequence using a scalar-value function will be used to insert into a table,which gave us big troubles while it was accessed concurrently leads to much heavy locks. So we have recommended to use another table to maintain a identity value, to use the ident_current to get the value, hence table already have another identity column.

The solution was good and acceptable, but while implementing such a thing on a existing huge database which was used over many years and by many teams. We need to know what are all the insert stored procedures will hit this table. To safely replace the new solution, we should know clearly what are all the places needs to be touched. On such a cases, the below DMV is much helpful to tell us all the related database objects linked with a specific table.

I did’t wrote much about the DMV, but just execute it by passing a table name on to referenced_entity_name, it will tell you more stories..

SELECT

referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),

referencing_object_name = o.name,

referencing_object_type_desc = o.type_desc,

referenced_schema_name,

referenced_object_name = referenced_entity_name,

referenced_object_type_desc = o1.type_desc,

referenced_server_name, referenced_database_name

--,sed.* -- Uncomment for all the columns

FROM sys.sql_expression_dependencies sed

INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id]

LEFT OUTER JOIN sys.objects o1 ON sed.referenced_id = o1.[object_id]

WHERE referenced_entity_name = 'Customers'