April 30, 2015 at 1:26 pm
I am trying to come up with a much measured/scientific way to calculate how much more RAM would i need on my server so that my queries would never go to the disk (please don't judge me on this, i know i could go out tune queries to get some juice out of it, i know writes would go to the disk). I would like to know how many distinct physical reads occur on a volume/file? My thinking is if i can find the number of distinct reads (also number of bytes per read), i would add that much amount of RAM so that most of my frequently "accessed" data is in memory. Any thoughts?
April 30, 2015 at 4:23 pm
This is actually something I do on a regular basis in evaluating environments, and the following is the method I've settled on.
I first check PLE during during business hours, and see the lowest it gets.
Then, I create a table with the columns database_id, file_id, and page_id, and set up a job to run at an interval about equal to the lowest PLE value I saw.
When the job runs it inserts into our newly created table the database_id, file_id, and page_id of any page in sys.dm_os_buffer_descriptors that does not already have an entry in our table.
After that runs for a business day, what I have is a table that contains 1 row for each unique page that was read into memory in that day. Just divide that count by 128 and you'll have the MB of memory you would need to fit all of what was read into memory in that time period.
I set the job to run at an interval about equal to the lowest I see PLE get so that I minimize the chances that a significant number of pages pass through the buffer pool without being accounted for.
I generally don't let it run overnight, since maintenance tasks like CHECKDB and reindexing skew the numbers and make them pretty much useless (the answer to the question of how much memory you "need" then becomes the answer to the question "How much data do you have"? :rolleyes:).
I'll run that during the business day for a few days, and I end up with a good idea of how much memory the instance could use. It's occasionally been VERY useful, as sometimes PLE will be very low, cache hit will be very low, but it turns out the active data set is not much bigger than what the current memory can handle. It's just being churned through at a high rate.
That can (and has) saved some money when it turns out we don't need to make a serious investment in gobs of RAM (or worse, beefier storage), and can just add a small bit of memory.
I hope that all made sense. If it's clear as mud, just berate me and I'll try to make it more clear 🙂
Cheers!
EDIT: It's probably obvious, but I should point out that since this is only looking at the buffer pool, when determining how much memory to add, I compare the amount indicated by my tracking table to the amount of data normally in the buffer pool, not to max server memory.
So, if on a server with 70 GB max server memory, I ran this process and it indicated the active data was about 80 GB, I wouldn't conclude that I should add 10 GB. I would compare 80 GB to what was normally in the buffer pool, which could be much less than the 70 GB max server memory, if other components are using sizable amounts of memory.
If it turned out the buffer pool normally had about 60 GB, then I would add 20 GB or so.
April 30, 2015 at 10:49 pm
Jacob Wilkins (4/30/2015)
This is actually something I do on a regular basis in evaluating environments, and the following is the method I've settled on.I first check PLE during during business hours, and see the lowest it gets.
Then, I create a table with the columns database_id, file_id, and page_id, and set up a job to run at an interval about equal to the lowest PLE value I saw.
When the job runs it inserts into our newly created table the database_id, file_id, and page_id of any page in sys.dm_os_buffer_descriptors that does not already have an entry in our table.
After that runs for a business day, what I have is a table that contains 1 row for each unique page that was read into memory in that day. Just divide that count by 128 and you'll have the MB of memory you would need to fit all of what was read into memory in that time period.
I set the job to run at an interval about equal to the lowest I see PLE get so that I minimize the chances that a significant number of pages pass through the buffer pool without being accounted for.
I generally don't let it run overnight, since maintenance tasks like CHECKDB and reindexing skew the numbers and make them pretty much useless (the answer to the question of how much memory you "need" then becomes the answer to the question "How much data do you have"? :rolleyes:).
I'll run that during the business day for a few days, and I end up with a good idea of how much memory the instance could use. It's occasionally been VERY useful, as sometimes PLE will be very low, cache hit will be very low, but it turns out the active data set is not much bigger than what the current memory can handle. It's just being churned through at a high rate.
That can (and has) saved some money when it turns out we don't need to make a serious investment in gobs of RAM (or worse, beefier storage), and can just add a small bit of memory.
I hope that all made sense. If it's clear as mud, just berate me and I'll try to make it more clear 🙂
Cheers!
EDIT: It's probably obvious, but I should point out that since this is only looking at the buffer pool, when determining how much memory to add, I compare the amount indicated by my tracking table to the amount of data normally in the buffer pool, not to max server memory.
So, if on a server with 70 GB max server memory, I ran this process and it indicated the active data was about 80 GB, I wouldn't conclude that I should add 10 GB. I would compare 80 GB to what was normally in the buffer pool, which could be much less than the 70 GB max server memory, if other components are using sizable amounts of memory.
If it turned out the buffer pool normally had about 60 GB, then I would add 20 GB or so.
First of all thank you for taking time in giving such detail steps : ). As you described i could monitor PLE ( every 5 secs) and get dump of the DMV ( every 1 min), there will a tipping point when my PLE will drop so i will see reduction in number of pages also from that DMV. What i really want to know if how much more memory can i add to avoid that drop or possibly push that drop further down, i am thinking if i could do (Avg.Disk Bytes/Read X Read operations( Disks Reads / Sec ) * duration ( amount of time took to get to "normal") , my problem is i don't know if (Avg.Disk Bytes/Read) will give me distinct number of reads? May be i am over engineering this 😉
April 30, 2015 at 11:05 pm
Ah, my apologies, I fear my explanation was not clear enough.
How much memory to add is exactly what you have at the end of the process I described.
I'm not sure exactly where our wires are crossed, but I'll take a stab at it 🙂
A drop in PLE would not necessarily mean a drop in the number of pages in the buffer pool. A drop in PLE just means that pages in the buffer pool are getting displaced by new ones more quickly.
That DMV shows what pages are in the buffer pool, and the process I described just uses that to record the unique pages that are read into the buffer pool. In turn, that allows you to see how big the buffer pool would have to be accommodate all the data that got read into the buffer pool.
If that doesn't help at all, just let me know what you think this is missing, and I'll try to come up with a clearer way to communicate it.
Cheers!
May 1, 2015 at 12:15 pm
Jacob Wilkins (4/30/2015)
Ah, my apologies, I fear my explanation was not clear enough.How much memory to add is exactly what you have at the end of the process I described.
I'm not sure exactly where our wires are crossed, but I'll take a stab at it 🙂
A drop in PLE would not necessarily mean a drop in the number of pages in the buffer pool. A drop in PLE just means that pages in the buffer pool are getting displaced by new ones more quickly.
That DMV shows what pages are in the buffer pool, and the process I described just uses that to record the unique pages that are read into the buffer pool. In turn, that allows you to see how big the buffer pool would have to be accommodate all the data that got read into the buffer pool.
If that doesn't help at all, just let me know what you think this is missing, and I'll try to come up with a clearer way to communicate it.
Cheers!
Can you share the script for the job?
May 1, 2015 at 2:58 pm
I should have provided more details. I have about 5 databases and i know that one of my biggest database is using 80% of allocated memory, this database is also spread across multiple volumes, i don't know how you can derive how much more memory you need from PLE, i can see that had there been one database on the server, i would have just looked at physical bytes/transfer on that drive and that would tell me how much more data a process needed that it had to go to the disk.
May 1, 2015 at 3:03 pm
Well, you can't calculate exactly how much you need from PLE, which is why that's not how my process works 🙂
You can't really derive much from any of those counters, since you can't tell if the pages being read from disk are pages that were in cache earlier or not, which can make a big difference.
I just happened to be on a coffee break when I saw this come in, so I don't have time to provide my scripts or any examples at the moment.
I'll post more detail with some examples either later tonight or early tomorrow morning. Hopefully the examples will clarify things.
Cheers!
May 1, 2015 at 3:24 pm
Well based on your criteria that you never want your queries going to disk you would need enough memory to hold your entire DB....
A good starting point might be to look at your biggest/most frequently queried tables and start working from there. And it would be a good idea to understand how the data is being used.
May 1, 2015 at 3:47 pm
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2015 at 3:55 pm
ZZartin (5/1/2015)
Well based on your criteria that you never want your queries going to disk you would need enough memory to hold your entire DB....A good starting point might be to look at your biggest/most frequently queried tables and start working from there. And it would be a good idea to understand how the data is being used.
Sure, i am in a weird spot. I have a single 7TB database, my largest table is close to 1 TB and i have only 512 Gb RAM,now my PLE today averages to 10 mins max. I do know for sure that only a subset of data is "accessed" during peak business hours and rest during off hours and weekend, my goal is to get that optimal value for memory and have it added so that during peak hours most of my data is cached.
May 5, 2015 at 11:49 am
Alright, life got busy, so it took me much longer to post the demo than I'd wanted.
At any rate, here's a demo of how the process works. So long as you have at least 4 GB RAM on a test system where you can run this, you'll be able to run it as is.
Hopefully actually seeing the code will make it clear what I was failing to explain earlier 🙂
In one query window, paste and step through this:
--First set max server memory to 2000 MB
sp_configure 'max server memory', 2000
RECONFIGURE
--Now create the table we will use to overrun
--the buffer pool. I'm using nchar(3000) since
--that forces the table to use one page for each
--row, which makes sizing the table easier.
CREATE TABLE BufferTest
(SomeData nchar(3000));
--Now I populate this with 2048 MB of data, which
--will overrun the buffer pool since SQL Server
--won't even be able to use all of the 2000 MB max server
--memory for the buffer pool.
WITH CTE (SomeData)
AS
(
SELECT 'a'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a'
)
INSERT INTO BufferTest
SELECT CTE1.SomeData
FROM
CTE AS CTE1
CROSS JOIN
CTE AS CTE2
CROSS JOIN
CTE AS CTE3
CROSS JOIN
CTE AS CTE4
CROSS JOIN
CTE AS CTE5
CROSS JOIN
CTE AS CTE6
--Confirm that the row count matches the number
--of data pages for the table, and that this
--is 2048 MB.
SELECT p.rows,
au.data_pages,
au.data_pages/128 AS DataMB
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id=i.object_id
INNER JOIN sys.partitions AS p ON i.object_id=p.object_id
INNER JOIN sys.allocation_units AS au ON au.container_id=p.partition_id
WHERE t.name='BufferTest'
--Now let's make sure that the buffer pool
--is indeed holding less than 2000 MB. On
--my test system, it's holding 1709 MB.
--That's on 2012, where max server memory
--includes more. On pre-2012 instances
--it'll be closer to 2000, but should still
--be less.
SELECT COUNT(*)/128 AS BufferPoolMB
FROM master.sys.dm_os_buffer_descriptors
--Now we'll start a process to continually
--scan the table. Since the table is bigger
--than what the buffer pool can hold, this
--will generate quite a bit of IO.
DECLARE @SomeData nchar(3000)
WHILE 1=1
BEGIN
SELECT @SomeData=SomeData
FROM BufferTest
END
--Here open perfmon and watch read bytes/sec
--for the drive with this DB's data file.
--On my test system this generates about
--100 MB/sec of reads after it has churned
--through the buffer pool.
--Now we can switch to our other query window,
--leaving the above process running.
Now in another query window, paste and step through this:
--This creates a table that will store the identifying information
--for each page that we record as having been in the buffer pool.
--A side note about this:
--I don't create any indexes on this table, as the most efficient
--query plan for the query that uses it just ends up being a hash
--join using all the columns as a key. A merge join would be more efficient
--actually, if both inputs were already sorted, but since the other
--"table" is sys.dm_os_buffer_descriptors, we can never achieve that.
CREATE TABLE [dbo].[BufferTracking](
[database_id] [int] NULL,
[FILE_ID] [int] NULL,
[page_id] [int] NULL
) ON [PRIMARY]
GO
--This checks the buffer pool for any pages we have not yet
--recorded as having been in the buffer pool. Ordinarily
--I would run this as a scheduled job that runs during
--business hours and check the results after a day or two,
--but here I'm just going to run it in a loop using a
--WAITFOR DELAY until it doesn't find any new pages
--in the buffer pool. On my test system, that takes about 90 seconds.
DECLARE @Rows int=1
WHILE @Rows>0
BEGIN
INSERT INTO BufferTracking (database_id,file_id,page_id)
SELECT
BUF.database_id,
BUF.file_id,
BUF.page_id
FROM master.sys.dm_os_buffer_descriptors BUF
WHERE NOT EXISTS (
SELECT 1
FROM BufferTracking BT
WHERE
BUF.database_id=BT.database_id AND
BUF.file_id=BT.file_id AND
BUF.page_id=BT.page_id)
OPTION (MAXDOP 1)
SET @Rows=@@ROWCOUNT
WAITFOR DELAY '00:00:05'
END
--Now let's see how many pages we saw pass through
--through the buffer pool, compare that to how much
--the buffer pool can currently hold, convert that
--to MB, and add that much to max server memory.
--On my system, that comes out to 399 MB.
DECLARE @CurrentMB int
DECLARE @NeededMB int
SELECT @CurrentMB=COUNT(*)/128
FROM master.sys.dm_os_buffer_descriptors
SELECT @NeededMB=COUNT(*)/128
FROM BufferTracking
SELECT @NeededMB-@CurrentMB
--I'll reconfigure max server memory to be its current
--value plus the amount indicated by that query.
--In this case, that would be 2000+399=2399 MB.
--I'll round to 2400 MB.
sp_configure 'max server memory', 2400
RECONFIGURE
--Within a few seconds, the physical IO will drop
--to nothing. Success!
--On my 2008 R2 test instance, the result was even
--more dramatic. The test still generated about 100
--MB/sec in IO, but only 73 MB was indicated by the
--process, and after adding that 73 MB, physical IO
--dropped from 100 MB/sec to 0.
--Now we can stop the read-generating process in the
--other window and then clean up our tables.
DROP TABLE BufferTest
DROP TABLE BufferTracking
Now, this case was obviously a very simple one, and since only one table was involved we could have guessed a good value just based on that table's size.
The nice thing is that this process is equally as accurate in more complicated real-world situations where many tables are frequently accessed, and only pieces of those tables, etc.
One caveat is that the time taken by the insert increases significantly with the size of the buffer pool, and 512 GB is the largest system I've used this on, so you're right on the edge of my experience. On that system, each run of the INSERT to populate the tracking table took about 5 minutes.
Relatedly, while this process is extremely accurate, as with so many things, we don't get that for free. The time used by the process is almost exclusively CPU time, so ideally there would be enough CPU headroom on a system to run this with minimal impact.
Because of that, my use of this process is usually limited to cases where one or more of the following are true:
1) The client cannot justify adding memory without ironclad justification (e.g., the environment's virtual, and adding significant memory to the instance would require adding memory physically to the hosts, and that maintenance window is hard to come by)
2) The amount of memory on the server is low enough where the process has almost zero impact (for most servers that tipping point is somewhere around 32 GB; after that it starts taking some time to run)
3) The server has ample CPU headroom to accommodate the process
For the one case where I used it on an instance with 512 GB, the cost of having the process run was offset by the cost of the alternative, which was going to be either maintenance to add memory to the underlying hosts so significant memory could be added to the instance, or costly upgrades to the storage layer (and yes, it had a happy ending; as in the demo, it turned out a fairly small bump in max server memory was sufficient to reduce IO significantly).
I know this was a long post, but hopefully this clears up the confusion my previous explanations couldn't.
Cheers!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply