Why does copy of SQL database have fragmented indexes?

We have a large SQL database, and every night a copy is made. We actually work with the copy.

My question is: Why is our copy of the database fragmented? I did a brief look and discovered heavy index fragmentation in some of the indexes.

Now I know file fragmentation is a different thing, but for file fragmentation back in the old days we would sometimes just write the whole disk to tape (that's how old this method is) and then write the whole thing from tape back to disk, and that would take care of our file fragmentation (well for a week at least. Then they'd have me stay up all night Sunday night and do it again...)

So my question is, when a SQL database is copied, why aren't the fragmented indexes magically unfragmented for us?

(I don't know how the IT guys make the copy, but the database is about 73GB. We have Microsoft SQL Server, and I think it's the 2008 version.)

(Perhaps I should also ask, is there a way they could copy the database and have the indexes magically defragmented for us during the copy?)
deleydSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Is your copied database created via backup and restore? If so, you'll restore to whatever state of fragmentation your source database is in.

Perhaps you re-index your source database on a different schedule than when you copy it off?

Not sure if I'm hitting the mark, but these are just some things to rule out.
deleydSoftware EngineerAuthor Commented:
That's probably it. They probably use BACKUP and RESTORE. I guess BACKUP and RESTORE does too good a job of duplicating exactly the database, including all the fragmentation.

Our IT department is too afraid to defragment the indexes. They fear data loss.

I have a 140MB table with 98% fragmentation. [I didn't even know it was possible to be that fragmented!]

I'm wondering if there's a way I can BACKUP and RESTORE a copy of the database on my local computer so I can defragment it and see if there's a performance improvement.
Mike EghtebasDatabase and Application DeveloperCommented:
Suppose your database indexes are refreshed and there is not fragmentation. If this database is not used for a week, there will be no index fragmentation at the end of the same week.

So, as data edited (revised, added, deleted) index fragmentation also increases. So, if you want to have backup with no index fragmentation, then first reindex and then make backup of it. Or, refresh index after restoring it.

Yes, you can restore offline to defrage it then backup it for later use ready to go.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dsackerContract ERP Admin/ConsultantCommented:
Hi deleyd. Can you defragment your database that you have copied, after you have copied the database?

If you have control over your copied version, perhaps you can copy it at night, run the re-indexing and update statistics on it. That would probably improve its response considerably.

And yes, you are correct. RESTORE creates the very image that was backed up, fragmentation and all. But, hopefully you have control over your copied database and can remedy that.
Vitor MontalvãoMSSQL Senior EngineerCommented:
So my question is, when a SQL database is copied, why aren't the fragmented indexes magically unfragmented for us?
You are confusing data fragmentation with file fragmentation. A SQL Server has one or more data files and each data files has one or more data pages. The data pages exists inside the data files, so when you copy a data file is like you're moving a disk from one computer to another one, so will take the fragmentation as is.
A disk defrag won't defragment data pages (actually, will do worst) and that's why each database engine has a reindex/reorg command to defragment the data pages.

Our IT department is too afraid to defragment the indexes. They fear data loss.
Defragment won't make any data loss. Rebuilding indexes should be part of a database maintenance plan. Should run regularly (at least once a week) so it's better to contract a DBA for your IT department or else you'll have problems with your databases and then may be too late to do anything.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Do you have Enterprise or Standard Edition?  With E, you can rebuild many indexes online, meaning no down time at all for the table.

>> Our IT department is too afraid to defragment the indexes. They fear data loss. <<
Cannot happen (except by a power failure or other server crash).  SQL logs defragmenting and rebuilding, so it can recover from it like for any other SQL statement.

Tables need defragmented or rebuilt only when the stats indicate it, not automatically based on any particular time (daily, weekly, etc.).  For convenience, after you've analyzed a table, you may decide to do it weekly, but just be aware that that is not necessarily optimal.

Btw, never update statistics after an index rebuild: the rebuild has already updated the statistics, and based on 100% of the table data, i.e., you have perfect stats.  If you rebuild them, SQL will use just a sampling of the rows, typically between 5 and 20%, and you will have used additional processing resources to get far less accurate stats -- obviously not a good thing.
dsackerContract ERP Admin/ConsultantCommented:
I have a 140MB table with 98% fragmentation. [I didn't even know it was possible to be that fragmented!]
It's quite possible that comes from someone shrinking your database.
deleydSoftware EngineerAuthor Commented:
Thank you everybody. Now I better understand how data is stored in files, and I will look into the shrinking thing which sounds like something they would do.
deleydSoftware EngineerAuthor Commented:
OMG thank you so much for mentioning the database shrinking! I looked into it and they have AutoShrink turned on for the databases which are highly fragmented!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.