Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simulating memory pressure


Simulating memory pressure

Author
Message
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 3821
Thanks for the clarification Smile

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
Hugo Kornelis (9/1/2008)

If this is the compliment it looks like, then I thank you for it.

Yes, it was a compliment (although a "passive" one ;-). You are welcome.

Best Regards,

Chris Büttner
Adam Gardner
Adam Gardner
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 54
According to Microsoft (link), SQL Server 2008 supports up to 8TB of RAM, and Windows Server 2003 SP1 on X64 currently supports 1TB of RAM.

Two out-of-the-box solutions to your problem, both of which assume your server is x64 with 2TB of RAM installed, and your database is 1.0TB (more specific than you posed in your question), and Windows Server 2003 SP1 is installed on your server...

a. Remove half your memory in your desktop.
b. Wait for a version of Windows Server that supports more than 1TB of RAM and upgrade.

Smile
Vitali Lisau
Vitali Lisau
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 183
What about reducing total memory size available to Windows by changing boot.ini:

"MAXMEM=
Limits Windows to ignore (not use) physical memory beyond the amount indicated. The number is interpreted in megabytes. Example: /MAXMEM=32 would limit the system to using the first 32 MB of physical memory even if more were present."

Is SQL Server memory for file cache totally independent from Windows? If not, Windows may still use free memory outside of SQL Server for caching pages from SQL Server, which will affect the final modelling result.
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6283 Visits: 1407
I learned a new thing. Thanks guys.



Yamachari-462811
Yamachari-462811
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 247
"Creating a bigger test database will also work, but involves a lot more work, will cause your tests to run longer, and might require you to clean up your collection of holiday pictures."

Personally, I don't care for any of the possible answers.

I've never had to cut down a 1TB database to 1GB for testing. Why would that be more work than dropping it to 5GB? I wouldn't recommend keeping so many personal pictures on your company's computer. I also wouldn't recommend doing this kind of performance testing on a laptop or desktop.

You want your testing environment to match your production environment as closely as possible. Move ALL the data back down to your dev or test server and rip into it. If you don't have the room to place it then there is a good chance you don't have a disaster recovery plan in place.

David
AJ-148218
AJ-148218
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 41
I think this is more or less the outcome of the discussion so far, but I think a combination of limiting the server memory and DBCC FREEPROCCACHE before executing the procedure would be the way forward to cover both the "memory pressure" and the "fair comparison" elements of the question.

Note that a CHECKPOINT immediately before the performance test is recommended to also write the dirty
buffers to disk.
Vitali Lisau
Vitali Lisau
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 183
Maybe use all of this:

1) checkpoint
2) dbcc dropcleanbuffers
3) dbcc freeproccache
4) dbcc freesystemcache('all')

(The last one N4 probably is the same as N2 + N3).

Using tests for the whole TB database may be very time-consuming even when you have the whole DB available. This should be done after the first approach with reduced in size DB.
lucassouzace
lucassouzace
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 79
About question: Simulating memory pressure

You have been asked to optimize a stored procedure that runs against a terabyte-sized database. The stored procedure executes several steps consecutively. The performance problems appear to be mainly I/O related.

You install a severely trimmed down test version of the database (1 GB in size) on your desktop computer running SQL Server Developer Edition. Before you start optimizing, you want to establish a baseline by timing the stored procedure on your development machine, so that you can later compare performance after adding indexes and tweaking code.

However, your desktop has 2 GB of memory installed, and you are concerned that the performance test results may be skewed because the test version of the database fits entirely in cache. What is the best way to simulate the production circumstances as closely as possible?

I'm don't understand, someone prune my explicate? Thank you Sad that question is very hard for me! Sad i'm novice in SQL Server
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 3821
lucassouzace,

You should read the previous posts in this thread. A number of people thought the 'correct' answer was wrong or the question did not have a single answer.

I am happy to accept that the 'correct' answer will simulate memory pressure, but I am also certain the 'correct' answer is not the right way to troubleshoot a problem with a 1TB table.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search