Why moving a database slowed it down a lot?

Question

Why moving a database slowed it down a lot?

Viewing 15 posts - 16 through 29 (of 29 total)

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

george sibbald (2/1/2010)
Steve Jones - Editor (2/1/2010)
If you're using RDP, network isn't an issue.
Stats should move, but as Jeff mentioned, they often don't seem to in a backup/restore.
I thought Jeff was joking when he posted that. How would that happen?

Heh... I never always joke sometimes... 😉

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

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

If i have to move databases from one machine to another, I also make the assumption that I need to do at least the daily maintenance of defragging/rebuiding indexes that need it and updating all stats because, technically, all the rows are "new". That assumption has saved me oodles of time answering the phone from angry users on a migrated database.

Can I explain exactly why all this happens? Not on your life. I only know that it works for me.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Hmmm. Having trouble wrapping my head around this one. I hope its not true or everything I hold dear about backups turns out to be a lie. I cannot remember advice from MS to update stats after a restore, let alone rebuild indexes.

Its not an effect I can say I have ever noticed, and I have restored a few databases in my time. 🙂

all a backup does is work through a database from the start to the end copying out the extents in order, then enough of the log to get any changes made and provide a consistent point. A restore just splats them back, so where does the table fragmentation come in? Are we saying backup\restore can have the same effect as a shrink?

As for stats are they not held in sysindexes so how do they get lost?

All I can see is physical file fragmentation on disk if there is not contiguous space on disk for the restore, but you could just as well remove physical fragmentation if you are restoring to a new disk and thus get performance improvements.

I don't doubt people have seen this effect but I would sure like to know why.

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

george sibbald (2/2/2010)
Hmmm. Having trouble wrapping my head around this one. I hope its not true or everything I hold dear about backups turns out to be a lie. I cannot remember advice from MS to update stats after a restore, let alone rebuild indexes.
Its not an effect I can say I have ever noticed, and I have restored a few databases in my time. 🙂
all a backup does is work through a database from the start to the end copying out the extents in order, then enough of the log to get any changes made and provide a consistent point. A restore just splats them back, so where does the table fragmentation come in? Are we saying backup\restore can have the same effect as a shrink?
As for stats are they not held in sysindexes so how do they get lost?
All I can see is physical file fragmentation on disk if there is not contiguous space on disk for the restore, but you could just as well remove physical fragmentation if you are restoring to a new disk and thus get performance improvements.
I don't doubt people have seen this effect but I would sure like to know why.

Oh, I agree... it's not something that MS recommends nor something that even the general populus recommends. It's not even a best practice. It's just something I do.

As for migrations from one version to another, there does seem to be scuttlebutt that rebuilding stats is essential. I've never confirmed it one way or the other because I just do the index rebuilds, stats rebuilds automatically.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 5

yes between versions stats need redoing because of changes in the optimiser , but the same version should not be an issue.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Ok, I am thinking saying that statistics are held in sysindexes might be simplistic so I have done some digging. Results so far

dbcc show_statistics in BOL says

The query optimizer stores statistics for a table or indexed view in a statistics object.

For a table, the statistics object is created on either an index or a list of table columns.

The statistics object includes a header with metadata about the statistics,

a histogram with the distribution of values in the first key column of the statistics object,

and a density vector to measure cross-column correlation.

this blog

http://www.cnblogs.com/WCFGROUP/archive/2009/01/02/1366977.html

says

All information about a single statistics object is stored in several columns of a single row in the sysindexes table, and in a statistics binary large object (statblob) kept in an internal-only table.

In addition, information about statistics can be found in the new metadata views sys.stats and sys.indexes.

there is a column called statblob in sysindexes so I am thinking this is the internal-only table referred to? Either way I would expect this internal object to be within the database still and therefore backed up with it.

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

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 7

They have to be stored somewhere since you don't need to rebuild stats on a restart of the server. They should be in a backup, and I would agree that it isn't something that MS says. However it just seems to be something that is recommended on migration/upgrade and "seems" to help. Why? No idea. Doesn't make sense to me either.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

upgrade definitely do it. a migration, I've never had to.

Just thinking, on a migration or even a restore to the same server, data will have to be read from disk and SPs recompiled so there will be initial relative slowness, maybe the stats rebuilding ia a red herring. 🙂

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

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 9

I'm not sure it's recompiles. I've seen people report poor performance from procs and queries for a period of time.

Nothing makes sense, but it's like the old "reboot" advice. Seems to work often enough to give it a try.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Steve Jones - Editor (2/2/2010)
However it just seems to be something that is recommended on migration/upgrade and "seems" to help. Why? No idea. Doesn't make sense to me either.

When upgrading it's because the statistics created by SQL 2000 are less detailed than those created by SQL 2005. While the SQL 2005 optimiser can use the statistics created by SQL 2000, it does not do so effectively. Hence, it is recommended to update all statistics after upgrading so that all are the more detailed form used by SQL 2005.

Moving from one server to another with the same version of SQL, stats updates are not necessary at all.

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

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 11

Gail,

Thanks, never knew that. Does it apply from 2005->2008 as well?

george sibbald SSC Guru Points: 104210 More actions · Answer 12

Steve Jones - Editor (2/2/2010)
Gail,
Thanks, never knew that. Does it apply from 2005->2008 as well?

I had mentioned it earlier in the thread :ermm:

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Steve Jones - Editor (2/2/2010)
Thanks, never knew that. Does it apply from 2005->2008 as well?

I don't know for sure. If I had to guess, I'd guess no, because the changes 2005-2008 weren't the comprehensive engine changes 2000-2005 was. Still, there's no harm in updating the stats after an upgrade as one of the post-upgrade steps.

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

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 14

Steve Jones - SSC Editor

SSC Guru

Points: 736445

February 2, 2010 at 8:31 am

#1112314

Doh, sorry George. Missed it. My apologies there.

tosscrosby SSCoach Points: 18045 More actions · Answer 15

Glad Gail chimed in to clarify things a bit further. I knew to include it in the upgrade process. From a regular maintenance standpoint though, I have always ensured my rebuild of indexes or statistics and the CheckDB were done prior to backup so that if we had a disaster, I could get the data back online as quickly as possible without additional steps after restoring the data. But that's just me....;-)

-- You can't be late until you show up.