SQL Server problems reading columns with a foreign key

Question

I have a weird situation, where simple queries seem to never finish

for instance

SELECT top 100 ArticleID FROM Article WHERE  ProductGroupID=379114

returns immediately

SELECT top 1000 ArticleID FROM Article WHERE  ProductGroupID=379114

never returns

SELECT ArticleID FROM Article WHERE  ProductGroupID=379114

never returns

SELECT top 1000 ArticleID FROM Article

returns immediately

By 'returning' I mean 'in query analyzer the green check mark appears and it says "Query executed successfully"'.

I sometimes get the rows painted to the grid in qa, but still the query goes on waiting for my client to time out - 'sometimes':

SELECT     
   ProductGroupID AS Product23_1_, 
   ArticleID AS ArticleID1_, 
   ArticleID AS ArticleID18_0_, 
   Inventory_Name AS Inventory3_18_0_, 
   Inventory_UnitOfMeasure AS Inventory4_18_0_, 
   BusinessKey AS Business5_18_0_, 
   Name AS Name18_0_, 
   ServesPeople AS ServesPe7_18_0_, 
   InStock AS InStock18_0_, 
   Description AS Descript9_18_0_, 
   Description2 AS Descrip10_18_0_, 
   TechnicalData AS Technic11_18_0_, 
   IsDiscontinued AS IsDisco12_18_0_, 
   Release AS Release18_0_, 
   Classifications AS Classif14_18_0_, 
   DistributorName AS Distrib15_18_0_, 
   DistributorProductCode AS Distrib16_18_0_, 
   Options AS Options18_0_, 
   IsPromoted AS IsPromoted18_0_, 
   IsBulkyFreight AS IsBulky19_18_0_, 
   IsBackOrderOnly AS IsBackO20_18_0_, 
   Price AS Price18_0_, 
   Weight AS Weight18_0_, 
   ProductGroupID AS Product23_18_0_, 
   ConversationID AS Convers24_18_0_, 
   DistributorID AS Distrib25_18_0_, 
   type AS Type18_0_
FROM         
   Article AS articles0_
WHERE     
   (IsDiscontinued = '0') AND (ProductGroupID = 379121)

shows this behavior.

I have no idea what is going on. Probably select is broken ;)

I got a foreign key on ProductGroups

ALTER TABLE [dbo].[Article] WITH CHECK ADD CONSTRAINT [FK_ProductGroup_Articles] 
FOREIGN KEY([ProductGroupID]) 
REFERENCES [dbo].[ProductGroup] ([ProductGroupID]) 
GO 
ALTER TABLE [dbo].[Article] CHECK CONSTRAINT [FK_ProductGroup_Articles] 

there are some 6000 rows and IsDiscontinued is a bit, not null, but leaving this condition out does not change the outcome.

Anyone can tell me how to handle such a situation? More info, anyone?

Additional Info: this does not seem to be restricted to this Foreign Key, but all/some referencing this entity.

Answer 1

• Do you have an index on the column ProductGroupID? If so are your indexes fragmented?
• Are your statistics up to date?
• Have you reviewed the Query Plans produced? Are they the same?

When you are performance tuning a query you should strive to ensure that you are comparing like for like, that is that each query is retrieving the result set from disk and not the buffer cache.

You can clear the buffer cache using the command DBCC DROPCLEANBUFFERS however this is NOT often an option for a production database.

You will also want to ensure that the statistics are up to date for the columns that form part of your WHERE clause predicates. This will ensure that SQL Server determines the most optimal query plan to use based on the selectivity of your data.

Answer 2

Foreign keys only define the relationship/constraint, you still need an index if you will want to find these values fast, so try this:

CREATE NONCLUSTERED INDEX IX_Article_ProductGroupID ON dbo.Article 
    (
    ProductGroupID 
    ) INCLUDE (IsDiscontinued) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
 ON [PRIMARY]
GO

it adds an index on Article.ProductGroupID and covers Article.IsDiscontinued

Answer 3

A few things that I would try out, to try and help diagnose the problem (may just rule things out):

Temporarily try the query that doesn't ever return with either a NOLOCK or READPAST table hint i.e.

SELECT top 1000 ArticleID FROM Article WITH (NOLOCK) WHERE ProductGroupID=379114

Does that return the results or not? Maybe if there's a row or data page locked somewhere (by some process that for some reason has a long-running lock), the query is being held up by it which this could show up as being the case.

Also, execute your problem query (WITHOUT the table hint) in one SSMS window, and note your SPID (the number in brackets in the bottom bar, alongside your login account) . In a separate window, run the following a few times repeatedly, and see what it shows:

SELECT status, wait_type
FROM sys.dm_exec_requests
WHERE session_id = <YourQuerySPID>

There's a good reference here on what the different wait types mean, and this could flag up the fact the query is waiting on something.

Update:
See this SO question on how to find blocked/blocking processes - I don't want to steal votes away from the answers in there!

Answer 4

Couple things - others have already pointed in those directions:

• do you have an index on your foreign key??

  CONSTRAINT [FK_ProductGroup_Articles] 
  FOREIGN KEY([ProductGroupID]) 
  REFERENCES [dbo].[ProductGroup] ([ProductGroupID]) 
  

  Creating a foreign key does not automatically create an index on that foreign key column - contrary to popular belief.

  If not - it would definitely help to index ProductGroupID - either separately or in a compound index.

• have you ever re-create and updated your statistics? Have you recently inserted a great amount of data?

  Simply run this command on those tables involved in your queries:

  UPDATE STATISTICS (table name)
  

• minor issue: if you compare against a BIT column, I would personally use

  (IsDiscontinued = 0) 
  

  There's no benefit in putting that 0 into single quotes and thus making it a string - SQL Server just has to convert it back to a BIT....