Perfomance goes down fast

Perfomance goes down fast

Post by m.bohs » Thu, 13 Oct 2005 21:11:07



Hello everybody,

I'm having a perfomance issue with an application I more or less
inherited. The strange thing is that a query on the production DB takes
about twice as long as when I run it on a Db copy from last week on the
same server (12 s vs 6 s).
So far I can see that that the query and the indexes could be improved
a lot, and in fact I managed to write and alternative query which
executes in 200 ms, but the app guys keep asking me, why does
perfomance decrease so fast.
And to be honest I have no idea. The amount of data increased by about
1 %, so even with worthless indexing and poorly written queries I
wouldn't expect such an impact. PerfMonitor shows no problems and the
server (SQL2K Ent. SP3, 4 XEON 2,8Ghz, 16Gb RAM) should cope with the
load.
Any ideas what is causing this decrease ?

Tia Markus
 
 
 

Perfomance goes down fast

Post by Terr » Thu, 13 Oct 2005 23:19:37

I had a similar problem. Simple querys with a single join were taking as
long as 6 seconds on my very small database. I could not figure out where my
performance had gone in the past few months. Turns out the drive was
fragmented. After running defrag while SQL Server was stopped, the same
query runs in less than 100ms.

 
 
 

Perfomance goes down fast

Post by m.bohs » Fri, 14 Oct 2005 01:53:33

thank's Terry, seems like you're right. The drive is very fragmented so
this could be the problem. Can't defrag now, so have to wait until he
weekend to see if it solves the problem.

M