Server Slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Server Slow

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-06-16 : 20:49:24

We have a virtual server which is running on 16 GB RAM and now when several users accessing this data from the servers for their reporting then it gets absolutely stand still and sometimes front-end users were thrown out of the server.
When I looked into this, there is decrease in FREE PAGES of its memory and on releasing this memory, users doing ok.
Server Buffer Counter_name cntr_value cntr_value_MB
MSSQL$EAM:Buffer Manager Free pages 133 1.0390625
MSSQL$EAM:Buffer Manager Total pages 12086 94.421875
MSSQL$EAM:Buffer Manager Database pages 5847 45.6796875

This is when released on clearing the Cache and Buffers the FREE PAGES were really high.

I think this is temporary solution but just this happen over the years neither Users accessing the server has increased nor decreased.
(We know increasing the RAM would solve our problems) But when there is no change in the data and over the number of years Users neither increased or decreased.

Now where can I find what causes this problem and where I can pinpoint this?

Can anyone analyse this and let me know where I can find the CAUSE.

Many Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-18 : 00:25:49

How much data are the users pulling in their reports?

Is anything other than SQL running on the server?

What is the max memory setting?

Looked at Perfmon counters Total Server Memory and Target Server Memory?

Is this a dedicated reporting box or are there OLTP transactions occurring?

Is this 32 or 64 bit?

Is lock pages in memory granted for service account?

Looked at the queries that pull the data?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-18 : 01:41:27

Are there any ad-hoc queries bloating the cache?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-06-20 : 20:18:25

Sorry for the late reply.

Thanks for your questionnaire.

How much data are the users pulling in their reports?
Data is 512 MB Considerably very small but RAM is 16 GB with 6- users

Is anything other than SQL running on the server?

No

What is the max memory setting?

16 GB

Looked at Perfmon counters Total Server Memory and Target Server Memory?

object_name counter_name cntr_value cntr_type
MSSQL$EAM:Memory Manager Target Server Memory (KB) 1620096 65792
MSSQL$EAM:Memory Manager Total Server Memory (KB) 588512 65792

Is this a dedicated reporting box or are there OLTP transactions occurring?

Yes.

Is this 32 or 64 bit?

32-Bit Virtual Server.

Is lock pages in memory granted for service account?

Yes

Looked at the queries that pull the data?

This is the worrying factor as there are more than 4 users + then server tends to slowdown and even strangle the users not going to fetch the data.

Are there any ad-hoc queries bloating the cache?

No

Any Solution Please.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-20 : 21:56:31

You're using less than half the allocated memory.

Are you booting with the /PAE switch?
Enabled AWE?

By the way, what OS and what Edition of SQL?

Finally, saw this?: http://msdn.microsoft.com/en-us/library/ms175581(v=sql.90).aspx

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-06-20 : 23:03:45

Thanks Russel for your time.

Correctly spotted this one and I could not rectify since there were no change in either users or the memory allocated yet the server tends to get slowed down when the same number of users connected now.

Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86) Sep 6 2009 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

AWE is enabled.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-06-22 : 18:41:30

Any further Clues to this niggling problem of mine.

Thanks again.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2011-06-23 : 21:22:28

Server Buffer Counter_name cntr_value cntr_value_MB
MSSQL$EAM:Buffer Manager Free pages 133 1.0390625
MSSQL$EAM:Buffer Manager Total pages 12086 94.421875
MSSQL$EAM:Buffer Manager Database pages 5847 45.6796875

Can anyone explain the COUNTERNAME relation for FREE PAGES to TOTAL PAGES to the DATABASE PAGES.

Many Thanks for all participated, is real worth learning for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources