Webinar: Tips performance TSQL

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

Mejorar los errores Data Truncation en SQL Server

Mejorar los errores Data Truncation en SQL Server

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  

TF 460 en el scope
DBCC TRACEON (460)

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.

Guia de auditoria SQL Server

Guia de auditoria SQL Server

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 Machine Learning: Modelos

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)

INSERT INTO CARSPEED
EXEC SP_EXECUTE_EXTERNAL_SCRIPT
        @LANGUAGE = N'R'
        , @SCRIPT = N'CAR_SPEED <- cars;'
        , @INPUT_DATA_1 = N''
        , @OUTPUT_DATA_1_NAME = N'CAR_SPEED'
SELECT * FROM CARSPEED

Paso 3: Crear un modelo de regresión lineal

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