SQL Server issues when reading columns using a foreign key
I have a weird situation where simple queries never end
eg
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
When "I mean" returns, a green checkmark appears in the Query Analyzer that says "The request was successful."
I sometimes get lines gridded in qa, but still the request continues until my client timeouts 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's going on. The choice probably doesn't work;)
I have a foreign key in 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 about 6000 lines and IsDiscontinued is a bit but not null, but excluding this condition does not change the result.
Can anyone tell me how to handle this kind of situation? More information, anyone?
Additional info: It doesn't look like this foreign key, but all / some refer to this object.
a source to share
A couple of things I would try to try and help diagnose the problem (you can just rule out everything):
Temporarily try a query that never returns using a NOLOCK or READPAST table hint that is.
SELECT top 1000 ArticleID FROM Article WITH (NOLOCK) WHERE ProductGroupID=379114
Does it return results or not? Perhaps if some row or page of data is locked somewhere (by some process that has a long-term lock for some reason), the request is held by it, which might appear in this case.
Also, run the issue query (WITHOUT the table prompt) in one SSMS window and note your SPID (the number in brackets in the bottom pane along with your logon account). In a separate window, repeat several times several times and see what it shows:
SELECT status, wait_type
FROM sys.dm_exec_requests
WHERE session_id = <YourQuerySPID>
There's a good link here on what the different wait types mean, and this could mean the fact that the request is waiting for something.
Update:
See this SO question for how to find blocked / blocking processes. - I don't want to steal votes from the answers there!
a source to share
- Do you have an index on the ProductGroupID column? If so your fragments are fragmented?
- Are your statistics up to date?
- Have you analyzed the generated query plans? They are the same?
When you tweak your query setup, you should aim to make sure you are comparing, for example, that each query is fetching a result set from disk rather than a buffer cache.
You can clear the buffer cache with the DBCC DROPCLEANBUFFERS command , however this is NOT often an option for a production database.
You will also want to make sure statistics are up to date for columns that are part of the WHERE clause predicates. This ensures that SQL Server determines the most optimal query plan to use based on the selectivity of your data.
a source to share
A couple of things - others have already pointed in these directions:
-
Do you have an index on your foreign key?
CONSTRAINT [FK_ProductGroup_Articles] FOREIGN KEY([ProductGroupID]) REFERENCES [dbo].[ProductGroup] ([ProductGroupID])
Foreign key creation does not automatically create an index on that foreign key column - contrary to popular belief.
If not, it will definitely help indexing
ProductGroupID
- either separately or in a composite index. -
Have you ever recreated and updated your stats? Have you entered a large amount of data recently?
Just run this command on those tables that are involved in your queries:
UPDATE STATISTICS (table name)
-
minor problem: if you are comparing a BIT column, I personally used
(IsDiscontinued = 0)
There is no benefit to putting that 0 in single quotes and thus making it a string - SQL Server just has to convert it back to BIT ....
a source to share
Foreign keys define a relation / constraint, you still need an index if you want to quickly find those values, 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 coversArticle.IsDiscontinued
a source to share