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

Memory usage Expand / Collapse
Author
Message
Posted Saturday, December 8, 2012 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 109, Visits: 1,105

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???!!!

Post #1394315
Posted Saturday, December 8, 2012 10:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 35,780, Visits: 32,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394377
Posted Sunday, December 9, 2012 3:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 109, Visits: 1,105
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?
Post #1394389
Posted Sunday, December 9, 2012 8:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1394398
Posted Sunday, December 9, 2012 4:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 35,780, Visits: 32,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394421
Posted Sunday, December 9, 2012 4:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 35,780, Visits: 32,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394422
Posted Monday, December 10, 2012 1:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1394474
Posted Monday, December 10, 2012 6:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 35,780, Visits: 32,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394568
Posted Monday, December 10, 2012 6:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1394573
Posted Monday, December 10, 2012 4:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 PM
Points: 35,780, Visits: 32,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394818
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse