Blog SQL Server de Maximiliano Accotto

Consultoría en SQL Server , servicios de DBA en CONDUIT Soluciones

SQL Server 2014 fin al modo de compatibilidad 90 (SQL 2005)

Bueno, en SQL Server 2014 ya esta deprecado el modo de compatibilidad 90. Si bien el mismo aparece como opción en una base de datos no se puede aplicar.

Si corremos el siguiente script veremos un error

USE [master]

GO

ALTER DATABASE [DMO1] SET COMPATIBILITY_LEVEL = 90

GO

 

Msg 15048, Level 16, State 3, Line 3
Valid values of the database compatibility level are 100, 110, or 120.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.

Si se hace un RESTORE o ATTACH DB el modo pasara a 100 (el mínimo soportado).

 

Lo que si estaría bueno y como en versiones anteriores es que desde el SSMS (SQL Server Management Studio) no aparezca ni la opción así no se presta a confusiones como en la imagen siguiente

 

Capture

Como comprimir todos los índices de una tabla

 

El siguiente script nos comprimirá (en este caso en modo page) todos los índices de una tabla.

USE MIDB

GO

 

ALTER

INDEX ALL ON

[TUTABLA]

REBUILD WITH (DATA_COMPRESSION=PAGE);

Ebook gratuito de SQL 2014

0844_9780735684751x_thumb_044C6AED

Un imperdible ebook (ingles) para todos aquellos que quieran aprender sobre SQL 2014.

Para descargarlos aquí dejo los links.

Formato PDF

Formato EPUB

Formato MOBI for Kindle

Libros gratis sobre tecnología Microsoft

Quieres libros gratis (ebooks) sobre tecnología Microsoft ? no podes dejar de ver este link en el mismo encontraras ebooks sobre varios productos, entre ellos

  • Lync
  • Office
  • Sharepoint
  • SQL Server
  • System Center
  • Visual Studio
  • Windows
  • Windows Azure
  • Windows Phone
  • Windows Server

Microsoft® OData Source for Microsoft SQL Server® 2012

Interesante conector para poder leer datos con ODATA desde SSIS (SQL Server Integration Services)

Hay varios productos que usan este protocolo de comunicación, entre ellos podemos mencionar

  • SharePoint Foundation 2010
  • SharePoint Server 2010
  • SQL Azure
  • Windows Azure Table Storage
  • Windows Azure Marketplace
  • SQL Server Reporting Services
  • Microsoft Dynamics CRM 2011
  • Windows Live

Como detectar problemas de Deadlock (interbloqueos) usando el SQL Server Profiler

Microsoft SQL Server Data Tools para SQL 2014 liberado

Se acaba de liberar esta nueva versión de las famosas SSDT pero para SQL 2014 la cual pueden integrar si desean con VS2013, VS2012 o VS2010.

Entre sus mejoras podemos mencionar

  • Static code analysis extensibility
  • Filter capabilities in the editable data grid
  • Saving data compare settings to file (.dcmp)
  • Additional TSQL Editor connection actions

Como reparar el error: Saving changes is not permitted en el Management Studio

SQL SERVER 2014 RTM ya tiene fecha de liberación

Se anuncio que SQL Server 2014 RTM (versión liberada para clientes) estará disponible a partir del 1 de abril de 2014

Para mayor información les sugiero este link

http://blogs.technet.com/b/microsoft_blog/archive/2014/03/18/sql-server-2014-released-to-manufacturers-will-be-generally-available-april-1.aspx

Se vendrán en mi blog próximamente una serie de artículos técnicos sobre SQL 2014 Sonrisa

Kit de desarrollo gratuito de SQL Server 2014

Como ya nos tienen acostumbrados en Microsoft, se libero el kit de desarrollo para SQL 2014. Un recurso gratuito que mucho contenido de presentaciones, script, demos, etc.

Les dejo el link de descarga

SQL Server Data Compression para mejorar la performance

Desde la versión 2008 Enterprise de SQL Server se pueden comprimir tanto datos como Backups. La compresión de datos (Data Compression) es una técnica que no fue en principio pensada para ahorrar espacio sino que para mejorar performance. Esto básicamente se debe a que hoy día los servidores de bases de datos tienen mas potencia en CPU pero el sistema de disco sigue siendo lento, con lo cual si yo pudiera consumir menos recursos de disco las cosas serian mas eficientes a costa de un mayor consumo de CPU.

Dicho en otros términos no es lo mismo leer un índice clustered de 1GB que uno de 100Mb.

Por defecto las tablas no están comprimidas ya que antes de hacerlo uno debería hacer un análisis de la instalación, revisar si la compresión tiene alguna ganancia y luego aplicarla, en otras palabras es una tarea del Dba’s en su role de tuning.

En este post veremos un ejemplo de como se mejora la performance en una tabla utilizando esta técnica de Data Compression.

Paso 1: Construimos 3 tablas iguales a partir de la tabla SalesOrderDetail de nuestra base de datos Adventureworks

SELECT * INTO Nocomprimida

FROM SALES.SalesOrderDetail

 

SELECT * INTO ComprimidaRow

FROM SALES.SalesOrderDetail

 

SELECT * INTO ComprimidaPage

FROM SALES.SalesOrderDetail

 

Paso 2: Creamos para cada una de ellas su índices Clustered, en la primera sin compresión, en la segunda usando compresión de Row y en la ultima usando compresión de Page.

 

CREATE CLUSTERED INDEX INoCompression ON dbo.Nocomprimida

(SalesOrderID, SalesOrderDetailID);

 

CREATE CLUSTERED INDEX ICompressionRow ON dbo.ComprimidaRow

(SalesOrderID, SalesOrderDetailID)

WITH (DATA_COMPRESSION = ROW);

 

CREATE CLUSTERED INDEX ICompressionPage ON dbo.ComprimidaPage

(SalesOrderID, SalesOrderDetailID)

WITH (DATA_COMPRESSION = PAGE);

 

Paso 3: Analizamos los espacios consumidos por cada tabla

compress1_thumb2 

 

Como podemos observar las tablas comprimidas tienen un ahorro de espacio significativo.

Paso 4: Probaremos el comportamiento a nivel performance de distintas consultas, haremos SELECT UPDATE y DELETE.

Probamos un SELECT

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.Nocomprimida

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaRow

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaPage

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

Ahora observaremos los planes de ejecución de estas 3 consultas.

 

compress2_thumb1

 

Lo que se observa es que para el mismo tipo de operación (Clustered Index Seek) los costos van disminuyendo al usar compresión. Básicamente esto se da ya que el costo de lectura en tablas comprimidas es mas bajo.

Ahora observemos las estadísticas de disco para las 3 consultas.

 

SET STATISTICS IO ON

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.Nocomprimida

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaRow

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SELECT SalesOrderID, SalesOrderDetailID, ProductID

FROM dbo.ComprimidaPage

WHERE SalesOrderID BETWEEN 50000 AND 52000

 

SET STATISTICS IO OFF

 

Table 'Nocomprimida'. Scan count 1, logical reads 174, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaRow'. Scan count 1, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaPage'. Scan count 1, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Se observa como se han disminuido las lecturas lógicas al usar la compresión.

Probamos un DELETE

Nuestra siguiente prueba es hacer un borrado masivo de las tres tablas y comparar sus planes de ejecución y estadísticas de disco como hicimos con el SELECT.

SET STATISTICS IO ON

 

DELETE FROM Nocomprimida

 

DELETE FROM ComprimidaRow

 

DELETE FROM ComprimidaPage

 

SET STATISTICS IO OFF

 

COMPRESS3_thumb1

 

Table 'Nocomprimida'. Scan count 1, logical reads 15750, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaRow'. Scan count 1, logical reads 9232, physical reads 0, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaPage'. Scan count 1, logical reads 5900, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Probamos el UPDATE

Haremos la misma prueba pero ahora actualizando todos los registros de las tablas.

SET STATISTICS IO ON

 

UPDATE Nocomprimida

SET UnitPrice = UnitPrice * 2

 

UPDATE ComprimidaRow

SET UnitPrice = UnitPrice * 2

 

UPDATE ComprimidaPage

SET UnitPrice = UnitPrice * 2

 

SET STATISTICS IO OFF

 

 

COMPRESS4_thumb3

Table 'Nocomprimida'. Scan count 1, logical reads 1513, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaRow'. Scan count 1, logical reads 2099, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ComprimidaPage'. Scan count 1, logical reads 1301, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusiones:

El Data Compression es una funcionalidad muy interesante, si bien no en todos los casos responde como lo vimos en este post es muy bueno evaluar las tablas mas criticas si con la compresión podemos o no mejorar performance.

Mi experiencia con esta funcionalidad es muy positiva, pero siempre recalco que lo mejor es hacer las pruebas correspondientes para determinar si es conveniente usarlo para alguna tabla, en muchos casos no se logra una buena compresión y los resultados son casi iguales, cuando hablamos de resultados estoy hablando del costo de las distintas operaciones.

Aquí les dejo algunos links adicionales y el script de toda esta demo.

SQL Server Compression Estimator

http://ssce.codeplex.com/

Data Compression: Strategy, Capacity Planning and Best Practices

http://msdn.microsoft.com/en-us/library/dd894051.aspx

Curso SQL Server performance tuning para DBA

El día 25 de febrero de 2014 estaré dando un curso de 8hs en el club de usuarios Microsoft. El mismo tiene como objetivos hacer tuning a una instalación de SQL Server y esta orientado a los DBA.

Aquí les dejo el link

Conversiones implícitas (CONVERT_IMPLICIT) y afectación en la performance

Las conversiones implícitas dentro de una query se da por lo general por desprolijidades en el código. Aquí veremos algunos ejemplos de esto y los impactos negativos en la performance que esto origina.

Veamos un primer ejemplo con las siguientes consultas.

USE AdventureWorks2012

GO

 

SELECT * FROM [HumanResources].[Employee]

WHERE [NationalIDNumber] = 295847284

 

SELECT * FROM [HumanResources].[Employee]

WHERE [NationalIDNumber] = '295847284'

 

Estas dos consultas retornan el mismo resultado, pero hay una diferencia! el campo NationalIdNumber en la tabla es de tipo Nvarchar(30)

CONVERT1

Observemos ahora cada query plan por separado, vayamos entonces primero a ver el query plan de la consulta 1 (la cual le hemos pasado el valor de un tipo de dato incorrecto)

convertqp1

Acá podemos observar que se hace un Index Scan sobre la tabla Employee, si observamos en el detalle de los predicados del Index Scan nos encontraremos con la siguiente situación:

CONVERT_IMPLICIT(int,[AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber],0)=(295847284)

Obviamente que al aplicar un convert en los campos se hará un Scan.

Ahora veamos el segundo query plan donde el tipo de dato coincide con el del campo

convertqp2

Ahora podemos observar que el Index Scan se transformo en un Index Seek Sonrisa .

Recomendaciones: Como se ha podido observar el usar tipos de datos en las variables distintos a los de los campos en las tablas puede traer este problema de CONVERT_IMPLICIT, la recomendación es tan sencilla como emprolijar esto y que las variables o datos del WHERE sean del mismo tipo de dato que la columna en la tabla.

Como obtener el listado de los errores de login hacia el SQL Server

Por defecto la instancia de SQL Server genera registros en el log de errores cuando se desea establecer una conexión y hay un error, el mismo puedo ser por error de contraseña o que el login no exista.

Tener un informe de los logins fallidos es algo aconsejable para poder monitorear el comportamiento de nuestro servidor y no encontrarnos con algunas sorpresas Sonrisa

Si bien hay varias formas de hacer este control, yo aquí les mostrare la mas estándar y la que funciona en todas las ediciones. Para ello debemos leer el log de errores con un procedimiento almacenado no documentado (interno digamos Sonrisa ) llamado sp_readerrorlog .

El siguiente script busca los errores en el log actual donde haya sido por login fail

USE TEMPDB

GO

 

CREATE TABLE ERRORLOG (LOGDATE DATETIME,

                       PROCESSINFO NVARCHAR(500),

                                 TEXT VARCHAR(MAX)

                                 )

 

INSERT INTO ERRORLOG

EXEC SP_READERRORLOG 0,1,'LOGIN FAILED'  

 

SELECT * FROM ERRORLOG ORDER BY LOGDATE DESC

Evitar las funciones y manipulación de los campos en el WHERE para mejorar la performance

Los índices son parte fundamental de nuestra base de datos. Los mismos permiten recuperar de forma mas eficiente los registros sin la necesidad de recorrer toda una tabla. Básicamente funcionan como los índices de un libro.

Ahora bien, hay cosas que debemos saber para poder no entrar en problemas, y es básicamente que si aplicamos funciones o hacemos manipulaciones en los campos de un WHERE o JOIN no se usaran los índices de forma eficiente de existir para dichas columnas.

En este post veremos varios ejemplos de esta mala practica de desarrollo la cual atenta contra la performance de nuestra instalación de SQL Server.

Ejemplo 1: Fechas: En este ejemplo veremos dos querys para recuperar todos los registros de la tabla Sales.SalesOrderHeader correspondientes al año 2005, el primer query (no performante) esta escrito de la siguiente manera:

SELECT ORDERDATE FROM

Sales.SalesOrderHeader

WHERE YEAR(OrderDate) = 2005

 

funciones1

Si observamos el query plan podemos ver que el mismo realiza una operación de Index Scan con un costo total de 0,092.

 

Si en lugar de escribir la query de esta manera donde usamos funciones en el campo OrderDate lo hacemos de la siguiente veremos un cambio radical en el Query Plan

 

SELECT ORDERDATE FROM

Sales.SalesOrderHeader

WHERE OrderDate >='20050101' AND

      OrderDate <  '20060101'

 

funciones2

 

El costo de este segundo query plan es de 0,0032 vs los 0,092 del anterior, logrando así una mejora sustancial en la performance.

Ejemplo 2: Manipulación de campos.

En este ejemplo veremos otra forma de generar esta mala practica la cual es manipular los valores en los campos del Where.

SELECT * FROM

Purchasing.PurchaseOrderHeader

WHERE PurchaseOrderID * 2 = 3400

funciones3

El costo de este query plan es de 0,017.

Ahora bien, esta segunda query responde a la misma pregunta pero lo hará de forma eficiente:

SELECT * FROM

Purchasing.PurchaseOrderHeader

WHERE PurchaseOrderID = 3400 / 2

funciones4

Aquí el costo del query plan es de : 0,0032 vs los 0,017 de la query anterior.

Ejemplo 3: Uso de funciones vs Like.

Aquí veremos otro ejemplo donde los resultados son los mismos pero la performance no. En el primer caso usaremos un LEFT y en el segundo un LIKE.

SELECT EmailAddress

FROM person.contact

WHERE left(EmailAddress,2) = 'As'

funciones5

El costo de este query plan es de : 0,15.

SELECT EmailAddress

FROM person.contact

WHERE EmailAddress like 'AS%'

funciones6

El costo de este query plan es de 0,0033 vs los 0,15 del ejemplo anterior.

Ejemplo 5: Traer todos los registros de Sales.SalesorderHeader de los últimos 3 días.

En este otro ejemplo debemos recuperar todas las ordenes de los últimos 3 días, para ello usaremos dos querys, la primera de ellas aplicando funciones en el where y la segunda haciendo una reingeniería de la misma para que sea mas performante y así evitar esta mala practicas.

SELECT OrderDate  FROM sales.salesorderheader

WHERE DATEDIFF(DD,OrderDate,GETDATE()) <= 3

funciones7

El costo de este primer query es de : 0,089

El segundo ejemplo es sin utilizar las funciones aplicadas a campos

SELECT OrderDate  FROM sales.salesorderheader

WHERE OrderDate >= GETDATE() - 3

funciones8

El costo de este segundo query es de 0,0032 vs los 0,089 anteriores.

Resumen y conclusiones:

Como se ha podido demostrar en este post el uso de funciones o manipulación de los datos en el campo en una condición de búsqueda hará básicamente un uso ineficiente de los índices (Index Scan) afectando la performance. Esto básicamente sucede ya que el índice no se creo con la función sino solo para el campo, con lo cual si aplicamos una función lo primero que se debe hacer es al índice aplicarla y luego buscar, por ello lo del Scan.

Esta practica de desarrollo es una de las que me encuentro con mas frecuencia en los clientes , mi recomendación es evitarla para poder lograr la mejor performance. En la mayoría de los casos es evitable solamente que muchas  veces de desconoce del alto impacto negativo que trae usarlas entonces se va por el código quizás mas simple y no así el mas optimo.

Como exportar todos los JOBs usando el Management Studio

En varias ocasiones necesitamos exportar los JOBs (trabajos) del agente a otro servidor, por ejemplo para una migración. Esta operación se puede hacer de forma simple desde el SQL Server Management Studio como veremos a continuación.

Paso 1: Entre a la instancia origen con su SSMS.

Paso 2: Vaya al agent de SQL Server como se muestra en la siguiente imagen

jobs1

Paso 3: Seleccione la opción JOB

jobs2

Paso 4: Activamos el Object Detail, para ello podemos ir al menú view y luego a la opción : Object Explorer Details o bien pulsar F7.

jobs3

Paso 5: Desde el object Explorer Details seleccionar todos los JOBS a exportar

jobs4

Paso 6: Pulsar el botón alterno del mouse sobre el object explorer Details y seleccionar : Script Jobs as –> CREATE TO – File .

Seleccionamos la ubicación y el nombre del archivo a guardar y ya tendremos los jobs en modo Script, ahora lo único que nos hace falta es abrir el .SQL en el servidor destino y ejecutarlo.

Nota: No se olvide de pasar los operadores previamente, para ello puede usar este mismo procedimiento pero en lugar de ir al menú JOBS del Agent debe seleccionar Operators