Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql server memory Expand / Collapse
Author
Message
Posted Monday, January 07, 2013 12:28 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi All,

I am not sure if this is a fair ask or no but this is something i want to learn much internals about sql server memory and how sql server responds to memory pressure and i wanted to see that practically.

Environment : windows 2003, 4 GB physical RAM, SQL server 2005 sp4.

Checking in if someone has some demo tsql scripts which can blow up memory so that i can setup the memory counters and monitor sql server memory usage.

Did anyone has done this earlier?

Thanks in advance.


Post #1403810
Posted Monday, January 07, 2013 1:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
I'm not entirely clear on what you're asking.

Do you need a way to monitor SQL Server memory use?

Or

Do you need a T-SQL script that will consume a huge amount of memory so you can test a monitor?

If you need a way to monitor memory use, you have a number of options. Partially, it depends on budget. RedGate (owners of this site) have a monitoring tool. So do SolarWinds, and several other companies. Search for "monitor server memory use" online and you'll find plenty of solutions for this.

If you need a script that will use a ton of memory, that's pretty easy to do. Open a connection, start a transaction, update a single row in a table, but don't commit the transaction or close the connection. Repeat this till the memory is as loaded up as you like. Make sure you aren't using SSMS on the server to do this, because you'll get SSMS eating more memory for the connections than you will get SQL Server eating memory for the transactions. Should use a ton of RAM in a sort of DDOS manner.

Alternatively, create a few varchar(max) datatype variables, and load strings into them till SQL Server dies, or you get the RAM load you're looking for.

Lots of ways to eat up a lot of RAM.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1403841
Posted Monday, January 07, 2013 8:19 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi Thank you.

I am looking for T-sql script which will use up memory so that i can monitor which process is eating up most of memory.
Post #1403949
Posted Monday, January 07, 2013 11:03 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi Gus,

I wanted to reproduce the below errormsg. I was able to inflate the sql memory. But i need someother process or exe which blot the memory and results in below error message. how to do it?

“A significant part of SQL Server process memory has been paged out. This may result in performance degradation".
Post #1403975
Posted Monday, January 07, 2013 11:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 20,458, Visits: 14,084
I have had Apache cause that several times for me. I have even had malware scanners cause it.





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1403976
Posted Monday, January 07, 2013 11:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Oracle_91 (1/7/2013)
“A significant part of SQL Server process memory has been paged out. This may result in performance degradation".
ANy reason why you ONLY need this particular error msg?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1403982
Posted Tuesday, January 08, 2013 12:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Nothing. Just wanted to reproduce the issue and see the performance counters. Doing some reading Memory....

Do you have any steps to reproducing the error?
Post #1404008
Posted Tuesday, January 08, 2013 2:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Oracle_91 (1/8/2013)
Do you have any steps to reproducing the error?
NO.

but see if these link can help you
http://blogs.msdn.com/b/karthick_pk/archive/2012/06/22/a-significant-part-of-sql-server-process-memory-has-been-paged-out.aspx
http://support.microsoft.com/kb/918483


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1404061
Posted Tuesday, January 08, 2013 6:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Oracle_91 (1/7/2013)
Hi Thank you.

I am looking for T-sql script which will use up memory so that i can monitor which process is eating up most of memory.


Whichever part your script targets.

I guess I'm a bit lost here. If you deliberately overload memory, what you'll see eating up memory is the thing you used to overload it.

Bend your finger backwards till it hurts (a little bit). If you then look at your hand to figure out what's causing the pain, it'll be you bending your finger backwards. In other words, you already know the cause, because you deliberately picked it and implemented it.

I'm not seeing the benefit to this, unless what you're doing is testing some sort of monitoring tool to see if it correctly identifies the problem and says (metaphorically), "Hey, check it out, you're bending your finger backwards! You should stop doing that." On the other hand, if the monitoring software says, "Possible stubbed toe detected. Suggested handling: Amputate at hip", then you've found out something useful, and know that you can dispense with using that monitoring software.

But that doesn't seem to be what you're doing here.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1404218
Posted Tuesday, January 08, 2013 9:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 03, 2014 2:14 PM
Points: 746, Visits: 1,702
Hi Gus,

I am just testing/learning memory concepts over here. not to throttle any productions systems
Post #1404356
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse