rebuild index

Question

rebuild index

sqlfriend

SSC Guru

Points: 52769
More actions
September 21, 2012 at 11:29 pm

#282028

Today I am trying to trouble shoot a slow query which uses multiple cte in the query.
I spent most of time trying to rewrite, but finally found out there is nothing wrong with the query, but one of the table 's priimary key need to rebuild index.
I know for this table, someone has just imported 2012 data into it.
so after rebuild the query runs like a charm.
Now I should learn my lesson after so long time to troubleshooting to rewrite instead of just rebuild index.
So my question is when should run rebuild index,
and even the Primary key is not automatically rebuilt?
when create a new index on a exiting table, is it automatically rebuilt when it creates it?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

Indexes does not get rebuilt or reorganized automatically. You have to schedule jobs to do this.

There are a lot of free and great scripts that will do this for you, for example http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.

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

The problem was unlikely to be due to a fragmented index, while fragmentation can slow queries, it's not usually that much. Most likely what fixed the problem was the statistics update that also happens when you rebuild an index.

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

Thanks, we are using that script doing maintenance of db, but only on weekend.

Now that reminds me another related question, we have a process nightly to create some tables in a database then import data into tables, then add indexes, pks, fks, then restore the database on another sever B

People use serverB to query, create excel reports. The above process is done nightly. So does that mean the indexes never been rebuilt? If so I wonder why not hearing much complain of the query running slow?

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 4

If so I wonder why not hearing much complain of the query running slow?

As Gail said; fragmented indexes will usually not slow down that much, not like out of date statistics causing non-optimal plans.

So does that mean the indexes never been rebuilt?

Yes. Are rows in these tables modified after the import? If not, then the indexes won't get fragmented, and there is no need to run any index or statistics maintenance.

You also say " import data into tables, then add indexes, pks, fks, then restore the database on another sever B"

Do I understand correctly if you drop all constraints prior to the import, then import and the recreate the constraints? And are you doing it in the sequence you mention? Unless your PK is non-clustered, then you really should create it before the other indexes. Rows in index leaf pages use the RID of the row in the heap as the pointer back to the row in the heap, while when the table has a clustered index it uses the cluster key columns as a reference instead.

So if you first create the indexes when there is no clustered index, the index leaf pages will use the RID, and then when you add a clustered index (the PK) all your newly created non-clustered indexes will have to be rebuilt again with the clustering key.

Back to you question

sqlfriend SSC Guru Points: 52769 More actions · Answer 5

Nils Gustav Stråbø (9/23/2012)
If so I wonder why not hearing much complain of the query running slow?
As Gail said; fragmented indexes will usually not slow down that much, not like out of date statistics causing non-optimal plans.
So does that mean the indexes never been rebuilt?
Yes. Are rows in these tables modified after the import? If not, then the indexes won't get fragmented, and there is no need to run any index or statistics maintenance.
You also say " import data into tables, then add indexes, pks, fks, then restore the database on another sever B"
Do I understand correctly if you drop all constraints prior to the import, then import and the recreate the constraints? And are you doing it in the sequence you mention? Unless your PK is non-clustered, then you really should create it before the other indexes. Rows in index leaf pages use the RID of the row in the heap as the pointer back to the row in the heap, while when the table has a clustered index it uses the cluster key columns as a reference instead.
So if you first create the indexes when there is no clustered index, the index leaf pages will use the RID, and then when you add a clustered index (the PK) all your newly created non-clustered indexes will have to be rebuilt again with the clustering key.
Back to you question

Thank you, the tables usually not modified after we restore daily to another server. The users mainly query on it. so I don't see we have a maintenance job to rebuild index.

The process we does nightly is to drop all tables, create new tables, import data, then add keys, (PK, FK) then last add indexes. thank you for educating me on the order of these objects creation, that makes a lot of sense.

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

So does that mean the indexes never been rebuilt?

Yes. Are rows in these tables modified after the import? If not, then the indexes won't get fragmented, and there is no need to run any index or statistics maintenance.

[/quote]

Also for above statement, do you mean when first time import data to a new table, the index is not fragmented, and the statistics is up to date?

But when I second time insert more data into this table, then the index is fragmented and the statistics is out of date?

( the records we imported each time is about 60000)

we'd better either run build index or update statistics before we query it.

(this query is not a simple one, but is to join this table with other tables in another database)

Thanks

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

sqlfriends (9/23/2012)
Also for above statement, do you mean when first time import data to a new table, the index is not fragmented, and the statistics is up to date?

The index won't be fragmented and the stats will be updated on the first access of the table

But when I second time insert more data into this table, then the index is fragmented and the statistics is out of date?

Fragmented, maybe. Stats out of date, probably, depending how much of the table has been affected, the stats might be updated the next time the table is queried, or they might not.

It is often a good idea to do a stats update with fullscan after a large data load, especially if the table is read-heavy the rest of the time.

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 8

Thanks a lot

Another case, if I just do a restore of a database from a backup to another database, do I need to do a update statictics right after that or no need to do it?

Thanks

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

Nope. REstored DB will be identical to the DB that was backed up

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