martes, 17 de agosto de 2010

Desempeño de SQL Server 2008 R2 y Max Worker Threads

El parámetro max worker threads se utiliza para configurar la cantidad de worker threads disponibles para los procesos de SQL Server, esto ayuda a optimziar el desempaño cuando gran cantidad clientes están conectados al servidor. La opción max worker threads permite que SQL Server cree un pool de worker threads para atender a gran cantidad de peticiones, lo cual mejora el desempeño. La opción por omisión es 0 y permite que SQL Server configure automáticamente el número de worker threads al inicio del servicio. Algunas veces para mejorar el desempeño es mejor especificar un valor directamente, usted puede utilizar la siguiente tabla como referencia:

 

Cantidad de CPUs

servidor 32-bit

servidor 64-bit

<= 4 procesadores

256

512

8 procesadores

288

576

16 procesadores

352

704

32 procesadores

   

Fuente: http://technet.microsoft.com/en-us/library/ms187024.aspx

 

Con base en el blog de Bob Duffy http://blogs.msdn.com/b/boduff/archive/2008/05/17/configuring-max-worker-threads-in-sql-2005.aspx se puede determinar la cantidad de threads que está utilizando SQL Server con la siguiente DMV:

select max_workers_count From sys.dm_os_sys_info

select count(*) from sys.dm_os_threads

Además Bob recomeinda la siguiente fórmula para calcular los worker threads:

 

  1. Para sistemas  x86 donde el total de procesadores lógicos es <=4
    • # max worker threads = 256
  2. Sino :
    • # max worker threads = 256 + ((# Procs – 4) * 8)
  3. Para sistemas x64 donde el total de procesadores lógicos es <= 4
    • # max worker threads = 512
  4. Sino
    • # max worker threads = 512 + ((# Procs – 4) * 16)

 

Hay que tomar en cuenta que Hyper-threading causa que SQL Server crea que tiene más procesadores físicos disponibles, puesto que aparecen como si fuesen dos cores distintos y SQL Server podría crear demasiados threads, en ese caso usted podría especificar directamente la cantidad de worker threads.

 

Saludos,

Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP

image image image image

http://mswindowscr.org

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

http://ecastrom.blogspot.com

http://ecastrom.wordpress.com

http://ecastrom.spaces.live.com

http://universosql.blogspot.com

http://todosobresql.blogspot.com

http://todosobresqlserver.wordpress.com

http://mswindowscr.org/blogs/sql/default.aspx

http://citicr.org/blogs/noticias/default.aspx

http://sqlserverpedia.blogspot.com/

Note: Cross posted from Eduardo Castro.

Permalink

SQL Server 2008 R2 Max Worker Threads and performance

The parameter max worker threads is used to configure the number of worker threads available to Microsoft SQL Server processes, this helps optimize performance when large numbers of clients are connected to the server. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query request, which improves performance. The default value of this options is 0, and allows SQL Server to automatically configure the number of worker threads at startup. Sometimes to improve performance is better to specify a specific value, you can use the following table as reference:

 

Number of CPUs

32-bit computer

64-bit computer

<= 4 processors

256

512

8 processors

288

576

16 processors

352

704

32 processors

   

Source: http://technet.microsoft.com/en-us/library/ms187024.aspx

 

Based on Bob Duffy blog http://blogs.msdn.com/b/boduff/archive/2008/05/17/configuring-max-worker-threads-in-sql-2005.aspx you can determine the maximum number of threads SQL Server has actually configured and how many it is currently using by the following DMVs:

select max_workers_count From sys.dm_os_sys_info

select count(*) from sys.dm_os_threads

Bob also recommends a formula to determine the max worker processes as follows:

 

  1. For x86 systems where total number of logical processors <=4
    • # max worker threads = 256
  2. Otherwise:
    • # max worker threads = 256 + ((# Procs – 4) * 8)
  3. For x64 systems where total number of logical processors <= 4
    • # max worker threads = 512
  4. Otherwise
    • # max worker threads = 512 + ((# Procs – 4) * 16)

 

Be aware that Hyper-threading causes SQL Server a lot of hassle is that it appears as two physical cores, so may result in SQL Server allocating too many threads, so in that case may need to set an specific value for the max worker threads option.

 

Regards ,

Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP

image image image image

http://mswindowscr.org

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

http://ecastrom.blogspot.com

http://ecastrom.wordpress.com

http://ecastrom.spaces.live.com

http://universosql.blogspot.com

http://todosobresql.blogspot.com

http://todosobresqlserver.wordpress.com

http://mswindowscr.org/blogs/sql/default.aspx

http://citicr.org/blogs/noticias/default.aspx

http://sqlserverpedia.blogspot.com/

Note: Cross posted from Eduardo Castro.

Permalink

miércoles, 11 de agosto de 2010

Introduction to SQL Server 2008 R2

In this presentation we review the new features in SQL Server 2008 R2. SQL Server 2008 R2 comes up with many enhancements in Security, Availability, Performance, Management, Storage, Programmability, BI( Business Intelligence ) and SSRS (Reporting Services)

 

Regards,

Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP

image image image image

http://mswindowscr.org

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

http://ecastrom.blogspot.com

http://ecastrom.wordpress.com

http://ecastrom.spaces.live.com

http://universosql.blogspot.com

http://todosobresql.blogspot.com

http://todosobresqlserver.wordpress.com

http://mswindowscr.org/blogs/sql/default.aspx

http://citicr.org/blogs/noticias/default.aspx

http://sqlserverpedia.blogspot.com/

miércoles, 4 de agosto de 2010

Testing Idera SQL Server Diagnostics Manager

I have being testing IDERA SQL Server Diagnostics Manager, in this blog I show you the basic steps to configure it to monitor a single SQL Server Instance. Idera SQL Server Diagnostic Managers offers an automated way to monitor your SQL Server Instances, after you install the software, you must follow this steps to configure it:

 

1. Add the SQL Server Instance to be monitored

image

 

image

image

2. You must configure what diagnostic information you want to collect

image

3. If you want to monitor queries change the configuration as follows

image

4. Click on start console

image

5. On the console you can view the critical queries

image

6. The query monitor can configure with thresholds

image

7.If you want the check the overall status of the server you can do it in the summary window

 

image

 

Regards,

Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP

image image image image

http://mswindowscr.org

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

http://ecastrom.blogspot.com

http://ecastrom.wordpress.com

http://ecastrom.spaces.live.com

http://universosql.blogspot.com

http://todosobresql.blogspot.com

http://todosobresqlserver.wordpress.com

http://mswindowscr.org/blogs/sql/default.aspx

http://citicr.org/blogs/noticias/default.aspx

http://sqlserverpedia.blogspot.com/

Note: Cross posted from Eduardo Castro.

Permalink