Exception on multiple indexes with identical name

florian kistner

Hello,

I am having trouble with identically named indexes in some of my databases on SQL Server 2008 R2.
It looks like the Dependency Tracker assumes that index names are globally unique, but this doesn't necessarily hold true.
Most prevalent example would be the _AUTO_IDX_1, which depending on the server configuration may be created automatically all over the place.

Category
Object Loading

Received
17.07.2014 14:35:19

Unable to load objects from database 'REDACTED': AARGH!!!! I've just encountered another object of type Index called '[dbo].[_AUTO_IDX_1]'!

AARGH!!!! I've just encountered another object of type Index called '[dbo].[_AUTO_IDX_1]'!
ReallyBadException

   bei RedGate.DependencyViewer.Engine.DatabaseObjects.AddObject(IDatabaseObjectWrapper obj)
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.GrabIndexes(ViewTableSuperClass vt)
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.GrabIndexes()
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.WrapObjects(ICancellable cancellable)
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.LoadObjects(LoadBehaviour behaviour)
   bei RedGate.DependencyViewer.Engine.Server.LoadObjects(LoadBehaviour behaviour)

My particular problem is that the Depedency Tracker doesn't resolve any dependencies of the added objects, if it stumples across the auto index.

I do get a lot of "Have no proxy for engine object" as well, but I am not sure these two are related:

Category
Application

Received
17.07.2014 14:35:19

Have no proxy for engine object #4271 REDACTED.DATABASE.c_fk_MyForeignKeyConstraint

Is this a known issue? Is there a workaround?
Can I tell the Depedency Walker, for example, to ignore all indexes and foreign key constraints? Or to just go on and resolve at least the other dependencies?

-- Florian

Brian Donahue

Hi Florian,

Thanks for contacting Red Gate. From my experience, system-generated index names contain a random string of numbers in the name to try to keep them unique. SQL Server should not allow identically-named objects (unless the database is case-sensitive and/or the object name is owned by a different schema).

Can you tell us what version of Dependency Tracker is doing this, and what objects were added that caused the error?

florian kistner

Hey Brian,

to be honest, I am not sure, how these indexes got there, but they don't contain anything random and are all owned by the same schema. We turned off automatic index generation a long time ago, but it seems like some are still around. Could have been some old performance tool that made this poor name choice as well. Anyway - since this is in production I am not going to be able to get rid of those in the short term.

I did some digging and it turns out, Microsoft actually does allow multiple indexes with identical names, as long as they are not created on the same object:

index_name
Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

See http://msdn.microsoft.com/en-us/library ... (v=sql.105).aspx

I am using Dependecy Tracker Version 2.8.1.164, which I downloaded just yesterday as part of the SQL Developer Bundle.

-- Florian

Brian Donahue

Thanks for pointing this out. I did create a representative sample of a set of tables with an index that is identically-named, and Dependency Tracker was able to add the objects to the view.

CREATE TABLE [23456] (
id int PRIMARY KEY,
col1 CHAR(10)
)
CREATE TABLE [34567] (
id INT PRIMARY KEY,
col2 CHAR(10)
)
CREATE INDEX [_MY_INDEX] ON [23456] (col1);
CREATE INDEX [_MY_INDEX] ON [34567] (col2)

I'll send you some debugging instructions via email, because we probably need some more information about this schema.

Aliraza70

Has anybody else experienced this problems? If so, what is the solution to get the dependency diagrams working for SQL Server 2012 (it seems to work fine for SQL Server 2008 databases though)? ???