SELECT RowID, DbName, Fark, [Table], Statistic, --updStat, updStatFull, [Stats Last Updated], Rows, [Rows Sampled], [Unfiltered Rows], [Row Modifications], [Histogram Steps], startDate, endDate FROM StaticUpdate WITH (nolock) -- WHERE startDate IS NULL ORDER BY startDate DESC
USE [DPAudit] GO /****** Object: StoredProcedure [dbo].[dbStatsUpdateFullScan] Script Date: 10/2/2019 4:37:58 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER procedure [dbo].[dbStatsUpdateFullScan] -- exec dpAudit.[dbo].dbStatsUpdateFullScan @Databases = 'ORKA', @execute=0
@Databases nvarchar(max) = NULL, -- ALL (Tüm veritabanlari), dpAudit (bir Database adi) @execute int=0, @SortLevel int = 99
as set nocount on DECLARE @cmd1 nvarchar(max) DECLARE @cmd2 nvarchar(max)
INSERT INTO #dbSize select DATABASE_NAME = db_name(s_mf.database_id), DATABASE_SIZE = convert(int, case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int... when sum(convert(bigint,s_mf.size)) >= 268435456 then null else sum(convert(bigint,s_mf.size))*8 -- Convert from 8192 byte pages to Kb end) from sys.master_files s_mf where s_mf.state = 0 and -- ONLINE has_dbaccess(db_name(s_mf.database_id)) = 1 and -- Only look at databases to which we have access db_name(s_mf.database_id) NOT IN ('master','msdb','tempdb','model') group by s_mf.database_id order by 2
DECLARE @Sayac INT = 1;
WHILE @Sayac <= (select count(*) from #dbSize) BEGIN select @Databases = [DbName] from #dbSize WHERE [rowID] = @Sayac
END; drop table #dbSize END ---staticupdate tablosuna eklenilen kayitlari dönerek execute edildigi yer
SET @Sayac =(select min(RowID) from StaticUpdate where startDate is null) declare @Sorting int = 99
WHILE @Sayac<= (select max(RowID) from [dpAudit].[dbo].[StaticUpdate] where startDate is null) BEGIN
select @cmd1 = [updStatFull], @Sorting = Sorting from [dpAudit].[dbo].[StaticUpdate] WHERE [RowID] = @Sayac if @execute=1 BEGIN update [dpAudit].[dbo].[StaticUpdate] set startDate=Getdate() WHERE [RowID] = @Sayac;
IF @Sorting >= @SortLevel exec (@cmd1)
update [dpAudit].[dbo].[StaticUpdate] set endDate=Getdate() WHERE [RowID] = @Sayac; END
else BEGIN print @cmd1 END SET @Sayac += 1;
END;
SELECT RowID, DbName, Fark, [Table], Statistic, updStat, updStatFull, [Stats Last Updated], Rows, [Rows Sampled], [Unfiltered Rows], [Row Modifications], [Histogram Steps], startDate, endDate --,round( [Row Modifications]*1.0 / [Rows]*100, 2) as Oran, ,CASE WHEN [Row Modifications] >= [Rows] THEN '99' WHEN [Unfiltered Rows] > [Rows] THEN '90' WHEN round( [Row Modifications]*1.0 / [Rows]*100, 2) >= 50 THEN 88 WHEN [Row Modifications] > 0 THEN '77' ELSE 1 END Sorting FROM StaticUpdate WHERE (startDate IS NULL) ORDER BY Sorting desc, [Rows]
