Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2011-02-08 : 05:28:33
|
| In the recent times the Server performance suddenly dropped and our environment is 64 GB RAM and 16 CPUs on cluster nodes.Then we have changed the MAXDOP set to 1 such that Parallelism on query can be chosen to single CPU. Thereafter server responded well; but again some of the databases performance dropped as the application accessing the data got too slowed down.I have checked with the existing indexes and all the indexes on the table were properly utilised.I could not find any error as why the application driven data gets slowed down.Can anyone analyse as to where could be the missing link.Many Thanks to all. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-02-08 : 07:18:13
|
| Have you checked the fragmentation of indexes? Have you checked the Statistics are up to date? Have you checked if you have anything else running on that server? Have you run a profiler trace to see what is running when it "slows down"? |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-02-08 : 20:37:29
|
| Have you checked the fragmentation of indexes? Have you checked the Statistics are up to date? Have you checked if you have anything else running on that server? Have you run a profiler trace to see what is running when it "slows down"?We have Fragment been Defragmented and percentage hovers around 30 - 50 percent which is quite acceptable.Statistics have run as a part of the House Keeping process and are up-to-date.There are several databases which are queuing up quite heavily during the business hours and is complaining that they were slow with the front-end application.Profiler trace is generated for Performance-wise; startup and end; etc for every 10 minutes and watched the queries that are pumped into that. No eividence.However, as a part of this Profiler trace we found some of the indexes which are running very low and not utlised and we have unearthed them and alternatively we have used MAXDOP set to 1 and thereafter only those CPU's were indulged in processing the query part.Still we dont have any clues with the help of continuation of Profiler Trace still goes on....Any vauluable suggestion would be appreciated and good thought for all those who are digging now and swollen with this nature in past.Thanks All. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-02-09 : 15:30:24
|
Any Clues please.... Running out of ideas |
|
|
|
|
|
|
Server Performance