SQL Server Performance Forum – Threads Archive
Cannot delete people from my db after indexing!!!
Hi, I explain you why… I have (to make it simple) 3 tables linked to eachother:-people: information about participants
-peopcont: contests in which every people is registered
-peopcontscore: score by contest by people (every contest can have more than one track and can then have many different scores) The joints have consistency check enabled. This means that when I delete one people, sql server also deletes related records in the two other tables. When I try doing so, i get following message:
The query has been canceled because the estimated cost of this query (7671) exceeds the configured threshold of 300. Contact the system administrator. I have looked at the execution plan and it appears that the reason why this query is so expensive is because it needs to delete an index (peopcontid) on the peopcontscore table. The cost of sorting the records by this index is of 98% of the total cost!!! When I remove the index, the delete operation can occur. The problem is that I don’t want to remove this index as it is frequently used in select statements. What can I do? Many thanks in advance, Nicolas.
Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate the plan. The cost threshold for parallelism option is actively used when a mix of short and longer queries is executed. The short queries execute serial plans while the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, thus executing only serial plans. The cost threshold for parallelism option can be set to any value from 0 through 32767 using SP_CONFIGURE. But this threshold value is relevant only in symmetrical multiprocessing (SMP) environments. Refer tohttp://www.sql-server-performance.com/sql_server_configuration_settings.asp link for more information. HTH Satya SKJ
Is the index you are deleting to fix your problem a primary key used to enforce referential integrity? The reason I ask is that like your problem may be related to referential integrity, and if it is, and the index you delete is used to enforce referential integrity, then this could explain your problem, but this is only a guess. Perhaps you can send us a copy of text version of the query plan so we can get a better look of your problem, because what you describe in the execution plan seem a little strange.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>