Database Performance Improves After Database Restore

Question

Post reply

Database Performance Improves After Database Restore

arrjay

Hall of Fame

Points: 3455
More actions
December 11, 2014 at 3:05 am

#312974

Hi there - I am troubleshooting a performance issue. On our production environment (168GB, 24cores) a fairly basic query takes 20secs to run, but if I restore the same database to a VM (40GB, 8cores) the same query takes 1 second.

SELECT count (*) FROM

WITH (NoLock) WHERE (table.FID IN (359890) OR

(table.FID IN (N'19508e3b-0595-41cf-88f2-c13141e0bc40') AND table.FID = 0))

AND table.date>= '09 November 2014 23:59:59'

AND table.date <= '11 December 2014 23:59:59'

AND table.Type >= 201 AND table.Type < 400

Why would this be? Is there something the restore process does to improve the performance?

Thanks .

--------------------------------------------

Laughing in the face of contention...

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Too much of a hardware difference to say anything useful.

Execution plans of fast and slow?

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

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Hi - I have attached the execution plans. There is a slight difference in the number of records this because the restore took place a week ago. But the difference in runtime is far greater to attribute the difference to the number of records. Thanks again.

--------------------------------------------

Laughing in the face of contention...

arrjay Hall of Fame Points: 3455 More actions · Answer 3

arrjay

Hall of Fame

Points: 3455

December 11, 2014 at 4:28 am

#1764001

Cracked it. It was a statistics issue. They're not being updated. Thanks!

--------------------------------------------

Laughing in the face of contention...

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

Glad to hear the solution. I was also going to suggest looking into bad parameter sniffing, but it's probably just the stats.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

Your initial query was impossible:

WHERE (table.FID IN (359890) OR

(table.FID IN (N'19508e3b-0595-41cf-88f2-c13141e0bc40')

but the query plan cleared it up: the second column is a different column from the first one.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!