Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Severe performance degradation between servers Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 12:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:29 AM
Points: 140, Visits: 277
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.
Post #1419163
Posted Tuesday, February 12, 2013 2:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:08 PM
Points: 72, Visits: 199
It could also be a Disk issue. Please check the Disk on Prod.

Regards
Srikanth Reddy Kundur


Please ignore the message above
Post #1419188
Posted Tuesday, February 12, 2013 4:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 25, 2014 10:09 AM
Points: 283, Visits: 1,114
Have you looked at the execution plans? That has to be the first step.



Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Post #1419232
Posted Tuesday, February 12, 2013 7:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 4,343, Visits: 6,150
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 at GMail
Post #1419266
Posted Thursday, February 14, 2013 6:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 15,630, Visits: 28,017
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1420014
Posted Thursday, February 14, 2013 11:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:29 AM
Points: 140, Visits: 277
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.
Post #1420223
Posted Thursday, February 14, 2013 1:08 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 4,343, Visits: 6,150
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 at GMail
Post #1420269
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse