Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mias
Starting Member
1 Post |
 Posted - 2012-04-26 : 12:59:08
|
| I was wondering if there is a way to Reindex only the tables in a database that have more than 1000 records. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-04-26 : 13:20:04
|
it can be done with multiple steps. BIG question is why would you do this. tomorrow your tables go above the 1000 threshold and you will need to revisit this issue. also for those tables with < 1000 you will still need some good indexing if large tables join to it. does not scalebut to get you started check this outSELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 ORDER BY st.row_count DESC <><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-26 : 13:50:52
|
| I agree with yosiasz, and more to the point, row count doesn't really mean anything with respect to table size. A 2000 row table with a single smallint column could fit on a few data pages and would not be worth reindexing. You really want to look at tables/indexes with a large page count. In that instance I'd recommend Ola Hallengren's reindexing script:http://ola.hallengren.com/It defaults to skipping indexes of less than 1000 pages, as per recommendations. You can change that of course. |
|
|
|
|
|
|
Reindex All Tables