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.
Showing posts with label threads. Show all posts
Showing posts with label threads. Show all posts
Wednesday, March 28, 2012
Multithreaded run of SSIS packages
Is it safe to run several SSIS packages in parallel in a multithreaded application? The package objects are not accessed from multiple threads: every thread is handling its own package (see below).
void task_inside_thread()
{
System::String ^s = "...";
Microsoft::SqlServer::Dts::Runtime::Package p;
p.LoadFromXML(s, nullptr);
// callback handler
MyEventsClass ^ eventsClass = gcnew MyEventsClass();
DTSExecResult result = p.Execute(nullptr, nullptr, eventsClass, nullptr, nullptr);
return;
}
Is there any relevant documentation with respect to this issue?
Thanks,
Bogdan
Yes, this should be safe, packages are independent of each other.|||I worried more about the fact the package instances might use global objects for registration and for signalling their current status.
Thanks for your answer.
Bogdan
|||Yes, they sometime do. I have to re-phrase it - they behave as independent objects, taking care of locking global state when needed.Monday, March 19, 2012
Multiple threads or not?
SQL 7.0/NT 4.0 latest SPs
Performance question.
One application will be writing to two tables. 1000s of
records at a time, in bursts, and at the same time, the
tables will be serving up data to end-users from a
separate application.
Question: Should there be a separate thread written to
each table that the appliation is writing to? Would that
help performance getting the records in faster and reduce
the amount of over-head so that the users making their
requests aren't slow in getting their data.
Thank you for your help.
DonHow are you inserting them? Have you tried using Bulk Insert?
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> SQL 7.0/NT 4.0 latest SPs
> Performance question.
> One application will be writing to two tables. 1000s of
> records at a time, in bursts, and at the same time, the
> tables will be serving up data to end-users from a
> separate application.
> Question: Should there be a separate thread written to
> each table that the appliation is writing to? Would that
> help performance getting the records in faster and reduce
> the amount of over-head so that the users making their
> requests aren't slow in getting their data.
> Thank you for your help.
> Don
>|||The developer is using INSERT. Looks like one record at
at time from C++.
Bulk insert would be better?
Don
>--Original Message--
>How are you inserting them? Have you tried using Bulk
Insert?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Don" <ddachner@.yahoo.com> wrote in message
>news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
>> SQL 7.0/NT 4.0 latest SPs
>> Performance question.
>> One application will be writing to two tables. 1000s of
>> records at a time, in bursts, and at the same time, the
>> tables will be serving up data to end-users from a
>> separate application.
>> Question: Should there be a separate thread written to
>> each table that the appliation is writing to? Would
that
>> help performance getting the records in faster and
reduce
>> the amount of over-head so that the users making their
>> requests aren't slow in getting their data.
>> Thank you for your help.
>> Don
>
>.
>|||A Bulk Insert would generally be many times faster than individual inserts.
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:055401c37ee9$c88931e0$a401280a@.phx.gbl...
> The developer is using INSERT. Looks like one record at
> at time from C++.
> Bulk insert would be better?
> Don
> >--Original Message--
> >How are you inserting them? Have you tried using Bulk
> Insert?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Don" <ddachner@.yahoo.com> wrote in message
> >news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> >> SQL 7.0/NT 4.0 latest SPs
> >>
> >> Performance question.
> >>
> >> One application will be writing to two tables. 1000s of
> >> records at a time, in bursts, and at the same time, the
> >> tables will be serving up data to end-users from a
> >> separate application.
> >>
> >> Question: Should there be a separate thread written to
> >> each table that the appliation is writing to? Would
> that
> >> help performance getting the records in faster and
> reduce
> >> the amount of over-head so that the users making their
> >> requests aren't slow in getting their data.
> >>
> >> Thank you for your help.
> >>
> >> Don
> >>
> >
> >
> >.
> >|||Bulk insert techniques are the fastest way to load large volumes of data
into SQL Server. You can use SQLOLEDB IRowserFastLoad or ODBC Bulk Copy
to load data directly from a C++ program. To load data from files, you
can use T-SQL BULK INSERT, the BCP command-line utility or DTS.
Parallel loading (e.g. multiple threads) can improve throughput further.
However, you may not need to resort to that since you can probably load
thousands of rows in a few seconds.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Don" <ddachner@.yahoo.com> wrote in message
news:055401c37ee9$c88931e0$a401280a@.phx.gbl...
> The developer is using INSERT. Looks like one record at
> at time from C++.
> Bulk insert would be better?
> Don
> >--Original Message--
> >How are you inserting them? Have you tried using Bulk
> Insert?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Don" <ddachner@.yahoo.com> wrote in message
> >news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> >> SQL 7.0/NT 4.0 latest SPs
> >>
> >> Performance question.
> >>
> >> One application will be writing to two tables. 1000s of
> >> records at a time, in bursts, and at the same time, the
> >> tables will be serving up data to end-users from a
> >> separate application.
> >>
> >> Question: Should there be a separate thread written to
> >> each table that the appliation is writing to? Would
> that
> >> help performance getting the records in faster and
> reduce
> >> the amount of over-head so that the users making their
> >> requests aren't slow in getting their data.
> >>
> >> Thank you for your help.
> >>
> >> Don
> >>
> >
> >
> >.
> >
Performance question.
One application will be writing to two tables. 1000s of
records at a time, in bursts, and at the same time, the
tables will be serving up data to end-users from a
separate application.
Question: Should there be a separate thread written to
each table that the appliation is writing to? Would that
help performance getting the records in faster and reduce
the amount of over-head so that the users making their
requests aren't slow in getting their data.
Thank you for your help.
DonHow are you inserting them? Have you tried using Bulk Insert?
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> SQL 7.0/NT 4.0 latest SPs
> Performance question.
> One application will be writing to two tables. 1000s of
> records at a time, in bursts, and at the same time, the
> tables will be serving up data to end-users from a
> separate application.
> Question: Should there be a separate thread written to
> each table that the appliation is writing to? Would that
> help performance getting the records in faster and reduce
> the amount of over-head so that the users making their
> requests aren't slow in getting their data.
> Thank you for your help.
> Don
>|||The developer is using INSERT. Looks like one record at
at time from C++.
Bulk insert would be better?
Don
>--Original Message--
>How are you inserting them? Have you tried using Bulk
Insert?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Don" <ddachner@.yahoo.com> wrote in message
>news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
>> SQL 7.0/NT 4.0 latest SPs
>> Performance question.
>> One application will be writing to two tables. 1000s of
>> records at a time, in bursts, and at the same time, the
>> tables will be serving up data to end-users from a
>> separate application.
>> Question: Should there be a separate thread written to
>> each table that the appliation is writing to? Would
that
>> help performance getting the records in faster and
reduce
>> the amount of over-head so that the users making their
>> requests aren't slow in getting their data.
>> Thank you for your help.
>> Don
>
>.
>|||A Bulk Insert would generally be many times faster than individual inserts.
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:055401c37ee9$c88931e0$a401280a@.phx.gbl...
> The developer is using INSERT. Looks like one record at
> at time from C++.
> Bulk insert would be better?
> Don
> >--Original Message--
> >How are you inserting them? Have you tried using Bulk
> Insert?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Don" <ddachner@.yahoo.com> wrote in message
> >news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> >> SQL 7.0/NT 4.0 latest SPs
> >>
> >> Performance question.
> >>
> >> One application will be writing to two tables. 1000s of
> >> records at a time, in bursts, and at the same time, the
> >> tables will be serving up data to end-users from a
> >> separate application.
> >>
> >> Question: Should there be a separate thread written to
> >> each table that the appliation is writing to? Would
> that
> >> help performance getting the records in faster and
> reduce
> >> the amount of over-head so that the users making their
> >> requests aren't slow in getting their data.
> >>
> >> Thank you for your help.
> >>
> >> Don
> >>
> >
> >
> >.
> >|||Bulk insert techniques are the fastest way to load large volumes of data
into SQL Server. You can use SQLOLEDB IRowserFastLoad or ODBC Bulk Copy
to load data directly from a C++ program. To load data from files, you
can use T-SQL BULK INSERT, the BCP command-line utility or DTS.
Parallel loading (e.g. multiple threads) can improve throughput further.
However, you may not need to resort to that since you can probably load
thousands of rows in a few seconds.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Don" <ddachner@.yahoo.com> wrote in message
news:055401c37ee9$c88931e0$a401280a@.phx.gbl...
> The developer is using INSERT. Looks like one record at
> at time from C++.
> Bulk insert would be better?
> Don
> >--Original Message--
> >How are you inserting them? Have you tried using Bulk
> Insert?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Don" <ddachner@.yahoo.com> wrote in message
> >news:04b701c37ed2$a0adc300$a101280a@.phx.gbl...
> >> SQL 7.0/NT 4.0 latest SPs
> >>
> >> Performance question.
> >>
> >> One application will be writing to two tables. 1000s of
> >> records at a time, in bursts, and at the same time, the
> >> tables will be serving up data to end-users from a
> >> separate application.
> >>
> >> Question: Should there be a separate thread written to
> >> each table that the appliation is writing to? Would
> that
> >> help performance getting the records in faster and
> reduce
> >> the amount of over-head so that the users making their
> >> requests aren't slow in getting their data.
> >>
> >> Thank you for your help.
> >>
> >> Don
> >>
> >
> >
> >.
> >
Multiple Threads and Licensing
Hello,
I am working on a project that will require the use of SQL Server 2005
Workgroup Edition. We were planning to use the version that comes with
5 CALs instead of the version that is licensed based on processors due
to the enormous cost difference.
The application I am working on is using c#. This program will spawn
multiple threads to communicate with instruments attached on the network
Each of these threads will have a connection to the db to save data. I
am being told that each thread will require a CAL. Is that correct? I
can not find anything in the licensing information that explicitly
states this. It was my understanding that a CAL is based on user or
device and could make as many connections to the db as needed.
ThanksIf you have only 5 instruments then you should be OK. Each instrument is a
user and can spawn multiple connections although I don't understand why it
would need to.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||If you have only 5 instruments then you should be OK. Each instrument is a
user and can spawn multiple connections although I don't understand why it
would need to.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||Hi Roger,
Thanks for replying.
I want to make sure I understand how this works. My program will be
running on the same computer as the SQL server. It will collect data
from up to 32 instruments in 32 threads. Based on this data, I will
save information to the database, 1 record for each instrument. Is this
considered multiplexing or pooling and that is why I would need 32 CALs?
If I used just 1 connection would 1 CAL be sufficient?
I will also have a couple of other computers that connect to this main
computer and view and edit the data. I know these will require separate
CALs.
I called MS Volume Licensing and was told that for each connection to
the database I needed a CAL period. If I have 1 user that opens up 5
connections (example 5 different programs) to the database that would
use 5 CALs. If I took 1 program that made a connection to the db and
ran it twice on the same computer that would require 2 CALS. Any
program I write needs a CAL for each connection to the database. To me
this seems like the CALs are based on connections and not users or
devices. Is that correct?
So I guess my real question is: Is a user or device CAL is only good
for 1 connection to the database? If the program this user is running
for whatever reason makes 2 connections to the database it requires 2
CALs even though its coming from the same user or device?
I have searched MS's website and have not found any solid information on
how this licensing is really suppose to work.
Thanks again
Roger Wolter[MSFT] wrote:
> If you have only 5 instruments then you should be OK. Each instrument is
a
> user and can spawn multiple connections although I don't understand why it
> would need to.
>|||Hi Roger,
Thanks for replying.
I want to make sure I understand how this works. My program will be
running on the same computer as the SQL server. It will collect data
from up to 32 instruments in 32 threads. Based on this data, I will
save information to the database, 1 record for each instrument. Is this
considered multiplexing or pooling and that is why I would need 32 CALs?
If I used just 1 connection would 1 CAL be sufficient?
I will also have a couple of other computers that connect to this main
computer and view and edit the data. I know these will require separate
CALs.
I called MS Volume Licensing and was told that for each connection to
the database I needed a CAL period. If I have 1 user that opens up 5
connections (example 5 different programs) to the database that would
use 5 CALs. If I took 1 program that made a connection to the db and
ran it twice on the same computer that would require 2 CALS. Any
program I write needs a CAL for each connection to the database. To me
this seems like the CALs are based on connections and not users or
devices. Is that correct?
So I guess my real question is: Is a user or device CAL is only good
for 1 connection to the database? If the program this user is running
for whatever reason makes 2 connections to the database it requires 2
CALs even though its coming from the same user or device?
I have searched MS's website and have not found any solid information on
how this licensing is really suppose to work.
Thanks again
Roger Wolter[MSFT] wrote:
> If you have only 5 instruments then you should be OK. Each instrument is
a
> user and can spawn multiple connections although I don't understand why it
> would need to.
>|||First, (disclaimer) I'm not a licensing expert.
As I understand your problem, you have one client program (where it executes
is immaterial -standalone or server).
That one client application has multiple threads connecting to various
input/output devices -including keyboard, mouse, etc.
That one client application obtains information from those input/output
devices, and then that one client saves data in SQL Server.
I believe you have one client requiring one CAL.
The various input/output devices are not connecting to SQL Server.
Consider other various input/output devices. Should you have a separate CAL
for keyboard, mouse, PLC, barcode reader, digital tablet, etc. I think you
can see where I'm going. If your various 'instruments' directly connected to
SQL Server, (and had the intelligence to use that connection) they would
require CALs -but they do not. You application is not serving as a 'proxy'
for these input/output devices (as a web server serves as proxy to
individual client sessions.)
If I have 10 (or whatever number) of client applications running on a single
computer, each running in its own thread, each connected to SQL Server, I
only need a single CAL (either device or user). Except of course, if by
using terminal services, or Citrix, my computer is serving as a proxy for
other computers -then each proxied connection requires its own CAL.
Let me know if this helps,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||My interpretation is that you would need 32 CALs to cover your 32 devices
but I'm not a lawyer so you need to go with what the licensing people say.
To me, your situation is the same as 32 users connecting to a middle tier
application that opens up one or more connections to the database. In that
case you pay for 32 CALs because what counts is users of the database not
connections. Conversely, those same 32 users could each open 2 connections
to the database and still only require 32 CALs. I don't know if there's a
different policy if your users are really inanimate objects (I've had users
that were pretty inanimate but that's a different story) so you need to talk
to the licensing people.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:%231sopWtlGHA.4076@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hi Roger,
> Thanks for replying.
> I want to make sure I understand how this works. My program will be
> running on the same computer as the SQL server. It will collect data from
> up to 32 instruments in 32 threads. Based on this data, I will save
> information to the database, 1 record for each instrument. Is this
> considered multiplexing or pooling and that is why I would need 32 CALs?
> If I used just 1 connection would 1 CAL be sufficient?
> I will also have a couple of other computers that connect to this main
> computer and view and edit the data. I know these will require separate
> CALs.
> I called MS Volume Licensing and was told that for each connection to the
> database I needed a CAL period. If I have 1 user that opens up 5
> connections (example 5 different programs) to the database that would use
> 5 CALs. If I took 1 program that made a connection to the db and ran it
> twice on the same computer that would require 2 CALS. Any program I write
> needs a CAL for each connection to the database. To me this seems like
> the CALs are based on connections and not users or devices. Is that
> correct?
> So I guess my real question is: Is a user or device CAL is only good for
> 1 connection to the database? If the program this user is running for
> whatever reason makes 2 connections to the database it requires 2 CALs
> even though its coming from the same user or device?
> I have searched MS's website and have not found any solid information on
> how this licensing is really suppose to work.
> Thanks again
> Roger Wolter[MSFT] wrote:|||As I said before, I'm not a licensing expert. I have engaged in lengthly
conversations with legal staff at several major manufacturing 'partners'
about my concerns in regards to licensing. I'm relating to what I have been
told and observed to be in actual practice.
There are a number of products in the process control field that serve to
collect input from PLC devices in manfacturing, and then store that input in
database servers such as SQL Server. Those products collect the data,
perhaps 'massage' it in some way, and then save the data in a data server.
Logically, it's really not much different than using a keyboard as an input
device -and often with as little 'intelligence' as a keyboard.
According to your logic, every major manufacturer is out of complicance
since they are not buying a CAL for each and every production line input
device. (Of course, Microsoft would like that, but the very large
manfacturing user base would be extremely uncomfortable with that analysis.)
I think the key is "Can (or) Does the device directly use the connection
with or without the 'middleman' application? If so, as in the case of any
middle tier (COM server, Web server, etc.) application, a CAL is required.
It seems that if the device could never use a connection with/or without the
'middleman' application, it 'may' not require a CAL. (Note the
equivocation -I'm not a licensing specialist. And sometimes when chatting
with VL phone folks, it takes a bit of explaining (sometimes over and over)
to be clearly understood -after all, their job is to sell licenses -not help
the customer avoid unneeded licenses!)
Perhaps Patrict needed assistance in understanding how to more clearly
communicate the issues to the VL folks. If so, I hope I helped. If I muddied
the waters, I offer my regrets.
However, as always, since everything changes so fast and so much, I am open
to my continued education...(And I'm backing out of this conversation
leaving it to more learned hands.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OhoxvVxlGHA.4100@.TK2MSFTNGP05.phx.gbl...
> My interpretation is that you would need 32 CALs to cover your 32 devices
> but I'm not a lawyer so you need to go with what the licensing people say.
> To me, your situation is the same as 32 users connecting to a middle tier
> application that opens up one or more connections to the database. In
> that case you pay for 32 CALs because what counts is users of the database
> not connections. Conversely, those same 32 users could each open 2
> connections to the database and still only require 32 CALs. I don't know
> if there's a different policy if your users are really inanimate objects
> (I've had users that were pretty inanimate but that's a different story)
> so you need to talk to the licensing people.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
> news:%231sopWtlGHA.4076@.TK2MSFTNGP03.phx.gbl...
>|||First, (disclaimer) I'm not a licensing expert.
As I understand your problem, you have one client program (where it executes
is immaterial -standalone or server).
That one client application has multiple threads connecting to various
input/output devices -including keyboard, mouse, etc.
That one client application obtains information from those input/output
devices, and then that one client saves data in SQL Server.
I believe you have one client requiring one CAL.
The various input/output devices are not connecting to SQL Server.
Consider other various input/output devices. Should you have a separate CAL
for keyboard, mouse, PLC, barcode reader, digital tablet, etc. I think you
can see where I'm going. If your various 'instruments' directly connected to
SQL Server, (and had the intelligence to use that connection) they would
require CALs -but they do not. You application is not serving as a 'proxy'
for these input/output devices (as a web server serves as proxy to
individual client sessions.)
If I have 10 (or whatever number) of client applications running on a single
computer, each running in its own thread, each connected to SQL Server, I
only need a single CAL (either device or user). Except of course, if by
using terminal services, or Citrix, my computer is serving as a proxy for
other computers -then each proxied connection requires its own CAL.
Let me know if this helps,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||My interpretation is that you would need 32 CALs to cover your 32 devices
but I'm not a lawyer so you need to go with what the licensing people say.
To me, your situation is the same as 32 users connecting to a middle tier
application that opens up one or more connections to the database. In that
case you pay for 32 CALs because what counts is users of the database not
connections. Conversely, those same 32 users could each open 2 connections
to the database and still only require 32 CALs. I don't know if there's a
different policy if your users are really inanimate objects (I've had users
that were pretty inanimate but that's a different story) so you need to talk
to the licensing people.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:%231sopWtlGHA.4076@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hi Roger,
> Thanks for replying.
> I want to make sure I understand how this works. My program will be
> running on the same computer as the SQL server. It will collect data from
> up to 32 instruments in 32 threads. Based on this data, I will save
> information to the database, 1 record for each instrument. Is this
> considered multiplexing or pooling and that is why I would need 32 CALs?
> If I used just 1 connection would 1 CAL be sufficient?
> I will also have a couple of other computers that connect to this main
> computer and view and edit the data. I know these will require separate
> CALs.
> I called MS Volume Licensing and was told that for each connection to the
> database I needed a CAL period. If I have 1 user that opens up 5
> connections (example 5 different programs) to the database that would use
> 5 CALs. If I took 1 program that made a connection to the db and ran it
> twice on the same computer that would require 2 CALS. Any program I write
> needs a CAL for each connection to the database. To me this seems like
> the CALs are based on connections and not users or devices. Is that
> correct?
> So I guess my real question is: Is a user or device CAL is only good for
> 1 connection to the database? If the program this user is running for
> whatever reason makes 2 connections to the database it requires 2 CALs
> even though its coming from the same user or device?
> I have searched MS's website and have not found any solid information on
> how this licensing is really suppose to work.
> Thanks again
> Roger Wolter[MSFT] wrote:
I am working on a project that will require the use of SQL Server 2005
Workgroup Edition. We were planning to use the version that comes with
5 CALs instead of the version that is licensed based on processors due
to the enormous cost difference.
The application I am working on is using c#. This program will spawn
multiple threads to communicate with instruments attached on the network
Each of these threads will have a connection to the db to save data. I
am being told that each thread will require a CAL. Is that correct? I
can not find anything in the licensing information that explicitly
states this. It was my understanding that a CAL is based on user or
device and could make as many connections to the db as needed.
ThanksIf you have only 5 instruments then you should be OK. Each instrument is a
user and can spawn multiple connections although I don't understand why it
would need to.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||If you have only 5 instruments then you should be OK. Each instrument is a
user and can spawn multiple connections although I don't understand why it
would need to.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||Hi Roger,
Thanks for replying.
I want to make sure I understand how this works. My program will be
running on the same computer as the SQL server. It will collect data
from up to 32 instruments in 32 threads. Based on this data, I will
save information to the database, 1 record for each instrument. Is this
considered multiplexing or pooling and that is why I would need 32 CALs?
If I used just 1 connection would 1 CAL be sufficient?
I will also have a couple of other computers that connect to this main
computer and view and edit the data. I know these will require separate
CALs.
I called MS Volume Licensing and was told that for each connection to
the database I needed a CAL period. If I have 1 user that opens up 5
connections (example 5 different programs) to the database that would
use 5 CALs. If I took 1 program that made a connection to the db and
ran it twice on the same computer that would require 2 CALS. Any
program I write needs a CAL for each connection to the database. To me
this seems like the CALs are based on connections and not users or
devices. Is that correct?
So I guess my real question is: Is a user or device CAL is only good
for 1 connection to the database? If the program this user is running
for whatever reason makes 2 connections to the database it requires 2
CALs even though its coming from the same user or device?
I have searched MS's website and have not found any solid information on
how this licensing is really suppose to work.
Thanks again
Roger Wolter[MSFT] wrote:
> If you have only 5 instruments then you should be OK. Each instrument is
a
> user and can spawn multiple connections although I don't understand why it
> would need to.
>|||Hi Roger,
Thanks for replying.
I want to make sure I understand how this works. My program will be
running on the same computer as the SQL server. It will collect data
from up to 32 instruments in 32 threads. Based on this data, I will
save information to the database, 1 record for each instrument. Is this
considered multiplexing or pooling and that is why I would need 32 CALs?
If I used just 1 connection would 1 CAL be sufficient?
I will also have a couple of other computers that connect to this main
computer and view and edit the data. I know these will require separate
CALs.
I called MS Volume Licensing and was told that for each connection to
the database I needed a CAL period. If I have 1 user that opens up 5
connections (example 5 different programs) to the database that would
use 5 CALs. If I took 1 program that made a connection to the db and
ran it twice on the same computer that would require 2 CALS. Any
program I write needs a CAL for each connection to the database. To me
this seems like the CALs are based on connections and not users or
devices. Is that correct?
So I guess my real question is: Is a user or device CAL is only good
for 1 connection to the database? If the program this user is running
for whatever reason makes 2 connections to the database it requires 2
CALs even though its coming from the same user or device?
I have searched MS's website and have not found any solid information on
how this licensing is really suppose to work.
Thanks again
Roger Wolter[MSFT] wrote:
> If you have only 5 instruments then you should be OK. Each instrument is
a
> user and can spawn multiple connections although I don't understand why it
> would need to.
>|||First, (disclaimer) I'm not a licensing expert.
As I understand your problem, you have one client program (where it executes
is immaterial -standalone or server).
That one client application has multiple threads connecting to various
input/output devices -including keyboard, mouse, etc.
That one client application obtains information from those input/output
devices, and then that one client saves data in SQL Server.
I believe you have one client requiring one CAL.
The various input/output devices are not connecting to SQL Server.
Consider other various input/output devices. Should you have a separate CAL
for keyboard, mouse, PLC, barcode reader, digital tablet, etc. I think you
can see where I'm going. If your various 'instruments' directly connected to
SQL Server, (and had the intelligence to use that connection) they would
require CALs -but they do not. You application is not serving as a 'proxy'
for these input/output devices (as a web server serves as proxy to
individual client sessions.)
If I have 10 (or whatever number) of client applications running on a single
computer, each running in its own thread, each connected to SQL Server, I
only need a single CAL (either device or user). Except of course, if by
using terminal services, or Citrix, my computer is serving as a proxy for
other computers -then each proxied connection requires its own CAL.
Let me know if this helps,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||My interpretation is that you would need 32 CALs to cover your 32 devices
but I'm not a lawyer so you need to go with what the licensing people say.
To me, your situation is the same as 32 users connecting to a middle tier
application that opens up one or more connections to the database. In that
case you pay for 32 CALs because what counts is users of the database not
connections. Conversely, those same 32 users could each open 2 connections
to the database and still only require 32 CALs. I don't know if there's a
different policy if your users are really inanimate objects (I've had users
that were pretty inanimate but that's a different story) so you need to talk
to the licensing people.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:%231sopWtlGHA.4076@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hi Roger,
> Thanks for replying.
> I want to make sure I understand how this works. My program will be
> running on the same computer as the SQL server. It will collect data from
> up to 32 instruments in 32 threads. Based on this data, I will save
> information to the database, 1 record for each instrument. Is this
> considered multiplexing or pooling and that is why I would need 32 CALs?
> If I used just 1 connection would 1 CAL be sufficient?
> I will also have a couple of other computers that connect to this main
> computer and view and edit the data. I know these will require separate
> CALs.
> I called MS Volume Licensing and was told that for each connection to the
> database I needed a CAL period. If I have 1 user that opens up 5
> connections (example 5 different programs) to the database that would use
> 5 CALs. If I took 1 program that made a connection to the db and ran it
> twice on the same computer that would require 2 CALS. Any program I write
> needs a CAL for each connection to the database. To me this seems like
> the CALs are based on connections and not users or devices. Is that
> correct?
> So I guess my real question is: Is a user or device CAL is only good for
> 1 connection to the database? If the program this user is running for
> whatever reason makes 2 connections to the database it requires 2 CALs
> even though its coming from the same user or device?
> I have searched MS's website and have not found any solid information on
> how this licensing is really suppose to work.
> Thanks again
> Roger Wolter[MSFT] wrote:|||As I said before, I'm not a licensing expert. I have engaged in lengthly
conversations with legal staff at several major manufacturing 'partners'
about my concerns in regards to licensing. I'm relating to what I have been
told and observed to be in actual practice.
There are a number of products in the process control field that serve to
collect input from PLC devices in manfacturing, and then store that input in
database servers such as SQL Server. Those products collect the data,
perhaps 'massage' it in some way, and then save the data in a data server.
Logically, it's really not much different than using a keyboard as an input
device -and often with as little 'intelligence' as a keyboard.
According to your logic, every major manufacturer is out of complicance
since they are not buying a CAL for each and every production line input
device. (Of course, Microsoft would like that, but the very large
manfacturing user base would be extremely uncomfortable with that analysis.)
I think the key is "Can (or) Does the device directly use the connection
with or without the 'middleman' application? If so, as in the case of any
middle tier (COM server, Web server, etc.) application, a CAL is required.
It seems that if the device could never use a connection with/or without the
'middleman' application, it 'may' not require a CAL. (Note the
equivocation -I'm not a licensing specialist. And sometimes when chatting
with VL phone folks, it takes a bit of explaining (sometimes over and over)
to be clearly understood -after all, their job is to sell licenses -not help
the customer avoid unneeded licenses!)
Perhaps Patrict needed assistance in understanding how to more clearly
communicate the issues to the VL folks. If so, I hope I helped. If I muddied
the waters, I offer my regrets.
However, as always, since everything changes so fast and so much, I am open
to my continued education...(And I'm backing out of this conversation
leaving it to more learned hands.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OhoxvVxlGHA.4100@.TK2MSFTNGP05.phx.gbl...
> My interpretation is that you would need 32 CALs to cover your 32 devices
> but I'm not a lawyer so you need to go with what the licensing people say.
> To me, your situation is the same as 32 users connecting to a middle tier
> application that opens up one or more connections to the database. In
> that case you pay for 32 CALs because what counts is users of the database
> not connections. Conversely, those same 32 users could each open 2
> connections to the database and still only require 32 CALs. I don't know
> if there's a different policy if your users are really inanimate objects
> (I've had users that were pretty inanimate but that's a different story)
> so you need to talk to the licensing people.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
> news:%231sopWtlGHA.4076@.TK2MSFTNGP03.phx.gbl...
>|||First, (disclaimer) I'm not a licensing expert.
As I understand your problem, you have one client program (where it executes
is immaterial -standalone or server).
That one client application has multiple threads connecting to various
input/output devices -including keyboard, mouse, etc.
That one client application obtains information from those input/output
devices, and then that one client saves data in SQL Server.
I believe you have one client requiring one CAL.
The various input/output devices are not connecting to SQL Server.
Consider other various input/output devices. Should you have a separate CAL
for keyboard, mouse, PLC, barcode reader, digital tablet, etc. I think you
can see where I'm going. If your various 'instruments' directly connected to
SQL Server, (and had the intelligence to use that connection) they would
require CALs -but they do not. You application is not serving as a 'proxy'
for these input/output devices (as a web server serves as proxy to
individual client sessions.)
If I have 10 (or whatever number) of client applications running on a single
computer, each running in its own thread, each connected to SQL Server, I
only need a single CAL (either device or user). Except of course, if by
using terminal services, or Citrix, my computer is serving as a proxy for
other computers -then each proxied connection requires its own CAL.
Let me know if this helps,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:uvdvuKjlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I am working on a project that will require the use of SQL Server 2005
> Workgroup Edition. We were planning to use the version that comes with 5
> CALs instead of the version that is licensed based on processors due to
> the enormous cost difference.
> The application I am working on is using c#. This program will spawn
> multiple threads to communicate with instruments attached on the network
> Each of these threads will have a connection to the db to save data. I am
> being told that each thread will require a CAL. Is that correct? I can
> not find anything in the licensing information that explicitly states
> this. It was my understanding that a CAL is based on user or device and
> could make as many connections to the db as needed.
> Thanks|||My interpretation is that you would need 32 CALs to cover your 32 devices
but I'm not a lawyer so you need to go with what the licensing people say.
To me, your situation is the same as 32 users connecting to a middle tier
application that opens up one or more connections to the database. In that
case you pay for 32 CALs because what counts is users of the database not
connections. Conversely, those same 32 users could each open 2 connections
to the database and still only require 32 CALs. I don't know if there's a
different policy if your users are really inanimate objects (I've had users
that were pretty inanimate but that's a different story) so you need to talk
to the licensing people.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Patrick Brand" <"pbrand$NOSPAM$"@.ludlums.com> wrote in message
news:%231sopWtlGHA.4076@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hi Roger,
> Thanks for replying.
> I want to make sure I understand how this works. My program will be
> running on the same computer as the SQL server. It will collect data from
> up to 32 instruments in 32 threads. Based on this data, I will save
> information to the database, 1 record for each instrument. Is this
> considered multiplexing or pooling and that is why I would need 32 CALs?
> If I used just 1 connection would 1 CAL be sufficient?
> I will also have a couple of other computers that connect to this main
> computer and view and edit the data. I know these will require separate
> CALs.
> I called MS Volume Licensing and was told that for each connection to the
> database I needed a CAL period. If I have 1 user that opens up 5
> connections (example 5 different programs) to the database that would use
> 5 CALs. If I took 1 program that made a connection to the db and ran it
> twice on the same computer that would require 2 CALS. Any program I write
> needs a CAL for each connection to the database. To me this seems like
> the CALs are based on connections and not users or devices. Is that
> correct?
> So I guess my real question is: Is a user or device CAL is only good for
> 1 connection to the database? If the program this user is running for
> whatever reason makes 2 connections to the database it requires 2 CALs
> even though its coming from the same user or device?
> I have searched MS's website and have not found any solid information on
> how this licensing is really suppose to work.
> Thanks again
> Roger Wolter[MSFT] wrote:
Multiple threads
Is it safe to use one database connection shared across multiple threads. Each thread has its own set of data i.e its own SQL command etc, but sharing a single database connection. Actually the scenario is that in a web site I want to open a single database connection when web application starts up. On each page hit will construct a SQL command and query/update the database.
I just want to know if multiple requests come at the same time will threads be blocked if I share global connection. What will happen if one request is trying to retrieve the data and another request is updating the database using the same connection?
Thanks
Hi!
I don't think it's a good idea to share DB connection across threads. One of them may open it, another may close, another may break... So many things to keep in mind.
DB connections are fast for open/close and if you make many of them, internally there is pool of connections, so framework will optimize access. If you do not have proven performance drop because of frequent connections opening, then don't waste your time with it - create, open and close connections as logic requires.
One more thing - if you have local connections, then your code is less dependent and can be better maintained, supported, evolved.
|||Let us imagine a situation that thousands of requests come to web site at a particular time then if I open connection for every request then even the connection pool will be exausted. Second thing I want to clarify that thread does not know if the connection is shared or not so thread will never end up closing the connection. Thread will talk to some kind of manager which will keep connection internally and leave it open for every request.
I still don't know how the thread behaviour will be. If all the requests use the same connection then whether each request will be serialized (running one after previous one is finished) or each can run in parallel.
Any idea?
Thanks
|||I will agree about thousands and speed problems when I will see some real numbers. So, you can try to simulate test load and watch how the system performs.
Also I want to notice, that connection is logical, it have state, which can be not only Open or Close, but also Executing, Fetching, Broken. What if one of your threads do fetching? All others must wait until it finish. And If you have 2 connections, then you can install 2 network adapters and they can split job. So I still think global connection is not a good thing to do because of scalability & reliability reasons.
Don't think of connections as of physical connections, you do not connect to some port in fact, instead you connect to client side layer, which will take care of real connection. You work with logical connections.
|||Thanks for the suggestions. I will see how it goes.
Subscribe to:
Posts (Atom)