I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365
Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select projectid
from tblMilestones
where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.
In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).
If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )"
To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
and enabled = 1 "
2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"
3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table
4) Adding: OPTION (FORCE ORDER)
5) OR completely fixing the query (remove redundant loops, etc)
...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.
I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.
Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).
I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha
P.S. Results of Showplan Text:
StmtText
SET STATISTICS PROFILE ON
(1 row(s) affected)
StmtText
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' )
and tblMilestones.enabled = 1 )
(1 row(s) affected)
StmtText
|--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
| |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
| | |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
| | | |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
| | | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join)
|--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
|--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
(15 row(s) affected)
StmtText
--
SET STATISTICS PROFILE OFF
(1 row(s) affected)
It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is. Steve Kass Drew University Smitha_Expesite@.discussions.microsoft.com wrote:
> I'm seeing some change in behavior for a query in SQL Server 2005
> (compared to behavior in SQL Server 2000). The query is as follows:
>
> create table #projects (projectid int) insert into #projects select
> projectid from tblprojects where istemplate = 0 and projecttemplateid =
> 365
>
> Select distinct tblProjects.ProjectID
> from tblProjects WITH (NOLOCK)
> inner join #projects on #projects.projectid = tblprojects.projectid
> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =
> tblMilestones.ProjectID
> and tblProjects.projectID in (
> select projectid
> from tblMilestones b
> where (parent = 683691 AND PrimaryDate between
> '4/15/2006' and '4/22/2006' )
> and enabled = 1 )
>
> This is dynamic SQL generated by the application when a user requests a
> report with variable parameters. It works fine in SQL Server 2000. It
> outputs 47 records which is correct.
>
> In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as
> a TOP operator and outputs just 1 record. (Results of Showplan Text at
> the end of this post).
>
> If I modify the query even the slightest bit by:
> 1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006'
> and '4/22/2006' )
> and enabled = 1 )"
> To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and
> '4/22/2006' ) )
> and enabled = 1 "
>
> 2) Changing " Select distinct tblProjects.ProjectID"
> To " Select distinct tblProjects.ProjectID+''"
>
> 3) Removing the Distinct keyword, storing into a Temp table, then
> performing a distinct on the temp table
>
> 4) Adding: OPTION (FORCE ORDER)
>
> 5) OR completely fixing the query (remove redundant loops, etc)
>
> ...it works fine (outputs 47 records). It also works if I created new
> tables (eg. tMilestones instead of tblMilestones) and inserted about 10
> records into each and ran the query referencing these new tables.
>
> I reindexed the tables, updated stats, updated usage, ran DBCC
> FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave
> the way it does in SQL Server 2000 without modifying the query/adding
> the query hint.
>
> Have you come across this? Any ideas on what might be causing the "TOP"
> operation. (Somewhat resembles the bug mentioned in this article:
> http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently
> fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server
> 2005 yet?).
>
> I will appreciate any new insights you might have on this issue.
> Thanks much,
> Smitha
>
>
> P.S. Results of Showplan Text:
>
> StmtText
>
> SET STATISTICS PROFILE ON
>
> (1 row(s) affected)
>
> StmtText
>
>
>
>
>
>
>
> Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
> inner join #projects on #projects.projectid = tblprojects.projectid
> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =
> tblMilestones.ProjectID
> and tblProjects.projectID in (
> select tblMilestones.projectid from tblMilestones
> where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006'
> and '4/22/2006' )
> and tblMilestones.enabled = 1 )
>
> (1 row(s) affected)
>
> StmtText
>
>
>
>
>
>
> |--Stream
> Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectI
> D]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
> |
> |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo]
> .[tblMilestones].[Enabled],0)=(1)))
> | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([Uniq1014],
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
> | | |--Merge Join(Inner Join,
> MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],
> [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],
> [Uniq1014]),
> RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] =
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND
> [Uniq1014] = [Uniq1014]))
> | | | |--Sort(ORDER
> BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC,
> [Uniq1014] ASC))
> | | | | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDa
> te]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate]
> >>= '2006-04-15 00:00:00.000' AND
>
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <=
> '2006-04-22 00:00:00.000') ORDERED FORWARD)
> | | | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]
> ),
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691))
> ORDERED FORWARD)
> | | |--Clustered Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])
> ,
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi
> teProductionCopy].[dbo].[tblMilestones].[ProjectID] AND
> [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
> | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProject
> s_1]),
> SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[Expesite
> ProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
> |--Top(TOP EXPRESSION:((1)))
> |--Nested Loops(Inner Join)
> |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]),
> WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempd
> b].[dbo].[#projects].[projectid]))
> |--Clustered Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])
> ,
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi
> teProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
>
> (15 row(s) affected)
>
> StmtText
> --
> SET STATISTICS PROFILE OFF
>
> (1 row(s) affected)
>
>
>
>
>
>|||OK...I will report it to the Feedback folks. Thanks!|||
The Hotfix that you mentioned (http://www.kbalertz.com/Feedback_910392.aspx) is fixed in SQL Server 2005 RTM, so you may have discovered a new bug in SQL. If so, I would like to fix it. However, I do not have enough information to do that yet.
The first thing I would recommend is to send me a clone of your database. This is a version with no data, but with all the statistics intact (as though the tables were large), so we will get the same plans.
Please contact me for help making the clone.
Marc Friedman
marcfr@.removethispart.microsoft.com
|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.
No comments:
Post a Comment