Codepoint

by Trentia Consulting

SQL Server 2012: Column Store Index

Column Store Index es una nueva característica de SQL Server 2012 que mejora el rendimiento del almacenamiento de datos. A diferencia de los índices normales o pilas que almacenan los datos en una estructura de árbol B, el Column Store Index almacena los datos en columnas y usa una compresión para reducir los accesos a disco E/S necesarios para atender las solicitudes con un modo de procesamiento por lotes.

Este índice ha sido diseñado para acelerar significativamente consultas comunes d ealmacenamiento de datos que requieren análisis, agregación y filtrado de grandes cantidades de datos o unión de múltiples tablas con un esquema en estrella. En este artículo crearemos dos tablas con la única diferencia que una trabajará con un índice regular y la otra con una Column Store Index.

La sintáxis para la creación de una Column Store Index sería:

CREATE NONCLUSTERED COLUMNSTORE   INDEX  ON  (, , , ... 
A continuación mostraré un ejemplo de creación de este tipo de índice. Primero crearé dos tablas:
CREATE TABLE TablaIndiceRegular(
       [ID] [int]   NOT NULL,
       [DetalleID] [int] IDENTITY(1,1) NOT NULL,
       [ProductoID] [int] NOT NULL,
       [Total] [money],
CONSTRAINT [PK_TablaConIndiceRegular_Index]   
PRIMARY KEY CLUSTERED 
(
       [ID] ASC,
       [DetalleID] ASC
)) ON [PRIMARY]
GO

CREATE TABLE TablaColumnStoreIndex(
       [ID] [int]   NOT NULL,
       [DetalleID] [int]   IDENTITY(1,1) NOT NULL,
       [ProductoID] [int] NOT NULL,
       [Total] [money],
CONSTRAINT [PK_TablaColumnStoreIndex_Index]   
PRIMARY KEY CLUSTERED 
(
[ID] ASC,
[DetalleID] ASC
)) ON [PRIMARY]
GO

 

Y por último crearé un índice regular (campos ProductoID y Total) en la primera tabla y un índice del tipo Column Store Index (campos ProductoID y Total) en la segunda tabla.

CREATE_NONCLUSTERED_INDEX[XI_TablaConIndiceRegular_Index_ProductoID_Total]
ON SalesOrderDetailWithRegularIndex
(ProductID, LineTotal)
 
CREATE_NONCLUSTERED_COLUMNSTORE_INDEX[XI_TablaColumnStoreIndex_Index_ProductoID_Total]
ON SalesOrderDetailWithColumnStoreIndex
(ProductID, LineTotal)
Los índices también se pueden crear usando el asistente en SSMS (SQL Server Management Studio) ý no sólamente mediante T-SL script.
Si se hace doble click en el índice creado se puede consultar y modificar las propiedades del índice creado.
Si en las tablas creadas se añadimos millones de registros y se ejecuta una consulta simple como puede ser por ejemplo:
SELECT ProductoID, SUM(Total) AS 'SumaTotal'   FROM 

GROUP BY ProductoID
ORDER BY ProductoID

 

Si miramos mediante SSMS el coste relativo a la consulta ejecutada sobre cada una de las tablas se puede apreciar una diferencia abismal ya que al trabajar con la tabla que usa el Column Store Index hablamos de un coste que se mueve cerca del 10%, en cambio trabajando con la tabla que tiene el índice regular hablamos de un coste relativo al 90%.

Esto se debe a que en SQL Server 2012 se ha introducido un nuevo modo de ejecución llamado modo por lotes, que procesa lotes de filas, en caso de procesar fila a fila que es la forma de trabajar de los índices regulares.

Quiero finalizar esta reseña nombrando un estudio realizado por Microsoft en un equipo de 32bits cons 256GB de RAM y una tabla de 1TB de datos (1,44 mil millones de filas) contrastando que las consultas que se realizaban habían variado su tiempo de respuesta de forma más que significativa puesto que consultas que tardadan 500 segundos originalmente, se ejecutaban en un tiempo de 1,1 segundos.

Reporting Services y SET FMTONLY

La instrucción SET FMTONLY en SQL Server se utiliza para definir si queremos recibir solamente metadatos cómo resultado de las solicitudes.

SET FMTONLY { ON | OFF }

La opción por defecto es SET FMTONLY OFF, por lo que si queremos ejecutar una consulta SQL y recibir solamente información sobre las columnas deberemos de cambiar previamente el valor a SET FMTONLY ON.

USE [Usuarios]
GO
SET FMTONLY ON
SELECT *
  FROM Usuarios
SET FMTONLY OFF
GO

SQL Reporting Services trabaja con FMTONLY activado (FMTONLY ON) al ejecutar la función Refresh Fields para leer las columnas de un procedimiento almacenado.

image_2

Sin embargo, es posible que si estamos tratando con un procedimiento almacenado complejo, SQL Server no pueda resolver los nombres de las columnas a no ser que previamente construya el conjunto de datos (lo que no ocurrirá ya que FMTONLY está en ON). Si nos encontramos en éste caso, nuestro conjunto de columnas de la respuesta del procedimiento almacenado estará vacío.

image_4

Para solucionarlo, podemos cambiar el estado de FMTONLY a OFF en nuestro procedimiento almacenado:

USE [Usuarios]
ALTER PROCEDURE [dbo].[sp_Consulta]
AS
BEGIN
    SET FMTONLY OFF;
 
    declare @sql varchar(5000)        

    SET @sql =
            'SELECT *                              
             FROM Usuario
             WHERE (1=1)'
    
    exec (@sql)
END

LLamada a un Web Service de creación de usuarios de SharePoint a través de un ensamblado de SQL Server

Como es sabido, SQL Server 2008/2005 permite integración de ensamblados de .NET para interactuar entre otras, con fuentes externas.

Pues bién, esta entrada va a tratar la problematica de la creación de usuarios de SharePoint con autentificación por FBA. El motivo es simplemente que cuando queremos importar las tablas de usuario de ASP.NET desde una fuente externa, no podemos insertar los datos completos del usuario, esto únicamente se puede hacer desde SharePoint.

Manos a la obra, en primer lugar hemos de crear Web Service para la creación del usuario en SharePoint del siguiente estilo:

[WebMethod]
public void CreateUser(string Site, string UserProvider,string UserId, string UserName) 
{
	SPSecurity.RunWithElevatedPrivileges(delegate()
	   {
		   using (SPSite site = new SPSite(Site))
		   {
			   SPWeb web = site.RootWeb;
			   web.AllUsers.Add(UserProvider + "|" + UserId, "", UserName, "");
			   web.EnsureUser(UserProvider + "|" + UserId);
		   }
	   });
			
}

Una vez creado, hemos de crear un proxy del WebService a través de la utilidad WSDL.EXE(lo podéis encontrar en C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\x64)

La llamada será del estilo:

WSDL.exe /o MyServiceProxy.cs /n Trentia.SPUsers http://<URLdelWebService>

Con la clase resultante ya podemos insertarla en nuestro proyecto de biblioteca de clases de SQL Server.

El siguiente paso, es crear nuestra clase que será invocada a través de SQL:

[SqlProcedure]
public static void GetMessage(SqlString WSUrl, SqlString SiteURL, SqlString UserProvider, SqlString UserID, SqlString UserName
   , out SqlString strMessge)
{

	try
	{
		Service1 svc = new Service1();
		svc.Url = WSUrl.ToString();
		//svc.Credentials = CredentialCache.DefaultCredentials;
		// call web service method; return as a DataTable
		svc.CreateUser(SiteURL.ToString(), UserProvider.ToString(), UserID.ToString(), UserName.ToString());

		strMessge = "OK";
	}
	catch (Exception ex)
	{
		strMessge = ex.ToString();
	}
}

En algunas ocasiones, sobre todo cuando intentamos acceder a WebServices e intercambiar datos, hemos de generar una clases serializable del ensamblado.

Para ello, hemos de ejecutar otra utilidad llamada SGEN.exe ubicada también en C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\x64

La ejecución es la siguiente:

SGEN.exe /a: <RutaYEnsambladoDLL> /compiler:keyfile:<RutaYFicheroSNK>

Con el ensamblado generado por el Visual Studio y el ensamblado generado con la utilidad SGEN.exe, ya tenemos todo lo suficiente para realizar las llamadas a través del SQL Server.

Desde el SQL Server, en primer lugar hemos de habilitar una seria de parámetros en nuestra BBDD

sp_configure 'clr enabled', 1
GO
reconfigure
GO

ALTER DATABASE [DATABASENAME]
SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY MSSQLTipsCLRLib 
FROM 'c:\Trentia.SQLAssemblySharePointUsers.dll'
WITH PERMISSION_SET = UNSAFE /* SI DA ERROR: EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false **/

CREATE ASSEMBLY [MSSQLTipsCLRLib.XmlSerializers]
FROM 'c:\Trentia.SQLAssemblySharePointUsers.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE

 

Una vez tenemos los ensamblados cargados únicamente nos queda crear un PROCEDURE que será quien invoque el ensamblado.

Para ello:

CREATE PROCEDURE [dbo].[AddSPUser]

   @WSUrl NVARCHAR(200),
   @SiteURL NVARCHAR(200),
   @UserProvider NVARCHAR(200),
   @UserID NVARCHAR(200),
   @UserName NVARCHAR(200),
   @strMessge NVARCHAR(400) OUT
  

WITH EXECUTE AS CALLER
AS
EXTERNAL NAME MSSQLTipsCLRLib.SPCreateUserProfileSharePoint.GetMessage
GO

EXEC DBO.AddSPUser 'http:///_vti_bin/SPUser.asmx','HTTP:///','i:0#.f|proveedoraspnet','1019','juanito',''

Recursividad en procedimientos almacenados (SQLServer)

Cuando se intentan crear procedimientos almacenados con SQLServer en caso de que existan cursores en el procedimiento en cuestión, aparecerá un error que indicará que el CURSOR XXX está abierto y que no es posible abrirlo nuevamente.

Este error se debe a que los cursores son globales a nivel de base de datos, la forma de solucionarlo es ir a Propiedades de la base de datos, sección Opciones y modificar el valor del Cursor predeterminado (Default cursor) de GLOBAL a LOCAL, con esta acción ya no aparecerá el problema en la recusividad con cursores.

Microsoft Business Intelligence Indexing Connector para SharePoint 2010

Microsoft acaba de liberar Microsoft Business Intelligence Indexing Connector.
Es un conector que permite realizar búsquedas de información sobre documentos de BI como podrían ser informes de SQL Reporting, Excel, ...
A través de este conector podríamos por ejemplo, realizar búsquedas de información a través de dimensiones.
Descargar Microsoft Business Intelligence Indexing Connector

Cursos Gratuitos sobre SQL Server 2008 R2 y SQL Azure

SQL-Server-2012
Microsoft ha liberado de manera gratuito unos recursos de aprendizaje gratuito sobre SQL Server 2008 R2 y SQL Azure.
Son los siguientes:

Máquina virtual disponible con las versiones 2010 de Microsoft

Microsoft, acaba de liberar una máquina virtual (Hyper-V) para poder testar todas las versiones 2010 de Microsoft (Office 2010, Sharepoint 2010,SQL Server 2008 R2, Visual Studio 2010,Visio 2010,Office Web Application, Fast Search Sharepoint 2010 y Project Server 2010).
Los requerimientos son Windows Server 2008 con soporte Hyper-V, 8Gb de RAM y 50Gb de espacio en disco.
La descarga ocupa 18Gb y la podemos descargar desde aquí.
Bon apetit !!


Introducció a Microsoft SQL Server 2008 R2

Microsoft nos ofrece un extenso ebook de 216 páginas en formato PDF o XPS dónde se tratan las novedades de la Release 2 de SQL Server 2008. Aquí tenéis una relación de los capítulos cubiertos:

042210_1611_Introduccia1

  • PART I Database Administration
  • CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements
  • CHAPTER 2 Multi-Server Administration
  • CHAPTER 3 Data-Tier Applications
  • CHAPTER 4 High Availability and Virtualization Enhancements
  • CHAPTER 5 Consolidation and Monitoring
  • PART II Business Intelligence Development
  • CHAPTER 6 Scalable Data Warehousing
  • CHAPTER 7 Master Data Services
  • CHAPTER 8 Complex Event Processing with StreamInsight
  • CHAPTER 9 Reporting Services Enhancements
  • CHAPTER 10 Self-Service Analysis with PowerPivot

 

Vía: http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

CTP de Report Builder 3.0

En noviembre de 2009 Microsoft nos deleita con la CTP (Community Technology Preview) de su herramienta de "usuario" para la creación de informes, Report Builder, en su versión 3.0. Esta nueva versión viene con novedades importantes relacionadas a nuevas funcionalidades de SQL Server 2008 y Sharepoint, como las ReportParts (trozos reutilizables de informes, la posibilidad de disponer las listas de Sharepoint como origen de datos (SharePoint List Data Extension), la posibilidad de representar datos geoespaciales sobre un mapa dentro del informe, etc.

Podeies ver una lista completa de las novedades en http://technet.microsoft.com/en-us/library/ee633667(SQL.105).aspx

12