Two identical systems; one performs well, one performs poorly

Question

Two identical systems; one performs well, one performs poorly

mcginn

SSCommitted

Points: 1592
More actions
October 3, 2012 at 4:35 pm

#282516

I have a perplexing performance issue. I have a database system installed at two remote sites. Both sites have identical hardware configuration, same version of Windows Server 2008 R2, identical memory, and SQL Server 2008 R2. According to the query plan analysis the database tables have the proper and necessary indexes. On the development machine the procedure returns immediately. Both databases at either site have roughly the same row counts in each of the tables.
A stored procedure executed via SSMS executes in less then 1 sec at one site but takes 15 min at the other site which of course is absurd. Since the sites are remote I am have the site rep sun through the DBCC commands to determine if there are any internal corruptions. There are no errors reported in the server log.
My question is, assuming the DBCC commands do not turn up any internal inconsistencies what other options should I persue to track down the root cause of this error?

Viewing 5 posts - 1 through 4 (of 4 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Get the execution plans (actual) for the 2 procs. The plans should be different and will probably give a clue as to how the server is deciding to attack the 2 queries. The answer to why the plans are different will point the way to the difference between the servers.

And then again, I might be wrong ...
David Webb

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 2

mcginn (10/3/2012)
I have a perplexing performance issue. I have a database system installed at two remote sites. Both sites have identical hardware configuration, same version of Windows Server 2008 R2, identical memory, and SQL Server 2008 R2. According to the query plan analysis the database tables have the proper and necessary indexes. On the development machine the procedure returns immediately. Both databases at either site have roughly the same row counts in each of the tables.
A stored procedure executed via SSMS executes in less then 1 sec at one site but takes 15 min at the other site which of course is absurd. Since the sites are remote I am have the site rep sun through the DBCC commands to determine if there are any internal corruptions. There are no errors reported in the server log.
My question is, assuming the DBCC commands do not turn up any internal inconsistencies what other options should I persue to track down the root cause of this error?

First blush guess would be parameter sniffing. Try executing the stored procedure on the slower machine with the option to recompile and see what happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Statistics?

Is large chunks of data in your PROD version were recently inserted/changed/removed?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mcginn SSCommitted Points: 1592 More actions · Answer 4

All of the data in the database was migrated from another system. After migration of the data all of the indexes were re-created and all of the statistics updated. We followed this procedure on both systems; one has fast performance the other really poor performance.