Generar y Exportar Datos de Rendimiento de SQL Server en Archivos

Visual Expert monitoriza el rendimiento de su código Transact-SQL y produce análisis sobre su ejecución. Esto ayuda a los desarrolladores de T-SQL a ajustar el rendimiento de su código.

Esta funcionalidad se basa en los datos de ejecución del código que su base de datos generará, y que usted cargará en su proyecto Visual Expert. Visual Expert combinará entonces estos datos con la información extraída durante el análisis estático de su código.

La generación de estos datos de ejecución del código depende de su configuración:

Si Visual Expert puede conectarse a su servidor SQL, NO NECESITA seguir este procedimiento. Sólo siga este artículo.
Si Visual Expert NO PUEDE conectarse al servidor SQL - por ejemplo, con una base de datos de producción alojada en un entorno separado - siga el procedimiento que se indica a continuación para generar un archivo que contenga los datos necesarios, e inclúyalo en su proyecto Visual Expert.

Conéctese a la base de datos del servidor SQL donde desea crear y ejecutar el procedimiento almacenado writePerformanceDataToFile (un procedimiento que lee los datos de rendimiento de los archivos de sesión de eventos y escribe en un archivo "*.VETSQLPERF")
Crear PROCEDIMIENTO - WritePerformanceDataToFile (ejecutar el script PROCEDIMIENTO de VEPerfUtils_TSQL.SQL)
Abra la línea de comandos y pegue la orden siguiente:

bcp "exec [{DataBaseName}].[{schema}].[WritePerformanceDataToFile] @databaseName = 
N'{DatabaseName1,DatabaseName2,...}', @recordsLimit = {NULL|any number}" queryout 
"{output file full path}.VETSQLPERF" -w {-T|-U "{username}" -P "{password}"} -S 
"{server\instance}"

Esto ejecutará el procedimiento almacenado - WritePerformanceDataToFile y escribirá el resultado en un archivo.

Ejemplos de órdenes:

Para escribir todos los registros de las Bases de Datos Múltiples usando una conexión de confianza

bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -T

Para escribir todos los registros de la Base de Datos Única usando una conexión de confianza

bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T

Para escribir un máximo de 10000 registros para la Base de Datos Única usando una conexión de confianza

 bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = 10000" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T

Para escribir todos los registros para la Base de Datos Única usando un ID de acceso "SA" usado para conectarse al SQL Server

bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -U "
TEST" -P "test user password" -S "server\SQL-Server-instance"

Para escribir todos los registros de las Bases de Datos Múltiples usando un ID de inicio de sesión "TEST" usado para conectarse al SQL Server

bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -U 
"TEST" -P "test user password" -S "server\ SQL-Server-instance"

Nota sobre los parámetros:

{DataBaseName}: Escriba el nombre de la base de datos que contiene el procedimiento WritePerformanceDataToFile.
{schema}: Nombre del Esquema que contiene el procedimiento WritePerformanceDataToFile.
@databaseName: Lista de nombres de bases de datos, separados por comas, en los que deben leerse los datos de rendimiento.

Por ejemplo:

@databaseName = N'{DatabaseName1,DatabaseName2}' -- multiple databases

@databaseName = N'{DatabaseName1}' -- single database

@recordsLimit: Número total de registros que se leerán y escribirán en el archivo de resultado (NULL = todos los registros, cualquier número = número total de registros)

Por ejemplo:

@recordsLimit = NULL -- all records will be read and written to output file

@recordsLimit = 10000 -- maximum 10000 records will be read and written to output file

queryout: El archivo de resultados con la ruta completa con la extensión .VETSQLPERF
(Tenga en cuenta que VE puede reconocer este archivo para su análisis sólo si la extensión es .VETSQLPERF)

Nota:Asegúrese que el directorio existe en la ruta del archivo, de lo contrario bcp mandara un error.

Por ejemplo:

queryout "D:\VEPerformanceFiles\alldatabase.VETSQLPERF"

queryout "D:\VEPerformanceFiles\SalesDB.VETSQLPERF"

-U {username}: nombre de usuario para conectarse al servidor SQL para ejecutar el procedimiento de almacenamiento WritePerformanceDataToFile.

Por ejemplo:

For "sa" USER -U "sa"

-P {password}: Contraseña para el nombre de usuario especificado en el paso 5 anterior

Nota: Para conectar al SQL Server con una conexión de confianza usando seguridad integrada, el usuario puede escribir -T en lugar de -U y -P

-S "{server\instance}": Especificar máquina/servidor e instancia de servidor sql

Ejemplos:

Para la máquina local SQL Express

-S "(local)\SQLExpress"

Para un servidor con nombre (por ejemplo, DB-SERVER1) y una instancia de sql server con nombre (por ejemplo, SQLSERVER2019)

-S "DB-SERVER1\SQLSERVER2019"

Para más detalles sobre el uso de bcp, por favor consulte: https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15

bcp "exec [TestDatabase].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB1', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" 
-w -U "SA" -P "testpassword" -S "(local)"

Permisos necesarios para ejecutar un procedimiento almacenado utilizando la bcp:

GRANT VIEW SERVER STATE to {user} 
USE master
GO
GRANT VIEW SERVER STATE TO test;

GRANT EXECUTE ON {database}.{schema}.[WritePerformanceDataToFile] to {user};
Use {database}
Go
GRANT EXECUTE to [test];

Captura de pantalla de la ejecución exitosa:

Permisos necesarios para ejecutar un procedimiento almacenado utilizando la utilidad bcp

Una vez que el archivo de rendimiento esté listo, puede ser analizado por Visual Expert. Sigue los pasos siguientes:

Abra su proyecto de SQL Server en Visual Expert.
En el menú de la cinta, vaya a Configuración > Administración del código fuente.
Haga clic en "Añadir" para agregar su archivo de datos de rendimiento (*.VETSQLPERF) como una nueva ubicación de código fuente.
En el Asistente de Proyectos de Visual Expert, seleccione "carpetas y/o archivos" > localice el directorio 'PERFDATADIR' > *Archivo VETSQLPERF para agregarlo como una nueva ubicación de código fuente. Haga clic en "Terminar".
Se añade una nueva entrada en la lista para el archivo *.VETSQLPERF.
Al cerrar este diálogo, Visual Expert notifica que debe actualizar el análisis del código. Haga clic en "Sí".
Una vez completado el proceso de análisis, un nuevo análisis se carga automáticamente.
Visite este artículo para aprovechar las últimas características disponibles para analizar las actuaciones:
MEJORAR EL RENDIMIENTO DEL CÓDIGO.

==================== Fuente del archivo VEPerfUtils_TSQL.SQL =========================

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'WritePerformanceDataToFile')  
	DROP PROCEDURE [dbo].[WritePerformanceDataToFile]  
GO  


CREATE PROCEDURE [dbo].[WritePerformanceDataToFile] (@databaseName nvarchar(Max), @recordsLimit int = NULL) 
AS
BEGIN

Declare @columdelimiter nvarchar(10);
Declare @rowdelimiter nvarchar(10);

Declare @eventFileFullPath nvarchar(Max) = NULL;
Declare @eventName nvarchar(Max);
Declare @index int;

DECLARE @recordLimitsString varchar(30);  
DECLARE @SQLString nvarchar(2500);  
DECLARE @ParmDefinition nvarchar(200); 
Declare @sliptDelimiter VARCHAR(10) = ',';
Declare @dbName nvarchar(Max);
Declare @position int;

SET @databaseName = CONCAT(@databaseName , @sliptDelimiter);

SET @ParmDefinition = N'@recordLimit int, @colum_delimiter nvarchar(10), @row_delimiter nvarchar(10), @eventFilePath nvarchar(260)';  

SET @columdelimiter = CONCAT( N'«',N'¬',N'»');
SET @rowdelimiter = CONCAT(N'«',N'®',N'»');
SET @eventName = Concat(N'VE_SQLPerformanceSession', '_',  DB_ID(@databaseName));

IF @recordsLimit IS NOT NULL
	SET @recordLimitsString = 'TOP ' + CAST(@recordsLimit AS VARCHAR(15)) + ' ';
ELSE
	SET @recordLimitsString = ' ';

SET @SQLString = 'SELECT ' + @recordLimitsString + ' Concat(ALLRECORDS.duration, @colum_delimiter, ALLRECORDS.sqlFullText, @row_delimiter) as record FROM ' + 
	'(SELECT ' +
		'qs.total_elapsed_time/qs.execution_count AS duration, ' +    
		'SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ' +
			'((CASE qs.statement_end_offset ' +
				'WHEN -1 THEN DATALENGTH(st.text)  ' + 
				'ELSE qs.statement_end_offset  ' + 
				'END - qs.statement_start_offset)/2) + 1) AS sqlFullText ' +
	'FROM sys.dm_exec_query_stats AS qs  ' + 
	'CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  ' + 
	'UNION ALL ' +
	'SELECT ' +	
		'qs.total_elapsed_time/qs.execution_count AS duration, ' +  	                                               
		'st.text AS sqlFullText ' +
	'FROM sys.dm_exec_procedure_stats AS qs ' +  
	'CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ' +  
	'UNION ALL ' +
	'SELECT ' +	
		'ts.total_elapsed_time/ts.execution_count AS duration, ' +  	                                             
		'st.text AS sqlFullText ' +
	'FROM sys.dm_exec_trigger_stats AS ts ' +  
	'CROSS APPLY sys.dm_exec_sql_text(ts.sql_handle) AS st ';

SET @position = CHARINDEX(@sliptDelimiter, @databaseName, 0);

WHILE @position <> 0
BEGIN
	SELECT
	@dbName = RTRIM(LTRIM(SUBSTRING(@databaseName, 1, @position-1))),
	@databaseName = RTRIM(LTRIM(SUBSTRING(@databaseName, @position + LEN(@sliptDelimiter), LEN(@databaseName))))
 
	SET @dbName = RTRIM(LTRIM(@dbName));

	IF LEN(@dbName) > 0
	BEGIN

		IF(Substring(@dbName, 1, 1) = '[' and Substring(@dbName, LEN(@dbName) , LEN(@dbName)) = ']')
		 SET @dbName = Substring(@dbName, 2, LEN(@dbName) - 2)

		SET @eventName = Concat(N'VE_SQLPerformanceSession', '_',  DB_ID(@dbName));


		SET @eventFileFullPath = (SELECT TOP 1 cast(f.value as NVARCHAR (Max))
		FROM sys.server_event_sessions AS s
		    JOIN  sys.server_event_session_targets  AS t ON  t.event_session_id = s.event_session_id
			JOIN  sys.server_event_session_fields   As f ON  f.event_session_id = s.event_session_id AND f.object_id = t.target_id
		WHERE s.name = @eventName);


		SET @index = CHARINDEX(@eventName, @eventFileFullPath);

		IF @index > -1  
		Begin
			SET @eventFileFullPath = SUBSTRING(@eventFileFullPath, 0, @index);
			SET @eventFileFullPath = Concat(@eventFileFullPath, @eventName);
		END 

		IF @eventFileFullPath IS NOT NULL 
		BEGIN
		SET @SQLString =  @SQLString + 
			'UNION ALL ' +
			'SELECT ' +	
				'CAST(event_Data AS xml).value(''(event/data[@name="duration"]/value)[1]'',''bigint'') AS duration, ' +
				'CASE ' +
					'WHEN CAST(event_Data AS xml).value(''(event/@name)[1]'', ''varchar(250)'') = ''module_end'' THEN CAST(event_Data AS xml).value(''(event/data[@name="object_name"]/value)[1]'',''nvarchar(250)'')  ' +
					'Else CAST(event_Data AS xml).value(''(event/data[@name="statement"]/value)[1]'',''nvarchar(MAX)'') ' + 
					'END AS sqlFullText ' +
				'FROM sys.fn_xe_file_target_read_file(CONCAT(@eventFilePath ,''*.xel''), CONCAT(@eventFilePath ,''*.xem''), NULL, NULL) ';
		END 

	END


	SET	@position = CHARINDEX(@sliptDelimiter, @databaseName, 0);
END

SET @SQLString =  @SQLString + ') AS ALLRECORDS';


EXECUTE sp_executesql @SQLString, @ParmDefinition, @recordLimit = @recordsLimit, @colum_delimiter = @columdelimiter, @row_delimiter = @rowdelimiter, @eventFilePath = @eventFileFullPath;

END

Generar y Exportar Datos de Rendimiento de SQL Server en Archivos

Ejemplos de órdenes:

Nota sobre los parámetros:

Permisos necesarios para ejecutar un procedimiento almacenado utilizando la bcp:

Recent posts

Videos

Novalys Websites

Find Us