Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlh
Starting Member
28 Posts |
 Posted - 2010-08-18 : 15:53:29
|
I'm seeking an explanation for why my query is running forever. The following works fine and produces a 50,000 row result in 20 seconds. But when I uncomment the last line, it never finishes.select *from MyTable join MyIndex as i1 on i1.IndexValue = MyTable.column1 and i1.DocID = i1.DocID join MyIndex as i2 on i2.IndexValue = MyTable.column2 and i2.DocID = i1.DocID join MyIndex as i3 on i3.IndexValue = MyTable.column3 and i3.DocID = i1.DocIDwhere i1.IndexType = 1 and i2.IndexType = 2 --and i3.IndexType = 3 I know this query was working before, so there must be something about the database itself that's preventing it from working now. Can anyone recommend how to diagnose & find the cause?Another symptom: the above query also never finishes if I replace the select * by any of the tables involved in the last join:select MyTable.*select i1.*select i3.* |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-08-18 : 16:03:54
|
quote:
Originally posted by tkizer
select * from master.dbo.sysprocesses where blocked <> 0
That returns 0 rows. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-08-18 : 16:13:06
|
quote:
Originally posted by tkizer
Did you run it while the query was running? It must be running in order to catch it.
Thanks, I didn't understand that before. But the blocking query still returns 0 rows while my query is running. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-18 : 16:13:50
|
is IndexType indexed? if so, is it selective enough to be useful? what does the estimated execution plan look like? how many records exist in MyIndex?and might as well remove this condition:and i1.DocID = i1.DocID though thats not the proble, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-18 : 16:15:14
|
| you ran the query Tara gave you in a seperate window with the long running query running? |
|
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-08-18 : 16:29:27
|
quote:
Originally posted by russell
is IndexType indexed? if so, is it selective enough to be useful? what does the estimated execution plan look like? how many records exist in MyIndex?
IndexType is indexed. I'm not sure what the important properties of the index are, but it says Page Fullness = 84%, Average Row Size = 27, which I suspect are fine. There are about 3 million rows in MyIndex.How do I go about posting an estimated execution plan?quote:
Originally posted by russell
you ran the query Tara gave you in a seperate window with the long running query running?
Yes.quote:
Originally posted by tkizer
Could you post your actual query and the execution plan?
Here's the actual query I'm running. There's really no difference other than the real object names and some collation. I'll emphasize this was working fine previously.select *from Viewpoint.dbo.bPMSM inner join PaperlessEnvironments.dbo.DocumentIndex di1 on di1.IndexValue = bPMSM.Submittal collate SQL_Latin1_General_CP1_CI_AS and di1.DocumentID = di1.DocumentID inner join PaperlessEnvironments.dbo.DocumentIndex di2 on di2.IndexValue = bPMSM.SubmittalType collate SQL_Latin1_General_CP1_CI_AS and di2.DocumentID = di1.DocumentID inner join PaperlessEnvironments.dbo.DocumentIndex di3 on di3.IndexValue = bPMSM.Project collate SQL_Latin1_General_CP1_CI_AS and di3.DocumentID = di1.DocumentIDwhere di1.IndexTypeID = 140001 and di2.IndexTypeID = 140000 and di3.IndexTypeID = 100011 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-18 : 19:02:44
|
| SET SHOWPLAN ONI'd like to see definitions of Viewpoint.dbo.bPMSM and PaperlessEnvironments.dbo.DocumentIndex with all indexes included.Are either (or both) of these VIEWS?If they're tables, might try updating statistics with fullscan, but 1st lets see the definitions |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-08-18 : 21:31:12
|
quote:
Originally posted by russell
SET SHOWPLAN ONI'd like to see definitions of Viewpoint.dbo.bPMSM and PaperlessEnvironments.dbo.DocumentIndex with all indexes included.Are either (or both) of these VIEWS?
Both are tables, not views. I'm hesitant to use SET SHOWPLAN_ALL ON because this is a production database and other processes are using it.Here are the relevant parts of the table definitions:create table Viewpoint.dbo.bPMSM( Project varchar(10) NOT NULL, Submittal varchar(10) NOT NULL, SubmittalType varchar(10) NOT NULL, --35 other irrelevant columns --no keys or other indexes)create table PaperlessEnvironments.dbo.DocumentIndex( ID int identity(1,1) NOT NULL, DocumentID int NOT NULL, IndexTypeID int NOT NULL, IndexValue varchar(200) NULL, --a few other irrelevant columns constraint PK_DocumentIndex primary key clustered ( ID asc ))create nonclustered index [IndexType, IndexValue] on PaperlessEnvironments.dbo.DocumentIndex ( IndexTypeID asc, IndexValue asc ) quote:
Originally posted by tkizer
russell mentioned this earlier, but I see it's still in there, so I'll mention it:Remove "and di1.DocumentID = di1.DocumentID" from your query. Have you tried doing a COUNT(*) on this thing? Perhaps it's returning a ton of data.
I'm aware of the redundancy in the join-conditions -- it's there because the query is automatically generated and I didn't bother to program an exclusion for the trivial case. As was said, it's definitely not the source of my problem.Per my original post, the less restrictive result set (with the last line commented out) is about 50,000 rows. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-08-19 : 07:01:38
|
quote:
Originally posted by dlhI'm aware of the redundancy in the join-conditions -- it's there because the query is automatically generated and I didn't bother to program an exclusion for the trivial case. As was said, it's definitely not the source of my problem.Per my original post, the less restrictive result set (with the last line commented out) is about 50,000 rows.
Found the problem!  http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-19 : 09:11:30
|
set showplan on only affects your session...and...i agree with Don  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-19 : 09:16:40
|
by the way, it's not the number of rows that i'm particularly interested in...it's the number of reads and scans.it's possible (even likely) that you're scanning the same table 3 times -- PaperlessEnvironments -- how big is that table?also, how often are records inserted? it is likely that your stats are no good. common issue with identity and datetime fields with frequent inserts.might tryUPDATE STATISTICS dbo.PaperlessEnvironments WITH FULLSCAN EDIT: One more thing...if you're uncomfortable working in the live environment (as u should be :) ) restore a backup to the dev environment and troubleshoot it there. Once you have the fix, only then apply it to live. |
|
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-08-19 : 13:23:25
|
By the way, thank you very much for taking the time to look at this.quote:
Originally posted by russell
set showplan on only affects your session...and...i agree with Don
Thanks for the clarification on SHOWPLAN.I really believe that a single redundant A = A clause cannot be the reason this simple SQL statement takes hours to complete (when it previously ran in ~1 minute). Red herring.  quote:
Originally posted by russell
it is likely that your stats are no good. common issue with identity and datetime fields with frequent inserts.
I see that statistics on the DocumentIndex table are about a week old. How do I determine what are the current settings for when statistics will be automatically recalculated?I'm still not sure how to post an entire execution plan. Perhaps certain columns would be helpful? Is the sum of the TotalSubtreeCost column a meaningful number that is comparable from one query to another? Where can I find total numbers of reads & scans? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-19 : 13:59:06
|
set the output to text and show us the entire result. and of course, i made a typo (guess u figured that out) it's SET SHOWPLAN_ALL ONalso SET STATISTICS IO ON and show us the output from that please. do one at a time.Do you have auto_update_statistics on in your databases? Please show us the output of thisSELECT name, is_auto_update_stats_onFROM sys.databasesWHERE name in ('PaperlessEnvironments', 'Viewpoint') |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-19 : 14:03:01
|
| by the way, when a query that performs fine suddenly stops performing, it is almost always either an index went missing (someone dropped it) or bad statistics.the output of the statements we're asking you for will help us (and you) figure out what it is. |
|
|
|
dlh
Starting Member
28 Posts |
Posted - 2010-08-19 : 16:40:50
|
Okay. Comparing two variations on the query. This first one finishes in ~20 seconds.select *from Viewpoint.dbo.bPMSM inner join PaperlessEnvironments.dbo.DocumentIndex di1 on di1.IndexValue = bPMSM.Submittal collate SQL_Latin1_General_CP1_CI_AS and di1.DocumentID = di1.DocumentID inner join PaperlessEnvironments.dbo.DocumentIndex di2 on di2.IndexValue = bPMSM.SubmittalType collate SQL_Latin1_General_CP1_CI_AS and di2.DocumentID = di1.DocumentID inner join PaperlessEnvironments.dbo.DocumentIndex di3 on di3.IndexValue = bPMSM.Project collate SQL_Latin1_General_CP1_CI_AS and di3.DocumentID = di1.DocumentIDwhere di1.IndexTypeID = 140001 and di2.IndexTypeID = 140000 IO Statistics:Table 'DocumentIndex'. Scan count 273237, logical reads 1886141, physical reads 5093, read-ahead reads 2824, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'bPMSM'. Scan count 5, logical reads 560, physical reads 3, read-ahead reads 501, lob logical reads 67572, lob physical reads 356, lob read-ahead reads 0.Actual Execution Plan:Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------45859 1 select... 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 7.954604 NULL NULL SELECT 0 NULL45859 1 |--Filter(WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexValue] as [di2].[IndexValue]=[Expr1010])) 1 2 1 Filter Filter WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexValue] as [di2].[IndexValue]=[Expr1010]) NULL 1 0 2.342996E-06 4394 7.954604 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 0 158423 1 |--Parallelism(Gather Streams) 1 3 2 Parallelism Gather Streams NULL NULL 4.881242 0 0.02930971 4400 7.954602 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 158423 4 |--Nested Loops(Inner Join, OUTER REFERENCES:([di2].[ID]) OPTIMIZED) 1 4 3 Nested Loops Inner Join OUTER REFERENCES:([di2].[ID]) OPTIMIZED NULL 4.881242 0 1.02018E-05 4400 7.925292 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 158423 4 |--Nested Loops(Inner Join, OUTER REFERENCES:([di3].[DocumentID])) 1 6 4 Nested Loops Inner Join OUTER REFERENCES:([di3].[DocumentID]) NULL 4.881242 0 4.175915E-05 4378 7.921385 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 162713 4 | |--Nested Loops(Inner Join, OUTER REFERENCES:([di3].[ID]) OPTIMIZED) 1 7 6 Nested Loops Inner Join OUTER REFERENCES:([di3].[ID]) OPTIMIZED NULL 19.98045 0 4.175915E-05 4366 7.91506 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 162713 4 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([di1].[DocumentID], [Expr1009], [Expr1014]) OPTIMIZED WITH UNORDERED PREFETCH) 1 9 7 Nested Loops Inner Join OUTER REFERENCES:([di1].[DocumentID], [Expr1009], [Expr1014]) OPTIMIZED WITH UNORDERED PREFETCH NULL 19.98045 0 0.02332972 4362 7.908734 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 1194551 4 | | | |--Filter(WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID])) 1 12 9 Filter Filter WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]) NULL 11162.55 0 0.002679011 4339 4.913255 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 1194551 4 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([di1].[ID], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH) 1 13 12 Nested Loops Inner Join OUTER REFERENCES:([di1].[ID], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH NULL 11162.55 0 0.02332972 4339 4.910576 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 1194551 4 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011], [Expr1012]) WITH UNORDERED PREFETCH) 1 16 13 Nested Loops Inner Join OUTER REFERENCES:([Expr1011], [Expr1012]) WITH UNORDERED PREFETCH NULL 11162.55 0 0.03340865 4327 3.119323 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 10 0 | | | | | |--Compute Scalar(DEFINE:([Expr1009]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Project],0), [Expr1010]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[SubmittalType],0), [Expr1011]=CONVERT(varchar(10),[Vie 1 18 16 Compute Scalar Compute Scalar DEFINE:([Expr1009]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Project],0), [Expr1010]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[SubmittalType],0), [Expr1011]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Submittal],0)) [Expr1009]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Project],0), [Expr1010]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[SubmittalType],0), [Expr1011]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Submittal],0) 15985 0 0.00079925 4318 0.3831649 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 115985 4 | | | | | | |--Clustered Index Scan(OBJECT:([Viewpoint].[dbo].[bPMSM].[biPMSM])) 1 19 18 Clustered Index Scan Clustered Index Scan OBJECT:([Viewpoint].[dbo].[bPMSM].[biPMSM]) [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph 15985 0.3734954 0.00887025 4293 0.3823656 [Viewpoint].[dbo].[bPMSM].[PMCo], [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType], [Viewpoint].[dbo].[bPMSM].[Rev], [Viewpoint].[dbo].[bPMSM].[Description], [Viewpoint].[dbo].[bPMSM].[Ph NULL PLAN_ROW 1 1194551 15985 | | | | | |--Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexType, IndexValue] AS [di1]), SEEK:([di1].[IndexTypeID]=(140001) AND [di1].[IndexValue]=[Expr1011]) ORDERED FORWARD) 1 27 16 Index Seek Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexType, IndexValue] AS [di1]), SEEK:([di1].[IndexTypeID]=(140001) AND [di1].[IndexValue]=[Expr1011]) ORDERED FORWARD [di1].[ID], [di1].[IndexTypeID], [di1].[IndexValue] 1 0.003125 0.0001581 30 2.702749 [di1].[ID], [di1].[IndexTypeID], [di1].[IndexValue] NULL PLAN_ROW 1 15985194551 194551 | | | | |--Clustered Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di1]), SEEK:([di1].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di1].[ID]) LOOKUP ORDERED FORWARD) 1 29 13 Clustered Index Seek Clustered Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di1]), SEEK:([di1].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di1].[ID]) LOOKUP ORDERED FORWARD [di1].[DocumentID], [di1].[DocumentTypeID], [di1].[ListGroupNo] 1 0.003125 0.0001581 19 1.767924 [di1].[DocumentID], [di1].[DocumentTypeID], [di1].[ListGroupNo] NULL PLAN_ROW 1 11162.5562713 194551 | | | |--Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID, DocumentTypeID, IndexTypeID, IndexValue] AS [di3]), SEEK:([di3].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1 1 38 9 Index Seek Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID, DocumentTypeID, IndexTypeID, IndexValue] AS [di3]), SEEK:([di3].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]), WHERE:([PaperlessEnvironment [di3].[ID], [di3].[DocumentID], [di3].[DocumentTypeID], [di3].[IndexTypeID], [di3].[IndexValue] 1 0.003125 0.0002299709 38 2.616713 [di3].[ID], [di3].[DocumentID], [di3].[DocumentTypeID], [di3].[IndexTypeID], [di3].[IndexValue] NULL PLAN_ROW 1 11162.5562713 62713 | | |--Clustered Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di3]), SEEK:([di3].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di3].[ID]) LOOKUP ORDERED FORWARD) 1 40 7 Clustered Index Seek Clustered Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di3]), SEEK:([di3].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di3].[ID]) LOOKUP ORDERED FORWARD [di3].[ListGroupNo] 1 0.003125 0.0001581 11 0.00628391 [di3].[ListGroupNo] NULL PLAN_ROW 1 19.9804558423 62713 | |--Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IX_DocumentIndexDocumentIDIndexTypeID] AS [di2]), SEEK:([di2].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di3].[DocumentID] AND [di2] 1 44 6 Index Seek Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IX_DocumentIndexDocumentIDIndexTypeID] AS [di2]), SEEK:([di2].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di3].[DocumentID] AND [di2].[IndexTypeID]=(140000)) ORDERED FOR [di2].[ID], [di2].[DocumentID], [di2].[IndexTypeID] 1 0.003125 0.0001581 19 0.00628391 [di2].[ID], [di2].[DocumentID], [di2].[IndexTypeID] NULL PLAN_ROW 1 19.9804558423 58423 |--Clustered Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di2]), SEEK:([di2].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di2].[ID]) LOOKUP ORDERED FORWARD) 1 46 4 Clustered Index Seek Clustered Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di2]), SEEK:([di2].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di2].[ID]) LOOKUP ORDERED FORWARD [di2].[DocumentTypeID], [di2].[IndexValue], [di2].[ListGroupNo] 1 0.003125 0.0001581 119 0.003896724 [di2].[DocumentTypeID], [di2].[IndexValue], [di2].[ListGroupNo] NULL PLAN_ROW 1 4.881242 Now the 2nd variation on the query. Exactly the same except it includes only a single column. Runs "forever"...select di1.IDfrom Viewpoint.dbo.bPMSM inner join PaperlessEnvironments.dbo.DocumentIndex di1 on di1.IndexValue = bPMSM.Submittal collate SQL_Latin1_General_CP1_CI_AS and di1.DocumentID = di1.DocumentID inner join PaperlessEnvironments.dbo.DocumentIndex di2 on di2.IndexValue = bPMSM.SubmittalType collate SQL_Latin1_General_CP1_CI_AS and di2.DocumentID = di1.DocumentID inner join PaperlessEnvironments.dbo.DocumentIndex di3 on di3.IndexValue = bPMSM.Project collate SQL_Latin1_General_CP1_CI_AS and di3.DocumentID = di1.DocumentIDwhere di1.IndexTypeID = 140001 and di2.IndexTypeID = 140000 IO Statistics:Not available (execution aborted)Estimated Execution Plan:StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------select di1.ID... 1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 6.956594 NULL NULL SELECT 0 NULL |--Parallelism(Gather Streams) 1 2 1 Parallelism Gather Streams NULL NULL 1 0 0.02850187 11 6.956594 [di1].[ID] NULL PLAN_ROW 1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([di2].[DocumentID], [Expr1009]) OPTIMIZED) 1 3 2 Nested Loops Inner Join OUTER REFERENCES:([di2].[DocumentID], [Expr1009]) OPTIMIZED NULL 1 0 1.681385E-05 11 6.928092 [di1].[ID] NULL PLAN_ROW 1 1 |--Filter(WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di2].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID])) 1 5 3 Filter Filter WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di2].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]) NULL 8.044907 0 0.0007677551 24 6.922821 [di1].[ID], [di2].[DocumentID], [Expr1009] NULL PLAN_ROW 1 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([di2].[ID], [Expr1015]) OPTIMIZED WITH UNORDERED PREFETCH) 1 6 5 Nested Loops Inner Join OUTER REFERENCES:([di2].[ID], [Expr1015]) OPTIMIZED WITH UNORDERED PREFETCH NULL 3198.979 0 0.006685867 28 6.922053 [di1].[ID], [di1].[DocumentID], [di2].[DocumentID], [Expr1009] NULL PLAN_ROW 1 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1014]) WITH UNORDERED PREFETCH) 1 9 6 Nested Loops Inner Join OUTER REFERENCES:([Expr1010], [Expr1014]) WITH UNORDERED PREFETCH NULL 3198.979 0 0.02683339 28 6.406408 [di1].[ID], [di1].[DocumentID], [di2].[ID], [Expr1009] NULL PLAN_ROW 1 1 | | |--Filter(WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID])) 1 11 9 Filter Filter WHERE:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di1].[DocumentID]) NULL 11163.32 0 0.002679196 29 4.600796 [di1].[ID], [di1].[DocumentID], [Expr1009], [Expr1010] NULL PLAN_ROW 1 1 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([di1].[ID], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH) 1 12 11 Nested Loops Inner Join OUTER REFERENCES:([di1].[ID], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH NULL 11163.32 0 0.02333133 29 4.598116 [di1].[ID], [di1].[DocumentID], [Expr1009], [Expr1010] NULL PLAN_ROW 1 1 | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011], [Expr1012]) WITH UNORDERED PREFETCH) 1 15 12 Nested Loops Inner Join OUTER REFERENCES:([Expr1011], [Expr1012]) WITH UNORDERED PREFETCH NULL 11163.32 0 0.03340865 25 2.80674 [di1].[ID], [Expr1009], [Expr1010] NULL PLAN_ROW 1 1 | | | | |--Compute Scalar(DEFINE:([Expr1009]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Project],0), [Expr1010]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[SubmittalType],0), [Expr1011]=CONVERT(varchar(10),[Viewpoin 1 17 15 Compute Scalar Compute Scalar DEFINE:([Expr1009]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Project],0), [Expr1010]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[SubmittalType],0), [Expr1011]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Submittal],0)) [Expr1009]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Project],0), [Expr1010]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[SubmittalType],0), [Expr1011]=CONVERT(varchar(10),[Viewpoint].[dbo].[bPMSM].[Submittal],0) 15985 0 0.00079925 33 0.07057228 [Expr1009], [Expr1010], [Expr1011] NULL PLAN_ROW 1 1 | | | | | |--Index Scan(OBJECT:([Viewpoint].[dbo].[bPMSM].[biKeyID])) 1 18 17 Index Scan Index Scan OBJECT:([Viewpoint].[dbo].[bPMSM].[biKeyID]) [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType] 15985 0.06090278 0.00887025 33 0.06977303 [Viewpoint].[dbo].[bPMSM].[Project], [Viewpoint].[dbo].[bPMSM].[Submittal], [Viewpoint].[dbo].[bPMSM].[SubmittalType] NULL PLAN_ROW 1 1 | | | | |--Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexType, IndexValue] AS [di1]), SEEK:([di1].[IndexTypeID]=(140001) AND [di1].[IndexValue]=[Expr1011]) ORDERED FORWARD) 1 26 15 Index Seek Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexType, IndexValue] AS [di1]), SEEK:([di1].[IndexTypeID]=(140001) AND [di1].[IndexValue]=[Expr1011]) ORDERED FORWARD [di1].[ID] 1 0.003125 0.0001581 11 2.702759 [di1].[ID] NULL PLAN_ROW 1 15985 | | | |--Clustered Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di1]), SEEK:([di1].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di1].[ID]) LOOKUP ORDERED FORWARD) 1 28 12 Clustered Index Seek Clustered Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di1]), SEEK:([di1].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di1].[ID]) LOOKUP ORDERED FORWARD [di1].[DocumentID] 1 0.003125 0.0001581 11 1.768045 [di1].[DocumentID] NULL PLAN_ROW 1 11163.32 | | |--Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexType, IndexValue] AS [di2]), SEEK:([di2].[IndexTypeID]=(140000) AND [di2].[IndexValue]=[Expr1010]) ORDERED FORWARD) 1 33 9 Index Seek Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[IndexType, IndexValue] AS [di2]), SEEK:([di2].[IndexTypeID]=(140000) AND [di2].[IndexValue]=[Expr1010]) ORDERED FORWARD [di2].[ID] 1.150101 0.003125 0.0001582651 11 1.778779 [di2].[ID] NULL PLAN_ROW 1 11163.32 | |--Clustered Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di2]), SEEK:([di2].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di2].[ID]) LOOKUP ORDERED FORWARD) 1 35 6 Clustered Index Seek Clustered Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[PK_DocumentIndex] AS [di2]), SEEK:([di2].[ID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[ID] as [di2].[ID]) LOOKUP ORDERED FORWARD [di2].[DocumentID] 1 0.003125 0.0001581 11 0.5089599 [di2].[DocumentID] NULL PLAN_ROW 1 3198.979 |--Index Seek(OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID, DocumentTypeID, IndexTypeID, IndexValue] AS [di3]), SEEK:([di3].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di2].[DocumentID]), WH 1 40 3 Index Seek Index Seek OBJECT:([PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID, DocumentTypeID, IndexTypeID, IndexValue] AS [di3]), SEEK:([di3].[DocumentID]=[PaperlessEnvironments].[dbo].[DocumentIndex].[DocumentID] as [di2].[DocumentID]), WHERE:([PaperlessEnvironment [di3].[IndexValue] 1 0.003125 0.0002279533 22 0.00500514 [di3].[IndexValue] NULL PLAN_ROW 1 8.044907 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-20 : 10:47:08
|
You said no indexes on bPMSM. Sure about that?!|--Clustered Index Scan(OBJECT:([Viewpoint].[dbo].[bPMSM].[biPMSM])) |--Index Scan(OBJECT:([Viewpoint].[dbo].[bPMSM].[biKeyID])) submittal, submittaltype and project all could use indexes to help this query.when is the last time the existing indexes were rebuilt? |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-08-20 : 13:09:39
|
quote:
Originally posted by dlh
By the way, thank you very much for taking the time to look at this.I really believe that a single redundant A = A clause cannot be the reason this simple SQL statement takes hours to complete (when it previously ran in ~1 minute). Red herring.
We are not saying that one stray line of A = A is trashing your server. We are saying that allowing the system to generate the code is the problem. A good DBA (or a bored Peter) should see what data is being asked for, then code a procedure "correctly".http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Next Page
|
|
|
|
|
adding a simple filter breaks query