SQL 2000 performance issues? - 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 2000 Forums

SQL Server Administration (2000)

SQL 2000 performance issues?

Author

Topic

mudcow007
Starting Member

6 Posts

Posted - 2010-06-15 : 09:14:39

hi guys

we are currently still using Server 2000 aswell as SQL 2000, with plans to move to server 2008/ SQL 2008 later this year

however, im having serious performance issues with SQL at the moment

the server is the PDC for the domain, DNS aswell as the SQL for the company, we have about 90 users.

the spec of the server is 8gb Ram (but only 3.5 will be seen due to 2000 limitations), Dual 3.2 Xeon Procs

when i have ran performance monitors i used

Target server memory (KB) average is @ 82.45 an maximum is @ 472.015!!!!

Batch Server requests - these readings are - average @ 890.230 and max is @ 1160.995!!

the memory is configured as dynamic

do those readings sound ok to you?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 12:28:36

What's the buffer cache hit ratio and page life expectancy show?

What performance issues are you experiencing? Does SQL Profiler show long-running queries? Have you checked blocking? How often do you update the statistics?

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

Subscribe to my blog

mudcow007
Starting Member

6 Posts

Posted - 2010-06-16 : 04:25:44

Hi thanks for the reply

the SQL database serve out Microsoft Dynamics/ Axapta application, which users are complaining about locking up and "freezing"

ok i have checked -
Buffer Cache hit ratio: average 99.612 Min 99.612 highest 99.612

Batch Requests: average 62.170 Min 0 Max 404.008

Transactions: average 3.921 Min 0 Max 42.0

Lock Blocks: average 3532.00 Min 3532.00 Max 3532.00

refresh is set to 1sec

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-06-16 : 10:35:33

"The spec of the server is 8gb Ram (but only 3.5 will be seen due to 2000 limitations)"

-- Why?? Can you give server specifications?

mudcow007
Starting Member

6 Posts

Posted - 2010-06-16 : 10:51:23

its running on Server 2000

is it was running on Advanced i could have used the boot.ini to increase the amount of RAM to the o/s

mudcow007
Starting Member

6 Posts

Posted - 2010-06-21 : 06:00:14

Quick Update

Update

Hello all im still getting loads fo complaints regarding our SQL server, i have run the following on performance monitor an results are below

Memory: Page/ Sec
- Average = 0.263 Max = 18.00

Memory: Available Bytes
- Average = 1013.280 Max = 1014.000

Physical Disk: % Disk Time
- Average = 12.351 Max = 27.533

Physical Disk: Avg. Disk Queue Length
- Average = 0.242 Max = 1.043

Processor: % Processor Time
- Average = 1.851 Max = 54.297

System: Processor Queue Length
- Average = 0.030 Max = 2.000

SQL Server Buffer: Buffer Cache Hit Ratio
- Average = 99.341 Max = 99.343

do these figures seem normal?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-21 : 12:27:02

You don't seem to have a hardware bottleneck, so we're going to need to look at SQL. Can you run SQL Profiler and check for long-running queries? Once you identify the queries, check if there are missing indexes. How often are you updating statistics?

Could you describe what the symptoms of your performance problem is?

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

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-06-21 : 13:48:21

"How often are you updating statistics?"

and I would add: "and how often are you rebuilding indexes?"

mudcow007
Starting Member

6 Posts

Posted - 2010-06-22 : 03:56:06

quote:
Originally posted by tkizer

You don't seem to have a hardware bottleneck, so we're going to need to look at SQL. Can you run SQL Profiler and check for long-running queries? Once you identify the queries, check if there are missing indexes. How often are you updating statistics?

Could you describe what the symptoms of your performance problem is?

Hi there, SQL is linked into our sales software (Microsoft Dynamixs) which our sales staff are increasingly complaining about, saying the app keeps stopping and "freezing" after a few mins, the app works normal. this happens randomly throughout the day

i will take a look at profiler. should i just run profiler with the default options?

the performance statistics were being updated every 1 second.

many thanks

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 04:45:56

"the performance statistics were being updated every 1 second."

That doesn't sound right - might be the answer to a different question?!

"the app keeps stopping and "freezing""

From the way you describe it I doubt this is the case, but worth fixing anyway if it is set wrongly:

On SQL2000 Database expansion can cause very significant slow-down on SQL 2000. This is particularly noticeable when automatic database growth is set to percentage, rather than (reasonable) fixed MB size.

I would not expect this to be a problem often, but when the database is extended it could be a problem for a prolonged period of time (i.e. many minutes)

mudcow007
Starting Member

6 Posts

Posted - 2010-06-22 : 07:20:04

oh sorry i meant using the performance monitor on server 2000, the refresh rate was set to 1 sec

is there an update statistics in SQL too?

sorry im lame when it comes to SQL

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 09:41:27

You can use

UPDATE STATISTICS

on specific table's or view's index(es)

or

sp_updatestats

to update all of them.

See SQL Documentation ("Books Online") for more info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources