Reduce SQLServer Wait Time

Hi,
I ran waittype procedure to for 10 mins to check the sqlserver waittype and
time and got the following result
wait type
wait time percentage
-- -- --
***total***
7086.0 100.0
CXPACKET
3254.0 45.9
LATCH_EX
3034.0 42.8
..
...
I just want to reduce the above wait types,any idea. Currently the max
degree of parallelism is 0 .
SQL BOX has 2GB Memory and SQLServer is configured to use 1032MB fixed.
Env:
SQL2000 + SP3 /Windows 2000 Advance Server 2 Processors with Hyper-Threading
Technology.
thanks
Latch_EX wait times usually indicate you are having a lot of memory pressure
and sql server has to spend a lot of time swapping data or procedure pages
in and out of memory. The cxpacket waits are most likely due to the fact
that your procs are too busy doing this and can't keep up with each other.
My guess is that the HT is getting in the way some. Bottom line is you can
probably use more ram but here are as few things to try and see if they
help. One is to turn off HT. Another is to change the MAXDOP at the server
level. Try 2 first and then 1 if that doesn't help. Why do you only have
1GB allocated to SQL Server if you have 2GB total? Try bumping it up a few
hundred MB and see if that helps some. How much procedure cache do you
have? Do you have lots of recompiles or plans that are not reused? If so
then work on plan reuse.
Andrew J. Kelly SQL MVP
"kris" <kpxus@.yahoo.com> wrote in message
news:OqCgT0obEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I ran waittype procedure to for 10 mins to check the sqlserver waittype
and
> time and got the following result
> wait type
> wait time percentage
> ----
--
> -- -- --
> ***total***
> 7086.0 100.0
> CXPACKET
> 3254.0 45.9
> LATCH_EX
> 3034.0 42.8
> .
> ..
> I just want to reduce the above wait types,any idea. Currently the max
> degree of parallelism is 0 .
> SQL BOX has 2GB Memory and SQLServer is configured to use 1032MB fixed.
> Env:
> SQL2000 + SP3 /Windows 2000 Advance Server 2 Processors with
Hyper-Threading
> Technology.
> thanks
>
|||Andrew,
thanks for your suggestions, actually another application is running on the
sqlbox which is taking 150MB. I will change the settings as per your
suggestion and again i will check that.
thanks
kris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lys46obEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Latch_EX wait times usually indicate you are having a lot of memory
pressure
> and sql server has to spend a lot of time swapping data or procedure pages
> in and out of memory. The cxpacket waits are most likely due to the fact
> that your procs are too busy doing this and can't keep up with each other.
> My guess is that the HT is getting in the way some. Bottom line is you
can
> probably use more ram but here are as few things to try and see if they
> help. One is to turn off HT. Another is to change the MAXDOP at the
server
> level. Try 2 first and then 1 if that doesn't help. Why do you only have
> 1GB allocated to SQL Server if you have 2GB total? Try bumping it up a
few
> hundred MB and see if that helps some. How much procedure cache do you
> have? Do you have lots of recompiles or plans that are not reused? If so
> then work on plan reuse.
> --
> Andrew J. Kelly SQL MVP
>
> "kris" <kpxus@.yahoo.com> wrote in message
> news:OqCgT0obEHA.1248@.TK2MSFTNGP11.phx.gbl...
> and
> ----
> --
> Hyper-Threading
>
|||Other apps on the same box compete for the same resources and will add
complexity to what is the root cause. That can increase the risk of HT
being an issue as well. Another thing I forgot to mention is that if your
having issues with HT you may want to consider Win2003 as it handles HT and
more much better than 2000.
Andrew J. Kelly SQL MVP
"kris" <kpxus@.yahoo.com> wrote in message
news:e71CvCqbEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> thanks for your suggestions, actually another application is running on
the[vbcol=seagreen]
> sqlbox which is taking 150MB. I will change the settings as per your
> suggestion and again i will check that.
> thanks
> kris
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23lys46obEHA.1152@.TK2MSFTNGP09.phx.gbl...
> pressure
pages[vbcol=seagreen]
fact[vbcol=seagreen]
other.[vbcol=seagreen]
> can
> server
have[vbcol=seagreen]
> few
so[vbcol=seagreen]
waittype[vbcol=seagreen]
> ----
fixed.
>