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

Microsoft ha liberado el 24 de diciembre de 2010 la actualización acumulativa 1 del Microsoft SQL Server 2005 (build 9.00.5254) que solo se debe aplicar a una revisión de SQL Server 2005 Service Pack 4 (build 9.00.5000). Esta actualización contiene soluciones para problemas que han sido corregidos desde el lanzamiento de SQL Server 2005 Service Pack 4.

Es importante tener en cuenta que es recomendable instalar esta actualización solo en sistemas que experimenten los problemas específicos que corrige este paquete y solo si estos sistemas se ven muy afectados por dichos problemas.  El próximo service pack del SQL Server 2005 va a contener los hotfix de este paquete de actualización.

Pueden encontrar los detalles de la actualización (incluidos los problemas resueltos) y el enlace de descarga en:

http://support.microsoft.com/kb/2464079

También pueden encontrar el paquete como Cumulative update package 1 for SQL Server 2005 Service Pack 4 o CU1 for SQL Server 2005 Service Pack 4

Microsoft ha liberado el 17 de diciembre 2010 el Service Pack 4 del SQL Server 2005 (BUILD 9.00.5000). El SQL Server 2005 SP4 incluye las actualizaciones acumulativas del SQL Server 2005 SP3 desde la 1 a la 11, además de otras correcciones solicitadas por los clientes.

Debido a que las compilaciones de estas actualizaciones son acumulativas, cada nueva actualización contiene todas las mejoras anteriores y por lo tanto no es necesario instalarla antes de esta actualización.

En el SQL Server 2005 las actualizaciones no son multilenguaje. Por lo tanto debe ser utilizado el paquete del idioma que coincide con la instalación que se desea actualizar. Este service pack 4 incluye actualizaciones de todos los componentes del SQL Server, pero solo se instalarán los correspondientes a los componentes que ya están instalados en el sistema.

Esta actualización se puede utilizar para actualizar las siguientes ediciones de SQL Server 2005:

• Enterprise
• Developer
• Standard
• Workgroup

Para obtener SQL Server 2005 Express Edition , se debe descargar la versión SP4 del SQL Server 2005 Express o el SQL Server 2005 Express Edition con Advanced Services.

Para obtener más información, puede ver los siguientes links:

Microsoft SQL Server 2005 SP4 Release Notes
Notas de la versión de Microsoft SQL Server 2005 SP4 (es-ES)

Lista de los errores corregidos en SQL Server 2005 Service Pack 4
http://support.microsoft.com/kb/2463332

Descargas del Service Pack 4 de Microsoft SQL Server 2005

Service Pack 4 de Microsoft SQL Server 2005 (Inglés)

Service Pack 4 de Microsoft SQL Server 2005 (Español)

SQL Server 2005 Express Edition SP4

Service Pack 4 de Microsoft SQL Server 2005 Express Edition (Español)

Microsoft SQL Server 2005 Express Edition Service Pack 4 (Inglés)

Microsoft SQL Server Management Studio Express Service Pack 4 (Inglés)

Service Pack 4 de Microsoft SQL Server Management Studio Express (Español)

Otras descargas

Feature Pack for Microsoft SQL Server 2005 SP4 (Inglés)

Feature Pack de Microsoft SQL Server 2005 SP4 (Español)

Sistemas operativos compatibles:

• Windows 7
• Windows Server 2003
• Windows Server 2008
• Windows Server 2008 R2
• Windows Vista
• Windows XP

NOTA: Siempre es recomendable que pruebe las actualizaciones antes de implementarlas en un entorno de producción.

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