En el tiempo que llevo trabajando con SQL Server creo que el comando que mas veces ejecuté es el sp_who o su versión extendida sp_who2. Este comando cuando se está administrando una base es muy comodo por su sencillez y nos da un buen resumen de que está ocurriendo en el servidor. Claramente cuando hay alguna situación fuera de lo normal esto es solo la puerta de entrada para despues controlar las DMVs, los contadores del sistema operativo, etc, pero siempre es un buen comienzo. Muchas personas les pasa que acostumbradas a ambientes SQL Server se sientan frente a un sistema Oracle y buscan algo equivalente y no lo encuentran, así que veamos como sería una consulta equivalente en Oracle.
No contamos con un stored procedure prearmado que nos brinde la información, pero al igual que en SQL Server, esta información se nutre de las sesiones, así que consultaremos la V_$SESSION al igual que el sp_who original.
En este caso para que les sea mas comodo acomodé los campos para que queden igual que en la versión original.
SELECT sid, status, Username, terminal, blocking_session_status, schemaname, a.name Command_Action, logon_time, program
FROM SYS.V_$SESSION s
INNER JOIN AUDIT_ACTIONS a ON s.command=a.action;
Como podrán ver aparte de consultar la vista de sesiones hice un join con la tabla AUDIT_ACTIONS que es la que contiene las descripciones de cada evento para que sepamos que está haciendo la session.
Lo que recomiendo es guardar esta consulta en un script a mano y poco a poco irse internalizando mas con la SYS.V_$SESSION visto que tiene mucho para darnos. Quien no esté acostumbrado se va a sorprender al encontrar muchos mas campos que en su equivalente de SQL Server.
Pueden encontrar mas detalles en:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm
Por último si lo que se desea es hacer algo mas semejante al sp_who2 y no tienen ganas de internalizarse en los demas campos, puede hacerse una función que nos retorne los resultados de la consulta. Esta función sería:
-- Creacion de funcion que retorna como resultado una tabla
CREATE OR REPLACE function sp_who2 return sp_who2_tab PIPELINED
IS
CURSOR cur0
IS
SELECT sid, status, Username, terminal, blocking_session_status, schemaname, a.name Command_Action, logon_time, program
FROM SYS.V_$SESSION s
INNER JOIN AUDIT_ACTIONS a ON s.command=a.action;
out_rec sp_who2_rec := sp_who2_rec(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
OPEN cur0;
LOOP
FETCH cur0 INTO out_rec.sid, out_rec.status, out_rec.Username,
out_rec.terminal, out_rec.blocking_session_status, out_rec.schemaname, out_rec.Command_Action, out_rec.logon_time, out_rec.program;
EXIT WHEN cur0%NOTFOUND;
PIPE ROW (out_rec);
END LOOP;
CLOSE cur0;
RETURN;
END;
Y como sucede con las funciones que retornan tablas en Oracle, la forma de utilizarla sería:
SELECT * FROM TABLE(sp_who2);
Claramente a esto hay que sumarle la creación de los objetos necesarios (record y table).
Espero que les haya servido!
martes, 25 de enero de 2011
Sp_who en Oracle
Etiquetas:
Oracle,
sp_who,
sp_who2,
SQL 2008,
SQL Server
lunes, 17 de enero de 2011
Drop and Create
Muchas veces al armar los scripts figuran creación de objetos, y obviamente es deseable que nuestro script no falle por mas que dicho objeto ya se encuentre creado. En el caso de los stored procedures, en Oracle, tenemos la opción de CREATE OR REPLACE, pero cuando nos manejamos con tablas esta opción, lamentablemente, no existe ni en Oracle ni en SQL Server.
Para eliminar y crear una tabla desde cero en SQL Server es bastante conocido el método (de echo en la versión 2008 en adelante ya lo tenemos a un click del managment). El código es:
-- Prueba 1: Controlo si existe la tabla, si es necesario la elimino y la vuelvo a crear
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.prueba_creacion') AND type in (N'U'))
DROP TABLE dbo.prueba_creacion
CREATE TABLE prueba_creacion(clave INT);
De esta forma controlamos si la tabla existe, y en caso afirmativo la eliminamos. Luego podemos proceder a crearla sin problema.
Otra es la historia cuando la tabla es una tabla temporaria. Lo intuitivo sería ahcer lo mismo con el nombre de la tabla temporaria, pero esto arrojaría error. ¿porqué? porque la tabla temporaria se encuentra alojada en la tempdb. El código para poder realizar lo mismo con tablas temporarias sería:
-- Prueba 2: Controlo si existe la tabla temporaria, si es necesario la elimino y la vuelvo a crear
IF NOT OBJECT_ID('tempdb.dbo.#prueba_creacion_temp') IS NULL
DROP TABLE dbo.#prueba_creacion_temp
CREATE TABLE #prueba_creacion_temp(clave INT);
Pueden ver que se código no importa cuantas veces se ejecute nunca arrojará error.
Por último es interesante resaltar que en oracle no podemos realizar ninguno de estos controles debido a que el parser arrojará error la primera vez al hacer un DROP TABLE de una tabla que aún no existe. Por este motivo la forma de realizar lo mismo en Oracle sería:
-- Prueba 3: Controlo si existe la tabla en oracle, si es necesario la elimino y la vuelvo a crear
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE prueba_creacion';
EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE prueba_creacion(clave INT);
De esta forma se ejecutará siempre el DROP TABLE y arrojará error de sintaxis cuando no exista la tabla, pero el error será en el sql dinámico y no en todo el script, y estará capturado por el WHEN OTHERS, haciendo que sea transparente para el resto de la ejecución.
Para eliminar y crear una tabla desde cero en SQL Server es bastante conocido el método (de echo en la versión 2008 en adelante ya lo tenemos a un click del managment). El código es:
-- Prueba 1: Controlo si existe la tabla, si es necesario la elimino y la vuelvo a crear
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.prueba_creacion') AND type in (N'U'))
DROP TABLE dbo.prueba_creacion
CREATE TABLE prueba_creacion(clave INT);
De esta forma controlamos si la tabla existe, y en caso afirmativo la eliminamos. Luego podemos proceder a crearla sin problema.
Otra es la historia cuando la tabla es una tabla temporaria. Lo intuitivo sería ahcer lo mismo con el nombre de la tabla temporaria, pero esto arrojaría error. ¿porqué? porque la tabla temporaria se encuentra alojada en la tempdb. El código para poder realizar lo mismo con tablas temporarias sería:
-- Prueba 2: Controlo si existe la tabla temporaria, si es necesario la elimino y la vuelvo a crear
IF NOT OBJECT_ID('tempdb.dbo.#prueba_creacion_temp') IS NULL
DROP TABLE dbo.#prueba_creacion_temp
CREATE TABLE #prueba_creacion_temp(clave INT);
Pueden ver que se código no importa cuantas veces se ejecute nunca arrojará error.
Por último es interesante resaltar que en oracle no podemos realizar ninguno de estos controles debido a que el parser arrojará error la primera vez al hacer un DROP TABLE de una tabla que aún no existe. Por este motivo la forma de realizar lo mismo en Oracle sería:
-- Prueba 3: Controlo si existe la tabla en oracle, si es necesario la elimino y la vuelvo a crear
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE prueba_creacion';
EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE prueba_creacion(clave INT);
De esta forma se ejecutará siempre el DROP TABLE y arrojará error de sintaxis cuando no exista la tabla, pero el error será en el sql dinámico y no en todo el script, y estará capturado por el WHEN OTHERS, haciendo que sea transparente para el resto de la ejecución.
Etiquetas:
CREATE TABLE,
drop and create,
Eliminar tabla temporal,
Eliminar tabla temporaria,
Oracle,
SQL 2008
jueves, 6 de enero de 2011
SQL Server Denali - Sequence
Estuve probando el SQL 11 y una de las cosas que mas contento me puso es la incorporación de las SECUENCIAS tal como lo maneja Oracle. ¿Qué es esto? básicamente es un objeto en el motor que podemos crear y nos arroja números de forma ordenada según el criterio que hayamos definido. El principal objetivo es no depender de claves de tipo IDENTITY.
En este artículo hablaré de 3 cosas:
1) Como se utilizan las secuencias (sequence)
2) Comparar con Oracle el manejo de secuencias
3) Stress test comparando secuencias con campos identity
Un ejemplo de como utilizar este nuevo objeto sería el siguiente:
USE SQLDENA
GO
-- La forma tradicional de hacer un "autonumerico" es
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY IDENTITY,
dato1 VARCHAR(100)
)
GO
-- Pero ahora con las secuencias lo podemos hacer de la siguiente forma
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO
-- Creamos la tabla que SIN campo identity
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR(100)
)
GO
-- Creo la secuencia, se puede definir como se incrementa, etc
-- Como se puede ver son muy flexibles permitiendo configurar como se van a comportar
CREATE SEQUENCE dbo.NuevaSec
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20
;
GO
-- Obtengo unos valores de ejemplo
SELECT NEXT VALUE FOR dbo.NuevaSec
GO 4
-- Reinicio la secuencia para el ejemplo
ALTER SEQUENCE dbo.NuevaSec RESTART
GO
-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select next value for dbo.NuevaSec as nro, 'Valor1' as dato1
-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')
-- Que valor ingrese ultimo?
select current_value from sys.sequences where name = 'NuevaSec'
-- Esto me permite pasarle el valor a otra rutina que tenga que insertar en base a lo ingresado
-- La forma de realizar esto mismo en ORACLE hubiera sido:
/* PRUEBA ORACLE */
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR2(100)
)
CREATE SEQUENCE NuevaSec
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20;
-- Obtengo unos valores de ejemplo
SELECT NuevaSec.NEXTVAL FROM DUAL
-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select NuevaSec.NEXTVAL as nro, 'Valor1' as dato1 FROM DUAL
-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (NuevaSec.NEXTVAL, 'Valor1')
-- Que valor ingrese ultimo?
select NuevaSec.CURRVAL FROM DUAL
/* FIN PRUEBA ORACLE */
Comparando ambas formas creo que por suerte mantuvieron una sintaxis muy similar en lo que corresponde a la creación del objeto, y como oracle no tiene su hermoso CREATE OR REPLACE para las secuencias entonces es totalmente consistente. En lo que respecta a su uso diario, me parece mucho mas comoda y agradable la sintaxis de Oracle, principalmente la forma de ver el valor actual que es algo que no entiendo como no incorporaron. Por otra parte en Oracle es muy incomodo reiniciar una secuencia (es necesario restarle el valor actual) , mientras que SQL ha simplificado esta tarea para evitarnos dolores de cabeza.
Volviendo al SQL Server, realice un stress test con ambas tablas, una con identity y otra sin, utilizando las siguientes instrucciones
-- test1
INSERT INTO PruebaSecuencia (dato1)
VALUES ('Valor1')
GO 50000
-- test2
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')
GO 50000
Los resultados fueron parejos tardando un poco menos el ejemplo con la secuencia pero no de forma significativa. Es importante notar que la prueba no la realicé en un gran servidor sino en una pc virtual, y según varios test esta diferencia suele ser mayor.
En este artículo hablaré de 3 cosas:
1) Como se utilizan las secuencias (sequence)
2) Comparar con Oracle el manejo de secuencias
3) Stress test comparando secuencias con campos identity
Un ejemplo de como utilizar este nuevo objeto sería el siguiente:
USE SQLDENA
GO
-- La forma tradicional de hacer un "autonumerico" es
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY IDENTITY,
dato1 VARCHAR(100)
)
GO
-- Pero ahora con las secuencias lo podemos hacer de la siguiente forma
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO
-- Creamos la tabla que SIN campo identity
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR(100)
)
GO
-- Creo la secuencia, se puede definir como se incrementa, etc
-- Como se puede ver son muy flexibles permitiendo configurar como se van a comportar
CREATE SEQUENCE dbo.NuevaSec
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20
;
GO
-- Obtengo unos valores de ejemplo
SELECT NEXT VALUE FOR dbo.NuevaSec
GO 4
-- Reinicio la secuencia para el ejemplo
ALTER SEQUENCE dbo.NuevaSec RESTART
GO
-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select next value for dbo.NuevaSec as nro, 'Valor1' as dato1
-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')
-- Que valor ingrese ultimo?
select current_value from sys.sequences where name = 'NuevaSec'
-- Esto me permite pasarle el valor a otra rutina que tenga que insertar en base a lo ingresado
-- La forma de realizar esto mismo en ORACLE hubiera sido:
/* PRUEBA ORACLE */
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR2(100)
)
CREATE SEQUENCE NuevaSec
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20;
-- Obtengo unos valores de ejemplo
SELECT NuevaSec.NEXTVAL FROM DUAL
-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select NuevaSec.NEXTVAL as nro, 'Valor1' as dato1 FROM DUAL
-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (NuevaSec.NEXTVAL, 'Valor1')
-- Que valor ingrese ultimo?
select NuevaSec.CURRVAL FROM DUAL
/* FIN PRUEBA ORACLE */
Comparando ambas formas creo que por suerte mantuvieron una sintaxis muy similar en lo que corresponde a la creación del objeto, y como oracle no tiene su hermoso CREATE OR REPLACE para las secuencias entonces es totalmente consistente. En lo que respecta a su uso diario, me parece mucho mas comoda y agradable la sintaxis de Oracle, principalmente la forma de ver el valor actual que es algo que no entiendo como no incorporaron. Por otra parte en Oracle es muy incomodo reiniciar una secuencia (es necesario restarle el valor actual) , mientras que SQL ha simplificado esta tarea para evitarnos dolores de cabeza.
Volviendo al SQL Server, realice un stress test con ambas tablas, una con identity y otra sin, utilizando las siguientes instrucciones
-- test1
INSERT INTO PruebaSecuencia (dato1)
VALUES ('Valor1')
GO 50000
-- test2
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')
GO 50000
Los resultados fueron parejos tardando un poco menos el ejemplo con la secuencia pero no de forma significativa. Es importante notar que la prueba no la realicé en un gran servidor sino en una pc virtual, y según varios test esta diferencia suele ser mayor.
Etiquetas:
IDENTITY,
NEXT VALUE FOR,
NEXTVAL,
Oracle,
secuencia,
Sequence,
SQL,
SQL 11,
SQL Denali
lunes, 3 de enero de 2011
RML para SQL 2008 - Parte 1
A quien no le ha pasado de tener un sistema de base de datos, hacer unos cambios y no estar seguro si dichos cambios realmente mejoran la performance del proceso que se deseaba atacar? Este problema es muy común y por suerte contamos con unas series de herramientas que nos permiten solucionarlo. Este pack se llama RML y consta de varias herramientas, comentaré las mas importantes a mi parecer:
ReadTrace, OStress, y ORCA.
El ReadTrace nos permite hacer un análisis te las trazas de SQL y pasarlas a lo que se llaman archivos RML. Estos archivos a diferencia de la traza, se encuentran organizados por proceso, de esta forma se hace posible el simulacro.
El OStress toma los archivos RML y los lanza al SQL simulando el comportamiento original, pudiendo configurarle si lo debe lanzar una sola vez o muchas, cada que intervalo, y muchas cosas mas.
En esté primer artículo les dejo un overview de las pruebas realizadas.
Las herramientas son muy configurables y de un uso muy simple e intuitivo, y al ser por linea de comando es facil de documentar los pasos a seguir.
El readtrace viene con 3 archivos de ejemplo para una carga full, media o liviana. La herramienta esta pensada para hacer reportes y para trabajar con el Ostress, pero la verdad es que ninguno de estos tres archivos de configuración es adecuado para el OS. El archivo full consume muchisimo espacio, y el mediano no posee varios eventos que son requeridos por el OS.
El Ostress es un poco caprichoso con los eventos que requiere y las columnas que se deben haber capturado. Algunas (a mi ver) están de mas pero bue... sus mensajes no son del todo intuitivos visto que algunas veces les va a decir "falta tal evento y tal otro" y en realidad falta uno solo de los dos, pero se debe a que usa el mismo mensaje para determinado grupo de eventos (ejemplo los started y completed, si falta uno de los dos le va a decir que faltan los dos).
Me armé una traza personalizada que tiene menos eventos que la full pero los suficientes para que funcione el OStress, intentando minimizar las columnas tambien (hay algunos warnings que me da pero alcanza para que ejecute). Probé en un sistema con carga moderada y tardó un varias horas en llegar al giga de traza, pero en sistemas con mucho movimiento en menos de una hora ya rozaba el giga... Es una herramienta que hay que tener cuidado como se la usa visto la gran demanda de recursos (principalmente disco, compare los contadores y no hubo diferencias significativas en la performance del sistema al realizar la traza) así que me parece que lo adecuado es definir una franja horaria crítica o modelo y utilizarla sobre dicha franja. Claramente no es una traza para dejar corriendo durante toda la jornada.
Pro: Es muy facil una vez capturado hacer un simulacro para poder comparar la performance de un sistema antes y despues de hacer un cambio
Contra: Pese a poder configurar el paralelismo y los delay entre conexion, se dificulta hacer simulaciones de una ventana de tiempo prolongada o hacer la captura sin saber cuando será el momento crítico.
Les dejo el link para bajarlo: http://support.microsoft.com/kb/944837
ReadTrace, OStress, y ORCA.
El ReadTrace nos permite hacer un análisis te las trazas de SQL y pasarlas a lo que se llaman archivos RML. Estos archivos a diferencia de la traza, se encuentran organizados por proceso, de esta forma se hace posible el simulacro.
El OStress toma los archivos RML y los lanza al SQL simulando el comportamiento original, pudiendo configurarle si lo debe lanzar una sola vez o muchas, cada que intervalo, y muchas cosas mas.
En esté primer artículo les dejo un overview de las pruebas realizadas.
Las herramientas son muy configurables y de un uso muy simple e intuitivo, y al ser por linea de comando es facil de documentar los pasos a seguir.
El readtrace viene con 3 archivos de ejemplo para una carga full, media o liviana. La herramienta esta pensada para hacer reportes y para trabajar con el Ostress, pero la verdad es que ninguno de estos tres archivos de configuración es adecuado para el OS. El archivo full consume muchisimo espacio, y el mediano no posee varios eventos que son requeridos por el OS.
El Ostress es un poco caprichoso con los eventos que requiere y las columnas que se deben haber capturado. Algunas (a mi ver) están de mas pero bue... sus mensajes no son del todo intuitivos visto que algunas veces les va a decir "falta tal evento y tal otro" y en realidad falta uno solo de los dos, pero se debe a que usa el mismo mensaje para determinado grupo de eventos (ejemplo los started y completed, si falta uno de los dos le va a decir que faltan los dos).
Me armé una traza personalizada que tiene menos eventos que la full pero los suficientes para que funcione el OStress, intentando minimizar las columnas tambien (hay algunos warnings que me da pero alcanza para que ejecute). Probé en un sistema con carga moderada y tardó un varias horas en llegar al giga de traza, pero en sistemas con mucho movimiento en menos de una hora ya rozaba el giga... Es una herramienta que hay que tener cuidado como se la usa visto la gran demanda de recursos (principalmente disco, compare los contadores y no hubo diferencias significativas en la performance del sistema al realizar la traza) así que me parece que lo adecuado es definir una franja horaria crítica o modelo y utilizarla sobre dicha franja. Claramente no es una traza para dejar corriendo durante toda la jornada.
Pro: Es muy facil una vez capturado hacer un simulacro para poder comparar la performance de un sistema antes y despues de hacer un cambio
Contra: Pese a poder configurar el paralelismo y los delay entre conexion, se dificulta hacer simulaciones de una ventana de tiempo prolongada o hacer la captura sin saber cuando será el momento crítico.
Les dejo el link para bajarlo: http://support.microsoft.com/
Etiquetas:
microsoft,
performance,
RML,
simulacion,
SQL,
SQL 2008,
stress test,
test
viernes, 12 de noviembre de 2010
Controlando el AS
Miles de veces ejecutamos el bendito sp_who o sp_who2 entre otros, pero hablando con varias personas me encontré que desconocían como hacer lo mismo en Analisys Services.
Si queremos hacer un monitoreo del AS contamos con tablas parecidas a las DMV del SQL.
Para acceder a estas debemos conectarnos y generar una nueva consulta MDX, una vez ahí podemos escribir:
select * from $system.discover_commands
Y esto nos retorna la información equivalente al sp_who. Desde ahí podemos obtener el inputbuffer de cada comando que se encuentra en ejecución.
Esta es la que mas utilizo pero no es la única.
Para saber las conexiones existentes podemos utilizar:
select * from $system.discover_connections
Luego para analisis mas profundos contamos con otras como:
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat
Podemos obtener un listado completo de las tablas del sistema con la sentencia
SELECT TABLE_NAME
FROM $system.dbschema_tables
WHERE TABLE_SCHEMA = '$SYSTEM'
ORDER BY table_name"
ahí podremos ver que se encuentran divididas en 4 categorias:
DBSCHEMA_: Brinda información de la base
DISCOVER_: Brinda información para administración
DMSCHEMA_: Brinda información de datamining
MDSCHEMA_: Brinda información de la estructura de los cubos
En lo personal el que mas utilizo es el "discover", pero depende la tarea de cada uno esto puede variar.
Saludos!
Si queremos hacer un monitoreo del AS contamos con tablas parecidas a las DMV del SQL.
Para acceder a estas debemos conectarnos y generar una nueva consulta MDX, una vez ahí podemos escribir:
select * from $system.discover_commands
Y esto nos retorna la información equivalente al sp_who. Desde ahí podemos obtener el inputbuffer de cada comando que se encuentra en ejecución.
Esta es la que mas utilizo pero no es la única.
Para saber las conexiones existentes podemos utilizar:
select * from $system.discover_connections
Luego para analisis mas profundos contamos con otras como:
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat
Podemos obtener un listado completo de las tablas del sistema con la sentencia
SELECT TABLE_NAME
FROM $system.dbschema_tables
WHERE TABLE_SCHEMA = '$SYSTEM'
ORDER BY table_name"
ahí podremos ver que se encuentran divididas en 4 categorias:
DBSCHEMA_: Brinda información de la base
DISCOVER_: Brinda información para administración
DMSCHEMA_: Brinda información de datamining
MDSCHEMA_: Brinda información de la estructura de los cubos
En lo personal el que mas utilizo es el "discover", pero depende la tarea de cada uno esto puede variar.
Saludos!
Etiquetas:
$system.discover,
Analisys Services,
sp_who
martes, 5 de octubre de 2010
Socorro! no puedo frenar del CDC!!!
El CDC de SQL 2008 es en muchos aspectos una bendición, pero en algunos casos se puede volver una pesadilla si lo deseamos apagar. Una catarata de errores nos pueden ocurrir al intentar desactivar el CDC de una base o de una tabla, principalmente si no dejamos todo intacto como cuando lo creamos (ejemplo renombramos la tabla que tenia CDC).
En este caso vamos a ver que si borramos la tabla pasamos a estar peor, porque ahora no solo que no nos deja desactivar el CDC de la base sino que aparecerán algunos errores, así que lo que debemos hacer es erradicar todo rastro.
Primero debemos eliminar "dbo.systranschemas" y luego todas las referencias en el schema CDC.
Con una consulta nos alcanza para encontrarlas:
select * from sys.objects where schema_id=schema_id('cdc')
Esto nos devolvera cuales son las tablas, procedures y functions que se crearon. Debemos eliminar todos estos, con especial cuidado en las funciones que algunas tienen nombres raros como "fn_cdc_get_net_changes_ ..." y si no la ponen entre corchetes no se podrán eliminar.
Una vez borradas todas estas tablas ejecutamos el comando:
EXECUTE sys.sp_cdc_disable_db
y dejamos que el SQL se encargue del resto. Les recuerdo que esto es un último recurso, no deben hacerlo salvo que sea extremadamente necesario, nunca es recomentable borrar la información propia del SQL.
Saludos!
Andrés
En este caso vamos a ver que si borramos la tabla pasamos a estar peor, porque ahora no solo que no nos deja desactivar el CDC de la base sino que aparecerán algunos errores, así que lo que debemos hacer es erradicar todo rastro.
Primero debemos eliminar "dbo.systranschemas" y luego todas las referencias en el schema CDC.
Con una consulta nos alcanza para encontrarlas:
select * from sys.objects where schema_id=schema_id('cdc')
Esto nos devolvera cuales son las tablas, procedures y functions que se crearon. Debemos eliminar todos estos, con especial cuidado en las funciones que algunas tienen nombres raros como "fn_cdc_get_net_changes_ ..." y si no la ponen entre corchetes no se podrán eliminar.
Una vez borradas todas estas tablas ejecutamos el comando:
EXECUTE sys.sp_cdc_disable_db
y dejamos que el SQL se encargue del resto. Les recuerdo que esto es un último recurso, no deben hacerlo salvo que sea extremadamente necesario, nunca es recomentable borrar la información propia del SQL.
Saludos!
Andrés
jueves, 16 de septiembre de 2010
Role de ejecución para todos los stored procedures
Una situación bastante común es querer que un usuario (o grupo de usuarios) tenga permiso de ejecución para todos los stored procedures de una base. El SQL Server nos brinda los roles db_datareader, db_datawriter u otros, pero ninguno da solamente esos permisos. Para poder hacer esto con roles es necesario asignarlo al role db_owner, lo cual da muchos mas permisos de los deseados.
La única forma es dar los permisos de cada stored de forma individual, lo cual si tenemos muchos puede ser realmente molesto.
La solución que planteo es hacer un grupo al cual llamé db_executeall. Armé un script que recorre todas las bases y en todas crea dicho role.
El segundo paso es crear un cursor que recorra todos los stored procedures y asigne los permisos al role. El código estaría estructurado de la siguiente forma:
Cursor que recorre bases
begin
Crear role db_executeall si no existe
Cursor que recorre procedures
begin
Asignar permiso de ejecución al role
end
end
Esto nos dejaría en cada base un role nuevo que tiene todos los permisos de ejecución así solamente debemos asignarlo ahí al usuario. Una idea extra es agregar este script en un job diario, o por hora, para asegurarnos que nuestro role siempre esté actualizado con todos los permisos.
Si alguién necesita el código puede escribirme.
Saludos!
La única forma es dar los permisos de cada stored de forma individual, lo cual si tenemos muchos puede ser realmente molesto.
La solución que planteo es hacer un grupo al cual llamé db_executeall. Armé un script que recorre todas las bases y en todas crea dicho role.
El segundo paso es crear un cursor que recorra todos los stored procedures y asigne los permisos al role. El código estaría estructurado de la siguiente forma:
Cursor que recorre bases
begin
Crear role db_executeall si no existe
Cursor que recorre procedures
begin
Asignar permiso de ejecución al role
end
end
Esto nos dejaría en cada base un role nuevo que tiene todos los permisos de ejecución así solamente debemos asignarlo ahí al usuario. Una idea extra es agregar este script en un job diario, o por hora, para asegurarnos que nuestro role siempre esté actualizado con todos los permisos.
Si alguién necesita el código puede escribirme.
Saludos!
martes, 7 de septiembre de 2010
Defaul role como en Oracle
Una cosa que mucha gente que trabaja con SQL Server extraña de Oracle es lo conocidos como Default Roles.
¿En que consiste esto? Consiste en tener un conjunto de permisos al comenzar la sesion y en tiempo de ejecución poder acceder a otros permisos de forma explicita. Un ejemplo util sería darle a un usuario permisos de DATAREADER con la opción de ascender a DATAWRITER. De esta forma si la mayoria de sus tareas consisten en solo leer datos no podría modificar cosas por error salvo que explicitamente suba sus permisos.
Para implementar esto haremos lo siguiente. Para empezar crearemos en la master (o en alguna base de DBA) una tabla que tenga la siguiente estructura:
T_Permisos(Usuario SYSNAME, Role SYSNAME, Defualt TINYINT)
Con esta tabla indicaremos los roles que tiene permitidos un usuario. La primer columna sera el nombre del LOGIN, la segunda el role y la última si este role lo tiene desde el comienzo o lo debe explicitar.
El segundo paso es generar un logon trigger. Este trigger lo que hará será leer la tabla con un filtro en el where de Default=1 y Usuario=ORIGINAL_LOGIN() y hacer con SQL Dinamico una asignación de permisos a los roles indicados y quitandole cualquier otro que pudiera tener.
Con esto ya tenemos garantizado que al momento de conectarse tendrá un subconjunto de permisos.
El último paso es permitir la escalada de permisos, lo cual lo haremos mediante un stored procedure. El stored GrantRoles(Role SYSNAME) deberán tener permiso de ejecución todos los miembros de public. Este stored lo que hará es un select para controlar si el usuario logueado tiene en la tabla registrado el rol que solicita, en caso negativo no se hace nada y en caso afirmativo se asigna de forma dinámica el role. Es importante que este stored utilice la sentencia EXECUTE AS SELF por ejemplo y se haya creado con un usuario administrador. La idea es que el no tenga los permisos pero el stored si, y con la lógica adecuada se los asigne.
Una vez terminada su tarea, cuando se desconecte mantendrá los permisos, pero al establecer una nueva conexion los perderá.
Es interesante el tema de conexiones simultaneas, ahí se pueden analizar varias variantes como controlar en la sys.dm_exec_sessions para ver de solo sacar los permisos si no hay otra conexion viva del mismo usuario.
Saludos!!
¿En que consiste esto? Consiste en tener un conjunto de permisos al comenzar la sesion y en tiempo de ejecución poder acceder a otros permisos de forma explicita. Un ejemplo util sería darle a un usuario permisos de DATAREADER con la opción de ascender a DATAWRITER. De esta forma si la mayoria de sus tareas consisten en solo leer datos no podría modificar cosas por error salvo que explicitamente suba sus permisos.
Para implementar esto haremos lo siguiente. Para empezar crearemos en la master (o en alguna base de DBA) una tabla que tenga la siguiente estructura:
T_Permisos(Usuario SYSNAME, Role SYSNAME, Defualt TINYINT)
Con esta tabla indicaremos los roles que tiene permitidos un usuario. La primer columna sera el nombre del LOGIN, la segunda el role y la última si este role lo tiene desde el comienzo o lo debe explicitar.
El segundo paso es generar un logon trigger. Este trigger lo que hará será leer la tabla con un filtro en el where de Default=1 y Usuario=ORIGINAL_LOGIN() y hacer con SQL Dinamico una asignación de permisos a los roles indicados y quitandole cualquier otro que pudiera tener.
Con esto ya tenemos garantizado que al momento de conectarse tendrá un subconjunto de permisos.
El último paso es permitir la escalada de permisos, lo cual lo haremos mediante un stored procedure. El stored GrantRoles(Role SYSNAME) deberán tener permiso de ejecución todos los miembros de public. Este stored lo que hará es un select para controlar si el usuario logueado tiene en la tabla registrado el rol que solicita, en caso negativo no se hace nada y en caso afirmativo se asigna de forma dinámica el role. Es importante que este stored utilice la sentencia EXECUTE AS SELF por ejemplo y se haya creado con un usuario administrador. La idea es que el no tenga los permisos pero el stored si, y con la lógica adecuada se los asigne.
Una vez terminada su tarea, cuando se desconecte mantendrá los permisos, pero al establecer una nueva conexion los perderá.
Es interesante el tema de conexiones simultaneas, ahí se pueden analizar varias variantes como controlar en la sys.dm_exec_sessions para ver de solo sacar los permisos si no hay otra conexion viva del mismo usuario.
Saludos!!
Etiquetas:
dm_exec_sessions,
grant,
roles,
SQL 2008
jueves, 26 de agosto de 2010
Restore
Este artículo es corto pero muchas veces me lo preguntaron y yo mismo lo he necesitado asi que me parece un buen dato para tener siempre a mano. Cuantas veces uno tiene que hacer un restore de una base de varios gigas y no tiene idea de cuanto va a tardar, y del otro lado del teléfono tenemos al usuario reclamando que quiere su base online hace una hora (aunque el pedido se haya realizado hace 15 minutos, siempre es así el usuario). Para esto podemos utilizar una vista del sistema, DM_EXEC_REQUESTS (http://msdn.microsoft.com/es-es/library/ms177648.aspx). Una consulta sencilla que me permitirá saber el tiempo que resta para un restore es la siguiente:
SELECT CONVERT(NVARCHAR(3), CAST(percent_complete AS INTEGER)) + '%' Porcentaje
, r.estimated_completion_time/(1000*60) MinutosRestantes
FROM SYS.DM_EXEC_REQUESTS r
WHERE percent_complete > 0
Esta consulta nso dirá el tiempo esperado para finalizar el restore pero tiene un problema, en caso de estar realizando varios restore al mismo tiempo no nos indica que a que base corresponde cada tiempo estimado. Esta información la podemos enriquecer haciendo un join con la información obtenida en el SP_WHO2 y así tener una consulta mucho mas completa que nos muestre también cual es la base sobre la cual operamos.
CREATE TABLE #sp_who2
(SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2
EXEC sp_who2
SELECT w.DBName
, CONVERT(NVARCHAR(3), CAST(percent_complete AS INTEGER)) + '%' Porcentaje
, r.estimated_completion_time/(1000*60) MinutosRestantes
FROM #sp_who2 w, SYS.DM_EXEC_REQUESTS r
WHERE r.SESSION_ID = w.SPID
AND w.Command IN ('RESTORE DATABASE')
AND r.percent_complete>0
AND w.DBName<>'master'
ORDER BY 1
DROP TABLE #sp_who2
Saludos!
SELECT CONVERT(NVARCHAR(3), CAST(percent_complete AS INTEGER)) + '%' Porcentaje
, r.estimated_completion_time/(1000*60) MinutosRestantes
FROM SYS.DM_EXEC_REQUESTS r
WHERE percent_complete > 0
Esta consulta nso dirá el tiempo esperado para finalizar el restore pero tiene un problema, en caso de estar realizando varios restore al mismo tiempo no nos indica que a que base corresponde cada tiempo estimado. Esta información la podemos enriquecer haciendo un join con la información obtenida en el SP_WHO2 y así tener una consulta mucho mas completa que nos muestre también cual es la base sobre la cual operamos.
CREATE TABLE #sp_who2
(SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2
EXEC sp_who2
SELECT w.DBName
, CONVERT(NVARCHAR(3), CAST(percent_complete AS INTEGER)) + '%' Porcentaje
, r.estimated_completion_time/(1000*60) MinutosRestantes
FROM #sp_who2 w, SYS.DM_EXEC_REQUESTS r
WHERE r.SESSION_ID = w.SPID
AND w.Command IN ('RESTORE DATABASE')
AND r.percent_complete>0
AND w.DBName<>'master'
ORDER BY 1
DROP TABLE #sp_who2
Saludos!
viernes, 13 de agosto de 2010
Todo sobre logins (parte 2)
Ya hablamos de como mantener bien nuestros logins, ahora lo que falta es controlar que todo funcione como queremos. La auditoria es una de las partes mas importantes a la hora de administrar la seguridad.
Hay varias cosas que podemos auditar pero principalmente quisiera hacer foco en dos:
1) Auditoria SQL
2) Logon triggers
En la auditoria podemos definir los eventos que queremos que se controlen. Esto en la versión 2008 se ha echo muy sencillo, con solo un par de clicks podemos hacerlo. Si en cambio nuestra base es SQL 2005 deberemos trabajar un poco mas y habilitar el service broker (ENABLE_BROKER) y tenemos varios eventos que esta bueno monitorear y loguear en una tabla:
AUDIT_LOGIN_CHANGE_PROPERTY_EVENT
AUDIT_LOGIN_FAILED
AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT
AUDIT_ADDLOGIN_EVENT
Estos eventos nos permitiran monitorear si se han creado nuevos logins o se le asignaron permisos a los ya existentes. Seguro se estarán preguntando "y no voy a auditar si se borraron loggins?" el evento cuando se borra un login es "AUDIT_ADDLOGIN_EVENT" pero en el xml del evento el subclass es 2 en lugar de 1.
Lo otro que podemos hacer interesante es definir logon tringgers y aquí podemos dejar volar nuestra imaginación. Entre las ideas que se pueden implementar estaría:
- Definir que un login pueda conectarse solamente desde un host en particular
- Definir que un login pueda tener N ocurrencias en el día o simultaneas
- Guardar en una tabla los horarios de login del usuario
- No permitir que dos usuarios se conecten de forma simultanea
y esas son solo algunas ideas, cada uno puede adaptarlas según las necesidades de su empresa
Hay varias cosas que podemos auditar pero principalmente quisiera hacer foco en dos:
1) Auditoria SQL
2) Logon triggers
En la auditoria podemos definir los eventos que queremos que se controlen. Esto en la versión 2008 se ha echo muy sencillo, con solo un par de clicks podemos hacerlo. Si en cambio nuestra base es SQL 2005 deberemos trabajar un poco mas y habilitar el service broker (ENABLE_BROKER) y tenemos varios eventos que esta bueno monitorear y loguear en una tabla:
AUDIT_LOGIN_CHANGE_PROPERTY_EVENT
AUDIT_LOGIN_FAILED
AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT
AUDIT_ADDLOGIN_EVENT
Estos eventos nos permitiran monitorear si se han creado nuevos logins o se le asignaron permisos a los ya existentes. Seguro se estarán preguntando "y no voy a auditar si se borraron loggins?" el evento cuando se borra un login es "AUDIT_ADDLOGIN_EVENT" pero en el xml del evento el subclass es 2 en lugar de 1.
Lo otro que podemos hacer interesante es definir logon tringgers y aquí podemos dejar volar nuestra imaginación. Entre las ideas que se pueden implementar estaría:
- Definir que un login pueda conectarse solamente desde un host en particular
- Definir que un login pueda tener N ocurrencias en el día o simultaneas
- Guardar en una tabla los horarios de login del usuario
- No permitir que dos usuarios se conecten de forma simultanea
y esas son solo algunas ideas, cada uno puede adaptarlas según las necesidades de su empresa
Suscribirse a:
Entradas (Atom)