Data fields are not allowed in the Page Header section

How can I build the page header (Sales for John Smith) where John Smith is
returned in the query results? Data fields are not allowed in the Page
Header section.
All responses greatly appreciated.
--
Any and all contributions are greatly appreciated ...
Regards TJYou can use read-only parameter with default value from query (assuming that
you render separate instance of report for every person)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:O2Mt1aNoEHA.3564@.tk2msftngp13.phx.gbl...
> How can I build the page header (Sales for John Smith) where John Smith is
> returned in the query results? Data fields are not allowed in the Page
> Header section.
> All responses greatly appreciated.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>
>

Data corruption in ldf when debugging WinForm app

I'm trying to build my 1st WinForm app which uses SqlExpress and when I click Debug and test my app everything is fine (though occasionally I do get time out errors - but that's another question I guess), however I can't then debug the app again because I get the following error:

"The log scan number (34:410:82) passed to log scan in database '3D1D0C232D2C55B0EF6C455470CD3D4C_S\\VISUAL STUDIO 2005\\PROJECTS\\MYFIRSTAPP\\MYFIRSTAPP\\BIN\\DEBUG\\OTJDB.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. \r\nAn error occurred during recovery, preventing the database '3D1D0C232D2C55B0EF6C455470CD3D4C_S\\VISUAL STUDIO 2005\\PROJECTS\\MYFIRSTAPP\\MYFIRSTAPP\\BIN\\DEBUG\\OTJDB.MDF' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.\r\nCannot open user default database. Login failed.\r\nLogin failed for user 'SQurreL\\Simon'."

I can't delete the ldf or mdb files in the debug\bin folder because they are in use, so I have to stop the SQLExpress service, kill the sqlserv.exe process, delete the ldf and mdb files, then restart the SQLExpress service and try again... which is beginning to get right on my nerves now

Can anyone help with my sanity please?

Hi ganseki,

I think the error message has claimed very clearly: " .....that the log file (.ldf) does not match the data file (.mdf)." If mdf file and the corresponding ldf file cannot match, we cannot open that database again--that's designed by microsoft. So, we need to "If this error occurred during replication, re-create the publication. Otherwise, restore from backup..".

I have to stop the SQLExpress service, kill the sqlserv.exe process, delete the ldf and mdb files, then restart the SQLExpress service and try again

I think what you did is right. So, does this error still occure after you did that? Also i'm curious about what you did to that database thru your application, that you get that error message.

Hope my suggestion helps

|||

Thanks for the reply.

Any idea why that is happening each time I debug the app? Or is it normal for WinForms development to have to jump through those hoops in order to test the changes.

It takes longer to stop the service, kill the process, delete the files and restart the service than to make a change to a column width in a datagridview, which if I then find is too narrow or too wide means I have to go through that whole process again just to check that changing the width from 150 to 100 isn't enough? This involves no change to the database, all that would have happened is a select to get the data for the grid, but it happens even without any database interaction - i.e. just opening the app (which starts with a blank page).

After debugging I click the close button on the form, the app closes and Visual Studio quits debug mode. I make changes (which can be as trivial as my illustration above) and then have to go through that routine. And that happens each and every time I want to debug.

It is driving meinsane
Is there something I should be doing differently to stop this from happening?

Data Acess aplication block __How to use it on server without Microsoft

Hi all
As per my earlier conversation with Ciaran (thx for reply) I have
installed the MS APplication block on the server , when i ran Build
Enterprise Library file and Install Services from (batch files )
programme files menu it was asking for visual studio 2003 , I have only
.net framework on the server how can i use the MS application block
data access library on my server plz help...
Mukesh Agarwal
mukesh.nitb@.gmail.com
Delhi India
Ciaran O''Donnell wrote:
> The error you are getting is because the enterprise library wants to
write events to the event log and setting this up requires registry
permissions. You need to installutil the dlls for the enterprise library
(this is covered in the docs somewhere). The reason it is writing to the
event log is because the connection failed. If you setup the event log
access correctly then you will have an eventlog entry telling you why
the connection failed.
>
> HTH
>
> Ciaran O'Donnell
>
> "Mukesh" wrote:
>
>> Hi
>>
>> I have Microsoft Enterprise Library 2005 installed on my local system.
>> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
Server 2000.
>>
>> I am developing a web application in which i am using data Access
Application Block for data access.
>>
>> I have a remote web n data server with asp.net 1.1 support
>>
>> the application is working fine here on the local server(web & data).
>>
>> when i change the data server path to the remote server it also
working fine but
>> When I uploaded the application on the remote server n tested there
it gives me the following err
>>
>>
>> ex.message was
>>
>>
>> >>The type initializer for
>>"Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFailedEvent"
>>threw an exception.
>>
>> ex.Source was
>>
>> ..........Microsoft.Practices.EnterpriseLibrary.Data
>>
>> ex.trace was
>>
>> at
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFailedEvent.Fire(String
connectionString) at
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumentationFacade.ConnectionFailed(String
connectionString) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBCommandWrapper
command) at property.m4.Button2_Click(Object sender, EventArgs e)
>>
>>
>> Plz help me why this is happening.........
>>
>> Mukesh Kumar agarwal
>> Delhi
>> India
>>
>>
>>
>>
>>
>>The build file calls devenv.exe, which is Visual Studio 2003. You best
option now is a command line compilation of the library. Follow the docs on
the csc.exe (C#) or vbc.exe (VB.NET) compilers.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside of the box!
*************************************************
"Mukesh" <efextra@.newsgroups.nospam> wrote in message
news:45056076.3060606@.newsgroups.nospam...
> Hi all
> As per my earlier conversation with Ciaran (thx for reply) I have
> installed the MS APplication block on the server , when i ran Build
> Enterprise Library file and Install Services from (batch files ) programme
> files menu it was asking for visual studio 2003 , I have only .net
> framework on the server how can i use the MS application block data access
> library on my server plz help...
> Mukesh Agarwal
> mukesh.nitb@.gmail.com
> Delhi India
>
> Ciaran O''Donnell wrote:
> > The error you are getting is because the enterprise library wants to
> write events to the event log and setting this up requires registry
> permissions. You need to installutil the dlls for the enterprise library
> (this is covered in the docs somewhere). The reason it is writing to the
> event log is because the connection failed. If you setup the event log
> access correctly then you will have an eventlog entry telling you why the
> connection failed.
> >
> > HTH
> >
> > Ciaran O'Donnell
> >
> > "Mukesh" wrote:
> >
> >> Hi
> >>
> >> I have Microsoft Enterprise Library 2005 installed on my local
> >> system.
> >> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> >>
> >> I am developing a web application in which i am using data Access
> Application Block for data access.
> >>
> >> I have a remote web n data server with asp.net 1.1 support
> >>
> >> the application is working fine here on the local server(web & data).
> >>
> >> when i change the data server path to the remote server it also
> working fine but
> >> When I uploaded the application on the remote server n tested there
> it gives me the following err
> >>
> >>
> >> ex.message was
> >>
> >>
> >> >>The type initializer for
> >>"Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFailedEvent"
> >>threw an exception.
> >>
> >> ex.Source was
> >>
> >> ..........Microsoft.Practices.EnterpriseLibrary.Data
> >>
> >> ex.trace was
> >>
> >> at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFailedEvent.Fire(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumentationFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBCommandWrapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
> >>
> >>
> >> Plz help me why this is happening.........
> >>
> >> Mukesh Kumar agarwal
> >> Delhi
> >> India
> >>
> >>
> >>
> >>
> >>
> >>|||I didnt mean install the enterprise library with the MSI file.
I mean't for each dll in the EL you use:
call InstallUtil.exe passing in the name of the dll. This will invoke the
custominstaller classes inside the dll's to register them selves in teh event
log and things.
Ciaran O'Donnell
"Mukesh" wrote:
> Hi all
> As per my earlier conversation with Ciaran (thx for reply) I have
> installed the MS APplication block on the server , when i ran Build
> Enterprise Library file and Install Services from (batch files )
> programme files menu it was asking for visual studio 2003 , I have only
> .net framework on the server how can i use the MS application block
> data access library on my server plz help...
> Mukesh Agarwal
> mukesh.nitb@.gmail.com
> Delhi India
>
> Ciaran O''Donnell wrote:
> > The error you are getting is because the enterprise library wants to
> write events to the event log and setting this up requires registry
> permissions. You need to installutil the dlls for the enterprise library
> (this is covered in the docs somewhere). The reason it is writing to the
> event log is because the connection failed. If you setup the event log
> access correctly then you will have an eventlog entry telling you why
> the connection failed.
> >
> > HTH
> >
> > Ciaran O'Donnell
> >
> > "Mukesh" wrote:
> >
> >> Hi
> >>
> >> I have Microsoft Enterprise Library 2005 installed on my local system.
> >> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> >>
> >> I am developing a web application in which i am using data Access
> Application Block for data access.
> >>
> >> I have a remote web n data server with asp.net 1.1 support
> >>
> >> the application is working fine here on the local server(web & data).
> >>
> >> when i change the data server path to the remote server it also
> working fine but
> >> When I uploaded the application on the remote server n tested there
> it gives me the following err
> >>
> >>
> >> ex.message was
> >>
> >>
> >> >>The type initializer for
> >>"Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFailedEvent"
> >>threw an exception.
> >>
> >> ex.Source was
> >>
> >> ..........Microsoft.Practices.EnterpriseLibrary.Data
> >>
> >> ex.trace was
> >>
> >> at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFailedEvent.Fire(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumentationFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBCommandWrapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
> >>
> >>
> >> Plz help me why this is happening.........
> >>
> >> Mukesh Kumar agarwal
> >> Delhi
> >> India
> >>
> >>
> >>
> >>
> >>
> >>
>|||Install the .msi on a development machine with VS2003.
Look at the .bat files.
You need to not find the compile code (csc or dev.exe), but rather the code
that installs the services.
You need the rights to install services , especially on a Server Machine.
Make sure the framework (free download) is installed on the server also.
"Mukesh" <efextra@.newsgroups.nospam> wrote in message
news:45056076.3060606@.newsgroups.nospam...
> Hi all
> As per my earlier conversation with Ciaran (thx for reply) I have
> installed the MS APplication block on the server , when i ran Build
> Enterprise Library file and Install Services from (batch files )
> programme files menu it was asking for visual studio 2003 , I have only
> .net framework on the server how can i use the MS application block
> data access library on my server plz help...
> Mukesh Agarwal
> mukesh.nitb@.gmail.com
> Delhi India
>
> Ciaran O''Donnell wrote:
> > The error you are getting is because the enterprise library wants to
> write events to the event log and setting this up requires registry
> permissions. You need to installutil the dlls for the enterprise library
> (this is covered in the docs somewhere). The reason it is writing to the
> event log is because the connection failed. If you setup the event log
> access correctly then you will have an eventlog entry telling you why
> the connection failed.
> >
> > HTH
> >
> > Ciaran O'Donnell
> >
> > "Mukesh" wrote:
> >
> >> Hi
> >>
> >> I have Microsoft Enterprise Library 2005 installed on my local
system.
> >> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> >>
> >> I am developing a web application in which i am using data Access
> Application Block for data access.
> >>
> >> I have a remote web n data server with asp.net 1.1 support
> >>
> >> the application is working fine here on the local server(web & data).
> >>
> >> when i change the data server path to the remote server it also
> working fine but
> >> When I uploaded the application on the remote server n tested there
> it gives me the following err
> >>
> >>
> >> ex.message was
> >>
> >>
> >> >>The type initializer for
>>"Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnect
ionFailedEvent"
> >>threw an exception.
> >>
> >> ex.Source was
> >>
> >> ..........Microsoft.Practices.EnterpriseLibrary.Data
> >>
> >> ex.trace was
> >>
> >> at
>
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFai
ledEvent.Fire(String
> connectionString) at
>
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumentati
onFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
>
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBCommandW
rapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
> >>
> >>
> >> Plz help me why this is happening.........
> >>
> >> Mukesh Kumar agarwal
> >> Delhi
> >> India
> >>
> >>
> >>
> >>
> >>
> >>|||Dear Mukesh,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me
know. I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Data Access Differences in SQL Server 2005

Hello,

We have a large set of applications that we developed and build for use with Borland InterBase (5.x-7.x). We have moved/migrated off of Borland InterBase to MS SQL Server 2005. We are find some situations where InterBase was an more elegant solution and finding it difficult to find the right way to change our applications to achieve the same result.

In InterBase, our Database Transactions allowed our user to make changes to the DB, Tables, Rows, etc, without blocking reads to the same DB, Tables, Rows, etc. We are looking for a mode in SQL Server 2005, which allows use this same functionality. We have been to a few Microsoft Conference's evaluating SQL Server 2005, and we discussed this with some Microsoft Tech repepresentatives at these conference who told use there was an roughly equivalent mode that was going to be available in 2005. Unfortunately I'm not able to remember the details of this information, does anyone know what new 'DB Modes' or Transaction Options where added that might allow InterBase-like features.

Thanks,

Wow!

The SQL Community doesn't know what SQL Conneciton/Transaction modes existing in SQL Server and how/which have changed in 2k5? Odd, I guess I came to the wrong place for help. Thanks for reading.

George

|||Moved post from SQL Server Database Engine to SQL Server Data Access Forum.|||

SQL Server 2005 supports all four SQL-99 Isolation levels, and also introduces two additional ones. MSDN article describing such. The default behavior is ReadCommitted which is usually preferable as it prevents dirty reads. You seem to desire dirty reads, so what you want is ReadUncommitted.

If you are using .Net code to access the SQL Server instance, you set this property on your SqlTransaction object. Here's the MSDN article on SQLTransaction.IsolationLevel.

You can also modify the Isolation Level using SQL by issuing the SET TRANSACTION ISOLATION LEVEL statement. Described on MSDN here.

|||

A_Bomb wrote:

SQL Server 2005 supports all four SQL-99 Isolation levels, and also introduces two additional ones. MSDN article describing such. The default behavior is ReadCommitted which is usually preferable as it prevents dirty reads. You seem to desire dirty reads, so what you want is ReadUncommitted.

No... I desire behavoir clostest to that of InterBases's defaults. Which seems (from the articles you site) to be closest matched by SNAPSHOT, or READ_COMMITED W/READ_COMMITTED_SNAPSHOT ON. Ie... reads not creating locks and writes not blocking reads because of locks (with reads getting a transaction 'consistent' version of the data).

A_Bomb wrote:

If you are using .Net code to access the SQL Server instance, you set this property on your SqlTransaction object. Here's the MSDN article on SQLTransaction.IsolationLevel.

You can also modify the Isolation Level using SQL by issuing the SET TRANSACTION ISOLATION LEVEL statement. Described on MSDN here.

This is the MSDN Article which I found my answer.

Thanks.

Data Access Differences in SQL Server 2005

Hello,

We have a large set of applications that we developed and build for use with Borland InterBase (5.x-7.x). We have moved/migrated off of Borland InterBase to MS SQL Server 2005. We are find some situations where InterBase was an more elegant solution and finding it difficult to find the right way to change our applications to achieve the same result.

In InterBase, our Database Transactions allowed our user to make changes to the DB, Tables, Rows, etc, without blocking reads to the same DB, Tables, Rows, etc. We are looking for a mode in SQL Server 2005, which allows use this same functionality. We have been to a few Microsoft Conference's evaluating SQL Server 2005, and we discussed this with some Microsoft Tech repepresentatives at these conference who told use there was an roughly equivalent mode that was going to be available in 2005. Unfortunately I'm not able to remember the details of this information, does anyone know what new 'DB Modes' or Transaction Options where added that might allow InterBase-like features.

Thanks,

Wow!

The SQL Community doesn't know what SQL Conneciton/Transaction modes existing in SQL Server and how/which have changed in 2k5? Odd, I guess I came to the wrong place for help. Thanks for reading.

George

|||Moved post from SQL Server Database Engine to SQL Server Data Access Forum.|||

SQL Server 2005 supports all four SQL-99 Isolation levels, and also introduces two additional ones. MSDN article describing such. The default behavior is ReadCommitted which is usually preferable as it prevents dirty reads. You seem to desire dirty reads, so what you want is ReadUncommitted.

If you are using .Net code to access the SQL Server instance, you set this property on your SqlTransaction object. Here's the MSDN article on SQLTransaction.IsolationLevel.

You can also modify the Isolation Level using SQL by issuing the SET TRANSACTION ISOLATION LEVEL statement. Described on MSDN here.

|||

A_Bomb wrote:

SQL Server 2005 supports all four SQL-99 Isolation levels, and also introduces two additional ones. MSDN article describing such. The default behavior is ReadCommitted which is usually preferable as it prevents dirty reads. You seem to desire dirty reads, so what you want is ReadUncommitted.

No... I desire behavoir clostest to that of InterBases's defaults. Which seems (from the articles you site) to be closest matched by SNAPSHOT, or READ_COMMITED W/READ_COMMITTED_SNAPSHOT ON. Ie... reads not creating locks and writes not blocking reads because of locks (with reads getting a transaction 'consistent' version of the data).

A_Bomb wrote:

If you are using .Net code to access the SQL Server instance, you set this property on your SqlTransaction object. Here's the MSDN article on SQLTransaction.IsolationLevel.

You can also modify the Isolation Level using SQL by issuing the SET TRANSACTION ISOLATION LEVEL statement. Described on MSDN here.

This is the MSDN Article which I found my answer.

Thanks.

Daily build - trying to deploy from the command line

Hi

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

Daily build - trying to deploy from the command line

Hi

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

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...
>

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
>> >>
>> >>
>> >>
>> >>
>>
>

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...
>