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
>
>

my SQL Server is too slow

Hi, All
recently, after I restored production DB (SQL7.0 +sp3)
backup file to my develope DB, my computer (develope DB --
desktop version)is too slow when I access develope DB. It
was fast before and it is slow right now. I didn't change
anything to my computer or server. The SQL server is still
fast, only my computer run the desktop develope DB is
slow. my pc is p4 2.4Ghz and 256 RM 120GB hard drive. I
checked my pc using Norton AntiVirus and non virus found.
Any ideas or suggestions?
Thanks and have a good one
HawkMost likely the 120GB Hard Drive has absolutely no comparison of performance
against the server. Server could have RAID or high cache disk drives. Also,
that may be a small amount of RAM for background services running locally to
your workstation.
Christopher Winn
Business Intelligence Engineer
Edugration, Corp.
"hawk" <abchawk@.hotmail.com> wrote in message
news:11f4201c410ed$bb3b8470$a401280a@.phx
.gbl...
> Hi, All
> recently, after I restored production DB (SQL7.0 +sp3)
> backup file to my develope DB, my computer (develope DB --
> desktop version)is too slow when I access develope DB. It
> was fast before and it is slow right now. I didn't change
> anything to my computer or server. The SQL server is still
> fast, only my computer run the desktop develope DB is
> slow. my pc is p4 2.4Ghz and 256 RM 120GB hard drive. I
> checked my pc using Norton AntiVirus and non virus found.
> Any ideas or suggestions?
> Thanks and have a good one
> Hawk|||To quantify what Chris Winn said, you should run Performance Monitor and
check % Processor Time (Processor), % Disc Time (PhysicalDisc), and %
Committed Bytes In Use (Memory) while you're running your queries. I think
what you'll see is % Disc Time very close to 100, % Processor Time very low,
and % Committed Bytes In Use very high.
"hawk" <abchawk@.hotmail.com> wrote in message
news:11f4201c410ed$bb3b8470$a401280a@.phx
.gbl...
> Hi, All
> recently, after I restored production DB (SQL7.0 +sp3)
> backup file to my develope DB, my computer (develope DB --
> desktop version)is too slow when I access develope DB. It
> was fast before and it is slow right now. I didn't change
> anything to my computer or server. The SQL server is still
> fast, only my computer run the desktop develope DB is
> slow. my pc is p4 2.4Ghz and 256 RM 120GB hard drive. I
> checked my pc using Norton AntiVirus and non virus found.
> Any ideas or suggestions?
> Thanks and have a good one
> Hawk|||Hi, Winn,
I;m sorry. What I want to say is my desktop version SQL
7.0 server is slow. It was fast. right now, it was slow
even i click any item from EM, of couse, the query is slow
too. My desltop version SQL server is develope DB and only
me can access it, none other uses. I try t0 rebuild the
index and to see what happen
my production DB on the win2000 server is still fast. not
problem at all
thanks!
hawk

>--Original Message--
>Most likely the 120GB Hard Drive has absolutely no
comparison of performance
>against the server. Server could have RAID or high cache
disk drives. Also,
>that may be a small amount of RAM for background services
running locally to
>your workstation.
>Christopher Winn
>Business Intelligence Engineer
>Edugration, Corp.
>|||As for the slowness, see if the autoclose database option is on for the
databases. Also, see if ODBC tracing is turned on at the client.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hawk" <anonymous@.discussions.microsoft.com> wrote in message
news:d76b01c410f8$8e6b5870$a001280a@.phx.gbl...
> Hi, Winn,
> I;m sorry. What I want to say is my desktop version SQL
> 7.0 server is slow. It was fast. right now, it was slow
> even i click any item from EM, of couse, the query is slow
> too. My desltop version SQL server is develope DB and only
> me can access it, none other uses. I try t0 rebuild the
> index and to see what happen
> my production DB on the win2000 server is still fast. not
> problem at all
>
> thanks!
> hawk
>
>
> comparison of performance
> disk drives. Also,
> running locally to
>|||thanks Tibor,
autoclose database is on and i will set it to off.
but how can I know ODBC tracing is turned on at client?
How can I check it ? Could you tell me about it?
Thnaks and have a good day
hawk
>--Original Message--
>As for the slowness, see if the autoclose database option
is on for the
>databases. Also, see if ODBC tracing is turned on at the
client.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>|||You find the ODBC applet in the Administrative Tools program group. Here you
can see if it is running and if so, turn it off.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"hawk" <anonymous@.discussions.microsoft.com> wrote in message
news:f32e01c410ff$18243ef0$a601280a@.phx.gbl...
> thanks Tibor,
> autoclose database is on and i will set it to off.
> but how can I know ODBC tracing is turned on at client?
> How can I check it ? Could you tell me about it?
> Thnaks and have a good day
> hawk
> is on for the
> client.
>

My Sql Express is so slow?

Has anyone else noticed delays with SQL Express? I'm not really talking about delays on the queries but just delays in general response. For example: everything is running great, then for about 2 minutes I get connection timeouts etc can't even open stuff in the management studio without getting timeouts ... then as strangely as it started everything goes back to normal and requests are served again.

The server has nothing on except 1 website, its Win 2003 Server. 512MB Ram on a PIV. The memory usage is low and during the "lockups" the machine isn't showing any processor usage and SQL mem usage is around 40Megs.

I am not using User Instances either. Nothing in the event logs. What is odd, is its happening on 3 of my machines .... all with different sites, the only thing in common between them is SQL.

thanks,

-c

Are you closing and destroying your connection objects?

If you dont close and set them to nothing they will sit in the application pool taking up resources.

Just a though