Slow database performance on a higher spec server

  • We have an interesting problem.

    We have a database that's around 2gig in size, runs great on our development server and other client sites, but on this one particular site, it runs very, very slow...to the point of users getting timeouts.

    We've been doing some basic comparions using one stored procedure that takes 1 sec to run on our development server, and about 9 seconds on the proposed production server which is double the spec in terms of hardware performance.

    It's got me beat.  Other clients on lesser servers with bigger versions of this database don't have these sort of issues.

    The proposed production server is SQL2000 spk3, with the default installation parameters.  Not been messed around with in any way, and is the same setup as we have at the office, except they have better hardware than us.

    The database we use between the two is the same database, indexes checked, database checked, etc.  We've run out of places to look and things to check.

    Any suggestions greatly appreciated.

  • Have you tried using profiler to get some idea of what CPU, memory and disk usage are for your new server? This will give the user group a bit more info for troubleshooting purposes.

  • Yes, we've looked at Processor time, Avg. Disk Read Queue Length, Page life Expectancy and User Connections.  Everything we've looked at seems normal.

    The production server currently hosts about half a dozen other databases, and we've noticed about 140 connections to the server overall.  Other databases/systems are running fine without any performance issues, which points the finger at our database.  But running the same database on our development server and even an average desktop MSDE, returns much faster.  In addition, other clients have larger databases with 40+ users without any problems runing on lower spec database servers.

  • How many processors are on the 2 servers. prod and test.  If you run sp_configure what is the cost threshold for parallelism? min memory per query (KB)? Are the indexes the same? Are they fragmented?  Are the execution plans the same?  Amount of data the same.

    A couple places to look.

    Let us know.

    Tom

  • Also check max memory setting:

     

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    sp_configure 'max server memory'

  • Development server has two Xeon processors, and the production server has 4 Xeons.

    Database on the two is identical, we re-indexed just to be sure, and did a checkdb which came back without any errors.

    I will check out sp_configure... and report back.

    Cheers.

    H

  • >>we re-indexed just to be sure,

    Speaking of indexes, does every table have a clustered index ? Rebuilding non-clustered indexes doesn't solve fragmentation in the underlying table data pages if the table is a heap without a clustered index.

     

  • Don't think so, but I'll check anyway.  But if it was indexing or db related, wouldn't we see an even worse performance on the development server (which is lower spec than production)?

  • Results of the SP_CONFIGURE:

    Production    Development    
    NameMinMaxConfig ValueRun ValueNameMinMaxConfig ValueRun Value
    affinity mask-2147483648214748364700affinity mask0214748364700
    allow updates0100allow updates0100
    awe enabled0100awe enabled0100
    c2 audit mode0100c2 audit mode0100
    cost threshold for parallelism03276755cost threshold for parallelism03276755
    Cross DB Ownership Chaining0100
    cursor threshold-12147483647-1-1cursor threshold-12147483647-1-1
    default full-text language0214748364710331033default full-text language0214748364710331033
    default language0999900default language0999900
    fill factor (%)010000fill factor (%)010000
    index create memory (KB)704214748364700index create memory (KB)704214748364700
    lightweight pooling0100lightweight pooling0100
    locks5000214748364700locks5000214748364700
    max degree of parallelism03200max degree of parallelism03200
    max server memory (MB)4214748364721474836472147483647max server memory (MB)4214748364721474836472147483647
    max text repl size (B)021474836476553665536max text repl size (B)021474836476553665536
    max worker threads3232767255255max worker threads3232767255255
    media retention036500media retention036500
    min memory per query (KB)512214748364710241024min memory per query (KB)512214748364710241024
    min server memory (MB)0214748364700min server memory (MB)0214748364700
    nested triggers0111nested triggers0111
    network packet size (B)5126553640964096network packet size (B)5123276740964096
    open objects0214748364700open objects0214748364700
    priority boost0100priority boost0110
    query governor cost limit0214748364700query governor cost limit0214748364700
    query wait (s)-12147483647-1-1query wait (s)-12147483647-1-1
    recovery interval (min)03276700recovery interval (min)03276700
    remote access0111remote access0111
    remote login timeout (s)021474836472020remote login timeout (s)021474836472020
    remote proc trans0100remote proc trans0100
    remote query timeout (s)02147483647600600remote query timeout (s)02147483647600600
    scan for startup procs0111scan for startup procs0100
    set working set size0100set working set size0100
    show advanced options0111show advanced options0111
    two digit year cutoff1753999920492049two digit year cutoff1753999920492049
    user connections03276700user connections03276700
    user options03276700user options03276700

    The real standout difference is they have Cross DB Ownership Chaining enabled, but I can't see how that would affect performance when our system uses the single database.  (That's the only reason I have highlighted this entry)

  • Hi,

    Just another possible problem: I have noticed performance issues on production servers having more CPU's than test machines. It seems that the degree of parallelism / hyperthreading can have a negative influence on performance. Check the processor tab on Enterprise manager properties. I sometimes just check 'use 1 processor for parallel execution'.

    Hth,

    JP de Jong

  • Tom mentioned it but I didn't see a response, have you checked the execution plans on both servers to see if there are any differences between the servers?

  • There are lots of factors that can affect performance other than procs. I'd run a trace for wait stats, I'd also check out disk i/o completion time and other disk stats to make sure that the disk subsystem isn't your bottleneck. Compare the RPC completion vs sp completion times in case you have a connectivity issue - poor performance from a middle tier server can impact sql server.

    Run some tests from QA on the actual server, either on console or terminal session.

    I think there are too many other variables at play here.

    I'd recommend SQL Server Performance Tuning by ms press ISBN 0-7356-1270-6  as a good reference point and basis for starting to understand performance issues.

     

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Without seeing the execution plans my guess right now would be parallelism. To test on production, I would set cost threshold for parallelism to 12 - 15.   That is normally what I run at on my multi proc production servers.  This is easy enough to change back and forth.

    Tom

  • I would assume that Auto close and Auto Shrink is off in Production

  • Before looking at the OS and configuration I'd start with:

    Round I

        DBCC UPDATEUSAGE

        UPDATE STATISTICS (with FULL scan) for all tables

        exec sp_recompile for all tables

        exec sp_refreshview for all views

    Round II

        DBCC DBREINDEX

        UPDATE STATISTICS (with FULL scan) for all tables

        exec sp_recompile for all tables

        exec sp_refreshview for all views

    Round III

        Profiler

        Query Hints

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 15 posts - 1 through 15 (of 31 total)

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