SQL Server Performance Forum – Threads Archive
New Server – SQL Knowledge – Low
Hi all, Probably a very basic question but her goes anyway. We recently moved a SQL database form an old Dell PIII single processor server with not a lot of memory or enough disk space left for more then 1 nights backup to a new Dell Power Edge 1850 with Two 2.8 Gbyte Dual Core Processors with 4 Gig Singel bank memory and 136 Gig Disk space. The Database is around 2 gig The performance we are getting is worse than the old server, some functions are showing the good old hourglass as a regular occurence. We have run the client on the server, so eliminating network communication as the cause?, with no improvement. I am fairly new to SQL server management, I have done the MS course but it did not prepare me for this, can anyone tell me where to look for and answer? All suggestions more then welcome. Thanx AndyFMD IT Manager
University of Reading
Start by looking at the performance monitor: %CPU, avg disk queue length, commited bytes, pages/sec, network utilization. This first step should indicate if and where you have a hardware bottleneck. Maybe you could post the results? Early morning, when I wake up,
I look like Kiss but without the makeup –Robbie Williams, Strong
I’ve just put some counters on. I am now totally confused! We have two dual core processors but the monitor sees 8 instances surely it should be 4? So this may be pointing to a config error at server level? Andy
FMD IT Manager
University of Reading
your new system has 2 processor sockets, each of which are dual core
for a total of 4 cores
each in based on the Pentium 4 (Xeon version), which supports Hyper-Threading
that is why perf counters show 8 if this system is slower than the old Pentium III, your problem is probably related to parallel execution plans
set the Max Degree of Parallelism to 1
Just another thought! When we installed SQL it asked us for teh number of processors, 2 dual core we entered 4 – was this correct? Sorry we were a bit hit and miss on this and the house which supplies the database is of very little help. Andy
FMD IT Manager
University of Reading
Look at the processor tab on the properties of your server in Enterprise Manager. If all available processors are selected, this is OK. Otherwise, select all & reboot SQL (if possible). Early morning, when I wake up,
I look like Kiss but without the makeup –Robbie Williams, Strong
That last one looks OK! However I did notice that Boost SQL Server Priority on Wondows was not checked, as this is teh only app on the server it would seem sensible to do that Yes? No? The worrying facto ar present is that there are only 10 users – very soon this will increase to around 40!
Andy
FMD IT Manager
University of Reading
Don’t boost anything. Like joechang suggested, see what happens if you set the Max Degree of Parallelism to 1. This is a known bug where too many CPUs are used for simple queries. Was supposed to have been fixed in a 2000 SP, but apparently still exists in 2005. If you can trace it to specific queries, then you can add the OPTION (MAXDOP 1) clause at the end of each query, instead of the server-wide setting for Max Degree of Parallelism.
Thanx for that one there, we are still looking round and changing nothing until we are sure. My biggest problem is the potential increase in users in the not too distant future, the server room amy be nice and cool but……………
Andy
FMD IT Manager
University of Reading
We’ve seen some instances where an index would have helped, with blocking as the outside symptom. Adding the MAXDOP option after each relevant query, the problem was solved. So you might also look into the indexes available for the poor performing queries – perhaps the Index Tuning Wizard has a suggestion or two.
From all the database work I’ve done in the past – going right back to Dataease and dbII in DOS I suspect this last one will help anyway. There is a but here though, the databse itself is a third party supplied product, we can’t get through to the code and I wouldn’t even if I could. Once I have proved the server is running well I can then hit them with a big stick if we can’t get any improvement in the performance. As an aside I took over support & management of this system a month or so back and took support management on board 2 weeks ago. I’ve had over 20 support requests through on software bugs in the last week. They have been using this (with a high level of frustration) for 2 years now. Not good to say the least, I have to be very specific about what I want and what is happening as I think they are getting a little ‘sensitive’ when I email them. I had the support agreement dug out for me today, I think that says where I am at. Thanx for all the help and please keep your thoguhts coming in. Andy
FMD IT Manager
University of Reading
How did you actually move the databases to the new server? Full restore of system and user database? The standard sql server installation out of the box is good enough for the majority of databases. Was the performance issue noticable immediately or gradually? If you only restored the production database and didn’t restore any system databases, did you then create any maintenance plans which include optimisations or have you done any reindexing, updating stats etc?
Clive Strong
SQL Server DBA
Hi – long time in coming – apologies to all – we changed the Max Degree of Parallelism to 1 in the end and it worked instantly. Big thanx for all the comments and help recieved, it was good to know we were not alone on this one. Andy
FMD IT Manager
University of Reading
]]>