CXPACKET & LATCH_EX

Dear all,
For some unknown reason, I am keep getting these 2 wait type on my SQL
server and it is considerably slowing down the whole server.
Is there a way to to get rid of it or any reference resources to
troubleshoot with these 2 wait type? Can it be possibily got anything to do
with my Application it self or the SQL server?
Thanx in advanced
Leethis is not doc'd very well. Tom Davidson from MS has a great white paper on
MSDN. I'm not sure if it was published yet? Search MSDN for his name...
also... we write a summary of it for SQL Server Magazine.
CX_PACKET deals with parallel query synchronization while LATCH_EX deals
with a a type of internal latching mechanism. Tom's paper talks about them
nicely...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> For some unknown reason, I am keep getting these 2 wait type on my SQL
> server and it is considerably slowing down the whole server.
> Is there a way to to get rid of it or any reference resources to
> troubleshoot with these 2 wait type? Can it be possibily got anything to
do
> with my Application it self or the SQL server?
> Thanx in advanced
> Lee
>
>|||Hi Brian,
Is it the below document?
http://sqldev.net/misc/WaitTypes.htm
Regards
Thirumal
>--Original Message--
>this is not doc'd very well. Tom Davidson from MS has a
great white paper on
>MSDN. I'm not sure if it was published yet? Search MSDN
for his name...
>also... we write a summary of it for SQL Server Magazine.
>CX_PACKET deals with parallel query synchronization while
LATCH_EX deals
>with a a type of internal latching mechanism. Tom's paper
talks about them
>nicely...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
>news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
wait type on my SQL
server.
resources to
possibily got anything to
>do
>
>.
>|||"Learn Yee" <learnyee@.freightmark.com.my> wrote in message
news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
> For some unknown reason, I am keep getting these 2 wait type on my SQL
> server and it is considerably slowing down the whole server.
We had a problem with lots of LATCH_EX locks on our SQL server and the
problem was caused by badly written ASP code.
The ASP code was opening up multiple connections which one would have done.
The problem wasn't to do with the number of connections but the frequency
that it was opening and closing them.
Instead of opening one connection and using that throughout the ASP code, it
would open a connection, run one query and close it and that was then run
within a loop which compounded the issue.
Once the ASP code had been modified to only use one connection, all the
LATCH_EX locks dissapeared|||As Brian says CX_PACKET wait is used as a synchronization mechanism for PQ.
What seems to happen is that one EC (execution context) waits 'behind' this
wait, while 2 other EC's (on my 2 way server) are doing (or should do, see
below) the work.
By extensive tracing on my test server(with tools available on my website) I
found that LATCH_EX is waited for by one EC while the other EC is doing(and
waiting for I/O). This looks like a bug, since the whole idea of PQ is of
course to read and process in parallel..
Btw I told a widely respected MS Server engineer about this a week ago.
Instead of taking this seriously, he questioned my tools..
regards,
Mario
http://www.sqlinternals.com
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:uXnBeivCEHA.2256@.TK2MSFTNGP12.phx.gbl...
> this is not doc'd very well. Tom Davidson from MS has a great white paper
on
> MSDN. I'm not sure if it was published yet? Search MSDN for his name...
> also... we write a summary of it for SQL Server Magazine.
> CX_PACKET deals with parallel query synchronization while LATCH_EX deals
> with a a type of internal latching mechanism. Tom's paper talks about them
> nicely...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Learn Yee" <learnyee@.freightmark.com.my> wrote in message
> news:OEtsdNvCEHA.1236@.TK2MSFTNGP11.phx.gbl...
SQL
> do
>