problemas con el sp4 del SQL

12/09/2005 - 16:27 por fgoico | Informe spam
Estimados señores,

Hemos detectado algunos problemas en nuestros servidores de desarrollo
despues instalar el Service Pack 4 del MSSQL. Concretamente, hemos detectado
un notable deterioro en el rendimiento del servidor, concretamente en el
consumo de CPU y acceso a disco cuando se ejecutan algunas querys. Os voy a
poner un ejemplo, no tengo muy claro cual es la mejor manera de mostrar esto,
pero espero que lo que os adjunto a continuacion os sirva de algo, y sino
pregúntarme lo que querais.

SERVIDOR1:
2 CPU PIV XEON 3,2GHZ 2MBL2
4GB RAM
un raid 1 (dos discos de 72GB SCSI U320 de 15K rpm)
un raid 10 (cuatro discos de 72GB SCSI U320 de 15K rpm)
WINDOWS 2000 SERVER CON SP4 Y ULTIMAS ACTUALIZACIONES
MICROSOFT SQL SERVER 2000 STANDARD

SERVIDOR2:
2 CPU PIV XEON 3,2GHZ 2MBL2
4GB RAM
un raid 1 (dos discos de 72GB SCSI U320 de 15K rpm)
un raid 1 (dos discos de 72GB SCSI U320 de 15K rpm)
un raid 10 (cuatro discos de 72GB SCSI U320 de 15K rpm)
WINDOWS 2003 SERVER STD CON SP2 Y ULTIMAS ACTUALIZACIONES
MICROSOFT SQL SERVER 2000 STANDARD

SERVIDOR3:
2 CPU PIV XEON 2,8GHZ 2MBL2
6GB RAM
un raid 1 (dos discos de 72GB SCSI U320 de 15K rpm)
un raid 1 (dos discos de 72GB SCSI U320 de 15K rpm)
un raid 10 (cuatro discos de 72GB SCSI U320 de 15K rpm)
WINDOWS 2003 SERVER ENTERPRISE CON SP2 Y ULTIMAS ACTUALIZACIONES
MICROSOFT SQL SERVER 2000 STANDARD

Bien, una de las querys en las que hemos detectado este problema es:

declare @IdEmpresas as decimal
declare @Login as varchar(30)
declare @IdAppModulos as varchar(5)
declare @IdAppContenedores as varchar(50)

set @IdEmpresas = 1
set @Login = 'betas'
set @IdAppModulos = 'EM'
set @IdAppContenedores = 'frmEmpleados'

SELECT AppPermisosEmpresas.IdAppObjetos AS Objeto,
AppPermisosEmpresas.IdAppContenedores AS Contenedor,
AppPermisosEmpresas.IdAppModulos AS Modulo,
IsNull(AppPermisosEmpresas.Visible,0) as Visible,
IsNull(AppPermisosEmpresas.Enabled,0) AS
Enabled,IsNull(AppPermisosEmpresas.NoDisponible,0) AS NoDisponible
FROM AppPermisosEmpresas INNER JOIN AppObjetos ON
AppPermisosEmpresas.IdAppModulos=AppObjetos.IdAppModulos AND
AppPermisosEmpresas.IdAppContenedoresAppObjetos.Contenedor AND AppPermisosEmpresas.IdAppObjetos=AppObjetos.Objeto
WHERE ISNULL(AppObjetos.EsControlVB, 1)=1 AND AppPermisosEmpresas.Login =
@Login AND
AppPermisosEmpresas.IdAppContenedores = @IdAppContenedores AND
AppPermisosEmpresas.IdEmpresas = @IdEmpresas AND
AppPermisosEmpresas.IdAppModulos = @IdAppModulos --And
ISNULL(AppObjetos.NoDisponible, 0)= 0
GROUP BY AppPermisosEmpresas.IdAppObjetos,
AppPermisosEmpresas.IdAppContenedores, AppPermisosEmpresas.IdAppModulos,
AppPermisosEmpresas.Enabled, AppPermisosEmpresas.Visible,
IsNull(AppPermisosEmpresas.NoDisponible,0)
UNION
SELECT dbo.AppObjetos.Objeto AS Objeto, dbo.AppObjetos.Contenedor AS
Contenedor, dbo.AppObjetos.IdAppModulos AS Modulo,
ISNULL(dbo.AppObjetos.DefVisible, 0) AS Visible,
ISNULL(dbo.AppObjetos.DefEnabled, 0) AS Enabled,
ISNULL(dbo.AppObjetos.NoDisponible, 0)
AS NoDisponible
FROM dbo.AppObjetos LEFT OUTER JOIN
dbo.AppPermisosEmpresas ON dbo.AppObjetos.IdAppModulos
= dbo.AppPermisosEmpresas.IdAppModulos
WHERE (dbo.AppPermisosEmpresas.IdEmpresas = @IdEmpresas) AND
(dbo.AppPermisosEmpresas.Login = @Login) AND
(ISNULL(dbo.AppObjetos.EsControlVB, 1) = 1) AND
(dbo.AppPermisosEmpresas.Visible = 1) AND
(dbo.AppObjetos.IdAppModulos = @IdAppModulos)
And (dbo.AppObjetos.Contenedor = @IdAppContenedores)
AND (dbo.AppObjetos.Objeto NOT IN
(SELECT AppPermisosEmpresas.IdAppObjetos
FROM AppPermisosEmpresas INNER JOIN
AppObjetos ON AppPermisosEmpresas.IdAppModulos =
AppObjetos.IdAppModulos AND
AppPermisosEmpresas.IdAppContenedores =
AppObjetos.Contenedor AND
AppPermisosEmpresas.IdAppObjetos =
AppObjetos.Objeto
WHERE ISNULL(AppObjetos.EsControlVB, 1) = 1 AND
AppPermisosEmpresas.Login = @Login AND
AppPermisosEmpresas.IdAppContenedores =
@IdAppContenedores AND AppPermisosEmpresas.IdEmpresas = @IdEmpresas AND
AppPermisosEmpresas.IdAppModulos =
@IdAppModulos
GROUP BY AppPermisosEmpresas.IdAppObjetos))

UNION
SELECT AppObjetos.Objeto AS Objeto, AppObjetos.Contenedor AS Contenedor,
AppObjetos.IdAppModulos AS Modulo, 0 as Visible, 0 AS Enabled,
IsNull(AppObjetos.NoDisponible,0)
AS NoDisponible From AppObjetos
Where AppObjetos.IdAppModulos = @IdAppModulos And
ISNULL(AppObjetos.EsControlVB,1) = 1
And AppObjetos.Contenedor = @IdAppContenedores And AppObjetos.Objeto Not
In (
SELECT AppPermisosEmpresas.IdAppObjetos AS Objeto
FROM AppPermisosEmpresas LEFT OUTER JOIN AppObjetos ON
AppPermisosEmpresas.IdAppModulos=AppObjetos.IdAppModulos AND
AppPermisosEmpresas.IdAppContenedoresAppObjetos.Contenedor AND AppPermisosEmpresas.IdAppObjetos=AppObjetos.Objeto
WHERE ISNULL(AppObjetos.EsControlVB, 1)=1 AND AppPermisosEmpresas.Login =
@Login AND
AppPermisosEmpresas.IdAppContenedores = @IdAppContenedores AND
AppPermisosEmpresas.IdEmpresas = @IdEmpresas AND
AppPermisosEmpresas.IdAppModulos = @IdAppModulos --And
ISNULL(AppObjetos.NoDisponible, 0)= 0
GROUP BY AppPermisosEmpresas.IdAppObjetos,
AppPermisosEmpresas.IdAppContenedores, AppPermisosEmpresas.IdAppModulos,
AppPermisosEmpresas.Enabled, AppPermisosEmpresas.Visible,
IsNull(AppPermisosEmpresas.NoDisponible,0)
UNION
SELECT dbo.AppObjetos.Objeto AS Objeto
FROM dbo.AppObjetos LEFT OUTER JOIN
dbo.AppPermisosEmpresas ON dbo.AppObjetos.IdAppModulos
= dbo.AppPermisosEmpresas.IdAppModulos
WHERE (dbo.AppPermisosEmpresas.IdEmpresas = @IdEmpresas) AND
(dbo.AppPermisosEmpresas.Login = @Login) AND
(ISNULL(dbo.AppObjetos.EsControlVB, 1) = 1) AND
(dbo.AppPermisosEmpresas.Visible = 1) AND
(dbo.AppObjetos.IdAppModulos = @IdAppModulos)
AND (dbo.AppObjetos.Objeto NOT IN
(SELECT AppPermisosEmpresas.IdAppObjetos
FROM AppPermisosEmpresas LEFT OUTER JOIN
AppObjetos ON
AppPermisosEmpresas.IdAppModulos = AppObjetos.IdAppModulos AND
AppPermisosEmpresas.IdAppContenedores =
AppObjetos.Contenedor AND
AppPermisosEmpresas.IdAppObjetos =
AppObjetos.Objeto
WHERE ISNULL(AppObjetos.EsControlVB, 1) = 1 AND
AppPermisosEmpresas.Login = @Login AND
AppPermisosEmpresas.IdAppContenedores =
@IdAppContenedores AND AppPermisosEmpresas.IdEmpresas = @IdEmpresas AND
AppPermisosEmpresas.IdAppModulos =
@IdAppModulos
GROUP BY AppPermisosEmpresas.IdAppObjetos))
)
GO

Las tablas son:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[AppObjetos]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AppObjetos]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[AppPermisosEmpresas]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[AppPermisosEmpresas]
GO

CREATE TABLE [dbo].[AppObjetos] (
[IdAppModulos] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Contenedor] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Objeto] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Descripcion] [varchar] (200) COLLATE Modern_Spanish_CI_AS NULL ,
[DefVisible] [bit] NULL ,
[DefEnabled] [bit] NULL ,
[FechaMod] [datetime] NULL ,
[Objeto_Padre] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[Contenedor_Padre] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[IdAppModulos_Padre] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
[EsControlVB] [bit] NULL ,
[NoDisponible] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AppPermisosEmpresas] (
[IdEmpresas] [decimal](9, 0) NOT NULL ,
[IdAppModulos] [varchar] (5) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[IdAppContenedores] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[IdAppObjetos] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Login] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[Visible] [bit] NOT NULL ,
[Enabled] [bit] NOT NULL ,
[Plantilla] [bit] NOT NULL ,
[NoDisponible] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AppObjetos] WITH NOCHECK ADD
CONSTRAINT [PKAppObjetos] PRIMARY KEY CLUSTERED
(
[IdAppModulos],
[Contenedor],
[Objeto]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[AppPermisosEmpresas] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[IdEmpresas],
[IdAppModulos],
[IdAppContenedores],
[IdAppObjetos],
[Login]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[AppObjetos] ADD
CONSTRAINT [DF_AppObjetos_EsControlVB] DEFAULT (1) FOR [EsControlVB]
GO

CREATE INDEX [IdAppModulos] ON [dbo].[AppObjetos]([IdAppModulos]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IdObjetos] ON [dbo].[AppObjetos]([Contenedor]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[AppPermisosEmpresas] ADD
CONSTRAINT [DF___TMP_DBTo__IdEmp__0B69FEE7] DEFAULT (0) FOR [IdEmpresas],
CONSTRAINT [DF___TMP_DBTo__IdApp__0C5E2320] DEFAULT ('') FOR [IdAppModulos],
CONSTRAINT [DF___TMP_DBTo__IdApp__0D524759] DEFAULT ('') FOR
[IdAppContenedores],
CONSTRAINT [DF___TMP_DBTo__IdApp__0E466B92] DEFAULT ('') FOR [IdAppObjetos],
CONSTRAINT [DF___TMP_DBTo__Login__0F3A8FCB] DEFAULT ('') FOR [Login],
CONSTRAINT [DF___TMP_DBTo__Visib__102EB404] DEFAULT (0) FOR [Visible],
CONSTRAINT [DF___TMP_DBTo__Enabl__1122D83D] DEFAULT (0) FOR [Enabled],
CONSTRAINT [DF___TMP_DBTo__Plant__1216FC76] DEFAULT (0) FOR [Plantilla]
GO

AppPermisosEmpresas tiene 507498 registros
AppObjetos tiene 1124 registros

El problema está en que si ejecutas esta query con el SP3a del SQL los
resultados que muestra el analizador del SQL son estos:

RPC COMPLETED
CPU : 79
READS : 2592
WRITES : 0
DURATION: 78

y despues de instalar el SP4 , la misma consulta devuelve estos resultados

RPC COMPLETED
CPU : 796
READS : 47167
WRITES : 0
DURATION : 1890

Como podeis comprobar el incremento de los tiempos y de las lecturas son
notables. Repito, el servidor es el mismo, la base de datos la misma, el
usuario el mismo, SOLO CAMBIA EL SERVICE PACK. Las pruebas se hicieron en los
servidores que se detallo al principio del mensaje y en los tres servidores
ocurre exactamente lo mismo. Por supuesto, las consultas las he ejecutado
varias veces y siempre con resultados muy parejos...

Preguntas similare

Leer las respuestas

#6 fernando goicoechea
13/09/2005 - 16:25 | Informe spam
Alejandro,

Hombre no espero que analizes los planes de ejecución, sino que te hagas una
idea de lo que pasa.
Aunque tienens razon en que los servidores están sobredimensionados para
trabajar con la versión standard del SQL, no se que tendrá que ver
con la caida del rendimiento al instalar el SP4, todo fue por instalar la
versión standard pero bueno, dispongo de la versión
enterprise del MSDN, si quieres quedarte mas tranquilo, instalo el Windows
2K3 enterprise y el SQL 2000 Enterprise y vuelvo hacer las mismas pruebas.

En lo que me comentas del "index seek" en la ejecucion del sp3 y del "index
scan" en la ejecucion del SP4 si nos ha
llamado la antencion, sobre todo por que el coste del "index seek" en el
primer plan son del 0% y en el segundo se disparan hasta el
15% y de los "index scan" hasta el 25%. Me gustaria enviarte dos imagenes
para que lo vieras.

un saludo,
Fernando


"Alejandro Mesa" escribió en el
mensaje news:
Mostrar la cita
#7 Alejandro Mesa
13/09/2005 - 17:35 | Informe spam
Fernando,

Hicistes la prueba que te dije con MAXDOP?

Mostrar la cita
Puedes escribirme a


AMB

"fernando goicoechea" wrote:

Mostrar la cita
#8 fernando goicoechea
14/09/2005 - 15:36 | Informe spam
Alejandro,

Ya te he enviado a tu email, las imagenes de los planes de ejecución. En
cuanto al option(MAXDOP 1) lo he probado tanto dentro de la query como con
el sp_configure. Los resultados son los mismos... bueno las diferencias son
las mismas, de hecho los dos planes de ejecucion que te he enviado a tu
email son con el option(MAXDOP 1).

un saludo


"Alejandro Mesa" escribió en el
mensaje news:
Mostrar la cita
#9 Carlo Sorrel
14/09/2005 - 17:30 | Informe spam
Fernando, por lo que pude entender del link que te envie, el Service Pack 4
trae activado un trace al comportamiento de los Discos Físicos, esto para
prevenir los "atascamientos" que se puedan producir por esto (excesivas
peticiones que provocan que los discos colapsen). Lo que podrias probar es
desactivarlo, con lo cual es probable que te mejore algo el rendimiento,
esto por lo que lei se realiza con el comando "dbcc traceoff(830, -1)".
Tambien podrias probar, como bien dijo Alejandro, es restringir el uso de
paralelismo en las consultas, en la pestaña procesador de tu server,
"paralelismo", restrinjelo a un sólo procesador.
Al igual que tú, pienso que este Service pack algo extraño tiene, como ya te
comente, en mis ambientes de pruebas y testing no tuve problemas, pero al
pasarlo a Producción, uno de mis Server (Tengo un Cluster Windows 2003, con
dos Instancias de SQL, una predeterminada y otra por Nombre, una corriendo
en cada nodo), la Intancia por Nombre, se degrado el rendimiento en una
forma horrible, procesos que duraban 2 minutos se aumentaron a 40, por lo
que tuve que volver atras, lo extraño es que no en todos mis Server e tenido
problemas.
Saludos.

Atte.,
Carlo Sorrel

"fernando goicoechea" escribió en el mensaje
news:
Mostrar la cita
Ads by Google
Search Busqueda sugerida