Help me crash my server!

Question

Help me crash my server!

Henry_Lee

SSCrazy

Points: 2714
More actions
March 10, 2010 at 12:11 pm

#386476

Good day,
We've had a few server crashes lately due to memory, so I'm trying to get a real good understanding of how memory works.
Setup is SS 2008 Standard, Win 2003 Standard, 4 gb memory, AWE is not enabled, all default SQL settings.
I thought I had a decent grasp of the concepts - the buffer pool, AWE, data pages, page file, etc. I monitor perfmon fairly regularly looking at working set, target and total server memory, page life expectancy, etc. So I'm not a total rookie...
I'm trying to intentionally crash a test server, so I can see exactly what type of actions will do this. My ultimate goal is to be able to tell developers "when you do a, b, and c that will probably crash the server". At the same time, a better understanding will allow me to better plan how much memory each server requires.
So, I tried a few different things like setting max server memory real low to 512 MB, then writing inefficient queries (no indexes, functions everywhere) insuring table scans to make sure every page had to be read. I did this on several connections at the same time against tables anywhere from 5 - 10 GB in size and couldn't crash the server.
I was hoping someone could tell me "do this, that, and this other thing at the same time and you'll probably crash your server".
Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

We've had a few server crashes lately due to memory, so I'm trying to get a real good understanding of how memory works.

With this message?

Error: 701, Severity: 17, State: 123.

There is insufficient system memory to run this query.

Causes are running dlls within the SQL Server address space including:

a) Linked Servers

b) Any OLE/COM components loaded in SQL Server sp_OA*s

c) Extended Stored Procedures ( xp_*)

d) sp_xml_preparedocument

e) SQL Mail components

f) SQL CLR

Take a look at "Understanding the VAS Reservation (aka MemToLeave) in SQL Server" at

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

Henry_Lee (3/10/2010)
Good day,
We've had a few server crashes lately due to memory, so I'm trying to get a real good understanding of how memory works.
Setup is SS 2008 Standard, Win 2003 Standard, 4 gb memory, AWE is not enabled, all default SQL settings.
I thought I had a decent grasp of the concepts - the buffer pool, AWE, data pages, page file, etc. I monitor perfmon fairly regularly looking at working set, target and total server memory, page life expectancy, etc. So I'm not a total rookie...
I'm trying to intentionally crash a test server, so I can see exactly what type of actions will do this. My ultimate goal is to be able to tell developers "when you do a, b, and c that will probably crash the server". At the same time, a better understanding will allow me to better plan how much memory each server requires.
So, I tried a few different things like setting max server memory real low to 512 MB, then writing inefficient queries (no indexes, functions everywhere) insuring table scans to make sure every page had to be read. I did this on several connections at the same time against tables anywhere from 5 - 10 GB in size and couldn't crash the server.
I was hoping someone could tell me "do this, that, and this other thing at the same time and you'll probably crash your server".
Thanks!

It doesn't sound like a bad server setting... it sounds like a memory leak in someone's code. Are you using sp_OA* at all? Maybe a poorly formed CLR? Someone not closing global cursors or global temp tables? There are a lot of things that can cause a memory leak.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Henry_Lee SSCrazy Points: 2714 More actions · Answer 3

Thanks Carl.

Actually the message was this:

Error: 701, Severity: 17, State: 123.

There is insufficient system memory in resource pool 'internal' to run this query.

Of your list of suggestions to look at the only one we do a lot of is linked servers.

How do you typically monitor how much memory is being consumed by the different items on your list? Do you have any particular DMV's or perfmon counters you like to look at to help diagnose this?

Thanks again.

Henry_Lee SSCrazy Points: 2714 More actions · Answer 4

Thanks Jeff.

We don't really use xp's or CLR. Good suggestion on global temp tables, I believe folks know the difference between local and global temp tables, but I will investigate to make sure we're using those properly.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 5

Now that I've said it, the temp table thing is probably the least of your worries because they automatically drop... temp tables drop when the current session ends... globabl temp tables drop when all sessions using them ends. Same goes for most cursors. IIRC, there may be issues with connection pooling for those.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 6

Just a thought... are any other apps besides SQL Server running on that particular box?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 7

Have you run something like memtest86+[/url] (Free and open source) to rule out hardware faults?

Edit: Why are links not underlined by default?

Henry_Lee SSCrazy Points: 2714 More actions · Answer 8

Jeff,

The only other services of note are Symantec Critical System Protection and Symantec Endpoint Protection.

I know there's lots of discussion out there about AV on SQL boxes, but I don't know enough about the subject myself. I believe we're omitting the mdf's and ldf's from being scanned, so I thought that was good enough.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 9

Found this:

SQL Server reports 701: "There is insufficient memory to run this query" when executing large batches

http://support.microsoft.com/kb/2001221

http://support.microsoft.com/kb/2000380

I also recall a SQL Server 2008 "memory leak" condition with linked servers but cannot find a KB url.

SQL = Scarcely Qualifies as a Language

Henry_Lee SSCrazy Points: 2714 More actions · Answer 10

Henry_Lee

SSCrazy

Points: 2714

March 12, 2010 at 1:17 pm

#1132982

Good find, thanks Carl.

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 11

A couple jobs ago on a sql server 2005 standard edition instance I had a problem like that with a memory leak. Randomly, even when the server wasn't busy I'd get a memory dump then that error message with the next query that ran. It would happened every couple of weeks and drove me nuts. After ruling out all the things Jeff mentioned I looked at anything that wasn't SQL related that ran on the server and found that a previous admin had installed APC Powershoot. After I uninstalled it I never had another memory leak issue with that server.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Henry_Lee SSCrazy Points: 2714 More actions · Answer 12

Thanks tstaker.

The only other services on the box are Symantec. If I can't find anything on the SQL side, I'll take a good look at Symantec.