MSSQL Server Statistics Job Komutları

Bu makalede mssql server yapısında istatistik job komutlarını görmüş olacağız. Sistemimizde performans sorunu varsa yapılması gereken ilk işlemlerden biri istatistikleri güncel tutmaktır. Aşağıdaki komut ile Always On altında bulunan tüm veritabanlarının istatistiklerini güncellemiş oluyoruz. Aşağıdaki komutta bulunan Fragmentation bölümünde belirli değerler verilip indexslerimizde rebuild veya reorganize yapılabilir. İndexs konusunda ilgili bölümleri detaylı bir şekilde açıklamış olacağız.

DECLARE @tsql nvarchar(max)=''
DECLARE @AGName nvarchar(50)
SELECT  @AGName=grp.ag_name
FROM sys.dm_hadr_availability_replica_states AS a JOIN
sys.availability_replicas AS b ON b.replica_id = a.replica_id left outer join
sys.dm_hadr_name_id_map as grp on grp.ag_id = a.group_id
WHERE a.role=1

IF   @AGName = 'AG_NAME'
BEGIN
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsModificationLevel int = 5,
@OnlyModifiedStatistics='Y',
@LogToTable = 'Y'
END

Not: Sistem üzerinde birden fazla AG varsa yukarıdaki komuttaki AG kontrol ifadesinin kullanılmaması gerekmektedir.

Not: Bazı veritabanı haricinde işlem yapmak istiyorsak değişecek ifademiz sadece @Databases = ‘USER_DATABASES,-DB1,-DB2’ ifadesi olmuş olacaktır.

Not: @StatisticsModificationLevel istatistik değeri yukarıdaki örnekte 5 olduğu zaman istatistik değeri 5’i geçerse otomatik olarak istatistik güncellenmektedir. Yoksa @OnlyModifiedStatistics değeri tek yapılırsa değişen tüm istatistikleri güncellenmektedir.

Not: Sadece belirli database üzerinde işlem yapmak istiyorsa @Databases = ‘DB1’ ifadesinin eklenmesi gerekmektedir.

Not: dbo.IndexOptimize stored procedure için ola.hallengren.com sayfasından ilgili script(MaintenanceSolution.sql) indirilir.İlgili linke ulaşmak için bağlantıya tıklayabilirsiniz. Aşağıdaki resimde dowload bölümünde ilgili script indirilir.

İndirildikten sonra sql server üzerinde  script çalıştırılır. Çalıştırdıktan sonra indexs optimize stored procedures geldiğini görmüş oluyoruz. Programmability>Stored Procedures  bölümünden görebiliriz.

İstatistik komutlarını makalenin başında verilen komutla güncellemek yerine aşağıdaki komutlarda job aracılığıyla veya yeni bir query açarakta yapabiliriz.

Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'

Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = NULL --@StatisticsSample= 100 

Yukarıda bulunan @OnlyModifiedStatistics script’i ise sadece değişmiş değerlerdeki istatistikleri güncellemektedir. Gereksiz yere istatistik güncelleme işlemine girmez.

Not: @UpdateStatistics = ‘ALL’, @OnlyModifiedStatistics=’Y’ değerlerinin birlikte kullanılması Tüm istatistik türleri hedef alınır. Ama sadece son istatistik güncellenmesinden sonra değişiklik görmüş olanlar güncellenir. Kullanılmayan veya değişmeyen istatistiklere dokunmaz. Sadece bir satır değişmiş se bile güncellenme yapılmaktadır. Avantaj: Veri değiştiği anda güncelleme yapılır → daha güncel istatistik, daha doğru sorgu planı Dezavantaj: 1 satır için tüm tablo istatistiği güncellenirse, büyük tabloda kaynak tüketimi yaşanabilir Özellikle FULLSCAN yapıyorsan aşırı I/O yüküne sebep olabilir.

Not: Sadece @UpdateStatistics = ‘ALL’ seçeneği kullanılırsa istatistikte değişiklik olup olmadığına bakmaz. Tüm tabloları güncellemektedir. Kendi belirlediği yapıyla küçük tabloları fullscan büyük tabloları küçük örneklem yapılarak baz alınmaktadır.

@StatisticsSample = NULL bu işlem yukarıdaki komutlarda kullanılırsa FULLSCAN yapmaktadır. Null yerine 10 yazılırsa %10 örneklem ile istatistik günceller. DEFAULT yazılırsa SQL Server’ın kendi örnekleme kararına bırakır. @StatisticsSample= 100 ifadesi ilede fullscan işlemi yapılmaktadır. Bazı sql server sürümlerinde @StatisticsSample = NULL değeri full scan işlemi yapmaz.

Yukarıdaki komutta sadece güncellenen veriler fullscan yapılır. Değişmeyen verilere dokunulmaz.

Güncellenmeye karar verilen istatistikler → FULLSCAN ile güncellenir.
Değişmemiş olanlar → hiç dokunulmaz (dolayısıyla FULLSCAN de olmaz)

Aşağıdaki ifadelerin olması hiç bir etki yapmaz istatistik komutlarında. Bu ifadeler istatistik komutlarında kullanılmasa da olur.

@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL

İstatistik fullscan yapılması isteniyorsa aşağıdaki komut kullanılmaktadır. @OnlyModifiedStatistics komutuyla beraber çalışmaz. Fullscan işlemi yapılmaz istatistikleri only modify şeklinde almaktadır.

EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks2014',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample = 100,
@StatisticsModificationLevel = 10,  
@LogToTable = 'Y';

@StatisticsModificationLevel: İstatistiklerin hangi % değerden sonra güncelleştirileceğini belirtiriz. Yukarıdaki örnekte %10’dan fazla değişen istatistikleri güncellenir.

Aşağıdaki komut ile veritabanı altında alınan tüm istatistiklerden sadece değişenlerin güncellendiği komut

EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks2014', 
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics='Y',
@LogToTable = 'Y'
END

Update istatistik komutunun diğer parametreleri:

• @UpdateStatistics = ‘ALL’: Index ve kolon istatistiklerini güncellemektedir.
• @UpdateStatistics = ‘INDEX’: Sadece Index istatistiklerini günceller.
• @UpdateStatistics = ‘COLUMNS’: Kolon seviyesinde otomatik istatistikleri güncellemektedir._WA_ statistic
• @UpdateStatistics = ‘NULL’: Herhangi bir istatistik güncelleme işlemi yapmaz.

Başka bir istatistiktik güncelleme job’ı:

DECLARE @tsql nvarchar(max)=''
DECLARE @AGName nvarchar(50)
SELECT  @AGName=grp.ag_name
FROM sys.dm_hadr_availability_replica_states AS a JOIN
sys.availability_replicas AS b ON b.replica_id = a.replica_id left outer join
sys.dm_hadr_name_id_map as grp on grp.ag_id = a.group_id
WHERE a.role=1
IF   @AGName = 'AG_NAME'
BEGIN
-- Filtering system databases and user databases from execution

SET @TSQL = '
IF (DB_ID(''?'') > 4)
BEGIN
   PRINT ''********** Rebuilding statistics on database: [?] ************''
   USE [?]; exec sp_updatestats
END
'
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL
END

Not: Index rebuild işlemlerinde istatistikler fullscan güncellenmektedir. Bu işlem index yapısına ait istatistik değerleri için geçerlidir.

Not: Ola Hallengren scriptleri kullanılıyorsa AG konrol şartı eklememize gerek yoktur.

Not: Loginin index ve statistics oluşturması için ddl yetkisinin olması yeterlidir.

Bu makalede istatistikler için job scriptlerini paylaşmış olduk. Başka bir makalede görüşmek dileğiyle..

“Allah yolunda infak edin ve kendinizi kendi ellerinizle tehlikeye atmayın. İyilik edin. Şüphesiz Allah, iyilik edenleri sever.” Bakara Suresi, 195. Ayet