Multithreading in Sql Server

Hi,
Does sqlserver 2000 supports multi threading?
If so how can we implement it?
As per my knowledge it supports Symmetric multi
processing.
Can you please confirm this?
I posted this in english query section.Iam reposting it
here since this group is more relevant.
Thanks in adavance.
-SriramSQL automatically handles multiple processors... However when you go into
SQL Enterprise Manager, right click your server registration and select
properties, one of the tabs will allow you to choose how many of the
processors to use, 0 means use them all...
One of the diferences between SQL Standard and SQL Enterprise is how much
multi processing goes on during maintenance, like creating indexes... Of
course SQL Enterprise is more scalable.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>|||You don't normally control threading within an application. AFAIK - SQL
Server is a multi-threaded application; there is nothing to "implement".
Perhaps you are confusing multiple threads with multiple processors. If so,
then see Wayne's response.
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>

Multithreading in Sql Server

Hi,
Does sqlserver 2000 supports multi threading?
If so how can we implement it?
As per my knowledge it supports Symmetric multi
processing.
Can you please confirm this?
I posted this in english query section.Iam reposting it
here since this group is more relevant.
Thanks in adavance.
-Sriram
SQL automatically handles multiple processors... However when you go into
SQL Enterprise Manager, right click your server registration and select
properties, one of the tabs will allow you to choose how many of the
processors to use, 0 means use them all...
One of the diferences between SQL Standard and SQL Enterprise is how much
multi processing goes on during maintenance, like creating indexes... Of
course SQL Enterprise is more scalable.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>
|||You don't normally control threading within an application. AFAIK - SQL
Server is a multi-threaded application; there is nothing to "implement".
Perhaps you are confusing multiple threads with multiple processors. If so,
then see Wayne's response.
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>

Multithreading in Sql Server

Hi,
Does sqlserver 2000 supports multi threading?
If so how can we implement it?
As per my knowledge it supports Symmetric multi
processing.
Can you please confirm this?
I posted this in english query section.Iam reposting it
here since this group is more relevant.
Thanks in adavance.
-SriramSQL automatically handles multiple processors... However when you go into
SQL Enterprise Manager, right click your server registration and select
properties, one of the tabs will allow you to choose how many of the
processors to use, 0 means use them all...
One of the diferences between SQL Standard and SQL Enterprise is how much
multi processing goes on during maintenance, like creating indexes... Of
course SQL Enterprise is more scalable.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>|||You don't normally control threading within an application. AFAIK - SQL
Server is a multi-threaded application; there is nothing to "implement".
Perhaps you are confusing multiple threads with multiple processors. If so,
then see Wayne's response.
"Sriram" <surapureddysriram@.yahoo.co.in> wrote in message
news:a08a01c4794f$2d51cf50$a601280a@.phx.gbl...
> Hi,
> Does sqlserver 2000 supports multi threading?
> If so how can we implement it?
> As per my knowledge it supports Symmetric multi
> processing.
> Can you please confirm this?
> I posted this in english query section.Iam reposting it
> here since this group is more relevant.
> Thanks in adavance.
> -Sriram
>sql

multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > I created a .net program which uses multithreading. The threads
> > concurrently access the same db resources. This has generated no end
> > of sql server deadlocks, which our dba is struggling to resolve.
> >
> > I am very close to giving up and going ahead and serializing all
> > database access for the application. This will reduce performance
> > quite a bit, although it will eliminate all the deadlocks.
> >
> > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > mine for building an app that hits the db so hard, but we are unable
> > to solve all the deadlocking issues using the errorlog and profiling
> > combo that has been suggested. We solve one deadlock issue, only to
> > see another one revealed later, perhaps on different resources,
> > perhaps not.
> >
> > I have heard there are many applications that use the Singleton
> > pattern to serialize db access accross the board, just to avoid
> > situations like this.
> >
> > Any feedback welcome.
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
--
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > Deadlocks are usually caused by 2 or more users attempting to update the
> > same set of tables in opposite orders. If you have one sp that updates
> > Orders and then Details while another updates details first and then
> orders
> > this will eventually cause a dead lock. If they are both in the same
> order
> > you most likely get only blocking which is normal and can be controlled.
> Do
> > you have an example of 2 procedures that deadlock on each other and the
> > associated DDL for those tables? Deadlocking is almost always a result
of
> a
> > poor schema or more likely poor data access methods. Serialization is
not
> > the way to go, you should do it right and find the cause so you can fix
> it.
> > Maybe these will help:
> >
> >
> > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > Blocking
> >
> >
> >
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > I created a .net program which uses multithreading. The threads
> > > concurrently access the same db resources. This has generated no end
> > > of sql server deadlocks, which our dba is struggling to resolve.
> > >
> > > I am very close to giving up and going ahead and serializing all
> > > database access for the application. This will reduce performance
> > > quite a bit, although it will eliminate all the deadlocks.
> > >
> > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > mine for building an app that hits the db so hard, but we are unable
> > > to solve all the deadlocking issues using the errorlog and profiling
> > > combo that has been suggested. We solve one deadlock issue, only to
> > > see another one revealed later, perhaps on different resources,
> > > perhaps not.
> > >
> > > I have heard there are many applications that use the Singleton
> > > pattern to serialize db access accross the board, just to avoid
> > > situations like this.
> > >
> > > Any feedback welcome.
> >
> >
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec:(0x53915568) Value:0x558fa140 Cost:(0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> > Yes there are other reasons why dead locks occur but the main reason is
as I
> > posted. Without more details it's all a guess so the most logical or
common
> > reason is what I posted.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Christian Smith" <csmith@.digex.com> wrote in message
> > news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Can't deadlocking also happen for locks other than tables? It can
happen
> > > for RID or KEY locks as well within the same table, can't it?
> > >
> > > Christian Smith
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > > Deadlocks are usually caused by 2 or more users attempting to update
the
> > > > same set of tables in opposite orders. If you have one sp that
updates
> > > > Orders and then Details while another updates details first and then
> > orders
> > > > this will eventually cause a dead lock. If they are both in the
same
> > order
> > > > you most likely get only blocking which is normal and can be
controlled.
> > Do
> > > > you have an example of 2 procedures that deadlock on each other and
the
> > > > associated DDL for those tables? Deadlocking is almost always a
result
> > of
> > a
> > > > poor schema or more likely poor data access methods. Serialization
is
> > not
> > > > the way to go, you should do it right and find the cause so you can
fix
> > it.
> > > > Maybe these will help:
> > > >
> > > >
> > > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > > http://www.support.microsoft.com/?id=271509 How to monitor SQL
2000
> > > > Blocking
> > > >
> > > >
> > > >
> > >
> >
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > > I created a .net program which uses multithreading. The threads
> > > > > concurrently access the same db resources. This has generated no
end
> > > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > > >
> > > > > I am very close to giving up and going ahead and serializing all
> > > > > database access for the application. This will reduce performance
> > > > > quite a bit, although it will eliminate all the deadlocks.
> > > > >
> > > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe
it's
> > > > > mine for building an app that hits the db so hard, but we are
unable
> > > > > to solve all the deadlocking issues using the errorlog and
profiling
> > > > > combo that has been suggested. We solve one deadlock issue, only
to
> > > > > see another one revealed later, perhaps on different resources,
> > > > > perhaps not.
> > > > >
> > > > > I have heard there are many applications that use the Singleton
> > > > > pattern to serialize db access accross the board, just to avoid
> > > > > situations like this.
> > > > >
> > > > > Any feedback welcome.
> > > >
> > > >
> > >
> > >|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.

multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/t...=books&n=507846
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/t...=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> orders
> order
> Do
of
> a
not
> it.
>
http://www.amazon.com/exec/obidos/t...=books&n=507846
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.
TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as
I
> posted. Without more details it's all a guess so the most logical or comm
on
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> orders
> order
> Do
> of
> a
> not
> it.
> http://www.amazon.com/exec/obidos/t...=books&n=507846|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec0x53915568) Value:0x558fa140 Cost0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec0x4872B568) Value:0x48b1e3e0 Cost0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec0x4872B568) Value:0x48b1e3e0 Cost0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.
TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as
I
> posted. Without more details it's all a guess so the most logical or comm
on
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> orders
> order
> Do
> of
> a
> not
> it.
> http://www.amazon.com/exec/obidos/t...=books&n=507846|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
as I
common
happen
the
updates
same
controlled.
the
result
is
fix
2000
http://www.amazon.com/exec/obidos/t...=books&n=507846
end
it's
unable
profiling
to|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.

Multiple users logging into a database

I'm developing an application in ASP.Net that uses SQLServer. The database person in the team wants to have each user have a seperate login to the database so he can tell who has done what changes in the database. Is this a good practice? Or are their performance\security issues with this model?For system performance it is better to use one account (to use one connection string) and connection pooling. My solution is as follows. Create your own users and log-in system. You can save current username or id to Session. Then create a table system_events to your database. Everytime user makes something you can insert a new row that describes user action. Also, and it is maybe even better, you can use plain text logs for this.|||We implemented this feature and what we did was write a stored procedure to check the old value against the new values being updated in the database and then it writes only the changes to a seperate table designed for tracking changes. We then wrote a page to display change history by date range in a D-Grid. It works very well and there has been no noticable performance hit since implementation.

CREATE PROCEDURE AddChangeHistory
(
@.propertyID INT,
@.userName VARCHAR(75), --Assigned in session when a user logins
@.tableName VARCHAR(75),
@.fieldName VARCHAR(80),
@.fieldChangeVal varchar(200)
)
AS
DECLARE @.fieldInitVal varchar(200)
BEGIN TRAN
CREATE TABLE #tempVal
(
fieldInitVal VARCHAR(200)
)
INSERT INTO #tempVal exec('SELECT TOP 1 ' + @.fieldName + ' FROM ' + @.tableName + ' WHERE propertyID=' + @.propertyID + '')
SELECT @.fieldInitVal = (SELECT top 1 * FROM #tempVal)
DROP TABLE #tempVal
IF(LOWER(@.fieldInitVal) <> LOWER(@.fieldChangeVal))
BEGIN
INSERT INTO ChangeHistory(propertyID, userName, tableName, fieldName, fieldInitVal, fieldChangeVal)
VALUES (@.propertyID, @.userName, @.tableName, @.fieldName, @.fieldInitVal, @.fieldChangeVal)
END
IF(@.@.ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
GO

SPROC to update my database

CREATE PROCEDURE dbo.oPK_UpdateAdminHours
(
@.Role AS varchar(100),
@.UserName AS varchar(100),
@.vcManager AS varchar(50),
@.vcAssistant1 AS varchar(50),
@.vcAssistant2 AS varchar(50),
@.vcLeasingManager AS varchar(50),
@.vcMarketingDirector AS varchar(50),
@.vcLeasing1 AS varchar(50),
@.vcLeasing2 AS varchar(50),
@.vcLeasing3 AS varchar(50),
@.vcLeasing4 AS varchar(50),
@.vcLeasing5 AS varchar(50),
@.bMon_Fri AS bit,
@.cDisplaySummer_Winter AS char(1),
@.vcOfficeHoursSummerMon AS varchar(25),
@.vcOfficeHoursWinterMon AS varchar(25),
@.vcOfficeHoursSummerTue AS varchar(25),
@.vcOfficeHoursWinterTue AS varchar(25),
@.vcOfficeHoursSummerWed AS varchar(25),
@.vcOfficeHoursWinterWed AS varchar(25),
@.vcOfficeHoursSummerThur AS varchar(25),
@.vcOfficeHoursWinterThur AS varchar(25),
@.vcOfficeHoursSummerFri AS varchar(25),
@.vcOfficeHoursWinterFri AS varchar(25),
@.vcOfficeHoursSummerSat AS varchar(25),
@.vcOfficeHoursWinterSat AS varchar(25),
@.vcOfficeHoursSummerSun AS varchar(25),
@.vcOfficeHoursWinterSun AS varchar(25),
@.vcTimeZone AS varchar(50),
@.PropertyID AS INT
)
AS
--Calling the AddChangeHistory SPROC to check the values before updating the Database
exec AddChangeHistory @.propertyID, @.userName, 'vcManager',@.vcManager
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant1',@.vcAssistant1
exec AddChangeHistory @.propertyID, @.userName, 'vcAssistant2',@.vcAssistant2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasingManager',@.vcLeasingManager
exec AddChangeHistory @.propertyID, @.userName, 'vcMarketingDirector',@.vcMarketingDirector
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing1',@.vcLeasing1
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing2',@.vcLeasing2
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing3',@.vcLeasing3
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing4',@.vcLeasing4
exec AddChangeHistory @.propertyID, @.userName, 'vcLeasing5',@.vcLeasing5
exec AddChangeHistory @.propertyID, @.userName, 'bMon_Fri',@.bMon_Fri
exec AddChangeHistory @.propertyID, @.userName, 'cDisplaySummer_Winter',@.cDisplaySummer_Winter
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerMon',@.vcOfficeHoursSummerMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterMon',@.vcOfficeHoursWinterMon
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerTue',@.vcOfficeHoursSummerTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterTue',@.vcOfficeHoursWinterTue
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerWed',@.vcOfficeHoursSummerWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterWed',@.vcOfficeHoursWinterWed
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerThur',@.vcOfficeHoursSummerThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterThur',@.vcOfficeHoursWinterThur
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerFri',@.vcOfficeHoursSummerFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterFri',@.vcOfficeHoursWinterFri
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSat',@.vcOfficeHoursSummerSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSat',@.vcOfficeHoursWinterSat
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursSummerSun',@.vcOfficeHoursSummerSun
exec AddChangeHistory @.propertyID, @.userName, 'vcOfficeHoursWinterSun',@.vcOfficeHoursWinterSun
exec AddChangeHistory @.propertyID, @.userName, 'vcTimeZone ',@.vcTimeZone
--Put your Update code here (removed for space)

Hope this helps.|||Thank you so much. That is what I needed.

Multiple servers

Hi All,

I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?

Thanks

Bryan

Are you storing the database connection information in the web application's web.config file? Or did you hard-code it into each sql command?

|||

all my other connections are stored in the webconfig. But as for the ASPNETDB.mdf connection string i have no idea where it is stored by default. Does it matter if there is no sql server on the pc that the site resides on. To my knowledge it does, so i need to find away to have the database on the database server and the site on our webserver. I dont know if i am making myself clear... if you create a simple site with a login.aspx with a login control and a default.aspx with a simple "HELLO" on it and choose ASP.net configuration web utility and setup some users and roles, it created a folder called App_Data where the ASPNETDB database is stored.

Now if this project was a piece of electornic equipment seperated into distinct pieces, i would like to remove the database piece from where it is in my application and physically move it to another geographical place namelly my other server (database server), but if i remove it totally, clip all the wires and remove it then my equipment (website) does not work correctly. So what i want to do is to extend the cables so that they are able to reach the other server (databse server) so my equipment (website) still works fine, just with the extended cable ( some sort of connection string stored somewhere! ) :-) kind of a dumb ass analogy, but im sure you get what I mean now.

I dont know if i am missing something or the only person that has done something like this but all i do is create a simple website, which works fine on my pc, and deploy it and the login doesnt work...

ANY suggestions will be greatly appreciated, ive been pulling my hair out for almost 2 days now trying to figure this out.

thanks for all the help

Bryan

|||

Hello,

The solution was: mounted the default sql serverdatabase (ASPNETDB) on our database server and added a connectionstring in the webconfig to point to it. what i had to do was remove theconnection string and then recreate it as follows

<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="DataSource=CORE;Initial Catalog=ASPNETDB;Persist Security Info=True;UserID=user;Password=password" providerName="System.Data.SqlClient"/>

this sorted out all my issues.

Thanks

Bryan