| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-10-31 : 18:59:07
|
| We installed a new server recently and found the performance to be slower then a server, configured identically, earlier in the year. The only difference is the new server has SQL Server 2000 EE and the old has 2000 SE. The new servers processors are slightly faster and the memory is identical (4GB). The new server has been running in a test environment for nearly four weeks. How much time does SQL Server typically need to self-tune the server? I assumed a few days of steady use should have been sufficient. Would you agree?If so, I suspect there is something different within the application. The only other possibility is SQL Standard Edition vs. Enterprise Edition, but I've never read anything about EE being slower then SE.Thanks, DaveDave |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-31 : 19:38:33
|
i don't think this is a server problem...probably a db problem like indexing or something like that...are you running sp4?Go with the flow & have fun! Else fight the flow  |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-31 : 20:10:27
|
| Running SP3a on both servers. The indexes are suppose to be identical as are all of the objects, however I am in the process of comparing the object scripts for any differences. How long does it take typically for SQL Server to self-tune?Dave |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-31 : 20:21:36
|
| Except for auto-update and -create of statistics, I'm not aware of any "self-tuning" feature of SQL Server. Even these feature will not automatically defragment data or indexes, which can become a significant performance problem if your database has a lot of write activity.You didn't mention anything about the disk system, is it the same one or different? You have 4 GB of RAM on both systems, is SQL Server using all of it? Is it the exact same type of RAM? Is the network configuration on both servers the same? There are a number of subtle things that could change the performance, and there's no self-tuning available to fix them. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 20:38:24
|
| Have you reindexed yet? If not, do so. Also, what is the difference in processors? Can you post the specs for both? Was one hyper-threaded and the other not? Have you ran Profiler to see what's running slower?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-31 : 20:45:53
|
| 2GB memory to OS and 2GB to SQL Server.RAID configurations were identical, except I just moved tempdb off the data partition on the slow server to see if this would help.Both servers have 4 processors, with the slower performing server having the faster processors.Disk speed is 15k rpm on both servers.The controllers are different. The faster performing server has two external Smart Array 6400 controllers with 192MB cache each. The slower server has one internal and one external controller. The internal controller has only 64MB of cache and is home for the C drive and SQL log files. However, perfmon indicates no issue with the C or log drives on either server. Also, when I say "slower server", the application is running slower, however the processors are slightly faster. The controller cache Read/Write configuration is different for the data drives. The faster server is 75% Read / 25% Write and the slower server is 50% Read / 50% Write. I just found this a few days ago and will be changing it tomorrow. I'm still not sure what configuration to use. I've read people suggest 100% Read / 0% Write and other suggest just the opposite. I'll try both.Both servers are Windows Server 2003 SE, however the faster performing server does not have SP1 and the slower server does have SP1. I'll be applying SP1 tomorrow to see if this slows down the server. I doubt it will make much of a difference.Dave |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-31 : 20:50:57
|
| Reindexing runs nightly on both servers.Perfmon counters for memory, processor, physical disk and page file usage against both servers show little difference, with some counters favoring the slower performing server with slightly better hardware. I see a lot of table scans on both servers. Have you ever heard of SQL EE being slower then SQL SE? I'm starting to think something is different with the application, however I'm having a difficult time proving this.Dave |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 21:01:47
|
| It could be. Are the newer processers hyper-threaded. Were the older ones hyper-threaded?Can you identify what is running slow with Profiler? If so, you can look at the individual execution plans and tune accordingly. You might also be having select parallelism issues if you can actually ID what's running slower. Sometimes, the execution plans will take this into account on a slightly faster processor due to the estimated cost difference.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-31 : 21:05:38
|
| One thing to check is that auto-update statistics is turned on on both servers (oh yeah, it's easier than you think for this to get turned off) Just for laughs, run sp_updatestats on both anyway. The more you can be sure that both databases are as similar as possible, the fewer variables can affect your investigation.One other thing you might want to check on the disks is cluster size. If they differ, that can have an impact as well.Also, how much slower is the slower server? 10%? 20? Is this a web application, or does it connect to the servers via a proxy? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-10-31 : 21:26:06
|
| Also, can you describe the disk setup on the two systems?Be sure to include How many disks, of what size and speed, in what type of RAID array, holding what data.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-10-31 : 22:02:47
|
| Hyperthreading is turned off on both servers. I originally turned it on for the newer server, but was unable to get the processors to ballance the load. I've run profiler and looked for excessive compiles and long durations, but found neither to be true. It's possible I need to look at different profiler events.Disk queueing spikes fairly high for the data drive, but overall time is good.Auto-update Stats is turned on for the databases on both servers.The disks on the newer server are lager, 72GB each for data and backups vs. 36GB each on the faster performing server. Data is RAID5. Although I prefer RAID10, the perfmon results look good. Logs are RAID 1+0, however since only two drives are involved with the RAID, that equates to RAID1. RAID configurations are the same on both servers.64KB stripe size for RAID4 and 128KB strip size for RAID1.Thanks, Dave |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 22:18:00
|
| Which queue spikes high, the read or write? What do they spike to? How many physical disks?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-01 : 09:19:16
|
| The Disk Read Queue counter spikes to 5.476. The Disk Write Queue counter spikes to 1.966. There are three physical disks on both servers. The slower server has three 72GB (15k rpm) drives in a RAID5 and the faster performing server has three 36GB (15k rpm) drives in a RAID5. The average read and write queue values are well below .5.Dave |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-01 : 11:09:14
|
Wouldn't say you have much of an issue there then.  That's not what I would consider a bad spike.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-11-01 : 13:21:23
|
| Self tuning. I think that's a Microsoft Access feature. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-01 : 14:25:22
|
| BOL and some other MS documentation spends a little time on the self-tuning features, but nothing in great detail that I could find.Here's an update on our performance problems. Microsoft wanted me to run another perfmon trace capturing all counters for 9 different objects and set the interval to 2 seconds. I questioned in an email if this would create a lot of overhead, but received no response. I created the trace and we ran our application tests only to see performance get worse. I then turned off perfmon and we ran more tests, which showed performance increase substantially. This got me thinking that our previous perfmon tests, using an interval of 5 seconds, was also creating a large amount of overhead on the server. We ran the same set of tests today on both servers, minus perfmon, and the results were acceptable. I never used anything less then 15 seconds in the past for monitoring a SQL Server and will be sticking to this rule in the future.Dave |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2005-11-03 : 14:31:09
|
| DBADave,Yep, every 2 secs probably will degrade the server. You might try 15 second intervals.1 more thing to check - on the server that is slower, are any of the databases set to 'Auto Shrink' ? (in Options). When a new database is created, I think the default is to NOT Auto Shrink, which is generally what you want. If Auto Shrink turned ON, the server spends most of its time shrinking/expanding, then shrinking...It can be a performance killer. Not likely in your case but worth checking.Good luck with finding the cause. John |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-03 : 14:39:05
|
| It's funny you mention that about Auto-Shrink. A new database was installed for a software package yesterday and the Auto-shrink option was enabled. Since I now know the performance problems were in a sense "false-possitives" due to perfmon, Auto-shrink could not have been the issue. However that doesn't mean it hasn't slowed performance on both servers. I was going to search the forums for auto-shrink conversations and possibly post a question about its dangers.Thanks, Dave |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-03 : 14:59:28
|
| Why don't you setup a monitoring box and have it collect perfmon stats for all your database servers. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-03 : 15:39:34
|
| That's on my agenda. I was recently provided a new PC with 1GB ram and will be installing Windows Server 2003 SE and SQL Server 2000 EE. The server will be used as my DBA playground and for performance monitoring and possibly a central point for alert management, although I'm not certain I want to centralize the alerts just yet.I'll probably store the perfmon results to a file and then BCP the data to a table. As time permits I'll have a nightly job check the results and send me a report if problems are detected. Of course the "as time permits" part will probably cause me some problems.Dave |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-03 : 21:51:16
|
quote:
although I'm not certain I want to centralize the alerts just yet.
Of course you do. You want to do this. You want to do this. You do this. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Next Page
|
How long for SQL Server to self-tune?