January 7, 2013 at 12:28 pm
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.
January 7, 2013 at 1:12 pm
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
January 7, 2013 at 8:19 pm
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.
January 7, 2013 at 11:03 pm
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".
January 7, 2013 at 11:08 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 11:34 pm
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?:w00t:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 8, 2013 at 12:52 am
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?
January 8, 2013 at 2:12 am
Oracle_91 (1/8/2013)
Do you have any steps to reproducing the error?
NO.
but see if these link can help you
http://support.microsoft.com/kb/918483
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 8, 2013 at 6:56 am
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
January 8, 2013 at 9:53 am
Hi Gus,
I am just testing/learning memory concepts over here. not to throttle any productions systems 🙂
January 9, 2013 at 11:08 am
Oracle_91 (1/8/2013)
Hi Gus,I am just testing/learning memory concepts over here. not to throttle any productions systems 🙂
Ah! That makes sense then.
Yeah, learning how to deliberately overload the data cache vs the plan cache vs crushing the O/S space, etc., are all useful things. Good idea. Gives you a better idea of what can do those kinds of things, so you can fix it when it happens in the wild.
Try some things like loading too many big tables into active queries, all at the same time.
Try firing off a bunch of hyper-complex queries all at once.
Try non-deadlock long locks.
- 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
January 9, 2013 at 6:40 pm
You may also try to run a query with a huge IN clause....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply