Los índices hipotéticos (Hypothetical Indexes) son creados por el asistente para la optimización de índices (Index tuning wizard o Database Engine Tuning Advisor) cuando intenta determinar que tablas y que columnas se pueden utilizar en un índice para optimizar las consultas.

Por lo general, se borran todos los índices hipotéticos cuando se sale del asistente para optimización de índices. Esto sucede sin problemas cuando termina limpiamente, pero si falla, cancela, o no termina correctamente, entonces estos índices hipotéticos pueden quedar en la base de datos.

El problema de rendimiento originado por los índices hipotéticos.

Cuando se realiza una consulta o se ejecuta un procedimiento almacenado existe normalmente una compilación inicial que permite determinar un plan de ejecución optimizado de acuerdo a la distribución y las características de los datos y la consulta utilizada. Como los índices hipotéticos no se actualizan, existe una gran posibilidad de que los índices creados luego de un tiempo contengan información desactualizada que no representa fielmente los datos. Como el optimizador utiliza parte de la información de estos índices hipotéticos, incorrectamente determina que se requiere una recompilación. Durante los siguientes recompilaciones, la información de los índices hipotéticos nunca se actualiza, por lo que el optimizador permanece recompilando el plan de ejecución y usando información estadística vieja.

Los índices hipotéticos pueden dificultar la generación de un plan de ejecución optimizado para la consulta. Por lo tanto, la eliminación de los índices hipotéticos es la mejor alternativa.

Como eliminar los índices Hipotéticos

Para determinar si un índice es hipotético se puede usar la función INDEXPROPERTY(<table_id>,<índ_name>,IsHypothetical) que devuelve 1 si el índice es hipotético.

El siguiente query genera un script para eliminar los índices hipotéticos.

SET NOCOUNT ON
DECLARE @sSql NVARCHAR(1024)
DECLARE @objid INT
DECLARE @indid TINYINT
DECLARE HypIndexCursor CURSOR FOR 
    SELECT id, indid 
    FROM sysindexes 
    WHERE 
            INDEXPROPERTY(id, name, 'IsHypothetical') = 1
            --AND name LIKE 'hind_%'         
    ORDER BY name

OPEN HypIndexCursor
FETCH NEXT FROM HypIndexCursor INTO @objid, @indid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    SELECT @sSql = (
            SELECT 
                CASE WHEN INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 
                    THEN 'DROP STATISTICS ' 
                ELSE 'DROP INDEX ' 
                END 
                + QUOTENAME(OBJECT_NAME(i.id)) + '.' + QUOTENAME(i.name)
            FROM sysindexes i 
            JOIN sysobjects o 
                ON i.id = o.id
            WHERE     i.id = @objid 
            AND     i.indid = @indid 
            )
    
    --EXEC(@sSql)
    PRINT @sSql
    FETCH NEXT FROM HypIndexCursor INTO @objid, @indid
END
CLOSE HypIndexCursor
DEALLOCATE HypIndexCursor

Existen ocasiones en las cuales  el collation de la base de datos no coincide con el del servidor. Si bien existe la posibilidad de utilizar ALTER DATABASE para cambiar el collation esto no resuelve todos los problemas porque internamente las tablas pueden tener columnas con un collation diferente al del servidor, y cambiarlas impone más desafíos.

Una de las posibles consecuencias que se producen bajo estas circunstancias, es que al ser diferentes los collations, se debe utilizar la sintaxis COLLATE por ejemplo para hacer un join entre una tabla temporal con el collation de la instancia y una tabla dentro de la base de datos con diferente collation. Esto en ocasiones invalida la utilización de índices y produce menor rendimiento. Por este y otros motivos a veces nos vemos forzados a modificar todas las tablas que tienen columnas con un collation diferente al del servidor, y hacerlo homogeneo e idéntico al del servidor. Para realizar esta tarea tediosa en ocasiones se requiere eliminar las restricciones FK, VIEW, TIGGERS,  y otros elementos relacionados con la tabla, antes de realizar las modificaciones al collation con ALTER TABLE y luego recrearlos.

El script de PowerShell que pueden encontrar en el siguiente enlace les permite resolver esta tarea mediante objetos SMO y PowerShell. Para ello deben tener instalado al menos la versión 2 de PowerShell y los componentes clientes del SQL server 2008 o SQL server 2008 R2.

Artículo en Ingles

http://sqlblogcasts.com/blogs/martinbell/archive/2011/03/07/Changing-Database-Collations-with-Powershell.aspx?CommentPosted=true#commentmessage

Fuente del Script que cambia el collation

http://sqlblogcasts.com/blogs/martinbell/Powershell/Change-Collation.ps1.txt

IMPORTANTE

Es un excelente script que he utilizado en dos ocasiones y ha funcionado a la perfección.  Sin embargo al momento de escribir este post existe un pequeño error en el script que se repite dentro de todas las funciones que generan los scripts TSQL que reconstruyen al final (FK, VIEW, etc) y que solo se produce cuando se ejecuta el script para realizar el cambio mediante autenticación SQL Server utilizando un usuario y password de SQL Server.

Solución

Para resolver este prblema, en TODAS las líneas donde dice

$Scripter=New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") ($srv.Name)  ;

Debe decir

$Scripter=New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") ($srv)

El constructor del objeto  “Smo.Scripter” tiene como parámetro un objeto “Smo.Server” y si no es válido usa las credenciales de Windows para intentar conectarse.

Por falta de tiempo he estado escribiendo poco, pero hace unos días me encontré con este problema en  SQL Server Integration Services que puede ser interesante compartir ya que la solución es simple y el problema parece ilógico.

El problema

Estaba generando un nuevo paquete donde los datos eran importados desde un AS400 a un SQL Server.

• La columna destino en el SQL Server  era del tipo DATETIME.
• La columna origen en el AS400 era carácter y tenía una fecha con el siguiente formato “yyyy-mm-dd”. 

Los valores nulos o inválidos de la columna eran representados con  el valor ‘0000-00-00’

Como el tipo de dato de destino era diferente al de origen me asegure que la conversión implícita que se realiza sobre esa columna funcionara correctamente. Como no se puede convertir “000-00-00” a DATETIME (o interpretar como fecha) decidí usar un componente Columna Derivada (Derived Column) para reemplazar el valor de la columna “000-00-00” por un NULL usando la siguiente expresión condicional.

(FECVTO ¡= “000-00-00”) ? FECVTO; NULL(DT_STR, 10, 1252)

Según el manual la función NULL(typespec) devuelve un valor NULL asociado al tipo de datos solicitado, por lo que esperaba que NULL(DT_STR,10,1252) me devolviera un NULL del tipo string de 10 posiciones y codepage 1252.

NULL (expresión de SSIS)
http://msdn.microsoft.com/es-es/library/ms141758.aspx

En lugar de eso obtuve el siguiente error:

For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "(FECVTO  ¡= “000-00-00”) ?  FECVTO ; NULL(DT_STR,10,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator

Lo extraño de esto es que la ayuda en línea dice claramente  “devuelve un valor NULL asociado al tipo de datos solicitado” por lo que al especificar explícitamente el tipo de NULL que quería  debería devolverme el tipo de datos NULL adecuado

La solución

Parece ser que la tercera parte del operador condicional (NULL(DT_STR,10,1252)) no cumple los criterios de ser "una columna de entrada o el resultado de una conversión".  Debido a esto, se debe hacer explícitamente un CAST sobre el resultado de la función NULL.

Ésta es la sintaxis que funciona correctamente:

(FECVTO ¡= “000-00-00”) ? FECVTO; (DT_STR, 10, 1252)NULL(DT_STR, 10,1252)

Como los instaladores del SQL Server de las últimas versiones, no incorporan automáticamente al grupo de administradores de Windows (builtin\Administrators) como administradores del SQL Server (y tampoco es una buena práctica hacerlo), es bastante probable que ocurran este tipo de problemas o accidentes si los procedimientos para la generación, protección y guardado de contraseñas no están documentados y no son estrictos.

A veces, durante los procesos de operación y administración de los servidores, o instalación y configuración, puede suceder que se pierden las contraseñas o los privilegios de los administradores de un motor de base de datos de SQL Server. Entre los casos más habituales se encuentran por ejemplo:

• Se han quitado todos los inicios de sesión o todos los grupos de Windows que son miembros del rol fijo de servidor sysadmin,
  
• Los inicios de sesión que son miembros del rol fijo de servidor sysadmin son personas que no están disponibles porque han dejado la empresa o están de vacaciones por ejemplo
  
• Se pierde la contraseña, ya sea del SA o de un inicio de sesión creado como administrador de SQL Server.

Una manera de solucionar este problema es reinstalar SQL Server. Sin embargo este procedimiento tiene algunas consecuencias ya que más allá del tiempo necesario para realizar la reinstalación se pierden en este proceso datos que están grabados en las bases del sistema, como por ejemplo los inicios de sesión (o logins) y por lo tanto se deberá recuperar estos datos desde una copia de seguridad. Se debe tener en cuenta que al realizar este proceso de restauración se vuelve al estado anterior al proceso de instalación porque se reescribe la base de datos del sistema con una copia previa y dependiendo del caso, podría tener los mismos inicios de sesión que antes de la reinstalación y por consiguiente, los administradores aún no tendrán acceso.

Solución

Para resolver este problema, lo mejor es aprovechar las propiedades que tienen los administradores de Windows luego de iniciar la instancia de SQL Server en modo de usuario único utilizando la opción -m. Después de iniciar la instancia del SQL Server en modo de usuario único, cualquier miembro del grupo local de administradores del equipo puede conectarse a la instancia de SQL Server como miembro del rol fijo de servidor sysadmin y realizar tareas administrativas como cambiar permisos. Se debe tener en cuenta que en este modo no se pueden realizar todos los cambios posibles. Esto previene que los administradores de Windows abusen de los privilegios de actuar como un administrador de SQL Server.

Los pasos a seguir son los siguientes:

1. Abrir el SQL Server Configuration Manager desde el menú de Windows y detener el servicio de la instancia del SQL Server involucrada.
   
2. En la solapa de “Avanzado” (Advanced), en el cuadro de texto propiedades (Properties), agregar “;–m” al final de la lista en la opción de parámetros de arranque “Startup parameters”. Debe tenerse en cuenta que no haya espacios entre “;” y “-m” porque si no va a dar error.
   
3. Aceptar los cambios presionando “OK”.
   
4. Reiniciar la instancia del SQL Server. En este instante se debe verificar que el resto de los servicios que componen el SQL Server están de detenidos, especialmente si alguno de esos corre con una cuenta de servicio que es administrador local, porque estos servicios podrían conectarse utilizando la única conexión disponible antes de que lográramos conectarnos para realizar los cambios necesarios, y ya no sería posible que nos conectáramos porque sería una segunda conexión.
   
5. Conectarse al sql server utilizando autenticación Windows mediante la herramienta de línea de comandos “sqlcmd”. El usuario de Windows debe ser administrador local. (*)
   
   Por ejemplo:
   
   SQLCMD –S Localhost –E
   
6. Ejecutar por ejemplo un comando TSQL como "sp_addsrvrolemember" para agregar un inicio de sesión existente (o uno nuevo recién creado con CREATE LOGIN) como administrador del sql server o cambiar la contraseña de inicio de sesión con ALTER LOGIN.
   
   Por ejemplo:
   

   CREATE LOGIN [Dominio\usuario] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
   GO
   EXEC master..sp_addsrvrolemember             
                  @loginame = N'Dominio\usuario',             
                  @rolename = N'sysadmin'
   GO

7. Luego de que se tiene acceso al sql server como administrador, eliminar el “;-m” como parámetro de arranque utilizando el configuration manager y reiniciar la instancia del sql server.

(*) Importante: Si el sistema operativo tiene User Account Control (UAC), y está activado,  a pesar de pertenecer al grupo de administradores locales, no es posible conectarse al SQL Server con privilegios de administrador local a menos que la consola se abra con la opción de “ejecutar como administrador” utilizando el menú de contesto que aparece presionando el botón derecho sobre el icono del CMD.EXE.

Referencias:

Solucionar problemas: conectarse a SQL Server cuando los administradores del sistema no tienen acceso

http://msdn.microsoft.com/es-ar/library/dd207004.aspx

Iniciar SQL Server en modo de usuario único

http://msdn.microsoft.com/es-ar/library/ms188236.aspx

Hace poco migrando de equipo y edición (pero no de versión) una base de datos de SQL Server utilizada por Blackberry que estaba en un SQL Server 2005 SP3 me dio un error al realizar un RESTORE con el siguiente comando en el nuevo servidor:

RESTORE DATABASE [BESMgmt] 
FROM  DISK = N'O:\MSSQL\Blackberry\BESMgmt20101125.bak' 
WITH MOVE N'BESMgmt_data' 
TO N'O:\MSSQL\Blackberry\BESMgmt.mdf',  
MOVE N'BESMgmt_log'      
TO N'O:\MSSQL\Blackberry\BESMgmt.ldf',  
NOUNLOAD,  STATS = 10
GO

El error fue el siguiente

Error 3154: The backup set holds a backup of a database other than the existing database.

Lo extraño fue que había hecho el Backup con el siguiente comando con lo cual estaba totalmente seguro que el archivo de Backup contenía la base de datos correcta.

BACKUP DATABASE [BESMgmt] 
TO  DISK = N'C:\Backup\BESMgmt20101125.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'BESMgmt-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

La solución

La solución es simple e ingeniosa. Únicamente debemos “mentirle” para que crea que debe sobrescribir una base de datos existente utilizando WITH REPLACE. Aunque la base de datos no existe la ejecución es exitosa y resuelve el problema, permitiendo realizar el RESTORE.

Ejemplo

RESTORE DATABASE [BESMgmt] 
FROM  DISK = N'O:\MSSQL\Blackberry\BESMgmt20101125.bak' 
WITH REPLACE,  
MOVE N'BESMgmt_data' 
TO N'O:\MSSQL\Blackberry\BESMgmt.mdf',  
MOVE N'BESMgmt_log'      
TO N'O:\MSSQL\Blackberry\BESMgmt.ldf',  
NOUNLOAD,  STATS = 10
GO

La dimensión de tiempo existe habitualmente en cada data warehouse y la granularidad más usada es el día. Generalmente tiene muchos atributos pero solo algunos de estos atributos como por ejemplo el nombre del mes y el año pueden ser obtenidos fácilmente usando una expresión SQL aplicada a la fecha. Si la dimensión tiene atributos para dar soporte a múltiples lenguajes resulta a veces complicado construirla con el lenguaje SQL. Otros atributos no se pueden obtener mediante SQL ya que no hay mecanismos predecibles y dependen de decisiones humanas.

Otra característica de la dimensión tiempo es que suele tener muchos atributos como por ejemplo marcas de días feriados o no laborables, atributos de períodos fiscales o de temporadas, atributos relacionados a la fecha como el número de semana, la marca de último día del mes, y otros atributos muy útiles usados especialmente para la navegación y que deben estar integrados en la dimensión fecha mediante atributos dimensionales.

La gran ventaja de esta dimensión es que está completamente definida y especificada desde el inicio del proyecto del data warehouse. Pero la desventaja es que no tiene una fuente de datos convencional sino que se suele generar mediante algún mecanismo como una planilla o tabla cargada manualmente o mediante algún proceso.

La clave de la dimensión fecha

Todas las dimensiones fecha necesitan un atributo que representa a la fecha y un atributo para relacionar con la tabla de hechos. Debe haber al menos un registro que permita representar situaciones especiales como una fecha no aplicable o no informada o que no ha ocurrido aún y además es posible que desee distinguir varias de estas condiciones inusuales.

En estos casos en que se deben representar hechos relacionados a estas “fechas especiales”, las referencias foreign key en la tabla de hechos deben permitir referenciar a estos registros con “fechas inusuales” en la tabla de la dimensión fecha. Recordemos que el valor de estos atributos en los campos de la tabla de hechos no puede ser nulo, ya que debe estar relacionado con la tabla de la dimensión tiempo.

La clave principal de la dimensión fecha ideal debería ser una clave subrogada sin representación semántica, como por ejemplo un numero entero (que no representa una fecha), pero muchos diseñadores no pueden resistir la tentación de hacer que la clave sea legible como por ejemplo 20101116 significando el 16 de noviembre de 2010. Sin embargo, como con todas las claves inteligentes, los pocos registros especiales en la dimensión fecha harán que el diseñador utilice trucos para representarlas. Por ejemplo, la clave inteligente para una fecha “no aplicable” tendría que ser un valor sin sentido como por ejemplo 99999999, y esto trae aparejado que las aplicaciones que tratan de interpretar la fecha directamente para obtener información (como por ejemplo el mes) sin usar los atributos de la dimensión tengan inconvenientes porque no es una fecha válida.

Conclusión

En definitiva utilizar un tipo de dato fecha para el atributo clave de la dimensión fecha no es una buena elección y conviene que el valor este subrogado utilizando una clave entera que está oculto al usuario dentro de la meta data. El atributo representativo de la fecha debe ser un atributo que acepte representaciones especiales y no solamente representación de fechas válidas.

Referencia:

[PDF] Kimball Design Tip #51: Latest Thinking On Time Dimension Tables
http://www.rkimball.com/html/designtipsPDF/KimballDT51LatestThinking.pdf

Recientemente escribí un post que habla de cómo transferir los Logins entre instancias de SQL Server utilizando scripts documentados en la base de conocimiento de Microsoft.  Pueden encontrar este post en:

Cómo transferir los inicios de sesión y las contraseñas entre instancias de SQL Server

Además de transferir la contraseña, normalmente queremos que el nuevo Login tenga el mismo SID para evitar que los usuarios queden huérfanos dentro de las bases de datos migradas o transferidas. Esto lo realizan los script  documentados en la base de conocimiento que hago referencia en el post.

Sin embargo uno de los lectores me hizo notar que no había explicado que los scripts generados por estos documentos no generan los scripts correspondientes a los roles (funciones) de servidor que tenían en la instancia de origen.  Solo el método 2 del documento en inglés que transfiere los Logins desde el SQL Server 2000 al SQL Server 2005/2008 tiene la posibilidad de generar el script para transferir los roles de servidor entre las instancias. Las otras versiones del script no lo hacen.

Si ya hemos creado los Login con el script de estos documentos de la base de conocimiento y debemos  además, transferir los roles de servidor a esta nueva instancia, y nuestro origen es un SQL Server 2005 o un SQL Server 2008, podemos usar el siguiente script para generar el script de transferencia:

-- Genera el script con los Roles para los Logins
Select 
 'exec master.dbo.sp_addsrvrolemember @loginame=''' 
 + sp.name + ''', @rolename='''+sp2.name +''';' 
from 
master.sys.server_principals as sp 
join master.sys.server_role_members as srm 
on sp.principal_id = srm.member_principal_id 
join master.sys.server_principals as sp2 
on srm.role_principal_id=sp2.principal_id
where sp.type in ('S','U','G')


-- Genera el script con la base de datos por default (omision) para los Logins
Select 
 'ALTER LOGIN ['+ sp.name 
 +'] WITH DEFAULT_DATABASE = [' 
 + sp.default_database_name +'];'
from 
master.sys.server_principals as sp 
where sp.type in ('S','U','G')
and sp.default_database_name is not null