Friday, March 30, 2012
Max Num of Worker Threads and User Connections/Logins
I need to know what is the relationship between the "Maxium Number of Worker Threads" setting (default = 255) and user connections/sessions/logins.
From time to time, we are experiencing random login failure for several busy databases. On average, we see about 264 total sessions on the box and we were wondering if the max worker threads number, bumping it up would fix the issue.
Can anyone offer any suggestions?
Thanks in advance,
Errol Neal
As long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
"Errol U. Neal" <eneal@.enhtech.com> wrote in message
news:09B0F39B-1468-4069-A8A1-BF2AF2E645C8@.microsoft.com...
> Hi everyone,
> I need to know what is the relationship between the "Maxium Number of
Worker Threads" setting (default = 255) and user
connections/sessions/logins.
> From time to time, we are experiencing random login failure for several
busy databases. On average, we see about 264 total sessions on the box and
we were wondering if the max worker threads number, bumping it up would fix
the issue.
> Can anyone offer any suggestions?
>
> Thanks in advance,
>
> Errol Neal
|||Okay. I understand that. Thanks!
But why are we having random login failures? We are per-processor licensing so I am confused. Its not that the login is timing out. They are outright failing and I know because I am doing a SQL Server Trace and watching them as they fail.
-- Wayne Snyder wrote: --
As long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
|||Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Errol Uriel Neal" <eneal@.enhtech.com> wrote in message
news:94D7AB61-A7F2-4FA1-B19C-D79B64B3B23D@.microsoft.com...
> Okay. I understand that. Thanks!
> But why are we having random login failures? We are per-processor
licensing so I am confused. Its not that the login is timing out. They are
outright failing and I know because I am doing a SQL Server Trace and
watching them as they fail.
>
> -- Wayne Snyder wrote: --
> As long as there are fewer than "Max worker threads" number of
connections,
> each connection gets its own individual thread... Once you go over
that
> number, several connections will share the same thread... You can
increase
> the number of worker threads, but that uses more memory ( even if
there are
> fewer connections.)
> Thread sharing could result in front end timeouts ( especially if the
other
> connection you are sharing the thread with is a busy one.)
>
|||No. There is nothing like that or remotely similiar to that in the logs.
I've been having this issue now for over a month. I even completely rebuilt my SQL Server because
of it.
-- Jasper Smith wrote: --
Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
Max Num of Worker Threads and User Connections/Logins
I need to know what is the relationship between the "Maxium Number of Worker
Threads" setting (default = 255) and user connections/sessions/logins.
From time to time, we are experiencing random login failure for several busy
databases. On average, we see about 264 total sessions on the box and we we
re wondering if the max worker threads number, bumping it up would fix the i
ssue.
Can anyone offer any suggestions?
Thanks in advance,
Errol NealAs long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
"Errol U. Neal" <eneal@.enhtech.com> wrote in message
news:09B0F39B-1468-4069-A8A1-BF2AF2E645C8@.microsoft.com...
> Hi everyone,
> I need to know what is the relationship between the "Maxium Number of
Worker Threads" setting (default = 255) and user
connections/sessions/logins.
> From time to time, we are experiencing random login failure for several
busy databases. On average, we see about 264 total sessions on the box and
we were wondering if the max worker threads number, bumping it up would fix
the issue.
> Can anyone offer any suggestions?
>
> Thanks in advance,
>
> Errol Neal|||Okay. I understand that. Thanks!
But why are we having random login failures? We are per-processor licensing
so I am confused. Its not that the login is timing out. They are outright fa
iling and I know because I am doing a SQL Server Trace and watching them as
they fail.
-- Wayne Snyder wrote: --
As long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)|||Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Errol Uriel Neal" <eneal@.enhtech.com> wrote in message
news:94D7AB61-A7F2-4FA1-B19C-D79B64B3B23D@.microsoft.com...
> Okay. I understand that. Thanks!
> But why are we having random login failures? We are per-processor
licensing so I am confused. Its not that the login is timing out. They are
outright failing and I know because I am doing a SQL Server Trace and
watching them as they fail.
>
> -- Wayne Snyder wrote: --
> As long as there are fewer than "Max worker threads" number of
connections,
> each connection gets its own individual thread... Once you go over
that
> number, several connections will share the same thread... You can
increase
> the number of worker threads, but that uses more memory ( even if
there are
> fewer connections.)
> Thread sharing could result in front end timeouts ( especially if the
other
> connection you are sharing the thread with is a busy one.)
>|||No. There is nothing like that or remotely similiar to that in the logs.
I've been having this issue now for over a month. I even completely rebuilt
my SQL Server because
of it.
-- Jasper Smith wrote: --
Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.orgsql
Max Num of Worker Threads and User Connections/Logins
I need to know what is the relationship between the "Maxium Number of Worker Threads" setting (default = 255) and user connections/sessions/logins.
From time to time, we are experiencing random login failure for several busy databases. On average, we see about 264 total sessions on the box and we were wondering if the max worker threads number, bumping it up would fix the issue.
Can anyone offer any suggestions
Thanks in advance
Errol NealAs long as there are fewer than "Max worker threads" number of connections,
each connection gets its own individual thread... Once you go over that
number, several connections will share the same thread... You can increase
the number of worker threads, but that uses more memory ( even if there are
fewer connections.)
Thread sharing could result in front end timeouts ( especially if the other
connection you are sharing the thread with is a busy one.)
"Errol U. Neal" <eneal@.enhtech.com> wrote in message
news:09B0F39B-1468-4069-A8A1-BF2AF2E645C8@.microsoft.com...
> Hi everyone,
> I need to know what is the relationship between the "Maxium Number of
Worker Threads" setting (default = 255) and user
connections/sessions/logins.
> From time to time, we are experiencing random login failure for several
busy databases. On average, we see about 264 total sessions on the box and
we were wondering if the max worker threads number, bumping it up would fix
the issue.
> Can anyone offer any suggestions?
>
> Thanks in advance,
>
> Errol Neal|||Do you see any errors on the SQL error log e.g. "could not spawn process
login thread" or similar ? We have seen these under conditions of high
memory contention especially in the MemToLeave area where new threads cannot
allocate enough memory to start.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Errol Uriel Neal" <eneal@.enhtech.com> wrote in message
news:94D7AB61-A7F2-4FA1-B19C-D79B64B3B23D@.microsoft.com...
> Okay. I understand that. Thanks!
> But why are we having random login failures? We are per-processor
licensing so I am confused. Its not that the login is timing out. They are
outright failing and I know because I am doing a SQL Server Trace and
watching them as they fail.
>
> -- Wayne Snyder wrote: --
> As long as there are fewer than "Max worker threads" number of
connections,
> each connection gets its own individual thread... Once you go over
that
> number, several connections will share the same thread... You can
increase
> the number of worker threads, but that uses more memory ( even if
there are
> fewer connections.)
> Thread sharing could result in front end timeouts ( especially if the
other
> connection you are sharing the thread with is a busy one.)
>|||No. There is nothing like that or remotely similiar to that in the logs.
I've been having this issue now for over a month. I even completely rebuilt my SQL Server because
of it.
-- Jasper Smith wrote: --
Do you see any errors on the SQL error log e.g. "could not spawn proces
login thread" or similar ? We have seen these under conditions of hig
memory contention especially in the MemToLeave area where new threads canno
allocate enough memory to start
--
HT
Jasper Smith (SQL Server MVP
I support PASS - the definitive, globa
community for SQL Server professionals
http://www.sqlpass.or
max memory setting and /3GB switch
set.
1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
GB to account for the OS?
2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
Thanks.
YOu need PAE to let the OS see more than 4GB. The /3GB is optional but I
would recommend it in most cases with only 8GB of ram. Absolutely set the
max memory setting to less than 8GB. Try 7.0 and see how that works. Once
you turn on AWE the memory is fixed and you need to leave room for the OS
and any other applications or processes.
Andrew J. Kelly SQL MVP
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:E88EF65B-B3A4-4BF4-BC6C-C59A3590049F@.microsoft.com...
>A server with 8 GB RAM and MS SQL only server with one instance, /PAE and
>AWE
> set.
>
> 1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
> GB to account for the OS?
> 2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
> Thanks.
max memory setting and /3GB switch
E
set.
1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
GB to account for the OS?
2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
Thanks.YOu need PAE to let the OS see more than 4GB. The /3GB is optional but I
would recommend it in most cases with only 8GB of ram. Absolutely set the
max memory setting to less than 8GB. Try 7.0 and see how that works. Once
you turn on AWE the memory is fixed and you need to leave room for the OS
and any other applications or processes.
Andrew J. Kelly SQL MVP
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:E88EF65B-B3A4-4BF4-BC6C-C59A3590049F@.microsoft.com...
>A server with 8 GB RAM and MS SQL only server with one instance, /PAE and
>AWE
> set.
>
> 1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
> GB to account for the OS?
> 2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
> Thanks.
max memory setting and /3GB switch
set.
1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
GB to account for the OS?
2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
Thanks.YOu need PAE to let the OS see more than 4GB. The /3GB is optional but I
would recommend it in most cases with only 8GB of ram. Absolutely set the
max memory setting to less than 8GB. Try 7.0 and see how that works. Once
you turn on AWE the memory is fixed and you need to leave room for the OS
and any other applications or processes.
--
Andrew J. Kelly SQL MVP
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:E88EF65B-B3A4-4BF4-BC6C-C59A3590049F@.microsoft.com...
>A server with 8 GB RAM and MS SQL only server with one instance, /PAE and
>AWE
> set.
>
> 1) Shoud Max Server Memory be set to the full 8 GB or a less amount like 6
> GB to account for the OS?
> 2) Is it necessary to set /3GB switch or does /PAE make it irrelevant?
> Thanks.
Monday, March 26, 2012
Max character in field
I am using right now nvarchar with a length of 4000.
Is there another setting to allow even more in a field?
Thanks all,
ZathA VARCHAR can hold up to 8000 characters in SQL 2000. If you need more, look at the TEXT type.
Matrix: Hide null value row
The matrix that i have contains null values and is creating empty rows. I tried grouping the row and then setting the visible property, but that just hides the entire rows. Is there an expression that i would need to ensure that only the null rows are not visible on the matrix?
Thanks for taking the time to read.
The database tables looks like:
Month Sales Product
- - --
August 2007 700.00 Apples
August 2007 400.00 Oranges
September 2007 380.00 Apples
October 2007 1200.00 Oranges
November 2007 NULL NULL
December 2007 NULL NULL
Jan 2008 400.00 Grapefruit
The matrix looks like:
August 2007 Sept 2007 Oct 2007 Nov 2007 Dec 2007 Jan 2008
Apples 700.00 380.00
Oranges 400.00 1200.00
Grapfruit 400.00
Try filtering these records. You can use the filter within the Matrix properties to filter all records where Product Names are NULL
|||I actually tried filtering in the edit group searching for nulls but it didn't work.
I did a bit of searching and found that i needing to use this: =IsNothing(Fields!productname.Value) in the filter expression for the group.
Thanks for your help.sqlWednesday, March 21, 2012
Matrix report not setting the pagewidth properly
I have a matrix report that displays offices down the page and all the
days in a month across the page, with a subreport in each cell
displaying other data.
The problem i am having is when i load the page the first time it will
only display half the days, when i press refresh or change the zoom
level it correctly displays all the data.
I have tried setting the zoom level on the url and setting the actual
and interactive page size to A2 (594mm x 420mm) and it has made no
difference.
Any suggestions on how i can get this to display correctly?
Thanks
DarrynOn Mar 11, 9:49 pm, darryn.brya...@.gmail.com wrote:
> Hi,
> I have a matrix report that displays offices down the page and all the
> days in a month across the page, with a subreport in each cell
> displaying other data.
> The problem i am having is when i load the page the first time it will
> only display half the days, when i press refresh or change the zoom
> level it correctly displays all the data.
> I have tried setting the zoom level on the url and setting the actual
> and interactive page size to A2 (594mm x 420mm) and it has made no
> difference.
> Any suggestions on how i can get this to display correctly?
> Thanks
> Darryn
Usually, when including subreports in a table, etc of another report,
the format tends to be a little hard to control (at least, at the
granular level). If there is a way to avoid using the subreport(s) in
this way, I would suggest it. I would check the complexity of the
queries/stored procedures used for the subreports and maybe use the
Database Engine Tuning Advisor to improve the performance of the
queries/stored procedures. It seems like there might be a bottleneck
with the queries. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Monday, March 19, 2012
Matrix pivot column question
The dataset is returning:
Description | Total | Available | Section | SectionQty
Item 1 | 4 | 4 | |
Item 2 | 8 | 6 | DIS | 1
Item 2 | 8 | 6 | FNC | 1
Item 3 | 4667 | 4651 | FNC | 16
Which shows, for each item, the total # and the available #, along with
which section has how many.
The Item, Total & Available columns are set up as row groups, the Section
column is the column group, and the SectionQty is the detail.
When the report renders, I get results of:
Item | Total | Available | | DIS | FNC
Item 1 | 4 | 4 |
Item 2 | 8 | 6 | | 1 | 1
Item 3 | 4667 | 4651 | | | 16
Note the empty column showing up between the Available and DIS columns.
How do I get rid of that empty column caused by the null for the first item
(which still needs to be on the report to show the total / available qty)?
Thanks,
Waynecreate a data set to get above data then create a matix to pipulate
data
On Jan 30, 3:27=A0pm, Wayne <Wa...@.discussions.microsoft.com> wrote:
> I have a report that I'm setting up as a matrix.
> The dataset is returning:
> Description | Total | Available | Section | SectionQty
> Item 1 =A0 =A0 =A0 | =A0 =A0 =A04 =A0| 4 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 ==A0 =A0 =A0 =A0 |
> Item 2 =A0 =A0 =A0 | =A0 =A0 =A08 =A0| 6 =A0 =A0 =A0 =A0 =A0 =A0| DIS =A0 ==A0 =A0 | 1
> Item 2 =A0 =A0 =A0 | =A0 =A0 =A08 =A0| 6 =A0 =A0 =A0 =A0 =A0 =A0| FNC =A0 ==A0 =A0| 1
> Item 3 =A0 =A0 =A0 | 4667 =A0| 4651 =A0 =A0 =A0 | FNC =A0 =A0 =A0| 16
> Which shows, for each item, the total # and the available #, along with
> which section has how many.
> The Item, Total & Available columns are set up as row groups, the Section
> column is the column group, and the SectionQty is the detail.
> When the report renders, I get results of:
> Item =A0 =A0| Total | Available | =A0 =A0 | DIS | FNC
> Item 1 | =A0 =A04 =A0 =A0| =A0 =A0 4 =A0 =A0 =A0 =A0|
> Item 2 | =A0 =A08 =A0 =A0| =A0 =A0 6 =A0 =A0 =A0 =A0| =A0 =A0 | =A0 =A01 |= =A0 =A0 1
> Item 3 | 4667 =A0| 4651 =A0 =A0 =A0 | =A0 =A0 | =A0 =A0 =A0| =A0 =A016
> Note the empty column showing up between the Available and DIS columns.
> How do I get rid of that empty column caused by the null for the first ite=m
> (which still needs to be on the report to show the total / available qty)?=
> Thanks,
> Wayne
Friday, March 9, 2012
Matrix BackgroundColor
I am still having problems setting the backcolor of my matrix cells.
I am able to get a handle on eachcell as the rows and colums have vlaues
associated to them.
E.G.
I have four columns and 4 rows. these will never change.
The code below access the bottom left corner of the matrix and this works.
all i want to do is do 16 if statements to manually set the color.
The problem is when i go to add more if staements. the report jsut doesnt
compile.
How do i add 16 if statements. what is the syntax.
iif(( Fields!PotentialPoints.Value = 25)AND(Fields!PerfPoints.Value = 25),"RED","Yellow")
--
Thanks in advance,
Dave HuntIt may be easier to use VB Switch function.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"DustpanDave" <DustpanDave@.discussions.microsoft.com> wrote in message
news:9723C975-71A4-4379-846B-3EF59E7DB241@.microsoft.com...
> Hi all... again.
> I am still having problems setting the backcolor of my matrix cells.
> I am able to get a handle on eachcell as the rows and colums have vlaues
> associated to them.
> E.G.
> I have four columns and 4 rows. these will never change.
> The code below access the bottom left corner of the matrix and this works.
> all i want to do is do 16 if statements to manually set the color.
> The problem is when i go to add more if staements. the report jsut doesnt
> compile.
> How do i add 16 if statements. what is the syntax.
> iif(( Fields!PotentialPoints.Value = 25)AND(Fields!PerfPoints.Value => 25),"RED","Yellow")
> --
> Thanks in advance,
> Dave Hunt