Home All Groups Group Topic Archive Search About

SQL 2005 Index properties

Author
18 Dec 2006 10:37 PM
kzakoski
When viewing index properties of an index using SQL Server Management Studio,
we see some errors and/or features that are disabled that we once were able
to view and change for SQL 2000 and 2005 instances.

Options are completely grayed out after applying 2005 SP2 CTP on a client
machine, for any PK and/or Unique constraint.  These options are available on
pre SP2 CTP clients, however FILL Factor percent never displays the original
fill factor that the index was set up with, instead it always displays 100%
for everything.  If your managing index fragmentation, this information is
useful in determining your changes.  Currently, you need to query the system
table or views to get this info.

We're using the new Management Studio to manage both 2000 and 2005 instances
and we can't manage indexes in 2000 very well with the tools.

Also, Fragmentation has never returned the correct fragmentation level. 
When actual logical frag level is 8.25%, logical fragmentation shows up 100
times larger as 825%.

--
Kevin Z

Author
18 Dec 2006 11:17 PM
Erland Sommarskog
kzakoski (kzako***@discussions.microsoft.com) writes:
> Options are completely grayed out after applying 2005 SP2 CTP on a
> client machine, for any PK and/or Unique constraint.  These options are
> available on pre SP2 CTP clients,

That's one of these changes that looks like it was made by design -
because else how could it happen? But, no, I don't really see the
reason why you should be able to do this. At least not on SQL 2005
which has syntax for it.

So I suggest that you go to https://connect.microsoft.com/SQLServer/Feedback
and submit a bug report for this. You will need a Passport login for
this.

If you post the URL to them, you may be able to whip up some votes for
them.

> however FILL Factor percent never displays the original
> fill factor that the index was set up with, instead it always displays
> 100%  for everything. 

Yes, I saw this. Submit a bug for this as well. (While it's convenient
to submit issues in the same bug report, it's easier to track them if
they are separate.)

> We're using the new Management Studio to manage both 2000 and 2005
> instances and we can't manage indexes in 2000 very well with the tools.

Are there any issues that are specific to SQL 2000? I don't not try
that.

> Also, Fragmentation has never returned the correct fragmentation level.
> When actual logical frag level is 8.25%, logical fragmentation shows up
> 100 times larger as 825%.

This I was not able to repeat, but the numbers that I saw appeear OK.
But maybe this is only happening on SQL 2000?


--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

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

AddThis Social Bookmark Button