Indexes are not visible

Question

Indexes are not visible

ramana3327

SSCoach

Points: 19722
More actions
February 17, 2016 at 3:51 pm

#316039

Hi,
When I run sp_helpindex @objname='Testtable'
I got 10 indexes. 1 clustered and 9 non-clustered but when I see the node Indexes under that table, I can able to see only clustered index. I can't able to see the remaining index.
Why I can't able to see those indexes from SSMS?
We are using SQL 2008r2 standard eidition

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Try doing a refresh? Maybe you have an old version of SSMS (just because you patch the server, doesn't patch your client software)?

You should be able to see database objects.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ramana3327 SSCoach Points: 19722 More actions · Answer 2

I am running from the local machine and we didn't upgrade the instance. Also I refreshed the db & Tables

But we restored the database from SQL 2005 to this instance (SQL 2008r2) but didn't change the compatibility to SQL 2008r2 because didn't test the application with new compatability. So just left the compatability to 90

Is that could effect the visibility of the indexes from SSMS?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Are they hypothetical indexes (from DTA)? Don't know if those show un with helpindex, but they won't in SSMS.

Try querying sys.indexes directly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

ramana3327 SSCoach Points: 19722 More actions · Answer 4

I am not sure those are created by DTA or some other tool but the description is

nonclustered, hypothetical located on PRIMARY

index name is IX_SQLdoctor_8_5_95f565ef22664301a95c1840be217031

sqlquaker Say Hey Kid Points: 697 More actions · Answer 5

See this article on Hypothetical indexes >> https://msdn.microsoft.com/en-us/library/ms190172(SQL.90).aspx

Use this query to list Hypothetical indexes:

SELECT *

FROM sys.indexes

WHERE is_hypothetical = 1

-Regards

ramana3327 SSCoach Points: 19722 More actions · Answer 6

Those are hypothetical indexes. Is it better to keep or drop those hypothetical index?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

ramana3327 (2/19/2016)
Those are hypothetical indexes. Is it better to keep or drop those hypothetical index?

Google is your friend here...

https://www.google.com/?gws_rd=ssl#q=deleting+hypothetical+indexes+and+statistics

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 8

ramana3327

SSCoach

Points: 19722

February 24, 2016 at 11:36 am

#1860839

Thank you all