| Author |
Topic |
|
izaltsman
A custom title
1139 Posts |
 Posted - 2002-06-24 : 11:40:53
|
| Hi All! I have a server with 4GB RAM (SQL2k Standard Edition running on Win2k Adv. Server). Since the standard edition of SQL Server can only use up to 2GB of memory, I have almost 2GB free on the server at all times. I have a job that generates some files on the local drive, compresses them (using PKZipC), and copies them to a network share. I noticed that performance of my server drops dramatically while this job runs. When I went to investigate, I saw that the value of Memory:Pages/Sec PerfMon counter (which normally stays at 0) jumps as high as 1700 while the job runs. At the same time the amount of available free memory remains at almost 2GB. So what I want to know is why my server is paging memory like mad despite the fact that it has another 2 gigs of RAM at its disposal. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-24 : 11:46:25
|
| Is it a sql server process that generates the files? BCP or DTS maybe? If so, sql may need to buffer up those pages for select thus swapping out what you have in memory. PKZip should run in its own memory space, right?<O> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 13:15:59
|
| Do you notice the excessive paging during the query phase of the job, the compression, or the file copying? ( or a combination? )Jonathan Boott, MCDBA |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-24 : 13:31:34
|
| The files it generates are dump devices (the job executes a database backup that spreads the data across 6 devices). I believe PKZip should run in its own memory space even though it gets kicked off by a SQL Server job. I see memory paging going on for the entire duration of the job -- starting when the backup begins, and continuing until the last file has been copied.Edited by - izaltsman on 06/24/2002 13:33:07 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-24 : 14:04:11
|
| Well the backup is certainly going to cause paging. Any dirty pages will need to be flushed from the log cache, and then (I believe) the devices are written to from memory, not straight from disk. I assume you are using a cmdshell step or xp_cmdshell to run pkzip and the copy steps. I would expect these to run outside of SQL's 2 gigs, but I could be wrong. It may be that once the backups are through and additional requests come into the server all those pages need to be recalled back into the buffer. I don't know. Can you verify that pkzip and copy are running outside the sql memory space?<O> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 14:07:26
|
| try[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q139609[/url]Jonathan Boott, MCDBA |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2002-06-24 : 14:22:35
|
| In my opinion, there is no way on earth that your server can be paging to or from disk 1800 times per second. Have a look at KB article Q139609. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-24 : 14:31:09
|
| I think that counter indicates how many pages of data are written per second, not how many times it writes a page each second.Ilya, how big is the Windows swap file on that server? Have you tried twiddling its size to see if it alleviates this? Also, are you using a permanent swap file instead of letting Windows handle it automatically? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 15:15:42
|
| Not to beat a dead horse here, but Q139609 implies that the paging/sec counter is incremented when a page is accessed from disk and when pages are sequentially read from a memory-mapped file, which would not be disk access. My guess is that is the mechanism used by SQL Server to sequentially read pages from the database for the backup, and the paging is phantom. Q139609 advises to check the paging file utilization % counter to confirm this.Jonathan Boott, MCDBA |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-24 : 16:39:29
|
| Thanks for all your replies, guys! Page47:As far as I can tell, PKZip is running outside of SQL's 2gigs. But I will test it further just to make sure. I don't believe the data needed for the incoming SQL requests is pushed out of cache by the backup operation (my SQLServer:Buffer Manager\Buffer cache hit ratio counter for that time period remains at 100%)Setbased and BM1000:Yep, looks like that article does apply to my situation. I wish it had mentioned something about the performance implications of the sequential reading of memory-mapped files though. I mean, obviously it means a lot of I/O activity, but based on the performance counters, my disks should be able to handle the load. Yet, I am seeing significant performance degradation. Any thoughts?Rob:The size of the pagefile is 2GB, which should be more than enough. But per setbased's suggestion I will add the pagefile utilization counter next time I collect performance data on that machine to see what's going on there.Edited by - izaltsman on 06/24/2002 16:40:57 |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 17:58:08
|
| What are the characteristics of the degradation? A CPU spike? Network segment saturation? Excessive disk queue length?What are the specifications for your disk subsystem?Jonathan Boott, MCDBA |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-24 : 18:49:21
|
| The problem manifests itself in severely increased response times (to the point where some of the ASP pages that touch this server begin timing out). Right now I am trying to figure out where the bottleneck is, and I'm thinking along the exact same lines you are... I can definitely say that CPU isn't a problem. Network segment appears to be ok also. For disk queue though I am going to have to obtain another PerfMon log with a shorter sampling interval before I can come out with any conclusions (the log I have right now shows only one spike to 4 and the rest of the values are low... but that's with a 5 min sample interval...). And of course there is that memory paging thing that I brought up in the begining of this thread (although I am feeling better about it after reading the KB article you referenced). Anyhow, I should have more info on it within a few days. I do appreciate any thoughts you might want to share in the meantime. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-07-08 : 11:08:16
|
Well, in case anyone is interested, I have done some further testing, and my disk system was indeed the bottleneck. Thanks to everyoneone who responded. Huge thanks to Jonathan for pointing out the KB article! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-08 : 11:11:37
|
 thanks Ilya.So out of curiousity, what did you do to alleviate the bottleneck?Jonathan Boott, MCDBA |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-07-09 : 10:16:39
|
| For now I spread my files across several network shares. Also, I might be able to purchase more disks some time in the near future. |
|
|
|
|
Memory Paging