Dual Xeon 3.8GHz x64 processors with SQL Server 2000 + Windows Server 2003...performance hits
-
Both the OS and the database software are 32-bit right now, but this is a Dell Poweredge 2800 with 4GB RAM, 3 72GB SCSI hard drives in a RAID-5 array.
Here's the problem. We have an existing system with a 4GB database that runs a certain program, searching in 10 seconds. With identical software at the front end on the new box, it searches in 15 seconds.
Our software programmers are telling me something is not set up right in SQL Server and I'm inclined to agree with them, but I don't know where to go to help performance as I am not certified as a DBA, though I would like to get my MCDBA certification.
Can someone help me figure this one out? I am in BIOS setup enabling Hyperthreading to see if it will help.
While I'm here, I did notice there is an option to optimize for Sequential or Random memory access. Right now it's on sequential. Should it be Random instead?
Your help is greatly appreciated.
-
Rather than messing with the hardware, you need to extract the query and its query plan, either with profiler or QA and examine the plan to see exactly what is happening.
Unless you had made changes to the default hardware and sql server before your upgrade I'd suggest you start with out of the box settings.
No dount there will be postings to tell you that Hyperthreading is the root of all evil - it's worth noting that in w2k there could be problems but these were fixed in w2003. I've used HT in all prod boxes since HT arrived and have found vastly improved performance.( I use 8 proc boxes = 16 virtual procs with no major issues )
From a hardware point of view the cache setting and memory on the controller card could produce your difference, however, out of date stats, poor indexing, fragmentation, changed data sets, fill factors and so on could do equally as much.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
use profiler to intercept the activity and analyse !
especialy look for hints in statements. You could use a trace-flag for the system to disregard hints and see what th effect is like !
8602
Disable index hints
8722
Disable all other types of hints (all but index/lock)
8755
Disable locking hints
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
-
Well lets look at the processess running. It could have antivirus reading the database tables or sys vol share if its a DC. I would contend more information about the setup of the box is neccessary. I'm not especially fond of the OS sitting on the same raid as the SQL SERVER.
-
The OS is on C: and the RAID is D:, so it really isn't on the same RAID, I don't think (could be wrong). The server is a member server, not a DC.
-
what was the old box?
-
The old box was a dual 1.3GHz Pentium IV running 2000 Advanced Server with SQL Server 2000. Three 36GB drives in a RAID-5 array with 4GB RAM.
-
Have the statistics been updated & stored procedures recompiled?
-
I updated the statistics yesterday, but have not done any kind of massive recompile on the stored procedures (some of them I created, some our vendors created).
-
I have noticed on my win2003 server that sql server is a little slower than win2k, could be that the dtc is native for 2003 and a part of SQL Server 2k for win2k. Is it possible they are different versions?
And the code used for the third party apps might not be compiled for the xeon processor is another possiblilty should they be old c++ stuff.
-
The software is Java-based.
-
You've run sp_updatestats.
Did you try to dbcc dbreindex('Yourdb') with count_cows ?
Be carefull, this will invoke heavy locking, so plan it to run in "easy" times.
An extra sp_updatestats after this surely won't hurt !
and then clear the proccache, so all sp's will recompile.
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
-
I actually didn't know you could run dbreindex on the whole database. What is this count_cows parameter? And how do I clear the proccache?
-
count_rows : my mistake.
I mixed up DBCC UPDATEUSAGE ('yourdb') with count_rows. which should be done after the reindex as well !To clear the proccache you could use : DBCC freeproccache
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
-
SQL Server Books online says nothing about reindexing the whole database. Is there a command for doing that without having to pick each individual table?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply