Data Extension for SQL Server Reporting Services

Is there any other then "Data Extension process" way to manipulate a Dataset ?
Is it a good idea to edit dataset from SQL server using "Data Extension
process" ?
Thanks, JoshWhat is it you are trying to do?
I would stay away from a data extension unless you absolutely have no other
way of solving the problem. It is non-trivial plus it will be unneccesary
when version 2 comes out (probably late summer). Version 2 will have both a
web form and winform control that you can pass a dataset to.
I have found that usually people can get what they need done by using a
stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Josh T" <Josh T@.discussions.microsoft.com> wrote in message
news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> Is there any other then "Data Extension process" way to manipulate a
> Dataset ?
> Is it a good idea to edit dataset from SQL server using "Data Extension
> process" ?
> Thanks, Josh|||Thanks Bruce!
We are trying to solve a conceptual problem: is possible to add a business
layer to a dataset, although it was received from SQL server, before it'll be
processed by Report Server e.g. how we can manipulate dataset before it gets
into report.
Best regards, Ilya
"Bruce L-C [MVP]" wrote:
> What is it you are trying to do?
> I would stay away from a data extension unless you absolutely have no other
> way of solving the problem. It is non-trivial plus it will be unneccesary
> when version 2 comes out (probably late summer). Version 2 will have both a
> web form and winform control that you can pass a dataset to.
> I have found that usually people can get what they need done by using a
> stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > Is there any other then "Data Extension process" way to manipulate a
> > Dataset ?
> > Is it a good idea to edit dataset from SQL server using "Data Extension
> > process" ?
> >
> > Thanks, Josh
>
>|||I've been struggling with this same question. The data extension IS daunting
(maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
(passing lots of parms from form data). With the resulting ADO.Net dataset, I
want to display a report in .PDF format. I've been struggling with all the
mechanics of capturing a model representation of the data as an XML file,
creating an XSD file from that, using the XSD file in the Report Designer.
Preview does not work for me unless I strip a bit of the XML file into the
Designer (I actually have to type it in; PASTE after COPY only puts in one
element - weird?). Then I deploy the RDL. When I run the app it works until I
get to the .RENDER method when it fails saying "item not found" regarding my
reportPath parameter.
In short, I agree this is fraught with problems. How can I accomplish the
display of a PDF report with a stored procedure?
--
John
"Josh T" wrote:
> Thanks Bruce!
> We are trying to solve a conceptual problem: is possible to add a business
> layer to a dataset, although it was received from SQL server, before it'll be
> processed by Report Server e.g. how we can manipulate dataset before it gets
> into report.
> Best regards, Ilya
> "Bruce L-C [MVP]" wrote:
> > What is it you are trying to do?
> >
> > I would stay away from a data extension unless you absolutely have no other
> > way of solving the problem. It is non-trivial plus it will be unneccesary
> > when version 2 comes out (probably late summer). Version 2 will have both a
> > web form and winform control that you can pass a dataset to.
> >
> > I have found that usually people can get what they need done by using a
> > stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > > Is there any other then "Data Extension process" way to manipulate a
> > > Dataset ?
> > > Is it a good idea to edit dataset from SQL server using "Data Extension
> > > process" ?
> > >
> > > Thanks, Josh
> >
> >
> >|||Could someone notice my post dated 5-12-2005 and help me out with an answer
or comment? Thanks.
--
John
"Bruce L-C [MVP]" wrote:
> What is it you are trying to do?
> I would stay away from a data extension unless you absolutely have no other
> way of solving the problem. It is non-trivial plus it will be unneccesary
> when version 2 comes out (probably late summer). Version 2 will have both a
> web form and winform control that you can pass a dataset to.
> I have found that usually people can get what they need done by using a
> stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > Is there any other then "Data Extension process" way to manipulate a
> > Dataset ?
> > Is it a good idea to edit dataset from SQL server using "Data Extension
> > process" ?
> >
> > Thanks, Josh
>
>|||You need to rethink this. You are making it wayyy more complicated than it
needs to be. From your web app you use either URL integration or Web
services. URL integration is easier. You call the report. The report uses
the stored procedure as its data source. When you call the report you
specify that it render it as PDF. The report has report parameters that
your web apps specifies when it calls the report.
Note, first get the report working prior to integrating with your app. I
usually first hard code the parameters to the stored procedure then I take
out the hard coded values and make the query parameters. When you create a
query parameter RS automatically (usually) creates the report parameter for
you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jjamjatra" <johna@.cbmiweb.donotspam.com> wrote in message
news:F30020E9-02FE-435C-94A1-CAC2199A93AC@.microsoft.com...
> I've been struggling with this same question. The data extension IS
> daunting
> (maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
> (passing lots of parms from form data). With the resulting ADO.Net
> dataset, I
> want to display a report in .PDF format. I've been struggling with all the
> mechanics of capturing a model representation of the data as an XML file,
> creating an XSD file from that, using the XSD file in the Report Designer.
> Preview does not work for me unless I strip a bit of the XML file into the
> Designer (I actually have to type it in; PASTE after COPY only puts in one
> element - weird?). Then I deploy the RDL. When I run the app it works
> until I
> get to the .RENDER method when it fails saying "item not found" regarding
> my
> reportPath parameter.
> In short, I agree this is fraught with problems. How can I accomplish the
> display of a PDF report with a stored procedure?
> --
> John
>
> "Josh T" wrote:
>> Thanks Bruce!
>> We are trying to solve a conceptual problem: is possible to add a
>> business
>> layer to a dataset, although it was received from SQL server, before
>> it'll be
>> processed by Report Server e.g. how we can manipulate dataset before it
>> gets
>> into report.
>> Best regards, Ilya
>> "Bruce L-C [MVP]" wrote:
>> > What is it you are trying to do?
>> >
>> > I would stay away from a data extension unless you absolutely have no
>> > other
>> > way of solving the problem. It is non-trivial plus it will be
>> > unneccesary
>> > when version 2 comes out (probably late summer). Version 2 will have
>> > both a
>> > web form and winform control that you can pass a dataset to.
>> >
>> > I have found that usually people can get what they need done by using a
>> > stored procedure.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
>> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
>> > > Is there any other then "Data Extension process" way to manipulate a
>> > > Dataset ?
>> > > Is it a good idea to edit dataset from SQL server using "Data
>> > > Extension
>> > > process" ?
>> > >
>> > > Thanks, Josh
>> >
>> >
>> >|||--
John
"Bruce L-C [MVP]" wrote:
> You need to rethink this. You are making it wayyy more complicated than it
> needs to be. From your web app you use either URL integration or Web
> services. URL integration is easier. You call the report. The report uses
> the stored procedure as its data source. When you call the report you
> specify that it render it as PDF. The report has report parameters that
> your web apps specifies when it calls the report.
> Note, first get the report working prior to integrating with your app. I
> usually first hard code the parameters to the stored procedure then I take
> out the hard coded values and make the query parameters. When you create a
> query parameter RS automatically (usually) creates the report parameter for
> you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jjamjatra" <johna@.cbmiweb.donotspam.com> wrote in message
> news:F30020E9-02FE-435C-94A1-CAC2199A93AC@.microsoft.com...
> > I've been struggling with this same question. The data extension IS
> > daunting
> > (maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
> > (passing lots of parms from form data). With the resulting ADO.Net
> > dataset, I
> > want to display a report in .PDF format. I've been struggling with all the
> > mechanics of capturing a model representation of the data as an XML file,
> > creating an XSD file from that, using the XSD file in the Report Designer.
> > Preview does not work for me unless I strip a bit of the XML file into the
> > Designer (I actually have to type it in; PASTE after COPY only puts in one
> > element - weird?). Then I deploy the RDL. When I run the app it works
> > until I
> > get to the .RENDER method when it fails saying "item not found" regarding
> > my
> > reportPath parameter.
> >
> > In short, I agree this is fraught with problems. How can I accomplish the
> > display of a PDF report with a stored procedure?
> > --
> > John
> >
> >
> > "Josh T" wrote:
> >
> >> Thanks Bruce!
> >> We are trying to solve a conceptual problem: is possible to add a
> >> business
> >> layer to a dataset, although it was received from SQL server, before
> >> it'll be
> >> processed by Report Server e.g. how we can manipulate dataset before it
> >> gets
> >> into report.
> >>
> >> Best regards, Ilya
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> > What is it you are trying to do?
> >> >
> >> > I would stay away from a data extension unless you absolutely have no
> >> > other
> >> > way of solving the problem. It is non-trivial plus it will be
> >> > unneccesary
> >> > when version 2 comes out (probably late summer). Version 2 will have
> >> > both a
> >> > web form and winform control that you can pass a dataset to.
> >> >
> >> > I have found that usually people can get what they need done by using a
> >> > stored procedure.
> >> >
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> >> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> >> > > Is there any other then "Data Extension process" way to manipulate a
> >> > > Dataset ?
> >> > > Is it a good idea to edit dataset from SQL server using "Data
> >> > > Extension
> >> > > process" ?
> >> > >
> >> > > Thanks, Josh
> >> >
> >> >
> >> >
>
>

data extension error

Im in the process of creating my first data extension for reporting
services.
Im getting an error while creating a new report via the add report
wizard. My entry shows up in the type dropdown just as expected but
the next screen has a text box that says querystring. When I click
next I get the following error:
"an error occurred while the query design method was being saved.
Object not set to an instance of an object."
How might I know where this occured? Any help is appreciated.And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.|||And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.

Data Encryption

Hi,

We need to set up a data export process from a SQL DB.

The output (be it XML, Text Files or whatever) needs to be encrypted before it is FTPd somewhere.

Is there support for encrption in SSIS? How / where in the package designer would you achive this?

Thanks in advance.

Martin

There is no built in support for encryption of data. It would be an interesting custom task though if you fancy having a go.

Otherwise, request this for a future enhancement at http://connect.microsoft.com

-Jamie

|||Thanks, shame I was hoping to use it as a lever to kick the upgrade process off from SQL2000.|||

Actually come to think of it - you could leverage .Net's encyption routines quite easily using the script component.

Try doing that!

-Jamie

|||I've been working on an encryption transform actually. We're waiting to get everything in place from corp to release it. If you would like, send an e-mail to jason.gerard at idea.com and I'll let you know when it's ready.

In the meantime, you could use the .NET ecryption API's from inside a Script Transform.

data conversion -- varchar to nvarchar

Hello,
The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
We are in the process of investigating international support for our
application and part of that will likely require changing all of the
char/varchar columns in our database to nchar/nvarchar.
From what I gather by reading these forums, altering the columns in place
via ALTER TABLE statements is an acceptable method for doing this.
The question that I have been unable to completely confirm, however, is what
happens to existing data in the tables? Does the data get converted to
Unicode as part of the ALTER TABLE statement? And, if so, is there any risk
that this conversion will produce unexpected or unpredictable results?
Preliminary testing seems to indicate that the data does get converted
successfully during this process but I am just trying to confirm that
suspicion.
Thanks in advance for any assistance,
JohnLx> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement?
Yes, but of course there can't already be any data in there that requires
Unicode. Other than disk space requirements for those columns doubling (and
don't forget indexes), you shouldn't see any noticeable difference, except
(see next comment).

> And, if so, is there any risk
> that this conversion will produce unexpected or unpredictable results?
Absolutely. For char/varchar in the smaller size ranges (up to 4000) you
shouldn't see any problems. However, if you have a varchar(8000) with at
least one tuple with 4001 or more characters, you will get the following
when you try to convert VARCHAR(8000) to NVARCHAR(4000) (the largest size
for varying double-wide):
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
In addition, you'll want to reindex and update statistics for any tables
that have indexes/statistics on the column(s) you're changing. You'll also
want to verify that you re-compile (and alter params and converts, where
necessary) any stored procedures or functions that reference the columns.
You might also want to issue sp_refreshview for any views that reference
those tables.
A|||John,
the data get's converted in place as long as they fit into the new
datatype. Inyour case there shouldn't be an problem. The only thing
you have to keep in mind is while you can store up 8000 char in a
varchar column, nvarchar uses twice the space and thus the limit is
4000 characters.
Markus|||Whenever you alter the structure of a table, it can cause index
fragmentation. You will want to use DBCC INDEXDEFRAG for each altered table.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"johnlx" <nomail@.discussions.microsoft.com> wrote in message
news:2288C9DC-40B8-48E0-A63B-143A622A7030@.microsoft.com...
> Hello,
> The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
> We are in the process of investigating international support for our
> application and part of that will likely require changing all of the
> char/varchar columns in our database to nchar/nvarchar.
> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement? And, if so, is there any
> risk
> that this conversion will produce unexpected or unpredictable results?
> Preliminary testing seems to indicate that the data does get converted
> successfully during this process but I am just trying to confirm that
> suspicion.
> Thanks in advance for any assistance,
> JohnLx
>|||Thanks to everyone who responded. You confirmed what I thought with regards
to the data conversion.
Aaron, good point about the views. I hadn't thought to rebuild them but
will include that in the process.
Thanks,
--John Lennox|||On Mon, 19 Dec 2005 14:23:08 -0500, Aaron Bertrand [SQL Server MVP]
wrote:
(snip)
>In addition, you'll want to reindex and update statistics for any tables
>that have indexes/statistics on the column(s) you're changing. You'll also
>want to verify that you re-compile (and alter params and converts, where
>necessary) any stored procedures or functions that reference the columns.
>You might also want to issue sp_refreshview for any views that reference
>those tables.
Hi Aaron (and JohnLx),
And to avoid implicit conversions that might damage performance, the
next step would be to check all variable declarations, temp table
definitions and table variable definitions - they too should be changed
from varchar to nvarchar and from char to nchar.
And finally, put an N in front of all string constants in your code.
(I.e., use SET @.var = N'Text', not SET @.var = 'Text')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up th
e
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users mad
e
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have t
o
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that ha
s
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?Same as 7.0. Data pages are backuped as they are, and the transaction log re
cords generated during
the backup process are also included. If the transaction isn't finished at e
nd-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the
backup, the transaction
will be rolled back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on dis
k
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a use
r
> needs to update a record, SQL Server allows the update if the backup proce
ss
> has already backed up the record. Otherwise, SQL Server holds up the reque
st
> for a moment-long enough for the backup process to jump ahead and back up
the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, th
e
> backup process backs up the transaction log, capturing the changes users m
ade
> during the backup process. Although dynamic backup comes with a performanc
e
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have
to
> back up the transaction log several times a day, you won't be able to avoi
d
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it
is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions befor
e
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could sti
ll
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that
has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finish
es
> the backup ?

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up the
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users made
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have to
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that has
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?
Same as 7.0. Data pages are backuped as they are, and the transaction log records generated during
the backup process are also included. If the transaction isn't finished at end-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the backup, the transaction
will be rolled back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on disk
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
> needs to update a record, SQL Server allows the update if the backup process
> has already backed up the record. Otherwise, SQL Server holds up the request
> for a moment-long enough for the backup process to jump ahead and back up the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, the
> backup process backs up the transaction log, capturing the changes users made
> during the backup process. Although dynamic backup comes with a performance
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have to
> back up the transaction log several times a day, you won't be able to avoid
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions before
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could still
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
> the backup ?

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up the
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users made
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have to
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that has
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?Same as 7.0. Data pages are backuped as they are, and the transaction log records generated during
the backup process are also included. If the transaction isn't finished at end-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the backup, the transaction
will be rolled back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on disk
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
> needs to update a record, SQL Server allows the update if the backup process
> has already backed up the record. Otherwise, SQL Server holds up the request
> for a moment-long enough for the backup process to jump ahead and back up the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, the
> backup process backs up the transaction log, capturing the changes users made
> during the backup process. Although dynamic backup comes with a performance
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have to
> back up the transaction log several times a day, you won't be able to avoid
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions before
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could still
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
> the backup ?

Data Base Backup & Restore

We are in the process of migrating an application from an
Oracle data base to a SQL Server data base.
My DBA is telling me that with SQL Server he will not be
able to do a backup & restore at the 'alias' level like
he can now do with Oracle. He says he will have to do
an entire data base backup.
Is this true?In our current environment with our databases on Oracle,
we have one physical db for 1099 tax info. When it comes
to keeping the tax years separate, we set up different
aliases for each year...1099_01, 1099_02, etc. By doing
this, we are able to backup and restore at the alias
level. In case there is a problem with a
certain 'alias', we are able to restore just that
specific alias and not the entire db.
We would like to be able to do the same thing using SQL
Server, but it sounds like we may not be able to.
Any suggestions'
>--Original Message--
>Yes,
>Assuming you mean a database backup. He can also copy
out individual tables using something like DTS or BCP if
required.
>He can also do file or filegroup backups to subset
things if the db is very large, but frequently full db
backups are more than sufficient.
>What are you trying to achieve?
>Mike John
>"Don" <Don@.nomail.com> wrote in message news:08ed01c38211
$b2eb5d10$a101280a@.phx.gbl...
>> We are in the process of migrating an application from
an
>> Oracle data base to a SQL Server data base.
>> My DBA is telling me that with SQL Server he will not
be
>> able to do a backup & restore at the 'alias' level
like
>> he can now do with Oracle. He says he will have to
do
>> an entire data base backup.
>> Is this true?
>.
>

Data

I am in the planning process of upgrading the hardware on
our cluster. I need info/advice/suggestions on how to go
about this.
I am running an active/active cluster on 2 ML370 servers.
They are running Windows 2000 Advanced Server/SP2, MSSQL
2000 Enterprise/SP2.
I want to know if all I have to do is Failover all
resources from Server2 to Server1, then evict Server2?
Then do all I have to do is run the cluster install? I am
not sure what needs to be done with the SQL instance from
Server2? Do I uninstall it or what? I hope this is clear
enough. Any ideas please let me know.See if this helps -
http://support.microsoft.com/defaul...kb;en-us;243218
Ray Higdon MCSE, MCDBA, CCNA
--
"Viraj" <shethvir@.msu.edu> wrote in message
news:7cf701c3e92f$67e03ef0$a301280a@.phx.gbl...

quote:

---

> I am in the planning process of upgrading the hardware on
> our cluster. I need info/advice/suggestions on how to go
> about this.
> I am running an active/active cluster on 2 ML370 servers.
> They are running Windows 2000 Advanced Server/SP2, MSSQL
> 2000 Enterprise/SP2.
> I want to know if all I have to do is Failover all
> resources from Server2 to Server1, then evict Server2?
> Then do all I have to do is run the cluster install? I am
> not sure what needs to be done with the SQL instance from
> Server2? Do I uninstall it or what? I hope this is clear
> enough. Any ideas please let me know.

---

Daily job runs slow after upgrade to 2005.

We have a process that uses XMLSpy to import an xml file of products/pricing
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/pricing
> daily. We receive this file through an ftp site. This process usually ran
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>> That's not much to go on Tim. I don't know what this XMLSpy does but even
>> 15 minutes is way to long a time to import just a few thousand rows. I
>> would think 15 seconds would be too long. Can you give some more details
>> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
>> --
>> Andrew J. Kelly SQL MVP
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so
>> different?
>> The database used is set to compatibility level 90, simple recovery
>> mode, ansi defaults. We've tried turning off create and udpate of
>> statistics as this is temporary data used for updating other tables.
>> Nothing seems to make a difference. I just bet there's something simple
>> I'm missing here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
>>
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
>> We have a process that uses XMLSpy to import an xml file of
>> products/pricing daily. We receive this file through an ftp site. This
>> process usually ran in 15-20 min with SQL 2000. It is now running about
>> 2 1/2 hours which is unacceptable. Any ideas what could be so different?
>> The database used is set to compatibility level 90, simple recovery mode,
>> ansi defaults. We've tried turning off create and udpate of statistics
>> as this is temporary data used for updating other tables. Nothing seems
>> to make a difference. I just bet there's something simple I'm missing
>> here..
>> There is only about 8 tables, 14k-15k rows each and no indexes anywhere
>> we add those after the import.
>> Many thanks for any input !!
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Daily job runs slow after upgrade to 2005.

We have a process that uses XMLSpy to import an xml file of products/pricing
daily. We receive this file through an ftp site. This process usually ran
in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
unacceptable. Any ideas what could be so different?
The database used is set to compatibility level 90, simple recovery mode,
ansi defaults. We've tried turning off create and udpate of statistics as
this is temporary data used for updating other tables. Nothing seems to
make a difference. I just bet there's something simple I'm missing here..
There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
add those after the import.
Many thanks for any input !!That's not much to go on Tim. I don't know what this XMLSpy does but even 15
minutes is way to long a time to import just a few thousand rows. I would
think 15 seconds would be too long. Can you give some more details on
exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
> We have a process that uses XMLSpy to import an xml file of
> products/pricing daily. We receive this file through an ftp site. This
> process usually ran in 15-20 min with SQL 2000. It is now running about 2
> 1/2 hours which is unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>|||We receive product updates in an XML file. We use XMLSpy because the
company we receive the file from does...just so we can skip any
inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
connection and then creates the necessary tables in an empty database. It
then loads all the product rows from the XML file into the respective
tables. 15 minutes was reasonable we think as XMLSpy is doing an enourmous
amount of string processing and then inserting rows from a workstation into
the server.
XMLSpy is also deriving keys from related data that we specify. These keys
are included in the data but they are just integer columns at that point.
We create the actual indexes after the import is done. This is just another
reason for 15 minutes vs 15 seconds.
At any rate, it does boil down to just reading through an XML file and
inserting rows into a table. I'm not wanting to be skimpy on details but
that's about all there is to it. This isn't a procedure I have control of,
it is a 3rd party COM object we call into to do the import so I cannot
address the NOCOUNT issue. I do not believe it to be the 3rd parties issue
either as it worked fine until now. We've tried this on both SQL2k5 32 and
64 bit servers with the same results.
I've double-checked the recovery method is set to simple...compatibility is
90. Even turned off auto create/update statistics. Would it help any to
use bulk recovery mode? We don't need ANY logging of this data it is
completely transient in nature.
Thanks for responding!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
> That's not much to go on Tim. I don't know what this XMLSpy does but even
> 15 minutes is way to long a time to import just a few thousand rows. I
> would think 15 seconds would be too long. Can you give some more details
> on exactly what it is doing? Do you have SET NOCOUNT ON in the job step?
> --
> Andrew J. Kelly SQL MVP
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23yy6klgoGHA.5084@.TK2MSFTNGP03.phx.gbl...
>|||Tim Greenwood wrote:
> We have a process that uses XMLSpy to import an xml file of products/prici
ng
> daily. We receive this file through an ftp site. This process usually ra
n
> in 15-20 min with SQL 2000. It is now running about 2 1/2 hours which is
> unacceptable. Any ideas what could be so different?
> The database used is set to compatibility level 90, simple recovery mode,
> ansi defaults. We've tried turning off create and udpate of statistics as
> this is temporary data used for updating other tables. Nothing seems to
> make a difference. I just bet there's something simple I'm missing here..
> There is only about 8 tables, 14k-15k rows each and no indexes anywhere we
> add those after the import.
> Many thanks for any input !!
>
Ignoring the XML aspect for now, start with the basics, look at Perfmon
while this process is running. Look at Avg disk queue lengths, CPU %.
What other databases are hosted on this machine? Is the transaction log
on the same volume as the data file? Where is TEMPDB? Multi-processor
machine?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Is this a new server or OS as well? Is this SQLSpy running ont he same
server as SQL Server? If so are you sure there is enough memory for both?
Have you looked at profiler and perfmon to see what may be going on? You
need to narrow down the possibilities otherwise it's really hard to say.
These may help:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OuMtLKioGHA.4024@.TK2MSFTNGP03.phx.gbl...
> We receive product updates in an XML file. We use XMLSpy because the
> company we receive the file from does...just so we can skip any
> inconsistencies. XMLSpy uses either SQLOLEDB or SQL Native Client
> connection and then creates the necessary tables in an empty database. It
> then loads all the product rows from the XML file into the respective
> tables. 15 minutes was reasonable we think as XMLSpy is doing an
> enourmous amount of string processing and then inserting rows from a
> workstation into the server.
> XMLSpy is also deriving keys from related data that we specify. These
> keys are included in the data but they are just integer columns at that
> point. We create the actual indexes after the import is done. This is
> just another reason for 15 minutes vs 15 seconds.
> At any rate, it does boil down to just reading through an XML file and
> inserting rows into a table. I'm not wanting to be skimpy on details but
> that's about all there is to it. This isn't a procedure I have control
> of, it is a 3rd party COM object we call into to do the import so I cannot
> address the NOCOUNT issue. I do not believe it to be the 3rd parties
> issue either as it worked fine until now. We've tried this on both SQL2k5
> 32 and 64 bit servers with the same results.
> I've double-checked the recovery method is set to simple...compatibility
> is 90. Even turned off auto create/update statistics. Would it help any
> to use bulk recovery mode? We don't need ANY logging of this data it is
> completely transient in nature.
> Thanks for responding!!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OjwfA7hoGHA.148@.TK2MSFTNGP04.phx.gbl...
>|||Yes I will run profiler on monday while this is running. To answer your
questions...we have 7 db's on this server. All tlogs are on their own
mirrored set of spindles and tempdb is on it's own mirrored set as well. It
is a dual 64-bit machine. 64bit sql2005 and 64bit windows server 2003
enterprise.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OY4gPrioGHA.4036@.TK2MSFTNGP05.phx.gbl...
> Tim Greenwood wrote:
> Ignoring the XML aspect for now, start with the basics, look at Perfmon
> while this process is running. Look at Avg disk queue lengths, CPU %.
> What other databases are hosted on this machine? Is the transaction log
> on the same volume as the data file? Where is TEMPDB? Multi-processor
> machine?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

daily backup and log shipping

Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba
Zorba,
A full database backup does not truncate the transaction log. When you start log shipping, you can make as many full backups of the primary database as you want - it will not affect log shipping. However, you cannot make both a full database backup and a transaction log backup of the same database at the same time.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Zorba" <nospam@.nonexistent> wrote in message news:OX0Z6LemEHA.3336@.TK2MSFTNGP10.phx.gbl...
Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba

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