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.

What indexes are there (is there one on `ProductGroupID`, and what type)? How much data is there? Is `IsDiscontinued` a `bit`? — Marc Gravell, May 05 '10 at 15:37
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. — Jan, May 05 '10 at 15:41
Is concurrency a possible issue? Are you the only user accessing the server when you run these tests? — Philip Kelley, May 05 '10 at 15:43
When you say "never returns", how long have you waited? I know you don't want to wait hours, but I wonder if there are some broken indexes or disk problems that are preventing SQL from loading the data it needs. Maybe some extreme level of fragmentation in the files that allows SQL to select the first 100 rows without trouble, but then stops cold when it selects 1000. — SqlRyan, May 05 '10 at 15:43
@ Philip Kelley: this is on a website. So this could potentially be an issue. But I am not sure what to make of this... — Jan, May 05 '10 at 15:45
@rwmnau: it seems to start getting problems as soon as I request more than there is. I have a hunch that the indices could be broken, but should not sql server take care of this? — Jan, May 05 '10 at 15:46
@rwmnau: I waited some 30 minutes once, just to be sure. I think this falls into the 'either works immediately or dies' category of things — Jan, May 05 '10 at 15:59

score 2 · Accepted Answer · edited May 23 '17 at 10:27

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!

Aha! The query works without locks and the 2nd query returns suspended LCK_M_S Unfortunately the linked document does not tell me how to resolve this... — Jan, May 05 '10 at 16:37
@illdev See my update - that link (and the references it links to) should help you find the blocking query — AdaTheDev, May 05 '10 at 17:08

score 1 · Answer 2 · answered May 05 '10 at 15:45

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.

answered May 05 '10 at 15:45

John Sansom

41,005
9
72
84

ProductGroupID is in a Foreigh Key, so I assume the index is there, right? Statistics - cannot tell - where should I look? Queryplans all seem normal, from what I can tell. – Jan May 05 '10 at 15:58
1

@ildev, Foreign keys do not automatically get indexes created. This is probably the very first thing you need to do. PRimary keys are automatically indexed, FKs are not. – HLGEM May 05 '10 at 17:50
@ildev: As HLGEM kindly points out, you should place nonclustered indexes on your foreign key columns. You want to validate that your WHERE predicate is selected via an Index Seek operation by reviewing the Query Plan. – John Sansom May 05 '10 at 21:39

score 1 · Answer 3 · answered May 05 '10 at 16:23

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....

Thanks, Marc, that's very useful info, doesn't seem to apply in my case though... — Jan, May 05 '10 at 16:45

score 0 · Answer 4 · answered May 05 '10 at 15:46

0

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

answered May 05 '10 at 15:46

KM.

101,727
34
178
212

if you do not have this index, your query is doing a table scan, and is subject being blocked/locked by active transactions on the table. With the index, you read the rows you want and are less likely to hit a blocked/lock from a transaction. – KM. May 05 '10 at 15:53

I tried it with

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

and this query, too, never returns. – Jan May 05 '10 at 15:55

SQL Server problems reading columns with a foreign key

4 Answers4