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

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
 

Comparing data between two tables in SQL Server

Hi SQL Server Folks,

Today I got a requirement from one of my friend to comare the data between two tales in SQL Server 2005.  It can be achieved using many ways including built-in features like Checksum and TableDiff utility. However, I would like to share an alternate way to achieve the objective, which might be useful in some situations.

Usually the intention of comparison is to find out the missing rows from either of the tables or both. This where the operator “EXCEPT” comes very handy. Let us see it with an example.

 –Create two Tables–

CREATE TABLE TableA(ID Int, Name Varchar(256))

GO

CREATE TABLE TableB(ID Int, Name Varchar(256))

GO

INSERT INTO TableA VALUES(1,’A’),(2,’B’),(3,’D’)

INSERT INTO TableB VALUES(1,’A’),(2,NULL),(3,’C’)

–Get rows from TableA that are not found in TableB–

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB

–Get rows from TableB that are not found in TableA–

SELECT * FROM TableB

EXCEPT

SELECT * FROM TableA

The comparison can’t be performed for some of the data types of the columns like XML, Text and Image etc. So, I have written a generic stored procedure that does the comparison between two tables by excluding the columns that can’t be compared. It also allows to compare tables across databases and schemas. Validations and error handling are not added as the intention is to just present the idea.

 ———————————————————————————————————-

CREATE PROCEDURE CompareTableData

(

@SourceDB sysname

,@SourceSchema sysname

,@SourceTable sysname

,@TargetDB sysname

,@TargetSchema sysname

,@TargetTable sysname

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL NVarchar(Max)

DECLARE @ColList Varchar(Max)

 

–Concatenate the column list by excluding the data types that can’t be used in comparision–

 

SET @SQL = ‘SELECT @ColList = CASE WHEN @ColList IS NULL THEN ”” ELSE @ColList + ”,” END + SC.Name FROM ‘ + @SourceDB + ‘.sys.columns SC ‘

SET @SQL = @SQL + ‘ INNER JOIN ‘ + @SourceDB + ‘.sys.Types ST ON SC.system_type_id = ST.system_type_id WHERE object_id = object_id(@SourceTable) ‘

SET @SQL = @SQL + ‘ AND ST.Name NOT IN (”xml”,”Text”,”Image”,”Geometry”,”Geography”)’

EXEC sp_executesql @SQL,N’@ColList varchar(Max) OUTPUT,@SourceTable sysname’,@ColList OUTPUT,@Sourcetable

 

–Get the rows that are missing from Target table–

 

SET @SQL = ‘SELECT ”’ + @TargetTable + ”’ as RowMissingFrom,’ + @ColList + ‘ FROM ‘ + @SourceDB + ‘.’ + @SourceSchema + ‘.’ + @SourceTable

SET @SQL = @SQL + ‘ EXCEPT ‘

SET @SQL = @SQL + ‘ SELECT ”’ + @TargetTable + ”’ as RowMissingFrom,’ + @ColList + ‘ FROM ‘ + @TargetDB + ‘.’ + @TargetSchema + ‘.’ + @TargetTable

EXEC sp_executesql @SQL,N’@ColList varchar(Max)’,@ColList

 

–Get the rows that are missing from Source table–

 

SET @SQL = ‘ SELECT ”’ + @SourceTable + ”’ as RowMissingFrom,’ + @ColList + ‘ FROM ‘ + @TargetDB + ‘.’ + @TargetSchema + ‘.’ + @TargetTable

SET @SQL = @SQL + ‘ EXCEPT ‘

SET @SQL = @SQL + ‘SELECT ”’ + @SourceTable + ”’ as RowMissingFrom,’ + @ColList + ‘ FROM ‘ + @SourceDB + ‘.’ + @SourceSchema + ‘.’ + @SourceTable

EXEC sp_executesql @SQL,N’@ColList varchar(Max)’,@ColList

END

 

Hope it will Help Someone !!!

Thanks for reading this Blog.

SQL Server 2008 R1 Client Tools

Hi SQL Server Folks,

I got a requiremet from one of my customer to install client tools but for SQL Server 2008 R1. It was very simple in SQL Server 2005 i.e. in the binaries for SQL Server 2005 you have two folders name as “Server” & “Tool” so if you want to install only client tools then simply open the folder name as “Tool” and run the setup.exe and your task is done. But in the binaries of SQL Server 2008 there is nothing like that i.e. all servers and client related components are mixed. Below is the script that I wrote to make SQL Server 2008 R1 client tool.

Open the command prompt and go in to the path where your SQL Server binaries are there for ex. C:\Gaurav\SQL Server 2008 R2 

 set source=C:\Gaurav\SQL Server 2008 R2

set dest=C:\SQLFULL_ENU_ToolsOnly

 REM Copy Root ONLY

robocopy %source% %dest%

REM Copy Root ONLY

robocopy %source%\x86 %dest%\x86 /XF fixsqlregistrykey_ia64.exe

robocopy %source%\x64 %dest%\x64 /XF fixsqlregistrykey_ia64.exe

 robocopy %source%\x86\1033 %dest%\x86\1033

robocopy %source%\x64\1033 %dest%\x64\1033

 robocopy %source%\x86\help %dest%\x86\help /S

robocopy %source%\x64\help %dest%\x64\help /S

 REM Copy redists

 robocopy %source%\x86\redist\DotNetFrameworks %dest%\x86\redist\DotNetFrameworks /S /XF *ia64*

robocopy %source%\x64\redist\DotNetFrameworks %dest%\x64\redist\DotNetFrameworks /S /XF *ia64*

 robocopy %source%\x86\redist\Powershell %dest%\x86\redist\Powershell /S /XD ia64

robocopy %source%\x64\redist\Powershell %dest%\x64\redist\Powershell /S /XD ia64

 robocopy %source%\x86\redist\Watson %dest%\x86\redist\Watson /S

robocopy %source%\x64\redist\Watson %dest%\x64\redist\Watson /S

 robocopy “%source%\x86\redist\Windows Installer” “%dest%\x86\redist\Windows Installer” /S /XD ia64

robocopy “%source%\x64\redist\Windows Installer” “%dest%\x64\redist\Windows Installer” /S /XD ia64

 REM Setup Folder and exclude folders and files that aren’t needed

robocopy %source%\x86\Setup %dest%\x86\Setup /S /XD sql_engine_core_inst_loc_msi sql_engine_core_inst_msi sql_engine_core_shared_loc_msi sql_engine_core_shared_msi /XF rsfx.msi rsSharePoint.msi sql_as.msi sql_fulltext.msi sql_is.msi sql_rs.msi sqlbrowser.msi Synchronization.msi SyncServicesADO.msi sql_as_loc.msi sql_is_loc.msi sql_rs_loc.msi sqlserver2005_bc.msi sqlwriter.msi

 robocopy %source%\x64\Setup %dest%\x64\Setup /S /XD sql_engine_core_inst_loc_msi sql_engine_core_inst_msi sql_engine_core_shared_loc_msi sql_engine_core_shared_msi /XF rsfx.msi rsSharePoint.msi sql_as.msi sql_fulltext.msi sql_is.msi sql_rs.msi sqlbrowser.msi Synchronization.msi SyncServicesADO.msi sql_as_loc.msi sql_is_loc.msi sql_rs_loc.msi sqlserver2005_bc.msi sqlwriter.msi

After running this above script your SQL Server 2008 R1 client tools are reday and it will be save in your destination location that you defined above in script, in our case this location is C:\SQLFULL_ENU_ToolsOnly .

Instructions to use SQL 2008 R1 Client Tools

  1. Copy the whole folder “SQL 2008 Client Tools”  on machine where you want to install it.
  2. For 32-bit machine open “SQL 2008 Client Tools”  folder  and open the file name as “MyConfigurationFile.ini” in Notepad. After opening it search the text as “MEDIASOURCE” and in that field specify the location where you copied “SQL 2008 Client Tools” folder and save the changes in this “MyConfigurationFile.ini” . Now search the text name as “INSTALLSHAREDWOWDIR=”C:\Program Files (x86)\Microsoft SQL Server” and delete this text from this file and save the changes in this “MyConfigurationFile.ini” .
  3.  For 64-bit machine open “SQL 2008 Client Tools”  folder  and open the file name as “MyConfigurationFile.ini” in Notepad. After opening it search the text as “MEDIASOURCE” and in that field specify the location where you copied “SQL 2008 Client Tools” folder.
  4.  Now Open the command prompt and go in to “SQL 2008 Client Tools”  folder  location and type the parameter as “Setup.exe /q /ACTION=Install /FEATURES=Tools /ConfigurationFile=MyConfigurationFile.INI” and wait to install SQL 2008 client tools.
  5.  After finishing this task go in to Start à Programs and check for “Microsoft SQL Server 2008”.

Hope it will help someone. Stay tune for more interesting SQL Server stuff’s.

Thanks.