Cihan Güngör | Bilişim Blog

SQL Server I/O Performansı ve Darboğazları Gözlemleme

SQL Server’da düşük performansın teşhisi konmaya çalışılırken, CPU kullanımı veya SQL Server disk I/O kapasitesi gibi herhangi bir tek tanısal veriye dayanan kök sebeple ilgili çıkarımlara varmak yanlış olur. Aslında, tek bir metrik kullanmak, genellikle bir sorunun yanlış teşhisine neden olur.

Karmaşık bağımlılıklar, SQL Server’daki işlemci, I/O ve bellek kullanımı kalıpları arasında var ve daha fazla bellek ekleme veya daha fazla disk kapasitesi veya yapılandırmayı değiştirme gibi ‘diz çöküşü’ giderici önlem almadan önce tam resmi görmemiz gerekiyor.

Sunucu Düzeyinde Görünüm

 

DBA olarak, Windows Kaynak İzleyicisi’ni yukarı çekmeye ve sunucuda anormal bir disk etkinliği olduğunu görmeye başladınız.

Şekil 1 – Sunucuda disk etkinliğinde alışılmadık bir ipucu gösteren Windows Kaynak İzleyicisi

Disk alt sisteminin azami kapasiteye ulaştığını görünce endişeleniyorsunuz , Performans İzleyicisi’nde (PerfMon) bazı diskle ilgili sayaçların bir enstantane görünümünü alıyorsunuz .

Şekil 2 – Ortalama Disk Kuyruğu Uzunluğu ve sn /read, SQL Server Performans İzleyicisi’nde

Ortalama disk kuyruğu uzunluğunda keskin bir artış ile birlikte, diskten veri okumayla ilişkili yüksek gecikme sürelerini göstermektedir. Disk alt sistemi bir tıkanıklık haline geliyor gibi görünüyor; istek sayısına yetişmek için mücadele ediyor, SQL Server disk I/O performansı bozuluyor ve kuyruklar oluşuyor.

Sorunu çözmek için hangi işlemi yapmalısınız? Disk kapasitesini artırmak için yapılacak herhangi bir şey olup olmadığını görmek için sistem yöneticinize başvurmadan önce, derinlemesine ayrıntılı bir şekilde incelemek akıllıca olacaktır.

Yalnızca belirli bir performans metriğine odaklanırsanız, SQL Server performans sorunlarını yanlış tanılamak kolaydır. Bu durumda SQL Server disk I/O’da belirli bir tıkanıklığın varlığına dair kanıt bulunabilir, ancak disk alt sisteminin yetersiz kapasitesinin veya yapılandırmasının sorunun nedeni olduğu sonucuna varmak tehlikelidir . Aşırı disk I/O kullanımı farklı bir nedenden kaynaklanabilir. Sorunun nedenini teyit edecek daha fazla veri toplamalısınız. Ama nereden başlıyorsun?

Daha Derin Kazma Bekleme İstatistikleri

 

Başlamak için iyi bir yer , SQL Server oturumlarının, isteği yerine getirmek için gereken bir kaynağa erişmeden önce işleme bekletme (bekleme) işlemini neden zorladığını gösteren en yaygın nedenleri belirten bir SQL Server bekleme istatistikleri analizidir . Beklemeleri analiz ederek, meşgul bir sunucudaki çekişmenin ve kuyrukların temel nedenlerini belirleyebiliyoruz.

sys.dm_os_waiting_tasksDinamik yönetim görünümünü (DMV) sorgulamak, örneğin Pinal Dave tarafından gösterildiği gibi ilişkili oturumların ve isteklerin ayrıntıları için diğer DMV’lere katılmak için kaynakları bekleyen şu anda yürütülmekte olan istekleri gösterir . Bu örnekte, iki bekleme türünün yanı sıra, ilişkili isteklerin sorgu metni ve planlarını görüyoruz.

http://assets.red-gate.com/products/dba/sql-monitor/assets/files/whitepaper-wait-statistics.pdf

Şekil 3 – ASYNC_NETWORK_IOve PAGEIOLATCHbekler

Tabii ki, sıklıkla bir sorunu geriye dönük olarak araştırıyoruz; bu durumda bu DMV bizim için uygun değil. Bunun yerine, sys.dm_os_wait_statssunucunun son başlatılmasından bu yana tüm istekler arasında biriken tüm bekleme türleri için “toplam yayınlar” içeren DMV’ye sorabilir veya istatistikler DBCC SQLPERFkomutu kullanarak istatistikleri el ile sıfırlayabiliriz . Paul Randal , iyi huylu bekleme türlerinin çoğunu atlayan yararlı bir sorgu kullanıyor. Şekil 4, bu örnek için çıktıyı göstermektedir.

Şekil 4 – Sunucumuz son sıfırlandıktan sonra tüm istekler arasında toplanan bekleme türleri listesi

Geçerli sorguların yaşadığı iki bekleme türü ASYNC_NETWORK_IOve PAGEIOLATCH_SH(Şekil 3) diğerleri ile birlikte bu geçmiş listede de yüksek görünmektedir. Soruşturma altındaki sorun tekrar eden veya uzun süredir devam eden bir mesele değilse, ilgili bekleme türlerinin tarihi beklemeler arasında “boğuldu” olacağı mümkündür. Bu nedenle, bekleme istatistikleri için taban çizgilerini korumak önemlidir (yukarıdaki e-kitapta özetlendiği gibi).

ASYNC_NETWORK_IO

ASYNC_NETWORK_IOBekleme tip bir ağla ilgili bekleme, istemci (örnekte kullanılan sorguları art arda çalıştırmak ve bir gönderme yeterince hızlı veri işleme dair sık bir göstergesidir çok SSMS veri). Genel olarak, bu bekleme türünü, istemci tarafında verimsiz bir veri işleme uyarısı olarak gör.

PAGEIOLATCH_SH

Bu durumda daha ilginç olan PAGEIOLATCH_SHbekleme türü, okuma isteklerinin arabellek önbelleğinde tutulmayan disk sayfalarından okumak için bir mandal almak için beklemek zorunda kaldığı anlamına gelmektedir. Bu, yetersiz güç veya yanlış yapılandırılmış disklerdeki sorunun ortaya çıktığı ilk önermeyi güçlendirecek gibi görünebilir: Disk alt sistemi sayfaları hızlı bir şekilde döndüremezse, sayfa elde etmeyi bekleyen isteklerin uzun bir sıraya ve kilitleme çekişmesine neden olabilir.

Bununla birlikte, bir disk GÇ darboğundan şüpheleniyorsak, kök nedeni nedir? Yararlı bir sonraki adım, G / Ç iş yükü hakkında daha fazla bilgi edinmektir. Örneğin, herhangi bir veritabanı ve dosya IO etkinliğinin belirli “sıcak noktaları” mısınız?

Sanal Dosya İstatistikleri

 

sys.dm_io_virtual_file_statsDMV’yi sorgulayarak, örneğin Glenn Berry ( query # 31 ) tarafından sağlanan bir sorgu kullanarak dosya kullanımı istatistiklerini inceleyebiliriz . Şekil 5, bu örnek için çıktıyı göstermektedir.

Bir SQL Server örneğinde GÇ faaliyeti hakkında bilgi sağlar ve özellikle çeşitli veritabanlarında dağıtılan g / Ç dağıtımının dağılımını ortaya çıkarmak için yararlıdır.

Şekil 5 – sys.dm_io_virtual_file_statsDMV’nin sonuçları

Bu, SQLMonTestveritabanının bir GÇ sıcak noktası olduğunu gösterir. Yine, bu istatistikler son örnek yeniden başlatıldıktan sonra biriktirilir, ancak bekleme istatistiklerinden farklı olarak manuel olarak sıfırlanamaz. Diğer benzeri sorgular (daha önce atıfta bulunulan e-Kitaptaki Listeleme 6 gibi), belirli veritabanları ile ilişkili yüksek okuma veya yazma gecikmesine ilişkin belirli kanıtlar sunabilir. AdventureWorks2014ve SQLMonTestlistenin başında görünür.

Şekil 6 – IO sıcak noktaları olan iki veri tabanı

Sorgu Yürütme İstatistikleri

 

Sorunu bir veya iki veritabanına indirdikten sonra, bir sonraki mantıklı soru şuydu: Hangi veritabanında bu GÇ faaliyetlerinin hangi taleplere neden oluyor? sys.dm_exec_query_statsBelli bir veritabanı için, en yüksek toplam fiziksel okuma ve ilişkili plan tutamaçları (bazı sütunlar çıktıdan çıkarılmıştır) içeren sorguları bulmak için saklanan sorgu yürütme istatistiklerini inceleyebiliriz .

SELECT TOP 10
	        t.text ,
	        execution_count ,
	        statement_start_offset AS stmt_start_offset ,
	        sql_handle ,
	        plan_handle ,
	        total_logical_reads / execution_count AS avg_logical_reads ,
	        total_logical_writes / execution_count AS avg_logical_writes ,
	        total_physical_reads / execution_count AS avg_physical_reads
	FROM	sys.dm_exec_query_stats AS s
	        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
	WHERE	DB_NAME(t.dbid) = 'SQLMonTest'
	ORDER BY avg_physical_reads DESC;

Liste 1 – icra istatistiklerini almak için bir sorgusys.dm_exec_query_stats

Biz sorgulayabilir sys.dm_exec_query_planbu sorgu ile potansiyel sorun olup olmadığını görmek için, bu sorgu için yürütme planı dönmek ve gözden geçirmek Liste 1’deki planını kolları kullanarak.

SQL Monitor: Hepsi bir arada görüntüleme

 

Önceki bölümlerde, bir performans sorununun kökenini teşhis etmeye çalışırken, SQL Server etkinliğinin daha kapsamlı bir resmini oluşturmak için çeşitli araçlar, görünümler ve sorgular kullandık.

SQL Monitor gibi bir araç, tüm bu kullanışlı veriyi tek bir yerde bir araya getirmeye çalışır. Belirli bir hataya veya anormal koşullara yanıt olarak bu durumda muhtemelen bir “uzun süren sorgu uyarısı” yanıt olarak uyarılar yükseltir ve bağlamsal bilgi her türlü enstantane, toplama ve kaynak kullanımının özetlerini ve sunucu üzerindeki sorgu etkinliğini sağlar uyarı zamanı, soruna neden olabilecekleri hızlı bir şekilde belirlememize yardımcı olur.

Aynı örnek için, SQL Monitor, PerfMon aracılığıyla gözlemlediğimiz gibi, okuma gecikmesinin arttığına dair kanıtlar gösterir. Bununla birlikte, aynı grafikte çizebileceğimiz diğer metrikleri de topluyor. SQL Monitor, farklı sunucu kaynaklarının kullanımındaki değişiklikler ve artışları ilişkilendirmemizi ve bunları doğrudan o anda sunucuda meydana gelen etkinlikle ilişkilendirmemize olanak tanıyan, başka yerlerde birçok farklı bilgiyi bir araya getiriyor.

İlginçtir ki, disk IO gecikmesinde çarpıcı artışlar yaşandığımız ilginçtir ki, ilginç bir şekilde, sayfa ömründe sürekli bir düşüş görüyoruz. Bu da, SQL Server’ın sayfaları önbellekten boşaltmak için yer açmak için zorladığı bir bellek baskısı dönemi gösterebilir. istenilen diğer sayfalar.

Şekil 7 – SQL Monitor’un Ort. disk kuyruğu uzunluğu, okuma süresi ve arabellek sayfası ömür beklentisi

Belki de sorun, disk altsistemi değil, ancak küçük boyutlu bir arabellek havuzu ya da sadece çok verimli olmayan sorgular, arabellek havuzunda olmayan birçok sayfayı ister mi?

SQL Monitor, daha önce tartışılan PAGEIOLATCH_SHve ASYNC_NETWORK_IObekleyişleri gösteren, seçilen zaman aralığındaki üst beklemeleri görüntüler .

BEKLEYEN SORGULAR

Şekil 8 – SQL Monitor Top Waits listesi gösteren PAGEIOLATCH_SHveASYNC_NETWORK_IO

Bir bekleme türünü tıklarsak, bu beklemeyle ilişkili önemli sorguların ayrıntılarını görebiliriz. Makalenin önceki bölümlerinde açıklanan manuel yöntemleri kullanarak, sorguların hangi bekleme türlerinden sorumlu olduğunu eşleştirmek, çok fazla dedektif çalışması ve genellikle küçük tahmin gerektiren soruları eşleştirir. SQL Monitor ile, doğrudan bir bekleme türüne ayrıntılı bir şekilde matkap yapabilir ve bu beklemelerden büyük ölçüde sorumlu sorgulara bağlayabiliriz. Ayrıca, önceki “son sıfırlamadan beri” uyarılarını da kaldırır, çünkü bu SQL Monitor istatistikleri sunucunun yeniden başlatılması veya el ile sıfırlanmadan etkilenmez.

Şekil 9, veritabanını PAGEIOLATCHkullanan beklemelerle ilişkili sorguyu göstermektedir

Şekil 9 – PAGEIOLATCH_SHbekleme ile ilişkili verimsiz bir sorgu

Ayrıca, veritabanı seviyesi metriklerine ayrıntılı bir şekilde bakarsak, veritabanı başına en çok sorulan soruları, metinleri, plan tutamaçlarını ve ilişkili beklemeleri de bulabilirsiniz.

İlişkili yürütme planını inceleyerek, Şekil 9’daki sorgu için, filtrelenmemiş birleştirme koşulları, çok büyük tablo taramaları ve pahalı katılma işlemleri bulacağız.

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

%d blogcu bunu beğendi: