InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 09:52:13 GMT20http://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspxThank you both for your helpful explanation.I will drill down into the details of memory management.Tue, 11 Dec 2012 01:45:09 GMTmah_jhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]GilaMonster (12/10/2012)[/b][hr][quote][b]Jeff Moden (12/10/2012)[/b][hr] 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.[/quote]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.[/quote]Ah, dang it. Sorry for the confusion. I was thinking the wrong reads. Thanks, Gail.Mon, 10 Dec 2012 16:46:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]Jeff Moden (12/10/2012)[/b][hr] 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.[/quote]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.Mon, 10 Dec 2012 06:22:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]GilaMonster (12/10/2012)[/b][hr][quote][b]Jeff Moden (12/9/2012)[/b][hr][quote][b]GilaMonster (12/9/2012)[/b][hr]No.Page reads != fragmentation. [/quote]Do you consider page splits to be a form of fragmentation?[/quote]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[/quote]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.Mon, 10 Dec 2012 06:08:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]Jeff Moden (12/9/2012)[/b][hr][quote][b]GilaMonster (12/9/2012)[/b][hr]No.Page reads != fragmentation. [/quote]Do you consider page splits to be a form of fragmentation?[/quote]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 fileMon, 10 Dec 2012 01:24:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]GilaMonster (12/9/2012)[/b][hr]No.Page reads != fragmentation. [/quote]Do you consider page splits to be a form of fragmentation?Sun, 09 Dec 2012 16:33:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]mah_j (12/9/2012)[/b][hr]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?[/quote]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.Sun, 09 Dec 2012 16:32:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspxNo.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.Sun, 09 Dec 2012 08:48:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspxThanks 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?Sun, 09 Dec 2012 03:43:43 GMTmah_jhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspx[quote][b]mah_j (12/8/2012)[/b][hr]HIIn 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 Mmin server memory is 0sqlservr.exe gets 500Malso MsDtsSrvr.exe gets 5GI dont have any idea what to do???!!![/quote]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.Sat, 08 Dec 2012 22:16:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1394315-391-1.aspxHIIn 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 Mmin server memory is 0sqlservr.exe gets 500Malso MsDtsSrvr.exe gets 5GI dont have any idea what to do???!!!Sat, 08 Dec 2012 02:26:04 GMTmah_j