| Author |
Topic |
|
rSyn
Starting Member
5 Posts |
 Posted - 2005-01-13 : 19:49:04
|
hi,my company was move our old database at HP ML 530 to new server (HP Proliant DL740), logically with new server, i believe that the speed will increase extreamly high, but conversely, the performance still poor, application still slow down. we use attach database method to move our old database to new server not export import method, is this cause performance of server ??? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-13 : 19:50:58
|
| Did you UPDATE STATISTICS WITH FULLSCAN? Did you run DBCC DBREINDEX?Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-13 : 22:25:52
|
| Have you ran Performance Monitor or Profiler to determine what's wrong? In performance monitor, you need to look at:ProcessorBuffer Cache Hit RatioTarget Server MemoryTotal Server MemoryPhysical Disk Write Queue LengthPhysical Disk Read Queue LengthIn Profiler, look for queries that are running really long and level it down from there. Let us know what you find. In addition, try out what Tara said.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rSyn
Starting Member
5 Posts |
Posted - 2005-01-18 : 21:50:57
|
this "UPDATE STATISTICS WITH FULLSCAN" should be run ?We re-index database every night, but performance still low.Our application is web server application, in pick hours, user who to our web server more than 300 user, this is reduce performance of our database ?  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-18 : 23:34:25
|
| And did you run Perforamnce Monitor and Profiler to determine what's wrong?I'm sensing a theme here.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-20 : 01:12:52
|
| IME on a web server once the queries start to take longer than normal more connections are created - this is because if it normally takes, say, 50ms to do a query and there are 50 connections open, then if the queries start taking 100ms then there will eb 100 connectios open, this is then a death spiral because once you get to 100ms, and twice the normal connectios, you are going to make the server even slower ...Optimising the querries will oviously fix the problem. We also have a CPU monitoring task on the SQL box and once we get over a "too busy" threshold we start refusing new sessions on the web box until the activity drops again - this allows users who are already connected to finish-upKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 12:33:48
|
| Yes you should run UPDATE STATISTICS regardless of the reindexing occurring at night. They are different operations and both should be run for optimal performance.Tara |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2005-01-24 : 00:59:34
|
| Distribution statistics will be automatically updated unless indexes are created with STATISTICS_NORECOMPUTE option. Generally, UPDATE STATISTICS is needed only in case of major updates that need to be reflected in the statistics immediately.I'd go with the MeanOldDba on this, to run the profiler and perf mon. IME, more often than not, it's the bad code for poor performance and upgrading your hardware isn't really a solution.Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-24 : 01:39:40
|
| "Generally, UPDATE STATISTICS is needed only in case of major updates"That hasn't been my experience!We had total slowdown of SQL boxes for approximately 30 minutes at unpredictable times. I can only assume that this was to do with automatic generation of Statistics as we now do our own UPDATE STATISTICS and the problem has gone away.Also, we use the WITH FULLSCAN option, rather than the default, and that made performance improvements compared to the Bog Standard method."IME, more often than not, it's the bad code for poor performance and upgrading your hardware isn't really a solution"I'll definitely second that!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-24 : 12:41:22
|
quote:
Distribution statistics will be automatically updated unless indexes are created with STATISTICS_NORECOMPUTE option.
Then what's with this:"According to a SQL Server tuning expert at Microsoft (one of their best), it was suggested to me that if your SQL Server maintenance window allows for it, that you should update statistics for all tables and for all databases every night. This is because the auto-update option is less than perfect, and accurate and up-to-date statistics is so important to overall performance of SQL Server. Of course, if this is not possible because of time constraints, then you will have to perform this step less often, or even not at all, and rely on the auto-update feature. But if you do have this window, you should take advantage of it."Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-24 : 19:36:56
|
| The distribuion statistics are automatically updated. The cycle is many times not frequent enough though. Many of the DBAs on this board have been burned by this. The entire system goes South...you UPDATE STATISTICS manually, and the entire world is peaceful again. HOWEVER, having said that......RUN PROFILER AND PERFORMANCE MONITOR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!I'm sensing a theme here. Band-Aids aren't the right solution when you just got your eye shot out. Run the diagnostic tools; and figure out what's really happening.RUN PROFILER AND PERFORMANCE MONITOR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!I'm sensing a theme here.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-01-24 : 20:12:14
|
| rSyn,You need to know where your issue is before thowing solutions at it. You do this by running Profiler and Performance Monitor. I don't know if anyone has mentioned this before to you but if they have, you should listen to them.HTH=================================================================Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971) |
|
|
|
GenerationWithoutName
Starting Member
26 Posts |
Posted - 2005-01-29 : 05:34:09
|
| Reindex everyday?Just like a FoxPro...what a...?DIJE |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-29 : 10:38:47
|
| DIJE, you shouldn't have to reindex every night on a system that's even halfway tuned.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rSyn
Starting Member
5 Posts |
Posted - 2005-01-31 : 05:55:37
|
Yeah,........We running SQL Performance And Profile, we found some of our SP take about 30 - 60 second to return result, we already change those SP.We don't have SQL DBA here (do we need one ?  ), so I don't know exactly how to UPDATE STATISTIC since we do not CREATE one, if this (CREATE STATISTIC) create automatically by SQL Server ? If so, how we Update Statistic ? |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2005-01-31 : 06:15:08
|
quote:
Originally posted by rSyn(do we need one ? )
No, you don't. GOD shall take care of it. Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2005-01-31 : 06:16:06
|
| refer BOL under UPDATE STATISTICSHemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
|
|
Poor Performance