IT Employment

General discussion


Looking for some quick help.

By tbragsda ·

I will post in Q/A to give out points, but I need some eyes on this as soon as possible.


Got a MS-SQL server with paging problems.

System info:
2x Xeon 3.? GHz
3g RAM
SQL 7 sp4
NT 4 sp6

All drives are SCSI3 10K RPM.
Page file is on scratch drive
Temp DB is on separate scratch drive
Data is on raid 5.
SQL memory is set to dynamic.

Dam thing is paging between 80/s and spiking to 1000+/s. Looking at processed etc. not overloaded server. Not virus. The queries and apps are bad, but not that bad. Databases are poorly optimized, some missing indexes etc., but again, just not that bad.

Looking, its SQL doing all the paging. I have seen too much memory going to SQL, and starving the OS. Watching the spikes, it seems like memory just not getting allocated. Tried setting static memory, and about the same result.

Any thoughts?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

In the beginning ...

by deepsand In reply to Looking for some quick he ...

Presumably, there was a time when all was well.

The first rule in troubleshooting any system is, what change(s) were effected immediately prior to the appearance of the problem?

Collapse -

Love to, but can't

by tbragsda In reply to In the beginning ...

Not enough history on this server. In a tough spot. Had a DBA "tweeking" as best I can tell. I did a quick reinstall of sql and SPs, but no go as master holds that stuff. If push comes to shove, I will DTS or restore the dbs into a clean install, but really hope to not have to do that.

Thanks, keep them comming.


Collapse -

Well,with no evidence to work with, ...

by deepsand In reply to Love to, but can't

I suspect that the cumulative effect of the various application deficiencies that you mentioned may have reached the point where performance is suffering noticeably.

You might turn an eye toward remediation of such, particularly the missing indices, before resorting to more drastic measures.

Collapse -

I think you are right.

by tbragsda In reply to Well,with no evidence to ...

Thats my position. Got to run out hardware, software config before I can offer the expensive time consuming solution.

Looking at some of the DBs, I think they were build by a congressional subcommittee.

Im not discounting the chance im overlooking something, so anything else you can think of, let me know.


Collapse -

Yep; there's always room for a little more slop.

by deepsand In reply to I think you are right.

But, eventually, you find yourself trying to put 15 pounds of sh*t in a 5 lb. bag.

Collapse -


by 5jgibbs In reply to Looking for some quick he ...

i have a feeling its that OS your running.. windows did not really get good at managing memory "ram" until 2003 server and XP... even then .. its still pretty bad.. maybe you want to get one of those ram sweeping programs.. that may be a temp fix until you can find your memory leak

Collapse -


by tbragsda In reply to Hmm

I have NT servers running SQL7 and not paging at more than 30/s.

The server will need a OS upgrade, but now is not the time.

Its not like a memory leek, as on startup we have about the same results.


Collapse -

SQL memory allocated

by awfernald In reply to Looking for some quick he ...

Sounds to me like your dba screwed up the SQL Memory allocation.

I think the following applies to SQL Server versions 7 and before.

Just taking 1 Gb off the top of my head, say that he went in and allocated "512 units" of memory to SQL. All looks fine and dandy, until you realize that the SQL memory allocation units have always been measured in TWO (2) Mb increments rather than One MB. So sounds to me like he "tweaked" this by allocating too much memory to SQL.

Check to make sure using enterprise manager that he hasn't stolen all your RAM for his DB.

Collapse -

Some thoughts for you.....

by NotSoChiGuy In reply to Looking for some quick he ...

1). If you have access to something like Diskeeper or other defrag software, it may be helpful to analyze the drives to see what shape they are in with regards to fragmentation. I know NT and SQL7 are both pretty bad in this area.

2). Are you running any sort of maintenance plans on the dbs? Backup the trans, clear them out, re-index, compact, etc?? If so, it may help to throttle down the frequency, or set them to run off-hours entirely. Typically, I don't like doing this, but it could help isolate/eliminate potential causes of the issue.

3). I've also seen sometimes that using Enterprise manager GUI to set the memory allocation doesn't always work in SQL. There is a SP you can run (sorry, don't have my SQL notes handy, or I'd tell you) from within Query Analyzer that will also set the memory allocation. You'll have to restart the SQL Agent to have the change take hold.

Hope some of this is helpful. Good luck!

Collapse -

Lookin for the SP.

by tbragsda In reply to Some thoughts for you.... ...

In 6.5 you could set all memory from this set of SPs. Been looking for same such. I do think someone has set manualy, and most any change I make from EntMgr doing nothing.

Related Discussions

Related Forums