Monitoreando el Performance en SQL Server

SQL Server Profiler

·         Muestra como SQL Server resuelve las queries internamente

·         Permite a los administradores ver como se ven las sentencias T-SQL  y como el servidor regresa los resultados.

·         Se puede:

o   Crear una traza basado en Templates

o   Verificar los resultados de la traza

o   Almacenar los resultados de la traza en un archivo o tabla

·         Capturar datos enviados al servidor que permite al programador verificar errores o datos incorrectos.

Windows System Monitor

·         Monitorear el uso de recursos.

·         System Monitor también llamado Performance Monitor.

·         Comparando SQL Server Profiler,  este monitorea eventos del motor de base de datos,  System Monitor monitorea el uso de recursos asociados con los procesos del servidor.

·         Se encuentra en el Panel de Control , Herramientas Administrativas, Monitor de rendimiento (en Español)   o bien Performance Monitor.

Activity Monitor

       Secciones:

1.       Vista General -  Muestra gráficamente el comportamiento del resto de secciones.

2.       Procesos – Muestra las actividades de los usuarios.  Que usuario esta impactando en que base.

3.       Recursos en Espera -  Espera el estado de la información. Que proceso está esperando un recurso.

4.       Data File  I/O -  Archivos de Data y Log  con información I/O

5.       Queries utilizadas -  Muestra las queries mas utilizadas recientemente.

Transact-SQL

·         sp_who 

·         sp_lock

·         sp_spaceused

·         sp_monitor

Windows Logs

·         Aplicación Externa a SQL Server

·         Podremos ver advertencias o errores ocurridos en los procesos de SQL Server.

·         Se encuentra en el Panel de Control , Herramientas Administrativas, Visor de Eventos(en Español)   o bien Events Log.

Default Trace

·         SQL Server “Caja Negra”

·         Para habilitarlo:

sp_configure 'default trace enabled', 1

RECONFIGURE

·         El trace que se creara por defecto será en la ruta donde esté instalado SQL Server. 

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

·         Buscamos el archivo de traza  con extension .trc generado.

Trabajando con SQL Server Agent

¿Qué es el SQL Server Agent?

Es un servicio de Windows, ejecuta tareas administrativas programadas en un calendario.   Se ejecuta ante un calendario de tareas,  eventos o solicitud del usuario.

Pasos para utilizarlo

1.       Identificar tareas administrativas

2.       Definir Jobs, Schedules, Alerts y Operators

3.       Ejecutar Jobs que esten definidos

Configuracion

En SQL Server:   la cuenta de inicio debe ser parte del rol sysadmin

En el SQL Server Configuration Manager: Iniciarlo.

Trabajando con SQL Server

1.       Primero vamos a crear un nuevo operador al cual podremos notificar en el caso que falle una tarea o se haya realizado una tarea satisfactoriamente.

2.       Luego crearemos un nuevo Job.

3.       Agregaremos una nueva Task  y pondremos la siguiente sentencia T-SQL para realizar el respaldo de base de datos con un archivo dinamico.

DECLARE @DBArchivo NVARCHAR(50);

SET @DBArchivo = N'C:\SQLSERVER\Demo_' + CONVERT(varchar, getdate(), 112) + '.bak'

BACKUP DATABASE DemoUPOLI TO DISK = @DBArchivo  

4.       En Schedules programaremos fecha y hora de nuestra tarea,  será una tarea recurrente de todos los días a la 1:00 AM.

5.       Las alertas nos brindan la capacidad de enviar notificaciones o realizar acciones basada en ciertos eventos o condiciones propias de SQL Server o incluso de la máquina donde está corriendo la instancia de SQL Server.

6.       En Notificaciones,  le enviaremos por correo cuando haya finalizado correctamente o bien cuando haya fallado nuestro respaldo a nuestro operador.

7.       Finalmente creamos el Job.

8.       Verificamos el Job Activity Monitor.

Usando Snapshots en SQL Server

Esta característica fue introducida en SQL Server 2005, y su principal función es permitir a los usuarios crear copias de solo lectura de una DB. Es una base de datos sólo lectura, estática basada en una base datos normal.

¿Para qué sirve?

Pueden ser utilizadas para crear reportes.

En el caso de un error del usuario en una base de producción, puede restaurar al estado en que estaba cuando se creó el snapshot.

Además, la creación de una snapshot de base de datos puede ser útil inmediatamente antes de hacer un cambio importante de una base de datos, como cambiar el esquema o la estructura de una tabla.

Importante:
Database snapshots, sólo están disponibles en las ediciones Enterprise de SQL Server 2005, SQL Server 2008 y SQL Server 2008 R2.

¿Cómo crear una Snapshots?

La única manera de crear una SNAPSHOT es utilizar Transact-SQL.

create database Demo_SS032011

on ( Name = Demo,

    Filename = N'c:\SqlServer\demo_snapshot.ss')

as snapshot of Demo;

Para restaurarla en una base normal seria de esta manera:

RESTORE DATABASE Demo FROM DATABASE_SNAPSHOT = 'Demo_SS';

Articulo Recomendado:

http://www.sqlcoffee.com/Tips0004.htm

Utilizacion de XML en SQL Server

Utilizando el Tipo de Datos

-- Crear una Tabla con una columna XML

CREATE TABLE XMLEjemplo (

  Col1 int NOT NULL PRIMARY KEY IDENTITY(1,1),

  Col2 varchar(20) NULL,

  Col3 xml NULL)

GO

-- Crear una tabla con una Columan xml y un valor por defecto

-- usando un CAST a XML

CREATE TABLE tblXMLExample2 (

  Col1 int NOT NULL PRIMARY KEY IDENTITY(1,1),

  Col2 varchar(20) NULL,

  Col3 xml NULL DEFAULT CAST('' AS xml))

GO

-- Declarar una variable usando el tipo de datos XML

DECLARE @doc xml

SELECT @doc = ''

GO

Asignacion Directa

DECLARE @empXML xml

SET @empXML = CAST('1234567890Perez, Juan'

    + 'Gerente de VentasVentas' AS xml)

SELECT @empXML AS DatosEmpleados

Ejecutar consulta y verificar estructura con el editor XML de SQL Server Management Studio.

Usando FOR XML

DECLARE @empXML xml

SET @empXML = (SELECT TOP 3 IdCliente,

                   Nombre,

                   Pais, Departamento

               FROM Clientes 

               FOR XML AUTO, ELEMENTS, ROOT ('Clientes'))

SELECT @empXML AS DatosClientes

Crear Un Esquema

SELECT TOP 2 IdProducto, Descripcion, Precio

FROM Productos

FOR XML AUTO, ELEMENTS, XMLSCHEMA, ROOT ('Productos')

Ejecutar consulta y verificar estructura con el editor XML de SQL Server Management Studio.

Crear un esquema a partir del esquema generado anteriormente.

CREATE XML SCHEMA COLLECTION ProductosSchema AS N''

Verificar el esquema creado:

SELECT * FROM sys.xml_schema_collections WHERE name = 'ProductosSchema'

Crear una Nueva Tabla Productos XML y asginarle el Esquema

CREATE TABLE ProductosXML (

  ProdSchemaID int NOT NULL PRIMARY KEY IDENTITY(1,1),

  XMLEmpInfo xml (ProductosSchema))

Probar Ingresar un Documento XML que no cumpla con los requisitos del Esquema, Por Ejemplo:  IdProducto de tipo varchar.

Cargando Datos usando OPENROWSET

Cargar un archivo XML usando OPENROWSET

DECLARE @prodXML xml

SET @prodXML = (SELECT *

                FROM OPENROWSET(

                    BULK 'C:\SQLServer\Bicycles.xml',  SINGLE_BLOB) AS xmlDatosProductos)

SELECT @prodXML AS DatosProductos

Insertando datos desde un archivo

1.       Crear una Tabla con una columna código de Documento,  y una columna XML para almacenar un archivo.

CREATE TABLE XMLBicicletas (

  Codigo int NOT NULL PRIMARY KEY IDENTITY(1,1),

  BiciXML xml NULL)

GO

2.       Insertar los datos en la tabla anterior desde un archivo XML

INSERT INTO XMLBicicletas(BiciXML)

SELECT *

FROM OPENROWSET(

    BULK 'C:\SQLServer\Bicycles.xml', SINGLE_BLOB) AS xmlProdData

GO

3.       Estructura del Archivo Almacenado

SELECT * FROM XMLBicicletas

<Bicycles><Product>

    <ProductID>791ProductID>

    <Name>Road-250 Red, 52Name>

    <ProductNumber>BK-R89R-52ProductNumber>

    <MakeFlag>1MakeFlag>

    <FinishedGoodsFlag>1FinishedGoodsFlag>

    <Color>RedColor>

    <SafetyStockLevel>100SafetyStockLevel>

    <ReorderPoint>75ReorderPoint>

    <StandardCost>1518.7864StandardCost>

    <ListPrice>2443.3500ListPrice>

    <Size>52Size>

    <SizeUnitMeasureCode>CM SizeUnitMeasureCode>

    <WeightUnitMeasureCode>LB WeightUnitMeasureCode>

    <Weight>15.42Weight>

    <DaysToManufacture>4DaysToManufacture>

    <ProductLine>R ProductLine>

    <Class>H Class>

    <Style>U Style>

    <ProductSubcategoryID>2ProductSubcategoryID>

    <ProductModelID>26ProductModelID>

    <SellStartDate>2002-07-01T00:00:00SellStartDate>

    <SellEndDate>2003-06-30T00:00:00SellEndDate>

    <rowguid>C9FD1DF4-9512-420A-B379-067108033B75rowguid>

    <ModifiedDate>2004-03-11T10:01:36.827ModifiedDate>

  Product> Bicycles>

Usando XQuery para retornar datos

1.       Regresar todos los nombres de los Productos.

SELECT  BiciXML.query('/Bicycles/Product/Name') AS NombreProducto

FROM XMLBicicletas

2.       Retornar el n valor de una estructura XML  XPATH

SELECT BiciXML.value('(/Bicycles/Product/ProductNumber)[5]', 'varchar(20)') AS ModeloBicicleta

FROM XMLBicicletas

3.       Retornar la cantidad de teléfonos por un Contacto

DECLARE @XML xml

SET @XML = (SELECT DocXML

            FROM XMLContactos)

SELECT @XML.query(

  '

    {

      for $contacto in /Contacts/Contact

      let $contador :=count($contacto/PhoneNumbers/PhoneNumber)

      order by $contador

      return

     

          {$contacto/Name}

          {$contador}

     

    }

  ')

4.       Retornar un Contacto Especifico

SELECT @XML.query(

  '

    {

      for $contacto in /Contacts/Contact

      let $contador :=$contacto/PhoneNumbers/PhoneNumber

      where $contacto = "Anna Palmer"

      return

     

          {$contacto/Name}

          {$contador}

     

    }

  ')