I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.
I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.
It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??
If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.
Please help me understand this to solve this problem.
Thanks, AR
SET QUOTED_IDENTIFI ER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCo sto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT
IF @ccs_ids = 'null'
RETURN
SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@c cs_ids,',')
IF @cantidad = 1
BEGIN
SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
IF @cantidad = 0
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
RETURN
END
ELSE
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
a.ccs_id
FROM
dbo.fn_CentroCo sto(@ccs_id) As a
FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
END
ELSE
BEGIN
DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@c cs_ids,',') AS a )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
ccs_id
FROM
dbo.fn_CentroCo sto(@ccs_id)
WHERE
ccs_id not in (select ccs_id from
@Ret)
FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFI ER OFF
go
IF OBJECT_ID('dbo. fn_CentroCosto' ) IS NOT NULL
PRINT '<<< CREATED FUNCTION dbo.fn_CentroCo sto >>>'
ELSE
PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCo sto >>>'
go
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.
I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.
It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??
If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.
Please help me understand this to solve this problem.
Thanks, AR
SET QUOTED_IDENTIFI ER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCo sto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT
IF @ccs_ids = 'null'
RETURN
SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@c cs_ids,',')
IF @cantidad = 1
BEGIN
SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
IF @cantidad = 0
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
RETURN
END
ELSE
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
a.ccs_id
FROM
dbo.fn_CentroCo sto(@ccs_id) As a
FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
END
ELSE
BEGIN
DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@c cs_ids,',') AS a )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
ccs_id
FROM
dbo.fn_CentroCo sto(@ccs_id)
WHERE
ccs_id not in (select ccs_id from
@Ret)
FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFI ER OFF
go
IF OBJECT_ID('dbo. fn_CentroCosto' ) IS NOT NULL
PRINT '<<< CREATED FUNCTION dbo.fn_CentroCo sto >>>'
ELSE
PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCo sto >>>'
go
Comment