Database Reads/Writes Hot spots

Found following script useful to get database reads/writes hot spots. Must care that these cumulative statistics are from instance start date.

Reference: http://www.dbafire.com/2014/05/26/identify-top-ten-table-hotspots-sql-server/

 
--SQL Script begin
IF OBJECT_ID('tempdb..#HotSpots') IS NOT NULL
DROP TABLE #HotSpots
GO

CREATE TABLE #HotSpots
(Table_Name NVARCHAR(255), Seeks DEC, Scans DEC, Updates DEC)
INSERT INTO #HotSpots
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

SELECT Top 100 Table_Name as 'Table Name', sum(Seeks + Scans + Updates) as 'Total Accesses',
sum(Updates) as 'Total Writes',
CONVERT(DEC(25,2),(sum(Updates)/sum(Seeks + Scans + Updates)*100)) as '% Accesses are Writes',
sum(Seeks + Scans) as 'Total Reads',
CONVERT(DEC(25,2),(sum(Seeks + Scans)/sum(Seeks + Scans + Updates)*100)) as '% Accesses are Reads',
SUM(Seeks) as 'Read Seeks', CONVERT(DEC(25,2),(SUM(Seeks)/sum(Seeks + Scans)*100)) as '% Reads are Index Seeks',
SUM(Scans) as 'Read Scans', CONVERT(DEC(25,2),(SUM(Scans)/sum(Seeks + Scans)*100)) as '% Reads are Index Scans'
FROM #HotSpots
GROUP by Table_Name
HAVING sum(Seeks + Scans) > 0
ORDER by sum(Seeks + Scans + Updates) DESC
DROP table #HotSpots
--SQL Script end

Advertisements

Execute Script Using CMDSQL

Large scripts are hard to open in SQL Server Management Studio. These scripts can be easily executed through PowerShell as following:

 Invoke-Expression 'sqlcmd -S ServerNameHere -d DatabaseNameHere -U UserNameHere -P PasswordHere -i "D:\MyScript.sql"'

1-Place your script at drive as SQL

2-Execute PowerShell from SSMS

3-Paste above mentioned script after making required changes and press enter

Script: Remove Ghost Characters


/*
Script By: Aasim Abdullah
For: ReConnectSQL and http://www.connectsql.com
Purpose: to remove special characters from data
*/
Create FUNCTION [dbo].[RemoveGhostCharacters] (@s varchar(MAX)) returns varchar(MAX)
with schemabinding
BEGIN
if @s is null
return null
declare @s2 varchar(MAX)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 32 and 126 OR @C BETWEEN 156 AND 157
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
END
GO

Continue reading “Script: Remove Ghost Characters”

Creating NC Indexes Repository

---Get all indexes basic properties

 SELECT  '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,

            Ind.type_desc,

            CASE Ind.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END UniqueOrNot,

            CASE fill_factor WHEN 0 THEN 100 ELSE fill_factor END fill_factor,

        Ind.[name] AS IndexName,

        SUBSTRING(( SELECT  ', [' + AC.name + CASE IC.is_descending_key WHEN

                                                                              1 THEN '] DESC' ELSE ']' END

                    FROM    sys.[tables] AS T

                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

                                                                 AND I.[index_id] = IC.[index_id]

                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

                                                               AND IC.[column_id] = AC.[column_id]

                    WHERE   Ind.[object_id] = I.[object_id]

                            AND Ind.index_id = I.index_id

                            AND IC.is_included_column = 0

                    ORDER BY IC.key_ordinal

                  FOR

                    XML PATH('') ), 2, 8000) AS KeyCols,

        SUBSTRING(( SELECT  ', [' + AC.name +']'

                    FROM    sys.[tables] AS T

                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

                                                                 AND I.[index_id] = IC.[index_id]

                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

                                                               AND IC.[column_id] = AC.[column_id]

                    WHERE   Ind.[object_id] = I.[object_id]

                            AND Ind.index_id = I.index_id

                            AND IC.is_included_column = 1

                    ORDER BY IC.key_ordinal

                  FOR

                    XML PATH('') ), 2, 8000) AS IncludeCols

INTO #ALLindexes                   

FROM    sys.[indexes] AS Ind

        INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]

        INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]

            LEFT OUTER JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id

                                    AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID()

WHERE  Ind.type_desc ='NONCLUSTERED'

--AND Tab.name  = 'YourTableNameHere' -- uncomment to get single table indexes detail

AND Ind.is_unique <> 1

ORDER BY TableName




---Get create script as separate column

SELECT *,

'CREATE NONCLUSTERED INDEX ['+IndexName+ '] ON ' + TableName + '('+KeyCols+')' +

 CASE  WHEN IncludeCols IS NOT NULL

                  THEN ' INCLUDE ('+IncludeCols+') WITH (FILLFACTOR = '+CAST(fill_factor AS VARCHAR(10))+')'

                              ELSE ' WITH (FILLFACTOR = '+CAST(fill_factor AS VARCHAR(10))+')' END AS Script

FROM #ALLindexes







---drop table when not required

DROP TABLE #ALLindexes