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
Blog ala Vidar 