Post reply

Most bizzare issue of all times (Serious Performance Issue)

  • June 11, 2007 at 7:18 am

    #174075

    I consider this as the most Bizzare issue of All time 🙂

    Config:

    SQL Server 2005 Enterprise Edition SP2 on a Windows 2003 Advanced Server box which has 4CPU Xeon, 4GB RAM and has an EMC SAN attached for DB File Storage.

    Migration Process:

    I have an empty db server MYPROD. Tables and Indexes are created. Then a Custom Written utility brings one record at a time over to MYPROD from a database on the same server called OLDPROD. This custom written utility simply queries the OLDPROD and executes a Stored Procedure on MYPROD with the values of the source records as Parameters to the SP.

    The SP then Inserts and updates records in the MYPROD so accoumadate the change in database schema design we did. This also accoumplishes some level of Normalization. Like for example OLDPROD has the Student table, the SP normalizes the Student table and puts the values in Student, Address, StudentClass Tables.

    Problem:

    Once the utility brought over all the 1.2 million records to MYPROD from OLDPROD I executed a normal query that the Front End VB application normally calls. This query surprisingly took 3.5 mins and eventually timed out.

    I had executed the same process of migration step by step, unchanged , with exactly the same hardware in beta environment MYBETA and the query executed in 2 seconds.

    How come the same query takes so much time now ? We have strictly followed the same procedure and same database creation script.

    Bizzare:

    I feared that the Migration Process in MYPROD might have gone wrong, I wanted to confirm that so I took a backup of the MYBETA database from the BETA server and restored it on the PROD server as MYBETAONPROD. Now This database also started to have the same problem. So It was confirmed that it was not a migration issue.

    Bizzare 2:

    I then suspected that the Server Hardware was a problem, so I copied (backup>restore) the MYBETAONPROD back again on the BETA Server. and to add to the surprise now the BETA server started executing the query slow.

    When copying MYBETA to PROD server I had also copied MYBETA to a DEV server (almost same hardware). The MYBETA on DEV box ran ok with 2 seconds on the query.

    I am not sure what to blame here. Need Help Urgently. Had a huge fiasco as we could not launch our product. Any help will be greatly appreciated and thanked. Please feel free to ask me questions.

    Thanks a lot in advance. Thanks for even reading it and giving it a try if you do not have an answer.

  • June 11, 2007 at 11:44 am

    #712054

    Have you looked for configuration differences  between the BETA servera and PROD?

    Any chance that after inserting 1.2 million rows a record at a time that you've got index fragmentation problems?

    Those are the first things I'd look at. Neither is likely to be a magic bullet for you, but you never know.

    "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

  • June 12, 2007 at 4:10 am

    #712202

    What are the specs of the prod and dev servers? how much memory is allocated to SQL on each of them? Is there anything else running on those servers?

    I'd run performance monitor on the prod server, looking for signs of hardware bottlenecks.

    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

  • June 12, 2007 at 1:00 pm

    #712380

    have you compared execution plans?

  • June 12, 2007 at 8:38 pm

    #712489

    I had Microsoft come in today and they analyzed my Dev, Beta and Prod environments as well as the databases.

    They suspect that the Prod server is somehow not generating correct query execution plans. Looks like Recompiling the Procedures and views seems to be solving the problem.

    They will conclude tomm and I will post the results here.

    Thanks a lot guys. Thanks for your help.

  • June 14, 2007 at 11:35 am

    #713025

    then try doinog update statistics with fullscan, manually dropping and rebuilding indexes, dropping and rebuilding statistics or any combination of the above

  • June 14, 2007 at 1:56 pm

    #713049

    What was the outcome?

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

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

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