<\/div>\n

Merhaba,<\/p>\n

SQL Server, \u00e7al\u0131\u015ft\u0131r\u0131lan sorgular\u0131n \u00e7al\u0131\u015fma esnas\u0131nda olu\u015fturulan Execution Plan’lar\u0131n\u0131 Memory’de Plan Cache<\/em> diye nitelendirdi\u011fimiz bir alanda kaydetmektedir. Bu Plan Cache \u00fczerinde bulunan Execution Plan’lar\u0131n baz\u0131 durumlarda silinme ihtimaline kar\u015f\u0131 SQL Server 2016 ile gelen Query Store<\/strong><\/em> \u00f6zelli\u011fi ile bu Plan Cache alan\u0131n\u0131 logluyoruz ve g\u00f6rsel bir aray\u00fcz vas\u0131tas\u0131yla \u00f6nceki Execution Plan’lar\u0131 g\u00f6rebiliyoruz ve hatta sorgumuzu istedi\u011fimiz planda \u00e7al\u0131\u015ft\u0131rabiliyoruz. Hadi gelin bu Query Store \u00f6zelli\u011finin getirdi\u011fi nimetleri ve nas\u0131l kullan\u0131ld\u0131\u011f\u0131n\u0131 detayland\u0131ral\u0131m.<\/p>\n

SQL Server’da herhangi bir sorguyu \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131m\u0131z esnada direkt olarak sonu\u00e7lar\u0131 elde etsekte asl\u0131nda arkaplanda \u00e7al\u0131\u015fma an\u0131nda yap\u0131lan \u00f6ncelikli i\u015flemler vard\u0131r. Bu i\u015flemler s\u0131ras\u0131yla;<\/p>\n

\n
Parse \u0130\u015flemi<\/em><\/strong>
\nYaz\u0131lan SQL sorgusunun gramer olarak do\u011fru mu? yanl\u0131\u015f m\u0131? oldu\u011funa bak\u0131l\u0131r.<\/li>\n
Binding \u0130\u015flemi<\/em><\/strong>
\nSorgudaki objeler SQL Server’da var m\u0131? yok mu? kontrol edilir.<\/li>\n
Execution \u0130\u015flemi<\/em><\/strong>
\nSorgunun maliyetine ve performans\u0131na g\u00f6re Execution Plan se\u00e7ilir.<\/li>\n<\/ol>\n
Sorgu ilk \u00e7al\u0131\u015ft\u0131r\u0131ld\u0131\u011f\u0131nda Execution Plan olu\u015fturmak ekstra bir y\u00fck ve performans gerektirdi\u011fi i\u00e7in yukar\u0131larda da bahsetti\u011fimiz gibi olu\u015fturulan Execution Plan’lar Plan Cache denen Memory alan\u0131na kaydedilmektedirler. Haliyle var olan Execution Plan, yeniden olu\u015fturulmaya nazaran tercih edilmektedir.<\/p>\n
Plan Cache \u00fczerinde bulunan performansl\u0131, d\u00fc\u015f\u00fck maliyetli ve en optimum seviyedeki bir Execution Plan bazen ister istemez de\u011fi\u015febilmektedir. Hatta yeni\u00a0Execution Plan bir \u00f6ncekine nazaran daha az performans ve y\u00fcksek maliyette olabilir.<\/p>\n
Peki bu de\u011fi\u015fikli\u011fin sebepleri nelerdir?<\/span><\/h3>\n
\n
Baz\u0131 durumlarda SQL Server taraf\u0131ndan Execution Plan’lar silinir ve tekrar hesaplan\u0131r,<\/li>\n
Execution Plan’\u0131n kulland\u0131\u011f\u0131 istatistik objesinin g\u00fcncellenmesi ger\u00e7ekle\u015febilir,<\/li>\n
Yeni bir index olu\u015fturulmu\u015f olabilir,<\/li>\n
Execution Plan’\u0131n kulland\u0131\u011f\u0131 indeksi silmesi yahut g\u00fcncellemesi olabilir,<\/li>\n
SQL Server’\u0131n restart edilmesi olabilir.<\/li>\n<\/ul>\n
vs.. vs…<\/p>\n
\u0130\u015fte bu sayd\u0131\u011f\u0131m\u0131z olas\u0131 sebeplerden dolay\u0131 optimum Execution Plan’\u0131m\u0131z\u0131 kaybetmemeli, bir yerde kaydedebilmeli ve yeri ve zaman\u0131 gelince kullanabilmeliyiz. Bu i\u015flemi SQL Server 2016 ile gelen Query Store \u00f6zelli\u011fi ile yapabiliyoruz. \u015eimdi gelin bu \u00f6zelli\u011fi nas\u0131l kullanabilece\u011fimizi inceleyelim.<\/p>\n
Bu \u00f6zelli\u011fi\u00a0SQL Server Management Studio veya Transact SQL(T-SQL) programlama dili olmak \u00fczere iki farkl\u0131 \u015fekilde de aktifle\u015ftirebilmekteyiz.<\/p>\n
\u00d6ncelikle Management Studio \u00fczerinden konuyu ele alal\u0131m.<\/p>\n
$\"SQL$ <\/a>
Veritaban\u0131m\u0131za sa\u011f t\u0131klayarak “Properties” alan\u0131na t\u0131kl\u0131yoruz.<\/p><\/div>\n
Bu i\u015flemden sonra kar\u015f\u0131m\u0131za a\u015fa\u011f\u0131daki ekran gelecektir.<\/p>\n
$\"SQL$ <\/a><\/p>\n
Yukar\u0131daki ekran g\u00f6r\u00fcnt\u00fcs\u00fcn\u00fc incelersek sol taraftaki “Select a page<\/strong><\/em>” men\u00fcs\u00fcnden “(1<\/span>)<\/strong>Query Store<\/strong><\/em>” kategorisine gelerek \u00f6zelli\u011fimizle ilgili\u00a0b\u00f6l\u00fcme geliyoruz. Bu b\u00f6l\u00fcmde “(2<\/span>)General<\/strong><\/em>” kategorisindeki “Oparation Mode (Requested)<\/strong><\/em><\/span>” se\u00e7ene\u011fini default olan”Off” de\u011ferinden “Read Write<\/em><\/strong><\/span>” olarak de\u011fi\u015ftirerek ilgili veritaban\u0131m\u0131zda Query Store \u00f6zelli\u011fini aktifle\u015ftirmi\u015f oluyoruz.<\/p>\n
Evet, art\u0131k\u00a0Execution Plan’lar\u0131m\u0131z\u0131n takibini net bir \u015fekilde yapabilir, loglayabilir yahut g\u00f6rsel olarak izleyebiliriz. Tabi bu i\u015flemlere gelmeden \u00f6ncelikle \u015fu yukar\u0131daki pencereyi tam olarak izah etmekte fayda var.<\/p>\n
Hemen “(3<\/span>)Monitoring<\/strong><\/em>” kategorisinden ba\u015flayal\u0131m. “Data Flush Interval (Minutes)<\/span><\/strong><\/em>”\u00a0se\u00e7ene\u011fi ile Plan Cache i\u00e7inde yap\u0131lan de\u011fi\u015fikliklerin ne s\u0131kl\u0131kla kaydedilece\u011fini dakika cinsinden ayarl\u0131yoruz. Bu se\u00e7ene\u011fe verilen de\u011fer performans ve maliyet a\u00e7\u0131s\u0131ndan \u00f6nem arz etmektedir. Nihayetinde verilen dakika periyodunda SQL Server\u00a0asenkron bir \u015fekilde\u00a0Query Store i\u00e7in toplad\u0131\u011f\u0131 verileri kal\u0131c\u0131 olmas\u0131 i\u00e7in diske yazacakt\u0131r. Ayn\u0131 kategori alt\u0131nda “Statistics Collection Interval<\/strong><\/em><\/span>” se\u00e7ene\u011fi ise SQL Server’\u0131n ne s\u0131kl\u0131kla Query Store i\u00e7indeki istatiksel verinin toplanaca\u011f\u0131n\u0131 belirtmektedir.\u00a0Data Flush Interval de\u011ferinde oldu\u011fu gibi bu de\u011fer i\u00e7inde performans ve maliyet \u00f6n plandad\u0131r ve dakika, saat ve g\u00fcn olmak \u00fczere de\u011fer almaktad\u0131r.<\/p>\n
Bir di\u011fer kategorimiz olan “(4<\/span>)Query Store Retention<\/strong><\/em>“‘a g\u00f6z atal\u0131m. “Max Size (MB)<\/span><\/strong><\/em>” \u00f6zelli\u011fi ile Query Store \u00f6zelli\u011finin boyutunu, “Query Store Capture Mode<\/span><\/strong><\/em>” \u00f6zelli\u011fi ile de hangi sorgulara ait bilgilerin loglanaca\u011f\u0131 belirtilmektedir ve “None”, “All” ve “Auto” parametrelerini almaktad\u0131r. “Stale Query Threshold (Days)<\/strong><\/em><\/span>” \u00f6zelli\u011fi ise Query Store i\u00e7indeki istatistiksel verilerin fiziksel olarak ne kadar s\u00fcre saklanaca\u011f\u0131 g\u00fcn cinsinden ayarlanmaktad\u0131r.<\/p>\n
“(5<\/span>)Current Disk Usage<\/strong><\/em>” k\u0131sm\u0131nda; sol tarafta ilgili veritaban\u0131n\u0131n boyutunu, sa\u011f tarafta ise Query Store’un kulland\u011f\u0131 diskin alan\u0131n\u0131n boyutunu g\u00f6rebilmekteyiz.<\/p>\n
“(6<\/span>)Purge Query Data<\/strong><\/em>” butonu ile de Query Store taraf\u0131ndan depolanan t\u00fcm verileri silebiliriz.<\/p>\n
Evet… \u015eimdi s\u0131ra Query Store \u00f6zelli\u011fini T-SQL ile aktifle\u015ftirmeye ve kullanmaya geldi.<\/p>\n
\u00d6ncelikle veritaban\u0131m\u0131zda Query Store \u00f6zelli\u011fini aktifle\u015ftirelim.<\/p>\n
\r\nALTER DATABASE Northwind SET QUERY_STORE = ON\r\n<\/pre>\n
Bu \u015fekilde Query Store aktifle\u015ftirilmekte lakin t\u00fcm \u00f6zelliklere default de\u011feri atanmaktad\u0131r. E\u011fer ki parametrelere default de\u011fer d\u0131\u015f\u0131nda veri girmek istiyorsan\u0131z a\u015fa\u011f\u0131daki gibi bir \u00e7al\u0131\u015fma ger\u00e7ekle\u015ftirmelisiniz.<\/p>\n
\r\nALTER DATABASE Northwind SET QUERY_STORE = ON\r\n(\r\n\tOPERATION_MODE = Read_Write,\r\n\tCLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 365),\r\n\tDATA_FLUSH_INTERVAL_SECONDS = 1000,\r\n\tINTERVAL_LENGTH_MINUTES = 30,\r\n\tMAX_STORAGE_SIZE_MB = 250,\r\n\tQUERY_CAPTURE_MODE = AUTO,\r\n\tSIZE_BASED_CLEANUP_MODE = AUTO\r\n)\r\n<\/pre>\n
E\u011fer ki veritaban\u0131n\u0131zda Query Store \u00f6zelli\u011fi \u00f6nceden aktifle\u015ftirilmi\u015fse a\u015fa\u011f\u0131daki gibide \u00e7al\u0131\u015fabilirsiniz.<\/p>\n
\r\nALTER DATABASE Northwind SET QUERY_STORE\r\n(\r\n\tOPERATION_MODE = Read_Write,\r\n\tCLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 365),\r\n\tDATA_FLUSH_INTERVAL_SECONDS = 1000,\r\n\tINTERVAL_LENGTH_MINUTES = 30,\r\n\tMAX_STORAGE_SIZE_MB = 250,\r\n\tQUERY_CAPTURE_MODE = AUTO,\r\n\tSIZE_BASED_CLEANUP_MODE = AUTO\r\n)\r\n<\/pre>\n
Query Store \u00f6zelli\u011fini aktifle\u015ftirdikten sonra veritaban\u0131m\u0131za g\u00f6z atarsak e\u011fer a\u015fa\u011f\u0131daki gibi “Query Store” ad\u0131nda bir sekmenin a\u00e7\u0131ld\u0131\u011f\u0131n\u0131 g\u00f6rece\u011fiz.
\n $\"SQL$ <\/a><\/p>\n
\u015eimdi haz\u0131r Query Store \u00f6zelli\u011fi aktifken a\u015fa\u011f\u0131daki sorguyu \u00e7al\u0131\u015ft\u0131ral\u0131m.<\/p>\n
\r\nSelect * from [Satis Detaylari] where SatisID > 10900\r\n<\/pre>\n
Sorguyu \u00e7al\u0131\u015ft\u0131rd\u0131ktan sonra a\u015fa\u011f\u0131da g\u00f6r\u00fcld\u00fc\u011f\u00fc \u00fczere Query Store sekmesindeki “Top Resource Consuming Queries<\/em><\/strong>” b\u00f6l\u00fcm\u00fcn\u00fc a\u00e7\u0131n\u0131z.
\n $\"SQL$ <\/a><\/p>\n
A\u00e7t\u0131ktan sonra a\u015fa\u011f\u0131daki gibi bir ekranla kar\u015f\u0131la\u015facaks\u0131n\u0131z.
\n $\"SQL$ <\/a><\/p>\n
2.<\/span><\/strong><\/em> b\u00f6l\u00fcmde yazd\u0131\u011f\u0131m\u0131z sorgumuza ait istatistikleri g\u00f6rmekteyiz. Buradaki istatistiklerin grafiksel ayar\u0131n\u0131 1.<\/span><\/strong><\/em> b\u00f6l\u00fcmdeki Metric ve Statistics k\u0131sm\u0131ndan yapabiliriz. 4.<\/span><\/strong><\/em> b\u00f6l\u00fcmde ise Query Store \u00f6zelli\u011fi aktifken Execution Plan’lar\u0131n istatistiklerini g\u00f6rebilmekteyiz. 3.<\/span><\/strong><\/em> b\u00f6l\u00fcmdeki butonlar vas\u0131tas\u0131yla Execution Plan’lar\u0131 listeleyebilir, kar\u015f\u0131la\u015ft\u0131rabilir yahut herhangi bir plan\u0131 Force edebiliriz. 5.<\/span><\/strong><\/em> b\u00f6l\u00fcmde ise \u00f6nceki makalelerimizde inceledi\u011fimiz o anki Execution Plan’\u0131n detay\u0131n\u0131 g\u00f6rmekteyiz.<\/p>\n
Ayr\u0131ca dikkat\u00a0ederseniz 2.<\/span><\/strong><\/em> b\u00f6l\u00fcmde “query id” alan\u0131 sorgunun s\u0131ras\u0131n\u0131, “exec count” alan\u0131 sorgunun ka\u00e7 kere \u00e7al\u0131\u015ft\u0131r\u0131ld\u0131\u011f\u0131n\u0131 ve “Total Duration” gelen sat\u0131r say\u0131s\u0131n\u0131 vermektedir.<\/p>\n
G\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi “Query Store” \u00f6zelli\u011finin nas\u0131l konfig\u00fcre edildi\u011fini ve \u00e7al\u0131\u015ft\u0131r\u0131ld\u0131\u011f\u0131n\u0131 incelemi\u015f olduk. Bunun d\u0131\u015f\u0131nda son olarak “Query Store” \u00f6zelli\u011finin t\u00fcm verilerini silmek istiyorsak a\u015fa\u011f\u0131daki sorgu i\u015fimizi g\u00f6recektir.<\/p>\n
\r\nALTER DATABASE Northwind SET QUERY_STORE CLEAR ALL\r\n<\/pre>\n
Tabi ki de yukar\u0131larda de\u011findi\u011fim gibi bu i\u015flem i\u00e7in “Purge Query Data” butonunuda kullanabiliriz.<\/p>\n
Bu yaz\u0131m\u0131z\u0131nda sonuna gelmi\u015f bulunmaktay\u0131z. Umar\u0131m bol bol faydalan\u0131rs\u0131n\u0131z.
\nSonraki yaz\u0131lar\u0131mda g\u00f6r\u00fc\u015fmek \u00fczere…
\n\u0130yi \u00e7al\u0131\u015fmalar…<\/p>\n","protected":false},"excerpt":{"rendered":"
Merhaba, SQL Server, \u00e7al\u0131\u015ft\u0131r\u0131lan sorgular\u0131n \u00e7al\u0131\u015fma esnas\u0131nda olu\u015fturulan Execution Plan’lar\u0131n\u0131 Memory’de Plan Cache diye nitelendirdi\u011fimiz bir alanda kaydetmektedir. Bu Plan Cache \u00fczerinde bulunan Execution Plan’lar\u0131n baz\u0131 durumlarda silinme ihtimaline kar\u015f\u0131 SQL Server 2016 ile...<\/p>\n","protected":false},"author":1,"featured_media":3415,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1412,22],"tags":[1498,1497,1511,1505,1454,1500,1456,1513,1514,1501,1506,1503,1499,1496,1495,1494,1509,1508,1504,245,1413,1502,1510,1507,461,1512,85],"class_list":["post-5126","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server-2016","category-veritabani","tag-binding","tag-binding-islemi","tag-current-disk-usage","tag-data-flush-interval","tag-execution","tag-execution-islemi","tag-execution-plan","tag-force","tag-force-etmek","tag-management-studio","tag-monitoring","tag-oparation-mode","tag-parse","tag-parse-islemi","tag-plan-cache","tag-query-store","tag-query-store-capture-mode","tag-query-store-retention","tag-requested","tag-sql-server","tag-sql-server-2016","tag-sql-server-management-studio","tag-stale-query-threshold","tag-statistics-collection-interval","tag-t-sql","tag-top-resource-consuming-queries","tag-transact-sql"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/5126","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=5126"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/5126\/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=5126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=5126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=5126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}