{"id":460,"date":"2019-12-17T12:41:35","date_gmt":"2019-12-17T12:41:35","guid":{"rendered":"https:\/\/datarebus.wordpress.com\/?p=46"},"modified":"2021-04-09T08:14:29","modified_gmt":"2021-04-09T08:14:29","slug":"sql-server-memory-management-2","status":"publish","type":"post","link":"https:\/\/datarebus.com\/index.php\/2019\/12\/17\/sql-server-memory-management-2\/","title":{"rendered":"SQL Server Memory Management"},"content":{"rendered":"\n

T\u00fcm veritaban\u0131 sistemlerinde oldu\u011fu gibi SQL Server\u2019da da memory 2 farkl\u0131 ama\u00e7  i\u00e7in kullan\u0131l\u0131r:<\/p>\n\n\n\n

  1. Kendi i\u00e7 sistemleri (plan cache, baz\u0131 denetim DLL dosyalar\u0131 ve \u00e7al\u0131\u015fan kodlar, procedure cache vs) i\u00e7in ayr\u0131lan di\u011fer k\u0131sma g\u00f6re k\u00fc\u00e7\u00fck kalacak bir alan<\/li>
  2. Diskten okuma yazma i\u015flemlerini azaltmak ama\u00e7l\u0131 kullan\u0131lan ve SQL i\u00e7in ayr\u0131lan memory\u2019nin b\u00fcy\u00fck k\u0131sm\u0131n\u0131 olu\u015fturan \u201cBuffer Cache\u201d<\/li><\/ol>\n\n\n\n

    Teknolojiye paralel olarak disk eri\u015fim h\u0131zlar\u0131 ne kadar artm\u0131\u015f olursa olsun disk eri\u015fimleri memory eri\u015fimlerine k\u0131yasla her zaman \u00e7ok ama \u00e7ok yava\u015f kalacakt\u0131r. Bunun nedeni de tabiki disk eri\u015fimlerinin mekanik aksamlardan sa\u011flan\u0131yor olmas\u0131d\u0131r. Yani bir disk kafas\u0131 fiziksel olarak ilgili alana y\u00f6nelmeli ve buradan okumay\u0131 ger\u00e7ekle\u015ftirmesi gerekir. Aksine memory okumalar\u0131 ise tamamen elektronik i\u00e7eriklidir. K\u0131saca bu pragraf\u0131n \u00f6zeti: diskler yava\u015f memory h\u0131zl\u0131d\u0131r.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Bu nedenle de veritaban\u0131 sistemleri okuyabildikleri kadar veriyi \u00f6nceden Cache \u00fczerine y\u00fcklerler. B\u00f6ylece SQL Server\u2019\u0131n ilk yapaca\u011f\u0131 i\u015flem talep edilen verinin Cache\u2019de olup olmad\u0131\u011f\u0131n\u0131 kontrol edilmesidir. E\u011fer var ise (cache Hit) disk eri\u015fimine gerek kalmaz.<\/p>\n\n\n\n

    B\u00fcy\u00fck veriye sahip olan sistemlerde Cache, veritaban\u0131na k\u0131yasla ufak kalaca\u011f\u0131 i\u00e7in cache \u00fczerindeki veri ya\u015fland\u0131r\u0131l\u0131r ve belirli bir s\u00fcre burada kal\u0131p zaman\u0131n\u0131 dolduran k\u0131s\u0131mlar yeni okumalara yer a\u00e7mak ama\u00e7l\u0131 olarak diske yaz\u0131l\u0131r.<\/p>\n\n\n\n

    \u015eimdi biraz scriptler ile SQL Server\u2019\u0131n kulland\u0131\u011f\u0131 memory konusunda bilgi alal\u0131m :<\/p>\n\n\n

    <\/p>\n\n\n

    SELECT<\/p>

                physical_memory_kb,<\/p>

                virtual_memory_kb,<\/p>

                committed_kb,<\/p>

                committed_target_kb<\/p>

    FROM sys.dm_os_sys_info;<\/p><\/blockquote>\n\n\n

    <\/p>\n\n\n

    Buradaki sonu\u00e7larn manas\u0131n\u0131 k\u0131saca a\u00e7\u0131klamak gerekir ise:<\/p>\n\n\n\n

    • Physical_memory_kb<\/strong>: sunucu \u00fczerindeki RAM miktar\u0131<\/li>
    • Virtual_memory_kb<\/strong>: sunucu \u00fczerindeki tan\u0131ml\u0131 sanal memory miktar\u0131<\/li>
    • Committed_kb<\/strong>: veritaban\u0131n\u0131n Buffer Cache i\u00e7in kulland\u0131\u011f\u0131 miktar<\/li>
    • Committed_target_memory_kb<\/strong>: Buffer Cache\u2019in kullanmak istedi\u011fi alan\u0131 g\u00f6sterir. Bu de\u011fer e\u011fer committed k\u0131sm\u0131ndan ufak ise verinin diske yaz\u0131lmas\u0131n\u0131n zaman\u0131 gelmi\u015f demektir. E\u011fer b\u00fcy\u00fck ise daha fazla memory kullan\u0131lmak \u00fczere talep edilecek manas\u0131na gelir.<\/li><\/ul>\n\n\n\n

      Burada veritaban\u0131n\u0131z\u0131 performans olarak en iyi etkiyi sa\u011flayacak olan parameter, Buffer cache i\u00e7in ayr\u0131lan alan\u0131n yeterli olup olmad\u0131\u011f\u0131d\u0131r. Ek olarak a\u015fa\u011f\u0131daki sorgu ile de buffer cache i\u00e7in ne kadar alan ayr\u0131ld\u0131\u011f\u0131n\u0131 tam olarak tespit edebilirsiniz :<\/p>\n\n\n\n

      \n

      SELECT<\/p>

                  COUNT(*) AS buffer_cache_pages,<\/p>

                  COUNT(*) * 8 \/ 1024 AS buffer_cache_used_MB<\/p>

      FROM sys.dm_os_buffer_descriptors;<\/p><\/blockquote>\n<\/div><\/div>\n\n\n\n

       Peki bu alan\u0131 en \u00e7ok hangi tablo kullan\u0131yor :<\/p>\n\n\n\n

      SELECT<\/p>

                  indexes.name AS index_name,<\/p>

                  objects.name AS object_name,<\/p>

                  objects.type_desc AS object_type_description,<\/p>

                  COUNT(*) AS buffer_cache_pages,<\/p>

                  COUNT(*) * 8 \/ 1024  AS buffer_cache_used_MB<\/p>

      FROM sys.dm_os_buffer_descriptors<\/p>

      INNER JOIN sys.allocation_units<\/p>

      ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id<\/p>

      INNER JOIN sys.partitions<\/p>

      ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))<\/p>

      OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))<\/p>

      INNER JOIN sys.objects<\/p>

      ON partitions.object_id = objects.object_id<\/p>

      INNER JOIN sys.indexes<\/p>

      ON objects.object_id = indexes.object_id<\/p>

      AND partitions.index_id = indexes.index_id<\/p>

      WHERE allocation_units.type IN (1,2,3)<\/p>

      AND objects.is_ms_shipped = 0<\/p>

      AND dm_os_buffer_descriptors.database_id = DB_ID()<\/p>

      GROUP BY indexes.name,<\/p>

                               objects.name,<\/p>

                               objects.type_desc<\/p>

      ORDER BY COUNT(*) DESC;<\/p><\/blockquote>\n\n\n\n

      Peki, veri disk \u00fczerinden cache\u2019e okunuyor ve burada saklan\u0131yor. Belki daha \u00f6nce yazmay\u0131 unuttuk ama malum 8 KB olacak \u015fekilde formatlanm\u0131\u015f bir disk alan\u0131m\u0131z var. ayn\u0131 nedenle cache \u00fczerindeki minimum blok b\u00fcy\u00fckl\u00fc\u011f\u00fc de 8KB. Tabii bunun ne kadar\u0131n\u0131n ger\u00e7ekten dolu oldu\u011fu data b\u00fcy\u00fckl\u00fc\u011f\u00fcn\u00fcze ba\u011fl\u0131. Yani bir sat\u0131r\u0131 5KB b\u00fcy\u00fckl\u00fc\u011f\u00fcnde bir tablo tan\u0131mlam\u0131\u015f iseniz \u00e7ok kaba bir tarif ile her blokta 3KB bo\u015f alan\u0131n\u0131z var demektir.<\/p>\n\n\n\n

      Peki cache \u00fczerindeki kay\u0131tlar\u0131n ne kadar ger\u00e7ekten dolu ve ne kadar\u0131 bo\u015f:<\/p>\n\n\n\n

      \n

      SELECT<\/p>

                  objects.name AS object_name,<\/p>

                  objects.type_desc AS object_type_description,<\/p>

                  COUNT(*) AS buffer_cache_pages,<\/p>

                  CAST(COUNT(*) * 8 AS DECIMAL) \/ 1024  AS buffer_cache_total_MB,<\/p>

                  CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) \/ 1024 \/ 1024 AS buffer_cache_free_space_in_MB,<\/p>

                  CAST((CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) \/ 1024 \/ 1024) \/ (CAST(COUNT(*) * 8 AS DECIMAL) \/ 1024) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_space<\/p>

      FROM sys.dm_os_buffer_descriptors<\/p>

      INNER JOIN sys.allocation_units<\/p>

      ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id<\/p>

      INNER JOIN sys.partitions<\/p>

      ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))<\/p>

      OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))<\/p>

      INNER JOIN sys.objects<\/p>

      ON partitions.object_id = objects.object_id<\/p>

      WHERE allocation_units.type IN (1,2,3)<\/p>

      AND objects.is_ms_shipped = 0<\/p>

      AND dm_os_buffer_descriptors.database_id = DB_ID()<\/p>

      GROUP BY objects.name,<\/p>

                                          objects.type_desc,<\/p>

                                          objects.object_id<\/p>

      HAVING COUNT(*) > 0<\/p>

      ORDER BY COUNT(*) DESC;<\/p><\/blockquote>\n<\/div><\/div>\n\n\n\n

      Bu ne i\u015fimize yarar derseniz, asl\u0131nda \u00e7ok bir i\u015finize yaramaz ama en az\u0131ndan tablolar\u0131n\u0131zdaki bo\u015f alanlar\u0131, blob alanlar\u0131 ve fragmantasyonu d\u00fc\u015f\u00fcnmeye ba\u015flaman\u0131za yard\u0131mc\u0131 olur.<\/p>\n\n\n\n

      DBCC DROPCLEANBUFFERS<\/p><\/blockquote>\n\n\n\n

      Cache \u00fczerinde bir rahats\u0131zl\u0131k hissettiniz bu komutu \u00e7al\u0131\u015ft\u0131rabilirsiniz. SQL taraf\u0131ndan bakarsan\u0131z \u00e7ok istenen bir durum de\u011fil zira cache \u00fczerindeki t\u00fcm clean ya da dirty alanlar\u0131 diske yazarak cache\u2019I bo\u015faltacakt\u0131r. Dolay\u0131s\u0131 ile de ilk zamanlarda t\u00fcm okumalar disk \u00fczerinden yap\u0131laca\u011f\u0131 i\u00e7in bir s\u00fcre yava\u015flama olaiblir.<\/p>\n\n\n\n

      Page Life Expectancy<\/h2>\n\n\n\n

      SQL Server i\u00e7in tan\u0131mlanan memory alan\u0131 ne kadar yeterli sorusuna en iyi cevab\u0131 verecek olan sorgudur. PLE \u00f6zetle, cache \u00fczerinde bir verinin ne kadar s\u00fcre kald\u0131\u011f\u0131n\u0131 g\u00f6sterir. E\u011fer bu s\u00fcre uzun ise sorun yok. Ama k\u0131sa ise bu datan\u0131n devaml\u0131 olarak cache \u00fczerinden diske aktar\u0131ld\u0131\u011f\u0131n\u0131 g\u00f6sterir. Bu da biraz daha memory ihtiyac\u0131na en iyi ipucudur. Di\u011fer taraftan b\u00fcy\u00fck veri \u00e7eken sorgular\u0131n \u00e7al\u0131\u015ft\u0131r\u0131l\u0131yor olmas\u0131 PLE de\u011ferini d\u00fc\u015f\u00fcr\u00fcr.<\/p>\n\n\n\n

      SQL kitap\u00e7\u0131klar\u0131nda bu de\u011ferin >300 olmas\u0131 talep edilir ama g\u00fcn\u00fcm\u00fczdeki RAM b\u00fcy\u00fckl\u00fcklerine bakar isek 300 bence biraz d\u00fc\u015f\u00fck.<\/p>\n\n\n\n

      SELECT<\/p>

                  *<\/p>

      FROM sys.dm_os_performance_counters<\/p>

      WHERE dm_os_performance_counters.object_name LIKE ‘%Buffer Manager%’<\/p>

      AND dm_os_performance_counters.counter_name = ‘Page life expectancy’;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

      T\u00fcm veritaban\u0131 sistemlerinde oldu\u011fu gibi SQL Server\u2019da da memory 2 farkl\u0131 ama\u00e7  i\u00e7in kullan\u0131l\u0131r: Kendi i\u00e7 sistemleri (plan cache, baz\u0131 denetim DLL dosyalar\u0131 ve \u00e7al\u0131\u015fan kodlar, procedure cache vs) i\u00e7in ayr\u0131lan di\u011fer k\u0131sma g\u00f6re k\u00fc\u00e7\u00fck kalacak bir alan Diskten okuma yazma i\u015flemlerini azaltmak ama\u00e7l\u0131 kullan\u0131lan ve SQL i\u00e7in ayr\u0131lan memory\u2019nin b\u00fcy\u00fck k\u0131sm\u0131n\u0131 olu\u015fturan \u201cBuffer Cache\u201d […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[5,6,7],"_links":{"self":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/460"}],"collection":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/comments?post=460"}],"version-history":[{"count":1,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/460\/revisions"}],"predecessor-version":[{"id":475,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/460\/revisions\/475"}],"wp:attachment":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/media?parent=460"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/categories?post=460"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/tags?post=460"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}