Mysterious and sudden performance worsening

A few days ago one of the server I use become very slow. I've a tween
server, it's pretty equal to it's brother: it's still working properly (it
runs at about 10 time faster for the same query).
I checked the hardware (raid controller, network connection and general
performances) and it seems perfect. The memory occupation and the processor
use is equal to it's tween, but the disk access has abnormally increased. To
solve the proble I've:
1 - restarted the server
2 - installed all the patches, updates and services pack I could find.
My last chance is to reistall SQL server, but I still hope to find a more
"scientific" solution.
Thanks for any suggestion.
MTA variety of things coul cause this. Just a few ideas that might be useful:
Perhaps the database has become fragmented?
Perhaps index statistics are out of date / not being updated automatically?
Have indexes been dropped?
Are there any long running transactions?
Regards,
Greg Linwood
SQL Server MVP
"MT" <cutme.mttc1685@.hotmail.com> wrote in message
news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> A few days ago one of the server I use become very slow. I've a tween
> server, it's pretty equal to it's brother: it's still working properly (it
> runs at about 10 time faster for the same query).
> I checked the hardware (raid controller, network connection and general
> performances) and it seems perfect. The memory occupation and the
processor
> use is equal to it's tween, but the disk access has abnormally increased.
To
> solve the proble I've:
> 1 - restarted the server
> 2 - installed all the patches, updates and services pack I could find.
> My last chance is to reistall SQL server, but I still hope to find a more
> "scientific" solution.
> Thanks for any suggestion.
> MT
>|||To add to Greg's post:
Check out the execution plans, se if that differs. This is what I would start with. Can for example
lead to out-of-date statistics (as per Greg's post).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MT" <cutme.mttc1685@.hotmail.com> wrote in message
news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> A few days ago one of the server I use become very slow. I've a tween
> server, it's pretty equal to it's brother: it's still working properly (it
> runs at about 10 time faster for the same query).
> I checked the hardware (raid controller, network connection and general
> performances) and it seems perfect. The memory occupation and the processor
> use is equal to it's tween, but the disk access has abnormally increased. To
> solve the proble I've:
> 1 - restarted the server
> 2 - installed all the patches, updates and services pack I could find.
> My last chance is to reistall SQL server, but I still hope to find a more
> "scientific" solution.
> Thanks for any suggestion.
> MT
>|||I have had similar proplems. Run a DBCC INDEX DEFRAG and
sp_updatestatistics. That should greatly improve the performance.
Thanks
Bryan
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eBWsv6fnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> To add to Greg's post:
> Check out the execution plans, se if that differs. This is what I would
start with. Can for example
> lead to out-of-date statistics (as per Greg's post).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "MT" <cutme.mttc1685@.hotmail.com> wrote in message
> news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> > A few days ago one of the server I use become very slow. I've a tween
> > server, it's pretty equal to it's brother: it's still working properly
(it
> > runs at about 10 time faster for the same query).
> > I checked the hardware (raid controller, network connection and general
> > performances) and it seems perfect. The memory occupation and the
processor
> > use is equal to it's tween, but the disk access has abnormally
increased. To
> > solve the proble I've:
> > 1 - restarted the server
> > 2 - installed all the patches, updates and services pack I could find.
> >
> > My last chance is to reistall SQL server, but I still hope to find a
more
> > "scientific" solution.
> > Thanks for any suggestion.
> > MT
> >
> >
>|||Problem "partially" solved: the test was made using microsoft DAO on a PC
with microsft XP with integrated security. I changed to SqlServer security
and now everything works properly. I'm afraid I will never know what caused
the sudden worsening.
If you use DAO with XP never access the database with integrated security,
as I don't believe that they will release a patch for an obsolete platform
like DAO.
"MT" <cutme.mttc1685@.hotmail.com> ha scritto nel messaggio
news:qACnb.73796$vO5.2693264@.twister1.libero.it...
> A few days ago one of the server I use become very slow. I've a tween
> server, it's pretty equal to it's brother: it's still working properly (it
> runs at about 10 time faster for the same query).
> I checked the hardware (raid controller, network connection and general
> performances) and it seems perfect. The memory occupation and the
processor
> use is equal to it's tween, but the disk access has abnormally increased.
To
> solve the proble I've:
> 1 - restarted the server
> 2 - installed all the patches, updates and services pack I could find.
> My last chance is to reistall SQL server, but I still hope to find a more
> "scientific" solution.
> Thanks for any suggestion.
> MT
>
>