¡Hola, chicos!

En este post me gustaría compartir contigo cómo aumentar la velocidad de tus consultas cambiando la configuración de energía del servidor.

Introducción

Si sigues a la comunidad técnica de TI, especialmente SQL Server, seguramente conoces la Fabricio Lima, director ejecutivo de Ajuste de potencia y uno de los pioneros en blogs sobre SQL Server en Brasil.

Hace unos años relató un caso muy interesante en el artículo “Casos cotidianos: ¡Compré un servidor mejor y SQL Server es más lento! Como puedes???” donde un cliente compra un servidor mucho mejor que el anterior y las consultas terminan siendo mucho más lentas que en el servidor anterior debido al plan de energía de Windows.

Desde entonces, siempre analizo esto cuando accedo a un nuevo entorno. A pesar de creer en todas las referencias que cita en su post (y yo también le creo jajaja), quise probar esto en mi propio entorno y sacar mis conclusiones al respecto.

El entorno de prueba

Para realizar las pruebas de este post, utilizaré la siguiente tabla:

USE [dirceuresende]
GO

IF (OBJECT_ID('dbo.TesteCPU') IS NOT NULL) DROP TABLE dbo.TesteCPU
SELECT TOP(10000) 
    MyInt = CONVERT(BIGINT, o1.[object_id]) + CONVERT(BIGINT, o2.[object_id]) + CONVERT(BIGINT, o3.[object_id])
INTO
    dbo.TesteCPU
FROM
    sys.objects o1
    JOIN sys.objects o2 ON o1.[object_id] < o2.[object_id]
    JOIN sys.objects o3 ON o1.[object_id] < o3.[object_id]

La consulta que utilizaré para probar el rendimiento en diferentes planes de energía será la siguiente:

SELECT
    SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))
FROM
    dbo.TesteCPU o1
    JOIN dbo.TesteCPU o2 ON o1.MyInt < o2.MyInt
OPTION(MAXDOP 16) -- Forçar usar todos os cores do meu servidor

La herramienta que utilizaré para ejecutar varias consultas simultáneamente y medir tiempos de ejecución es SQLQueryStress, de la que ya hablé en el artículo. SQL Server: cómo utilizar la herramienta SQLQueryStress.

Para gestionar mis planes de energía, activar/desactivar Turbo Boost y gestionar las frecuencias de mi procesador, uso la herramienta CPU rápida, que creo que es el mejor disponible para Windows para la gestión del procesador.

Y finalmente, el procesador utilizado en esta prueba es un Intel i9 9900K de 8 núcleos lógicos y 16 núcleos, con Turbo Boost 2.0 habilitado, donde definí una frecuencia máxima de hasta 4,7 GHz.

Las pruebas

Usando la herramienta SQLQueryStress, ejecutaré la consulta que compartí anteriormente (que obliga a usar los 16 colores) en 10 iteraciones, ejecutándola 2 veces simultáneamente en cada iteración.

Agregué un tiempo de espera de 2 segundos entre cada consulta, para dejar que el procesador “descanse” y reducir el reloj.

Y el resultado fue este:

Usando el plan de energía de ahorro de energía:
59,21 segundos de duración total, 3,91 segundos de tiempo medio de ejecución y 18,5 segundos de CPU (paralelismo) en cada iteración

Usando el plan de energía de alto rendimiento:
43,64 segundos de duración total, 2,33 segundos de tiempo medio de ejecución y 10,56 segundos de CPU (paralelismo) en cada iteración

Como puede ver, en el plan de energía de Alto Rendimiento, tuvimos una reducción en la duración total del 21%, una reducción en el tiempo promedio de ejecución del 40,4% y una reducción en el tiempo de CPU del 43%.

¿Lo que está sucediendo?

Para intentar comprender qué puede estar provocando una diferencia de rendimiento tan absurda debido a una simple configuración del plan de energía de Windows, comprendamos cómo funcionan estos planes de energía. De acuerdo a documentación oficial, tenemos 3 planes de energía por defecto en Windows:

  • Equilibrado: Este plan te brinda pleno rendimiento cuando realmente lo necesitas. Este plan ahorra energía durante los periodos de inactividad.
  • Ahorro de energía: Este plan ahorra energía al reducir el rendimiento del sistema. Puede ayudar a los usuarios de PC móviles a aprovechar al máximo una sola carga de batería.
  • Rendimiento alto: Este plan maximiza la capacidad de respuesta y el rendimiento del sistema. Los usuarios de PC móviles pueden encontrar que la carga de su batería no dura tanto cuando usan este plan.

Para comprender exactamente qué diferencia a estos planes de energía de Windows entre sí, podemos ver la configuración avanzada de cada plan de energía:

Ahorro de energía (uso mínimo de CPU al 5%, uso máximo al 100% y refrigeración pasiva)

Equilibrado (uso mínimo de CPU al 5 %, uso máximo al 100 % y refrigeración activa)

Alto rendimiento (uso mínimo de CPU al 100%, uso máximo al 100% y refrigeración activa)

Como podemos ver, la mayor diferencia está en relación a la refrigeración (activa/pasiva) y el uso mínimo de CPU. Mientras que en los modos “Equilibrado” y “Ahorro de energía”, el uso mínimo es del 5% y la frecuencia del reloj de la CPU variará del 5% al ​​100% de la capacidad máxima, dependiendo del uso, en el modo de energía “Alto rendimiento”, Windows siempre usará la capacidad máxima de la CPU en todo momento.

En los modos de energía “Equilibrado” y “Ahorro de energía”, si el servidor del banco tiene periodos en los que tiene poca actividad y de repente recibe algunas consultas pesadas, acabará teniendo que aumentar la velocidad del procesador cuando llegue esa consulta pesada e inmediatamente después de ejecutarla reducirá el reloj automáticamente para ahorrar energía.

En mis pruebas con Turbo Boost habilitado, el plan de energía "Ahorro de energía" solo elevó la frecuencia del procesador al máximo nominal del procesador (3,6 GHz) y alcanzó un mínimo de 1,4 GHz. El plan de energía "equilibrado" alcanzó los 4,7 GHz cuando ejecuté algunas consultas y después de ejecutarlas, varió entre 3,5 GHz y 4,5 GHz. En el plan “Alto Rendimiento”, la frecuencia de reloj varió entre 4,5 y 4,7 GHz.

Esto es lo que marca la diferencia en el “mundo real” cuando se utiliza un plan de energía distinto al de “Alto Rendimiento”

También puede utilizar Quick CPU para observar más de cerca los planes de energía:

Importante: Recuerda verificar si la opción “Turbo Boost” está habilitada (si tu procesador es Intel), ya que automáticamente aumenta significativamente la frecuencia de reloj de la CPU, aumentando la velocidad de las consultas. Esta característica es nativa del procesador y no se considera overclocking, ya que lo realiza la propia CPU, junto con la placa base.

Cómo identificar y cambiar su plan de energía actual

La forma más sencilla de identificar y cambiar su plan de energía actual es utilizar la pantalla Elegir plan de energía de Windows. Para abrir esta pantalla, abra la pantalla de aviso de DOS o la ventana Ejecutar (Windows + R) y escriba esto:

%windir%\system32\control.exe /name Microsoft.PowerOptions

También puedes escribir "plan de energía" en el menú Inicio.

También puede acceder a las opciones de energía a través del Panel de control:

O incluso, usando la herramienta Quick CPU

Cómo identificar y cambiar el plan de energía actual mediante SQL Server

Si no tiene acceso RDP al servidor y necesita identificarse utilizando únicamente SQL Server, puede utilizar la siguiente consulta:

DECLARE
    @value VARCHAR(64)
 
EXEC master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes',
    @value_name = 'ActivePowerScheme',
    @value = @value OUTPUT;
 
SELECT (CASE 
    WHEN @value = '381b4222-f694-41f0-9685-ff5bb260df2e' THEN '(Balanced)'
    WHEN @value = '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' THEN '(High performance)'
    WHEN @value = 'a1841308-3541-4fab-bc81-f71556f20b4a' THEN '(Power saver)'
END)

Resultado:

Y para cambiar el plan de energía también a través de SQL Server (el usuario del servicio debe tener permiso para hacerlo), puede usar el siguiente comando:

DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell')
 
IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXECUTE sp_configure 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


-- 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c (High Performance = Alta Performance)
-- 381b4222-f694-41f0-9685-ff5bb260df2e (Balanced = Balanceado)
-- a1841308-3541-4fab-bc81-f71556f20b4a (Power saver = Economia de Energia)

EXEC sys.xp_cmdshell 'powercfg.exe /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' -- High Performance


IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE sp_configure 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END

Referencias

¡Y eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y ¡hasta la próxima!