SQL Server problems reading columns with a foreign key?

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.

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 = 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 Limpens May 5 '10 at 16:37 is there a way to get to the locking sql statement? – Jan Limpens May 5 '10 at 16:46 @illdev See my update - that link (and the references it links to) should help you find the blocking query – AdaTheDev May 5 '10 at 17:08.

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.

Queryplans all seem normal, from what I can tell. – Jan Limpens May 5 '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 5 '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 5 '10 at 21:39.

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 Limpens May 5 '10 at 16:45.

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.

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 5 '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 Limpens May 5 '10 at 15:55.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.