deAttach database: parent

Showing posts with label parent. Show all posts

Wednesday, March 21, 2012

deadlock on parent-child relationship

I have a program that inserts a row to a parent table and before it
commits then calls another program to insert rows to the child table.
This is causing a deadlock. When I looked at it, the
first program has an X lock on the primary key of the parent table and
the second program is trying to get a share lock on the index of the
parent table ?
Why is this happening ? How can I avoid it ?
Thanks
RogerMake sure the order of the tables in the from clause is the same in both
queries and consider using the UPDLOCK table hint.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8i93.asp
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
ML
http://milambda.blogspot.com/|||You can't avoid it unless both updates occur on the same connection, or
unless you bind the second connection to the first. Look up sp_bindsession
in BOL. Exclusive locks are held on an inserted row until it is committed,
so no other transaction can see the row until it's committed (unless you use
WITH(NOLOCK), which should be avoided whenever possible).
I prefer to dump an update that contains related information into temp
tables so that they can be committed using set-based operations within a
stored procedure, but that can have performance and scalability implications
depending on whether tempdb is on it's own disk subsystem and on whether
there's enough memory so that the contents of the temp tables aren't
migrated out to disk. Set-based operations minimize lock duration, index
maintenance and transaction logging, so it's a trade-off. Without testing,
it cannot be determined which method provides the best performance and
scalability for a particular update scenario. However, I prefer to keep
transaction processing within stored procedures because I've found that
troubleshooting and repairing blocking and deadlock problems is less
expensive if all transactions are contained in procedures. It's a lot
easier to add a SELECT WITH(UPDLOCK) to a stored procedure than to alter,
recompile, and redeploy a client program.
"Roger" <wonderinguys@.gmail.com> wrote in message
news:1138809010.484456.142920@.z14g2000cwz.googlegroups.com...
>I have a program that inserts a row to a parent table and before it
> commits then calls another program to insert rows to the child table.
> This is causing a deadlock. When I looked at it, the
> first program has an X lock on the primary key of the parent table and
> the second program is trying to get a share lock on the index of the
> parent table ?
> Why is this happening ? How can I avoid it ?
> Thanks
> Roger
>|||the program that inserts the child table is in a new spid...a different
one from the parent program. Why is that ? i am from DB2 running on
mainframe where this never happens. So need some help with this.|||On 2 Feb 2006 12:21:33 -0800, Roger wrote:

>the program that inserts the child table is in a new spid...a different
>one from the parent program. Why is that ? i am from DB2 running on
>mainframe where this never happens. So need some help with this.
Hi Roger,
That's the cause of your deadlock, then.
This surely doesn't happen automatically. In fact, you have to work
pretty hard to get a subprocedure to run in a different spid in SQL
Server. (Doing it from the client is easier, but still takes some
effort).
Can you post (snippets of) your code?
Hugo Kornelis, SQL Server MVP

Deadlock Issue.

The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:[vbcol=seagreen]
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6
d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrenc
y
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table si
ze
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:[vbcol=seagreen]
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on

> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegroups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>

Deadlock Issue.

The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:[vbcol=seagreen]
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrency
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table size
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegr oups.com...
|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:[vbcol=seagreen]
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on

> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:
|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegro ups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>

Deadlock Issue.

Greetings All, here is the ddl to create my test:
create table Parent
(
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
RIAmt decimal(28,10),
CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
)
go
create table Child
(
CPK1 decimal(10) not null,
CPK2 decimal(9) not null,
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
)
go
ALTER TABLE Child ADD CONSTRAINT FK
FOREIGN KEY (PPK1, PPK2)
REFERENCES Parent(PPK1, PPK2)
go
Next I open two different SQLCMD Windows: cmd1 and cmd 2
cmd1: begin tran;
go
insert into parent values (1, 999999999);
go
cmd2: begin tran;
go
insert into parent values (2, 999999999);
go
insert into child values (1, 999999999, 2, 999999999);
go
cmd1: insert into child values (2, 999999999, 1, 999999999);
go
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
WAIT CONDITION IS GENERATED
cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
go
DEADLOCK OCCURS
I am curious why this deadlock occurs when each thread is only
accessing data created in its own thread? I am thinking that a table
scan is taking place on the child table when I do the select and it is
bumping into a locked record?
Any and all help would be greatly appreciated.
Regards, TFD.> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
Your theory is correct. Since there is no index on PPK1 and PPK2, the
SELECT select statements must scan all data and become blocked when
uncommitted data are encountered.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> Greetings All, here is the ddl to create my test:
> create table Parent
> (
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> RIAmt decimal(28,10),
> CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> )
> go
> create table Child
> (
> CPK1 decimal(10) not null,
> CPK2 decimal(9) not null,
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> )
> go
> ALTER TABLE Child ADD CONSTRAINT FK
> FOREIGN KEY (PPK1, PPK2)
> REFERENCES Parent(PPK1, PPK2)
> go
>
> Next I open two different SQLCMD Windows: cmd1 and cmd 2
> cmd1: begin tran;
> go
> insert into parent values (1, 999999999);
> go
> cmd2: begin tran;
> go
> insert into parent values (2, 999999999);
> go
> insert into child values (1, 999999999, 2, 999999999);
> go
> cmd1: insert into child values (2, 999999999, 1, 999999999);
> go
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> WAIT CONDITION IS GENERATED
> cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> go
> DEADLOCK OCCURS
> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
> Any and all help would be greatly appreciated.
> Regards, TFD.
>|||Dan, let me ask you a broad question that may not have a direct answer
but hopefully some best practice might be applicable. This issue I
demonstrated here is happening in an application developed by my
company. It is a mult-threaded parallel processing application that is
required to have high throughput and will be performing complex
calculations. One way I can prevent the issue I brought up her is to
have ADO start the transaction in "snapshot" mode. This will avoid the
deadlock issue but I am worried about tempdb peformance? An
alternative is to go throught he physical data model and ensure that
all FK's have the appropriate indexes so that the scenario here (which
can happen in many places in the application) will not occur.
What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
Regards, TFD.
Dan Guzman wrote:
> > I am curious why this deadlock occurs when each thread is only
> > accessing data created in its own thread? I am thinking that a table
> > scan is taking place on the child table when I do the select and it is
> > bumping into a locked record?
> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> SELECT select statements must scan all data and become blocked when
> uncommitted data are encountered.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> > Greetings All, here is the ddl to create my test:
> >
> > create table Parent
> > (
> > PPK1 decimal(10) not null,
> > PPK2 decimal(9) not null,
> > RIAmt decimal(28,10),
> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> > )
> > go
> >
> > create table Child
> > (
> > CPK1 decimal(10) not null,
> > CPK2 decimal(9) not null,
> > PPK1 decimal(10) not null,
> > PPK2 decimal(9) not null,
> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> > )
> > go
> >
> > ALTER TABLE Child ADD CONSTRAINT FK
> > FOREIGN KEY (PPK1, PPK2)
> > REFERENCES Parent(PPK1, PPK2)
> > go
> >
> >
> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> >
> > cmd1: begin tran;
> > go
> > insert into parent values (1, 999999999);
> > go
> >
> > cmd2: begin tran;
> > go
> > insert into parent values (2, 999999999);
> > go
> > insert into child values (1, 999999999, 2, 999999999);
> > go
> >
> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> > go
> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> > go
> > WAIT CONDITION IS GENERATED
> >
> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> > go
> > DEADLOCK OCCURS
> >
> > I am curious why this deadlock occurs when each thread is only
> > accessing data created in its own thread? I am thinking that a table
> > scan is taking place on the child table when I do the select and it is
> > bumping into a locked record?
> >
> > Any and all help would be greatly appreciated.
> >
> > Regards, TFD.
> >|||> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
should not be used as a general cure for blocking. The SQL Server 2005
Books Online does a pretty good job of discussing the pros and cons of the
various row versioning levels
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
However, I want to add that performance and concurrency go hand-in-hand.
Blocking is often a symptom of an underlying performance issue as
illustrated by you example. Sure, you might be able to improve concurrency
by using SNAPSHOT ISOLATION but that's not the right approach unless you
know the root cause and ramifications. If you simply change the isolation
level rather than perform index/query tuning, you'll find the app doesn't
scale. CPU and disk i/o will be consumed in direct proportion to table size
snapshot isolation overhead only compounds the issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> Dan, let me ask you a broad question that may not have a direct answer
> but hopefully some best practice might be applicable. This issue I
> demonstrated here is happening in an application developed by my
> company. It is a mult-threaded parallel processing application that is
> required to have high throughput and will be performing complex
> calculations. One way I can prevent the issue I brought up her is to
> have ADO start the transaction in "snapshot" mode. This will avoid the
> deadlock issue but I am worried about tempdb peformance? An
> alternative is to go throught he physical data model and ensure that
> all FK's have the appropriate indexes so that the scenario here (which
> can happen in many places in the application) will not occur.
> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> Regards, TFD.
>
> Dan Guzman wrote:
>> > I am curious why this deadlock occurs when each thread is only
>> > accessing data created in its own thread? I am thinking that a table
>> > scan is taking place on the child table when I do the select and it is
>> > bumping into a locked record?
>> Your theory is correct. Since there is no index on PPK1 and PPK2, the
>> SELECT select statements must scan all data and become blocked when
>> uncommitted data are encountered.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
>> > Greetings All, here is the ddl to create my test:
>> >
>> > create table Parent
>> > (
>> > PPK1 decimal(10) not null,
>> > PPK2 decimal(9) not null,
>> > RIAmt decimal(28,10),
>> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
>> > )
>> > go
>> >
>> > create table Child
>> > (
>> > CPK1 decimal(10) not null,
>> > CPK2 decimal(9) not null,
>> > PPK1 decimal(10) not null,
>> > PPK2 decimal(9) not null,
>> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
>> > )
>> > go
>> >
>> > ALTER TABLE Child ADD CONSTRAINT FK
>> > FOREIGN KEY (PPK1, PPK2)
>> > REFERENCES Parent(PPK1, PPK2)
>> > go
>> >
>> >
>> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
>> >
>> > cmd1: begin tran;
>> > go
>> > insert into parent values (1, 999999999);
>> > go
>> >
>> > cmd2: begin tran;
>> > go
>> > insert into parent values (2, 999999999);
>> > go
>> > insert into child values (1, 999999999, 2, 999999999);
>> > go
>> >
>> > cmd1: insert into child values (2, 999999999, 1, 999999999);
>> > go
>> > select * from child where ppk1 = 2 and ppk2 = 999999999;
>> > go
>> > WAIT CONDITION IS GENERATED
>> >
>> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
>> > go
>> > DEADLOCK OCCURS
>> >
>> > I am curious why this deadlock occurs when each thread is only
>> > accessing data created in its own thread? I am thinking that a table
>> > scan is taking place on the child table when I do the select and it is
>> > bumping into a locked record?
>> >
>> > Any and all help would be greatly appreciated.
>> >
>> > Regards, TFD.
>> >
>|||Dan, perhaps you can entertain one more question for me seeing that you
know what is going on :)
The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:
> > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrency
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table size
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> > Dan, let me ask you a broad question that may not have a direct answer
> > but hopefully some best practice might be applicable. This issue I
> > demonstrated here is happening in an application developed by my
> > company. It is a mult-threaded parallel processing application that is
> > required to have high throughput and will be performing complex
> > calculations. One way I can prevent the issue I brought up her is to
> > have ADO start the transaction in "snapshot" mode. This will avoid the
> > deadlock issue but I am worried about tempdb peformance? An
> > alternative is to go throught he physical data model and ensure that
> > all FK's have the appropriate indexes so that the scenario here (which
> > can happen in many places in the application) will not occur.
> >
> > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> >
> > Regards, TFD.
> >
> >
> > Dan Guzman wrote:
> >> > I am curious why this deadlock occurs when each thread is only
> >> > accessing data created in its own thread? I am thinking that a table
> >> > scan is taking place on the child table when I do the select and it is
> >> > bumping into a locked record?
> >>
> >> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> >> SELECT select statements must scan all data and become blocked when
> >> uncommitted data are encountered.
> >>
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> >> > Greetings All, here is the ddl to create my test:
> >> >
> >> > create table Parent
> >> > (
> >> > PPK1 decimal(10) not null,
> >> > PPK2 decimal(9) not null,
> >> > RIAmt decimal(28,10),
> >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> >> > )
> >> > go
> >> >
> >> > create table Child
> >> > (
> >> > CPK1 decimal(10) not null,
> >> > CPK2 decimal(9) not null,
> >> > PPK1 decimal(10) not null,
> >> > PPK2 decimal(9) not null,
> >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> >> > )
> >> > go
> >> >
> >> > ALTER TABLE Child ADD CONSTRAINT FK
> >> > FOREIGN KEY (PPK1, PPK2)
> >> > REFERENCES Parent(PPK1, PPK2)
> >> > go
> >> >
> >> >
> >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> >> >
> >> > cmd1: begin tran;
> >> > go
> >> > insert into parent values (1, 999999999);
> >> > go
> >> >
> >> > cmd2: begin tran;
> >> > go
> >> > insert into parent values (2, 999999999);
> >> > go
> >> > insert into child values (1, 999999999, 2, 999999999);
> >> > go
> >> >
> >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> >> > go
> >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> >> > go
> >> > WAIT CONDITION IS GENERATED
> >> >
> >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> >> > go
> >> > DEADLOCK OCCURS
> >> >
> >> > I am curious why this deadlock occurs when each thread is only
> >> > accessing data created in its own thread? I am thinking that a table
> >> > scan is taking place on the child table when I do the select and it is
> >> > bumping into a locked record?
> >> >
> >> > Any and all help would be greatly appreciated.
> >> >
> >> > Regards, TFD.
> >> >
> >|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on :)
> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:
> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> >
> > I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> > should not be used as a general cure for blocking. The SQL Server 2005
> > Books Online does a pretty good job of discussing the pros and cons of the
> > various row versioning levels
> > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> >
> > However, I want to add that performance and concurrency go hand-in-hand.
> > Blocking is often a symptom of an underlying performance issue as
> > illustrated by you example. Sure, you might be able to improve concurrency
> > by using SNAPSHOT ISOLATION but that's not the right approach unless you
> > know the root cause and ramifications. If you simply change the isolation
> > level rather than perform index/query tuning, you'll find the app doesn't
> > scale. CPU and disk i/o will be consumed in direct proportion to table size
> > snapshot isolation overhead only compounds the issue.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> > news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> > > Dan, let me ask you a broad question that may not have a direct answer
> > > but hopefully some best practice might be applicable. This issue I
> > > demonstrated here is happening in an application developed by my
> > > company. It is a mult-threaded parallel processing application that is
> > > required to have high throughput and will be performing complex
> > > calculations. One way I can prevent the issue I brought up her is to
> > > have ADO start the transaction in "snapshot" mode. This will avoid the
> > > deadlock issue but I am worried about tempdb peformance? An
> > > alternative is to go throught he physical data model and ensure that
> > > all FK's have the appropriate indexes so that the scenario here (which
> > > can happen in many places in the application) will not occur.
> > >
> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> > >
> > > Regards, TFD.
> > >
> > >
> > > Dan Guzman wrote:
> > >> > I am curious why this deadlock occurs when each thread is only
> > >> > accessing data created in its own thread? I am thinking that a table
> > >> > scan is taking place on the child table when I do the select and it is
> > >> > bumping into a locked record?
> > >>
> > >> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> > >> SELECT select statements must scan all data and become blocked when
> > >> uncommitted data are encountered.
> > >>
> > >>
> > >> --
> > >> Hope this helps.
> > >>
> > >> Dan Guzman
> > >> SQL Server MVP
> > >>
> > >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> > >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> > >> > Greetings All, here is the ddl to create my test:
> > >> >
> > >> > create table Parent
> > >> > (
> > >> > PPK1 decimal(10) not null,
> > >> > PPK2 decimal(9) not null,
> > >> > RIAmt decimal(28,10),
> > >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> > >> > )
> > >> > go
> > >> >
> > >> > create table Child
> > >> > (
> > >> > CPK1 decimal(10) not null,
> > >> > CPK2 decimal(9) not null,
> > >> > PPK1 decimal(10) not null,
> > >> > PPK2 decimal(9) not null,
> > >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> > >> > )
> > >> > go
> > >> >
> > >> > ALTER TABLE Child ADD CONSTRAINT FK
> > >> > FOREIGN KEY (PPK1, PPK2)
> > >> > REFERENCES Parent(PPK1, PPK2)
> > >> > go
> > >> >
> > >> >
> > >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> > >> >
> > >> > cmd1: begin tran;
> > >> > go
> > >> > insert into parent values (1, 999999999);
> > >> > go
> > >> >
> > >> > cmd2: begin tran;
> > >> > go
> > >> > insert into parent values (2, 999999999);
> > >> > go
> > >> > insert into child values (1, 999999999, 2, 999999999);
> > >> > go
> > >> >
> > >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> > >> > go
> > >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> > >> > go
> > >> > WAIT CONDITION IS GENERATED
> > >> >
> > >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> > >> > go
> > >> > DEADLOCK OCCURS
> > >> >
> > >> > I am curious why this deadlock occurs when each thread is only
> > >> > accessing data created in its own thread? I am thinking that a table
> > >> > scan is taking place on the child table when I do the select and it is
> > >> > bumping into a locked record?
> > >> >
> > >> > Any and all help would be greatly appreciated.
> > >> >
> > >> > Regards, TFD.
> > >> >
> > >|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegroups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>> Dan, perhaps you can entertain one more question for me seeing that you
>> know what is going on :)
>> The scenario I described above is further complicated by the fact that
>> in my application the base table is accessed via view. When I create
>> the index on the FK's and then execute the SQL the scan goes away.
>> When I make the same call via a database view the index is not used and
>> I am once again doing a table scan and my deadlock rears its ugly head.
>> How do I force an index when selecting data through a view?
>>
>> e.g.)
>> CREATE INDEX Parent_IDX1
>> ON Parent(PPK1,PPK2);
>> ** This uses the index on PPK1 and PPK2
>> select * from child where ppk1 = 2 and ppk2 = 999999999;
>> go
>> ** This does not use the index on PPK1 and PPK2
>> ** The Optimizer comes back sayign it used the Primary Key of Child for
>> a Clustered Index seek.
>> CREATE VIEW MyView AS
>> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
>> FROM Child
>> go
>>
>> How do I force the Index Parent_IDX1 to get used? MY test only has a
>> few rows of data but in production this table will be heavily populated
>> and used.
>> Any and all help woudl be greatly appreciated.
>> TFD
>>
>>
>> Dan Guzman wrote:
>> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
>> >
>> > I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
>> > should not be used as a general cure for blocking. The SQL Server 2005
>> > Books Online does a pretty good job of discussing the pros and cons of
>> > the
>> > various row versioning levels
>> > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
>> >
>> > However, I want to add that performance and concurrency go
>> > hand-in-hand.
>> > Blocking is often a symptom of an underlying performance issue as
>> > illustrated by you example. Sure, you might be able to improve
>> > concurrency
>> > by using SNAPSHOT ISOLATION but that's not the right approach unless
>> > you
>> > know the root cause and ramifications. If you simply change the
>> > isolation
>> > level rather than perform index/query tuning, you'll find the app
>> > doesn't
>> > scale. CPU and disk i/o will be consumed in direct proportion to table
>> > size
>> > snapshot isolation overhead only compounds the issue.
>> >
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> > news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
>> > > Dan, let me ask you a broad question that may not have a direct
>> > > answer
>> > > but hopefully some best practice might be applicable. This issue I
>> > > demonstrated here is happening in an application developed by my
>> > > company. It is a mult-threaded parallel processing application that
>> > > is
>> > > required to have high throughput and will be performing complex
>> > > calculations. One way I can prevent the issue I brought up her is to
>> > > have ADO start the transaction in "snapshot" mode. This will avoid
>> > > the
>> > > deadlock issue but I am worried about tempdb peformance? An
>> > > alternative is to go throught he physical data model and ensure that
>> > > all FK's have the appropriate indexes so that the scenario here
>> > > (which
>> > > can happen in many places in the application) will not occur.
>> > >
>> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
>> > >
>> > > Regards, TFD.
>> > >
>> > >
>> > > Dan Guzman wrote:
>> > >> > I am curious why this deadlock occurs when each thread is only
>> > >> > accessing data created in its own thread? I am thinking that a
>> > >> > table
>> > >> > scan is taking place on the child table when I do the select and
>> > >> > it is
>> > >> > bumping into a locked record?
>> > >>
>> > >> Your theory is correct. Since there is no index on PPK1 and PPK2,
>> > >> the
>> > >> SELECT select statements must scan all data and become blocked when
>> > >> uncommitted data are encountered.
>> > >>
>> > >>
>> > >> --
>> > >> Hope this helps.
>> > >>
>> > >> Dan Guzman
>> > >> SQL Server MVP
>> > >>
>> > >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> > >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
>> > >> > Greetings All, here is the ddl to create my test:
>> > >> >
>> > >> > create table Parent
>> > >> > (
>> > >> > PPK1 decimal(10) not null,
>> > >> > PPK2 decimal(9) not null,
>> > >> > RIAmt decimal(28,10),
>> > >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
>> > >> > )
>> > >> > go
>> > >> >
>> > >> > create table Child
>> > >> > (
>> > >> > CPK1 decimal(10) not null,
>> > >> > CPK2 decimal(9) not null,
>> > >> > PPK1 decimal(10) not null,
>> > >> > PPK2 decimal(9) not null,
>> > >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
>> > >> > )
>> > >> > go
>> > >> >
>> > >> > ALTER TABLE Child ADD CONSTRAINT FK
>> > >> > FOREIGN KEY (PPK1, PPK2)
>> > >> > REFERENCES Parent(PPK1, PPK2)
>> > >> > go
>> > >> >
>> > >> >
>> > >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
>> > >> >
>> > >> > cmd1: begin tran;
>> > >> > go
>> > >> > insert into parent values (1, 999999999);
>> > >> > go
>> > >> >
>> > >> > cmd2: begin tran;
>> > >> > go
>> > >> > insert into parent values (2, 999999999);
>> > >> > go
>> > >> > insert into child values (1, 999999999, 2, 999999999);
>> > >> > go
>> > >> >
>> > >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
>> > >> > go
>> > >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
>> > >> > go
>> > >> > WAIT CONDITION IS GENERATED
>> > >> >
>> > >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
>> > >> > go
>> > >> > DEADLOCK OCCURS
>> > >> >
>> > >> > I am curious why this deadlock occurs when each thread is only
>> > >> > accessing data created in its own thread? I am thinking that a
>> > >> > table
>> > >> > scan is taking place on the child table when I do the select and
>> > >> > it is
>> > >> > bumping into a locked record?
>> > >> >
>> > >> > Any and all help would be greatly appreciated.
>> > >> >
>> > >> > Regards, TFD.
>> > >> >
>> > >
>

Wednesday, March 7, 2012

DDL

Hi All,
Is there a way to get the Data Definition Library for a
Database? I need to send it to my parent Corporation.
Thanks,
JoeThere are a number of ways to generate the T-SQL code to recreate your
database. Here is an article that might help:
http://www.dbazine.com/larsen4.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:2b68501c468f4$50d93e60$a601280a@.phx.gbl...
> Hi All,
> Is there a way to get the Data Definition Library for a
> Database? I need to send it to my parent Corporation.
> Thanks,
> Joe|||Hi,
Ope Enterprise Manager -- Expand the database-- Right click above the
database you need the DDL, Select "Alltasks" and click "Generate SQL Script"
and select options you require and click OK. THis
will generate the DDL script for that database.
You can save this as a .SQL file and send to parent corporation.
Thanks
Hari
MCDBA
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:2b68501c468f4$50d93e60$a601280a@.phx.gbl...
> Hi All,
> Is there a way to get the Data Definition Library for a
> Database? I need to send it to my parent Corporation.
> Thanks,
> Joe|||Thank you Gregory,
I know how to generate the SQL Script for a DB. I was
wondering if there was a tool or a process to list all
table relationships, indexes and triggers.
Thanks again,
Joe|||This is a multi-part message in MIME format.
--=_NextPart_000_00DB_01C468C7.F0F8E300
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Have you tried the Database Diagram Wizard. If not this wizard should =be able to build you a picture of all the tables and will drawlines to =represent the foreign key relationships. Is this what you are looking =for? You might also consider looking at the =INFORMATION_SCHEMA.TABLE_CONSTRAINTS view
As far as the triggers are concerned you could use SQL-DMO you could =process thru the table object and then through the trigger collection to =identify all the triggers.
Another option might for both might be to select to script all tables, =but then only select the check boxes that generate triggers and foreign =key constraints.
-- ----=----=--
Need SQL Server Examples check out my website at =http://www.geocities.com/sqlserverexamples
"Joe" <anonymous@.discussions.microsoft.com> wrote in message =news:2bdfe01c468f8$502ed0c0$a501280a@.phx.gbl...
> Thank you Gregory,
> > I know how to generate the SQL Script for a DB. I was > wondering if there was a tool or a process to list all > table relationships, indexes and triggers.
> > Thanks again,
> Joe
--=_NextPart_000_00DB_01C468C7.F0F8E300
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Have you tried the Database Diagram =Wizard. If not this wizard should be able to build you a picture of all the =tables and will drawlines to represent the foreign key relationships. Is this =what you are looking for? You might also consider looking at the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view
As far as the triggers are concerned =you could use SQL-DMO you could process thru the table object and then through the =trigger collection to identify all the triggers.
Another option might for both might be =to select to script all tables, but then only select the check boxes that generate =triggers and foreign key constraints.
-- ---=----=--
Need SQL Server Examples check out my =website at http://www.geocities.com/sqlserverexamples
"Joe" wrote in message news:2bdfe01c468f8$502ed0c0$a501280a@.phx.gbl...> Thank you =Gregory,> > I know how to generate the SQL Script for a DB. I was => wondering if there was a tool or a process to list all > table relationships, indexes and triggers.> > Thanks =again,> Joe

--=_NextPart_000_00DB_01C468C7.F0F8E300--|||Thank you very much for this info!

DDL

Hi All,
Is there a way to get the Data Definition Library for a
Database? I need to send it to my parent Corporation.
Thanks,
Joe
There are a number of ways to generate the T-SQL code to recreate your
database. Here is an article that might help:
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:2b68501c468f4$50d93e60$a601280a@.phx.gbl...
> Hi All,
> Is there a way to get the Data Definition Library for a
> Database? I need to send it to my parent Corporation.
> Thanks,
> Joe
|||Hi,
Ope Enterprise Manager -- Expand the database-- Right click above the
database you need the DDL, Select "Alltasks" and click "Generate SQL Script"
and select options you require and click OK. THis
will generate the DDL script for that database.
You can save this as a .SQL file and send to parent corporation.
Thanks
Hari
MCDBA
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:2b68501c468f4$50d93e60$a601280a@.phx.gbl...
> Hi All,
> Is there a way to get the Data Definition Library for a
> Database? I need to send it to my parent Corporation.
> Thanks,
> Joe
|||Thank you Gregory,
I know how to generate the SQL Script for a DB. I was
wondering if there was a tool or a process to list all
table relationships, indexes and triggers.
Thanks again,
Joe
|||Have you tried the Database Diagram Wizard. If not this wizard should be able to build you a picture of all the tables and will drawlines to represent the foreign key relationships. Is this what you are looking for? You might also consider looking at the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view
As far as the triggers are concerned you could use SQL-DMO you could process thru the table object and then through the trigger collection to identify all the triggers.
Another option might for both might be to select to script all tables, but then only select the check boxes that generate triggers and foreign key constraints.
-------
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
"Joe" <anonymous@.discussions.microsoft.com> wrote in message news:2bdfe01c468f8$502ed0c0$a501280a@.phx.gbl...
> Thank you Gregory,
> I know how to generate the SQL Script for a DB. I was
> wondering if there was a tool or a process to list all
> table relationships, indexes and triggers.
> Thanks again,
> Joe
|||Thank you very much for this info!

DDL

Hi All,
Is there a way to get the Data Definition Library for a
Database? I need to send it to my parent Corporation.
Thanks,
JoeThere are a number of ways to generate the T-SQL code to recreate your
database. Here is an article that might help:
http://www.dbazine.com/larsen4.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:2b68501c468f4$50d93e60$a601280a@.phx
.gbl...
> Hi All,
> Is there a way to get the Data Definition Library for a
> Database? I need to send it to my parent Corporation.
> Thanks,
> Joe|||Hi,
Ope Enterprise Manager -- Expand the database-- Right click above the
database you need the DDL, Select "Alltasks" and click "Generate SQL Script"
and select options you require and click OK. THis
will generate the DDL script for that database.
You can save this as a .SQL file and send to parent corporation.
Thanks
Hari
MCDBA
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:2b68501c468f4$50d93e60$a601280a@.phx
.gbl...
> Hi All,
> Is there a way to get the Data Definition Library for a
> Database? I need to send it to my parent Corporation.
> Thanks,
> Joe|||Thank you Gregory,
I know how to generate the SQL Script for a DB. I was
wondering if there was a tool or a process to list all
table relationships, indexes and triggers.
Thanks again,
Joe|||Have you tried the Database Diagram Wizard. If not this wizard should be ab
le to build you a picture of all the tables and will drawlines to represent
the foreign key relationships. Is this what you are looking for? You might
also consider looking at the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view
As far as the triggers are concerned you could use SQL-DMO you could process
thru the table object and then through the trigger collection to identify a
ll the triggers.
Another option might for both might be to select to script all tables, but t
hen only select the check boxes that generate triggers and foreign key const
raints.
--
----
----
--
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserve
rexamples
"Joe" <anonymous@.discussions.microsoft.com> wrote in message news:2bdfe01c468f8$502ed0c0$a50
1280a@.phx.gbl...
> Thank you Gregory,
>
> I know how to generate the SQL Script for a DB. I was
> wondering if there was a tool or a process to list all
> table relationships, indexes and triggers.
>
> Thanks again,
> Joe|||Thank you very much for this info!

Wednesday, March 21, 2012

deadlock on parent-child relationship

Deadlock Issue.

Deadlock Issue.

Deadlock Issue.

Wednesday, March 7, 2012

DDL

DDL

DDL

deAttach database

Blog Archive

About Me