5 razones salvadoras para la salud de SQL Server

INTRODUCCIÓN

Originalmente una oferta de nicho hace más de diez años se ha convertido en una herramienta imprescindible para la comunidad de SQL Server. Este articulo profundiza en las preguntas comunes sobre las comprobaciones de estado y luego ofrece una lista de verificación junto con algunos scripts para ayudarlo a realizar sus propias comprobaciones de estado de SQL Server.

Además, la intención de abordar cinco áreas clave que podrían considerarse salvavidas. Lo que quiero decir con salvar vidas es que lo ayudarán a crecer, mostrarán su valor para la organización y mantendrán su organización funcionando sin problemas.

La verificación de la salud de SQL Server.

• Evite errores de copia de seguridad
• Manténgase al margen de las noticias y asegure sus servidores SQL
• Hacer que el mantenimiento de SQL Server sea beneficioso
• Hacer que el rendimiento sea predecible.

¿QUÉ ES UNA VERIFICACIÓN DE SALUD DEL SERVIDOR SQL?

En una palabra: Inspección. Aunque, según mis más de diez años de realizar comprobaciones de estado de SQL Server, no hay dos comprobaciones de estado iguales. Se trata de analizar un entorno, enumerar problemas y trabajar para acción correctiva. Sin embargo, hay algunos elementos comunes críticos que debe abordar, ya sea que esté creando nuevos servidores, heredando servidores, mejorando las comprobaciones en su entorno existente o revisando una instancia de SQL Server que no ha tenido una inspección en mucho tiempo.

Puedo recordar claramente a un cliente con el que trabajamos durante varios años. Llamémosle Juan. Todo el tiempo, John decía: «No sabes lo que no sabes». A veces lo decía con una sonrisa y otras veces por pura frustración. En muchos aspectos, esto podría ser el ímpetu para una verificación de estado de SQL Server.

Llámelo como quiera, pero una verificación de estado es una inspección que tiene como objetivo obtener un estado sensato de su entorno actual. A menudo, estas inspecciones descubren problemas en su entorno; otras veces, confirman la causa raíz de un problema y brindan recomendaciones concretas para resolverlo.

Un control de salud debe:

• Validar la arquitectura, el diseño de la base de datos o el código de un proceso.
• Determinar el código problemático que provoca ralentizaciones, bloqueos, bloqueos y usuarios frustrados.
• Prepárese para las actualizaciones de SQL Server, Windows, Storage o la propia aplicación.
• Resuma las consideraciones al pasar a la nube, la virtualización o el nuevo hardware.
• Enumerar los problemas operativos que afectan el negocio y las ruedas giratorias de los miembros del equipo.
• Identificar los obstáculos a los problemas de capacidad y escalabilidad.
• Asegúrese de que su entorno de SQL Server esté adecuadamente protegido y que sus clientes estén protegidos.

A partir de estos elementos, y posiblemente más, debe establecer sus objetivos de verificación de estado de SQL Server a medida que comienza a inspeccionar su entorno.

LA COMPROBACIÓN DE SANIDAD DEL SERVIDOR SQL

Poner los brazos alrededor de un entorno no es poca cosa. Debe realizar un inventario y comprender el entorno para iniciar el proceso de identificación de los problemas. También necesita comprender la instancia de SQL Server en el contexto de la infraestructura, la aplicación y el negocio. Comencemos por ese camino de recopilación de datos para su entorno de SQL Server:

Una vez que comprenda el contexto general de la instancia de SQL Server, ahora debe reducirlo un poco y comenzar a comprender los aspectos técnicos:

A medida que comienza a recopilar esta información, también debe realizar una verificación de cordura en los servidores. Considere estos elementos al realizar su inspección:

WINDOWS.

• ¿El servidor tiene demasiada o muy poca CPU, memoria y disco?
• ¿Tiene sentido el diseño de la unidad?
• ¿La versión de Windows y el Service Pack están actualizados?

 SQL SERVER.

• ¿La versión de SQL Server y el Service Pack están actualizados?
• ¿Son adecuadas las configuraciones de SQL Server (es decir, MAXDOP, tempdb, Max Memory)
• ¿Hay numerosos trabajos fallidos del agente SQL Server?
• ¿No se realizan copias de seguridad de las bases de datos, incluidas las bases de datos del sistema?
• ¿Se supone que no será necesario restaurar las bases de datos del sistema?
• ¿Se han probado las copias de seguridad?
• ¿Son los registros de transacciones proporcionalmente demasiado grandes en comparación con el tamaño de la base de datos
• ¿Está funcionando el mantenimiento durante períodos incorrectos? ¿No hay ningún tipo de mantenimiento
• ¿Falta seguridad con muchos inicios de sesión con derechos de administrador del sistema (SA), inicios de sesión compartidos y sin registro?

EVITE LOS ERRORES DE RESPALDO

¿Llegará algún día en que la recomendación sea «No necesita hacer una copia de seguridad de sus bases de datos de SQL Server?» Eso no es una realidad hoy en día. Para muchas empresas, las copias de seguridad son la última línea de defensa si se produce un problema. Algunas empresas tienen una solución sofisticada de alta disponibilidad y recuperación ante desastres. Para otras empresas, las copias de seguridad son la primera y última línea de defensa.

Aunque casi todas las empresas tienen un plan de copia de seguridad de la base de datos de SQL Server, ¿es a prueba de balas? La mayoría de las veces, el plan, la implementación y las pruebas pueden faltar en varias áreas. Aquí hay algunos que he visto a lo largo de los años. Tener un plan de copias de seguridad sólido como una roca es mucho más que simplemente emitir copias de seguridad completas todos los días. Se trata de ser capaz de recuperarse de un fracaso. Debe considerar las posibles fallas para determinar formas de mitigar los riesgos.

¿Plan de copia de seguridad de SQL Server o simulación?

Tener un plan de copias de seguridad sólido como una roca es mucho más que simplemente emitir copias de seguridad completas todos los días. Se trata de ser capaz de recuperarse de un fracaso. Debe considerar las posibles fallas para determinar formas de mitigar los riesgos.

Asegúrese de que su plan de copia de seguridad de la base de datos de SQL Server incluya lo siguiente:

• Bases de datos del sistema y del usuario: haga una copia de seguridad de todas las bases de datos definidas por el usuario y el sistema, excepto tempdb y ReportServerTempDB. Es posible poner en marcha su instancia sin las bases de datos del sistema, pero lleva mucho tiempo y es estresante cuando ocurre un desastre.
• Hágalo automático: asegúrese de que su código de respaldo incluya automáticamente nuevas bases de datos que se agreguen a la instancia.
• Riesgo frente a presupuesto: trabaje con la empresa para comprender el presupuesto disponible para las copias de seguridad y el impacto correspondiente en la empresa en función del tiempo de inactividad. Ese es un verdadero sistema de riesgo versus recompensa. Si la empresa solo le proporciona almacenamiento suficiente para un solo conjunto de copias de seguridad, infórmeles cuántos datos, horas-persona y tiempo de inactividad sufrirá la empresa. Si la empresa comprende los riesgos, es posible que pueda encontrar algún presupuesto.
• Ubicación de la copia de seguridad: Copia de seguridad en el sitio: ¿buena o mala? Copia de seguridad fuera del sitio: ¿buena o mala? Copia de seguridad en el sitio y fuera del sitio: probablemente su mejor opción para volver a estar en línea localmente lo más rápido posible o evitar que un problema más importante lo deje fuera del negocio por completo.
• Monitoreo: Es increíble ver procesos fallando por días, semanas o meses sin que nadie tenga idea hasta que es demasiado tarde. Si las copias de seguridad de su base de datos de SQL Server son su última línea de defensa, entonces asegúrese de que estén en su radar y sepa que son exitosas todos los días.
• Punto en el tiempo: tenga en cuenta que SQL Server tiene más que copias de seguridad completas. Según sus necesidades, asegúrese de incluir copias de seguridad diferenciales y de registros de transacciones para restaurar a un punto en el tiempo.
• Protección: las copias de seguridad completas de la base de datos son una versión puntual de sus datos, así que protéjalos. Tenga en cuenta que un hacker no necesita comprometer su SQL Server para acceder a sus datos. Pueden hacerlo con las copias de seguridad. Por lo tanto, proteja sus directorios de copia de seguridad, cifre la copia de seguridad y trate sus copias de seguridad con el mismo cuidado con el que trata la base de datos de producción en línea.

¿Plan de copia de seguridad de SQL Server o simulación?

Aquí hay una secuencia de comandos simple para emitir una copia de seguridad completa de todas sus bases de datos de SQL Server:

DECLARE @name VARCHAR(50) — nombre de la base de datos DECLARE @path VARCHAR(256) — ruta para los archivos de copia de seguridad

DECLARE @fileName VARCHAR(256) — nombre de archivo para la copia de seguridad DECLARE @fileDate VARCHAR(20) — utilizado para el nombre de archivo

DECLARE @cmd1 VARCHAR(500) — encadene el comando de copia de seguridad

— especificar el directorio de copia de seguridad de la base de datos

SET @path = ‘C:\Backup\’ — Cambie esto según su entorno

— especificar formato de nombre de archivo

SELECCIONE @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,’’)

DECLARE db_cursor CURSOR PARA SELECCIONAR nombre

DESDE master.dbo.sysbases de datos

DONDE nombre NO EN (‘TempDB’, ‘reportservertempdb’) — exclusión de base de datos OPEN db_cursor

OBTENER SIGUIENTE DESDE db_cursor A @name MIENTRAS @@FETCH_STATUS = 0

EMPEZAR

SET @fileName = @ruta + @name + ‘_’ + @fileDate + ‘.BAK’ SET @cmd1= ‘BACKUP DATABASE’ +'[‘ + @name + ‘]’ +

‘ AL DISCO = ‘ + char(39) + @fileName + char(39) + ‘ CON INIT, COMPRESIÓN;’

EJECUTIVO (@cmd1)

OBTENER SIGUIENTE DESDE db_cursor A @name

FINAL

CERRAR db_cursor DESACTIVAR db_cursor

Para obtener más detalles, visite Script simple para hacer una copia de seguridad de todas las bases de datos de SQL Server.

PRUEBAS DE COPIA DE SEGURIDAD DE SQL SERVER

Probar las copias de seguridad debe ser simple, directo y habitual. Para comenzar a probar sus copias de seguridad, saque algunas de la rotación y restáurelas con un nuevo nombre, es decir, tmp_YOURDATABASE a una instancia de Desarrollo o Prueba. Asegúrese de probar las copias de seguridad semanal o mensualmente de los recursos compartidos de archivos en el sitio y fuera del sitio. Para mejorarlo un poco, cree una lógica de restauración para restaurar las copias de seguridad diariamente en una instancia de desarrollo o prueba y emita comandos DBCC CHECKDB en ellas para validar las copias de seguridad y la integridad de la base de datos. Más información sobre el mantenimiento de SQL Server viene pronto.

No pase por alto este paso crítico y descubra que sus bases de datos no se pueden restaurar después de que ocurra un desastre. Lo mismo es válido para trabajar en el proceso de poner en marcha una instancia de SQL Server. Practique este proceso antes de un problema imprevisto. Sea proactivo y reúna sus guiones, para no andar a tientas mientras el tiempo de inactividad avanza.

COMPROBACIONES DE ESTADO DE SQL SERVER MÁS QUE UN INFORME

Un control de salud valioso es más que un informe. Es la educación y el intercambio de conocimientos. Con cada control de salud que realizamos en Edgewood Solutions, nos esforzamos por educar a nuestros clientes. La comunidad de SQL Server tiene una gran cantidad de información valiosa (consejos, trucos, publicaciones de blog, artículos, videos, publicaciones de foros y más) que debe compartirse para mejorar el conocimiento de la comunidad en su conjunto. Con recursos como estos, con suerte, todos están mejor educados, y podemos crecer colectivamente. Encuentre los problemas en su entorno y trabaje para corregirlos para mostrar su valor a la organización

3. MANTÉNGASE ALEJADO DE LAS NOTICIAS Y PROTEJA SUS SERVIDORES SQL

Parece que las brechas de seguridad están en las principales noticias con regularidad. Hay muchos grandes conjuntos de legislación destinados a proteger al público en general de tener datos confidenciales comprometidos y para prevenir otros problemas financieros. Ya sea que la legislación lo afecte o no, debe adoptar un enfoque de sentido común para garantizar que sus servidores SQL estén seguros. Aquí hay un conjunto básico de elementos a considerar:

• Permisos mínimos: usuarios y aplicaciones
• Código no susceptible de inyección SQL
• Cifrar datos y copias de seguridad
• Copias de seguridad seguras en el sitio y fuera del sitio
• Contraseñas: seguras y cambiadas con frecuencia
• Auditoría: actividad clave en los servidores
• Sin inicios de sesión compartidos
• Autenticación basada en Windows
• Ofuscar datos para entornos más bajos
• Procedimientos de escalamiento

En términos de permisos, echemos un vistazo a dos scripts esenciales para comenzar a identificar vulnerabilidades. Lo primero es revisar qué inicios de sesión tienen derechos de administrador del sistema (SA) en SQL Server. SA es una abreviatura de Administrador del sistema, y este inicio de sesión en SQL Server tiene permisos para realizar cualquier operación en la instancia. En segundo lugar, revisaremos qué inicios de sesión tienen derechos de propietario de la base de datos (DBO). DBO es una abreviatura de Propietario de la base de datos, y este usuario tiene permiso para realizar cualquier operación en la base de datos. Aunque hay más permisos en SQL Server que puede ser problemático, comencemos con una inspección de estos.

¿Quién tiene derechos de administrador del sistema (SA)?

En el siguiente script, vamos a determinar los inicios de sesión con derechos de administrador del sistema (SA) de SQL Server:

SELECCIONE SP1.[nombre] AS Inicio de sesión, SP2.[nombre] AS Permiso DE sys.server_principals SP1

ÚNASE a sys.server_role_members SRM

EN SP1.principal_id = SRM.member_principal_id ÚNASE a sys.server_principals SP2

EN SRM.role_principal_id = SP2.principal_id DONDE SP2.[nombre] = ‘sysadmin’

ORDEN POR SP1.[nombre]

Inicio de sesión de rol

1 servicio NT de administrador de sistema\MSSQL$SQL19

2 sysadnin NT SERVICIO\SQLAgent$SQL19

3 sysadnin NT SERVICIO\SQLWriter

4 sysadnin NT SERVICIO\SQLWinmgmt

5 sysadnin RÁPIDO\

6 sysadnin sa

Para obtener más detalles, visite Auditoría de permisos y roles de SQL Server para el servidor.

En el siguiente script, vamos a determinar los inicios de sesión con derechos de administrador del sistema (SA) de SQL Server:

DECLARE @LoginName sysname DECLARE @sql NVARCHAR (2000)

CREAR TABLA ##tmp_xp_logininfo (Nombre de cuenta varchar(128) NO NULO, Tipo varchar(10) NO NULO, Privilegio varchar(10) NO NULO,

MappedLoginName varchar(128) NOT NULL, PermissionPath varchar(128) NOT NULL) DECLARE cur_Loginfetch CURSOR PARA SELECCIONAR [nombre]

DESDE master.sys.server_principals DONDE TIPO = ‘G’

Y Nombre NO EN (‘SERVICIO NT\MSSQLSERVER’, ‘SERVICIO NT\SQLSERVERAGENT’)

ABIERTO cur_Loginfetch

OBTENER SIGUIENTE DESDE cur_Loginfetch EN @LoginName MIENTRAS @@FETCH_STATUS = 0

EMPEZAR

INSERTAR EN ##tmp_xp_logininfo

EXEC xp_logininfo @LoginName, ‘miembros’ FETCH NEXT FROM cur_Loginfetch INTO @LoginName FIN

CERRAR cur_Loginfetch DEALLOCATE cur_Loginfetch

SELECCIONE DISTINTO (IZQUIERDA (Nombre de cuenta, 35)) COMO Nombre de inicio de sesión, IZQUIERDA (Ruta de permiso, 35) COMO Nombre de grupo

DESDE ##tmp_xp_logininfo DONDE Privilegio = ‘admin’ ORDEN POR 1

DROP TABLE ##tmp_xp_logininfo

 Para obtener más detalles, visite Auditoría de grupos de Windows desde SQL Server.

 ¿Quién tiene los derechos de propietario de la base de datos (DBO)?

Para averiguar qué usuarios tienen derechos de propietario de la base de datos (DBO), podemos ejecutar el siguiente código en cada base de datos.

EXEC sp_helprolemember ‘db_propietario’;

¿TODAS LAS COMPROBACIONES DE ESTADO DE SQL SERVER SON SOLO UNA VALIDACIÓN DE LAS MEJORES PRÁCTICAS?

Generalmente, la mejor práctica es una recomendación que es valiosa para las masas. Lo que viene con la experiencia es comprender cómo aplicar las mejores prácticas en función de su entorno específico. En teoría, las mejores prácticas en particular deberían funcionar. Sin embargo, la realidad es que necesita evaluar y probar las mejores prácticas para validar que las mejores prácticas son apropiadas en su entorno. Una mejor práctica tradicional es nunca usar cursores. Ha sido documentado en artículos, videos y escuchado en conferencias.

Una declaración vergonzosa es una regla absoluta que incluye palabras como siempre o nunca. Sin duda, es el caso cuando se trata de cursores. ¿Recomendaría un cursor para operaciones básicas de creación, lectura, actualización y eliminación (CRUD)? No. ¿Son las opciones basadas en conjuntos la forma de codificar su lógica la mayoría de las veces? Sí, pero he estado en circunstancias en las que construí una lógica basada en cursores que es más rápida de construir y más fácil de entender que la lógica masiva basada en conjuntos que tiene errores, bloquea numerosas tablas y es difícil de solucionar.

Entonces, ¿los controles de salud son solo una validación de las mejores prácticas? No. No deberían serlo. Las mejores prácticas pueden servir como parte de la base del proceso, pero debe personalizar su verificación de estado para su entorno único.

4. HAGA BENEFICIOSO EL MANTENIMIENTO DEL SERVIDOR SQL

¿Cambias el aceite de tu coche o el filtro de agua de tu nevera? ¿Abonas tu césped? Eso es mantenimiento, y si es una prioridad en otras partes de su vida, debe ser una prioridad para su entorno de SQL Server.

• Sea inteligente con estos elementos de mantenimiento de SQL Server:
• DBCC CHECKDB
• RECONSTRUIR ÍNDICE y REORGANIZAR
• ACTUALIZAR ESTADÍSTICAS

DBCC CHECKDB

DBCC CHECKDB es responsable de validar la integridad y consistencia de una base de datos. Como se mencionó en la sección anterior, se puede lograr un medio simple de realizar DBCC CHECKDB sin afectar su entorno de producción restaurando la base de datos a una instancia de prueba o desarrollo y luego emitiendo los comandos DBCC CHECKDB. Si no tiene la oportunidad de usar una instancia de prueba o desarrollo con suficientes recursos, asegúrese de ejecutar el comando DBCC CHECKDB en todas sus bases de datos regularmente durante un período de bajo uso.

Aquí hay un script de muestra para emitir DBCC CHECKDB contra todas sus bases de datos:

ESTABLECER SIN CUENTA EN

— 1a – Declaración Sentencias para todas las variables DECLARE @DatabaseName varchar(128)

DECLARAR @CMD1 varchar(8000)

— 1b – Variables de la base de datos DECLARE @DatabaseListLoop int DECLARE @DatabaseListTable tabla

(UIDDatabaseList int IDENTIDAD (1,1), nombre de la base de datos varchar (128))

— 2a – Bucle para completar los nombres de la base de datos INSERTAR EN @DatabaseListTable(DatabaseName) SELECCIONAR Nombre

DESDE Master.sys.bases de datos

DONDE Nombre NO EN (‘tempdb’, ‘reportservertempdb’)

ORDENAR POR Nombre ASC

— 2b – Determinar la UIDDatabaseList más alta para recorrer los registros SELECT @DatabaseListLoop = MAX(UIDDatabaseList) FROM @DatabaseListTable

— 2c – Condición while para recorrer los registros de la base de datos WHILE @DatabaseListLoop > 0

EMPEZAR

— 2d – Encadene el comando DBCC final SELECCIONE @DatabaseName = DatabaseName

DESDE @DatabaseListTable

DONDE UIDDatabaseList = @DatabaseListLoop

— 2e – Cabecera

IMPRESIÓN ‘*************************’

IMPRIMIR @NombreBaseDeDatos + ‘ – ‘+ CAST(GETDATE() AS varchar(25)) IMPRIMIR ‘*************************’

— 2f – Encadena el comando DBCC final

SELECCIONE @CMD1 = ‘DBCC CHECKDB ([‘ + Nombre de la base de datos + ‘])’

DESDE @DatabaseListTable

DONDE UIDDatabaseList = @DatabaseListLoop

— 2g – Ejecutar la cadena final para completar los DBCC

— SELECCIONE @CMD1 EJECUTIVO(@CMD1)

— 2h – Pie de página

IMPRIMIR ‘***************************’ IMPRIMIR ‘’

IMPRIMIR » IMPRIMIR »

— 2i – Desciende por la lista de la base de datos

SELECCIONE @DatabaseListLoop = @DatabaseListLoop – 1 FIN

ÍNDICE RECONSTRUIR Y REORGANIZAR

Otra forma de aprender sobre el mantenimiento de la base de datos de SQL Server es mediante el uso de reconstrucciones y reorganizaciones de índices. Estos comandos son los encargados de corregir los índices fragmentados. Los dos comandos principales con los que debe familiarizarse son ALTER INDEX REORGANIZE y ALTER INDEX REBUILD. El comando ALTER INDEX REORGANIZE es una operación en línea que reorganiza los nodos hoja del índice para que coincidan el orden del índice lógico y físico. Sin embargo, no puede cambiar ninguna de las propiedades del índice. El comando ALTER INDEX REBUILD descarta y crea internamente el índice para corregir los problemas de fragmentación, pero también proporciona la capacidad de cambiar las propiedades del índice.

A continuación se muestran dos comandos de ejemplo:

ALTERAR ÍNDICE { nombre_índice | ALL } ON <objeto> REORGANIZAR ALTER INDEX { index_name | TODO } EN <objeto> RECONSTRUIR CON (

{FACTOR DE RELLENO = factor de relleno

| SORT_IN_TEMPDB = { ACTIVADO | APAGADO }

| ESTADÍSTICAS_NORECOMPUTE = { ACTIVADO | APAGADO }

| EN LÍNEA = { EN | APAGADO }

| MAXDOP = máx_grado_de_paralelismo

| COMPRESIÓN_DATOS = { NINGUNO | FILA | PÁGINA }

[ EN PARTICIONES ( { <expresión_número_partición> | <rango> } [ , …n ] ) ]

ACTUALIZAR ESTADÍSTICAS

Démosle a SQL Server la información que necesita para acceder a los datos de manera eficiente. La mejor forma de hacerlo es actualizando las estadísticas de SQL Server. Puede realizar la actualización de todas las estadísticas de una sola base de datos con un solo comando. Ejecute el siguiente comando en su base de datos deseada:

EXEC sp_updatestats;

Hay un último paso: revisar el resultado

Es necesario revisar los resultados para ver si hay errores. Asegúrese de hacer de esto una parte de su proceso de mantenimiento, de modo que un problema menor que debería haber descubierto no se convierta en un problema importante que podría haber evitado.

¡Pero lo necesito! ¿En serio?

«Necesito <completar el espacio en blanco>». Nombra la última moda en el mundo de SQL Server y he escuchado a alguien decir que lo necesita. No porque la tecnología vaya a resolver un problema tecnológico o comercial, sino porque es la última y mejor función de SQL Server. Sé que no estoy solo al escuchar este tipo de sentimientos. En una de las reuniones del grupo de usuarios de SQL Server de Baltimore, un orador de Microsoft mencionó que tenían un cliente que dijo: «Necesito BIG Data».

La realidad en muchas de estas situaciones es que alguien ha visto un artículo y ahora quiere hacer frente a todo un entorno para encontrar un problema que esta nueva tecnología resolverá. Mientras tanto, el resto del entorno está en ruinas. La lección aquí es no perder de vista encontrar la solución correcta para corregir los problemas centrales que afectan a la organización.

5. HAGA PREDECIBLE EL RENDIMIENTO

Todo el mundo tiene problemas de rendimiento, como ralentizaciones, bloqueos y bloqueos, y situaciones inexplicables. He visto muchos de los mismos tipos de situaciones con muchos clientes donde se invierte mucho tiempo y energía en corregir el código dañino, pero no el peor código ofensivo. A menudo, el código es de ejecución prolongada, pero solo se ejecuta una vez al día o a la semana. Claro, toma algunas horas, y eso no es lo ideal. Sin embargo, hay código que se ejecuta miles de veces al día y toma segundos para cada ejecución.

En conjunto, el último conjunto de código es más perjudicial que el primero. Si ese código estuviera optimizado para ejecutarse en milisegundos, los usuarios serían mucho más productivos y la instancia de SQL Server requeriría muchos menos recursos de CPU, memoria y disco.

¿Cómo identificar el código problemático?

DBCC CHECKDB es responsable de validar la integridad y consistencia de una base de datos. Como se mencionó en Hay algunas formas diferentes de identificar el código problemático. Profiler, Extended Events y las vistas de administración dinámica son probablemente las más populares. Echemos un vistazo a esta consulta de vista de administración dinámica simplificada para identificar el código que se ejecuta con mayor frecuencia con el recuento de recursos agregados más alto.

SELECCIONA LOS 20 PRINCIPALES

GETDATE() COMO «Fecha de recopilación», qs.execution_count COMO «Recuento de ejecuciones», SUBSTRING(qt.text,qs.statement_start_offset/2 +1,

(CASO CUANDO qs.statement_end_offset = -1

ENTONCES LEN(CONVERTIR(NVARCHAR(MAX), qt.texto)) * 2

ELSE qs.statement_end_offset FIN –

qs.statement_start_offset

)/2

) COMO “Texto de consulta”, DB_NAME(qt.dbid) COMO “Nombre de base de datos”, qs.total_worker_time COMO “Tiempo total de CPU”,

qs.total_worker_time/qs.execution_count COMO “Tiempo promedio de CPU (ms)”, qs.total_physical_reads COMO “Total de lecturas físicas”, qs.total_physical_reads/qs.execution_count COMO “Promedio de lecturas físicas”, qs.total_logical_reads COMO “Total de lecturas lógicas” , qs.total_logical_reads/qs.execution_count COMO “Promedio de lecturas lógicas”, qs.total_logical_writes COMO “Total de escrituras lógicas”, qs.total_logical_writes/qs.execution_count COMO “Promedio de escrituras lógicas”, qs.total_elapsed_time COMO “Duración total”, qs. total_elapsed_time/qs.execution_count COMO “Duración media (ms)”, qp.query_plan COMO “Plan”

DESDE sys.dm_exec_query_stats COMO qs

APLICACIÓN CRUZADA sys.dm_exec_sql_text(qs.sql_handle) COMO qt APLICACIÓN CRUZADA sys.dm_exec_query_plan(qs.plan_handle) COMO qp DONDE

qs.execution_count > 50 O qs.total_worker_time/qs.execution_count > 100 O qs.total_physical_reads/qs.execution_count > 1000 O qs.total_logical_reads/qs.execution_count > 1000 O qs.total_logical_writes/qs.execution_count > 1000 O qs.total/_elapsed_time qs.execution_count > 1000

ORDENAR POR

qs.execution_count DESC, qs.total_elapsed_time/qs.execution_count DESC, qs.total_worker_time/qs.execution_count DESC, qs.total_physical_reads/qs.execution_count DESC, qs.total_logical_reads/qs.execution_count DESC, qs.total_logical_writes/qs.execution_count DESC

Para obtener una explicación completa, visite Recopilación y almacenamiento de consultas de SQL Server de bajo rendimiento para análisis.

Esta consulta le permite ver el plan de ejecución real en la última columna del conjunto de resultados. Simplemente haga clic en él y el plan de ejecución se cargará en una nueva ventana de Management Studio.

¿Cómo se corrige el código problemático?

Si elige usar Profiler\Extended Events en lugar de la consulta anterior, una vez que haya identificado el código, su siguiente paso es revisar el Plan de ejecución. Así es como revisa el plan de ejecución gráfico:

1. Estudio de gestión abierto
2. Abra una nueva ventana de consulta y pegue su código
3. Para incluir el Plan de ejecución real, presione CTRL + M
4. Para ejecutar la consulta, presione F5 Aquí hay un ejemplo básico:

Mientras revisa el plan de ejecución, desea centrarse en las áreas que tienen el porcentaje de uso más alto. A partir de ahí, puede determinar si es necesario optimizar partes del código con índices nuevos o diferentes, así como ver si es necesaria una nueva técnica de codificación. La solución exacta variará, pero el plan de ejecución debería brindarle la perspectiva para comenzar.

Como nota final, es posible que incluso se sorprenda de que el optimizador de consultas le indique cómo mejorar el código. Ese es el caso de la mitad inferior de la imagen del plan de ejecución de arriba. El optimizador de consultas detecta un índice faltante con un impacto de casi el 99 por ciento. Management Studio incluso hace que el código esté disponible para copiar, modificar, probar e implementar para mejorar el rendimiento. No hay nada más simple que eso.