Script to identify heavily used tables and indexes in your database

Hi SQL Server Folks – There are many situation comes when we have a requirement in SQL Server to identify heavily used indexes in a database OR heavily used tables in a database. Below are the queries that can help you to find these things in SQL 2005\2008\R2.

Query to find heavily used indexes in your database :

Use database_name
go

SELECT CAST(OBJECT_NAME(usg.OBJECT_ID) AS VARCHAR(30)) AS ‘Table’,
CAST(idx.name AS VARCHAR(30)) AS ‘Index’,
usg.user_seeks + usg.user_scans + usg.user_lookups AS ‘Reads’,
usg.user_updates AS ‘Writes’
FROM sys.dm_db_index_usage_stats AS usg
INNER join sys.indexes AS idx
ON usg.OBJECT_ID = idx.OBJECT_ID
and idx.index_id = usg.index_id
WHERE OBJECTPROPERTY(usg.OBJECT_ID,’isusertable’) = 1
ORDER BY ‘Table’;
go

Query to find heavily used tables in your database :

Use database_name
go

IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(TableName NVARCHAR(255), UserSeeks DEC, UserScans DEC, UserUpdates DEC)
INSERT INTO #Temp
EXEC sp_MSForEachDB ‘USE [?]; IF DB_ID(”?”) = 6
BEGIN
SELECT DB_NAME() + ”.” + object_name(b.object_id), a.user_seeks, a.user_scans, a.user_updates
FROM sys.dm_db_index_usage_stats a
RIGHT OUTER JOIN [?].sys.indexes b on a.object_id = b.object_id and a.database_id = DB_ID()
WHERE b.object_id > 100
END’

SELECT TableName as ‘Table Name’, sum(UserSeeks + UserScans + UserUpdates) as ‘Total Accesses’,
sum(UserUpdates) as ‘Total Writes’,
CONVERT(DEC(25,2),(sum(UserUpdates)/sum(UserSeeks + UserScans + UserUpdates)*100)) as ‘% Accesses are Writes’,
sum(UserSeeks + UserScans) as ‘Total Reads’,
CONVERT(DEC(25,2),(sum(UserSeeks + UserScans)/sum(UserSeeks + UserScans + UserUpdates)*100)) as ‘% Accesses are Reads’,
SUM(UserSeeks) as ‘Read Seeks’, CONVERT(DEC(25,2),(SUM(UserSeeks)/sum(UserSeeks + UserScans)*100)) as ‘% Reads are Index Seeks’,
SUM(UserScans) as ‘Read Scans’, CONVERT(DEC(25,2),(SUM(UserScans)/sum(UserSeeks + UserScans)*100)) as ‘% Reads are Index Scans’
FROM #Temp
GROUP by TableName
HAVING sum(UserSeeks + UserScans) > 0
ORDER by sum(UserSeeks + UserScans + UserUpdates) DESC
DROP table #Temp

Note :

This script makes use of the sys.dm_db_index_usage_stats SQL Dynamic Management View. The values returned by this DMV do not persist beyond server restarts. This means the information it gathers is only valid since the last SQL Server restart or (less likely) database attach. So, if you just re-started your database server you are not going to get good numbers. Also, this information is kept in cache and is subject to memory pressure flushing, not a likely scenario but possible.

The script will gather index information from which one can infer table access information. It uses the sp_MSForEachDB stored procedure to run through all databases on the instance, places the information in a temporary table, sums the values for index reads and writes, does a bunch of fancy math, rolls it up to the table level and returns the results to show reads, writes, percent of each and the type of index read.

Hope you’ll find this as useful. Thanks.

Advertisements

Update Statistics for all db’s excluding system db’s

Hi SQL Server Folks,

Sometimes it requires to update the stastics for all user db’s except of system db’s. Below is the queriey that you can use for it. In that script you can specify any database that you want to exclude to update the statistics.

DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)
ORDER BY [name]

OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘USE [‘ + @DB +’]’ + CHAR(13) + ‘EXEC sp_updatestats’ + CHAR(13)
PRINT @SQL
exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END

CLOSE curDB
DEALLOCATE curDB

Hope it’ll be usefull for you.

Cheers,
Gaurav Shrivastav

What is Hypothetical Index and how to delete it

Hi Folks,

In this post I’m going to tell you what is Hypothetical Index and how to remove them :

When Database Engine Tuning Advisor runs on a database, it makes some recommendations. For all the recommendations it actually creates the objects on the database. The recommendations can be Indexes, Stats etc. They are called Hypothetical Indexes/Statistics. If Database Engine Tuning Advisor is closed normally, it ensures that all the objects that it had created are cleaned up. If for some reason, it does not exit normally, then these objects will still remain on the database.

Since these objects are hypothetical, they can be removed manually. These objects’ name will start with “_dta”. They can also be identified with the below query.

select * from sys.indexes where is_hypothetical = 1

To delete these type of indexes use below command :

USE <My Database>
go

DROP INDEX  <Schema_Name> . <Table_Name> . <Index_Name>
go

Cheers :
Gaurav Shrivastav

Query to Find out Exact Duplicate Indexes in SQL Server

Hi Folks,

Below is the query to find out exact duplicate indexes in a database. This query finds exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints.

 

— exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path(”)) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path(”)) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’,
c1.name as ‘index’,
c2.name as ‘exactduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

Hope it’ll be usefull !!

Cheers,
Gaurav Shrivastav
 

Query to find out Un-used Indexes in SQL Server

Hi Folks,

You all knows that to improve the seraching mechanism in SQL Server indexes plays a Vital role but sometimes to improve the performance its necessary to delete OR offline the existing indexes. The Indexes that are not frequently used OR a table that is having more writes compare than Reads then you should delete those indexes OR offline them , Becuase whenever you are performing a write statement then SQL Server also updates the index pages which requires resources to do that and its time consuming, result in bad Performance. Below is the query that will guide you to find out un-used indexes.

 

SELECT
sch.name + ‘.’ + t.name AS [Table Name],
i.name AS [Index Name],
i.type_desc,
ISNULL(user_updates,0) AS [Total Writes],
ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
s.last_user_seek,
s.last_user_scan ,
s.last_user_lookup,
ISNULL(user_updates,0) – ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],
p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,’IsUserTable’) = 1
INNER JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
–AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) –shows all indexes including those that have not been used
–AND ISNULL(user_updates,0) – ISNULL((user_seeks + user_scans + user_lookups),0)>0 –only shows those indexes which have been used
–AND i.index_id > 1 — Only non-first indexes (I.E. non-primary key)
–AND i.is_primary_key<>1 — Only those that are not defined as a Primary Key)
–AND i.is_unique_constraint<>1 — Only those that are not classed as “UniqueConstraints”.
ORDER BY [Table Name], [index name]

 

Hope it will be usefull for you !!!

Thanks.

How to Find the Actual Host Name on Hyper-V machines

Hi Guys,

Sometimes it’s necessary to find out host machine information from a particular guest virtual machine.

In VmWare Machines it’s very easy to find the hostname, You need to just open the command prompt and type there “HOSTNAME”, it will give you Physical Host Name. But this command doesn’t works in Hyper-V machines. If you will fire that command in Hyper-V m/c then it will give you still virtual machine name insead of physical m/c name.

With Hyper-V Integration Services installed, the following registry key contains information about the Host server:

HKLM\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters

Some keys of interest are:

  • HostName
  • PhysicalHostName
  • PhysicalHostNameFullyQualified
  • VirtualMachineName
  • Plus a number of settings about the Host Server’s version

In this registry path you can find all information like physical host name and Virtual m/c name etc.

Hope you will find it as usefull stuff.

Top Queries in the SQL that is consuming most of the memory of Server

Hi SQL Server Guys,

It has been many times happens when performance of the SQL Server goes down, Then we are trying to find out out the cause of this. Frankly say there can be multiple issues for that which can cause for performance but one area is that where we can find out what are the queries that is currently running in the SQL Server and they only causing the problem.

Below are the queries that help you to find out memory contentation in SQL Server.

/* This query returns back the queries that use the most IO. This can mean that either the query is reading from disk more than usual or occupying and utilizing a large amount of buffer cache. These are typical symptoms of queries that do not have the proper indexesor queries that simply read a lot of data.*/

/**********************************************************

* Top procedures memory consumption per execution

* (this will show mostly reports & jobs)

***********************************************************/

SELECT TOP 100 *FROM

( SELECT DatabaseName =DB_NAME(qt.dbid)

,ObjectName  =OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)

FROM sys.dm_exec_query_statsAS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS qt

GROUP BY DB_NAME(qt.dbid),OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)T ORDERBY IO_Per_Execution DESC

/**********************************************************

* Top procedures memory consumption total

* (this will show more operational procedures)

***********************************************************/

SELECT

TOP 100 *FROM

(

SELECT DatabaseName =DB_NAME(qt.dbid)

,ObjectName =OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Total_IO_Reads     =SUM(qs.total_physical_reads + qs.total_logical_reads)

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)

FROM

sys.dm_exec_query_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

GROUP

BYDB_NAME(qt.dbid),OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY Total_IO_Reads DESC

/**********************************************************

* Top adhoc queries memory consumption total

***********************************************************/

SELECT

TOP 100 *FROM

(

SELECT DatabaseName =DB_NAME(qt.dbid)

,QueryText  = qt.text

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Total_IO_Reads     =SUM(qs.total_physical_reads + qs.total_logical_reads)

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)


FROM

sys.dm_exec_query_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

WHERE

OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)ISNULL

GROUP

BYDB_NAME(qt.dbid), qt.text,OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY Total_IO_Reads DESC

/**********************************************************

* Top adhoc queries memory consumption per execution

***********************************************************/

SELECT

TOP 100 *FROM

(

SELECT DatabaseName =DB_NAME(qt.dbid)

,QueryText  = qt.text

,DiskReads  =SUM(qs.total_physical_reads)   — The worst reads, disk reads

,MemoryReads  =SUM(qs.total_logical_reads)    –Logical Reads are memory reads

,Total_IO_Reads     =SUM(qs.total_physical_reads + qs.total_logical_reads)

,Executions =SUM(qs.execution_count)

,IO_Per_Execution   =SUM((qs.total_physical_reads + qs.total_logical_reads)/ qs.execution_count)

,CPUTime    =SUM(qs.total_worker_time)

,DiskWaitAndCPUTime =SUM(qs.total_elapsed_time)

,MemoryWrites =SUM(qs.max_logical_writes)

,DateLastExecuted   =MAX(qs.last_execution_time)


FROM

sys.dm_exec_query_statsAS qs

CROSS

APPLYsys.dm_exec_sql_text(qs.sql_handle)AS qt

WHERE

OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)ISNULL

GROUP

BYDB_NAME(qt.dbid), qt.text,OBJECT_SCHEMA_NAME(qt.objectid,dbid)+‘.’+OBJECT_NAME(qt.objectid, qt.dbid)

)

T ORDERBY IO_Per_Execution DESC

 

Hope it will be usefull.