Hello,
When the "Max Worker Threads" parameter is lower than the number of client
connections, SQL Server pools the worker threads. Is there a performance
counter to see the worker thread pool queue length (connections waiting a
thread to handle requests)?
TIA.There is Server Work Queues - Queue Length counter , which gives the
current length of the server queue related to this CPU>.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> Hello,
> When the "Max Worker Threads" parameter is lower than the number of client
> connections, SQL Server pools the worker threads. Is there a performance
> counter to see the worker thread pool queue length (connections waiting a
> thread to handle requests)?
> TIA.
>|||Is this related to SQL Server ? What I want to know is the number of "work
items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
an accurate value of the amout of work requested, wich can be used to
properly size the number of Threads/CPU needed to handle the workload
without eating 100% of the CPU.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
> There is Server Work Queues - Queue Length counter , which gives the
> current length of the server queue related to this CPU>.
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>> Hello,
>> When the "Max Worker Threads" parameter is lower than the number of
>> client
>> connections, SQL Server pools the worker threads. Is there a performance
>> counter to see the worker thread pool queue length (connections waiting a
>> thread to handle requests)?
>> TIA.
>>
>|||If it's SQL2005, something like the following may be useful:
select
scheduler_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
load_factor
from sys.dm_os_schedulers
where scheduler_id < 255
Pay attention to the work_queue_count value.
Linchi
"Olivier Matrot" wrote:
> Is this related to SQL Server ? What I want to know is the number of "work
> items" (SQL Commands) waiting to be dispatched to the thread pool. It gives
> an accurate value of the amout of work requested, wich can be used to
> properly size the number of Threads/CPU needed to handle the workload
> without eating 100% of the CPU.
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
> > There is Server Work Queues - Queue Length counter , which gives the
> > current length of the server queue related to this CPU>.
> > --
> > Jack Vamvas
> > ___________________________________
> > Receive free SQL tips - www.ciquery.com/sqlserver.htm
> > ___________________________________
> >
> >
> > "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
> > news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
> >> Hello,
> >> When the "Max Worker Threads" parameter is lower than the number of
> >> client
> >> connections, SQL Server pools the worker threads. Is there a performance
> >> counter to see the worker thread pool queue length (connections waiting a
> >> thread to handle requests)?
> >> TIA.
> >>
> >>
> >
> >
>
>|||Very interesting.
Thanks for that.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F08138DD-A908-4295-AEDA-DE39C9C745EF@.microsoft.com...
> If it's SQL2005, something like the following may be useful:
> select
> scheduler_id,
> current_tasks_count,
> runnable_tasks_count,
> current_workers_count,
> active_workers_count,
> work_queue_count,
> load_factor
> from sys.dm_os_schedulers
> where scheduler_id < 255
> Pay attention to the work_queue_count value.
> Linchi
> "Olivier Matrot" wrote:
>> Is this related to SQL Server ? What I want to know is the number of
>> "work
>> items" (SQL Commands) waiting to be dispatched to the thread pool. It
>> gives
>> an accurate value of the amout of work requested, wich can be used to
>> properly size the number of Threads/CPU needed to handle the workload
>> without eating 100% of the CPU.
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:RvydnVZuaLzD2EHZnZ2dnUVZ8qadnZ2d@.bt.com...
>> > There is Server Work Queues - Queue Length counter , which gives
>> > the
>> > current length of the server queue related to this CPU>.
>> > --
>> > Jack Vamvas
>> > ___________________________________
>> > Receive free SQL tips - www.ciquery.com/sqlserver.htm
>> > ___________________________________
>> >
>> >
>> > "Olivier Matrot" <olivier.matrot.rte@.online.nospam> wrote in message
>> > news:#1OEG3RvGHA.1296@.TK2MSFTNGP02.phx.gbl...
>> >> Hello,
>> >> When the "Max Worker Threads" parameter is lower than the number of
>> >> client
>> >> connections, SQL Server pools the worker threads. Is there a
>> >> performance
>> >> counter to see the worker thread pool queue length (connections
>> >> waiting a
>> >> thread to handle requests)?
>> >> TIA.
>> >>
>> >>
>> >
>> >
>>|||What would be the command for SQL 2000 i like to monitor this aswell.
How often to run say once hour
Thanks
