{"id":5161,"date":"2018-10-20T12:58:52","date_gmt":"2018-10-20T12:58:52","guid":{"rendered":"http:\/\/mehmetsalihdeveci.net\/?p=5161"},"modified":"2019-02-28T08:18:23","modified_gmt":"2019-02-28T08:18:23","slug":"sql-server-performance-troubleshooting-5","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-performance-troubleshooting-5\/","title":{"rendered":"SQL Server Performance Troubleshooting -5"},"content":{"rendered":"<p>Merhaba Arkada\u015flar,<\/p>\n<p>Bu yaz\u0131mda sizlere \u00f6nceki yaz\u0131mda ba\u015flad\u0131\u011f\u0131m SQL Server da Performance Troubleshooting kavram\u0131n\u0131 ve nas\u0131l yap\u0131ld\u0131\u011f\u0131n\u0131n devam\u0131n\u0131 anlat\u0131yor olaca\u011f\u0131m.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5162\" src=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/2453_tuning_clip_image002.gif\" alt=\"\" width=\"513\" height=\"391\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>Bu konunun devam\u0131 olan di\u011fer yaz\u0131lar\u0131m\u0131n linkleri a\u015fa\u011f\u0131daki gibidir.<\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2018\/10\/06\/sql-server-performance-troubleshooting-1\/\">https:\/\/ittutorial.org\/2018\/10\/06\/sql-server-performance-troubleshooting-1\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2018\/10\/07\/sql-server-performance-troubleshooting-2\/\">https:\/\/ittutorial.org\/2018\/10\/07\/sql-server-performance-troubleshooting-2\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2018\/10\/13\/sql-server-performance-troubleshooting-3\/\">https:\/\/ittutorial.org\/2018\/10\/13\/sql-server-performance-troubleshooting-3\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2018\/10\/14\/sql-server-performance-troubleshooting-4\/\">https:\/\/ittutorial.org\/2018\/10\/14\/sql-server-performance-troubleshooting-4\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2018\/10\/20\/sql-server-performance-troubleshooting-5\/\">https:\/\/ittutorial.org\/2018\/10\/20\/sql-server-performance-troubleshooting-5\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2018\/10\/21\/sql-server-performance-troubleshooting-6\/\">https:\/\/ittutorial.org\/2018\/10\/21\/sql-server-performance-troubleshooting-6\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2014\/03\/01\/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-1\/\">https:\/\/ittutorial.org\/2014\/03\/01\/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-1\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2014\/03\/03\/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-2\/\">https:\/\/ittutorial.org\/2014\/03\/03\/sql-server-dmv-ve-dmf-kavramlari-ve-kullanimlari-2\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2013\/03\/28\/sqldiag-araci-ile-veritabani-performans-verisi-toplama\/\">https:\/\/ittutorial.org\/2013\/03\/28\/sqldiag-araci-ile-veritabani-performans-verisi-toplama\/<\/a><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/2013\/03\/29\/rml-utilities-tool-u-ile-veritabani-performansini-raporlama\/\">https:\/\/ittutorial.org\/2013\/03\/29\/rml-utilities-tool-u-ile-veritabani-performansini-raporlama\/<\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>Performance Sorunlar\u0131n\u0131 Giderme<\/strong><\/p>\n<p>SQL Server veritabanlar\u0131nda performans problemlerini gidermek i\u00e7in yukarda bir \u00e7ok y\u00f6ntemle analiz etti\u011fimiz verileri de\u011ferlendirmek gerekiyor.\u00a0 \u00d6r: Bir sorgu \u00e7ok fazla CPU zaman\u0131 harc\u0131yor bu durumda sorgu incelenmeli ve SQL Tuning yap\u0131lmal\u0131d\u0131r. Yada bir sorgu \u00e7ok fazla I\/O yap\u0131yorsa sorgu incelenmeli ve neden I\/O oran\u0131 azalt\u0131lam\u0131yor bu incelenmelidir ? Bellek mi \u00e7ok yetersiz yada sorgunun kulland\u0131\u011f\u0131 index bozulmu\u015f mu ? Yada tablolar\u0131n ve indexlerin istatistikleri bozulmu\u015fmu ? Bu sorular\u0131n cevab\u0131 aranmal\u0131 ve gerekli durumda \u00f6ng\u00f6r\u00fclen m\u00fcdahaleler yap\u0131lmal\u0131d\u0131r.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Index Maintenance<\/strong><\/p>\n<p>SQL Server bir sorguyu \u00e7al\u0131\u015ft\u0131r\u0131rken kulland\u0131\u011f\u0131 query optimizer en do\u011fru execution plan\u0131 \u00e7\u0131kart\u0131r ve sorgu buna g\u00f6re \u00e7al\u0131\u015f\u0131r. Ancak index ve tablolar\u0131n istatistikleri g\u00fcncel olmad\u0131\u011f\u0131 zaman Query optimizer yanl\u0131\u015f kararlar verebilir yada beklenen execution plan\u0131 \u00e7\u0131karmayabilir \u00e7\u00fcnk\u00fc execution plan \u00e7\u0131kart\u0131l\u0131rken istatistik bilgileri kullan\u0131l\u0131r. En do\u011fru execution plan\u0131n \u00e7\u0131kar\u0131labilmesi i\u00e7in Tablo ve indexlerin istatistiklerinin g\u00fcncel olmas\u0131 \u015fartt\u0131r.<\/p>\n<p>SQL Server execution plan \u00e7\u0131kar\u0131rken tablolar i\u00e7in a\u015fa\u011f\u0131daki incelemeleri yapar.<\/p>\n<ul>\n<li>Sorguda ilgili tablolar\u0131n sat\u0131r say\u0131s\u0131<\/li>\n<li>Tablolar\u0131n data pages say\u0131lar\u0131<\/li>\n<li>En son update istatistik ten sonra olu\u015fan transactionlar\u0131n say\u0131s\u0131<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>SQL Server da default kurulumda indexlerin otomatik istatisti\u011fini alan Auto update statistics \u00f6zelli\u011fi aktif olarak gelir.\u00a0 SQL Server da herhangi bir tablonun en son ne zaman auto update g\u00f6rd\u00fc\u011f\u00fcn\u00fc \u00f6\u011frenmek i\u00e7in a\u015fa\u011f\u0131daki sp kullan\u0131labilir.<\/p>\n<p>sp_autostats \u2018Table_name\u2019<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5163\" src=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/22.png\" alt=\"\" width=\"484\" height=\"174\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/22.png 484w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/22-300x108.png 300w\" sizes=\"auto, (max-width: 484px) 100vw, 484px\" \/><\/p>\n<p>\u0130ndexlerin en son ne zaman istatisti\u011finin g\u00fcncellendi\u011fine ise a\u015fa\u011f\u0131daki scriptle bak\u0131labilir.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<pre>SELECT d.name,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name(si.[object_id]) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS [TableName]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , CASE\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN si.[stats_id] = 0 then 'Heap'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN si.[stats_id] = 1 then 'CL'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN INDEXPROPERTY ( si.[object_id], si.[name], 'IsAutoStatistics') = 1 THEN 'Stats-Auto'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN INDEXPROPERTY ( si.[object_id], si.[name], 'IsHypothetical') = 1 THEN 'Stats-HIND'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN INDEXPROPERTY ( si.[object_id], si.[name], 'IsStatistics') = 1 THEN 'Stats-User'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN si.[stats_id] BETWEEN 2 AND 1004 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[stats_id]), 3)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE 'Text\/Image'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 END \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS [IndexType]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , si.[name] \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS [IndexName]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , si.[stats_id]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS [IndexID]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , CASE\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN si.[stats_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[stats_id]) &lt; DATEADD(m, -1, getdate())\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '!! More than a month OLD !!'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN si.[stats_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[stats_id]) &lt; DATEADD(wk, -1, getdate())\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '! Within the past month !'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN si.[stats_id] BETWEEN 1 AND 250 THEN 'Stats recent'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE ''\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 END \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS [Warning]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , STATS_DATE (si.[object_id], si.[stats_id]) \u00a0\u00a0\u00a0 AS [Last Stats Update]\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , no_recompute\r\n\r\nFROM sys.stats AS si,sys.databases d\r\n\r\nWHERE OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1 and d.name='DB_NAME'\r\n\r\n--\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND (INDEXPROPERTY ( si.[object_id], si.[name], 'IsAutoStatistics') = 1\r\n\r\n--\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR INDEXPROPERTY ( si.[object_id], si.[name], 'IsHypothetical') = 1\r\n\r\n--\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR INDEXPROPERTY ( si.[object_id], si.[name], 'IsStatistics') = 1)\r\n\r\nORDER BY [Warning] asc\r\n\r\ngo<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5164\" src=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/23.png\" alt=\"\" width=\"614\" height=\"256\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/23.png 1223w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/23-300x125.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/23-768x320.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/23-1024x426.png 1024w\" sizes=\"auto, (max-width: 614px) 100vw, 614px\" \/><\/p>\n<p>\u0130statisti\u011fi g\u00fcncellenmesi gereken \u0130ndexler ve index update scriptleri veren sorgu a\u015fa\u011f\u0131daki gibidir.<\/p>\n<p>&nbsp;<\/p>\n<pre>select 'UPDATE STATISTICS\u00a0 '+OBJECT_NAME(s.object_id)\r\n\r\n\/*objname = OBJECT_NAME(s.object_id),\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.object_id,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_name= i.name,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_type_desc,\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 avg_fragmentation_in_percent *\/\r\n\r\nfrom sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) as s\r\n\r\njoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id\u00a0\r\n\r\nwhere avg_fragmentation_in_percent&gt;95\r\n\r\norder by avg_fragmentation_in_percent desc, page_count desc;\r\n\r\n\r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5165\" src=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/24.png\" alt=\"\" width=\"614\" height=\"331\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/24.png 660w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/24-300x162.png 300w\" sizes=\"auto, (max-width: 614px) 100vw, 614px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Baz\u0131 tablolar \u00fczerinde ise \u00e7ok fazla sorgu \u00e7ekilmesine ra\u011fmen index bulunmayabilir. Bu tablolarda index bulunmad\u0131\u011f\u0131 i\u00e7in sorgular \u00e7ekilirken gereksiz bir s\u00fcr\u00fc veride diskten okunur buda sistemi gereksiz yere yoracakt\u0131r. Veritaban\u0131nda olmas\u0131 gereken indexlere missing index denir. Missing indexleri a\u015fa\u011f\u0131daki sorguyla tespit edebiliriz.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<pre>SELECT\r\n\r\n\u00a0 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)\r\n\r\n\u00a0 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'\r\n\r\n\u00a0 + ' ON ' + mid.statement\r\n\r\n\u00a0 + ' (' + ISNULL (mid.equality_columns,'')\r\n\r\n\u00a0\u00a0\u00a0 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END\r\n\r\n\u00a0\u00a0\u00a0 + ISNULL (mid.inequality_columns, '')\r\n\r\n\u00a0 + ')'\r\n\r\n\u00a0 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,\r\n\r\n\u00a0 migs.avg_total_user_cost * (migs.avg_user_impact \/ 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, mid.statement,\r\n\r\n\u00a0 migs.*, mid.database_id, mid.[object_id]\r\n\r\nFROM sys.dm_db_missing_index_groups mig\r\n\r\nINNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle\r\n\r\nINNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle\r\n\r\nWHERE migs.avg_total_user_cost * (migs.avg_user_impact \/ 100.0) * (migs.user_seeks + migs.user_scans) &gt; 10\r\n\r\nORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5166\" src=\"https:\/\/salihdeveci.files.wordpress.com\/2019\/01\/25.png\" alt=\"\" width=\"614\" height=\"377\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/25.png 1229w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/25-300x184.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/25-768x472.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/01\/25-1024x629.png 1024w\" sizes=\"auto, (max-width: 614px) 100vw, 614px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>B\u00f6ylece bu yaz\u0131n\u0131n sonuna gelmi\u015f bulunmaktay\u0131m bir sonraki yaz\u0131da SQL Server Performance Troubleshooting e devam ediyor olaca\u011f\u0131m.<\/p>\n\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\">Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda a\u015fa\u011f\u0131daki konularda 7&#215;24 Uzman Dan\u0131\u015fmanlara yada E\u011fitimlere mi\u00a0<\/span><span style=\"color: #ff0000;\">\u0130htiyac\u0131n\u0131z var mehmet.deveci@gridgroup.com.tr adresine mail atarak Bizimle ileti\u015fime ge\u00e7ebilirsiniz.<\/span><\/p>\n<p><span style=\"color: #ff0000;\">&#8211; Oracle Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle Veritaban\u0131 Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Exadata Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Exadata Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; SQL Server Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; SQL Server Veritaban\u0131 Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Goldengate Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Goldengate Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Linux Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Linux Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle EBS Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle EBS Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Weblogic Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Weblogic Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle Veritaban\u0131 E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle VM Server Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle VM Server Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle EPPM Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle EPPM Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle Primavera Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle Primavera Bak\u0131m ve Destek<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; SQL Server E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Goldengate E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Exadata E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Linux E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle EBS E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle VM Server E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Weblogic E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle EPPM E\u011fitimleri<\/span><br \/>\n<span style=\"color: #ff0000;\">&#8211; Oracle Primavera E\u011fitimleri<\/span><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Merhaba Arkada\u015flar, Bu yaz\u0131mda sizlere \u00f6nceki yaz\u0131mda ba\u015flad\u0131\u011f\u0131m SQL Server da Performance Troubleshooting kavram\u0131n\u0131 ve nas\u0131l yap\u0131ld\u0131\u011f\u0131n\u0131n devam\u0131n\u0131 anlat\u0131yor olaca\u011f\u0131m.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[19,23],"tags":[457,480,586,1028,1031,1120,1199,1266,1267,1623,1629,1644,1659,1661,1667,1859,1860,1861],"class_list":["post-5161","post","type-post","status-publish","format-standard","","category-sql-server","category-veritabani","tag-database-bakim-ve-destek","tag-dba-danismanlik","tag-exadata-egitim","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-oracle","tag-oracle-danismanlik","tag-oracle-egitim","tag-oracle-egitimi","tag-sql-server-bakim-destek","tag-sql-server-danismanlik","tag-sql-server-egitim","tag-sql-server-performance-troubleshooting","tag-sql-server-performans-tuning","tag-sql-server-sorun-cozme","tag-veritabani-bakim-ve-destek","tag-veritabani-danismanlik","tag-veritabani-egitim"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5161","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=5161"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5161\/revisions"}],"predecessor-version":[{"id":5787,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/5161\/revisions\/5787"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=5161"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=5161"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=5161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}