All tables under partitioned union view get locked - how to reduce?

Hi. We are using a partitioned view across a number of tables. They are
partitioned on a single column. We use the view to insert and update rows in
the underlying tables.
When loading all the rows for table XYZ through the view we find that SQL
Server is creating IX locks against all of the tables even though all the
rows are for one table only as identified by the check constraint on the
partitioned column.
Is there any way to get SQL Server to lock only the table that will be
affected?
McGy
[url]http://mcgy.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in message
news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update rows
> in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
>
Partitioned views have serious limitations. You should expect to have to
directly address the underlying tables for many operations. Qu|||Cheers David. I am considering duplicating the load stored procedures, 1 per
table, so as to remove the dependency on the partitioned view. It will make
maintenance a bit more complex but ultimately performance should improve.
Does that sound sensible?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4TVf3khGHA.3860@.TK2MSFTNGP02.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
rows
SQL
the
> Partitioned views have serious limitations. You should expect to have to
> directly address the underlying tables for many operations. Qu
>|||"McGy" <anon@.anon.com> wrote in message
news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
> Cheers David. I am considering duplicating the load stored procedures, 1
> per
> table, so as to remove the dependency on the partitioned view. It will
> make
> maintenance a bit more complex but ultimately performance should improve.
> Does that sound sensible?
>
Yes. And you can always use dynamic SQL to load the table.
David|||The only problem with dynamic SQL is that the stored procedure would be a
nightmare to maintain in that form as it uses lots of variables. Also,
because of the stored procedure's size it takes several seconds to compile.
Presumably we would take that compilation hit each time the dynamic SQL is
constructed and prepared for execution?
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
improve.
> Yes. And you can always use dynamic SQL to load the table.
> David
>|||Not necessarily; if you use sp_executeSQL with variables you are
essentially creating a parameterized SQL statement which increases the
likelihood that the execution plan will be reused.
This issue is interesting to me; we use partioned views as well.
However all of our inserts are done with bulk load methods, so locking
hasn't been a problem (yet). I understand the SQL 2005's partitoned
tables are much better than the the views; yet another reason to
consider upgrading.
Stu
McGy wrote:
> The only problem with dynamic SQL is that the stored procedure would be a
> nightmare to maintain in that form as it uses lots of variables. Also,
> because of the stored procedure's size it takes several seconds to compile
.
> Presumably we would take that compilation hit each time the dynamic SQL is
> constructed and prepared for execution?
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> improve.|||Hmmm. Interesting point on the sp_executesql. I'll take a look.
We use bulk insert too; but we also need to apply a bunch of rules to the
records that get loaded. They get loaded twice: once in to a transaction
table which are all inserts - the other in to an aggregation table that
applies certain rules depending upon what type the transaction is.
Just thought of a problem on the dynamic SQL front. The stored procedure
would be well in excess of the 8000 char limit on variable sizes. Is there a
way to work around that?
McGy
[url]http://mcgy.blogspot.com[/url]
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1149427023.769413.5440@.i39g2000cwa.googlegroups.com...
> Not necessarily; if you use sp_executeSQL with variables you are
> essentially creating a parameterized SQL statement which increases the
> likelihood that the execution plan will be reused.
> This issue is interesting to me; we use partioned views as well.
> However all of our inserts are done with bulk load methods, so locking
> hasn't been a problem (yet). I understand the SQL 2005's partitoned
> tables are much better than the the views; yet another reason to
> consider upgrading.
> Stu
> McGy wrote:
a
compile.
is
procedures, 1
will
>|||McGy (anon@.anon.com) writes:
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update
> rows in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
Are the intent locks causing any real problems? I ran a quick test, and
I was not able detect any locking problems, but I might have missed
something. (I was only testing concurrent SELECT statements.)
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|||Hi Erland. I am not actually sure if those IX locks are causing a problem.
What is the impact of an IX lock? When we were loading multiple files in
production we noticed that some were being blocked waiting for others to
complete. We assumed it was because of the view creating locks against all
the tables - but perhaps not?
McGy
[url]http://mcgy.blogspot.com[/url]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97D9732464F2Yazorman@.127.0.0.1...
> McGy (anon@.anon.com) writes:
> Are the intent locks causing any real problems? I ran a quick test, and
> I was not able detect any locking problems, but I might have missed
> something. (I was only testing concurrent SELECT statements.)
>
> --
> 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|||McGy (anon@.anon.com) writes:
> Hi Erland. I am not actually sure if those IX locks are causing a problem.
> What is the impact of an IX lock? When we were loading multiple files in
> production we noticed that some were being blocked waiting for others to
> complete. We assumed it was because of the view creating locks against all
> the tables - but perhaps not?
The purpose of an intent lock is to tell "I am working here, so don't
try to take all this place for your own".
If a transaction updates a few rows in a table it acquires an X lock
on these rows, and also an IX lock on the table. This prevents other
processes from getting an X-lock on the table. Or put into other words,
the process that wants an exclusive lock on the table, does not need to
check if any rows are currently locked.
In case of the partitioned view the IX locks are there to prevent other
processes from acquire exclusive locks on the other tables, as there
could suddenly appear a row that should be inserted the other tables.
SQL Server cannot conclude that all data that is being inserted goes
only into one table in the view.
So, yes, if you have different insert processes in parallel, they will
block each other. And a process what would try "SELECT COUNT(*) FROM
pview" or anything else that requires a scan would also probably be
blocked, even with a condition that filtered out the table being
loaded.
If you want to run parallel loads at maximum speeds, you will probably
have to load into the underlying tables directly.
I will have to admit that I had read up in Books Online on what an
intent lock really is.
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