Report runs faster after Backup and Restore

Question

Report runs faster after Backup and Restore

Max101

SSC Rookie

Points: 41
More actions
September 6, 2018 at 8:46 pm

#376926

This is the detail of the situation:
The Report process was running very slow in the Live Database (10+) minutes. When I Backup and restore the Production database and restore to Test Company in the same SQL Instance, the report runs fast (1 min) in Test Database. Eventually the solution was to simply Backup Live Database and restore Back to the same Live Database. That solved the slow issue.
The Question I have is what process in the Backup and Restore process fixed the issue? I did not run any other maintenance tasks. Are there any maintenance such as Update Statistics that are automatically run during the Restore process?

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Nothing in the backup and restore would make the query run faster. However, what you're likely seeing is the result of a new compilation of the query, possibly with different parameters, hard to know based on the evidence. To test this, get the execution plan out of cache for both databases and compare them. I'll bet they're different.

"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

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

Long shot, but I suppose it's possible that the original db was very physically fragmented on disk and the restore made the disk contiguous. Very rare nowadays, esp. with modern disk drives, but theoretically possible.

What are the autogrow amounts for the data files? In particular, are they very small (such as 1M)?

Use contig.exe to check for extents and, if needed, to compact file(s).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Grant Fritchey - Friday, September 7, 2018 2:46 AM
Nothing in the backup and restore would make the query run faster. However, what you're likely seeing is the result of a new compilation of the query, possibly with different parameters, hard to know based on the evidence. To test this, get the execution plan out of cache for both databases and compare them. I'll bet they're different.

That would be my "guess", as well.

--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)