SQL Server 2000 SP3a Performance Issue

[apc2004]

Hi everyone,

I have been asked to post a question by my company's Database Administrator.

We have a database which is showing some performance problems. We took a full backup and restored it under a different name on the same server as the first database.

The newely restored database does not seem to have the performace problem the first has.

I have performed fragmentation checks and other such queries on the Database for the Database Administrator here and can't see a reason for the performance issue to no longer be visible in the restored copy of the original database.

Does anyone know or can offer some explanation as to why this issue seems to have disappeared in the restored copy of our database?

Thanks in advance...

 

[Crowley16]

have you tried running the same performance checks with no-one else logged onto the database? Could be locking issues...

--------------------
Procrastinate Now!

 

[apc2004]

Yeh that was one of my first thoughts that while the database was being used during an operation locks may have been accuring slowing thing down while other requests waited for tables to be released and available.

Is very odd... my DBA came in a few mins ago and apparently the first database is now working at correct speed but the restored backup is now running slow...

Am not sure what's going on, don't think it's a programmatical problem, seems to me like its a SQL Server setup / maintenance issue.

 

[Crowley16]

what's the memory usage on the server?

maybe you're running out of ram...

what about the hard drive, maybe a defrag or is it running out of space?

--------------------
Procrastinate Now!

 

[apc2004]

• Server RAM (4gb)
• Hard Drive / Page File space - ok
• SQL Server Memory usage is set to 1.2gb
• Unsure on last HDD Defrag... will ask DBA to check HDD Fragmentation to clarify.

When the database was slowing Task Manager on the server was showing SQL Server using 30% CPU... when we checked for connections to the server there was only the normal system ones and the connections from the .net client that was performing the operation on the database which was running very slow.

We checked all maintanence plans etc for activity and nothing.

Normally the task performed by the .net client runs start to finish in 5min-10min, with current slow performance of unknown cause it is taking 40min-60min.

Thanks...

 

[onpnt]

.net client runs on the server? Are you sure you don't have a memory leak issue?

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[apc2004]

The .net client runs from terminal PC(s) in other offices...

The slow performance has only started occuring a couple of days ago, there haven't been any updates to the .net client made for several weeks so it has remained unchanged since the time the server was running at the correct speed until now with the server running slow.

Am starting to wonder if a Microsoft Update has been applied in the last day or two to either Microsoft Windows 2000 or SQL Server 2000... will ask the DBA to check into it.

 

[Crowley16]

try giving sql server more memory and see if that helps, usually sql server would be setup to take all available memory...

--------------------
Procrastinate Now!

 

[apc2004]

When the problem first started SQL Server was set to use 2gb of memory... for some reason the DBA lowered it to 1.2gb... the problem seems to come and go, sometimes it runs really slow other times it is perfectly normal which would say to me something else is running on the database at the times of the slow down but everytime it occurs I can't find a single process that could be causing it which now leads me to believe it might be network related in some way, if that is possible?

 

[apc2004]

Sorry, it seems I have been mis-informed...

The server in question which SQL Server is running on has 2gb RAM not the 4gb as started previously...

The SQL Server memory usage was when the problem started set to 1.5gb and is now set to 1.2gb.

Problem still exists, coming and going at a random occurence.