La performance de nuestro código TSQL es fundamental para que nuestras aplicaciones funcionen no solo de forma eficiente sino que también podamos reducir costos de HW o bien en los servicios de la nube.
Siempre la performance fue un tema a tratar en nuestras bases de datos MSSQL pero hoy día muchas empresas que migran a la nube y no tienen su base optimizada lo terminan pagando con mayores costos mensuales en los servicios que contratan.
Les dejo este webinar que di hace un tiempo en donde muestro algunos tips de performance en T-SQL que pueden ser de utilidad.
No olvides de seguirme en mi canal de Youtube o en nuestro grupo de Facebook para DBA de habla hispana
Los errores de truncamiento de datos es algo que alguna vez como programadores de SQL Server nos hemos topado. cuando hablamos de estos errores nos referimos a por ejemplo querer insertar en un VARCHAR(10) la palabra ‘TRIGGERDB CONSULTING SRL‘ lo cual como es lógico nos dará un error.
Ahora bien , este error no es muy detallado que digamos, por ende vamos a ver como podemos trabajar con lo que se denomina “Silent Truncation”
Veamos el siguiente ejemplo:
USE MASTER
GO
DROP DATABASE IF EXISTS TRIGGERDB2019
CREATE DATABASE TRIGGERDB2019
GO
USE TRIGGERDB2019
GO
DROP TABLE IF EXISTS DBO.DEMO_TRUNCATE;
CREATE TABLE DBO.DEMO_TRUNCATE
(ID INT IDENTITY PRIMARY KEY,
C1 VARCHAR (10));
INSERT INTO DBO.DEMO_TRUNCATE (C1)
VALUES ('TRIGGERDB CONSULTING SRL')
Al ejecutar nuestra operación INSERT observamos el siguiente error
Msg 8152, Level 16, State 30, Line 16 Los datos de cadena o binarios se truncarían. Se terminó la instrucción.
Msg 8152, Level 16, State 30, Line 1 String or binary data would be truncated. The statement has been terminated.
SQL Server 2019
En SQL Server 2019 usando solamente el modo de compatibilidad 150 podremos tener un mejor mensaje de error.
ALTER DATABASE TRIGGERDB2019
SET COMPATIBILITY_LEVEL = 150
INSERT INTO DBO.DEMO_TRUNCATE (C1)
VALUES ('TRIGGERDB CONSULTING SRL')
Msg 2628, Level 16, State 1, Line 16 Los datos binarios o de la cadena se truncan en la columna “C1” de la tabla “TRIGGERDB2019.dbo.DEMO_TRUNCATE”. Valor truncado: “TRIGGERDB “. Se terminó la instrucción.
Msg 2628, Level 16, State 1, Line 7 String or binary data would be truncated in table ‘TRIGGERDB2019.dbo.DEMO_TRUNCATE’, column ‘C1’. Truncated value: ‘TRIGGERDB ‘. The statement has been terminated.
SQL Server 2016 y 2017
Si se quiere usar esta funcionalidad en SQL Server 2016 o 2017 se requiere activar el Trace Flag 460 el cual esta disponible en el Sp2 de SQL Server 2016 y CU6 de 2017
Msg 2628, Level 16, State 1, Line 16 Los datos binarios o de la cadena se truncan en la columna “C1” de la tabla “TRIGGERDB2019.dbo.DEMO_TRUNCATE”. Valor truncado: “TRIGGERDB “. Se terminó la instrucción.
Msg 2628, Level 16, State 1, Line 7 String or binary data would be truncated in table ‘TRIGGERDB2019.dbo.DEMO_TRUNCATE’, column ‘C1’. Truncated value: ‘TRIGGERDB ‘. The statement has been terminated.
Auditar los eventos de nuestro SQL Server suele ser una tarea habitual para las áreas de seguridad informática. SQL Server desde su versión 2008 en su edición Enterprise dispone de todo un módulo completo para que podamos auditar distintos tipos de operaciones sobre nuestra instancia o bases de datos.
Esta funcionalidad yo la vengo implementando en varios clientes donde puedo indicar que tiene unos resultados excelentes.
La siguiente guía paso a paso la arme basada en mi experiencia en como se puede implementar esta funcionalidad.
Para acceder al código de los script lo puede hacer por medio de este link en github
SQL Server Machine Learning: Trabajando con modelos.
Autor: Maximiliano Accotto (MVP Data Platform).
www.triggerdb.com TriggerDB Consulting SRL
Trabajar con modelos es lo más habitual para las tareas de Machine Learning.
En este Lab usaremos el store procedure sp_execute_external_script el cual vimos en el LAB01 para poder crear y guardar modelos.
Paso 1: Creación de tablas
En este paso lo que haremos es crear una tabla en SQL Server en la cual luego guardaremos los modelos de R o Python, como así también una segunda tabla en la cual guardaremos datos de velocidades y distancias de frenados de autos usando el Dataset Cars de R.
Para ello usaremos el siguiente código desde el SSMS
-- CREACION DE BASE DE DATOS
DROP DATABASE IF EXISTS MLTRIGGERDB
CREATE DATABASE MLTRIGGERDB
USE MLTRIGGERDB
GO
DROP TABLE IF EXISTS DBO.TRIGGERDB_MODEL
CREATE TABLE DBO.TRIGGERDB_MODEL
(
ID INT IDENTITY PRIMARY KEY,
MODELNAME VARCHAR(255),
MODEL VARBINARY(MAX)
)
DROP TABLE IF EXISTS DBO.CARSPEED
CREATE TABLE DBO.CARSPEED ([SPEED] INT NOT NULL,
[DISTANCE] INT NOT NULL);
Paso 2: Cargar la tabla CarSpeed
En este paso cargaremos la tabla CarSpeed usando un dataset de R (Cars)
En este paso crearemos un Store Procedure el cual invocará a sp_execute_external_script pero para la creación de un modelo de regresión lineal, este SP que crearemos nos retornara como resultado un binario el cual contiene la representación de dicho modelo.
DROP PROCEDURE IF EXISTS generate_linear_model;
GO
CREATE PROCEDURE generate_linear_model
AS
BEGIN
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'lrmodel <- rxLinMod(formula = distance ~ speed, data = CarsData);
trained_model <- data.frame(payload = as.raw(serialize(lrmodel, connection=NULL)));'
, @input_data_1 = N'SELECT [speed], [distance] FROM CarSpeed'
, @input_data_1_name = N'CarsData'
, @output_data_1_name = N'trained_model'
WITH RESULT SETS ((model varbinary(max)));
END;
GO
Paso 4: Guardar el modelo en una tabla
En este paso guardaremos el modelo en la tabla que hemos creado en el paso 1
INSERT INTO DBO.TRIGGERDB_MODEL (model)
EXECUTE generate_linear_model
UPDATE DBO.TRIGGERDB_MODEL SET MODELNAME = 'ModeloCars'
WHERE ID = SCOPE_IDENTITY()
SELECT * FROM DBO.TRIGGERDB_MODEL
Paso 5: Usar el modelo para predicciones
En este paso usaremos al modelo creado con R y guardado en una tabla SQL Server para poder hacer predicciones, para ello crearemos una nueva tabla con velocidades para que nos determine la distancia de frenado en base al modelo.
DROP TABLE IF EXISTS [dbo].[NewSpeed]
CREATE TABLE [dbo].[NewSpeed] (
[speed] [int] NOT NULL,
[distance] [int] NULL) ON [PRIMARY]
GO
INSERT [dbo].[NewSpeed] (speed)
VALUES (40), (50), (60), (70), (80), (90), (100),(110),(133),(200)
-- predecimos con el modelo guardado anteriormente en la tabla
DECLARE @speedmodel varbinary(max) =
(SELECT model FROM [dbo].[TRIGGERDB_MODEL] WHERE MODELNAME = 'ModeloCars');
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
current_model <- unserialize(as.raw(speedmodel));
new <- data.frame(NewData);
predicted.distance <- rxPredict(current_model, new);
str(predicted.distance);
OutputDataSet <- cbind(new, ceiling(predicted.distance));
'
, @input_data_1 = N' SELECT speed FROM [dbo].[NewSpeed] '
, @input_data_1_name = N'NewData'
, @params = N'@speedmodel varbinary(max)'
, @speedmodel = @speedmodel
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))
Creando un SP para predicciones
Creamos un Store Procedure el cual recibe como parámetro la velocidad y en base al modelo nos predice la distancia. Para esto usamos la posibilidad de pasarle parametros a sp_execute_external_script
-- CREAMOS UN SP QUE RECIBE UNA VELOCIDAD
DROP PROCEDURE IF EXISTS DBO.USP_PREDICT_VELOCIDAD;
CREATE PROCEDURE DBO.USP_PREDICT_VELOCIDAD
@VELOCIDAD INT
AS
DECLARE @speedmodel varbinary(max) =
(SELECT model FROM [dbo].[TRIGGERDB_MODEL] WHERE MODELNAME = 'ModeloCars');
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
current_model <- unserialize(as.raw(speedmodel));
new <- data.frame(NewData);
predicted.distance <- rxPredict(current_model, new);
str(predicted.distance);
OutputDataSet <- cbind(new, ceiling(predicted.distance));
'
, @input_data_1 = N'select @vSpeed as speed '
, @input_data_1_name = N'NewData'
, @params = N'@speedmodel varbinary(max),@vSpeed int'
, @speedmodel = @speedmodel
,@vSpeed = @VELOCIDAD
WITH RESULT SETS (([new_speed] INT, [predicted_distance] INT))
go
-- PRUEBAS DE PREDICCION
EXECUTE DBO.USP_PREDICT_VELOCIDAD 300
EXECUTE DBO.USP_PREDICT_VELOCIDAD 77
Maximiliano Accotto
Especialista y orador en Plataformas de datos Microsoft con más de 20 años de trayectoria