SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Severe performance degradation between servers


Severe performance degradation between servers

Author
Message
Angela Henry
Angela Henry
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 285
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.
ksrikanth77
ksrikanth77
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 257
It could also be a Disk issue. Please check the Disk on Prod.

Regards
Srikanth Reddy Kundur


Please ignore the message above
Richard Fryar
Richard Fryar
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 1172
Have you looked at the execution plans? That has to be the first step.


Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32757 Visits: 8677
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 ... Cool

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99523 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Angela Henry
Angela Henry
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 285
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.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32757 Visits: 8677
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search