May (or CAN) I use triggers ?

I need to call a procedure/function in VB when my DB is modified
with insert, update or delete. I know that its possible to do
something called Trigger, put how can my Triggers (in SQL Server or
MSDE) call a procedure in VB ?

Just to understand, this database catch information of users
connected to a support chat and I need to know when this user get
logged in (thru the rows of my table). When I notice that a new user
have logged in, I need to support him.

Thanks.You can try to create extended stored procedure but I think it is not a good idea to use something like this in triggers (performance). Trigger could save info about new users in special table and VB could monitor this table.|||but if I do a procedure for VB to monitor a table, isn't it also bad for the permormance ?|||Originally posted by ralf_davi
but if I do a procedure for VB to monitor a table, isn't it also bad for the permormance ?

Select from table is much faster (index, etc.) than running something no-native from trigger.|||What interface are your users loggin in through? Can't that interface also alert you when the login occurs? I wonder if you aren't trying to put functionality in the wrong place.

blindman|||Originally posted by blindman
What interface are your users loggin in through? Can't that interface also alert you when the login occurs? I wonder if you aren't trying to put functionality in the wrong place.

blindman

Users are logging in thru ASP pages.
I think that using ASP is not possible to alert me or anyone else. So I try do do a VB interface.
Have another idea of what can I use ?|||All contact with your database should be done through stored procedures, and you could place code in the procedure to notify your VB application.

blindman|||Originally posted by blindman
All contact with your database should be done through stored procedures, and you could place code in the procedure to notify your VB application.

blindman

How can I work with stored procedures to notify my VB application ?
Can you give me a sign ?|||Hmmm...I'm not a VB expert, and a lot probably depends upon the particulars of your application, but some of the methods SQL Server can use to communicate with other applications are:

1) Writing files.
2) Calling xp_cmdshell to issue operating system commands.
3) Calling xp_logevent to write to the NT Event Log.
4) Creating your own extended stored procedure.
5) xp_Sendmail

blindman|||if you're carrying a pager you'd probably be better off calling an sp from the ASP page that can page you with an info pertinent to the newly logged on user. there are 2 (that I know of) methods that do not involve SQLMail, - xp_smtp_sendmail and cdo-based sendmail. i don't remember the web-sites, but google will find them.|||Originally posted by ralf_davi
Users are logging in thru ASP pages.
I think that using ASP is not possible to alert me or anyone else. So I try do do a VB interface.
Have another idea of what can I use ?

There are so many ways to alert you through ASP page. You can call your vb dll, send an email directly to you, etc. I would not suggest you to use trigger in this case.

MaximumErrorCount Problem with Copy Database Wizard

I am getting the MaximumErrorCount error when running the Copy Database Wizard. There is an error that I recognize with a stored procedure. I am not interested necessarily in fixing the stored proc error. Rather, I want to just simply copy it "as is" (working in production by the way) and then modify it locally.

I am trying to copy a production database to a develoment environment in order to test against some data. I keep getting the following error:

"The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount."

How do I configure the MaximumErrorCount on a particular Copy Database process?

I have looked through the documentation, and have not found any place in the UI to make this change.

I am copying a SQL 2000 db to a SQL 2005 (SP1) instance running on Windows XP SP2.

It makes no sense that I cannot make a configuration as recommended by the error message.

Thanks.

Dennis

I don't think the wizard UI makes this property available. However, if you save the package and open it in BIDS, you can change it there.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32

i m getting this error when i m executing a stored procedure

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

can anyone help me out what exactly the problemIt looks like you call stack exceeds maximum allowable level. It means that you run a stored procedure that executes another stored procedure that executes yet another... and so on.

Maximum number of databases used for each query in 2005?

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
MikeHoly Moly, what kind of queries are you writing!?|||Hi, I have the same problem with 2000. I am about to upgrade to 2005 and I am very concerned about this because our database requirements are rapidly growing.

Thanks,
benn

Quote:

Originally Posted by Michael.Suarez@.gmail.com

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
Mike

Maximum Attribute Values

I have a procedure that detarmines the maximum current values of user table
attributes. However I don't know how to use SQL to get the maximum value of
a
datatype. Can anyone help?There's no way to get them via SQL AFAIK, but you can just look them up in
Books Online, under the specific datatypes.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>I have a procedure that detarmines the maximum current values of user table
> attributes. However I don't know how to use SQL to get the maximum value
> of a
> datatype. Can anyone help?|||To add to Jacco's response, you can also get the min/max permissible values
for numeric types in .Net application code using constants in the
System.Data.SqlTypes namespace (e.g. SqlInt32.MaxValue).
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>I have a procedure that detarmines the maximum current values of user table
> attributes. However I don't know how to use SQL to get the maximum value
> of a
> datatype. Can anyone help?|||try looking at the system table systypes
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"marcmc" wrote:

> I have a procedure that detarmines the maximum current values of user tabl
e
> attributes. However I don't know how to use SQL to get the maximum value o
f a
> datatype. Can anyone help?|||Thx
So you can't get them in SQL? How then does it know ehen they are exceeded?
"Dan Guzman" wrote:

> To add to Jacco's response, you can also get the min/max permissible value
s
> for numeric types in .Net application code using constants in the
> System.Data.SqlTypes namespace (e.g. SqlInt32.MaxValue).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>
>|||> So you can't get them in SQL? How then does it know ehen they are
exceeded?
because the engine is not written in tsql.|||I know that it's not recommended to query system tables directly, but isn't
the information accessible through the systypes table.
INT is listed with a length of 4 but you can easily calculate the max and
min values from this.
The problem that I see would be that Microsoft modifies this table in the
future.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ustFpMBQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> There's no way to get them via SQL AFAIK, but you can just look them up in
> Books Online, under the specific datatypes.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>|||Because an overflow exception is thrown by the engine.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E68737B1-89BA-4915-A1F0-EF816A91DF05@.microsoft.com...
> Thx
> So you can't get them in SQL? How then does it know ehen they are
> exceeded?
>
> "Dan Guzman" wrote:
>|||I know of no way to get this info in Transact-SQL. The characteristics of
built-in datatypes are hard-coded in the engine code so these don't need to
be stored as meta-data or exposed.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E68737B1-89BA-4915-A1F0-EF816A91DF05@.microsoft.com...
> Thx
> So you can't get them in SQL? How then does it know ehen they are
> exceeded?
>
> "Dan Guzman" wrote:
>|||Keep in mind that underneath SQL's engine is a simple check that detemrines
whether the numeric data it is trying to store is larger than the byte thres
hold
(Integer=4, SmallInt=2, TinyInt=1). It's not "storing" the maximum decimal v
alue
per se. You just have to know that the range of an integer is 2^31 to 2^31-1
.
Thomas
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E68737B1-89BA-4915-A1F0-EF816A91DF05@.microsoft.com...
> Thx
> So you can't get them in SQL? How then does it know ehen they are exceeded
?
>
> "Dan Guzman" wrote:
>

Maximum Allowed Stored Procedure in SQL Server

Here is something that perhaps a lot of you out there wonder about..

My company is on a large Enterprise Project.
The database plans for this to be fully supported predict 1000+ stored procedures.

Question:
Will there be any limitation for an SQL Server 2000 to handle so many stored procs?

and if there is a limitation what will we have to do in order to work around it?The limitation for the total number of objects in a database is 2,147,483,647. This includes all the objects (Tables, views, functions, stored procedures etc) ...|||A fellow programmer said that there would be a problem with this number of procs. However he isn't famous for his experience :)

Thanks for the answer, it figures that there isn't any actual limitation of the number of objects you can handle. This guy was probably wrong.|||The practical limit is too high given the situation to be reached. The only issue I see is the maintenance of such huge number of stored procedures ... If you have a proper process to keep track, you are safe for sure building such solutions ...|||Agreed, you should not hit any physical limitation. I think you should carefully plan out naming conventions for your stored procedures so that you can keep things organized. One convention I like is:
sp
module name
underscore(_)
action (lower case)
noun (proper case)

For example:
spOrders_putOrderDetail
spMaintainUsers_deactivateUser
spReports_getZeroInventory

Also, you should strongly consider using standard documentation at the top of the stored procedures if you are not doing so already. I like to list the author, date created, purpose, where the sproc is used, any notes, and a revision history (with date, author, and summary of change).

Terri

max worker threads

We have some SQL Servers that receive high number of connections and stored
procedure calls.
Every once in a while, we may have a bad query plan and then one stored proc
blocks the other stored procs and in no time, we are out of worker threads
and no one can connect
Have you come across such situations ? If so, how did you handle it ? Is it
safe to increase the max worker threads ? What are the consequences ?The default setting for the 'max worker threads' option is 255,But if the
number of user connections surpasses this value the thread pooling will be
used. For example, if the maximum number of the user connections to your SQL
Server box is equal to 255, you can set the 'max worker threads' options to
255, this frees up resources for SQL Server to use elsewhere. If the maximum
number of the user connections to your SQL Server box is equal to 500, you
can set the 'max worker threads' options to 500, this can improve SQL Server
performance because thread pooling will not be used.
In your case , it sounds like it's worth investigating the query plan
problem , as increasing the worker threads will take up extra resources on
your server
Jack Vamvas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> We have some SQL Servers that receive high number of connections and
stored
> procedure calls.
> Every once in a while, we may have a bad query plan and then one stored
proc
> blocks the other stored procs and in no time, we are out of worker threads
> and no one can connect
> Have you come across such situations ? If so, how did you handle it ? Is
it
> safe to increase the max worker threads ? What are the consequences ?
>|||Hi Jack
The number of connections is not related to the number of worker threads
quite as simply as you've described.
Connetions only use threads while they're actually executing. When
connections are in idle state (usually a majority of the time in typical
OLTP apps), there are no threads associated with the connection. This
asynchronous design allows any given number of threads to service a far
larger number of connections.
The way to tell if SQL Server is running short on threads is to use the
Windows Perfmon.exe & monitor the process object sqlservr instance's thread
count counter. If the process SQL Server is running has close to 255
threads, it might be worth increasing Max Worker Threads.
In my experience, this is typically when an OLTP application has thousands
of user connections running, as statistically, this translates to ~255
connections executing concurrently therefore requiring at least 255
threads..
As for the original question, it may help to increase the number of workers,
but keep in mind that this means that SQL Server will be able to schedule
that many more threads to perform concurrent work & you need to take into
account your server resources when making this decision. This is a gross
simplification, but in my experience increasing Max Worker Threads past 255
on systems with fewer than 4 CPUs has rarely helped much as these servers
usually don't have the capacity to process in increased concurrent workload
for example. It should hurt much to increase this setting & see for yourself
whether this helps SQL Server get its work done more effieciently or whether
it contributes further to the problem by increasing context switching etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
> The default setting for the 'max worker threads' option is 255,But if the
> number of user connections surpasses this value the thread pooling will be
> used. For example, if the maximum number of the user connections to your
> SQL
> Server box is equal to 255, you can set the 'max worker threads' options
> to
> 255, this frees up resources for SQL Server to use elsewhere. If the
> maximum
> number of the user connections to your SQL Server box is equal to 500, you
> can set the 'max worker threads' options to 500, this can improve SQL
> Server
> performance because thread pooling will not be used.
> In your case , it sounds like it's worth investigating the query plan
> problem , as increasing the worker threads will take up extra resources on
> your server
> Jack Vamvas
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ? Is
> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>|||I looked at the thread counter for sqlservr process and it states around
180.
What does that mean ?
Are there 180 threads running concurrently ?
If so, would that mean that when i run sp_who2 active, i should see a high
number of connections atleast around 180 or so..
Thats not the case.
Can you tell me more ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi Jack
> The number of connections is not related to the number of worker threads
> quite as simply as you've described.
> Connetions only use threads while they're actually executing. When
> connections are in idle state (usually a majority of the time in typical
> OLTP apps), there are no threads associated with the connection. This
> asynchronous design allows any given number of threads to service a far
> larger number of connections.
> The way to tell if SQL Server is running short on threads is to use the
> Windows Perfmon.exe & monitor the process object sqlservr instance's
> thread count counter. If the process SQL Server is running has close to
> 255 threads, it might be worth increasing Max Worker Threads.
> In my experience, this is typically when an OLTP application has thousands
> of user connections running, as statistically, this translates to ~255
> connections executing concurrently therefore requiring at least 255
> threads..
> As for the original question, it may help to increase the number of
> workers, but keep in mind that this means that SQL Server will be able to
> schedule that many more threads to perform concurrent work & you need to
> take into account your server resources when making this decision. This is
> a gross simplification, but in my experience increasing Max Worker Threads
> past 255 on systems with fewer than 4 CPUs has rarely helped much as these
> servers usually don't have the capacity to process in increased concurrent
> workload for example. It should hurt much to increase this setting & see
> for yourself whether this helps SQL Server get its work done more
> effieciently or whether it contributes further to the problem by
> increasing context switching etc.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jack Vamvas" <info@.nospam.com> wrote in message
> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to your
>> SQL
>> Server box is equal to 255, you can set the 'max worker threads' options
>> to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ? Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>|||And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
and saw just 2 user processes running.
Yes its a busy system with 4000 connections but did not appear i was running
under pressure or am I and dont know about it
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>I looked at the thread counter for sqlservr process and it states around
>180.
> What does that mean ?
> Are there 180 threads running concurrently ?
> If so, would that mean that when i run sp_who2 active, i should see a high
> number of connections atleast around 180 or so..
> Thats not the case.
> Can you tell me more ?
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>> Hi Jack
>> The number of connections is not related to the number of worker threads
>> quite as simply as you've described.
>> Connetions only use threads while they're actually executing. When
>> connections are in idle state (usually a majority of the time in typical
>> OLTP apps), there are no threads associated with the connection. This
>> asynchronous design allows any given number of threads to service a far
>> larger number of connections.
>> The way to tell if SQL Server is running short on threads is to use the
>> Windows Perfmon.exe & monitor the process object sqlservr instance's
>> thread count counter. If the process SQL Server is running has close to
>> 255 threads, it might be worth increasing Max Worker Threads.
>> In my experience, this is typically when an OLTP application has
>> thousands of user connections running, as statistically, this translates
>> to ~255 connections executing concurrently therefore requiring at least
>> 255 threads..
>> As for the original question, it may help to increase the number of
>> workers, but keep in mind that this means that SQL Server will be able to
>> schedule that many more threads to perform concurrent work & you need to
>> take into account your server resources when making this decision. This
>> is a gross simplification, but in my experience increasing Max Worker
>> Threads past 255 on systems with fewer than 4 CPUs has rarely helped much
>> as these servers usually don't have the capacity to process in increased
>> concurrent workload for example. It should hurt much to increase this
>> setting & see for yourself whether this helps SQL Server get its work
>> done more effieciently or whether it contributes further to the problem
>> by increasing context switching etc.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jack Vamvas" <info@.nospam.com> wrote in message
>> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if
>> the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to your
>> SQL
>> Server box is equal to 255, you can set the 'max worker threads' options
>> to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ?
>> Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>>
>|||Hi Hassan
180 threads, means exactly that - there are 180 worker threads in SQL Server
windows process. Most would be worker threads (rather than dedicated system
threads), waiting for queries or i/o work to service. Having 180 threads
doesn't imply any system pressure on its own. It does suggest that, of your
~ 4000 connections, you might typically see peaks of ~ 180 queries actually
running concurrently.
It's possible for the # of threads to exceed connections and vice versa due
to the thread pooling model SQL Server uses in its work scheduling engine
(UMS). There's no directl correlation you can draw to assume that the number
of threads is either higher or lower than the number of connections at any
time. It's perfectly reasonable to see more threads than connections for
example where many connections may have been in use but close. The threads
will hang around in the pool for a while before SQL Server decides to trim
the worker pool. I don't have any info on exactly how or when it does this
trimming, but this is a fairly common design in multi-threaded server
programming..
It's certainly far more common to see the # of threads far lower than the #
of connections though, so if you see the # of threads > connections for
extended periods, there's possibly something wrong.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uqs1e7t8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
> and saw just 2 user processes running.
> Yes its a busy system with 4000 connections but did not appear i was
> running under pressure or am I and dont know about it
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>>I looked at the thread counter for sqlservr process and it states around
>>180.
>> What does that mean ?
>> Are there 180 threads running concurrently ?
>> If so, would that mean that when i run sp_who2 active, i should see a
>> high number of connections atleast around 180 or so..
>> Thats not the case.
>> Can you tell me more ?
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>> Hi Jack
>> The number of connections is not related to the number of worker threads
>> quite as simply as you've described.
>> Connetions only use threads while they're actually executing. When
>> connections are in idle state (usually a majority of the time in typical
>> OLTP apps), there are no threads associated with the connection. This
>> asynchronous design allows any given number of threads to service a far
>> larger number of connections.
>> The way to tell if SQL Server is running short on threads is to use the
>> Windows Perfmon.exe & monitor the process object sqlservr instance's
>> thread count counter. If the process SQL Server is running has close to
>> 255 threads, it might be worth increasing Max Worker Threads.
>> In my experience, this is typically when an OLTP application has
>> thousands of user connections running, as statistically, this translates
>> to ~255 connections executing concurrently therefore requiring at least
>> 255 threads..
>> As for the original question, it may help to increase the number of
>> workers, but keep in mind that this means that SQL Server will be able
>> to schedule that many more threads to perform concurrent work & you need
>> to take into account your server resources when making this decision.
>> This is a gross simplification, but in my experience increasing Max
>> Worker Threads past 255 on systems with fewer than 4 CPUs has rarely
>> helped much as these servers usually don't have the capacity to process
>> in increased concurrent workload for example. It should hurt much to
>> increase this setting & see for yourself whether this helps SQL Server
>> get its work done more effieciently or whether it contributes further to
>> the problem by increasing context switching etc.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jack Vamvas" <info@.nospam.com> wrote in message
>> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if
>> the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to
>> your SQL
>> Server box is equal to 255, you can set the 'max worker threads'
>> options to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one
>> stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ?
>> Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>>
>>
>