January 22, 2018 at 12:27 am
We have a Sharepoint instance hosting around 2.5 TB of Databases on it. When i try to perform the Update Statistics procedure through a Maintenance Plan, it executes for around 5-7 hrs time whereas when i try to perform the same using the below T-SQL it completes within 75 minutes. SQL Version is 2014 Enterprise Edition. I know that sp_msforeachtable is a undocumented stored procedure; i will try to modify the same but i would like to understand this strange behaviour.
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT s.[name] AS dbName
FROM master.sys.databases s
WHERE s.state_desc = 'ONLINE'
and s.[name] not in ('tempdb')
AND s.is_read_only != 1
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_MSForEachTable' + SPACE(1)+ ''''+SPACE(1)+'UPDATE STATISTICS ? WITH FULLSCAN'+SPACE(1)+''''+CHAR(13)
PRINT(@SQL)
EXEC(@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
January 22, 2018 at 5:03 am
Without seeing the wait statistics on the server, blocked process reports, etc., it's hard to say for certain why one is behaving differently than the other. It's pretty likely that the maintenance plan is running sp_updatestats, which is going to call things differently than the code you wrote. That could also explain the differences. If you really must nail down exactly why, I'd suggest capturing the queries, wait statistics and os behavior during each and comparing them.
You might want to look to Ola Hollengren's scripts instead of writing your own. No need to reinvent the wheel.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 23, 2018 at 11:22 am
Sequential instead of parallel?
January 23, 2018 at 11:58 am
I'd probably start with an Extended Events session to capture exactly what statements are run by each, and get their execution characteristics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply