SQL Server Performance Troubleshooting -6

Merhaba Arkadaşlar,

Bu yazımda sizlere önceki yazımda başladığım SQL Server da Performance Troubleshooting kavramını ve nasıl yapıldığının devamını anlatıyor olacağım.

Bu konunun devamı olan diğer yazılarımın linkleri aşağıdaki gibidir.

https://ittutorial.org/2018/10/06/sql-server-performance-troubleshooting-1/

https://ittutorial.org/2018/10/07/sql-server-performance-troubleshooting-2/

https://ittutorial.org/2018/10/13/sql-server-performance-troubleshooting-3/

https://ittutorial.org/2018/10/14/sql-server-performance-troubleshooting-4/

https://ittutorial.org/2018/10/20/sql-server-performance-troubleshooting-5/

https://ittutorial.org/2018/10/21/sql-server-performance-troubleshooting-6/

https://ittutorial.org/2014/03/01/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-1/

https://ittutorial.org/2014/03/03/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-2/

https://ittutorial.org/2013/03/28/sqldiag-araci-ile-veritabani-performans-verisi-toplama/

https://ittutorial.org/2013/03/29/rml-utilities-tool-u-ile-veritabani-performansini-raporlama/

Veritabanında fragmente olmuş indexler ve bunların Rebuild edilmesi için gerekli Create scriptleri aşağıdaki gibidir.

select 'ALTER INDEX [' + i.name +'] on '+OBJECT_NAME(s.object_id)+' REBUILD WITH (ONLINE = ON);' as [Statement],

objname = OBJECT_NAME(s.object_id),

       s.object_id,

       index_name= i.name,

       index_type_desc,

       avg_fragmentation_in_percent,page_count

from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) as s

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id 

where s.database_id = db_id() and s.page_count > 1000 and s.avg_fragmentation_in_percent>10 and i.name is not null

order by avg_fragmentation_in_percent desc;

Tüm indexlerin kullanıcılar tarafından kullanılma durumları ve ne kadar güncellendiğini aşağıdaki scriptle öğrenebiliriz.

select  OBJECT_NAME(s.object_id), i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates

from sys.dm_db_index_usage_stats s

           left outer join sys.indexes as i with (nolock) on s.object_id = i.object_id and s.index_id = i.index_id

where OBJECTPROPERTY(s.object_id, 'IsMsShipped')=0 and name is not null order by user_seeks desc

Veritabanında çok az kullanılan indexler gereksiz yere kaynak tüketmeleri yerine iyice analiz edildikten sonra drop edilebilirler. Veritabanında çok az kullanılan indexler ve drop index scriptlerini veren sorgu aşağıdaki gibidir.

SELECT

o.name

, indexname=i.name

, i.index_id  

, reads=user_seeks + user_scans + user_lookups  

, writes =  user_updates  

, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)

, CASE

              WHEN s.user_updates < 1 THEN 100

              ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

  END AS reads_per_write

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'

FROM sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  

INNER JOIN sys.objects o on s.object_id = o.object_id

INNER JOIN sys.schemas c on o.schema_id = c.schema_id

WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

AND s.database_id = DB_ID()  

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000

ORDER BY reads;

Blocking

SQL Server da bir session transaction başlatıp herhangi bir tablonun bir veya daha fazla satırı için update,delete gibi işlemler yapacaksa ilgili tablonun bu satırlarına lock koyar. Başka bir session aynı işlemi ilgili satırlara yapmaya çalıştığında ise bloklanır. Lock ı koyan transaction lock I kaldırana kadar 2.session 1.session I bekler. Bu olayın çok fazla ve farklı sessionlar tarafından tekrarlanması veritabanında probleme yol açacaktır.

Bloklamanın en büyük sebeblerinden birisi uzun süren sorgulardır.  Bloklamalar veritabanında işlem yapamayacak kadar artmışsa ilgili sessionların DBA ler tarafından kill edilmesi faydalı olacaktır. Yada default kurulumda pasif olarak gelen LOCK_TIMEOUT parametresi aktif edilebilir. Bu parametre aktif edildiği zaman belirlenen sure kadar bekleme yapan 2.session SQL Server tarafından öldürülür ve kullanıcıya Error message 1222, “Lock request time-out period exceeded” hatası döndürülür. LOCK_TIMEOUT parametresi transaction içinde aşağıdaki gibi enable edilir.

Set lock_timeout <Timeout_aralığı>

Veritabanı üzerinde bloklamaya neden olan Sessionlar ve bloklanan sessionların bilgilerini veren script aşağıdaki gibidir.

print ' a blocking report (by using DMVs)'

-- sys.dm_tran_locks       ---> request_session_id (spid  waiting resource)

-- sys.dm_os_waiting_tasks ---> blocking_session_id (spid  blocking resource)

select t1.resource_type as [lock type] ,

      db_name(resource_database_id) as [database],

      t1.resource_associated_entity_id as [blk object],

      t1.request_mode as [lock req] -- lock requested

      ,t1.request_session_id as [waiter sid] -- spid of waiter

      ,t2.wait_duration_ms as [wait time(ms)]

      ,(select text

        from sys.dm_exec_requests as r -- get sql for waiter

           cross apply sys.dm_exec_sql_text(r.sql_handle)

        where r.session_id = t1.request_session_id) as waiter_batch

      ,(select substring(qt.text,r.statement_start_offset/2,

          (case when r.statement_end_offset = -1 then len(convert(nvarchar(max),qt.text)) * 2

           else r.statement_end_offset end - r.statement_start_offset)/2)

        from sys.dm_exec_requests as r

        cross apply sys.dm_exec_sql_text(r.sql_handle) as qt

        where r.session_id = t1.request_session_id) as waiter_stmt -- statement executing now

      ,t2.blocking_session_id as [blocker sid] -- spid of blocker

      ,(select text from sys.sysprocesses as p -- get sql for blocker

        cross apply sys.dm_exec_sql_text(p.sql_handle)

        where p.spid = t2.blocking_session_id) as blocker_stmt

 from sys.dm_tran_locks as t1,

      sys.dm_os_waiting_tasks as t2

 where t1.lock_owner_address = t2.resource_address

 order by [wait time(ms)] desc;

Bloklamaları ayrıca SQL Server Profiler danda seçip izleyebiliriz.

TempDB nin ayarlanması

SQL Server da Global yada Temporary table,index vb objeler Tempdb veritabanında tutulur. Ayrıca sorgular sirasinda ihtiyaç duyulan order by, group by ve union gibi işlemlerin ara sonuçlarıda tempdb de tutulur. Bunun dışında DBCC CHECKDB komutunu çalıştırdığımız zaman oluşan geçici değerlerde Tempdb de tutulur. Kısaca veritabanında meydana gelen transactionlar sırasında oluşan geçici değerler Tempdb veritabanında tutulur. Tempdb düzgün bir performansta çalışması Veritabanı performansı için çok çok önemlidir. Tempdb nin performanslı çalışabilmesi için aşağıdaki adımların yapılması faydalı olacaktır.

• Veritabanı verilerinin saklanması için ayrılan (mdf ve ldf dosyaları) disk (ler) ile Tempdb nin disk(ler) inin ayrı olmalıdır.
• Mümkün olduğunca tempdb için verilecek disk(ler) in yüksek performanslı disklerden seçilmesi gerekmektedir.
• Disk I/O sunu düşürmek için tempdb dosyalarını eşit büyüklüğe sahip dosyalara bölmek gerekmektedir.

Böylece bu yazı dizisinin sonuna gelmiş bulunmaktayım bir sonraki yazıda görüşmek dileğiyle esen kalın.

Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda aşağıdaki konularda 7×24 Uzman Danışmanlara yada Eğitimlere mi İhtiyacınız var [email protected] adresine mail atarak Bizimle iletişime geçebilirsiniz.

– Oracle Veritabanı Danışmanlığı
– Oracle Veritabanı Bakım ve Destek
– Exadata Danışmanlığı
– Exadata Bakım ve Destek
– SQL Server Veritabanı Danışmanlığı
– SQL Server Veritabanı Bakım ve Destek
– Goldengate Danışmanlığı
– Goldengate Bakım ve Destek
– Linux Danışmanlığı
– Linux Bakım ve Destek
– Oracle EBS Danışmanlığı
– Oracle EBS Bakım ve Destek
– Weblogic Danışmanlığı
– Weblogic Bakım ve Destek
– Oracle Veritabanı Eğitimleri
– Oracle VM Server Danışmanlığı
– Oracle VM Server Bakım ve Destek
– Oracle EPPM Danışmanlığı
– Oracle EPPM Bakım ve Destek
– Oracle Primavera Danışmanlığı
– Oracle Primavera Bakım ve Destek
– Oracle Eğitimleri
– SQL Server Eğitimleri
– Goldengate Eğitimleri
– Exadata Eğitimleri
– Linux Eğitimleri
– Oracle EBS Eğitimleri
– Oracle VM Server Eğitimleri
– Weblogic Eğitimleri
– Oracle EPPM Eğitimleri
– Oracle Primavera Eğitimleri