{"id":5033,"date":"2016-10-03T14:17:09","date_gmt":"2016-10-03T14:17:09","guid":{"rendered":"http:\/\/www.gencayyildiz.com\/blog\/?p=5033"},"modified":"2016-10-03T14:17:09","modified_gmt":"2016-10-03T14:17:09","slug":"sql-server-2016-live-query-statisticscanli-sorgu-istatistigi","status":"publish","type":"post","link":"https:\/\/www.gencayyildiz.com\/blog\/sql-server-2016-live-query-statisticscanli-sorgu-istatistigi\/","title":{"rendered":"SQL Server 2016 &#8211; Live Query Statistics(Canl\u0131 Sorgu \u0130statisti\u011fi)"},"content":{"rendered":"<div id=\"fb-root\"><\/div>\n<p>Merhaba,<\/p>\n<p>K\u00e2h yaz\u0131l\u0131m\u0131m\u0131zda olu\u015fturmu\u015f oldu\u011fumuz algoritmada, k\u00e2h veritaban\u0131m\u0131zda yazm\u0131\u015f oldu\u011fumuz herhangi bir sorguda art\u0131k temel olarak dikkate al\u0131nan, mevcut yap\u0131n\u0131n performans ve h\u0131z verimlili\u011fidir. Haliyle ilgili i\u00e7eri\u011fimiz veritaban\u0131yla kapsam\u0131nda oldu\u011fundan dolay\u0131 olu\u015fturdu\u011fumuz mevcut sorgular\u0131n performanslar\u0131 bizim i\u00e7in olduk\u00e7a \u00f6nemlidir. \u0130\u015fte bu \u00f6nem arz eden yap\u0131ya \u00f6zel SQL Server 2016&#8217;da <em>Canl\u0131 Sorgu \u0130statisti\u011fi<\/em> olarak nitelendirdi\u011fimiz <em>Live Query Statistics<\/em> \u00f6zelli\u011fi gelmi\u015ftir.<\/p>\n<p>Live Query Statistics \u00f6zelli\u011fi ile sorgular\u0131m\u0131z\u0131n ya\u015fam d\u00f6ng\u00fcs\u00fcn\u00fc takip ederek; performans, h\u0131z, tamamlanma y\u00fczdeli\u011fi vs. gibi de\u011fi\u015fkenleri detayl\u0131 bir \u015fekilde elde edebilmekteyiz.<\/p>\n<p>SQL Server, \u00fczerinde \u00e7al\u0131\u015ft\u0131r\u0131lacak bir sorguyu \u00e7al\u0131\u015ft\u0131rabilmek i\u00e7in o sorguya \u00f6zel Execution Plan dedi\u011fimiz bir \u00e7al\u0131\u015fma yolu olu\u015fturmaktad\u0131r. Sorgunun \u00e7al\u0131\u015fma an\u0131nda Execution Plan \u00fczerinde hangi tablo ve indeksten hangi veri okunuyor gibisinden canl\u0131 olarak bu verileri g\u00f6rebiliyor, maliyeti hesaplayabiliyoruz.<\/p>\n<p>Live Query Statistics \u00f6zelli\u011fi ile \u00e7al\u0131\u015fan sorgular\u0131n anl\u0131k olarak hangi veriyi okudu\u011funu, y\u00fczdelik olarak i\u015flemin ne kadar\u0131n\u0131n tamamland\u0131\u011f\u0131n\u0131, zaman\u0131n\u0131, maliyetini vs. gibi de\u011fi\u015fken yahut fakt\u00f6rleri net bir \u015fekilde takip edebiliyoruz. Haliyle biz bu \u00f6zellik sayesinde sorgumuzda hangi operat\u00f6rlerin \u00e7al\u0131\u015ft\u0131\u011f\u0131n\u0131 ve sorgunun ne zaman bitece\u011fini takip edebiliyoruz.<\/p>\n<p>Live Query Statistics \u00f6zelli\u011fi her ne kadar SQL Server 2016 ile gelmi\u015f olsada asl\u0131nda SQL Server 2014 ile beraber gelen <strong>sys.dm_exec_query_profiles<\/strong> ad\u0131nda bir DMV(Dynamic Management View)&#8217;e dayanmaktaym\u0131\u015f. Bu DMV ile sorgunun \u00e7al\u0131\u015fma zaman\u0131nda belli ba\u015fl\u0131 bilgiler edinilebiliyor, tamamlanmas\u0131na ne kadar zaman kald\u0131\u011f\u0131 hesaplanabiliyormu\u015f. Lakin Live Query Statistics \u00f6zelli\u011fi ile bu i\u015flem art\u0131k daha basit hale gelmi\u015f. <em>(Anlayaca\u011f\u0131n\u0131z gibi bende sys.dm_exec_query_profiles isimli DMV yap\u0131s\u0131n\u0131 \u015fu ana kadar hi\u00e7 duymam\u0131\u015f bulunmaktay\u0131m.)<\/em><\/p>\n<p>Tabi Live Query Statistics \u00f6zelli\u011finin sorgu ile ilgili verisel bilgileri elde edebilmemiz i\u00e7in getirdi\u011fi en b\u00fcy\u00fck kolayl\u0131k s\u00fcrece g\u00f6rsel bir aray\u00fcz kazand\u0131rmas\u0131d\u0131r.<\/p>\n<p>\u015eimdi Live Query Statistics \u00f6zelli\u011fini \u00f6rneklendirebilmek i\u00e7in Northwind veritaban\u0131nda pratik uygulama \u00fczerinden gidece\u011fiz.<\/p>\n<p>A\u015fa\u011f\u0131daki ekranda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi ilgili sorguyu Live Query Statistics \u00f6zelli\u011fi ile analiz etmek istiyorsak e\u011fer &#8220;Include Live Query Statistcs&#8221; butonunu aktifle\u015ftiriyoruz.<br \/>\n<a href=\"http:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5044 size-full\" src=\"http:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi.png\" alt=\"SQL Server 2016 - Live Query Statistics(Canl\u0131 Sorgu \u0130statisti\u011fi)\" width=\"448\" height=\"84\" srcset=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi.png 448w, https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-300x56.png 300w\" sizes=\"auto, (max-width: 448px) 100vw, 448px\" \/><\/a><\/p>\n<p>Bu ad\u0131mdan sonra ilgili sorguyu \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131m\u0131zda a\u015fa\u011f\u0131daki g\u00f6r\u00fcnt\u00fcde oldu\u011fu gibi Execution Plan\u0131m\u0131z\u0131 net bir \u015fekilde inceleyelim.<\/p>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.youtube-nocookie.com\/embed\/fxCWVhK-yLE\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/p>\n<p>\u0130statistik s\u00fcrecinden herhangi bir T zaman\u0131ndaki ekran g\u00f6r\u00fcnt\u00fcs\u00fcn\u00fc a\u015fa\u011f\u0131ya alal\u0131m.<\/p>\n<p><a href=\"http:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5052 size-full\" src=\"http:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-1.png\" alt=\"SQL Server 2016 - Live Query Statistics\" width=\"792\" height=\"395\" srcset=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-1.png 792w, https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-1-300x150.png 300w, https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-1-768x383.png 768w\" sizes=\"auto, (max-width: 792px) 100vw, 792px\" \/><\/a><\/p>\n<p>Evet, g\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere sol alt k\u00f6\u015fede sorgunun 13%&#8217;\u00fcn\u00fcn \u00e7al\u0131\u015ft\u0131r\u0131ld\u0131\u011f\u0131n\u0131, &#8220;Satislar&#8221; tablosunun 2.692 saniye i\u00e7erisinde 10%&#8217;unun tamamland\u0131\u011f\u0131n\u0131, &#8220;[Satis Detaylari]&#8221; tablosunun 2.692 saniyede 100%&#8217;\u00fcn\u00fcn tamamland\u0131\u011f\u0131n\u0131 g\u00f6rebiliyoruz.<\/p>\n<p>Burada dikkat etmemiz gereken nokta kesikli \u00e7izgilerde i\u015flem devam ediyor anlam\u0131na gelirken, i\u015flemi biten g\u00f6stergede s\u00fcrekli \u00e7izgiler g\u00f6z\u00fckmektedir.<\/p>\n<p>Ayr\u0131ca istatistik nesnesinin \u00fczerine geldi\u011fimiz vakit ka\u00e7 kay\u0131t\u0131n ka\u00e7 saniyede i\u015flendi\u011fini a\u015fa\u011f\u0131daki gibi g\u00f6rebilmekteyiz.<\/p>\n<p><a href=\"http:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-5057 size-full\" src=\"http:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-2.png\" alt=\"sql-server-2016-live-query-statisticscanli-sorgu-istatistigi\" width=\"558\" height=\"433\" srcset=\"https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-2.png 558w, https:\/\/www.gencayyildiz.com\/blog\/wp-content\/uploads\/2016\/10\/SQL-Server-2016-Live-Query-StatisticsCanl\u0131-Sorgu-\u0130statisti\u011fi-2-300x233.png 300w\" sizes=\"auto, (max-width: 558px) 100vw, 558px\" \/><\/a><\/p>\n<p>G\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere <em>Actual Number of Rows<\/em> b\u00f6l\u00fcm\u00fcnde g\u00f6z\u00fcken\u00a01269932\u00a0verinin, <em>ElapsedTime<\/em> b\u00f6l\u00fcm\u00fcnde g\u00f6z\u00fcken 17001 zamanda i\u015flendi\u011fini g\u00f6stermektedir.<\/p>\n<p>Live Query Statistics \u00f6zelli\u011fi her ne kadar sorgumuzla alakal\u0131 bize t\u00fcm gerekli bilgileri detayl\u0131ca versede haliyle bu hizmetin bedelini performans ve maliyetten bizlere \u00f6detecektir. Bu y\u00fczden bu \u00f6zelli\u011fi kullan\u0131rken dikkatli olmal\u0131, her sorguya \u00f6zel de\u011fil, ger\u00e7ekten a\u011f\u0131r ve kompleks sorgular\u0131 dahada iyile\u015ftirmek ama\u00e7l\u0131 bu \u00f6zelli\u011fi kullanman\u0131z\u0131 tavsiye ediyorum.<\/p>\n<p>Son olarak bu yap\u0131n\u0131n <em>Column Store Index<\/em>, <em>Memory Optimized Table<\/em> ve <em>Natively Compiled Stored Procedure<\/em> yap\u0131lar\u0131nda kullanamayaca\u011f\u0131m\u0131z\u0131, sonraki g\u00fcncellemelerde kullanmay\u0131 temenni etti\u011fimizi bildiririm.<\/p>\n<p>Sonraki yaz\u0131lar\u0131mda g\u00f6r\u00fc\u015fmek \u00fczere&#8230;<\/p>\n<p>\u0130yi \u00e7al\u0131\u015fmalar dilerim&#8230;<\/p>\n<p>&nbsp;<\/p>\n<!-- AddThis Advanced Settings generic via filter on the_content --><!-- AddThis Share Buttons generic via filter on the_content -->","protected":false},"excerpt":{"rendered":"<p>Merhaba, K\u00e2h yaz\u0131l\u0131m\u0131m\u0131zda olu\u015fturmu\u015f oldu\u011fumuz algoritmada, k\u00e2h veritaban\u0131m\u0131zda yazm\u0131\u015f oldu\u011fumuz herhangi bir sorguda art\u0131k temel olarak dikkate al\u0131nan, mevcut yap\u0131n\u0131n performans ve h\u0131z verimlili\u011fidir. Haliyle ilgili i\u00e7eri\u011fimiz veritaban\u0131yla kapsam\u0131nda oldu\u011fundan dolay\u0131 olu\u015fturdu\u011fumuz mevcut sorgular\u0131n&#46;&#46;&#46;<!-- AddThis Advanced Settings generic via filter on get_the_excerpt --><!-- AddThis Share Buttons generic via filter on get_the_excerpt --><\/p>\n","protected":false},"author":1,"featured_media":3415,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1412],"tags":[1460,1453,1462,1458,1459,1461,1454,1456,1452,1463,1464,1455,1413,1457],"class_list":["post-5033","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server-2016","tag-actual-number-of-rows","tag-canli-sorgu-istatistigi","tag-column-store-index","tag-dmv","tag-dynamic-management-view","tag-elapsedtime","tag-execution","tag-execution-plan","tag-live-query-statistics","tag-memory-optimized-table","tag-natively-compiled-stored-procedure","tag-plan","tag-sql-server-2016","tag-sys-dm_exec_query_profiles"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/5033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=5033"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/5033\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media\/3415"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=5033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=5033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=5033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}