High Pagelatch and threadpool

I see high pagelatch and threadpool wait types in SQL 2005. What does that
mean ? How can I resolve it ?
Also when i see a waitresource of 7:1:145620? I understand the first is the
dbid , whats the 3rd value ?
Any help would be appreciated. This is causing heavy blocking on our
systems.
High pagelatch waits usually mean that you have some pages that are being
accessed frequently. You have some hot pages. In the case of 7:1:145620, 7 is
the database id, 1 is the primary data file, and 145620 is the page number in
the primary data file. To find what object owns this page, you can use DBCC
PAGE(dbid, file_id, page_num). See
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
for more info on using DBCC PAGE to find the object name.
Once you find the hot object(s), the next step is to find the user process,
SQL statements, SPs, and so on that access the hot object, and determine if
you can modify these SQL statements/SPs and so on to reduce contention.
I'm pretty sure that threadpool waits are related to contention on the
worker threads, but I need to double check.
Linchi
"Hassan" wrote:

> I see high pagelatch and threadpool wait types in SQL 2005. What does that
> mean ? How can I resolve it ?
> Also when i see a waitresource of 7:1:145620? I understand the first is the
> dbid , whats the 3rd value ?
> Any help would be appreciated. This is causing heavy blocking on our
> systems.
>
>
|||Yes we do have worker thread contention. Its all related to this massive
batch of many inserts against a single table and the pagelatch i believe
causes contention and hence the rest of the threads stack up and worker
threads become starved too.
As you mentioned I have some hot pages.. I'm sure I do.. Are these pages
that are being accessed in memory since its a PageLatch wait as opposed to
PageIOLatch ? Is that the only difference between the 2 wait types i.e. one
being accessed from memory vs other being accessed from disk ?
Also page 49 of Kalens Query Tuning and Optimization states the following:
PageIOLatch_ex : This wait occurs when a task is waiting on a latch for a
buffer that is NOT in an IO request.
What does that mean ? If its not IO related, then what is it ? And is a
reference to a buffer always means it in memory as in RAM or could it be
virtual memory and hence be a latch on a buffer on disk ?
Thanks
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:20BE1711-42E7-485F-84E8-3769A78AA1E5@.microsoft.com...[vbcol=seagreen]
> High pagelatch waits usually mean that you have some pages that are being
> accessed frequently. You have some hot pages. In the case of 7:1:145620, 7
> is
> the database id, 1 is the primary data file, and 145620 is the page number
> in
> the primary data file. To find what object owns this page, you can use
> DBCC
> PAGE(dbid, file_id, page_num). See
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
> for more info on using DBCC PAGE to find the object name.
> Once you find the hot object(s), the next step is to find the user
> process,
> SQL statements, SPs, and so on that access the hot object, and determine
> if
> you can modify these SQL statements/SPs and so on to reduce contention.
> I'm pretty sure that threadpool waits are related to contention on the
> worker threads, but I need to double check.
> Linchi
> "Hassan" wrote:
|||Perhaps you can consider using some form of bulk mechanism (there are
several) to load your data instead of a "massive number of inserts". Also,
consider using some form of queing mechanism to slow down the intensity of
the load process and spread out the crush on the server.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:OEyk7JGMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> Yes we do have worker thread contention. Its all related to this massive
> batch of many inserts against a single table and the pagelatch i believe
> causes contention and hence the rest of the threads stack up and worker
> threads become starved too.
> As you mentioned I have some hot pages.. I'm sure I do.. Are these pages
> that are being accessed in memory since its a PageLatch wait as opposed to
> PageIOLatch ? Is that the only difference between the 2 wait types i.e.
> one being accessed from memory vs other being accessed from disk ?
> Also page 49 of Kalens Query Tuning and Optimization states the following:
> PageIOLatch_ex : This wait occurs when a task is waiting on a latch for a
> buffer that is NOT in an IO request.
> What does that mean ? If its not IO related, then what is it ? And is a
> reference to a buffer always means it in memory as in RAM or could it be
> virtual memory and hence be a latch on a buffer on disk ?
> Thanks
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:20BE1711-42E7-485F-84E8-3769A78AA1E5@.microsoft.com...
>
|||> What does that mean ? If its not IO related, then what is it ? And is a
> reference to a buffer always means it in memory as in RAM or could it be
> virtual memory and hence be a latch on a buffer on disk ?
I don't know the exact context in which this is stated. But PageIOLatch is
I/O related. You can easily confirm this yourself by running some quick tests.
Linchi
"Hassan" wrote:

> Yes we do have worker thread contention. Its all related to this massive
> batch of many inserts against a single table and the pagelatch i believe
> causes contention and hence the rest of the threads stack up and worker
> threads become starved too.
> As you mentioned I have some hot pages.. I'm sure I do.. Are these pages
> that are being accessed in memory since its a PageLatch wait as opposed to
> PageIOLatch ? Is that the only difference between the 2 wait types i.e. one
> being accessed from memory vs other being accessed from disk ?
> Also page 49 of Kalens Query Tuning and Optimization states the following:
> PageIOLatch_ex : This wait occurs when a task is waiting on a latch for a
> buffer that is NOT in an IO request.
> What does that mean ? If its not IO related, then what is it ? And is a
> reference to a buffer always means it in memory as in RAM or could it be
> virtual memory and hence be a latch on a buffer on disk ?
> Thanks
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:20BE1711-42E7-485F-84E8-3769A78AA1E5@.microsoft.com...
>
>
|||And is PageLatch from memory only ?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:6677072C-AA1D-4E87-993F-C606D2C0AC20@.microsoft.com...[vbcol=seagreen]
> I don't know the exact context in which this is stated. But PageIOLatch is
> I/O related. You can easily confirm this yourself by running some quick
> tests.
> Linchi
> "Hassan" wrote:
|||Yes, a PageIOLatch is when it is syncing with the physical disk and a plain
latch is totally memory related.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:eOP7PjIMIHA.6060@.TK2MSFTNGP05.phx.gbl...
> And is PageLatch from memory only ?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:6677072C-AA1D-4E87-993F-C606D2C0AC20@.microsoft.com...
>
|||So when I have high PageLatch, whats a fix ? Will more memory help ? Doesnt
seem like.. I guess I might just have to rearchitect the way we hit the page
in contention.
Since the table that undergoes these high inserts has an identity column
thats the clustered index and with all those inserts hitting that one pages
as its sequential, i guess we have our contention on that page.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23%23FCA4IMIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Yes, a PageIOLatch is when it is syncing with the physical disk and a
> plain latch is totally memory related.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.test.com> wrote in message
> news:eOP7PjIMIHA.6060@.TK2MSFTNGP05.phx.gbl...
>
|||I am not convinced the insert process is the only culprit here. Have you
checked for blocking (sp_who2 active is simplest probably). dbcc
inputbuffer(spid) will get you the executing code. Are their other blocking
processes ongoing that are hitting the table at the same time? Are
transactions being held as short as possible? Are the locks being acquired
row, page or something larger? Lots of things to investigate here.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.hotmail.com> wrote in message
news:utE8VeJMIHA.1204@.TK2MSFTNGP03.phx.gbl...
> So when I have high PageLatch, whats a fix ? Will more memory help ?
> Doesnt seem like.. I guess I might just have to rearchitect the way we hit
> the page in contention.
> Since the table that undergoes these high inserts has an identity column
> thats the clustered index and with all those inserts hitting that one
> pages as its sequential, i guess we have our contention on that page.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23%23FCA4IMIHA.5208@.TK2MSFTNGP04.phx.gbl...
>
|||On Mon, 26 Nov 2007 11:27:51 -0800, "Hassan" <hassan@.test.com> wrote:

>Yes we do have worker thread contention. Its all related to this massive
>batch of many inserts against a single table and the pagelatch i believe
>causes contention and hence the rest of the threads stack up and worker
>threads become starved too.
So you have many spids inserting many rows to the same table at the
same time? Well, they will contend, won't they, and bottleneck at the
log if nowhere else, and block anybody trying to read the target table
while the inserts are going. Are there also readers, and have they
tried read-uncommitted (eg nolock)?
J.