CREATE PROCEDURE dbo.usp_CacheTables
@Dbname VARCHAR(256),
@PrintInfo BIT = 1,
@ThreadCount TINYINT = 1,
@WhereClause VARCHAR(MAX) = NULL -- Starts with AND
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX)
SET @WhereClause = ISNULL(@WhereClause, '')
EXEC('USE ' + @Dbname + '
DECLARE @BC BIGINT
SELECT @BC = SUM(BC)
FROM (SELECT SUM(CAST(BINARY_CHECKSUM(*) AS BIGINT)) BC
FROM sys.objects (NOLOCK)
UNION ALL
SELECT SUM(CAST(BINARY_CHECKSUM(*) AS BIGINT)) BC
FROM sys.columns (NOLOCK)
UNION ALL
SELECT SUM(CAST(BINARY_CHECKSUM(*) AS BIGINT)) BC
FROM sys.indexes (NOLOCK)
UNION ALL
SELECT SUM(CAST(BINARY_CHECKSUM(*) AS BIGINT)) BC
FROM sys.index_columns (NOLOCK)
UNION ALL
SELECT SUM(CAST(BINARY_CHECKSUM(*) AS BIGINT)) BC
FROM sys.partitions (NOLOCK)
UNION ALL
SELECT SUM(CAST(BINARY_CHECKSUM(*) AS BIGINT)) BC
FROM sys.stats (NOLOCK)) k')
IF @ThreadCount <= 1
BEGIN
IF OBJECT_ID('TEMPDB..#PrintInfo') IS NOT NULL
DROP TABLE #PrintInfo
CREATE TABLE #PrintInfo (BC BIGINT,
TableName VARCHAR(256) NOT NULL,
Duration NUMERIC(12, 3) NOT NULL)
SET @SQL = CASE WHEN @PrintInfo = 1 THEN 'PRINT ''------- Starting usp_CacheTables on ' + @Dbname + ''''
ELSE ''
END + '
DECLARE @SQL VARCHAR(MAX)
SELECT
@SQL = ''USE ' + @Dbname + '
SET NOCOUNT ON
'' + dbo.StringConcat(''' + CASE WHEN @PrintInfo = 1 THEN '
' ELSE ''
END
+ 'EXEC(''''DECLARE @DateStart DATETIME = GETDATE()
INSERT #PrintInfo(BC, TableName, Duration)
SELECT SUM(BC),'''''''''' + FullName + '''''''''' as TableName, CAST(DATEDIFF(MILLISECOND, @DateStart, GETDATE()) / 1000.000 AS NUMERIC(12 ,3)) as Duration
FROM (SELECT TOP 10 CAST(BINARY_CHECKSUM(*) AS BIGINT) AS BC FROM '' + FullName + '' (NOLOCK))k'''')
'', '''')
FROM(SELECT '''' + s.NAME + ''.'' + t.NAME + '''' AS FullName
FROM ' + @Dbname + '.sys.tables AS t (NOLOCK) INNER JOIN ' + @Dbname
+ '.sys.schemas AS s (NOLOCK) ON t.schema_id = s.SCHEMA_ID
WHERE 1 = 1
' + @WhereClause + ')ff
IF @SQL <> ''''
EXEC (@SQL)
'
--EXEC dbo.PrintLargeText
-- @SQL
--RETURN
EXEC(@SQL)
IF @PrintInfo = 1
BEGIN
DECLARE @STR VARCHAR(MAX)
SELECT @STR = dbo.StringConcat(Info, '
')
FROM (SELECT TOP 999999
TableName + ': ' + CAST(Duration AS VARCHAR) AS Info
FROM #PrintInfo
ORDER BY Duration DESC) k
EXEC dbo.PrintLargeText
@STR
END
END
ELSE
BEGIN
IF OBJECT_ID('TEMPDB..#TableList') IS NOT NULL
DROP TABLE #TableList
CREATE TABLE #TableList (FullName VARCHAR(500) NOT NULL)
EXEC('INSERT #TableList (FullName)
SELECT ''' + @Dbname + '.'' + s.NAME + ''.'' + t.NAME + '''' AS FullName
FROM ' + @Dbname + '.sys.tables AS t (NOLOCK) INNER JOIN ' + @Dbname + '.sys.schemas AS s (NOLOCK) ON t.schema_id = s.SCHEMA_ID
WHERE t.is_ms_shipped = 0
' + @WhereClause)
SELECT @SQL = 'DECLARE @RC INT, @ErrorMessage VARCHAR(MAX)
EXEC PE.Init ''usp_CacheTables ' + @Dbname + '''
EXEC PE.SetMaxThreads ' + CAST(@ThreadCount AS VARCHAR) + '
EXEC PE.SetCommandTimeout 300
' + dbo.StringConcat('EXEC PE.AddSQL ''' + FullName + ''', '''
+ 'EXEC(''''DECLARE @BS BIGINT SELECT @BS = SUM(BS) FROM (SELECT TOP 10 CAST(BINARY_CHECKSUM(*) AS BIGINT) AS BS FROM '
+ FullName + ' (NOLOCK))k'''')''', '
') + '
EXEC @RC = PE.Parallelexecute
IF @RC != 0
BEGIN
SET @ErrorMessage = PE.GetErrorMessage()
PRINT @ErrorMessage
END
'
+ CASE WHEN @PrintInfo = 1
THEN '
DECLARE @Output VARCHAR(MAX) = ''---- Summary ----
'' + ISNULL((SELECT dbo.StringConcat(CASE WHEN Success = 1 THEN ''Success: '' + CAST(Counter AS VARCHAR)
ELSE ''Fail: '' + CAST(Counter AS VARCHAR)
END, '', '')
FROM (SELECT Success,
COUNT(*) AS Counter
FROM PE.GetResult()
GROUP BY Success) k), '''') + ''
---- Failure (Top 100) ----
'' + ISNULL((SELECT dbo.StringConcat(''Key: '' + [Key] + ''
RunTimeMs: '' + CAST(RunTimeMs AS VARCHAR) + ''
Errors: '' + Errors, ''
'')
FROM (SELECT TOP 100 [Key], RunTimeMs, Errors FROM PE.GetResult () WHERE Success = 0 ORDER BY RunTimeMs DESC) kk), '''') + ''
---- Success (Top 100) ----
'' + ISNULL((SELECT dbo.StringConcat(''Key: '' + [Key] + ''
RunTimeMs: '' + CAST(RunTimeMs AS VARCHAR), ''
'')
FROM (SELECT TOP 100 [Key], Success, RunTimeMs FROM PE.GetResult () WHERE Success = 1 ORDER BY RunTimeMs DESC) ll), '''')
PRINT @Output
' ELSE ''
END
FROM #TableList
EXEC(@SQL)
END