We have several large reports that access tables with millions of records. In
such cases we expect that blocking may occur; however, the report server will
perform specific commands that lock the database as well. These commands
include UPDATE,DELETE,AWAITING COMMAND. We have a program running to monitor
blocking on our databases so we are able to see when the blocking occurs but
we are unable to find the cause of this problem. The program allows us to see
when a user is causing blocking by running a large report or performing some
other action. The blocking that concerns us is not generated by a user but by
the Report Server itself. It appears that the Report Server performs these
actions after a large report is run or after a report is deployed to the
server.I forgot to ask our question. Does anyone know what process is causing the
blocking and what can we do about it?|||Are you sure this activity you are seeing isn't against its own database.
Report server uses its own database extensively but that should not affect
the database you are reporting against. I go against 1-10 million row tables
all the time, I have an 80 million row table as well. Report server has
never caused a problem with any other application going against the
database. There is no way that Report server is doing an update or delete
against your database. The reports run under the credentials you apply them.
The credentials I use is a readonly account on SQL Server (and Sybase, I
report against both).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> We have several large reports that access tables with millions of records.
> In
> such cases we expect that blocking may occur; however, the report server
> will
> perform specific commands that lock the database as well. These commands
> include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> monitor
> blocking on our databases so we are able to see when the blocking occurs
> but
> we are unable to find the cause of this problem. The program allows us to
> see
> when a user is causing blocking by running a large report or performing
> some
> other action. The blocking that concerns us is not generated by a user but
> by
> the Report Server itself. It appears that the Report Server performs these
> actions after a large report is run or after a report is deployed to the
> server.|||Our production databases are on the same server as the report server so when
the report server causes blocking in the master database we are getting
blocking issues server-wide. Is there a way to fine tune the processes that
are blocking or schedule them to run during down times?
"Bruce L-C [MVP]" wrote:
> Are you sure this activity you are seeing isn't against its own database.
> Report server uses its own database extensively but that should not affect
> the database you are reporting against. I go against 1-10 million row tables
> all the time, I have an 80 million row table as well. Report server has
> never caused a problem with any other application going against the
> database. There is no way that Report server is doing an update or delete
> against your database. The reports run under the credentials you apply them.
> The credentials I use is a readonly account on SQL Server (and Sybase, I
> report against both).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > We have several large reports that access tables with millions of records.
> > In
> > such cases we expect that blocking may occur; however, the report server
> > will
> > perform specific commands that lock the database as well. These commands
> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > monitor
> > blocking on our databases so we are able to see when the blocking occurs
> > but
> > we are unable to find the cause of this problem. The program allows us to
> > see
> > when a user is causing blocking by running a large report or performing
> > some
> > other action. The blocking that concerns us is not generated by a user but
> > by
> > the Report Server itself. It appears that the Report Server performs these
> > actions after a large report is run or after a report is deployed to the
> > server.
>
>|||"Bruce L-C [MVP]" wrote:
> Are you sure this activity you are seeing isn't against its own database.
> Report server uses its own database extensively but that should not affect
> the database you are reporting against. I go against 1-10 million row tables
> all the time, I have an 80 million row table as well. Report server has
> never caused a problem with any other application going against the
> database. There is no way that Report server is doing an update or delete
> against your database. The reports run under the credentials you apply them.
> The credentials I use is a readonly account on SQL Server (and Sybase, I
> report against both).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > We have several large reports that access tables with millions of records.
> > In
> > such cases we expect that blocking may occur; however, the report server
> > will
> > perform specific commands that lock the database as well. These commands
> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > monitor
> > blocking on our databases so we are able to see when the blocking occurs
> > but
> > we are unable to find the cause of this problem. The program allows us to
> > see
> > when a user is causing blocking by running a large report or performing
> > some
> > other action. The blocking that concerns us is not generated by a user but
> > by
> > the Report Server itself. It appears that the Report Server performs these
> > actions after a large report is run or after a report is deployed to the
> > server.
>
>|||Let me rephrase the whole scenario.
All of our production databases are on the same server as our Reporting
Services database. The reporting services database is causing blocking that
we monitor via our app. Blocking occurs at random times during the day that
do not directly corresponding to pulling a report. For example, if I deploy a
new report to the server, I may get a blocking indicator that the reporting
server is AWAITING COMMAND, or UPDATE. I have had blocking issues when
clicking the Print Preview button as well. It is not necessarily the report
data that is causing the blocking as I mentioned earlier, but it is some
process that the Reporting Services database is performing.
Without getting to far into the Reporting Services database structure, can
anyone tell me what processes are being performed by Reporting Services that
could cause this blocking? Does the database create a cache when you deploy a
new report or print preview a report?
Anyone? Anyone? Beuhler?
If no one knows, then we can live with it; however, it would be nice to
eliminate the blocking altogether.
"BootieNH" wrote:
>
> "Bruce L-C [MVP]" wrote:
> > Are you sure this activity you are seeing isn't against its own database.
> > Report server uses its own database extensively but that should not affect
> > the database you are reporting against. I go against 1-10 million row tables
> > all the time, I have an 80 million row table as well. Report server has
> > never caused a problem with any other application going against the
> > database. There is no way that Report server is doing an update or delete
> > against your database. The reports run under the credentials you apply them.
> > The credentials I use is a readonly account on SQL Server (and Sybase, I
> > report against both).
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> > news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > > We have several large reports that access tables with millions of records.
> > > In
> > > such cases we expect that blocking may occur; however, the report server
> > > will
> > > perform specific commands that lock the database as well. These commands
> > > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > > monitor
> > > blocking on our databases so we are able to see when the blocking occurs
> > > but
> > > we are unable to find the cause of this problem. The program allows us to
> > > see
> > > when a user is causing blocking by running a large report or performing
> > > some
> > > other action. The blocking that concerns us is not generated by a user but
> > > by
> > > the Report Server itself. It appears that the Report Server performs these
> > > actions after a large report is run or after a report is deployed to the
> > > server.
> >
> >
> >|||Bruce, excuse me for hijacking this thread, but I am currently
troubleshooting a deadlock problem occurring between our application and
ReportServer. I've isolated that our application process, which is invoking
a stored procedure and passing XML to perform either an INSERT or UPDATE, is
deadlocking with a report SELECT query against a view of that database.
Examining the locking detail shows that our UPDATE query is holding an
exclusive intent lock (LCK_M_IX) on the table [inside a transaction] in order
to perform the update, while Report Services is holding a shared lock
(LCK_M_S) on a SELECT of a view. These locks are incompatible, so a deadlock
occurs, and the lower priority transaction is terminated. At least that's my
understanding. I'm wondering how I need to avoid this problem. I'm
surprised I'm not seeing more threads on this topic, I would have expected
more people to be experiencing this. Is it necessary to configure a lock
timeout for SQL Server to avoid this issue? Thanks!
"Bruce L-C [MVP]" wrote:
> Are you sure this activity you are seeing isn't against its own database.
> Report server uses its own database extensively but that should not affect
> the database you are reporting against. I go against 1-10 million row tables
> all the time, I have an 80 million row table as well. Report server has
> never caused a problem with any other application going against the
> database. There is no way that Report server is doing an update or delete
> against your database. The reports run under the credentials you apply them.
> The credentials I use is a readonly account on SQL Server (and Sybase, I
> report against both).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > We have several large reports that access tables with millions of records.
> > In
> > such cases we expect that blocking may occur; however, the report server
> > will
> > perform specific commands that lock the database as well. These commands
> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > monitor
> > blocking on our databases so we are able to see when the blocking occurs
> > but
> > we are unable to find the cause of this problem. The program allows us to
> > see
> > when a user is causing blocking by running a large report or performing
> > some
> > other action. The blocking that concerns us is not generated by a user but
> > by
> > the Report Server itself. It appears that the Report Server performs these
> > actions after a large report is run or after a report is deployed to the
> > server.
>
>|||Clark, I've spent the last 3 days muddling through extensive locking detail
trying to debug my issue. I think Bruce was implying that Report Server is
doing tons of stuff in its own database, and that shouldn't conflict with
your database. And of course your reports would only be running SELECT
queries. But I'm currently debugging locking issues with exactly this
scenario. Have you looked in detail at the SPIDs of the blocking? Is it
causing behavior problems? I'm trying to figure this out too, and it's not
fun.
"Clark Kent" wrote:
> Let me rephrase the whole scenario.
> All of our production databases are on the same server as our Reporting
> Services database. The reporting services database is causing blocking that
> we monitor via our app. Blocking occurs at random times during the day that
> do not directly corresponding to pulling a report. For example, if I deploy a
> new report to the server, I may get a blocking indicator that the reporting
> server is AWAITING COMMAND, or UPDATE. I have had blocking issues when
> clicking the Print Preview button as well. It is not necessarily the report
> data that is causing the blocking as I mentioned earlier, but it is some
> process that the Reporting Services database is performing.
> Without getting to far into the Reporting Services database structure, can
> anyone tell me what processes are being performed by Reporting Services that
> could cause this blocking? Does the database create a cache when you deploy a
> new report or print preview a report?
> Anyone? Anyone? Beuhler?
> If no one knows, then we can live with it; however, it would be nice to
> eliminate the blocking altogether.
> "BootieNH" wrote:
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Are you sure this activity you are seeing isn't against its own database.
> > > Report server uses its own database extensively but that should not affect
> > > the database you are reporting against. I go against 1-10 million row tables
> > > all the time, I have an 80 million row table as well. Report server has
> > > never caused a problem with any other application going against the
> > > database. There is no way that Report server is doing an update or delete
> > > against your database. The reports run under the credentials you apply them.
> > > The credentials I use is a readonly account on SQL Server (and Sybase, I
> > > report against both).
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> > > news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > > > We have several large reports that access tables with millions of records.
> > > > In
> > > > such cases we expect that blocking may occur; however, the report server
> > > > will
> > > > perform specific commands that lock the database as well. These commands
> > > > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > > > monitor
> > > > blocking on our databases so we are able to see when the blocking occurs
> > > > but
> > > > we are unable to find the cause of this problem. The program allows us to
> > > > see
> > > > when a user is causing blocking by running a large report or performing
> > > > some
> > > > other action. The blocking that concerns us is not generated by a user but
> > > > by
> > > > the Report Server itself. It appears that the Report Server performs these
> > > > actions after a large report is run or after a report is deployed to the
> > > > server.
> > >
> > >
> > >|||I was hoping that I could avoid getting into the spid detail, but I will look
into it further. As for interference, I can't recall specific instances of
non-reporting service transactions being blocked, but I am pretty sure it has
happened. I will have to pay closer attention to it to make sure. I was
hoping to find a quick answer to all this, but it doesn't look like that will
happen soon. It appears that reporting services is blocking itself the
majority of the time. I will let you know if I find out anything useful.
"BootieNH" wrote:
> Clark, I've spent the last 3 days muddling through extensive locking detail
> trying to debug my issue. I think Bruce was implying that Report Server is
> doing tons of stuff in its own database, and that shouldn't conflict with
> your database. And of course your reports would only be running SELECT
> queries. But I'm currently debugging locking issues with exactly this
> scenario. Have you looked in detail at the SPIDs of the blocking? Is it
> causing behavior problems? I'm trying to figure this out too, and it's not
> fun.
> "Clark Kent" wrote:
> > Let me rephrase the whole scenario.
> >
> > All of our production databases are on the same server as our Reporting
> > Services database. The reporting services database is causing blocking that
> > we monitor via our app. Blocking occurs at random times during the day that
> > do not directly corresponding to pulling a report. For example, if I deploy a
> > new report to the server, I may get a blocking indicator that the reporting
> > server is AWAITING COMMAND, or UPDATE. I have had blocking issues when
> > clicking the Print Preview button as well. It is not necessarily the report
> > data that is causing the blocking as I mentioned earlier, but it is some
> > process that the Reporting Services database is performing.
> >
> > Without getting to far into the Reporting Services database structure, can
> > anyone tell me what processes are being performed by Reporting Services that
> > could cause this blocking? Does the database create a cache when you deploy a
> > new report or print preview a report?
> >
> > Anyone? Anyone? Beuhler?
> >
> > If no one knows, then we can live with it; however, it would be nice to
> > eliminate the blocking altogether.
> >
> > "BootieNH" wrote:
> >
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Are you sure this activity you are seeing isn't against its own database.
> > > > Report server uses its own database extensively but that should not affect
> > > > the database you are reporting against. I go against 1-10 million row tables
> > > > all the time, I have an 80 million row table as well. Report server has
> > > > never caused a problem with any other application going against the
> > > > database. There is no way that Report server is doing an update or delete
> > > > against your database. The reports run under the credentials you apply them.
> > > > The credentials I use is a readonly account on SQL Server (and Sybase, I
> > > > report against both).
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> > > > news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > > > > We have several large reports that access tables with millions of records.
> > > > > In
> > > > > such cases we expect that blocking may occur; however, the report server
> > > > > will
> > > > > perform specific commands that lock the database as well. These commands
> > > > > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > > > > monitor
> > > > > blocking on our databases so we are able to see when the blocking occurs
> > > > > but
> > > > > we are unable to find the cause of this problem. The program allows us to
> > > > > see
> > > > > when a user is causing blocking by running a large report or performing
> > > > > some
> > > > > other action. The blocking that concerns us is not generated by a user but
> > > > > by
> > > > > the Report Server itself. It appears that the Report Server performs these
> > > > > actions after a large report is run or after a report is deployed to the
> > > > > server.
> > > >
> > > >
> > > >|||This is really not a RS issue but a generic database question. So I posted
your question on the private SQL Server MVP newsgroup and got this answer
back for you:
>>>>>>>>>
Don't use lock timeouts or you will end up with broken transactions. I
suggest using the NOLOCK hint on the select query. You may end up with
imperfect data, but this may not be critical depending on your application
and report. For example, if a weekly call center activity report is off by
one or two calls, it really doesn't matter.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BootieNH" <BootieNH@.discussions.microsoft.com> wrote in message
news:9B48D882-7479-4F90-BC8A-5D7308232496@.microsoft.com...
> Bruce, excuse me for hijacking this thread, but I am currently
> troubleshooting a deadlock problem occurring between our application and
> ReportServer. I've isolated that our application process, which is
> invoking
> a stored procedure and passing XML to perform either an INSERT or UPDATE,
> is
> deadlocking with a report SELECT query against a view of that database.
> Examining the locking detail shows that our UPDATE query is holding an
> exclusive intent lock (LCK_M_IX) on the table [inside a transaction] in
> order
> to perform the update, while Report Services is holding a shared lock
> (LCK_M_S) on a SELECT of a view. These locks are incompatible, so a
> deadlock
> occurs, and the lower priority transaction is terminated. At least that's
> my
> understanding. I'm wondering how I need to avoid this problem. I'm
> surprised I'm not seeing more threads on this topic, I would have expected
> more people to be experiencing this. Is it necessary to configure a lock
> timeout for SQL Server to avoid this issue? Thanks!
> "Bruce L-C [MVP]" wrote:
>> Are you sure this activity you are seeing isn't against its own database.
>> Report server uses its own database extensively but that should not
>> affect
>> the database you are reporting against. I go against 1-10 million row
>> tables
>> all the time, I have an 80 million row table as well. Report server has
>> never caused a problem with any other application going against the
>> database. There is no way that Report server is doing an update or delete
>> against your database. The reports run under the credentials you apply
>> them.
>> The credentials I use is a readonly account on SQL Server (and Sybase, I
>> report against both).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
>> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
>> > We have several large reports that access tables with millions of
>> > records.
>> > In
>> > such cases we expect that blocking may occur; however, the report
>> > server
>> > will
>> > perform specific commands that lock the database as well. These
>> > commands
>> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
>> > monitor
>> > blocking on our databases so we are able to see when the blocking
>> > occurs
>> > but
>> > we are unable to find the cause of this problem. The program allows us
>> > to
>> > see
>> > when a user is causing blocking by running a large report or performing
>> > some
>> > other action. The blocking that concerns us is not generated by a user
>> > but
>> > by
>> > the Report Server itself. It appears that the Report Server performs
>> > these
>> > actions after a large report is run or after a report is deployed to
>> > the
>> > server.
>>sql
Showing posts with label occur. Show all posts
Showing posts with label occur. Show all posts
Tuesday, March 27, 2012
Saturday, February 25, 2012
Data Warehouse Load Hints?
(SQL 2000) So if we are loading large tables while no one else is logged in
and no other processes will occur, should we provide hints for the table
inserts and updates for a performance boost? If so, which hints? Can any
help avoid deadlocks? And will this be different for 2005?
Does setting the db to singleuser help speed-up updates?
Thanks
Greg CA deadlock is when two processes have locked resources (typically in an
uncommitted transaction) that the other needs to continue. How would this
happen if the ETL is the only process running?
http://support.microsoft.com/defaul...kb;en-us;169960
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
As for maximizing the performance of your ETL, I would reccomend the "bulk
load" method, becuase it supports features such as minimal transaction
logging.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
Setting the database to DBO Use Only would also be a good idea; it may
improve performance, but mostly to insure that noone or nothing else logs
in.
"gc" <nospam@.hotmail.com> wrote in message
news:Xns977C861DB2E90nospam@.24.93.43.121...
> (SQL 2000) So if we are loading large tables while no one else is logged
> in
> and no other processes will occur, should we provide hints for the table
> inserts and updates for a performance boost? If so, which hints? Can any
> help avoid deadlocks? And will this be different for 2005?
> Does setting the db to singleuser help speed-up updates?
> Thanks
> Greg C
>
>|||Just a couple of tips that I've learned:
1. If you have a clustered index, make sure that it's located on a
monotonically increasing value. This will minimize page splitting.
2. The clustered index does not have to be your primary key; in fact,
if you use a natural key, then it should not be. Our data is very date
and time sensitive, so I index the date and time of load; others use a
sequential numbering system (like an identity column), but I needed the
date of load for other reasons, and I prefer not to add columns that
have no meaning.
3 Depending on the size of your data, it may be more appropriate to
drop all of your non-clustered indexes , load the data, and rebuild the
indexes.
HTH,
Stu|||"JT" <someone@.microsoft.com> wrote in
news:uTboj28PGHA.720@.TK2MSFTNGP14.phx.gbl:
Well...you are asking someone who really needs to ask the question. Did
that make sense? :-) I am not a dba, and have learned through trial and
error what seems to work, at least most of the time.
I thought 'bulk load' recovery was just beneficial when importing text
files or using 'select into', no?
In our ETL, we have a number of procedures (which can call other
procedures) to test and process the data after the raw data is imported
w/ DTS. The DTS part is always fast, some of the subsequent processing
will lock-up periodically...I assume this would be a deadlock or lock
escalation? (When I look at the locks I will see many pages on one
object). It just happened to occurr on one machine...I stopped and
restarted w/o mods to the code and the next time it processed w/o
halting.
I drop indexes for the DTS, for the data transformation I optimize sql
for performance, individual statements are broken into transactions,
most of the largest updates/inserts are broken into smaller transactions,
temp tables are used when needed, I aggressively manage the multiple data
and log files on multiple raids and hard drives. But I have not been
able to understand the hints but more importantly why a process will run
fine most times then decide to freeze once (btw, I also check for open
transactions to make certain that there was no minor error leaving an
open tran).
thanks for the references
> A deadlock is when two processes have locked resources (typically in
> an uncommitted transaction) that the other needs to continue. How
> would this happen if the ETL is the only process running?
> http://support.microsoft.com/defaul...kb;en-us;169960
> http://msdn.microsoft.com/library/default.asp
?url=/library/en-us/acdata/ac_8_con_7a_3hdf.asp As for maximizing the
performance of your ETL, I
> would reccomend the "bulk load" method, becuase it supports features
> such as minimal transaction logging.
http://www.microsoft.com/technet/pr...tain/incbulkloa
d.mspx Setting the database to DBO Use Only would also be a good
> idea; it may improve performance, but mostly to insure that noone or
> nothing else logs in.
> "gc" <nospam@.hotmail.com> wrote in message
> news:Xns977C861DB2E90nospam@.24.93.43.121...
>
>|||"Stu" <stuart.ainsworth@.gmail.com> wrote in news:1141535576.352832.290970
@.e56g2000cwe.googlegroups.com:
> 1. If you have a clustered index, make sure that it's located on a
> monotonically increasing value. This will minimize page splitting.
Not sure that I understand this one.
If I load some data into the field of the clustered key, are you saying
that it makes a difference what order that I load the records?
If so, is that different whether the clustered index is the primary key or
not?
> 2. The clustered index does not have to be your primary key; in fact,
> if you use a natural key, then it should not be. Our data is very date
> and time sensitive, so I index the date and time of load; others use a
> sequential numbering system (like an identity column), but I needed the
> date of load for other reasons, and I prefer not to add columns that
> have no meaning.
I use natural keys for something like a client id, but convert almost all
of the dimensions to surrogate (identity) keys.
> 3 Depending on the size of your data, it may be more appropriate to
> drop all of your non-clustered indexes , load the data, and rebuild the
> indexes.
>
I do this everytime. 1.5 out of 3... :-)|||You can know if a process if being blocked by another process by executing
sp_who2 and seeing if the [blkby] column for a spid (process id) contains
the spid of another blocking process.
When importing large amounts of data, use the bulk copy program (BCP.EXE) or
BULK INSERT command.
http://msdn.microsoft.com/library/d...>
bcp_9esz.asp
Try reducing the batch size of your bulk copy process:
http://support.microsoft.com/defaul...=kb;en-us;81339
Consider what impact transaction logging is having on your process. There
are a few conditions (such as dropping indexes and specifying the TABLOCK
hint) that are required for minimal logging to take effect.
http://support.microsoft.com/defaul...=kb;en-us;59462
http://support.microsoft.com/defaul...kb;en-us;110139
"gc" <nospam@.hotmail.com> wrote in message
news:Xns977CEEB88B493nospam@.24.93.43.121...
> "JT" <someone@.microsoft.com> wrote in
> news:uTboj28PGHA.720@.TK2MSFTNGP14.phx.gbl:
> Well...you are asking someone who really needs to ask the question. Did
> that make sense? :-) I am not a dba, and have learned through trial and
> error what seems to work, at least most of the time.
> I thought 'bulk load' recovery was just beneficial when importing text
> files or using 'select into', no?
> In our ETL, we have a number of procedures (which can call other
> procedures) to test and process the data after the raw data is imported
> w/ DTS. The DTS part is always fast, some of the subsequent processing
> will lock-up periodically...I assume this would be a deadlock or lock
> escalation? (When I look at the locks I will see many pages on one
> object). It just happened to occurr on one machine...I stopped and
> restarted w/o mods to the code and the next time it processed w/o
> halting.
> I drop indexes for the DTS, for the data transformation I optimize sql
> for performance, individual statements are broken into transactions,
> most of the largest updates/inserts are broken into smaller transactions,
> temp tables are used when needed, I aggressively manage the multiple data
> and log files on multiple raids and hard drives. But I have not been
> able to understand the hints but more importantly why a process will run
> fine most times then decide to freeze once (btw, I also check for open
> transactions to make certain that there was no minor error leaving an
> open tran).
> thanks for the references
>
> ?url=/library/en-us/acdata/ac_8_con_7a_3hdf.asp As for maximizing the
> performance of your ETL, I
> http://www.microsoft.com/technet/pr...tain/incbulkloa
> d.mspx Setting the database to DBO Use Only would also be a good
>|||Ugh. Page splitting is difficult to explain; think of your table as a
blank notebook. The clustered index is kind of like a page number for
your other indexes to go and retrieve information; it's a way for the
optimizer to go figure out where row X is located in your table.
As you write data to your notebook, it begins to fill up each page. As
you continue to write, it skips to the next page; if your clustered
index does not increase in a sequential fashion, when the optimizer
encounters a a value that is out of order, it has to split the page at
the insertion point, and move records below that point on a page to a
new page; that's why you should cluster on a sequential value that is
independent of the order of the data outside of the warehouse. That's
why I recommend a datetime representation so it increases without
splitting the pages.
If your primary key is a natural key (like a combination of clientID
and rowID), then the likelihood is that your rows will be out of order
as they load into the target database, and hence some reordering will
be necessary if you cluster on that.
Clear as mud?
Stu|||Also consider that if a good candidate for a clustered index cannot be
found, then best not to implement one.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1141539867.013564.240070@.e56g2000cwe.googlegroups.com...
> Ugh. Page splitting is difficult to explain; think of your table as a
> blank notebook. The clustered index is kind of like a page number for
> your other indexes to go and retrieve information; it's a way for the
> optimizer to go figure out where row X is located in your table.
> As you write data to your notebook, it begins to fill up each page. As
> you continue to write, it skips to the next page; if your clustered
> index does not increase in a sequential fashion, when the optimizer
> encounters a a value that is out of order, it has to split the page at
> the insertion point, and move records below that point on a page to a
> new page; that's why you should cluster on a sequential value that is
> independent of the order of the data outside of the warehouse. That's
> why I recommend a datetime representation so it increases without
> splitting the pages.
> If your primary key is a natural key (like a combination of clientID
> and rowID), then the likelihood is that your rows will be out of order
> as they load into the target database, and hence some reordering will
> be necessary if you cluster on that.
> Clear as mud?
> Stu
>|||gc (nospam@.hotmail.com) writes:
> In our ETL, we have a number of procedures (which can call other
> procedures) to test and process the data after the raw data is imported
> w/ DTS. The DTS part is always fast, some of the subsequent processing
> will lock-up periodically...I assume this would be a deadlock or lock
> escalation? (When I look at the locks I will see many pages on one
> object). It just happened to occurr on one machine...I stopped and
> restarted w/o mods to the code and the next time it processed w/o
> halting.
A deadlock is when two (or more) processes are waiting for each other
to release resources. SQL Server detects a deadlock, and will select one
of the processes as a deadlock victim and cancel execution for that
process, so that at least one of the processes can continue working.
If you processing "locks up" this can be due to blocking, but also due
to long-running queries. With the information you have provided, it is
difficult to tell. As JT said, use sp_who2 to check for blocking.
Another issue to watch out for is auto-grow of the database. By default,
SQL Server auto-grows a database with 10% when you run out of space. If
your database is huge - and data warehouses often are - then 10% can
take quite some to time grow. This can be dealt with expanding the database
to a reasonable size in advance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
and no other processes will occur, should we provide hints for the table
inserts and updates for a performance boost? If so, which hints? Can any
help avoid deadlocks? And will this be different for 2005?
Does setting the db to singleuser help speed-up updates?
Thanks
Greg CA deadlock is when two processes have locked resources (typically in an
uncommitted transaction) that the other needs to continue. How would this
happen if the ETL is the only process running?
http://support.microsoft.com/defaul...kb;en-us;169960
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
As for maximizing the performance of your ETL, I would reccomend the "bulk
load" method, becuase it supports features such as minimal transaction
logging.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
Setting the database to DBO Use Only would also be a good idea; it may
improve performance, but mostly to insure that noone or nothing else logs
in.
"gc" <nospam@.hotmail.com> wrote in message
news:Xns977C861DB2E90nospam@.24.93.43.121...
> (SQL 2000) So if we are loading large tables while no one else is logged
> in
> and no other processes will occur, should we provide hints for the table
> inserts and updates for a performance boost? If so, which hints? Can any
> help avoid deadlocks? And will this be different for 2005?
> Does setting the db to singleuser help speed-up updates?
> Thanks
> Greg C
>
>|||Just a couple of tips that I've learned:
1. If you have a clustered index, make sure that it's located on a
monotonically increasing value. This will minimize page splitting.
2. The clustered index does not have to be your primary key; in fact,
if you use a natural key, then it should not be. Our data is very date
and time sensitive, so I index the date and time of load; others use a
sequential numbering system (like an identity column), but I needed the
date of load for other reasons, and I prefer not to add columns that
have no meaning.
3 Depending on the size of your data, it may be more appropriate to
drop all of your non-clustered indexes , load the data, and rebuild the
indexes.
HTH,
Stu|||"JT" <someone@.microsoft.com> wrote in
news:uTboj28PGHA.720@.TK2MSFTNGP14.phx.gbl:
Well...you are asking someone who really needs to ask the question. Did
that make sense? :-) I am not a dba, and have learned through trial and
error what seems to work, at least most of the time.
I thought 'bulk load' recovery was just beneficial when importing text
files or using 'select into', no?
In our ETL, we have a number of procedures (which can call other
procedures) to test and process the data after the raw data is imported
w/ DTS. The DTS part is always fast, some of the subsequent processing
will lock-up periodically...I assume this would be a deadlock or lock
escalation? (When I look at the locks I will see many pages on one
object). It just happened to occurr on one machine...I stopped and
restarted w/o mods to the code and the next time it processed w/o
halting.
I drop indexes for the DTS, for the data transformation I optimize sql
for performance, individual statements are broken into transactions,
most of the largest updates/inserts are broken into smaller transactions,
temp tables are used when needed, I aggressively manage the multiple data
and log files on multiple raids and hard drives. But I have not been
able to understand the hints but more importantly why a process will run
fine most times then decide to freeze once (btw, I also check for open
transactions to make certain that there was no minor error leaving an
open tran).
thanks for the references
> A deadlock is when two processes have locked resources (typically in
> an uncommitted transaction) that the other needs to continue. How
> would this happen if the ETL is the only process running?
> http://support.microsoft.com/defaul...kb;en-us;169960
> http://msdn.microsoft.com/library/default.asp
?url=/library/en-us/acdata/ac_8_con_7a_3hdf.asp As for maximizing the
performance of your ETL, I
> would reccomend the "bulk load" method, becuase it supports features
> such as minimal transaction logging.
http://www.microsoft.com/technet/pr...tain/incbulkloa
d.mspx Setting the database to DBO Use Only would also be a good
> idea; it may improve performance, but mostly to insure that noone or
> nothing else logs in.
> "gc" <nospam@.hotmail.com> wrote in message
> news:Xns977C861DB2E90nospam@.24.93.43.121...
>
>|||"Stu" <stuart.ainsworth@.gmail.com> wrote in news:1141535576.352832.290970
@.e56g2000cwe.googlegroups.com:
> 1. If you have a clustered index, make sure that it's located on a
> monotonically increasing value. This will minimize page splitting.
Not sure that I understand this one.
If I load some data into the field of the clustered key, are you saying
that it makes a difference what order that I load the records?
If so, is that different whether the clustered index is the primary key or
not?
> 2. The clustered index does not have to be your primary key; in fact,
> if you use a natural key, then it should not be. Our data is very date
> and time sensitive, so I index the date and time of load; others use a
> sequential numbering system (like an identity column), but I needed the
> date of load for other reasons, and I prefer not to add columns that
> have no meaning.
I use natural keys for something like a client id, but convert almost all
of the dimensions to surrogate (identity) keys.
> 3 Depending on the size of your data, it may be more appropriate to
> drop all of your non-clustered indexes , load the data, and rebuild the
> indexes.
>
I do this everytime. 1.5 out of 3... :-)|||You can know if a process if being blocked by another process by executing
sp_who2 and seeing if the [blkby] column for a spid (process id) contains
the spid of another blocking process.
When importing large amounts of data, use the bulk copy program (BCP.EXE) or
BULK INSERT command.
http://msdn.microsoft.com/library/d...>
bcp_9esz.asp
Try reducing the batch size of your bulk copy process:
http://support.microsoft.com/defaul...=kb;en-us;81339
Consider what impact transaction logging is having on your process. There
are a few conditions (such as dropping indexes and specifying the TABLOCK
hint) that are required for minimal logging to take effect.
http://support.microsoft.com/defaul...=kb;en-us;59462
http://support.microsoft.com/defaul...kb;en-us;110139
"gc" <nospam@.hotmail.com> wrote in message
news:Xns977CEEB88B493nospam@.24.93.43.121...
> "JT" <someone@.microsoft.com> wrote in
> news:uTboj28PGHA.720@.TK2MSFTNGP14.phx.gbl:
> Well...you are asking someone who really needs to ask the question. Did
> that make sense? :-) I am not a dba, and have learned through trial and
> error what seems to work, at least most of the time.
> I thought 'bulk load' recovery was just beneficial when importing text
> files or using 'select into', no?
> In our ETL, we have a number of procedures (which can call other
> procedures) to test and process the data after the raw data is imported
> w/ DTS. The DTS part is always fast, some of the subsequent processing
> will lock-up periodically...I assume this would be a deadlock or lock
> escalation? (When I look at the locks I will see many pages on one
> object). It just happened to occurr on one machine...I stopped and
> restarted w/o mods to the code and the next time it processed w/o
> halting.
> I drop indexes for the DTS, for the data transformation I optimize sql
> for performance, individual statements are broken into transactions,
> most of the largest updates/inserts are broken into smaller transactions,
> temp tables are used when needed, I aggressively manage the multiple data
> and log files on multiple raids and hard drives. But I have not been
> able to understand the hints but more importantly why a process will run
> fine most times then decide to freeze once (btw, I also check for open
> transactions to make certain that there was no minor error leaving an
> open tran).
> thanks for the references
>
> ?url=/library/en-us/acdata/ac_8_con_7a_3hdf.asp As for maximizing the
> performance of your ETL, I
> http://www.microsoft.com/technet/pr...tain/incbulkloa
> d.mspx Setting the database to DBO Use Only would also be a good
>|||Ugh. Page splitting is difficult to explain; think of your table as a
blank notebook. The clustered index is kind of like a page number for
your other indexes to go and retrieve information; it's a way for the
optimizer to go figure out where row X is located in your table.
As you write data to your notebook, it begins to fill up each page. As
you continue to write, it skips to the next page; if your clustered
index does not increase in a sequential fashion, when the optimizer
encounters a a value that is out of order, it has to split the page at
the insertion point, and move records below that point on a page to a
new page; that's why you should cluster on a sequential value that is
independent of the order of the data outside of the warehouse. That's
why I recommend a datetime representation so it increases without
splitting the pages.
If your primary key is a natural key (like a combination of clientID
and rowID), then the likelihood is that your rows will be out of order
as they load into the target database, and hence some reordering will
be necessary if you cluster on that.
Clear as mud?
Stu|||Also consider that if a good candidate for a clustered index cannot be
found, then best not to implement one.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1141539867.013564.240070@.e56g2000cwe.googlegroups.com...
> Ugh. Page splitting is difficult to explain; think of your table as a
> blank notebook. The clustered index is kind of like a page number for
> your other indexes to go and retrieve information; it's a way for the
> optimizer to go figure out where row X is located in your table.
> As you write data to your notebook, it begins to fill up each page. As
> you continue to write, it skips to the next page; if your clustered
> index does not increase in a sequential fashion, when the optimizer
> encounters a a value that is out of order, it has to split the page at
> the insertion point, and move records below that point on a page to a
> new page; that's why you should cluster on a sequential value that is
> independent of the order of the data outside of the warehouse. That's
> why I recommend a datetime representation so it increases without
> splitting the pages.
> If your primary key is a natural key (like a combination of clientID
> and rowID), then the likelihood is that your rows will be out of order
> as they load into the target database, and hence some reordering will
> be necessary if you cluster on that.
> Clear as mud?
> Stu
>|||gc (nospam@.hotmail.com) writes:
> In our ETL, we have a number of procedures (which can call other
> procedures) to test and process the data after the raw data is imported
> w/ DTS. The DTS part is always fast, some of the subsequent processing
> will lock-up periodically...I assume this would be a deadlock or lock
> escalation? (When I look at the locks I will see many pages on one
> object). It just happened to occurr on one machine...I stopped and
> restarted w/o mods to the code and the next time it processed w/o
> halting.
A deadlock is when two (or more) processes are waiting for each other
to release resources. SQL Server detects a deadlock, and will select one
of the processes as a deadlock victim and cancel execution for that
process, so that at least one of the processes can continue working.
If you processing "locks up" this can be due to blocking, but also due
to long-running queries. With the information you have provided, it is
difficult to tell. As JT said, use sp_who2 to check for blocking.
Another issue to watch out for is auto-grow of the database. By default,
SQL Server auto-grows a database with 10% when you run out of space. If
your database is huge - and data warehouses often are - then 10% can
take quite some to time grow. This can be dealt with expanding the database
to a reasonable size in advance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)