Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

rebuild index Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 11:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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
Post #1363072
Posted Saturday, September 22, 2012 12:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 1,855, Visits: 3,453
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.
Post #1363077
Posted Saturday, September 22, 2012 3:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
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 2008, MVP
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

Post #1363090
Posted Saturday, September 22, 2012 11:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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?
Post #1363192
Posted Sunday, September 23, 2012 12:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 1,855, Visits: 3,453
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
[/quote][quote]
Post #1363194
Posted Sunday, September 23, 2012 12:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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.
Post #1363264
Posted Sunday, September 23, 2012 12:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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
Post #1363268
Posted Sunday, September 23, 2012 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
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 2008, MVP
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

Post #1363270
Posted Sunday, September 23, 2012 12:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:31 PM
Points: 1,773, Visits: 3,209
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
Post #1363277
Posted Sunday, September 23, 2012 12:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
Nope. REstored DB will be identical to the DB that was backed up


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1363280
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse