Blog ala Vidar

SQL, AppFrame and other cool technologies

Scripts – FIND

Cache Usage Per Table

WITH memusage_CTE 
AS 
(
	SELECT
	  bd.database_id, bd.file_id, bd.page_id, bd.page_type,
	  COALESCE(p1.object_id, p2.object_id) AS object_id,
	  COALESCE(p1.index_id, p2.index_id) AS index_id, bd.row_count,
	  bd.free_space_in_bytes, CONVERT(TINYINT, bd.is_modified) AS DirtyPage
		FROM sys.dm_os_buffer_descriptors AS bd 
		INNER JOIN sys.allocation_units AS au 
			ON au.allocation_unit_id = bd.allocation_unit_id 
		OUTER APPLY (SELECT	TOP(1) 
					  p.object_id, p.index_id 
						FROM sys.partitions AS P
						WHERE P.hobt_id = au.container_id 
						  AND au.type IN (1, 3)) AS p1 
		OUTER APPLY (SELECT	TOP(1) 
					  p.object_id, p.index_id 
						FROM sys.partitions AS p 
						WHERE p.partition_id = au.container_id 
						  AND au.type = 2) AS p2 
		WHERE bd.database_id = DB_ID() 
		  AND bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE') 
)

SELECT TOP 20 
  DB_NAME(database_id) AS DatabaseName,
  OBJECT_NAME(object_id,database_id) AS TableName,
  index_id, COUNT(*) AS PagesInCache,
  SUM(dirtyPage) AS DirtyPages
	FROM memusage_CTE 
	GROUP BY database_id, object_id, index_id 
	ORDER BY COUNT(*) DESC

Databases with Last Clean DBCC Date

SET NOCOUNT ON;

CREATE TABLE #temp (
	ParentObject VARCHAR(255),
	[Object] VARCHAR(255),
	Field VARCHAR(255),
	[Value] VARCHAR(255)
)

CREATE TABLE #DBCCResults (
	DBName VARCHAR(255),
	LastCleanDBCCDate DATETIME
)

EXEC master.dbo.SP_MSFOREACHDB
	@Command1 = 'USE [?] INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')',
	@Command2 = 'INSERT INTO #DBCCResults 
		SELECT ''?'', value FROM #temp WHERE field = ''dbi_dbccLastKnownGood''',
	@Command3 = 'TRUNCATE TABLE #temp'   

;WITH DBCC_CTE AS
(
	SELECT 
	  ROW_NUMBER() OVER (PARTITION BY DBName, LastCleanDBCCDate 
						ORDER BY LastCleanDBCCDate) RowID
		FROM #DBCCResults
)

DELETE FROM DBCC_CTE WHERE RowID > 1;

SELECT        
  DBName, LastCleanDBCCDate
	FROM #DBCCResults
	ORDER BY 2

DROP TABLE #temp, #DBCCResults;

Days Online (Uptime)

SELECT 
  DATEDIFF(D, create_date, GETDATE()) AS DaysOnline
	FROM master.sys.databases 
	WHERE name = 'tempdb'

Index Info for One Table

DECLARE @objectID INT = OBJECT_ID('stbl_System_Users');
 
WITH indexCTE (partition_scheme_name, partition_function_name, data_space_id) 
AS (
	SELECT 
	  PS.name, PF.name, PS.data_space_id
		FROM sys.partition_schemes AS PS
		INNER JOIN sys.partition_functions AS PF ON PS.function_id = PF.function_id
)
 
SELECT 
  T.name AS 'table_name', ISNULL(I.name, '') AS 'index_name', I.object_id, I.index_id, 
  CAST(CASE WHEN I.index_id = 1 
                THEN 'clustered' 
            WHEN I.index_id =0
                THEN 'heap'
            ELSE 'nonclustered' END
		+ CASE WHEN I.ignore_dup_key <> 0 
            THEN ', ignore duplicate keys' 
                ELSE '' END
		+ CASE WHEN I.is_unique <> 0 
            THEN ', unique' 
                ELSE '' END
		+ CASE WHEN I.is_primary_key <> 0 
            THEN ', primary key' ELSE '' END AS VARCHAR(210)
        ) AS index_description, 
  ISNULL(REPLACE(REPLACE(REPLACE((
		SELECT C.name AS 'columnName'
			FROM sys.index_columns AS IC
			INNER JOIN sys.columns AS C 
				ON C.column_id = IC.column_id 
				  AND C.object_id = IC.object_id
			WHERE IC.OBJECT_ID = I.OBJECT_ID
			  AND IC.index_id = I.index_id
			  AND IC.is_included_column = 0
			ORDER BY IC.index_column_id
			FOR XML RAW), 
	'"/><row columnName="', ', '), 
	'<row columnName="', ''), 
	'"/>', ''), '') AS indexed_columns, 
  ISNULL(REPLACE(REPLACE(REPLACE((   
		SELECT C.name AS 'columnName'
			FROM sys.index_columns AS IC
			INNER JOIN sys.columns AS C 
				ON c.column_id = IC.column_id 
				  AND C.object_id = IC.object_id
			WHERE IC.OBJECT_ID = I.OBJECT_ID
			  AND IC.index_id = I.index_id
			  AND IC.is_included_column = 1
			ORDER BY IC.index_column_id
			FOR XML RAW), 
	'"/><row columnName="', ', '), 
	'<row columnName="', ''), 
	'"/>', ''), '') AS included_columns, 
  I.filter_definition, ISNULL(CTE.partition_scheme_name, '') AS partition_scheme_name, 
  COUNT(partition_number) AS 'partition_count', SUM(rows) AS row_count
	FROM sys.indexes AS I
	INNER JOIN sys.partitions AS P 
		ON I.object_id = P.object_id 
		  AND I.index_id = P.index_id
	INNER JOIN sys.tables AS T 
		ON I.object_id = T.object_id
	LEFT OUTER JOIN indexCTE AS CTE 
		ON I.data_space_id = CTE.data_space_id
	WHERE I.object_id = ISNULL(@objectID, I.object_id)
	GROUP BY 
	  T.name, ISNULL(I.name, ''), I.object_id, I.index_id, 
	  CAST(CASE WHEN I.index_id = 1 
                THEN 'clustered' 
            WHEN I.index_id =0
                THEN 'heap'
            ELSE 'nonclustered' END
		+ CASE WHEN I.ignore_dup_key <> 0 
            THEN ', ignore duplicate keys' 
                ELSE '' END
		+ CASE WHEN I.is_unique <> 0 
            THEN ', unique' 
                ELSE '' END
		+ CASE WHEN I.is_primary_key <> 0 
            THEN ', primary key' ELSE '' END AS VARCHAR(210)), 
	  I.filter_definition, ISNULL(CTE.partition_scheme_name, ''),
	  ISNULL(CTE.partition_function_name, '')
	ORDER BY table_name, index_id

Last Usage of Table

DECLARE @TableName AS NVARCHAR(128)
SET @TableName = 'stbl_System_Users'

SELECT 
  DB_NAME(ST.[dbid]) AS Database_Name,
  OBJECT_NAME(ST.objectid, ST.[dbid]) AS SP_Name, 
  MAX(QS.last_execution_time) AS Last_Execution
	FROM sys.dm_exec_query_stats AS QS
	CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
	WHERE ST.[text] LIKE '%' + @TableName + '%'
      AND ST.[dbid] IS NOT NULL
	GROUP BY DB_NAME(ST.[dbid]), OBJECT_NAME(ST.objectid, ST.[dbid])
	ORDER BY Database_Name, SP_Name
	OPTION (MAXDOP 1);

Missing Indexes

WITH Indexes AS
(
	SELECT
	  DENSE_RANK() OVER (ORDER BY MIGS.est_impact DESC, MIG.index_handle) AS IndexRank,
	  DB_NAME(MID.database_id) AS DatabaseName,
	  REPLACE(REPLACE(RIGHT(MID.statement, 
		CHARINDEX('[', REVERSE(MID.statement))), '[', ''), ']', '') AS TableName,
	  MID.statement AS TableNameFull,
	  MIGS.unique_compiles, MIGS.user_seeks, MIGS.user_scans,
	  MIGS.avg_total_user_cost, MIGS.avg_user_impact,
	  MIGS.est_impact, MID.index_handle,
	  MID.equality_columns, MID.inequality_columns, MID.included_columns AS IncludedColumns,
	  CASE WHEN MID.equality_columns IS NOT NULL 
			  AND MID.inequality_columns IS NOT NULL 
				THEN MID.equality_columns + ', ' + MID.inequality_columns
			WHEN MID.equality_columns IS NOT NULL 
			  AND MID.inequality_columns IS NULL 
				THEN MID.equality_columns
			WHEN MID.equality_columns IS NULL 
			  AND MID.inequality_columns IS NOT NULL 
				THEN MID.inequality_columns
	  END AS IndexedColumns
		FROM sys.dm_db_missing_index_details AS MID
		INNER JOIN sys.dm_db_missing_index_groups AS MIG
			ON MIG.index_handle = MID.index_handle
		INNER JOIN (SELECT
					  group_handle, unique_compiles, user_seeks, user_scans,
					  avg_total_user_cost, avg_user_impact,
					  avg_total_user_cost * avg_user_impact
					  * (user_seeks + user_scans) AS est_impact
						FROM sys.dm_db_missing_index_group_stats) AS MIGS
			ON MIG.index_group_handle = MIGS.group_handle
		WHERE MIGS.user_seeks + MIGS.user_scans
			  > (SELECT
					DATEDIFF(D, create_date, GETDATE()) AS DaysOnline
					FROM master.sys.databases
					WHERE name = 'tempdb') -- At least once a day since startup
)

SELECT 
  IndexRank, DatabaseName, TableName, IndexedColumns, IncludedColumns,
  unique_compiles, user_seeks, user_scans, avg_total_user_cost, avg_user_impact, est_impact,
  'CREATE NONCLUSTERED INDEX [IX_' + TableName + '_MissingIndex_' 
  + CAST(IndexRank AS NVARCHAR(100)) + '] ON ' + TableNameFull + ' (' + IndexedColumns + ')' 
  + CASE WHEN IncludedColumns IS NOT NULL 
			THEN ' INCLUDE (' + IncludedColumns + ')' 
		ELSE '' 
  END AS CreateIndexStatement
	FROM Indexes
	ORDER BY IndexRank

Number of Occurences of One String

CREATE FUNCTION dbo.FindNumberOfOccurencesOfOneStringInAnother
(
	@Haystack NVARCHAR(MAX), 
	@Needle NVARCHAR(MAX)
)
RETURNS INT
BEGIN
	DECLARE @count AS INT
	SET @count = 0

	WHILE CHARINDEX(@Needle, @Haystack, 0) > 0
	BEGIN
		SET @Haystack = 
			SUBSTRING(@Haystack, CHARINDEX(@Needle, @Haystack) + 1, LEN(@Haystack))
		SET @count = @count + 1
	END

	RETURN @count
END

Percentage Complete of Query

SELECT
  R.session_id, R.command,
  CONVERT(NUMERIC(6, 2),R.percent_complete) AS PercentComplete,
  DATEADD(ms, R.estimated_completion_time, GETDATE()) AS ETACompletionTime,
  CONVERT(NUMERIC(10, 2), R.total_elapsed_time / 60000.0) AS ElapsedMinutes,
  CONVERT(NUMERIC(10, 2), R.estimated_completion_time / 3600000.0) AS ETAHours,
  CONVERT(NUMERIC(10, 2), R.estimated_completion_time / 60000.0) AS ETAMinutes,
  CONVERT(VARCHAR(1000), (SUBSTRING(T.text,
									R.statement_start_offset / 2,
									CASE WHEN R.statement_end_offset = -1 THEN 1000
										ELSE (R.statement_end_offset 
											- R.statement_start_offset) / 2
									END)))
	FROM sys.dm_exec_requests AS R
	CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS T
	WHERE R.command IN ('RESTORE DATABASE', 'BACKUP DATABASE')

Space Usage By Table

SELECT 
  [tablename], row_count,
  CASE 
	WHEN (((reserved * 1.0 /1024)/1024)/1024) >= 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(((reserved * 1.0 /1024)/1024)/1024))) + ' TB'
	WHEN ((reserved * 1.0 /1024)/1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),((reserved * 1.0 /1024)/1024))) + ' GB'
	WHEN (reserved * 1.0 /1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(reserved * 1.0 /1024))) + ' MB'
	ELSE CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),reserved * 1.0)) + ' KB'
  END As reserved,
  CASE 
	WHEN (((data * 1.0 /1024)/1024)/1024) >= 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(((data * 1.0 /1024)/1024)/1024))) + ' TB'
	WHEN ((data * 1.0 /1024)/1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),((data * 1.0 /1024)/1024))) + ' GB'
	WHEN (data * 1.0 /1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(data * 1.0 /1024))) + ' MB'
	ELSE CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),data * 1.0)) + ' KB'
  END As data,
  CASE 
	WHEN (((index_size * 1.0 /1024)/1024)/1024) >= 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(((index_size * 1.0 /1024)/1024)/1024))) + ' TB'
	WHEN ((index_size * 1.0 /1024)/1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),((index_size * 1.0 /1024)/1024))) + ' GB'
	WHEN (index_size * 1.0 /1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(index_size * 1.0 /1024))) + ' MB'
	ELSE CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),index_size * 1.0)) + ' KB'
  END As index_size,
  CASE 
	WHEN (((unused * 1.0 /1024)/1024)/1024) >= 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(((unused * 1.0 /1024)/1024)/1024))) + ' TB'
	WHEN ((unused * 1.0 /1024)/1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),((unused * 1.0 /1024)/1024))) + ' GB'
	WHEN (unused * 1.0 /1024) > = 1 
	  THEN CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),(unused * 1.0 /1024))) + ' MB'
	ELSE CONVERT(VARCHAR,CONVERT(DECIMAL(10,2),unused * 1.0)) + ' KB'
  END As unused
	FROM (SELECT
		  OBJECT_NAME(TS.object_id) AS [tablename],
		  TS.row_count,
		  (TS.reserved + ISNULL(IXS.reserved, 0)) AS reserved, 
		  TS.data,
		  (CASE WHEN (TS.used + ISNULL(IXS.used,0)) > TS.data 
				THEN (TS.used + ISNULL(IXS.used,0)) - TS.data 
				ELSE 0 
		   END) AS index_size,
		  (CASE WHEN (TS.reserved + ISNULL(IXS.reserved,0)) > TS.used 
				THEN (TS.reserved + ISNULL(IXS.reserved,0)) - TS.used 
				ELSE 0 
		   END) AS unused
			FROM (SELECT 
					PS.object_id,
					SUM (CASE WHEN (PS.index_id < 2) THEN row_count ELSE 0 END) AS row_count,
					SUM (PS.reserved_page_count) * 8 AS reserved,
					SUM (CASE WHEN (PS.index_id < 2) 
							THEN (PS.in_row_data_page_count + PS.lob_used_page_count 
								+ PS.row_overflow_used_page_count)
							ELSE (PS.lob_used_page_count + PS.row_overflow_used_page_count)
						END) * 8 AS data,
					SUM (PS.used_page_count) * 8 AS used
						FROM sys.dm_db_partition_stats AS PS
						GROUP BY PS.object_id) AS TS
			LEFT OUTER JOIN (SELECT 
								IT.parent_id,
								SUM(PS.reserved_page_count) * 8 AS reserved,
								SUM(PS.used_page_count) * 8 AS used
									FROM sys.dm_db_partition_stats AS PS
									INNER JOIN sys.internal_tables AS IT
										ON IT.object_id = PS.object_id
									WHERE IT.internal_type IN (202,204)
									GROUP BY IT.parent_id) AS IXS 
				ON IXS.parent_id = TS.object_id
			WHERE EXISTS (SELECT *
							FROM sys.all_objects
							WHERE object_id = TS.object_id
							  AND type NOT IN ('S', 'IT'))) AS X
	ORDER BY X.unused DESC

Tables without Clustered Index

SELECT O.name
       FROM sys.sysindexes AS I
       INNER JOIN sys.sysobjects AS O ON I.id = O.id
       WHERE I.indid = 0
         AND O.xtype = 'U'
       ORDER BY O.name

Unused Indexes

SELECT 
  O.name AS ObjectName, I.name AS IndexName,
  user_seeks + user_scans + user_lookups AS Reads,
  user_updates AS Writes, P.rows AS RowsNum
	FROM sys.dm_db_index_usage_stats AS IUS
	INNER JOIN sys.indexes AS I 
		ON I.index_id = IUS.index_id 
		  AND IUS.object_id = I.object_id
	INNER JOIN sys.partitions AS P 
		ON P.index_id = IUS.index_id 
		  AND IUS.object_id = P.object_id
	INNER JOIN sys.objects AS O 
		ON IUS.object_id = O.object_id
	WHERE O.type = 'U'
	  AND IUS.database_id = DB_ID()   
	  AND I.type_desc = 'nonclustered'
	  AND I.is_primary_key = 0
	  AND I.is_unique_constraint = 0
	  AND P.rows > 10000
	ORDER BY Reads, RowsNum DESC
Advertisements
%d bloggers like this: