Post reply

Performance problems

  • April 17, 2009 at 6:01 am

    #199586

    Hi,

    I have very different response times on two different SQL server 2005 SP2 instances running on different servers (Dell power Edges 6850). On instance B, queries are taking 2 or 3 more time than in instance A

    Both physical server have the same hardware configuration and there is no charge on it.

    Both SQL server instances have the default (out of the box) configuration.

    For responses time I'm using the same database on both instances.

    Does anybody have an idea on what the problem can be? Could this be a Windows configuration problem?

  • April 17, 2009 at 6:31 am

    #979580

    Same data volume?

    Same indexes?

    Same statistics?

    Same execution plan?

    Same number of concurrent users?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 17, 2009 at 6:49 am

    #979604

    I have have restored the same database backup on both instances so both databases have same indexes, same statistics, same amount of data and same number of users (Actually I'm the only user as for the moment this have been installed for test purposes)

  • April 17, 2009 at 6:51 am

    #979606

    stineo (4/17/2009)

    I have have restored the same database backup on both instances so both databases have same indexes, same statistics, same amount of data and same number of users (Actually I'm the only user as for the moment this have been installed for test purposes)

    Have a look at the execution plans for the same query on both servers to see if there are any differences, this will give you a good starting point to identify any issues.

  • April 17, 2009 at 6:58 am

    #979615

    What about the parameters?

    Does one of the servers have a different parallelism threshold than the other? How about the parallelism setting? ANSI connection settings? Um, memory allocations, any other kinds of server settings that are different? Is maintenance running on one server or the other, but not on the other?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 17, 2009 at 7:03 am

    #979624

    Hi all,

    I have give a lookup for a query and execution plnas are the same on both instances.

    Concerning the parameters for the sql server instance both are the same. Parellelims and memory allocation are configured the same way on both servers (On the sql server side).

    Is the a way of configuring memory/parallelims at windows side?

  • April 17, 2009 at 7:16 am

    #979638

    Parallelism, no. Memory yes.

    There has to be a difference somewhere. Are they on the same service pack and hot fix #?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 17, 2009 at 7:30 am

    #979659

    Both instances are at 9.0.3310 hotfix.

    I agree that there must be a difference somewhere. The things is that I've been trying to find it that last 3/4 days and no luck as of now.

    Could you please tell me where can I configure the memory management at the windows level?

  • April 17, 2009 at 7:31 am

    #979660

    Are they both running on the Physical Server, or on a Virtual Server?

  • April 17, 2009 at 7:36 am

    #979663

    Both on the physical server.

    No hyperthreading on the boxes

  • April 17, 2009 at 8:04 am

    #979684

    Here is further information :

    I'm executing this query on both instances :

    SET STATISTICS IO ON

    GO

    SET STATISTICS TIME ON

    GO

    Select fieldID, f.fieldName, FieldValue from field f

    inner join dbo.getOppTableDataPivoted(3, 2) as t on t.fieldName = f.fieldName

    GO

    SET STATISTICS IO OFF

    GO

    SET STATISTICS TIME OFF

    GO

    Execution plans are the same on both instances

    Here are the results :

    For instance A :

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    (46 row(s) affected)

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'field'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#14270015'. Scan count 1, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1703 ms, elapsed time = 1751 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    For Instance B :

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    (46 row(s) affected)

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'field'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#3D94C0AA'. Scan count 1, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 3109 ms, elapsed time = 3131 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 1 ms.

  • April 17, 2009 at 8:38 am

    #979717

    I'd suggest looking into the waits & queues. Use this white paper from MS.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 17, 2009 at 8:48 am

    #979725

    I've experienced similar - i think this is to do with your UDF

    try creating a temp table (for test purposes) and insert into the test table the data returned from

    dbo.getOppTableDataPivoted(3, 2)

    then re-run the query joining against the temp table rather than the UDF

    MVDBA

  • April 17, 2009 at 8:49 am

    #979726

    also before you run these tests make sure you clear the buffer cache and make sure they are both compiled - otherwise you will skew your results

    MVDBA

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply