Re: SQL Server Performance Issue

Also we test other heavy queries and they work OK in both machines, it
seems that the problem is when the query use this specific UDF.

On 15 feb, 17:43, Andres Rormoser <arormo...@xxxxxxxxx> wrote:
Yes, the database are exactly the same (it's a restore from the
previous day) .

Exactly the same Execution Plans. I already update statics in the
database, also measure Memory/CPU/Virtual Memory/Disk I/O.

But can determine where the problem is. Now i'm installing a RAID 1 in
my development machine, to test if that is the problem, but i don't
think so.

On 15 feb, 16:54, "Jack Vamvas" <DEL_TO_RE...@xxxxxxx> wrote:



Firstly , could you confirm the 2 dbs are similar in terms of
indices,statistics and execution plans. For example, when your run the UDF
are they similar exceution plans?

--

Jack Vamvas
___________________________________
Search  IT jobs from multiple sources-  http://www.ITjobfeed.com

"Andres Rormoser" <arormo...@xxxxxxxxx> wrote in message

news:6d10d455-0c58-4aec-a928-1cf3c31b0bc0@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCosto (@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(@ccs_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_CentroCosto(@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(@ccs_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_CentroCosto(@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_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULL
   PRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'
ELSE
   PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'
go- Ocultar texto de la cita -

- Mostrar texto de la cita -- Ocultar texto de la cita -

- Mostrar texto de la cita -

.

Relevant Pages

  • Re: Single-Threading / Performance issues
    ... Apparently not since at least 3 physical disks are required for RAID-5. ... This is normal but the percent disk utilization metric isn't much use ... might want to set SQL Server 'max server memory' if you have other apps ... > From Query Analyser, I run my import proc - which first copies data ...
    (microsoft.public.sqlserver.server)
  • Re: Query Using 20 Parallel Sessions
    ... Windows Server 2003 ... We have a query going against a 100 million plus row partitioned table using ... it only takes about 2 minutes and uses about 20 parallel sessions. ... written to disk before it reads... ...
    (comp.databases.oracle.server)
  • Re: SQL: Running Sum?
    ... has many advantages over loading up the server. ... but later the client machine software will ... when the query result window was changed so I dumped that idea. ... One interesting result is that a UDF ...
    (comp.databases.ms-access)
  • RE: check disk spack usage on NT 4
    ... > On NT 4 server, is there a way to write a script to query a particular ... > disk volume to know how much space each user has taken? ...
    (microsoft.public.scripting.vbscript)
  • check disk spack usage on NT 4
    ... On NT 4 server, is there a way to write a script to query a particular ... disk volume to know how much space each user has taken? ...
    (microsoft.public.scripting.vbscript)