Post reply

Severe performance degradation between servers

  • February 12, 2013 at 12:58 pm

    #270801

    We have a Dev server and a Prod server, as below:

    Dev

    ----

    Microsoft SQL Server 2012 - 11.0.2316.0 (X64)

    Developer Edition (64-bit)

    Procs: 2

    Memory: 16GB

    Virtual Machine

    Prod

    -----

    Microsoft SQL Server 2012 - 11.0.2316.0 (X64)

    Enterprise Edition (64-bit)

    Procs: 6

    Memory: 32GB

    Physical Machine

    We just promoted some code from dev to prod and now have a severe performance issue in prod. A query that takes 4 minutes in dev, takes 5 HOURS in prod.

    Things I have checked on:

    -Table row counts match between dev & prod.

    -Did a schema compare and they have the same objects.

    -Checked stats date and they are up to date in both dev & prod.

    -Rebuilt all indexes, updated stats and usage for all tables in both dev & prod, then checked stats date again and they are all up to date.

    In my experience when this kind of thing happens, it typically is because stats are out of date and caused a runaway thread on the parallelism. However, when I watch the activity monitor, multiple threads are NOT being spawned.

    So what else can I look at/for?

    Thanks

    -A.

  • February 12, 2013 at 2:25 pm

    #1587198

    It could also be a Disk issue. Please check the Disk on Prod.

    Regards

    Srikanth Reddy Kundur

    Please ignore the message above

  • February 12, 2013 at 4:03 pm

    #1587235

    Have you looked at the execution plans? That has to be the first step.


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • February 12, 2013 at 7:21 pm

    #1587262

    execution plan differences

    blocking

    check wait stats during execution

    check file IO stalls during execution

    sp_whoisactive (awesome freebie from Adam Machanic of SQLBlog fame) can really help you here

    Or get a professional to remote into your system for about 10 minutes ... 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 14, 2013 at 6:02 am

    #1587875

    Also check the ANSI connection settings on the two servers.

    Let's see, cost threshold for parallelism, the max degree of parallelism, umm... The stuff already mentioned.

    "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

  • February 14, 2013 at 11:15 am

    #1588054

    I found our issue. This is my best guess since I don't have any concrete evidence to prove it.

    I must mention that I did look at the execution plans (that's always the first thing when you have a performance problem, I just didn't mention it, because I guess I thought it went without saying, my bad). The execution plans showed nothing out of the ordinary and were almost an exact match between dev and prod. Only deviated by 1/10 of a percent in most cases, so that led me no where (other than to update my version of SQL Sentry's Plan Explorer).

    I did discover that we neglected to move our tempdb from the local super slow physical drive to the super speedy SAN drive a coupe of months ago, so got rid of the waits were seeing in tempdb, which by the way is the only place we saw any kind of waits. Unfortunately, this didn't help either.

    I had forgotten about Adam's sp_whoisactive (thank you for reminding me), so downloaded that and discovered something interesting, but not all that helpful. It showed me that the reads were going magnitudes faster in dev than in prod, which I already new, but still had no clue as to why.

    I tried moving the physical data files around between SAN drives to see if our tiered storage was really working like was supposed to, which didn't help either.

    We also have Idera's SQLdm product and it did nothing to help us, it was like nothing was wrong with the server.

    Finally this morning I did one more cursory compare between the tables and discovered a very odd anomaly that I missed earlier on one table. Even though the tables had the exact same number of records, approx 7 Million, (in addition to the exact same records, thanks to RedGate's SQL Data Compare), the table in prod was using 2x more storage than the table in dev. Now, we had rebuilt all the indexes on all the tables (by actually dropping all indexes and then recreating them), updated the stats and usage in both dev and prod earlier, but still no improvement. On a hunch, I decided to recreate this one offending table along with all it's dependent objects (i.e., indexes, views, etc) and Abracadabra back down to 4 minutes in prod. When I say recreate the table, I did a "select into.." with a new table name then renamed.

    Not sure exactly what the cause of the bloat was, but now the storage matches, within a byte or two, of the storage in dev. Bad meta data around the table? indexes? not sure. Anybody have any explanation of this?

    Thanks for all the suggestions, it's been a while since I've had to do "DBA" work and I had to dust off the cobwebs a bit on this one.

    Best Valentine's Day present I've had at work ever!

    -A.

  • February 14, 2013 at 1:08 pm

    #1588097

    Normally I would say that this caused different query plans that made it more efficient, but that isn't the case here. So I will go with someone having rebuilt the indexes with a 50% or lower fill factor which would account for the table size difference. That affects not only disk IO required but also RAM buffer pool usage, locks/latching, etc.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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