Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Can't Delete Index b/c of Constraint Expand / Collapse
Author
Message
Posted Friday, November 28, 2008 8:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:18 PM
Points: 205, Visits: 641
Gail,

This did the trick nicely, thank you. However, I'm seeing that my processors are tracking extremely high now. Any idea why this would be happening?

Thanks,
Nate
Post #610379
Posted Friday, November 28, 2008 12:24 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
It could be anything. That's almost the database equivalent of asking "Why's my car making a strange noise?"

Probably you have some badly performing queries. Run profiler for a while and see what has high values for CPU and see if you can tune those queries. It may also be excessive compiles as by changing the indexes on that table, all query plans that use that table are now invalid and will have to be recompiled when the queries run. If that's the case, the cpu usage will drop off.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #610520
Posted Friday, November 28, 2008 12:27 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:18 PM
Points: 205, Visits: 641
Makes sense, thanks!
Post #610525
Posted Wednesday, July 13, 2011 1:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
Gail,

I ran into this. I don't need help resolving, but I did want to know if you found where the behavior is documented. This is my scenario.

Create table called main with a primary key column called MainID with an identity 1,1 and other columns if you wish. Do not set the pk to clustered index but only as unique.
create a table called SubMain with an FK to PK on main.

Later create a unique clustered index on on MainID call it IDX_UQ_CL_MainID.

Just to test go an delete it. To prove you can delete it.

Then add it back. So it will be there for the next step.

Drop your FK in SubMain
add back your FK in SubMain

Try to delete the IDX_UQ_CL_MainID. You will get the error in this article.

It seems that SQL Server uses the best index at the time an FK is created. Best meaning if there is a unique cluster index on the PK field then use it, if not use the next best index.

Do you know of any documentation or setting that controls this. During the ALTER table ADD FK, you can only specify the column not the index that is used so i see no way to control this.

I can see how to detect it and write better schema change scripts, but not how to prevent it in the first place...Assuming you have to add the indexes in the order specified based on a preexisting schema, or if you just inherit the problem.

Ill work on getting the above in code if you have any trouble reproducing.


Jimmy

"I'm still learning the things i thought i knew!"
Post #1141267
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse