Re: SQL2005 Rebuild Index Not Working

(wackyphill@xxxxxxxxx) writes:
I understand your point Erland, but these Primary keys were created w/o
specifying that they should be non-clustered, so I'm certain they are.

You can always use sp_helpindex to find out.

Why is it that w/o a clustered index defragmentation on non-clustered
indexes can't occur, or did I misunderstand the previous poster?

I don't recall exactly what he said, but you can't defragment the data
pages without a clustering index. The NC indexes are defragmentable
nevertheless.

You are correct that these tables currently are small by most people's
standards. I'm guessing that the largest one has < 1000 records. Is it
mentioned somewhere in SQL books on-line about free space looking lile
fragmentaion, that I can read up on?

Let me put it this way: with tables of that size, there's usually not
much reason to worry about fragementation anyway.

Fragmentation matters when it results in data being spread out on
many extra pages. Say that you have a table with 1 GB of data. If
the data pages are only 50% full, the server needs to read 2 GB of
disk to scan all data. This can affect the performance of the system.

Whether this is spelt out in clear in Books Online, I don't know. But
you can read about the physical architecture starting on
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/33572c3b-343d-45aa-bb42-1d31423ae5cc.htm.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Query training -- Complex queries
    ... > Update PositionsEOM ... Here you don't have a derived table, but a correlated subquery. ... UPDATE in Books Online. ... is not written for SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Linked Server: How to check if server exists?
    ... The root problem is described in the Books Online topic Batches. ... Rick Byham, SQL Server Books Online ... linked server don't run on those development machines not having that linked ...
    (microsoft.public.sqlserver.connect)
  • Re: Problem when changing Views
    ... There aree several reasons. ... I use neither of the tools, but I guess that Design View resubmits the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: STORED PROCEDURE - passing table name as a parameter
    ... T-SQL, is that you get problems if the view definition does not fit into ... will need to query other system tables, for instance syscolumns. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: SQL 2005: Help files not valid?
    ... Please try the following instructions to remove and reinstall Books Online. ... If this doesn't solve the problem, we'll need to get the Visual Studio folks ... In Add or Remove Programs, select Microsoft SQL Server 2005, and then ... Complete the remaining steps in the Installation Wizard. ...
    (microsoft.public.sqlserver.setup)