--- Please follow the below comments to insert SQL statements. -- Dash copy fallen behind --------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY --------- SET NOCOUNT ON DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) DECLARE @queryId AS INTEGER = 65 DECLARE @surveyXML NVARCHAR(MAX) --------- END - GENERATED CODE --------- DECLARE @STANDALONE INT = 0 /* Standalone -- (UNCOMMENT OUT MULTI-LINE COMMENT DELIMITERS BEFORE DEPLOYMENT!): SET @STANDALONE = 1 DECLARE @outputXML XML */ --------- BEGIN SURVEY QUERY --------- --------- Insert your SQL statements here -- Query to get DDB Performance by Average Q&I Time -- Get Average Q&I time from Idxsidbusagehistory table DECLARE @ONE_MB INT = (1024*1024) declare @releaseId int SET @releaseId =(SELECT releaseId from APP_Client where id =2) DECLARE @csSPVersion INT = 0 IF @releaseId = 16 SELECT @csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2 DECLARE @ddbperformance XML declare @nstring nvarchar(max) DECLARE @timzonename NVARCHAR(1024) = '' SELECT @timzonename = dbo.GetClientTimeZone(2) IF ISNULL(@timzonename, '') = '' BEGIN SELECT @timzonename = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2 SELECT @timzonename = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK) WHERE TimeZoneName = SUBSTRING(@timzonename, CHARINDEX(':', @timzonename, CHARINDEX(':', @timzonename, 0) + 1) + 1, 255) END ----------DDB which needs to be upgraded to V5 DDB-------------- IF object_id('tempdb.dbo.#upgradeTov5DDBs') is not null DROP TABLE #upgradeTov5DDBs create table #upgradeTov5DDBs( SIDBStoreID INT ) IF @releaseId >= 16 AND @csSPVersion >= 14 BEGIN DECLARE @str NVARCHAR(MAX); SET @str = N'INSERT INTO #upgradeTov5DDBs SELECT S.SIDBStoreId FROM IdxSIDBStore S WITH (READUNCOMMITTED) WHERE S.ExtendedFlags&4 <> 4 --IDX_SIDBSTORE_EX_FLAGS_MARK_AND_SWEEP_ENABLED AND S.SIDBStoreId <> 0' exec(@str) END ---------------------------------------------------------------- IF object_id('tempdb.dbo.#tmpStores') is not null DROP TABLE #tmpStores create table #tmpStores( StoreId int, StoreName nvarchar(256), MediaAgentNames nvarchar(1024), CreatedTime datetime, LastRecordedTime datetime, AvgQITime int, PrimaryRecords bigint, SecondaryRecords bigint, ZeroRefRecords bigint, DDBSizeMB bigint, TotalDataSizeMB bigint, InstancedDataSizeMB bigint, FrontendBackupMB bigint, FrontendArchiveMB bigint, BaseLineSizeMB bigint default NULL, Readiness VARCHAR(128) DEFAULT 'N/A', ExtendedFlags BIGINT DEFAULT 0) -- MR 172535 Add BaseLineSizeMB IF object_id('tempdb.dbo.#tmpSubStores') is not null DROP TABLE #tmpSubStores CREATE TABLE #tmpSubStores( StoreId int, SubStoreId int, MediaAgentName nvarchar(1024), accessPath nvarchar(1024), PrimaryRecords bigint, SecondaryRecords bigint, ZeroRefRecords bigint, AvgQITime int, LastRecordedTime datetime, partitionSize bigint ) IF(@releaseId >= 15) -- Release 10.0 BEGIN set @nstring =N' declare @tblLatestStoreInfo table ( SIDBStoreId int, SubStoreId int, CommCellId int, latestUpdateTime int, QIValidTime int, avgQITime int, primaryRecs bigint, secondaryRecs bigint, zeroref bigint) --Get most recent modified time for each Dedupe Store partition insert into @tblLatestStoreInfo select U.SIDBStoreId, U.SubStoreId, U.CommCellId, max(U.ModifiedTime), 0, 0, 0, 0, 0 from IdxSIDBUsageHistory U WITH (NOLOCK) inner join archCopySIDBStore agcStore WITH (NOLOCK) ON U.SIDBStoreId = agcStore.SIDBStoreId where U.HistoryType <> 2 group by U.SIDBStoreId, U.SubStoreId, U.CommCellId if exists ( select * from sys.objects where object_id = OBJECT_ID(N''GetCurrentAvgQITimeMedian'') and type in ( N''FN'', N''IF'', N''TF'', N''FS'', N''FT'' )) begin update @tblLatestStoreInfo set avgQITime = dbo.GetCurrentAvgQITimeMedian(SIDBStoreId, SubStoreId , commcellId , 0) end else begin --Get valied last modified time for QITime UPDATE T SET T.QIValidTime = T1.latestQIModifiedTime FROM @tblLatestStoreInfo T INNER JOIN (SELECT U.SIDBStoreId, U.SubStoreId, max(U.ModifiedTime) AS latestQIModifiedTime FROM IdxSIDBUsageHistory U WITH (NOLOCK) INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId AND U.SubStoreId = LI.SubStoreId WHERE U.AvgQITime > 0 AND U.HistoryType <> 2 GROUP BY U.SIDBStoreId, U.SubStoreId ) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId AND T.SubStoreId = T1.SubStoreId --Take average of QITime for last 3 days. UPDATE T SET T.avgQITime = T1.QITime FROM @tblLatestStoreInfo T INNER JOIN (SELECT U.SIDBStoreId, U.SubStoreId, AVG(U.AvgQITime) AS QITime FROM IdxSIDBUsageHistory U WITH (NOLOCK) INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId AND U.SubStoreId = LI.SubStoreId WHERE U.ModifiedTime > (LI.QIValidTime - (3*24*60*60)) AND U.AvgQITime > 0 AND U.AvgQITimeSampleCount >= 100 -- Discard QI Times if sample count less than 100 AND U.HistoryType = 0 GROUP BY U.SIDBStoreId, U.SubStoreId ) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId AND T.SubStoreId = T1.SubStoreId end UPDATE T SET T.PrimaryRecs = U.PrimaryEntries, T.SecondaryRecs = U.SecondaryEntries, T.Zeroref = U.ZeroRefCount FROM @tblLatestStoreInfo T INNER JOIN IdxSIDBUsageHistory U WITH (NOLOCK) ON T.SIDBStoreId = U.SIDBStoreId AND T.SubStoreId = U.SubStoreId WHERE T.latestUpdateTime = U.ModifiedTime INSERT INTO #tmpStores (StoreId, LastRecordedTime, AvgQITime, primaryRecords, secondaryRecords, zeroRefRecords, BaseLineSizeMB) SELECT SIDBStoreId, dbo.getDateTime(MAX(latestUpdateTime)), MAX(AvgQITime), SUM(primaryRecs), SUM(secondaryRecs), SUM(zeroref), 0 --MR 172535 Add BaseLineSizeMB placeholder FROM @tblLatestStoreInfo GROUP BY SIDBStoreId INSERT INTO #tmpSubStores(StoreId, SubStoreId, accessPath, AvgQITime, PrimaryRecords, SecondaryRecords, ZeroRefRecords, LastRecordedTime) SELECT S.SIDBStoreId, S.SubStoreId, A.Path, AvgQITime, primaryRecs, secondaryRecs, zeroref, dbo.getDateTime(latestUpdateTime) FROM IdxSIDBSubStore S INNER JOIN IdxAccessPath A ON A.IdxAccessPathId = S.IdxAccessPathId AND A.IdxCacheId = S.IdxCacheId INNER JOIN IdxCache C ON C.IdxCacheId = S.IdxCacheId INNER JOIN @tblLatestStoreInfo T ON T.SIDBStoreId = S.SIDBStoreId AND T.SubStoreId = S.SubStoreId UPDATE T SET DDBSizeMB = Size.DDBSizeMB FROM #tmpStores T inner join (SELECT SIDBStoreId, SUM(IC.DiskUsageMB) AS DDBSizeMB FROM #tmpStores T1, IdxSIDBSubStore Sub WITH (NOLOCK), IdxCache IC WITH (NOLOCK) WHERE Sub.SIDBStoreId = T1.StoreId AND Sub.IdxCacheId = IC.IdxCacheId GROUP BY Sub.SIDBStoreId) AS Size ON T.StoreId = Size.SIDBStoreId UPDATE T SET T.partitionSize = IC.DiskUsageMB FROM #tmpSubStores T INNER JOIN IdxSIDBSubStore Sub ON Sub.SubStoreId = T.SubStoreId INNER JOIN IdxCache IC ON Sub.IdxCacheId = IC.IdxCacheId UPDATE S SET Readiness = CASE WHEN (I.flags & 1073741824 = 1073741824 OR SubStore.flags & 1073741824 = 1073741824) THEN ''Resync in progress'' WHEN (I.flags & 33554432 = 33554432 OR SubStore.flags & 33554432 = 33554432)THEN ''Needs resync'' WHEN (I.flags & 67108864 = 67108864 OR SubStore.flags & 67108864 = 67108864)THEN ''Verification in progress'' WHEN (I.flags & 1024 = 1024 OR SubStore.flags & 1024 = 1024) THEN ''Partitions being moved'' WHEN (I.flags & 16777216 = 16777216 OR SubStore.flags & 16777216 = 16777216) THEN ''Maintenance'' ELSE ''Ready'' END FROM #tmpStores S INNER JOIN IdxSIDBStore I WITH (NOLOCK) ON S.StoreId = I.SIDBStoreId INNER JOIN IdxSIDBSubStore SubStore WITH (NOLOCK) ON I.SIDBStoreId = SubStore.SIDBStoreId ; WITH StoreMA (StoreId, MAName) AS ( SELECT DISTINCT SIDBStoreId, C.name FROM IdxSIDBSubStore S WITH (NOLOCK) INNER JOIN APP_Client C WITH (NOLOCK) ON S.ClientId = C.id ) , StoreMAList (StoreId, MANames) AS ( SELECT StoreId, STUFF( (SELECT '', '', '''' + MAName FROM StoreMA WHERE StoreId = S.StoreId ORDER BY MAName FOR XML PATH('''') ), 1, 2, '''') FROM StoreMA S GROUP BY StoreId ) UPDATE T SET MediaAgentNames = L.MANames FROM #tmpStores T INNER JOIN StoreMAList L ON T.StoreId = L.StoreId UPDATE T SET MediaAgentName = C.name FROM APP_Client C INNER JOIN IdxSIDBSubStore S ON C.Id = S.ClientId INNER JOIN #tmpSubStores T ON S.SubStoreId = T.SubStoreId ' END ELSE IF (@releaseId = 14) --Release 9.0 BEGIN set @nstring =N' declare @tblLatestStoreInfo table ( SIDBStoreId int, latestUpdateTime int, QIValidTime int, avgQITime int, primaryRecs bigint, secondaryRecs bigint, zeroref bigint) --Get most recent modified time for each Dedupe Store insert into @tblLatestStoreInfo select U.SIDBStoreId, max(U.ModifiedTime), 0, 0, 0, 0, 0 from IdxSIDBUsageHistory2 U WITH (NOLOCK) inner join archCopySIDBStore agcStore WITH (NOLOCK) ON U.SIDBStoreId = agcStore.SIDBStoreId group by U.SIDBStoreId --Get valied last modified time for QITime UPDATE T SET T.QIValidTime = T1.latestQIModifiedTime FROM @tblLatestStoreInfo T INNER JOIN (SELECT U.SIDBStoreId, max(U.ModifiedTime) AS latestQIModifiedTime FROM IdxSIDBUsageHistory2 U WITH (NOLOCK) INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId WHERE U.AvgQITime > 0 GROUP BY U.SIDBStoreId ) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId --Take average of QITime for last 3 days. UPDATE T SET T.avgQITime = T1.QITime FROM @tblLatestStoreInfo T INNER JOIN (SELECT U.SIDBStoreId, AVG(U.AvgQITime) AS QITime FROM IdxSIDBUsageHistory2 U WITH (NOLOCK) INNER JOIN @tblLatestStoreInfo LI ON U.SIDBStoreId = LI.SIDBStoreId WHERE U.ModifiedTime > (LI.QIValidTime - (3*24*60*60)) AND U.AvgQITime > 0 AND U.AvgQITimeSampleCount >= 100 -- Discard QI Times if sample count less than 100 GROUP BY U.SIDBStoreId ) AS T1 ON T.SIDBStoreId = T1.SIDBStoreId UPDATE T SET T.PrimaryRecs = U.PrimaryEntries, T.SecondaryRecs = U.SecondaryEntries, T.Zeroref = U.zeroRefCount FROM @tblLatestStoreInfo T INNER JOIN IdxSIDBUsageHistory2 U ON T.SIDBStoreId = U.SIDBStoreId WHERE T.latestUpdateTime = U.ModifiedTime INSERT INTO #tmpStores (StoreId, LastRecordedTime, AvgQITime, primaryRecords, secondaryRecords, zeroRefRecords, BaseLineSizeMB) SELECT SIDBStoreId, dbo.getDateTime(MAX(latestUpdateTime)), MAX(AvgQITime), MAX(primaryRecs), SUM(secondaryRecs), MAX(zeroref), 0 --MR 172535 Add BaseLineSizeMB placeholder FROM @tblLatestStoreInfo GROUP BY SIDBStoreId UPDATE T SET StoreName = iss.SIDBStoreAliasName, MediaAgentNames = cl.name, createdTime = dbo.GetDateTime(iss.CreatedTime), DDBSizeMB = ic.DiskUsageMB FROM #tmpStores T inner join IdxSIDBStore iss WITH (NOLOCK) ON T.StoreId = iss.SIDBStoreId inner join IdxCache ic WITH (NOLOCK) ON iss.IdxCacheId = ic.IdxCacheId inner join APP_Client cl WITH (NOLOCK) ON iss.ClientId = cl.id' END exec sp_executesql @nstring /* MR 172535: FOR NOW, PUT EVERYTHING * HERE. LATER FIGURE OUT WHERE IT REALLY SHOuLD GO, AND * IF THERE ARE "Release Level" Dependancies */ --------------------------- START BaseLineSizeMB MODS ------------------------------------------------------- IF (@releaseId > 16) OR (@releaseId = 16 AND @csSPVersion >= 15) BEGIN IF OBJECT_ID('tempdb..#lt_MMGetDDBEnginesOutput') IS NOT NULL DROP TABLE #lt_MMGetDDBEnginesOutput CREATE TABLE #lt_MMGetDDBEnginesOutput ( CommCellId int, SIDBStoreId int, SIDBStoreName nvarchar(256), SIDBStoreAliasName nvarchar(256), archGroupId integer, archGroupName nvarchar(256), copyId int, copyName nvarchar(64), totalDataSize bigint, sizeOnMedia bigint, totalAppSize BIGINT, deDupSavingSize BIGINT, deletedAFCount INT, MaintenanceReason INT, jobCount INT, baseLineSize BIGINT, freeSpaceOnLibrary BIGINT, retentionDaysForCopy INT, retentionCyclesForCopy INT, copyMediaAgentId INT, sealedTime INT, CreatedTime INT, SubstoreCount INT, AvgQITime INT, UniqueBlocks BIGINT, dedupeFlags integer, PrimaryRecPercent INT, Version INT, MaxAvgQITime INT, Flags INT, LimitFlag INT, AvgQITime14Days INT, MaxAllowedConnections INT, LastVerifiedOn BIGINT, JobsLastVerifiedOn BIGINT, AppBaselineSize BIGINT, SizeOfFragmentedSpace BIGINT, ActiveStreams INT, EnableAfterDelayTimeZone INT, EnableAfterDelayTime INT, SizeOffDiskMB BIGINT, SizeToBeInSiloMB BIGINT, SizeInSiloMB BIGINT, SizeOfBackup BIGINT, deDupSavingPercent VARCHAR(20), extendedFlags BIGINT , TotalSizeOfUniqueBlocks BIGINT ) IF @releaseId = 16 AND @csSPVersion < 20 BEGIN ALTER TABLE #lt_MMGetDDBEnginesOutput DROP COLUMN TotalSizeOfUniqueBlocks END EXEC MMGetDDBEngines 1 /*userId*/, 30 /*propertyLevel*/, 0, 0, 0 /*SIDBStoreId*/, 32 UPDATE T SET BaseLineSizeMB = baseLineSize/1024.0/1024.0, FrontendBackupMB = AppBaselineSize/1024.0/1024.0, FrontendArchiveMB = 0 FROM #TmpStores T INNER JOIN #lt_MMGetDDBEnginesOutput L ON L.SIDBStoreId = T.StoreId IF OBJECT_ID('tempdb..#lt_MMGetDDBEnginesOutput') IS NOT NULL DROP TABLE #lt_MMGetDDBEnginesOutput END ELSE BEGIN -- THE FOLLOWING CODE "BORROWED" FROM MMGetDDBEngines.sp (REL_11_0_0_B80_SP12_BRANCH [1.17.12.13]) -Skatz 20180417 IF object_id('tempdb.dbo.#SIAppID') is not null DROP TABLE #SIAppID CREATE TABLE #SIAppID( SIDBStoreId INT, appId INT, archGrpId INT) -- Check diffs from tab -- JMJobDatastats No changes that affect stmt -- archCopySIDBStore FK changes only IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL BEGIN INSERT INTO #SIAppID SELECT DISTINCT B.StoreId, A.appId, A.archGrpId FROM JMJobDatastats A WITH (NOLOCK), archSubclientCopyDDBMap C WITH (NOLOCK), #TmpStores B WHERE A.archGrpCopyId = C.CopyID AND A.AppId = C.appId and C.SIDBStoreId = B.StoreId AND A.status IN (100, 101, 102, 103) AND (A.disabled & (1 + 256)) = 0 END ELSE BEGIN INSERT INTO #SIAppID SELECT DISTINCT B.StoreId, A.appId, A.archGrpId FROM JMJobDatastats A WITH (NOLOCK), #TmpStores B, archCopySIDBStore C WITH (NOLOCK) WHERE A.archGrpCopyId = C.CopyID and C.SIDBStoreId = B.StoreId AND A.status IN (100, 101, 102, 103) AND (A.disabled & (1 + 256)) = 0 END -- Check diffs from tab --JMBkpStats - collumns added not reflected in folllowing stmt. UPDATE #TmpStores SET BaseLineSizeMB = (SELECT SUM(totalBackupSize) FROM JMBkpStats BSL WITH (NOLOCK), (SELECT BSL.appId, MAX(jobId) AS MaxFullJobID FROM JMBkpStats BSL WITH (NOLOCK), #SIAppID CT WHERE CT.SIDBStoreId = #TmpStores.StoreId AND CT.appId = BSL.appId AND BSL.status IN (1, 3, 14) AND BSL.bkpLevel IN (1, 0x40, 0x80, 0x400, 0x1000, 0x4000, 0x8000) GROUP BY BSL.appId ) T WHERE BSL.appId = T.appId AND BSL.jobId = MaxFullJobID)/@ONE_MB END --------------------------- END BaseLineSizeMB MODS ------------------------------------------------------- UPDATE S SET StoreName = I.SIDBStoreName, TotalDataSizeMB = I.TotalDataSizeBytes/1024/1024, InstancedDataSizeMB = I.InstancedDataSizeBytes/1024/1024, createdTime = dbo.GetDateTime(I.CreatedTime) FROM #tmpStores S INNER JOIN IdxSIDBStore I WITH (NOLOCK) ON S.StoreId = I.SIDBStoreId UPDATE #tmpStores SET LastRecordedTime = dbo.UTCToLocalTime(LastRecordedTime, @timzonename) , CreatedTime = dbo.UTCToLocalTime(CreatedTime, @timzonename) -- Begin of calculation for FET -- IF (@releaseId < 16) OR (@releaseId = 16 AND @csSPVersion < 15) BEGIN IF OBJECT_ID('tempdb.dbo.#DDBAppSize') IS NOT NULL DROP TABLE #DDBAppSize CREATE TABLE #DDBAppSize (SIDBStoreId INT, clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT, fullCycleNum INT, appSize FLOAT, bArchiver INT, bMultiInstances INT) --get the last full size if not a archiver and not a Turbo subclient --get the SUM of archiver without any time range INSERT INTO #DDBAppSize SELECT SIDBStoreId, clientId, appTypeId, instance, backupset, appId, fullCycleNum, SUM(totalUncompBytes), bArchiver, 0 FROM APP_Application A WITH(NOLOCK) INNER JOIN ( SELECT SIDBStoreId, B.appId, B.fullCycleNum, B.totalUncompBytes, ISNULL(H.isDMAppType, 0) AS bArchiver, ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, AF.appId ORDER BY B.servStartDate DESC) AS rowId FROM archFileCopyDedup AFD WITH(NOLOCK) INNER JOIN archFile AF WITH(NOLOCK) ON AFD.archFileId = AF.id AND AFD.commCellId = AF.commCellId AND AF.isValid = 1 INNER JOIN JMBkpStats B WITH(NOLOCK) ON AF.jobId = B.jobId AND AF.commCellId = B.commCellId LEFT OUTER JOIN APP_IDATypeHandlingInfo H WITH(NOLOCK) ON B.appType = H.appTypeId WHERE B.status IN (1, 3, 14) AND (B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768) OR ISNULL(H.isDMAppType, 0) = 1) AND B.appType <> 106 AND B.dataStatus = 0 AND B.totalUncompBytes > 0 AND AFD.SIDBStoreId > 0 ) T ON A.id = T.appId WHERE (T.rowId = 1 OR bArchiver > 0) AND A.subclientStatus&2 = 0 GROUP BY SIDBStoreId, clientId, appTypeId, instance, backupset, appId, fullCycleNum, bArchiver IF OBJECT_ID('tempdb.dbo.#TurboSubclient') IS NOT NULL DROP TABLE #TurboSubclient CREATE TABLE #TurboSubclient (appId INT) IF (@releaseId < 15) INSERT INTO #TurboSubclient SELECT id FROM APP_Application WITH(NOLOCK) WHERE appTypeId IN (SELECT componentNameID FROM APP_IDAProp WITH(NOLOCK) WHERE attrName = 'Enable Archive' AND attrVal = '1' AND modified = 0) ELSE INSERT INTO #TurboSubclient SELECT id FROM APP_Application WITH(NOLOCK) WHERE id IN (SELECT componentNameId FROM APP_subclientProp WITH(NOLOCK) WHERE attrName = 'Is Turbo Subclient' and cs_attrName = CHECKSUM(N'Is Turbo Subclient') and attrval = '1' and modified = 0) AND id IN (SELECT componentNameId FROM APP_subclientProp WITH(NOLOCK) WHERE attrName = 'First Turbo Job id' and cs_attrName = CHECKSUM(N'First Turbo Job id') and attrval != '0' and modified = 0) AND appTypeId IN ( SELECT type FROM APP_iDAType WITH(NOLOCK) WHERE type < 600 AND type NOT IN (28,86) AND (name LIKE '%file%system%' OR type IN (13,54,22,80) OR type IN (SELECT DISTINCT appTypeId FROM simAppTypeLicTypeMap WITH(NOLOCK) WHERE display_name = 'File System')) ) UNION SELECT id FROM APP_Application WITH(NOLOCK) WHERE (subclientStatus & 0x10000) > 0 --get the SUM of the last full cycle for Turbo subclients. #DDBAppSize only has the latest fullCycleNum for each subclient. UPDATE S SET appSize = T.totalUncompBytes, bArchiver = 1 FROM #DDBAppSize S INNER JOIN ( SELECT A.SIDBStoreId, A.appId, SUM(B.totalUncompBytes) AS totalUncompBytes FROM #DDBAppSize A INNER JOIN #TurboSubclient TS ON A.appId = TS.appId INNER JOIN JMBkpStats B WITH(NOLOCK) ON A.appId = B.appId AND A.fullCycleNum = B.fullCycleNum WHERE B.status IN (1, 3, 14) AND B.dataStatus = 0 AND B.totalUncompBytes > 0 GROUP BY A.SIDBStoreId, A.appId ) T ON S.SIDBStoreId = T.SIDBStoreId AND S.appId = T.appId DROP TABLE #TurboSubclient --Exclude DDB, Index and SILO subclients DELETE S FROM #DDBAppSize S INNER JOIN APP_subclientProp WITH(NOLOCK) ON appId = componentNameId WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') AND attrVal <> '0' AND modified = 0 AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID')) --Exclude (DM2)SQL Backup run on the WeBServer Client. DELETE S FROM #DDBAppSize S INNER JOIN APP_subclientProp SP WITH(NOLOCK) ON S.appId = SP.componentNameId AND SP.attrName = 'DM2' AND SP.cs_attrName = CHECKSUM(N'DM2') AND SP.attrVal <> '0' AND SP.modified = 0 INNER JOIN (SELECT clientId FROM simInstalledPackages WITH(NOLOCK) WHERE simPackageID = 803) P ON S.clientId = P.ClientId INNER JOIN (SELECT componentNameId FROM APP_ClientProp WITH(NOLOCK) WHERE attrname LIKE '%DM2WebService%' AND attrVal <> '0' AND modified = 0 ) C ON S.ClientId = C.componentNameId -- For Oracle & SAP,Informix Database count largest subclient for each instance DELETE S FROM #DDBAppSize S INNER JOIN ( SELECT SIDBStoreId, clientId, appTypeId, instanceId, MAX(appSize) AS maxSize FROM #DDBAppSize WHERE appTypeId IN (3,5,22,61,79,80,104,128) AND bArchiver = 0 GROUP BY SIDBStoreId, clientId, appTypeId, instanceId ) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId AND S.instanceId = T.instanceId WHERE appSize < maxSize -- For DB2, count largest subclient for each backupset DELETE S FROM #DDBAppSize S INNER JOIN ( SELECT SIDBStoreId, clientId, appTypeId, instanceId, backupsetId, MAX(appSize) AS maxSize FROM #DDBAppSize WHERE appTypeId IN (37,62,103,135) AND bArchiver = 0 GROUP BY SIDBStoreId, clientId, appTypeId, instanceId, backupsetId ) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId AND S.instanceId = T.instanceId AND S.backupsetId = T.backupsetId WHERE appSize < maxSize --Only count the default instance of non-DB agents DELETE S FROM #DDBAppSize S INNER JOIN APP_InstanceName I WITH(NOLOCK) ON S.instanceId = I.id INNER JOIN ( SELECT SIDBStoreId, clientId, appTypeId, COUNT(DISTINCT instanceId) AS nInstance FROM #DDBAppSize WHERE appTypeId NOT IN (3,5,22,37,61,62,79,80,103,104,128,135) AND bArchiver = 0 GROUP BY SIDBStoreId, clientId, appTypeId HAVING COUNT(DISTINCT instanceId) > 1 ) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId WHERE I.name NOT LIKE 'defaultInstance%' -- Multiple backupset of same non-DB agent, count only the biggest backupset and ignore others. IF OBJECT_ID('tempdb.dbo.#BackupSetSize') IS NOT NULL DROP TABLE #BackupSetSize CREATE TABLE #BackupSetSize (SIDBStoreId INT, clientId INT, appTypeId INT, backupSetId INT, appSize BIGINT) INSERT INTO #BackupSetSize SELECT SIDBStoreId, clientId, appTypeId, backupSetId, SUM(appSize) FROM #DDBAppSize WHERE appTypeId NOT IN (3,5,22,37,61,62,79,80,103,104,128,135) AND bArchiver = 0 GROUP BY SIDBStoreId, clientId, appTypeId, backupSetId DELETE S FROM #DDBAppSize S INNER JOIN ( SELECT SIDBStoreId, clientId, appTypeId, backupSetId, ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, clientId, appTypeId, backupSetId ORDER BY appSize DESC) AS rowId FROM #BackupSetSize ) T ON S.SIDBStoreId = T.SIDBStoreId AND S.clientId = T.clientId AND S.appTypeId = T.appTypeId AND S.backupSetId = T.backupSetId WHERE rowId > 1 DROP TABLE #BackupSetSize -- For DAG clients, count largest DAG client per DAG group when [ExDAGCombineLicense] flag is enabled in GxGlobalParam IF (@releaseId >= 15) BEGIN IF OBJECT_ID('tempdb.dbo.#DAGClients') IS NOT NULL DROP TABLE #DAGClients CREATE TABLE #DAGClients (SIDBStoreId INT, DAGName VARCHAR(MAX), clientId INT, size BIGINT) SET @nstring = N' IF EXISTS (SELECT * FROM GxGlobalParam WITH(NOLOCK) WHERE name =''ExDAGCombineLicense'' AND value = ''1'' AND modified = 0) INSERT INTO #DAGClients SELECT SIDBStoreId, attrVal, S.clientId, SUM(appSize) FROM #DDBAppSize S INNER JOIN APP_ClientProp P WITH(NOLOCK) ON S.clientId = P.componentNameId AND attrName = ''DAG Name'' AND modified = 0 GROUP BY SIDBStoreId, attrVal, S.clientId' EXEC sp_executesql @nstring DELETE #DAGClients FROM #DAGClients D INNER JOIN (SELECT SIDBStoreId, DAGName, clientId, size, ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, DAGName ORDER BY size DESC) AS rowId FROM #DAGClients ) T ON D.SIDBStoreId = T.SIDBStoreId AND D.DAGName = T.DAGName WHERE rowId = 1 UPDATE S SET appSize = 0 FROM #DDBAppSize S INNER JOIN #DAGClients D ON S.SIDBStoreId = D.SIDBStoreId AND S.clientId = D.clientId AND S.appTypeId <> 106 DROP TABLE #DAGClients END --get legal hold jobs IF OBJECT_ID('tempdb.dbo.#LegalHoldJob') IS NOT NULL DROP TABLE #LegalHoldJob CREATE TABLE #LegalHoldJob (jobId INT, commCellId INT, appSize BIGINT) INSERT INTO #LegalHoldJob SELECT A.jobId, A.commCellId, A.totalBackupSize FROM JMAdminJobStatsTable A WITH(NOLOCK) INNER JOIN ( SELECT jobId, commCellId, CAST(SUBSTRING(xmlJobInfo, CHARINDEX('>', xmlJobInfo)+1, LEN(xmlJobInfo)) AS XML) AS xmlJobInfo FROM JMJobStats WITH(NOLOCK) WHERE opType = 64 ) T ON A.jobId = T.jobId AND A.commCellId = T.commCellId WHERE A.status in (1, 3, 14) AND A.dataStatus = 0 AND T.xmlJobInfo.value('(JobManager_AdminJobDetailsXml/compPolicyJobInfo/compliancePolicyInfo/@phaseDetails)[1]', 'INT') & 0x10 <> 0 UPDATE S SET appSize = CASE WHEN S.bArchiver = 0 THEN T.appSize ELSE (S.appSize+T.appSize) END FROM #DDBAppSize S INNER JOIN ( SELECT SIDBStoreId, appId, SUM(appSize) AS appSize FROM (SELECT DISTINCT SIDBStoreId, AF.appId, LH.jobId, LH.commCellId, LH.appSize FROM archFileCopyDedup AFD WITH(NOLOCK) INNER JOIN archFile AF WITH(NOLOCK) ON AFD.archFileId = AF.id AND AFD.commCellId = AF.commCellId AND AF.isValid = 1 INNER JOIN #LegalHoldJob LH ON AF.jobId = LH.jobId AND AF.commCellId = LH.commCellId WHERE AFD.SIDBStoreId > 0 ) L GROUP BY SIDBStoreId, appId ) T ON S.SIDBStoreId = T.SIDBStoreId and S.appId = T.appId DROP TABLE #LegalHoldJob -- VSA jobs: count unique VM size in last FULL/Synthetic FULL jobs of each VSA subclient IF OBJECT_ID('tempdb.dbo.#DDBVSAJobs') IS NOT NULL DROP TABLE #DDBVSAJobs CREATE TABLE #DDBVSAJobs (SIDBStoreId INT, VMclientId INT, jobId INT, commCellId INT, size BIGINT, bArchiver INT) INSERT INTO #DDBVSAJobs SELECT SIDBStoreId, VMclientId, jobId, commCellId, 0, 0 FROM ( SELECT SIDBStoreId, VMclientId, VM.jobId, VM.commCellId, ROW_NUMBER() OVER (PARTITION BY SIDBStoreId, VMclientId ORDER BY B.servStartDate DESC) AS rowId FROM archFileCopyDedup AFD WITH(NOLOCK) INNER JOIN archFile AF WITH(NOLOCK) ON AFD.archFileId = AF.id AND AFD.commCellId = AF.commCellId AND AF.isValid = 1 INNER JOIN JMBkpStats B WITH(NOLOCK) ON AF.jobId = B.jobId AND AF.commCellId = B.commCellId INNER JOIN APP_VMProp VM WITH(NOLOCK) ON B.jobId = VM.jobId AND B.commCellId = VM.commCellId AND attrName = 'vmStatus' AND attrVal = '0' WHERE B.status IN (1, 3, 14) AND B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768) AND B.dataStatus = 0 AND B.totalUncompBytes > 0 AND AFD.SIDBStoreId > 0 ) T WHERE T.rowId = 1 GROUP BY SIDBStoreId, VMclientId, jobId, commCellId CREATE INDEX #DDBVSAJobs_jobId_commCellId ON #DDBVSAJobs (jobId, commCellId, VMclientId) INCLUDE (SIDBStoreId) IF OBJECT_ID('tempdb.dbo.##VMSize') IS NOT NULL DROP TABLE #VMSize CREATE TABLE #VMSize (jobId INT, commCellId INT, VMclientId INT, vmStubStatus INT, vmSize FLOAT) INSERT INTO #VMSize SELECT jobId, commCellId, VMClientId, ISNULL([vmStubStatus], 0), ISNULL([vmUsedSpace], [vmGuestSize]) FROM (SELECT V.jobId, V.commcellId, V.VMclientId, attrName, attrVal FROM APP_VMProp V WITH(NOLOCK) INNER JOIN #DDBVSAJobs T ON V.jobId = T.jobId AND V.commCellId = T.commCellId AND V.VMclientId = T.VMclientId WHERE attrName IN ('vmUsedSpace', 'vmGuestSize', 'vmStubStatus') ) S PIVOT (MAX(attrVal) FOR attrName IN ([vmUsedSpace], [vmGuestSize], [vmStubStatus])) AS P INSERT INTO #DDBAppSize SELECT SIDBStoreId, J.VMclientId, 106, 0, 0, 0, 0, U.vmSize, CASE WHEN U.vmStubStatus <> 0 THEN 1 ELSE 0 END, 0 FROM #DDBVSAJobs J INNER JOIN #VMSize U ON J.jobId = U.jobId AND J.commCellId = U.commCellId AND J.VMclientId = U.VMclientId DROP TABLE #DDBVSAJobs DROP TABLE #VMSize UPDATE S SET FrontendBackupMB = appSizeMB FROM #tmpStores S INNER JOIN (SELECT SIDBStoreId, SUM(appSize)/1024/1024 AS appSizeMB FROM #DDBAppSize WHERE bArchiver = 0 GROUP BY SIDBStoreId ) D ON S.StoreId = D.SIDBStoreId UPDATE S SET FrontendArchiveMB = appSizeMB FROM #tmpStores S INNER JOIN (SELECT SIDBStoreId, SUM(appSize)/1024/1024 AS appSizeMB FROM #DDBAppSize WHERE bArchiver = 1 GROUP BY SIDBStoreId ) D ON S.StoreId = D.SIDBStoreId DROP TABLE #DDBAppSize END -- End of calculation for FET -- -- Begin of calculation for dedupe ratio -- CREATE TABLE #StoreDedupe (SIDBStoreId INT, SizeOnMediaMB BIGINT, AppSizeMB BIGINT, AppSizeDelMB BIGINT, DedupeRatio FLOAT) INSERT INTO #StoreDedupe SELECT SIDBStoreId, SUM(CAST(PhysicalBytesMB AS BIGINT)), 0, 0, 0 FROM MMVolume V WITH (NOLOCK) WHERE SIDBStoreId > 0 AND Attributes&512 = 0 AND SiloStatus <> 3 GROUP BY SIDBStoreId CREATE TABLE #StoreAppSize (SIDBStoreId INT, AppSizeMB BIGINT) CREATE TABLE #CopyStoreJob (jobId INT, commCellId INT, copyId INT, SIDBStoreId INT, physicalSize BIGINT) IF COL_LENGTH('archFileCopy','unCompSize') IS NULL BEGIN INSERT INTO #CopyStoreJob SELECT AF.jobId, AF.commCellId, AFD.archCopyId, AFD.SIDBStoreId, SUM(AFC.physicalSize) FROM archFile AF WITH (NOLOCK) INNER JOIN archFileCopyDedup AFD WITH (NOLOCK) ON AF.id = AFD.archFileId AND AF.commCellId = AFD.commCellId AND AF.isValid = 1 INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AFD.archFileId = AFC.archFileId AND AFD.commCellId = AFC.commCellId AND AFC.archCopyId = AFD.archCopyId WHERE AFD.SIDBStoreId > 0 GROUP BY AF.jobId, AF.commCellId, AFD.archCopyId, AFD.SIDBStoreId CREATE CLUSTERED INDEX CopyStoreJob_idx ON #CopyStoreJob (SIDBStoreId, physicalSize) INSERT INTO #StoreAppSize SELECT J.SIDBStoreId, SUM((1.0*J.physicalSize/B.totalBackupSize)*B.totalUncompBytes)/1024/1024 FROM #CopyStoreJob J INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId WHERE B.totalBackupSize > 0 GROUP BY J.SIDBStoreId INSERT INTO #StoreAppSize SELECT J.SIDBStoreId, SUM(J.physicalSize)/1024/1024 FROM #CopyStoreJob J INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId GROUP BY J.SIDBStoreId END ELSE BEGIN SET @nstring = N' INSERT INTO #StoreAppSize SELECT AFD.SIDBStoreId, SUM(AFC.unCompSize)/1024/1024 FROM archFileCopyDedup AFD WITH(NOLOCK) INNER JOIN archFileCopy AFC WITH(NOLOCK) ON AFC.archFileId = AFD.archFileId AND AFC.commCellId = AFD.commCellId AND AFC.archCopyId = AFD.archCopyId GROUP BY AFD.SIDBStoreId' EXEC sp_executesql @nstring END UPDATE S SET AppSizeMB = T.AppSizeMB FROM #StoreDedupe S INNER JOIN ( SELECT SIDBStoreId, SUM(AppSizeMB) AS AppSizeMB FROM #StoreAppSize GROUP BY SIDBStoreId ) T ON S.SIDBStoreId = T.SIDBStoreId IF (@releaseId >= 15) BEGIN SET @nstring = N' UPDATE S SET AppSizeDelMB = T.AppSizeDelMB FROM #StoreDedupe S INNER JOIN ( SELECT SIDBStoreId, SUM(ISNULL(appSizeFreedBytes,0))/1024/1024 AS AppSizeDelMB FROM MMDeletedAF WITH (NOLOCK) WHERE SIDBStoreId > 0 GROUP BY SIDBStoreId ) T ON S.SIDBStoreId = T.SIDBStoreId' EXEC sp_executesql @nstring END UPDATE #StoreDedupe SET DedupeRatio = 1.0*SizeOnMediaMB/(AppSizeMB + AppSizeDelMB) WHERE (AppSizeMB + AppSizeDelMB) > 0 DROP TABLE #CopyStoreJob DROP TABLE #StoreAppSize IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IdxSIDBStore' AND COLUMN_NAME = 'ExtendedFlags')--ExtendedFlags column was added in v11 SP14 BEGIN SET @nstring = N' UPDATE S SET ExtendedFlags = I.ExtendedFlags FROM #tmpStores S INNER JOIN IdxSIDBStore I WITH (NOLOCK) ON S.StoreId = I.SIDBStoreId' EXEC sp_executesql @nstring END -- END of calculation of dedupe ratio -- SET @ddbperformance = ( SELECT AGC.id as '@CopyId', StoreId as '@StoreId', StoreName as '@StoreName', MediaAgentNames as '@MediaAgentNames', CreatedTime as '@CreatedTime', LastRecordedTime as '@LastRecordedTime', AvgQITime as '@AvgQITime', PrimaryRecords as '@PrimaryRecords', SecondaryRecords as '@SecondaryRecords', ZeroRefRecords as '@ZeroRefRecords', TotalDataSizeMB as '@TotalDataSizeMB', InstancedDataSizeMB as '@InstancedDataSizeMB', DDBSizeMB as '@DDBSizeMB', FrontendBackupMB as '@FrontendBackupMB', FrontendArchiveMB as '@FrontendArchiveMB', BaseLineSizeMB as '@BaseLineSizeMB', Readiness AS '@Readiness', ISNULL(AppSizeMB, 0) AS '@AppSizeMB', ISNULL(DedupeRatio, 0) AS '@DedupeRatio', T.ExtendedFlags AS '@ExtendedFlags', CASE WHEN U.SIDBStoreID IS NULL THEN 0 ELSE 1 END AS '@NeedUpgrade', CAST( ( SELECT SubStoreId as '@SubStoreId', AccessPath as '@AccessPath', TS.PrimaryRecords as '@PrimaryRecords', TS.SecondaryRecords as '@SecondaryRecords', TS.ZeroRefRecords as '@ZeroRefCount', TS.LastRecordedTime as '@LastRecordedTime', TS.AvgQITime as '@AvgQITime', partitionSize as '@PartitionSize', TS.MediaAgentName as '@MediaAgentName' FROM #tmpSubStores TS WHERE TS.StoreId = T.StoreId FOR XML PATH ('PartitionInfo') ) AS XML ) FROM #tmpStores T INNER JOIN archCopySIDBStore CS WITH (NOLOCK) ON T.StoreId = CS.SIDBStoreId INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON CS.CopyId = AGC.id AND AGC.dedupeFlags&(262144|134217728) = 262144 AND AGC.type <> 5 -- CVA_AGCOPYTYPE_TRANSITIVE LEFT OUTER JOIN #StoreDedupe S ON T.StoreId = S.SIDBStoreId LEFT OUTER JOIN #upgradeTov5DDBs U ON T.StoreId = U.SIDBStoreID FOR XML PATH ('Metric') ) IF @STANDALONE > 0 Select * from #tmpStores SET @surveyXML = ( SELECT @ddbperformance FOR XML PATH ('DDBPerformance') -- Replace <> with specific name ) IF OBJECT_ID('tempdb..#tmpSubStores') IS NOT NULL DROP TABLE #tmpSubStores IF OBJECT_ID('tempdb..#tmpStores') IS NOT NULL DROP TABLE #tmpStores IF OBJECT_ID('tempdb..#StoreDedupe') IS NOT NULL DROP TABLE #StoreDedupe IF OBJECT_ID('tempdb..#SIAppID') IS NOT NULL DROP TABLE #SIAppID -- MR 172535 IF object_id('tempdb.dbo.#upgradeTov5DDBs') is not null DROP TABLE #upgradeTov5DDBs --------- END SURVEY QUERY --------- --------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY --------- DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) SET @outputXML = ( SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA') ) --SELECT cast (@surveyXML as XML) IF @STANDALONE > 0 SELECT @ddbperformance SET NOCOUNT OFF --------- END - GENERATED CODE ---------