dcsimg
Results 1 to 12 of 12

Thread: [RESOLVED] How to display index properties in SSMS

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Resolved [RESOLVED] How to display index properties in SSMS

    v17.9.1
    I feel stupid for asking, but I'm trying to learn some things about indexing and fragmentation and I see many tutorials that show a user highlighting the index and displaying it's properties. I have my properties pane open but when I highlight the index, the properties window doesn't change. It still shows the connection properties.
    In the articles they say "Right click the index and select properties." but when i right click the index, there is no option to display properties. What am I doing wrong? How do I view the index properties?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

    Re: How to display index properties in SSMS

    The Properties window only shows the properties of an object and its direct relations when it's open in the design window. Open the Object Explorer window, expand your database to see the tables, right-click a table and select Design. The table will be opened in the designer and then you will get some options in the drop-down at the top of the Properties window. They should include the table itself, its indexes and keys and maybe others too, e.g. triggers. If you expand further in the Object Explorer, you can right-click an index and select Properties and that will open a dialogue with many more properties of the index.

    Note that I have v18.1 of SSMS installed but, as far as I can recall, it's the same as your version in this regard. If you try to follow my instructions and it doesn't work, post some screenshots of exactly what you're doing.

  3. #3

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: How to display index properties in SSMS

    Thank you. I upgraded to 18.2 just to make sure we were the same. I see now how to get to that properties window in the first part. But that's the design properties. I was looking at examples online where they would right click the index and there was an option for properties. When one opened that properties, it showed statistics on many things including fragmentation.
    See this web page. Go to the paragraph that starts with "I'll talk about ways to analyze fragmentation later..." about 20% down the page. There's a cool properties window that shows the fragmentation. They say to get to it to right click the index. But when I right click on my index I see this:
    Name:  Right click index menu.jpg
Views: 72
Size:  13.6 KB
    Is it because I'm using Azure? I went to my local SQL Express and I can get to these properties. Many more right click options.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,607

    Re: How to display index properties in SSMS

    Quote Originally Posted by cory_jackson View Post
    I upgraded to 18.2 just to make sure we were the same.
    Hmmm... the 'Check for updates' option in 18.1 doesn't show anything available, I wonder whether that only looks for 18.1.x versions or they just haven't exposed 18.2 to that functionality yet.
    Quote Originally Posted by cory_jackson View Post
    Is it because I'm using Azure?
    It seems so. I just checked the same database on our server at work and on Azure and I could access those properties for the former but not for the latter. I haven't looked but I wonder whether the equivalent information is accessible via the Azure portal.

  5. #5

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: How to display index properties in SSMS

    18.2: Dunno. I went out and downloaded it form the site.

    Azure portal.... That's a grand idea. I'll check later. Thank you.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: How to display index properties in SSMS

    You might find this helpful.

    Basically, Azure doesn't readily expose index fragmentation stats to you because fragmentation shouldn't be a problem. Azure uses Column Store indexes by default rather than conventional BTree indexes and they're not subject to fragmentation. Where you specifically configure it to use BTrees they're held in solid state memory rather than disks so, again, fragmentation shouldn't matter.

    You can get at those properties using manual scripts etc. rather than SSMS but the intention is that you shouldn't have to. If this is purely a learning exercise you'd be better off installing a local version of sql server and playing with that.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,300

    Re: How to display index properties in SSMS

    You might find this helpful.

    Basically, Azure doesn't readily expose index fragmentation stats to you because fragmentation shouldn't be a problem. Azure uses Column Store indexes by default rather than conventional BTree indexes and they're not subject to fragmentation. Where you specifically configure it to use BTrees they're held in solid state memory rather than disks so, again, fragmentation shouldn't matter.

    You can get at those properties using manual scripts etc. rather than SSMS but the intention is that you shouldn't have to. If this is purely a learning exercise you'd be better off installing a local version of sql server and playing with that.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: How to display index properties in SSMS

    Funky I thank you for exposing me to Columnstore. I had not heard of this and am boning up on it now. But I have an MS tech who has helped with some scripts to display fragmentation. And it appears very bad. I looked and I think Columnstore is only for S3 and above. I found it in a few places, I think this is the most recent. The client is S2 and 50 DTU I think. Do you think we have Columnstore? Is there some way I could tell on the portal?

  9. #9

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: How to display index properties in SSMS

    John I poked around in the portal and I can't find anything that shows fragmentation. But the tech gave me some queries and they show much fragmentation.

    I have another request. I understand 8k pages of data and how things become fragmented with activity... But I'm struggling to understand how the actual data fragmentation seem to be regarded online as the same as the index fragmentation. An index to me seems like another specialized, organized, table for quick lookup. Different from the actual data. So why online do experts refer to indexes as being fragmented and not data? The best I can figure is that somehow there's a sympathetic relationship where if one is fragmented, the other is equally fragmented. But what I don't get with that is how de-fragmenting an index also defrags the actual data. Can someone point me to a white paper or something that explains this? Said another way, why when the data is fragmented, do all the instructions out there suggest de-fragmenting the index?

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,039

    Re: How to display index properties in SSMS

    Quote Originally Posted by cory_jackson View Post
    An index to me seems like another specialized, organized, table for quick lookup. Different from the actual data.
    Correct, it is basically a sub-set of the main data (containing whichever fields you specified as the index), and a link to the full data.

    But what I don't get with that is how de-fragmenting an index also defrags the actual data.
    It doesn't, it just makes the data defragmentation irrelevant.

    Most of the time data is found entirely by using the indexes, and fragmentation there matters because that slows down access to the indexes. The fragmentation of the data itself doesn't really matter, because the index specifies exactly where in the data the relevant row is.

    If you have any queries that don't make use of indexes then de-fragmenting the data would help them, but the chances are that you would have re-written those queries already because they would have run very slowly (either that or the speed of those queries doesn't matter to you).

  11. #11

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: How to display index properties in SSMS

    si_the_geek that's kind of what I was starting to think. Thank you for setting that straight in my mind. The queries are running very slow and some use columns that are not indexed. But the first condition or two of the query uses a columns that are indexed and should make plowing though the rows a minimal consumption of time. However, from looking at the execution plans it seems SQL Server creates an index on the fly for these things so I might need to see about indexing more columns. But I'm having troubles understanding how that works and how to do it best. Indexing where there's a single column being used for the criteria makes sense to me, but I'm struggling how one does indexes for multiple columns. Or if they're even needed.
    Right now I have a query that uses a single column that is not the PK and that column is indexed. And it's taking as long as a half an hour at S2 and 50 DTU. I've noticed that older rows work quicker so it seems likely this is a problem with the index being so fragmented. I'm doing a test now and have a query from MS to de-fragment them and something else. Clearing some kind of execution cache or something. I don't understand their query but I'll run it and see. I wish databases didn't require so much work to maintain.

  12. #12

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    896

    Re: How to display index properties in SSMS

    Going back to my original question, I think this has been answered. In Azure FTP the properties page does not exist. One must use a query to get that information.
    Thank you again everyone for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width