Post reply

Performance issues

  • January 23, 2006 at 11:20 pm

    #113167

    I have 2 SQL Servers and the same database on both. On the first one I'm runing a query from a stored procedure that updates, inserts and runing also a trigger that will update and insert also using views. The result: it is very slow. If I'm runing the same query on the same database on the second server I get a lot of speed. Now I tried to backup and restore the database on the first server; I detached and attached the database; I defragmented the indexes; I rebuilt the indexes. I checked every index to make sure I get the most speed from them. Nothing. Now the funny thing is that the second server where I get the most of performance has 1 CPU at 1.8 Ghz and 1GB Mb of memory and the slowest one (first one&nbsp has 4 CPUs and a lot of Gb of memory and space. Do you have some ideeas? Thank you.

  • January 23, 2006 at 11:29 pm

    #616573

    Update the statistics and see..

    ..hema

  • January 23, 2006 at 11:31 pm

    #616574

    create a trace on that stored procedure and see

    what is happening really...

    ..hema

  • January 24, 2006 at 12:20 am

    #616581

    Check the Execution plan on each server. This will verify if the same indexes are being used and if any statistics are missing or out-of-date.

     

    --------------------
    Colt 45 - the original point and click interface

  • January 24, 2006 at 12:27 am

    #616582

    I would also check the I/O of the second more powerful server and make sure that the hardware is all ok. You could have faulty hardware resulting in the bad performance.

     

     

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • January 25, 2006 at 2:03 am

    #616832

    You could also try recompiling all the stored procedures using

    EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''

    And refreshing the views using sp_refreshview.

    I had problems on my system, poor performance and data missing from views which I have attributed to schema changes, so now I refresh all the views and recompile all the stored procedures regularly.

    See http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=240819

    You haven't mentioned how you are transferring the database from one server to the other, or which server is the source.

    David

    If it ain't broke, don't fix it...

  • January 26, 2006 at 1:58 pm

    #617319

    This issue might be related to parallelism.  You also didn't mention how fast those 4 CPUs are...

    Look at the execution plan and see how much of the cost is due to parallel processing.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • January 26, 2006 at 3:50 pm

    #617336

    Yup I'd be willing to consider parallelism - seen it done it and been there !!! There is one other obscure possibility - are the collations identical on the servers and if you script the proc from each database ( through script object in EM ) are the set options OUTSIDE of the procedure drop and create identical ?

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

Viewing 8 posts - 1 through 7 (of 7 total)

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