SQL Server Memory Troubleshooting – Buffer Pool Usage (by Amit Bansal)

hey welcome to another video from sequel mistress and today I'm going to talk about buffer pool usage this is a part of a couple of videos that I'm going to do in sequel server memory troubleshooting topic now again this video is inspired by some questions that commonly keeps coming up in forums where DBAs and developers they're complaining about sequel server memory consumption and a comment remark always is that sequel server is hogging up memory well the first thing we need to understand is sequel server loves memory the more you give it the more it will take it and why not because it's always great that sequel server can have enough memory so that it can cache all those data pages and index pages in memory and can serve them faster to the client but when we see that servers with let's say 64 GB RAM 128 gig of ram and even more with such a high amount of physical RAM them the more you add the more they get consumed what we need to see is which component inside sequel server is consuming maximum amount of memory and by far you will see that buffer pool probably always consumes the maximum amount of memory this is one of the memory consumers and more technically one of the memory clerks that will always have a very high usage because this is the consumer that will reserve memory for your data and index pages and all those databases and objects that you have which are like you know mb/s and gigabytes in size buffer pool is the cloak that will give memory to cache those pages inside the memory so let's try to understand this concept of buffer pool usage and how you can actually break the break that maybe first is figuring out the overall server memory and then trying to find out which component is consuming maximum amount of memory and then trying to break down the buffer pool memory into databases and object which is trying to find out which database is consuming maximum amount of physical memory and which object inside that database is consuming maximum amount of memory so Before we jump into the demo here is a quick URL you can subscribe to this bitly slash connect with a B this is an exclusive distribution list that I only share with people who are learning from my videos or for or from my blogs or people who have attended my sessions in conferences or even pre con sessions and master classes so it's not publicly distributed it is only available to some of these sources so you can stay connected with me using this URL I will write to you once in two weeks yeah which is not too much and we'll share a lot of learning resources with you if you like this video and the demos please share it with your friends and colleagues and also subscribe to this channel all these links are there in the YouTube description we have also started a telegram channel called sequel mistress champs and if you want to participate in sequel server discussions you can join this channel the bitly link is up there quickly slash sequel mistress champs well this link is also there in the YouTube description if you are a Twitter fan you can follow me on twitter a underscore bunsen finally I am working towards my performance streaming video course which is an exclusive masterpiece that I am going to produce it is being released soon you can learn more about this video course on this URL bitly /a b video course this will be a very exhaustive video course with more than 100 hours of deep dive content on sequel server internals troubleshooting and performance tuning there are more than 21 modules that I am covering in this video course and you may want to learn more about this course using that link so all the links that I have been talking about are there in this youtube video description now let's jump to demo and understand the buffer pool concept and the memory consumption inside sequel server let's get started now Before we jump into buffer pool usage and I show you a lot of different DMVs including the buffer descriptors first let's go and see the memory usage by sequel server instance so I will jump into performance monitor and I rely a lot on this counter which is total server memory which gives kind of very accurate consumption of sequel server memory so you can see we are consuming close to two point three GB of memory right now and this counter total server memory belongs is actually inside this object memory manager so all that you have to do is when you click on this Add button you traverse the server name and this is a named instance sequel Miss Rose and under that you have object memory manager and the counter total server memory so the current comes in consumption is about 2.3 GB and as I said now we just want to really understand what is this bifurcation of 2.3 GB and how much is being used by the buffer pool so let's go and now look into this 2.3 GB with one more query which is looking into the DMV so DM there is a DMV DMOS process memory and if I try to do some mathematics here even the DMV gives me close to the same amount of memory that I see there is of course a slight difference but I am NOT going to talk about that right now but yeah more or less this is the same number that we see in the perfmon which means right now the database engine is consuming about 2.2 2.3 GB of memory and now the clerks so as I said if we look into since DMOS memory clerks we want to see the bifurcation of this 2.3 gb so here is this clerk which will allow you to see how much memory is being consumed by each component inside sequel server and sequel for pool is also a clerk so if I execute this you can see currently memory clerk xtp well this is for in-memory OLTP XT P stands for extreme processing and that was the internal code name and it is consuming about 879 MB then you have the cache store which is for the cache plans which is 2 for 2 for 3 MB and then for the boundaries you have some amount of memory there and now comes buffer pool so you can see buffer pool is sitting pretty nicely at about 140 MB or so and is really not consuming much I mean this is this is probably one of the lowest values you will ever see I have just started this VM and that's why the consumption is so low now we have seen the amount of memory being used by the server overall and the different components out there but yes the purpose of the demo is to kind of show you the usage by the buffer pool and then breaking that memory up into databases and objects and this DMV does the job which is demo s buffer descriptors so let me first execute this and show what is the kind of output so this DMV has one record for each page each 8 KB page that is there in sequel server memory and currently you can see about if i zoom in about 15,000 pages are there in sequel server memory right now and the end this is the description as the name says buffer descriptors this is the description of each page that is there in the memory so you have the you it gives you this page what is the page ID and to which file it belongs to to which database it belongs to and from allocation unit ID you can find out which object it belongs to what kind of page it is is it the data page is it an index page so on and so forth it also tells you whether the page is a dirty page or is it a clean page and of course this information is a lot of data but then this granular information is of little use you of course too want to see more summarized information and now let's go and look into the buffer pool usage so if I execute this which is simple mathematics on the buffer descriptors DMV you can see that ignoring resource D beat the hidden database the user database bases are hardly consuming anything at all so you can see adventureworks databases are just consuming about 5 MB so and of course very low values as I said we have I've just started this VM and not only consumption by the database you can also see the buffer pool consumption by each object in the database so if I execute this portion because there is an allocation unit ID and using that we can figure out how much memory is being consumed by each object in a specific database so let's say I choose 2014 and I can see the consumption of each object there now let's go back and look into the first query again where you see the consumption by each database and let's say adventureworks 2014 is sitting at 5 MB let me fire up a new query and let me simply say select star from sale oops I select start from sales dot sales order header let's take this as one of the tables and let's take one more sales order detail so these two tables have a decent amount of data and let's execute them the moment I execute these two queries now what has happened is all the pages that belonged to these two tables are being fetched from disk into memory and now I will probably see that the consumption of adventureworks 2014 will go right on the top most likely so let's go and execute this again and as you see adventureworks 2014 comes to 21 MB so earlier as you see it was 5 MB and now it is 21 and B so this is this is how memory is getting the zooom and as I mentioned before buffer pool as as a clerk as as a memory consumer will be one of the top consumers of memory inside sequel server because sequel server loves to keep everything in cash so that it can serve it faster to the client so that's what it is now you see an adventuress 2014 sitting at 21 mb and if you if we go and look into the distribution by objects in adventureworks 2014 and you will probably see sales order detail in sales order header right on top because these were the two select statements and I that we fired and I got all the data from disk into memory so the whole idea of understanding the buffer pool usage is that we need to find out which databases and which objects in those specific databases are consuming maximum memory so really the question about sequel server Hawkin up all the memory well if you give all the memory to sequel server sequel server will love to take it and will love to utilize it so that's not really a problem what is important is we find out where is that memory being consumed so a quick summary clerks play a very important role as I mentioned before this will help you find out memory consumption by each component inside sequel server and of course in most cases you will see buffer pool is the largest memory consumer and then using some of these queries on buffer descriptors you can figure out which databases which objects are consuming more memory you can also get a count as you see there was a column there is modified and you can also get a count of how many clean pages and how many dirty pages are there in the buffer pool so right now you can see there are about 2600 clean pages and about 11 dirty pages so all these clean pages have actually come from those tables that we just executed and if I say dbcc dropped clean buffers this is a command that you should definitely avoid firing on production server so I'm just using for the demo here I when I execute this you know what it means I'm cleaning all the I'm cleaning the buffer so I'm actually getting rid of all the clean pages now if I execute this you will see that there's absolutely no clean page now so there they're all removed from the buffer and yes I have eleven dirty pages so if I fire a manual checkpoint and this is just to show you as a demo of course dirty pages have to be written back on their disk that's it this is what a manual checkpoint will do and let's go and check now how many clean and dirty pages we have and now you can see dirty there are no more dirty pages but because I just executed something and there are all of these background threads already running I have some clean pages but now you can see even dirty pages are gone so the last portion of dirty page clean page please don't try to play around with all of this in your production server but the other queries that I showed you about the break up of buffer pool by the database and by each object these queries are pretty safe so hope you have got this concept of monitoring the buffer pool usage buffer pool consumption in sequel server hope the demo was useful and you have learned something new here are a few bullet points about me in short I have been working with sequel server since 7.0 it has been more than 20 years and I still love working with the praana there is a URL their sequel maestro's com / - pun cell click on that and you can read and learn more about who I am and what my work is here is a quick snapshot of my work and some credentials I am a Microsoft Certified Master of sequel server also Microsoft MVP for sequel server and Microsoft has also honored me with Regional Director status I do spend quite a bit of time with sequel maestro's in fact this is my day job and we have a very popular performance tuning video course a wonderful product hands-on lab learning kits and of course a popular sequel server he'll check service just visit sequel maestro's com to learn more about these offerings apart from my day job I also spend time with community I spend my time with sequel server geeks and data platform geeks we do lot of webinars and in-person events and one of our flagship initiative is data platform summit this is an annual conference which happens in Bangalore in the month of August and you can visit the PSTN dot-com to learn more about this a quick word about performance tuning video course so what I did was we had a popular master class on sequel server internals troubleshooting and performance tuning which I converted into a master series with more than hundred hours of deep type content expert level modules both in terms of breadth and the depth and it is constantly updated you can subscribe to it and watch anytime anywhere as many times and the labs you can access them via sequin wastrels hands-on lab that bitly link between /a B video course is where you can go and learn more about the video course or just go to sequel my screws comm and you can explore the video course all the links are available in the YouTube video description a quick word about the annual conference as well if you will see this about Microsoft Data Platform as your artificial intelligence big data are cloud and of course including sequel server do not miss this conference this is one of its kind world's best Microsoft product ingenious MVPs and MCM s come from more than 20 countries and they assemble in Bangalore in the month of August and deliver more than hundred sessions across three days and we also have full-day classes called data platform summit pre cons so this is a place where you should be visit dps 10.com to learn more about dps here is a quicker organizational structure of dominar sequel Mistral's is something that i spoke about this is on the community front and there are other units expand sm erp which is an award-winning ERP solution from a dominar and our corporate training unit people were india call to action you can subscribe to this YouTube channel of course you should do that you can follow sequel maestro's on facebook twitter linkedin it's just sequel maestro's and you can follow me on twitter i underscore bunsen and you can follow me on Facebook and LinkedIn as well the URLs are up there on the slide with this thank you very much hope this video was worth your time see you soon in another video

Channel: SQLMaestros

Views: 14,275

Rating: 4.9259257 out of 5

Keywords: sql server, microsoft sql server, SQL Server Memory, SQL Server Internals, Monitoring, Performance Tuning, Performance Monitor, SQL Server Administration

Id: 07lZ1yKc1F0

Length: 18min 3sec (1083 seconds)

Published: Mon May 27 2019