sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats in SQL Server 2005

Today I am happy to introduced one more article written by Mr. Mark Will regarding two very useful data management views  (DMV) in SQL Server 2005. I am sure every reader of my blog will be happy to read it as the information provided in the article could become very useful asset for everybody who are using SQL Server 2005.

Introducing SQL 2005 Two Data Management Views

by Mark Wills

In SQL 2005, new Data Management Views were introduced known as DMV's.

I have recently been involved in some discussions as to what these views really do, and thought I might share some relatively light hearted discussion.

At first glance they give information which doesn't reconcile. And that is why they are different, it is more the differences that become important rather than they don't match.

They actually tell us different pieces of the index puzzle, and collectively, are very telling about your index designs (which will be a different post).

The views are :

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted and updated each time the plan is executed.

sys.dm_db_index_operational_stats reports on all indexes regardless of whether they have been used and records how many times the storage engine executes a specific operation on the index.

The way I remember how to use them is by name (duh), as in "is my index useful" then usage, "is my index operating efficiently" then operational.

Let's create a couple of test tables for this purpose :

-- first a 'heap' table ie one with no PK or clustered index

IF object_id('tst_tbl_heap','U') is not null drop table tst_tbl_heap

CREATE TABLE tst_tbl_heap (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

go

-- now a 'clustered' table

IF object_id('tst_tbl_indexes','U') is not null drop table tst_tbl_indexes

CREATE TABLE tst_tbl_indexes (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

ALTER TABLE tst_tbl_indexes ADD CONSTRAINT PK_tst_tbl_id PRIMARY KEY CLUSTERED(ID)

CREATE UNIQUE INDEX idx_tst_tbl_SN ON tst_tbl_indexes(SecurityNumber)

go

--OK, now lets see what we have :

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- we get nothing - and why not ? remember the name ? we haven't used any indexes yet...

-- but...

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s            -- note : this has parameters

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

 

-- does show us our two indexes, and even shows us a row for our table without an index.

-- So, it is not just for indexes huh !

-- now, remember our name ? operational - but are they working, no, the counts are zero.

-- lets now add some data...

INSERT tst_tbl_heap(ID,FirstName,SecurityNumber) values (1,'Mark','1234567')

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber) values (1,'Mark','1234567')

-- now lets look again at our DMV's

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- Now we get something ! despite being called indexes, not just for indexes.

-- Similarly for below we also return information

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- We wont bother anymore about the heap table, you can play with that

-- the lesson was that even a table without indexes is getting in there.

-- Now lets do an insert

 

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber)

SELECT 2 as id,'Mark2' as firstname,'2234567' as SecurityNumber union all

SELECT 3 as id,'Mark3' as firstname,'3234567' as SecurityNumber

 

-- now lets look again at our DMV's

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

 

-- So, what happens with a select ?

SELECT * from tst_tbl_indexes where ID = 2

-- now lets look again at our DMV's

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- we get user_seeks=1

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- singleton_lookups=1

-- so let’s now try another query

 

SELECT * from tst_tbl_indexes where ID in (1,2,3)

go

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- we get user_seeks=2

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- singleton_lookups=4 on our PK, nothing on the second (1 per selected rows above)

-- and finally clean up those tables

IF object_id('tst_tbl_heap','U') is not null drop table tst_tbl_heap

IF object_id('tst_tbl_indexes','U') is not null drop table tst_tbl_indexes

 

Now I am not going to go into detail - there are plenty of other postings out there

but what I am going to do is to summarise how we can use these differences

sys.dm_db_index_usage_stats

Is cleared when service starts / reboots etc. So keep that firmly in mind

Good to help identify if an index is used - no entry unless it is

Good to help identify if a table is used - no entry unless it is

Has handy dates can help show when types of activity were last used

Has counts to help identify frequency of use

If machine has been up for the entire period that encapsulates all usage then can highlight unused indexes and consider their removal

 

sys.dm_db_index_operational_stats

Exists when table / indexes are created

Shows volumes of activity (plan, rows, pages), not just an instance

Far more detailed to help identify what type of activity

Shows row_lock_wait_count - indicating lock contention

Can measure the cost of having an index, or missing one

 

Lets look at the different types of activity that is going to be recorded:

 

SQL Statement   Read Write

Select          Yes  No

Insert          No   Yes on all indexes

Update          Yes  Yes if row affects the index

Delete          Yes  Yes

 

Armed with the above table, looking at DMV's activity, you can soon work out where possible areas of further inspection are. Now go read books on line for a more detailed understanding of what each column is saying...

 

http://msdn.microsoft.com/en-us/library/ms188755.aspx

http://msdn.microsoft.com/en-us/library/ms174281.aspx

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

LOG, wow, I have used it in my studies somewhere!!!! J

Anyway, You may be wonder when you use LOG function in Excel and in SQL Server. You will get different values in both LOG function, Is It bug? NO, IT IS NOT.

First of all, let us try it practically. Open your Excel workbook and type =LOG(1.5) in any of the cell and you will get results 0.176091 and when you try to do the same in SQL Server by executing following command, you will get results 0.405465108108164.

select LOG(1.5) --results will be 0.405465108108164

Why these are behaving like that? Well, there is a reason, when you execute LOG function is excel, it will have base 10 and in SQL Server LOG function is by default natural. If you want to get same results like SQL Server in Excel use =LN(1.5) rather than =Log(1.5).

In SQL Server, if you want to get same results as =LOG(1.5) of Excel than use  select LOG10(1.5)  rather than  select LOG(1.5).

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Today I helped one person in one forum, he wanted to generate script to make all columns in all tables NOT NULL and wanted to set DEFAULT value 0 for all numeric data types. I felt to share it with all my readers as it could be interesting to know and sometime could be very helpful and handy with some customize changes.

select

not_null = 'alter table ' + table_name + ' alter column '

                  + column_name + ' ' + data_type

                  + case when data_type = 'numeric' then '(' else '' end

                  + case when data_type = 'numeric' then convert(varchar,numeric_precision_radix) else '' end

                  + case when data_type = 'numeric' then ',' else '' end

                  + case when data_type = 'numeric' then convert(varchar,numeric_scale) else '' end

                  + case when data_type = 'numeric' then ')' else '' end

                  + ' not null '

,default_0 = 'alter table ' + table_name

                  + ' add default 0 for ' + column_name

from information_schema.columns

where COLUMN_DEFAULT is null

and   data_type not like '%char%'

and   data_type not like '%time%'

and   data_type not like '%image%'

and   data_type not like '%binary%'

 

Above query will list the T-SQL script which you can run in your query editor.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Today I was digging about principal and member of that principal from sys.database_principal. It might be handy and very useful sometime to list out all users’ list with its associated principal name. I have used sys.database_pricipal to get details about principal and its member’s details and sys.database_role_members for relationship of principal and its member.

Have a look at my T-SQL

SELECT

MemPri.Name as MemberPrincipal,     

RolePri.Name as RolePrincipal,

MemPri.Create_Date as DateCreated,

MemPri.Modify_date as DateModified,

MemPri.Type_Desc as Description

FROM

sys.database_role_members RoleMem

JOIN sys.database_principals RolePri ON RoleMem.role_principal_id = RolePri.principal_id

JOIN sys.database_principals MemPri ON RoleMem.member_principal_id = MemPri.principal_id

order by MemberPrincipal

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

We often need to check whether SQL Server installed on the server before installing our application developed in VB.NET or in C#. To find out any service installed on your server, you have to use ServiceProcess class from System. You can’t directly call System.ServiceProcess in your VB.NET or in C#, you have to first ADD REFERENCE to your application from “.NET” tab of ADD REFERENCE dilog box. Once you are done with adding reference, you can call serviceProcess

VB.NET
Imports System.ServiceProcess

C#
using System.ServiceProcess

Basically, ServiceProcess will give an array of all processes running on the box, you have to identify your service from that array. Generally SQL Server installed with default instance, which is MSSQLSERVER, you can simply check that instance name from array, if client machine has not installed with SQL server with default instance than there one small ray of hope is, you can check array item with substring item “SQL”, this way is not 100% sure shot, if named instance is completely different and doesn’t contain “SQL” word at all, this method won’t work, you are out of luck in this case.

Let us have a look at code in VB.NET, however, you can simply modify your syntax and made it work in C# as well.

Imports System.ServiceProcess

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim services As ServiceController()

        services = ServiceController.GetServices()

        For i As Integer = 0 To services.Length

            If (services(i).ServiceName.ToLower() = "mssqlserver") Then

                Label1.Text = "Service Found!!!"

                Exit For

            End If

        Next i

    End Sub

End Class

Create one windows application in VB.NET, call System.ServiceProcess namespace and have above code in your Form1’s page load event. Result will be printed on the label1 of the form1.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

We generally used to find primary key or foreign key by SSMS or by using SP_help for one table. Sometime we need to list out all primary key and/or foreign key and/or CHECK constraint and/or UNIQUE key available in database for all table and columns. How can we do that? I had generated very small and handy script for my personal use which I am going to share with you now.

--not only primary key, you can find

--CHECK

--FOREIGN KEY

--PRIMARY KEY

--UNIQUE KEY

select p.table_name,c.column_name,p.constraint_name

from

information_schema.table_constraints p join

information_schema.key_column_usage c

ON

p.table_name=c.table_name

and

p.constraint_name=c.constraint_name

where p.constraint_type='Primary Key'

order by p.table_name,c.column_name

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

We often need to delete data from table based on JOIN.  In that case, what we do is, try to execute SELECT query with JOIN, we find and confirm the records we want to delete and then remove SELECT statement and write DELETE instead. But this will not work in case of JOIN. We need to give table alias explicitly to tell SQL Server Engine that what table we need to include in delete. Have a look at following case.

--create table1 for demo

create table Orders1

(

      OrderID INT not null constraint pk_ordid primary key,

      orderdate datetime

)

--create table2 for demo

create table orderDetails1

(

OrderDetailsID int not null,

OrderID INT not null constraint fk_ordid references Orders1(OrderID)

)

--insert records

insert into orders1

select 1,getdate() union all

select 2,getdate() union all

select 3,getdate() union all

select 4,getdate()

insert into orderdetails1

select 1,3 union all

select 2,2 union all

select 3,3 union all

select 4,1 union all

select 5,2

GO

--try to select records which you want to delete

select o.OrderID,o.orderDate,od.OrderDetailsID

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

order by o.orderid

--now try to delete records by removeing

--SELECT statement and put DELETE instead.

--it will not work, as SQL Engine will be confused,

--what to delete, orders1? or orderdetails1?

delete

--select o.OrderID,o.orderDate,od.OrderDetailsID

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

--order by o.orderid

--here is the solution, you have to specify

--I need to delete from od (OrderDetails)

delete od

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com