miércoles, 11 de julio de 2012

Buscar texto dentro de los sp, vw, fx

Hola, buen día

con esta instrucción se puede ubicar cualquier texto(puede ser nombre de tabla, campo, nombre de objeto) que sean utilizados dentro de los los sp, vw o fx.


select distinct 'sp_helptext ', S.Name 
From Sysobjects S
Inner join Syscomments SC on S.Id = SC.ID
where SC.Text like '%Nombre_a_buscar%'
and   S.xType in ('P', 'FN', 'V')
Order by 2

espero les sirva

martes, 10 de julio de 2012

Resource Governor




Resource Governor es poder controlar consultas determinadas y poder adjudicarles una cantidad específica de recursos. Para lograrlo, todas las conexiones que se realizan contra el SQL Server pasan por unclasificador, que se encarga de asignar un grupo a cada sesión en base a una función de clasificación. A estos grupos les podemos asignar un pool , que se encarga de otorgar ylimitar los recursos (CPU y memoria). 

Con este query podemos identificar las consultas que ingresen con el aplicativo MS SQL Query Analyzer, si es así, se le asigna el recurso desarrollo, que solo tendrá un 10% de uso de CPU.



/****** Object:  ResourcePool [desarrollo]    Script Date: 12/10/2010 16:26:57 ******/
CREATE RESOURCE POOL [desarrollo] WITH(min_cpu_percent=0, 
           max_cpu_percent=10, 
           min_memory_percent=0, 
           max_memory_percent=10)

GO



/****** Object:  WorkloadGroup [desarrollo]    Script Date: 12/10/2010 16:29:42 ******/
CREATE WORKLOAD GROUP [desarrollo] WITH(group_max_requests=0, 
           importance=Low, 
           request_max_cpu_time_sec=0, 
           request_max_memory_grant_percent=25, 
           request_memory_grant_timeout_sec=0, 
           max_dop=0) USING [desarrollo]
GO


USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[clasificacionrecursos]    Script Date: 12/10/2010 16:38:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[clasificacionrecursos] ()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
declare @GRUPO  AS sysname
DECLARE @APP_NAME nvarchar(128)
DECLARE @HOST_NAME nchar

SET @GRUPO = 'default';
SET @APP_NAME = APP_NAME()
SET @HOST_NAME = HOST_NAME()
IF @APP_NAME = 'MS SQL Query Analyzer' or @APP_NAME = 'MICROSOFT SQL SERVER MANAGEMENT STUDIO'
begin
  SET @GRUPO = 'desarrollo';
  ELSE 
  SET @GRUPO = 'default';

 end
RETURN @GRUPO ;
END

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.clasificacionrecursos);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO


jueves, 3 de mayo de 2012

Eliminar Indices Hipoteticos




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.

Con este script podemos ver y luego decidir si se eliminan dichos indices:



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

martes, 10 de abril de 2012

SQL2008: Entendiendo el Waitresource

TOMADO TEXTUALMENTE DE LA PAGINA http://aiellodba.blogspot.com/2011/08/entendiendo-el-waitresource.html

SQL2008: Entendiendo el Waitresource
 
Introduccion
Al ver los xml de bloqueos o deadlocks muchas veces aparecen números casi inentendibles, y uno de los importantes es el WAITRESOURCE. Este número codifica que objeto de la base de datos está participando en la pelea por el bloqueo. El recurso puede principalmente ser:
  • Una tabla
  • Una página
  • Una clave
Puede ser algunas cosas mas pero para simplificar el análisis nos enfocaremos en estos.
Manos a la obra
Lo primero que debemos hacer es identificar cual de las tres cosas es, lo cual es fácil leyendo el texto de wait resourse que puede ser:
“OBJECT: 19:1275867612:10”
“PAGE: 12:1:79868773”
“KEY: 12:397371816017920 (760119e06bff)”
 
En el primer caso es cuando se trata de una tabla. Para este caso (y para los siguientes) lo primero que debemos hacer es analizar el primer número antes del separador (:) . Este número en todos los casos indica el id de la base de datos, por lo tanto debo hacer la siguiente consulta para obtenerla:
SELECT * FROM sys.sysdatabases WHERE Dbid=19
 
Lo cual me da por resultado Mibase. Recordemos esto porque es común a los tres casos.
Ahora siguiendo con el análisis del primer caso la información brindada se debe analizar de la siguiente manera:

OBJECT: dbId:ObjectId:IndexId
Con la salvedad de que indexId vale 0 cuando se trata del heap y 1 cuando se trata de un índice cluster. En otro caso figurará el número del índice.
Para obtener esta información en un formato útil debería hacer las siguientes consultas
 
  1. -- Nota: debo estar situado en MiBase
SELECT OBJECT_NAME(1275867612)
-- O bien
SELECT * FROM MiBase.sys.all_objects WHERE object_id = 1275867612
-- Y para buscar el índice en caso de ser mayor que 1:
SELECT * FROM MiBase.sys.indexes WHERE object_id=1275867612  

De esta forma ya sabemos que table y que índice participaba en el bloqueo.

Para el caso 2 (página) comenzaremos averiguando la base de la misma forma y la siguiente información nos viene como:

PAGE: dbId:FileId:PageId

Si no tenemos nuestra base con varios archivos sino todo en uno del primary el segundo campo siempre será 1.

Para analizar el último debemos obtener la información de la página (79868773 en el ejemplo). Para esto tenemos dos formas:

DBCC TRACEON ( 3604 )

DBCC PAGE (12,1,79868773)

o

DBCC PAGE (12 , 1, 79868773) WITH TABLERESULTS,NO_INFOMSGS

En el primer caso es necesario el traceon porque sino no podemos ver la salida, la cual saldrá en modo texto. En el segundo no es necesario visto que la salida la veremos en modo tabla.

Sea cual fuera el caso que elegimos debemos buscar el renglon (o registro) que tenga la siguiente información:

m_objId (AllocUnitId.idObj) = 1051306955 m_indexId (AllocUnitId.idInd) = 7

 
y con esto obtengo el ObjectId para poder continuar mi análisis como en el caso anterior.

pd. para saber cual es el id del objeto debemos ver la opcion
 
Metadata: ObjectId = 907488099
 
para saber el objeto simplemente utilizamos cualquiera de las opciones descritas anteriormete

SELECT * FROM sys.all_objects WHERE object_id = 907488099
SELECT * FROM sys.indexes WHERE object_id=907488099
SELECT OBJECT_NAME(907488099)


Por último nos queda el caso del índice (KEY: 12:397371816017920 (760119e06bff)). En este caso debemos leerlo como:

KEY: dbId:hObjecto (hash)



El hash puede ser ignorado, visto que no tenemos función para transformarlo (es la gracia de los hash!), así que nos quedaremos con el hObj. Este número se encuentra almacenado en la msdb así que lo podemos utilizar para calcular el objetcId
 

  1. SELECT * FROM MiBase.sys.partitions
WHERE hobt_id = 397371816017920 


De esta consulta obtenemos el object_id y podemos hacer nuestro análisis de siempre.

actualizacion de las estadisticas

Hola a todos!!!

con este script podemos actualizar todas las estadisticas de una base de datos

DECLARE @MaxDaysOld int

DECLARE @SamplePercent int

DECLARE @SampleType nvarchar(50)

SET @MaxDaysOld = 0

SET @SamplePercent = NULL --25

SET @SampleType = 'PERCENT' --'ROWS'

BEGIN TRY

DROP TABLE #OldStats

END TRY

BEGIN CATCH SELECT 1 END CATCH

SELECT

RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))

,TableName = OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)

,StatName = st.name

,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1)

INTO #OldStats

FROM sys.stats st WITH (nolock)

--WHERE DATEDIFF(day, ISNULL(STATS_DATE(object_id, st.stats_id),1), GETDATE()) > @MaxDaysOld

ORDER BY ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))

DECLARE @MaxRecord int

DECLARE @CurrentRecord int

DECLARE @TableName nvarchar(255)

DECLARE @StatName nvarchar(255)

DECLARE @SQL nvarchar(max)

SET @MaxRecord = (SELECT MAX(RowNum) FROM #OldStats)

SET @CurrentRecord = 1

SET @SQL = ''

WHILE @CurrentRecord <= 100--@MaxRecord

BEGIN

SELECT

@TableName = os.TableName

,@StatName = os.StatName

FROM #OldStats os

WHERE RowNum = @CurrentRecord

SET @SQL = N'UPDATE STATISTICS ' + @TableName + ' ' + @StatName + ' WITH FULLSCAN'

PRINT @SQL

EXEC sp_executesql @SQL

SET @CurrentRecord = @CurrentRecord + 1

END

miércoles, 4 de abril de 2012

tablas system information sql server 2000 vs sql server 2005-2008

Buenos dias

encontre la siguiente tabla donde nos indica las tablas del sistema en sql server 2000 vs las tablas del 2005-2008 como las podemos encontrar.

IDInformationSQL Server 2000SQL Server 2005
1Database system table\view - Source for all databases on the SQL Server to include the name, owner, creation date, etc.
SELECT *
FROM master.dbo.sysdatabases
GO
SELECT *
FROM sys.databases;
GO
2Database files system table\view - Source for the currently connected database's file names, size, location, type (database or log), etc.
SELECT *
FROM dbo.sysfiles
GO
SELECT *
FROM sys.database_files;
GO
3Database files system table\view - Source for all database's file related information
SELECT *
FROM master.dbo.sysaltfiles
GO
SELECT *
FROM sys.master_files;
GO
4IO statistics on database files - Returns the usage statistics on a per file basis-- Single database file
SELECT *
FROM :: fn_virtualfilestats(1, 1)
GO
-- All database files
SELECT *
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
GO
5Database meta data - Returns the pertinent database name, size and remarks
EXEC master.dbo.sp_databases
GO
EXEC master.dbo.sp_databases;
GO
6Database meta data - Fairly complete set of pertinent database information that can return data for all databases or 1 database
-- All databases
EXEC master.dbo.sp_helpdb
GO

-- Single database
EXEC master.dbo.sp_helpdb 'Northwind'
GO
-- All databases
EXEC master.dbo.sp_helpdb;
GO
-- Single database
EXEC master.dbo.sp_helpdb 'AdventureWorks';
GO

7Change database ownership - System stored procedure to change the database owner
EXEC sp_changedbowner sa
GO
EXEC sp_changedbowner sa;
GO
8Database ID to name translation - System function that will provide the database name when passed the database ID from the database system table
-- Returns the master database
SELECT DB_NAME(1)
GO
-- Returns the master database
SELECT DB_NAME(1);
GO
9Database name to ID translation - System function that will provide the database ID when passed the database name from the database system table
-- Returns 1
SELECT DB_ID('master')
GO
-- Returns 1
SELECT DB_ID('master');
GO
10Database status - System function that will return the value for 1 of ~25 database specific valuesSELECT DATABASEPROPERTYEX('master', 'Status')
GO

jueves, 2 de febrero de 2012

Traducción del Checklist de Mejores Prácticas para ser un DBA excepcional por Brad McGehee

Mejores Prácticas para ser un DBA excepcional SQL Server (redactado por el genial Brad McGehee )

1. Ingresa a un SQL grupo de usuarios (chapters.sqlpass.org)
2. Únete a SQL PASS (www.sqlpass.org)
3. Asistir a por lo menos una conferencia de profesionales cada año.
4. Asistir a por lo menos una sesión de entrenamiento cada año.
5. Lea por lo menos cuatro libros sobre SQL Server cada año.
6. Lea el libro electrónico Cómo convertirse en un DBA excepcional (descarga free en red-gate.com)
7. Aprenda todo lo que pueda acerca de su trabajo, especialmente aquellas áreas que nadie más le gusta o quiere dominar.
8. Sea voluntario en su trabajo para tareas nuevas y difíciles que le harán conocer en toda su organización.
9. Instale SQL Server en una computadora portátil o de escritorio y practique nuevos aspectos de SQL Server, especialmente de SQL Server 2008 y 2008R2 y del nuevo denali (2010).
10. Participar en los foros de SQL Server (preguntar y responder preguntas)
1. www.SQLServerCentral.com
2. http://ask.sqlservercentral.com/com
3. www.microsoft.com / comunidades / grupos de noticias / en-us / default.aspx
11. Obtenga la certificación como DBA de SQL Server:
1. a. Microsoft Certified Technology Specialist (http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx # Tab2)
2. b. TI de Microsoft Certified Professional (http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx # tab3)
Día a día
1. Compruebe los registros de eventos del S.O. y los registros de SQL Server para detectar eventos inusuales.
2. Verifique que todos los trabajos programados se han ejecutado correctamente.
3. Confirme que las copias de seguridad se han hecho y guardado con éxito a un lugar seguro.
4. Monitoree el espacio en disco para asegurarse de que sus servidores de SQL no se quedarán sin espacio en disco. Para un mejor rendimiento, todos los discos deben tener 15% o más de espacio libre.
5. A lo largo del día, haga un seguimiento periódico de rendimiento utilizando Monitor de sistema y de perfiles / Traza de SQL.
6. Monitorear regularmente e identificar problemas de bloqueo.
7. Mantenga un registro de los cambios que realice a los servidores, incluyendo la documentación de los problemas de rendimiento a identificar y corregir.
8. Crear alertas de SQL Server para que le avise de posibles problemas, y las envíe por mail. Tomar las medidas necesarias.
9. Regularmente restaure copias de seguridad en un servidor de prueba para verificar que realmente los puede restaurar. No es necesario para restaurar todas las copias de seguridad todos los días, pero debiera hacerlo a menudo para asegurarse de que usted tiene buenas copias de seguridad.
10. Tómese su tiempo para aprender algo nuevo como un DBA para promover su desarrollo profesional.

Instalación
1. Siempre documente las instalaciones de modo completo, aun cuando parezca redundante, esto hará que las instancias de SQL Server se pueden reproducir fácilmente en caso de emergencia.
2. Si es posible, instalar y configurar todas las instancias de SQL Server de una manera consistente, de la misma forma , basándose en las normas pre-acordadas de su organización.
3. No instale los servicios de SQL Server que no se utilizan, como Microsoft indexación de texto completo, Reporting Services o Analysis Services.
4. Para obtener el mejor rendimiento de SQL Server, detenga todos los servicios del sistema operativo que no son necesarios.
5. Para un óptimo rendimiento de SQL Server, no ejecutar al SQL Server junto con ninguna otra aplicación.
6. Para obtener el mejor rendimiento I / O, busque los archivos de base de datos (. mdf) y archivos de registro (. ldf) en discos separados para aislar a los patrones de acceso a disco.
7. Si tempdb se utiliza en gran medida, pongalo en un disco aparte.
8. No instale SQL Server en un controlador de dominio.
9. Asegúrese de que SQL Server se instala en una partición NTFS.
10. No utilice NTFS con cifrado de archivos de datos (EFS) y ni compresión de datos sobre el disco de la base de datos de SQL Server y los archivos de registro.

Actualizar1. Ejecute el Upgrade Advisor de SQL Server antes de actualizar. Haga los cambios necesarios antes de realizar la actualización.2. Realizar una actualización de prueba antes de actualizar los servidores de producción. Y no se olvide de probar las aplicaciones con la nueva versión también.3. Antes de actualizar, asegúrese de tener un plan para revertir los cambios en caso que la actualización sea problemática.4. No actualizar clústeres de SQL Server en su lugar. En cambio, a reconstruirlos en un nuevo hardware.5. Si actualiza desde una versión anterior de SQL Server, debe actualizar todas las estadísticas en todas las bases de datos. Esto se debe a que las estadísticas no se actualizan automáticamente durante el proceso de actualización.


Seguridad


  1. Asegure la seguridad física de cada Servidor SQL , previniendo que cualquier usuario no autorizado pueda acceder físicamente a sus servidores.
  2. Instalar solamente las mínimas y necesarias nettwork libraries y network protocols en sus instancias SQL
  3. Minimize el número de usuarios sysadmins con acceso a SQL Server.
  4. Como DBA, logueesé como sysadmin solo cuando realmente lo necesite. Cree cuentas específicas para DBAs para que puedan acceder a SQL Server cuando no necesiten privilegios sysadmin.
  5. Asigne a la cuenta "sa" una password realmente compleja y no la use nunca para loguearse a SQL Server. En vez de eso use una cuenta Windows Authentication para acceder SQL Server como sysadmin.
  6. Dele a los usuarios los mínimos permisos necesarios que le permitan a ellos realizar su trabajo.
  7. De ser posible, use stored procedures o views para el acceso a los datos en vez de dejarlos acceder directamente a las tablas.
  8. De ser posible, use Windows Authentication logins en vez de SQL Server logins.
  9. Use strong passwords (combinación de mayúsculas, minúsculas, numeros y caracteres especiales) para todos los SQL Server login.
  10. No le asigne permisos al rol de base de datos "public"
  11. Remueve Logins IDs que ya no necesitan acceder a SQL Server apenas los detecte.
  12. Deshabilite el usuario "guest" en cada base de datos usando REVOKE CONNECT FROM GUEST.
  13. No use "cross database ownership chaining" si no es realmente necesario.
  14. Nunca habilite permisos a xp_cmdshell a los usuarios que no son sysadmin.
  15. Eliminar las bases de ejemplo (adventureworks, pubs, northwind) de todas las instancias de producción.
  16. Usar Windows Global Groups, o SQL Server Roles para manejar grupos de usuarios con similares permisos.
  17. No debería crear ni permitir crear network shares en servidores SQL Server.
  18. Configurar login auditing , de esta manera se puede ver quienes intentaron loguearse y los intentos fallidos de login.
  19. No use jamás la cuenta sa ni login ids que sean sysadmin como cuentas de acceso desde aplicaciones.
  20. Asegurese que sus servidores están detrás de un firewall y no están expuestos directamente a internet.
  21. En SQL Server 2005 y previos, remover el grupo de BUILTIN/Administrators para evitar que los administradores locales del servidor puedan acceder a SQL Server. En SQL Server 2008, el grupo BUILTIN/Administrators group no existe en forma predeterminada.
  22. Ejecute cada servicio SQL Server bajo una cuenta de dominio diferente.
  23. Dele a las cuentas asignadas a los servicios SQL Server los minimos permisos que le permitan al mismo ejecutarse de la manera correcta. En la mayoría de los casos los permisos de un administrador local no son necesarios y menos aún los permisos de un administrador de dominio.
  24. Cuando use queries distribuidas use linked servers en vez de remote servers. Remote servers están solo por backward compatibility.
  25. No navegue internet desde una instancia de producción de SQL Server.
  26. En vez de instalar antivirus/antispyware en un SQL Server, realice scaneos desde un servidor remoto y solo cuando la actividad del servidor SQL no sea tan alta.
  27. Agregue los service packs, tanto de SQL Server com de sistema operativo apenás estén disponibles.
  28. Considere la posibilidad de encriptar los backups con herramientas como Red Gate SQL Backup Pro u otras.
  29. Solo habilite las trazas de "C2 auditing" o "Common Criteria compliance" cuando sea realmente necesario, porque sobrecargan el servidor si están habilitadas.
  30. Considere seriamente ejecutar un SQL Server security scanner para detectar si hay huecos de seguridad en sus servidores, tanto a nivel sistema operativo como servidor de base de datos. http://searchsqlserver.techtarget.com/feature/SQL-Server-security-test-checklist
  31. Considere habilitar SSL or IPSEC para las conexiones entre sql server y los clientes.
  32. Si está utilizando SQL Server 2005/2008, habilite password policy checking.
  33. Si está usando SQL Server 2008 en un entorno de alta seguridad, considere implementar TDE, Transparent Data Encryption para proteger los datos de su servidor.
  34. Si utiliza SQL Server 2005, no use SQL Server Surface Area Configuration para desbloquear características que nunca se utilizan y que pueden traer solamente problemas de seguridad.
  35. Si está usando SQL Server 2005/2008 y crea endpoints, solo habilite permisos grant CONNECT a los logins que realmente necesitan acceder esos endpoints. Explicitamente deniegue (deny) CONNECT permissions a los endpoints que no son necesarios para los usuarios.


Mantenimiento de Jobs


  1. Evite ejecutar jobs en el mismo horario. Preferentemente cada job debe ejecutarse en su propio horario.
  2. Cuando cree jobs asegurese de incluir intercepción de errores, de loguear la actividad del job y de establecer alertas (mails por ejemplo) para asegurarse de saber al momento de cualquier fallo que pudiera ocurrir.
  3. Cree un login específico con el solo propósito de ejecutar jobs y asignele a dicho login todos los jobs.
  4. Si su job incluye código Transact-SQL, asegurese que está optimizado para que se ejecute correctamente.
  5. Periodicamente (diario, semanal, mensual) reconstruya o reorganice los índices de sus bases de datos para remover la fragmentación logica y aprovechar mejor el especio en disco.
  6. Periódicamente y como parte de sus tareas de mantenimiento, ejecute DBCC CHECKDB en todas sus bases de datos para comprobar su integridad y detectar eventuales problemas.
  7. Evite ejecutar comandos DBCC en los horarios donde se da la mayor carga de trabajo en sus servidores. Estos comandos son altamente requirientes en términos de recursos y afectan la performance.
  8. Si usted reinicia muy raramente el servicio SQL Server service, se va a encontrar con que el log de SQL Server se pone muy grande y toma mucho tiempo para cargar y ser mostrado. Usted puede cerrar el log error actual y crear uno nuevo ejecutando sp_cycle_errorlog o DBCC ERRORLOG. Establezca esto como un job semanal.
  9. Haga un script de todos los jobs y guarde esos scripts en un lugar seguro por si necesita en algun momento reconstruir los servers.