New SQL Server 2005 instance, really slow compared to 2000

Question

New SQL Server 2005 instance, really slow compared to 2000

John Burchel

Hall of Fame

Points: 3611
More actions
October 9, 2006 at 7:25 am

#114688

Hi All

I have a new server configured to replace my old 2000 box, it’s has 4x Xenon cpu sitting on a SAN box, using sql 2005 standard.

We have been testing normal processes on the server and found it to be really slow. eg BCP !

I am not sure if there is anything I can do to sort the problem out, I started Performance monitoring, but its results don’t really give you a pointer as to exactly what the problem is.

Does anyone know how I can go about solving this problem ?

Thanks
Kindest Regards,
John Burchel (Trainee Developer)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ariadne SSCrazy Points: 2044 More actions · Answer 1

John,

First thing I'd do is check out the O/S. Are you running 2000 or 2003? and I would start investigating the storage formatting differences between the new SAN and the old storage structure. What you're looking for is through put. It's always a good idea to measure your baseline throughput before changing to a new disk subsystem. If the new system's throughput is slower than the old, then it's probably not the SQL Server version upgrade.

Another item I would check is what compatibility mode is your 2005 instance?

Johan Bijnens SSC Guru Points: 135445 More actions · Answer 2

- I'd start with a stop/start of sqlserver (to clear the performancerelated dmv's)

- then I'd do the standard maintenance !

dbcc dbreindex / sp_updatestats / dbcc updateusage ('yourdb')

- Then I'd start perfmon to see what IO / Memory / CPU is consumed and see if there bottlenecks overthere.

- then I'd use the dmv's to pick out the most consuming queries,...

( have a look at ... e.g.

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/default.mspx?mfr=true

)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

beath SSCrazy Points: 2939 More actions · Answer 3

I'm finding 2005 really really slow as well. And I'm not talking about running fancy queries or anything. I mean opening up management studio on the server - 20 or 30 seconds. Parsing some code - 20 or 30 seconds. Creating a database maintenance plan - up to a minute to move through the screens. It's almost unworkable and this is on all 5 of our instances and each of these is running on a big chunky box.

Makes me wonder if the 2005 out of the box install needs to be tweaked more than the 200 one needed?

John Burchel Hall of Fame Points: 3611 More actions · Answer 4

Hi Guys,

Thanks for the reply, its just that normal day set up processes like BCP or even creating filegroups through TSQL takes ages.

Any advice/solution to this problem ?

Thanks

Kindest Regards,

John Burchel (Trainee Developer)

Johan Bijnens SSC Guru Points: 135445 More actions · Answer 5

Indeed the GUI stuff is very slow.

IMO the datapart is greath.

regading you filegroup-slowness ... I didn't encounter this.

Is it with the creation of the filegroup, or of the files within a filegroup ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

John Burchel Hall of Fame Points: 3611 More actions · Answer 6

When adding files to the actual filegroup, this is the bit where its really slow...

Kindest Regards,

John Burchel (Trainee Developer)

Johan Bijnens SSC Guru Points: 135445 More actions · Answer 7

so this may be I/O related.

keep in mind that files are being initialized (=nulled out) at allocation time (unless you specify not to).

What filesize are you trying to allocate ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

John Burchel Hall of Fame Points: 3611 More actions · Answer 8

Thanks for the reply, the file size we are talking of here is around 200GB at one time.

Thanks

Kindest Regards,

John Burchel (Trainee Developer)

Johan Bijnens SSC Guru Points: 135445 More actions · Answer 9

- how long is it taking the system to allocate 200GB ?

- did you try to copy a 200Gb file to that logical disk ? And how long did that take ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

New SQL Server 2005 instance, really slow compared to 2000

Cookies on SQLServerCentral