SQL Server response times slow

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)

SQL Server response times slow

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-24 : 10:12:51

Guys,

I am running sql server 2000 with 30GB database on windows 2003 server having 2GB ram. The application was running extremely slow and I restarted sql server. After restart
the response times improved and then gradually back to normal self (slow). The memory used by sql server is consistent at 1.6GB.

After from query tuning is there anything I need to look at on the database server and instance level settings to improve the performance.

Any suggestion/inputs would help.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-24 : 10:23:14

Any Error in Application log regarding Memory Pressure or Memory Leak.You can run performance monitor with counters like Memory pages/sec,available bytes, buffer cache hit ratio or page life expectancy..Do you see memory paging out?

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-24 : 10:31:22

How can you check memory paging out and buffer cache hit ratio?

What does memory paging indicate

Any inputs would help greatly

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-24 : 10:49:09

Is it 64-bit SQL Server?

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-24 : 12:32:16

it is 32 bit server

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-24 : 17:26:54

Does adding 4GB RAM allow sql server 2000 (32bit) to grab more memory

how is this different from sql server 2000 with 64bit with 4 GB RAM

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-24 : 17:41:30

You'd have to enable AWE and the "lock pages in memory" setting to see the additional memory.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-24 : 19:58:36

Even when the RAM is increased TO 4GB on the server does the sql server 2000 (32bit) not automatically grab the memory unless AWE is enabled - is this correct.

As for sql server 2000 64bit the sql server automatically grabs the memory from OS without AWE set - is this correct

I come from Oracle where you can set shared pool memory, buffer pool as system parameters and restart the instance to take effect. I guess it is very different in SQL Server

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-24 : 22:00:41

How much of total memory you have in your server?

In 32-bit Server,If you have Window server 2003 without SP1, then to allocate more than 2 GB of RAM to SQL server,you have to enable /PAE to boot.ini to /3GB switch.Then you can enable AWE for SQL Server to use more memory

But with Window 2003 Sp1,/PAE is already enabled if you have 2GB of RAM.

In 64-bit, You need to enable 'Lock pages in Memory' so that Memory is not paged out and /PAE and boot.ini means nothing to it.

Hope now it is clear.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-24 : 22:18:00

quote:
Originally posted by amsqlguy

How can you check memory paging out and buffer cache hit ratio?
It will be logged in SQL Server log and Application Log or see in Performance Monitor for Memory pages/sec.
What does memory paging indicate

-- Indicates memory pages are not used and instead use disk space for data which doesn't fit in RAM.
Any inputs would help greatly

Thanks

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-25 : 08:45:50

quote:
Originally posted by sodeep

How much of total memory you have in your server?

In 32-bit Server,If you have Window server 2003 without SP1, then to allocate more than 2 GB of RAM to SQL server,you have to enable /PAE to boot.ini to /3GB switch.Then you can enable AWE for SQL Server to use more memory

But with Window 2003 Sp1,/PAE is already enabled if you have 2GB of RAM.

In 64-bit, You need to enable 'Lock pages in Memory' so that Memory is not paged out and /PAE and boot.ini means nothing to it.

Hope now it is clear.

We have windows 2003 with SP2 having 2GB RAM, we are trying to bump up the RAM to 4GB. After which we are trying to figure out

1. Does SQL SERVER (32BIT) automatically grab the extra emenory available or
2. Do we have to force SQL Server to via AWE to make it grab the extra memory available

I guess from the responses we have only option 2 available - is this correct

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-25 : 08:50:30

Did you read carefully all posts I have posted?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources