Memory usage

Question

Memory usage

mah_j

SSCrazy

Points: 2931
More actions
December 8, 2012 at 2:26 am

#285979

HI
In our server when we have time out in select & insert we have lack of memory.
the total memory is 24G,and free mem is 800M.
max server memory is 20480 M
min server memory is 0
sqlservr.exe gets 500M
also MsDtsSrvr.exe gets 5G
I dont have any idea what to do???!!!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

mah_j (12/8/2012)
HI
In our server when we have time out in select & insert we have lack of memory.
the total memory is 24G,and free mem is 800M.
max server memory is 20480 M
min server memory is 0
sqlservr.exe gets 500M
also MsDtsSrvr.exe gets 5G
I dont have any idea what to do???!!!

I would't say it's necessarily a memory problem. Most likely, the timeout is caused by improper indexing which can cause large numbers of frequent page and extent splits along with some performance challenged code.

Post the offending code and the actual execution plan IAW the second link in my signature line below and someone may be able to help.

--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)

mah_j SSCrazy Points: 2931 More actions · Answer 2

Thanks a lot for your reply.

Due to the increases in page reads counter in performance monitor(during the time out) I think the problem is fragmentation.I will check the fragmentation at night to see if it needs rebuild Index.Am i in the right way?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No.

Page reads != fragmentation. Page reads means swap file usage.

As for the memory, your memory settings look a little high if there's something outside of SQL that is using 5 GB of memory. If SQL is allowed 20 GB and DTS is using 5GB, that's 25GB, more than your server has. Maybe consider dropping max server memory down slightly.

Edit: corrected.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

mah_j (12/9/2012)
Thanks a lot for your reply.
Due to the increases in page reads counter in performance monitor(during the time out) I think the problem is fragmentation.I will check the fragmentation at night to see if it needs rebuild Index.Am i in the right way?

No. Rebuilding indexes isn't likely to fix the timeout problem you're having. You need to find the uderlying reason for the timeout. Like I said, it may be because of indexes that have a first column that has very few unique values in the column and it's causing page splits. Rebuilding the index could actually make things worse, if that's the case. You need to build a better index, in such cases.

But, like I said, it could be for other reasons, as well. It may be just some crap code that's doing scans all over the place as a part of the validation for the inserts. It may even be because your ORM is doing something like providing NVARCHAR parmaters for VARCHAR columns which cause the entire column to be converted for individual inserts. It might even be that you have an explicit transaction with other "stuff" going on in the transaction and it's causing deadlocks or just terribly long blocking.

--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: 1004409 More actions · Answer 5

GilaMonster (12/9/2012)
No.
Page reads != fragmentation.

Do you consider page splits to be a form of fragmentation?

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeff Moden (12/9/2012)
GilaMonster (12/9/2012)
No.
Page reads != fragmentation.
Do you consider page splits to be a form of fragmentation?

Page splits may cause logical fragmentation (or may not, depending which pages split).

Memory\Page reads/sec (the perfmon counter) is pages (windows memory pages) read into memory from disk because they're referenced in virtual memory but not present in physical memory, ie hard page faults, and isn't even a SQL specific counter. Probably SQL is either being swapped to the page file because there's other stuff on the server using memory or something else on the server is being swapped to the page file

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

GilaMonster (12/10/2012)
Jeff Moden (12/9/2012)
GilaMonster (12/9/2012)
No.
Page reads != fragmentation.
Do you consider page splits to be a form of fragmentation?
Page splits may cause logical fragmentation (or may not, depending which pages split).
Memory\Page reads/sec (the perfmon counter) is pages (windows memory pages) read into memory from disk because they're referenced in virtual memory but not present in physical memory, ie hard page faults, and isn't even a SQL specific counter. Probably SQL is either being swapped to the page file because there's other stuff on the server using memory or something else on the server is being swapped to the page file

Ah... thanks Gail. Now I see where you're coming from on the "Page reads != fragmentation" thing.

I was questioning that because page splits (which I think of as a sort of "physical fragmentation") can cause relatively large numbers of pages to be read during any form of a scan. In that case, the number of page reads has a direct correlation to the number of and "age" (they empty space may have be inserted to) of page splits because the scans have to read more pages to get the same info as if the pages weren't split.

And sorry for writing about something you already know. Just 'splainin' to myself out loud.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (12/10/2012)
In that case, the number of page reads has a direct correlation to the number of and "age" (they empty space may have be inserted to) of page splits because the scans have to read more pages to get the same info as if the pages weren't split.

Yeah, but that's not going to spike the Memory\page reads/sec counter in perfmon (assuming that is the counter that the OP is looking at), because that counter is tracking hard page faults (pages in virtual memory not in physical memory), it's not a SQL-specific counter that tracks database pages read.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

GilaMonster (12/10/2012)
Jeff Moden (12/10/2012)
In that case, the number of page reads has a direct correlation to the number of and "age" (they empty space may have be inserted to) of page splits because the scans have to read more pages to get the same info as if the pages weren't split.
Yeah, but that's not going to spike the Memory\page reads/sec counter in perfmon (assuming that is the counter that the OP is looking at), because that counter is tracking hard page faults (pages in virtual memory not in physical memory), it's not a SQL-specific counter that tracks database pages read.

Ah, dang it. Sorry for the confusion. I was thinking the wrong reads. Thanks, Gail.

--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)

mah_j SSCrazy Points: 2931 More actions · Answer 10

Thank you both for your helpful explanation.

I will drill down into the details of memory management.