I have a server with 300Gb+ of disk space on a SAN, with a database 55Gb for data and 15Gb for tranx log. SQL Server 2000 w/SP3 is installed. Whenever I run any query (simple, complex, short, long, using indexes or not) the server current activity shows many instances of NETWORKIO, PAGELATCH_EX, CXPACKET and other wait types on several processes. In Profiler, 500,000+ events were recorded within a 60 second timeframe. Someone else is responsible for "infrastructure" so before I go with recommendations to fix my issues, I would like as much accurate info as possible about the cause. This is supposed to be a relatively low-activity server. My understanding is serious disk subsystem issues would cause all these wait types together. But why would this happen when running a "select x, y, z from table" where the table contains < 250000 records and x,y,z make up a covering index. Additionally, I ran a relatively simple query, two tables, inner join, with properly defined indexes and went home to let it run. I returned and it had been running for 18 hours - I finally ran out of patience and stopped it. Any ideas?And you just went home? 2 possibilities:
1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)
the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.
there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.
Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.
Showing posts with label cxpacket. Show all posts
Showing posts with label cxpacket. Show all posts
Friday, February 17, 2012
CXPACKET Wait Issue
I was monitoring a client's warehouse build process last night and stumbled
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest.
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
Hi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumbled
> across the following. During a sproc call that moves data from a staging db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there with
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get more
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest.
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least not
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>
|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>
>
|||On Dec 24, 8:10Xpm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
>
>
>
>
>
>
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.
|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...[vbcol=seagreen]
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest.
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
Hi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumbled
> across the following. During a sproc call that moves data from a staging db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there with
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get more
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest.
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least not
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>
|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>
>
|||On Dec 24, 8:10Xpm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
>
>
>
>
>
>
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.
|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...[vbcol=seagreen]
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>
CXPACKET Wait Issue
I was monitoring a client's warehouse build process last night and stumbled
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest. :(
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netHi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumbled
> across the following. During a sproc call that moves data from a staging db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there with
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get more
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest. :(
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least not
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
>> I was monitoring a client's warehouse build process last night and
>> stumbled
>> across the following. During a sproc call that moves data from a staging
>> db
>> to warehouse db, the spid parallelized on all 8 cpus and then sat there
>> with
>> the following information shown by sys.dm_os_waiting_tasks. Note that
>> all
>> CPUs were pegged at 100% during this event, although I don't know if this
>> particular proc was the cause. To my knowledge this was the only
>> activity
>> on the server at the time however. There wasn't really any other waiting
>> tasks of note other than the typical system tasks. I was unable to get
>> more
>> detailed information on the actual blocking tasks tho. It was pretty
>> late
>> in the night and I wasn't my sharpest. :(
>> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM,
>> 4
>> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy
>> (7
>> drive RAID5 set on a middling SAN) and the database totals about 400GB or
>> so.
>>
>> There were 8 rows in waiting tasks with the below data:
>> waiting_task_address: 0x0000000000EDA868
>> session_id: 57
>> wait_duration_ms: 193860 (at the time of the DMV grab)
>> wait_type: CXPACKET
>> resource_address: 0x00000000801F9A50
>> blocking_task_address: all were different
>> blocking_session_id: 57
>> blocking_exec_context_id: all were different
>> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>>
>> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> resource_description is or means? Is it simply an internal mechanism
>> related to parallelization and perhaps this query (it was either a huge
>> insert or update - not sure which, sorry) could be sped up (or at least
>> not
>> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> maybe
>> 4? Any information or help would be appreciated.
>>
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>>|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> > Hi
> >
> > Have you checked your missing indexes for anything related to the task?
> >
> > John
> >
> > "TheSQLGuru" wrote:
> >
> >> I was monitoring a client's warehouse build process last night and
> >> stumbled
> >> across the following. During a sproc call that moves data from a staging
> >> db
> >> to warehouse db, the spid parallelized on all 8 cpus and then sat there
> >> with
> >> the following information shown by sys.dm_os_waiting_tasks. Note that
> >> all
> >> CPUs were pegged at 100% during this event, although I don't know if this
> >> particular proc was the cause. To my knowledge this was the only
> >> activity
> >> on the server at the time however. There wasn't really any other waiting
> >> tasks of note other than the typical system tasks. I was unable to get
> >> more
> >> detailed information on the actual blocking tasks tho. It was pretty
> >> late
> >> in the night and I wasn't my sharpest. :(
> >>
> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM,
> >> 4
> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy
> >> (7
> >> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> >> so.
> >>
> >>
> >> There were 8 rows in waiting tasks with the below data:
> >>
> >> waiting_task_address: 0x0000000000EDA868
> >> session_id: 57
> >> wait_duration_ms: 193860 (at the time of the DMV grab)
> >> wait_type: CXPACKET
> >> resource_address: 0x00000000801F9A50
> >> blocking_task_address: all were different
> >> blocking_session_id: 57
> >> blocking_exec_context_id: all were different
> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
> >>
> >>
> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> >> resource_description is or means? Is it simply an internal mechanism
> >> related to parallelization and perhaps this query (it was either a huge
> >> insert or update - not sure which, sorry) could be sped up (or at least
> >> not
> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
> >> maybe
> >> 4? Any information or help would be appreciated.
> >>
> >>
> >> --
> >> Kevin G. Boles
> >> TheSQLGuru
> >> Indicium Resources, Inc.
> >> kgboles a earthlink dt net
> >>
> >>
> >>
> >>
>
>|||On Dec 24, 8:10=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find=
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
> > Unfortunately the client rebuilds all indexes each night with their
> > warehouse rebuild so missing indexes isn't useful.
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> > kgboles a earthlink dt net
> > "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> > > Hi
> > > Have you checked your missing indexes for anything related to the task=?
> > > John
> > > "TheSQLGuru" wrote:
> > >> I was monitoring a client's warehouse build process last night and
> > >> stumbled
> > >> across the following. =A0During a sproc call that moves data from a s=taging
> > >> db
> > >> to warehouse db, the spid parallelized on all 8 cpus and then sat the=re
> > >> with
> > >> the following information shown by sys.dm_os_waiting_tasks. =A0Note t=hat
> > >> all
> > >> CPUs were pegged at 100% during this event, although I don't know if =this
> > >> particular proc was the cause. =A0To my knowledge this was the only
> > >> activity
> > >> on the server at the time however. =A0There wasn't really any other w=aiting
> > >> tasks of note other than the typical system tasks. =A0I was unable to= get
> > >> more
> > >> detailed information on the actual blocking tasks tho. =A0It was pret=ty
> > >> late
> > >> in the night and I wasn't my sharpest. =A0:(
> > >> This is a 2005 box, patched up past SP2 to build 3186. =A064bit, 32GB= RAM,
> > >> 4
> > >> dual core sprocs no hyperthreading. =A0The I/O subsystem isn't very b=eefy
> > >> (7
> > >> drive RAID5 set on a middling SAN) and the database totals about 400G=B or
> > >> so.
> > >> There were 8 rows in waiting tasks with the below data:
> > >> waiting_task_address: =A00x0000000000EDA868
> > >> session_id: =A057
> > >> wait_duration_ms: =A0 =A0 =A0193860 (at the time of the DMV grab)
> > >> wait_type: =A0CXPACKET
> > >> resource_address: =A0 0x00000000801F9A50
> > >> blocking_task_address: =A0all were different
> > >> blocking_session_id: =A057
> > >> blocking_exec_context_id: =A0all were different
> > >> resource_description: =A0exchangeEvent id=3Dport801f61c0 nodeId=3D5
> > >> Anyone got any ideas what an "exchangeEvent id=3Dport801f61c0 nodeId==3D5"
> > >> resource_description is or means? =A0Is it simply an internal mechani=sm
> > >> related to parallelization and perhaps this query (it was either a hu=ge
> > >> insert or update - not sure which, sorry) could be sped up (or at lea=st
> > >> not
> > >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or=
> > >> maybe
> > >> 4? =A0Any information or help would be appreciated.
> > >> --
> > >> Kevin G. Boles
> > >> TheSQLGuru
> > >> Indicium Resources, Inc.
> > >> kgboles a earthlink dt net- Hide quoted text -
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
>> Unfortunately the client rebuilds all indexes each night with their
>> warehouse rebuild so missing indexes isn't useful.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>> > Hi
>> >
>> > Have you checked your missing indexes for anything related to the task?
>> >
>> > John
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> I was monitoring a client's warehouse build process last night and
>> >> stumbled
>> >> across the following. During a sproc call that moves data from a
>> >> staging
>> >> db
>> >> to warehouse db, the spid parallelized on all 8 cpus and then sat
>> >> there
>> >> with
>> >> the following information shown by sys.dm_os_waiting_tasks. Note that
>> >> all
>> >> CPUs were pegged at 100% during this event, although I don't know if
>> >> this
>> >> particular proc was the cause. To my knowledge this was the only
>> >> activity
>> >> on the server at the time however. There wasn't really any other
>> >> waiting
>> >> tasks of note other than the typical system tasks. I was unable to
>> >> get
>> >> more
>> >> detailed information on the actual blocking tasks tho. It was pretty
>> >> late
>> >> in the night and I wasn't my sharpest. :(
>> >>
>> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB
>> >> RAM,
>> >> 4
>> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very
>> >> beefy
>> >> (7
>> >> drive RAID5 set on a middling SAN) and the database totals about 400GB
>> >> or
>> >> so.
>> >>
>> >>
>> >> There were 8 rows in waiting tasks with the below data:
>> >>
>> >> waiting_task_address: 0x0000000000EDA868
>> >> session_id: 57
>> >> wait_duration_ms: 193860 (at the time of the DMV grab)
>> >> wait_type: CXPACKET
>> >> resource_address: 0x00000000801F9A50
>> >> blocking_task_address: all were different
>> >> blocking_session_id: 57
>> >> blocking_exec_context_id: all were different
>> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>> >>
>> >>
>> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> >> resource_description is or means? Is it simply an internal mechanism
>> >> related to parallelization and perhaps this query (it was either a
>> >> huge
>> >> insert or update - not sure which, sorry) could be sped up (or at
>> >> least
>> >> not
>> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> >> maybe
>> >> 4? Any information or help would be appreciated.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >>
>> >>
>>|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>> Hi
>> A common cause of parellisation is missing indexes, so unless you can
>> find
>> the query that is probably causing this you may not be able to solve the
>> issue. You may want to try SQL profiling the process.
>> John
>> "TheSQLGuru" wrote:
>> Unfortunately the client rebuilds all indexes each night with their
>> warehouse rebuild so missing indexes isn't useful.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>> > Hi
>> >
>> > Have you checked your missing indexes for anything related to the
>> > task?
>> >
>> > John
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> I was monitoring a client's warehouse build process last night and
>> >> stumbled
>> >> across the following. During a sproc call that moves data from a
>> >> staging
>> >> db
>> >> to warehouse db, the spid parallelized on all 8 cpus and then sat
>> >> there
>> >> with
>> >> the following information shown by sys.dm_os_waiting_tasks. Note
>> >> that
>> >> all
>> >> CPUs were pegged at 100% during this event, although I don't know if
>> >> this
>> >> particular proc was the cause. To my knowledge this was the only
>> >> activity
>> >> on the server at the time however. There wasn't really any other
>> >> waiting
>> >> tasks of note other than the typical system tasks. I was unable to
>> >> get
>> >> more
>> >> detailed information on the actual blocking tasks tho. It was pretty
>> >> late
>> >> in the night and I wasn't my sharpest. :(
>> >>
>> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB
>> >> RAM,
>> >> 4
>> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very
>> >> beefy
>> >> (7
>> >> drive RAID5 set on a middling SAN) and the database totals about
>> >> 400GB or
>> >> so.
>> >>
>> >>
>> >> There were 8 rows in waiting tasks with the below data:
>> >>
>> >> waiting_task_address: 0x0000000000EDA868
>> >> session_id: 57
>> >> wait_duration_ms: 193860 (at the time of the DMV grab)
>> >> wait_type: CXPACKET
>> >> resource_address: 0x00000000801F9A50
>> >> blocking_task_address: all were different
>> >> blocking_session_id: 57
>> >> blocking_exec_context_id: all were different
>> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>> >>
>> >>
>> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> >> resource_description is or means? Is it simply an internal mechanism
>> >> related to parallelization and perhaps this query (it was either a
>> >> huge
>> >> insert or update - not sure which, sorry) could be sped up (or at
>> >> least
>> >> not
>> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> >> maybe
>> >> 4? Any information or help would be appreciated.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >>
>> >>
>>
>
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest. :(
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netHi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumbled
> across the following. During a sproc call that moves data from a staging db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there with
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get more
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest. :(
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least not
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
>> I was monitoring a client's warehouse build process last night and
>> stumbled
>> across the following. During a sproc call that moves data from a staging
>> db
>> to warehouse db, the spid parallelized on all 8 cpus and then sat there
>> with
>> the following information shown by sys.dm_os_waiting_tasks. Note that
>> all
>> CPUs were pegged at 100% during this event, although I don't know if this
>> particular proc was the cause. To my knowledge this was the only
>> activity
>> on the server at the time however. There wasn't really any other waiting
>> tasks of note other than the typical system tasks. I was unable to get
>> more
>> detailed information on the actual blocking tasks tho. It was pretty
>> late
>> in the night and I wasn't my sharpest. :(
>> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM,
>> 4
>> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy
>> (7
>> drive RAID5 set on a middling SAN) and the database totals about 400GB or
>> so.
>>
>> There were 8 rows in waiting tasks with the below data:
>> waiting_task_address: 0x0000000000EDA868
>> session_id: 57
>> wait_duration_ms: 193860 (at the time of the DMV grab)
>> wait_type: CXPACKET
>> resource_address: 0x00000000801F9A50
>> blocking_task_address: all were different
>> blocking_session_id: 57
>> blocking_exec_context_id: all were different
>> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>>
>> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> resource_description is or means? Is it simply an internal mechanism
>> related to parallelization and perhaps this query (it was either a huge
>> insert or update - not sure which, sorry) could be sped up (or at least
>> not
>> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> maybe
>> 4? Any information or help would be appreciated.
>>
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>>|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> > Hi
> >
> > Have you checked your missing indexes for anything related to the task?
> >
> > John
> >
> > "TheSQLGuru" wrote:
> >
> >> I was monitoring a client's warehouse build process last night and
> >> stumbled
> >> across the following. During a sproc call that moves data from a staging
> >> db
> >> to warehouse db, the spid parallelized on all 8 cpus and then sat there
> >> with
> >> the following information shown by sys.dm_os_waiting_tasks. Note that
> >> all
> >> CPUs were pegged at 100% during this event, although I don't know if this
> >> particular proc was the cause. To my knowledge this was the only
> >> activity
> >> on the server at the time however. There wasn't really any other waiting
> >> tasks of note other than the typical system tasks. I was unable to get
> >> more
> >> detailed information on the actual blocking tasks tho. It was pretty
> >> late
> >> in the night and I wasn't my sharpest. :(
> >>
> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM,
> >> 4
> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy
> >> (7
> >> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> >> so.
> >>
> >>
> >> There were 8 rows in waiting tasks with the below data:
> >>
> >> waiting_task_address: 0x0000000000EDA868
> >> session_id: 57
> >> wait_duration_ms: 193860 (at the time of the DMV grab)
> >> wait_type: CXPACKET
> >> resource_address: 0x00000000801F9A50
> >> blocking_task_address: all were different
> >> blocking_session_id: 57
> >> blocking_exec_context_id: all were different
> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
> >>
> >>
> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> >> resource_description is or means? Is it simply an internal mechanism
> >> related to parallelization and perhaps this query (it was either a huge
> >> insert or update - not sure which, sorry) could be sped up (or at least
> >> not
> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
> >> maybe
> >> 4? Any information or help would be appreciated.
> >>
> >>
> >> --
> >> Kevin G. Boles
> >> TheSQLGuru
> >> Indicium Resources, Inc.
> >> kgboles a earthlink dt net
> >>
> >>
> >>
> >>
>
>|||On Dec 24, 8:10=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find=
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
> > Unfortunately the client rebuilds all indexes each night with their
> > warehouse rebuild so missing indexes isn't useful.
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> > kgboles a earthlink dt net
> > "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> > > Hi
> > > Have you checked your missing indexes for anything related to the task=?
> > > John
> > > "TheSQLGuru" wrote:
> > >> I was monitoring a client's warehouse build process last night and
> > >> stumbled
> > >> across the following. =A0During a sproc call that moves data from a s=taging
> > >> db
> > >> to warehouse db, the spid parallelized on all 8 cpus and then sat the=re
> > >> with
> > >> the following information shown by sys.dm_os_waiting_tasks. =A0Note t=hat
> > >> all
> > >> CPUs were pegged at 100% during this event, although I don't know if =this
> > >> particular proc was the cause. =A0To my knowledge this was the only
> > >> activity
> > >> on the server at the time however. =A0There wasn't really any other w=aiting
> > >> tasks of note other than the typical system tasks. =A0I was unable to= get
> > >> more
> > >> detailed information on the actual blocking tasks tho. =A0It was pret=ty
> > >> late
> > >> in the night and I wasn't my sharpest. =A0:(
> > >> This is a 2005 box, patched up past SP2 to build 3186. =A064bit, 32GB= RAM,
> > >> 4
> > >> dual core sprocs no hyperthreading. =A0The I/O subsystem isn't very b=eefy
> > >> (7
> > >> drive RAID5 set on a middling SAN) and the database totals about 400G=B or
> > >> so.
> > >> There were 8 rows in waiting tasks with the below data:
> > >> waiting_task_address: =A00x0000000000EDA868
> > >> session_id: =A057
> > >> wait_duration_ms: =A0 =A0 =A0193860 (at the time of the DMV grab)
> > >> wait_type: =A0CXPACKET
> > >> resource_address: =A0 0x00000000801F9A50
> > >> blocking_task_address: =A0all were different
> > >> blocking_session_id: =A057
> > >> blocking_exec_context_id: =A0all were different
> > >> resource_description: =A0exchangeEvent id=3Dport801f61c0 nodeId=3D5
> > >> Anyone got any ideas what an "exchangeEvent id=3Dport801f61c0 nodeId==3D5"
> > >> resource_description is or means? =A0Is it simply an internal mechani=sm
> > >> related to parallelization and perhaps this query (it was either a hu=ge
> > >> insert or update - not sure which, sorry) could be sped up (or at lea=st
> > >> not
> > >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or=
> > >> maybe
> > >> 4? =A0Any information or help would be appreciated.
> > >> --
> > >> Kevin G. Boles
> > >> TheSQLGuru
> > >> Indicium Resources, Inc.
> > >> kgboles a earthlink dt net- Hide quoted text -
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
>> Unfortunately the client rebuilds all indexes each night with their
>> warehouse rebuild so missing indexes isn't useful.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>> > Hi
>> >
>> > Have you checked your missing indexes for anything related to the task?
>> >
>> > John
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> I was monitoring a client's warehouse build process last night and
>> >> stumbled
>> >> across the following. During a sproc call that moves data from a
>> >> staging
>> >> db
>> >> to warehouse db, the spid parallelized on all 8 cpus and then sat
>> >> there
>> >> with
>> >> the following information shown by sys.dm_os_waiting_tasks. Note that
>> >> all
>> >> CPUs were pegged at 100% during this event, although I don't know if
>> >> this
>> >> particular proc was the cause. To my knowledge this was the only
>> >> activity
>> >> on the server at the time however. There wasn't really any other
>> >> waiting
>> >> tasks of note other than the typical system tasks. I was unable to
>> >> get
>> >> more
>> >> detailed information on the actual blocking tasks tho. It was pretty
>> >> late
>> >> in the night and I wasn't my sharpest. :(
>> >>
>> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB
>> >> RAM,
>> >> 4
>> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very
>> >> beefy
>> >> (7
>> >> drive RAID5 set on a middling SAN) and the database totals about 400GB
>> >> or
>> >> so.
>> >>
>> >>
>> >> There were 8 rows in waiting tasks with the below data:
>> >>
>> >> waiting_task_address: 0x0000000000EDA868
>> >> session_id: 57
>> >> wait_duration_ms: 193860 (at the time of the DMV grab)
>> >> wait_type: CXPACKET
>> >> resource_address: 0x00000000801F9A50
>> >> blocking_task_address: all were different
>> >> blocking_session_id: 57
>> >> blocking_exec_context_id: all were different
>> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>> >>
>> >>
>> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> >> resource_description is or means? Is it simply an internal mechanism
>> >> related to parallelization and perhaps this query (it was either a
>> >> huge
>> >> insert or update - not sure which, sorry) could be sped up (or at
>> >> least
>> >> not
>> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> >> maybe
>> >> 4? Any information or help would be appreciated.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >>
>> >>
>>|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>> Hi
>> A common cause of parellisation is missing indexes, so unless you can
>> find
>> the query that is probably causing this you may not be able to solve the
>> issue. You may want to try SQL profiling the process.
>> John
>> "TheSQLGuru" wrote:
>> Unfortunately the client rebuilds all indexes each night with their
>> warehouse rebuild so missing indexes isn't useful.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>> > Hi
>> >
>> > Have you checked your missing indexes for anything related to the
>> > task?
>> >
>> > John
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> I was monitoring a client's warehouse build process last night and
>> >> stumbled
>> >> across the following. During a sproc call that moves data from a
>> >> staging
>> >> db
>> >> to warehouse db, the spid parallelized on all 8 cpus and then sat
>> >> there
>> >> with
>> >> the following information shown by sys.dm_os_waiting_tasks. Note
>> >> that
>> >> all
>> >> CPUs were pegged at 100% during this event, although I don't know if
>> >> this
>> >> particular proc was the cause. To my knowledge this was the only
>> >> activity
>> >> on the server at the time however. There wasn't really any other
>> >> waiting
>> >> tasks of note other than the typical system tasks. I was unable to
>> >> get
>> >> more
>> >> detailed information on the actual blocking tasks tho. It was pretty
>> >> late
>> >> in the night and I wasn't my sharpest. :(
>> >>
>> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB
>> >> RAM,
>> >> 4
>> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very
>> >> beefy
>> >> (7
>> >> drive RAID5 set on a middling SAN) and the database totals about
>> >> 400GB or
>> >> so.
>> >>
>> >>
>> >> There were 8 rows in waiting tasks with the below data:
>> >>
>> >> waiting_task_address: 0x0000000000EDA868
>> >> session_id: 57
>> >> wait_duration_ms: 193860 (at the time of the DMV grab)
>> >> wait_type: CXPACKET
>> >> resource_address: 0x00000000801F9A50
>> >> blocking_task_address: all were different
>> >> blocking_session_id: 57
>> >> blocking_exec_context_id: all were different
>> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>> >>
>> >>
>> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> >> resource_description is or means? Is it simply an internal mechanism
>> >> related to parallelization and perhaps this query (it was either a
>> >> huge
>> >> insert or update - not sure which, sorry) could be sped up (or at
>> >> least
>> >> not
>> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> >> maybe
>> >> 4? Any information or help would be appreciated.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >>
>> >>
>>
>
CXPACKET Wait Issue
I was monitoring a client's warehouse build process last night and stumbled
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest.
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netHi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumble
d
> across the following. During a sproc call that moves data from a staging
db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there wi
th
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get mo
re
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest.
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least no
t
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or mayb
e
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
>|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>
>|||On Dec 24, 8:10=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find=[/vbcol
]
[vbcol=seagreen]
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
>
>
>
?[vbcol=seagreen]
>
>
>
taging[vbcol=seagreen]
re[vbcol=seagreen]
hat[vbcol=seagreen]
this[vbcol=seagreen]
aiting[vbcol=seagreen]
get[vbcol=seagreen]
ty[vbcol=seagreen]
>
RAM,[vbcol=seagreen]
eefy[vbcol=seagreen]
B or[vbcol=seagreen]
>
>
>
=3D5"[vbcol=seagreen]
sm[vbcol=seagreen]
ge[vbcol=seagreen]
st[vbcol=seagreen]
[vbcol=seagreen]
>
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...[vbcol=seagreen]
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
>|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive...-3.asp
x
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest.
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netHi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumble
d
> across the following. During a sproc call that moves data from a staging
db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there wi
th
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get mo
re
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest.
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least no
t
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or mayb
e
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
>|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>
>|||On Dec 24, 8:10=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find=[/vbcol
]
[vbcol=seagreen]
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
>
>
>
?[vbcol=seagreen]
>
>
>
taging[vbcol=seagreen]
re[vbcol=seagreen]
hat[vbcol=seagreen]
this[vbcol=seagreen]
aiting[vbcol=seagreen]
get[vbcol=seagreen]
ty[vbcol=seagreen]
>
RAM,[vbcol=seagreen]
eefy[vbcol=seagreen]
B or[vbcol=seagreen]
>
>
>
=3D5"[vbcol=seagreen]
sm[vbcol=seagreen]
ge[vbcol=seagreen]
st[vbcol=seagreen]
[vbcol=seagreen]
>
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...[vbcol=seagreen]
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
>|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive...-3.asp
x
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>
CXPACKET error related to MOM process
While MOM processes are running at some point, a process goes into deadlock and uses up all existing CPUs.
Sysprocesses shows this it opened up 4 threads and program name is Microsoft? Reliability Analysis Service.
Profiler doesn't show which command it was trying to execute, but last notable command which has started was MRAS_pcLoad EXECUTE @.i_Return_Code = sp_getapplock @.Resource = N'MOM.Datawarehousing.DTSPackageGenerator.exe', @.LockMode = N'Exclusive', @.LockOwner = N'Session', @.LockTimeout =
Can you please help us, what could be the problem. It has been running fine till couple of days back.
--Prabhu
are you running sql2k or sql2k5? Try lowering the "degree of parallelism" (sp_configure 'max degree of parallelism', #) or using Maxdop query hint (maxdop=1) to relief the problem.
Consider updating to latest service pack if you haven't done so.
http://support.microsoft.com/kb/293232
|||
It's SQL 2K, we are running on latest service pack, version 8.00.2187.
I decreased the degree of parallelism to 3 processors, instead of 4 (MAX, in this case), now all these 3 are pegged. I couldn't use the qry hint, as I mentioned it is running DTS executable.
--Prabhu
|||If max degree parallelism is set to either zero (0) or greater than one (1), you'd still encounter parallelism issue. Try setting it to one (1) to see if it helps.
|||In that case, not more than one processor would be used for parallelism, meaning no parallelism for qry execution. Right?
We would like to make use of all existing processors for qry parallelism, too.
--Prabhu
|||That's correct. If you set 'max degree parallelism' you set it for the entire server. Thus, every query will be affected by this.
The only other option is to use query hint maxdop which affects only that query, but you've already said you can't change that.
CXPACKET and XEON Processors
I have a question that I hope someone can answer. We are
running a WIN2K Server with SQL Server 2K SP 3. It is a
dual xeon processor machine with hyperthreading enabled.
I have noticed that the SQL PERF (Waitstats) CXPACKET is
much higher with xeon processors and was told that WIN2K3
would help with the lock times with the threading. Can
anyone speak to this? I have disabled the hyperthreading
at the BIOS level and noticed a higher level of
performance on the WIN2K machine.
Thanks
GregYou might try reducing 'max degree of parallelism' configuration option
rather than disable hyperthreading. If this is an OLTP system that
doesn't really benefit from parallel queries, try setting it to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Greg" <greg@.stockamp.com> wrote in message
news:141001c3822d$d7be2d90$a001280a@.phx.gbl...
> I have a question that I hope someone can answer. We are
> running a WIN2K Server with SQL Server 2K SP 3. It is a
> dual xeon processor machine with hyperthreading enabled.
> I have noticed that the SQL PERF (Waitstats) CXPACKET is
> much higher with xeon processors and was told that WIN2K3
> would help with the lock times with the threading. Can
> anyone speak to this? I have disabled the hyperthreading
> at the BIOS level and noticed a higher level of
> performance on the WIN2K machine.
> Thanks
> Greg|||Waittype on CXPACKET is normal for parallel queries. Do not reduce MAXDOP
because you see waits on CXPACKET, reduce MAXDOP because you've proven it
makes queries you care about go faster.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Greg" <greg@.stockamp.com> wrote in message
news:141001c3822d$d7be2d90$a001280a@.phx.gbl...
> I have a question that I hope someone can answer. We are
> running a WIN2K Server with SQL Server 2K SP 3. It is a
> dual xeon processor machine with hyperthreading enabled.
> I have noticed that the SQL PERF (Waitstats) CXPACKET is
> much higher with xeon processors and was told that WIN2K3
> would help with the lock times with the threading. Can
> anyone speak to this? I have disabled the hyperthreading
> at the BIOS level and noticed a higher level of
> performance on the WIN2K machine.
> Thanks
> Greg
running a WIN2K Server with SQL Server 2K SP 3. It is a
dual xeon processor machine with hyperthreading enabled.
I have noticed that the SQL PERF (Waitstats) CXPACKET is
much higher with xeon processors and was told that WIN2K3
would help with the lock times with the threading. Can
anyone speak to this? I have disabled the hyperthreading
at the BIOS level and noticed a higher level of
performance on the WIN2K machine.
Thanks
GregYou might try reducing 'max degree of parallelism' configuration option
rather than disable hyperthreading. If this is an OLTP system that
doesn't really benefit from parallel queries, try setting it to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Greg" <greg@.stockamp.com> wrote in message
news:141001c3822d$d7be2d90$a001280a@.phx.gbl...
> I have a question that I hope someone can answer. We are
> running a WIN2K Server with SQL Server 2K SP 3. It is a
> dual xeon processor machine with hyperthreading enabled.
> I have noticed that the SQL PERF (Waitstats) CXPACKET is
> much higher with xeon processors and was told that WIN2K3
> would help with the lock times with the threading. Can
> anyone speak to this? I have disabled the hyperthreading
> at the BIOS level and noticed a higher level of
> performance on the WIN2K machine.
> Thanks
> Greg|||Waittype on CXPACKET is normal for parallel queries. Do not reduce MAXDOP
because you see waits on CXPACKET, reduce MAXDOP because you've proven it
makes queries you care about go faster.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Greg" <greg@.stockamp.com> wrote in message
news:141001c3822d$d7be2d90$a001280a@.phx.gbl...
> I have a question that I hope someone can answer. We are
> running a WIN2K Server with SQL Server 2K SP 3. It is a
> dual xeon processor machine with hyperthreading enabled.
> I have noticed that the SQL PERF (Waitstats) CXPACKET is
> much higher with xeon processors and was told that WIN2K3
> would help with the lock times with the threading. Can
> anyone speak to this? I have disabled the hyperthreading
> at the BIOS level and noticed a higher level of
> performance on the WIN2K machine.
> Thanks
> Greg
CXPACKET and max degree of parallelism
Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
CXPACKET and max degree of parallelism
Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
Basically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft .com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
Basically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft .com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
CXPACKET and max degree of parallelism
Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
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
>
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
>
Subscribe to:
Posts (Atom)