CODEWITHSUNDEEP
performancesql-server-2008indexingforeign-keysrebuild
Jesse Anderson
Jesse Anderson

Reputation: 31

Certain values for WHERE clause involving foreign key are slow

This query works:

SELECT [ID]
  ,[PROJECT_ID]
  ,[NAME]
  ,[LOCATION]
  ,[COMMENT]
FROM table1
WHERE PROJECT_ID = 4479

but this one doesn't:

SELECT [ID]
  ,[PROJECT_ID]
  ,[NAME]
  ,[LOCATION]
  ,[COMMENT]
FROM table1
WHERE PROJECT_ID = 3560

It spins and spins indefinitely. The only difference is the value used.

"PROJECT_ID" is a foreign key, and there is an index defined with it as the sole column (per usual with FKs).

In parallel: there is high fragmentation on the FK indexes on this table.

Are these probably related, ie, if I rebuild indexes should I expect the problem will be fixed?

I cannot rebuild them now as it is a production system and my understanding is I can only rebuild indexes when locks can be obtained... such as late tonight.

Any guidance on how to "force a rebuild of all indexes in all tables in a database even if it disconnects users" is also appreciated.

Thanks, - Jesse

Upvotes: 1

Views: 241

Answers (1)

mwigdahl
mwigdahl

Reputation: 16578

Are those queries encapsulated in a stored procedure? If so, you might be getting hit by parameter sniffing.

You might also check your statistics; it's possible that if they are very much out of date you could be getting a very poor execution plan with certain parameters and not with others.

Upvotes: 2

Related Questions