IT Employment

Ask something
Discussion Locked

Looking for some quick help.

0 helpful
13 replies

by tbragsda 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

All,

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:
HP-DL380
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.

Problem;
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?

13 replies

by deepsand

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?

by tbragsda

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

TBR

by deepsand

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.

by 5jgibbs

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

by tbragsda

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

Thanks.
TBR

by tbragsda

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

TBR

by deepsand

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

by awfernald

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.

by notsochiguy

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!

by tbragsda

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

by tbragsda

.
To add, watched locks grow to bafeling highs this AM, and procedure cache. Got to find that one.

After the locks cleared, were back to the paging problem. After watching this thing for about a day now, its really getting to me that paging is high. In most cases, it seems like memory is just fine, and its paging for no reason. Dont even see a great number of additional processes.

by tbragsda

.
I think its the apps/querys/dbs. I can't see anything wrong with memory, other than it seems to not use it.

I have completley eliminated hardware, to my satification anyway. Proveing this is the crapy dbs will be hard, but its the last thing it can be.

Any other way around this problem I could think of is lost to me.

by tbragsda

All,

Thanks all. Wanted to provide some closure to this for anyone reading.

Looking at this for several days shows that paging is the problem. The SQL paging is only 1/2 or less than the overall paging issue. WINLOGIN is the other. The apps seem to be connecting at a allarming rate (100+/s). This causes WINLOGIN to page like crazy. Add to this apps that never give up any memory, and we have big preformance loss.

So, to recap. Crap apps all along.

This discussion is closed to new replies.