February 4, 2014 at 9:11 am
Hi all,
from MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this: where [Field1] like @p1 and [Field1] < @p2.
Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause.
@p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401.
if object_id('tempdb..#tbl') is not null
drop table #tbl;
create table #tbl
(
[No] nvarchar(20)
,[Description1] nvarchar(250)
,[Description2] nvarchar(250)
,[Description3] nvarchar(250)
,[Description4] nvarchar(250)
,[Description5] nvarchar(250)
,[Description6] nvarchar(250)
,[Description7] nvarchar(250)
,[Description8] nvarchar(250)
,[Description9] nvarchar(250)
,[Description10] nvarchar(250)
,[Description11] nvarchar(250)
,[Description12] nvarchar(250)
,[Description13] nvarchar(250)
,[Description14] nvarchar(250)
,[Description15] nvarchar(250)
,[Description16] nvarchar(250)
,[Description17] nvarchar(250)
,[Description18] nvarchar(250)
,[Description19] nvarchar(250)
,[Description20] nvarchar(250)
,[Description21] nvarchar(250)
,[Description22] nvarchar(250)
,[Description23] nvarchar(250)
,[Description24] nvarchar(250)
,[Description25] nvarchar(250)
constraint pkci primary key clustered([No])
);
with
l0 as (select 1 as c union all select 1 as o), -- 2 rows
l1 as (select 1 as c from l0 as a cross join l0 as b), -- 4 rows
l2 as (select 1 as c from l1 as a cross join l1 as b), -- 16 rows
l3 as (select 1 as c from l2 as a cross join l2 as b), -- 256 rows
l4 as (select 1 as c from l3 as a cross join l3 as b), -- 65,536 rows
l5 as (select 1 as c from l4 as a cross join l4 as b), -- 4,294,967,296 rows
nums as(select row_number() over(order by (select null)) as n from l5)
insert into #tbl
select top 1000000
cast(right(N'0000000000000000000' + cast([n] as nvarchar(20)), 20) as nvarchar(20))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
,cast(replicate(N'asdfghjklö', 25) as nvarchar(250))
from nums
order by n;
set statistics io on;
declare @p1 nvarchar(20);
declare @p2 nvarchar(20);
set @p1 = N'0000000000000001647%';
set @p2 = N'10000000000000000000';
-- 1.006.582 Reads
select *
from #tbl
where [No] like @p1
and [No] < @p2;
-- 16 Reads
select *
from #tbl
where [No] like N'0000000000000001647%'
and [No] < N'10000000000000000000';
February 4, 2014 at 10:55 am
You need to look at the DETAILs of your query plan to see what is happening here. Put your cursor over the seek operator in the graphical query plan and then hit F4. Look at Predicate and Seek Predicate of the two queries and you will see what is happening.
For the variable query, you will see this:
predicate: [tempdb].[dbo].[#tbl].[No] like [@p1]
seek predicate: Seek Keys[1]: End: [tempdb].[dbo].[#tbl].No < Scalar Operator([@p2])
for the hard-coded one you will see this:
predicate: [tempdb].[dbo].[#tbl].[No]<N'10000000000000000000'
AND [tempdb].[dbo].[#tbl].[No] like N'0000000000000001647%'
seek predicate:
Seek Keys[1]: Start: [tempdb].[dbo].[#tbl].No >= Scalar Operator(N'0000000000000001647'),
End: [tempdb].[dbo].[#tbl].No < Scalar Operator(N'0000000000000001648')
This is of course a STUPID query coming in from Navision since the < part of the filter is completely unnecessary. However, you will have to live with that (after bitching to Navision about the crap code).
If you put an OPTION (RECOMPILE) after the variable query, you get the right plan and the almost 6 orders of magnitude performance improvement:
declare @p1 nvarchar(20) = N'0000000000000001647%',
@p2 nvarchar(20) = N'10000000000000000000';
select *
from #tbl
where [No] like @p1
and [No] < @p2
OPTION (RECOMPILE);
So you can either get Navision to fix there stuff or create a Plan Guide to associate with that query that using the plan you get with the OPTION (RECOMPILE). Note that if you do this it WILL use the plan - even if it is suboptimal for the given pair of inputs. But given how egregious the current situation is I would take that downside in a heartbeat if it were my client's system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2014 at 12:13 pm
Thanks Kevin,
they use the < filter for paging (that's at least what I think). They use TOP 50 with a filter on the primary key to get the data for the next page.
The recompile hint works in my repro, but unfortunately not in the original query.
Every query from Navision comes with "optimized for unknown", and querries from list pages also with "fast 50".
It seems that "optimized for unknown" beats recompile.
I had the same issue when trying to make Navision using filtered indexes.
Can I remove "optimized for unknown" with plan guides?
best
Tobias
February 4, 2014 at 12:59 pm
The option "optimize for unknown" is telling SQL Server to use same approach as when you use variables in your example. The optimizer will not sniff the value of the variable and instead use the "All Density" from the density vector if we have an equality predicate involving leading columns of the index/statistic or a rough 30 or 10 percent of the number of rows in the table for open or inequality predicates.
As you noticed already, if you have both options "optimize for unknown" and "recompile" then the value of the variable will not be sniffed even if we recompile the statement.
You could try using a constant through a plan guide with the option (optimize for @p1 = N'0000000000000001648') and see if that helps. This option prevails over "optimize for unknown".
I haven't seen this presentation but coming from Kimberly Tripp it should be a good one for you to get familiar with the things we have stated here.
SESSION: Skewed Data, Poor Cardinality Estimates, and ...
Another option would be if you have access to Pluralsight. There are great training videos from Kimberly and the rest of professionals from SQLSkills.
February 4, 2014 at 3:21 pm
lots of things you can specify with a plan guide and if they get picked they get used as is AFAIK.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2014 at 6:10 am
But the FAST (50) hint is also going to impact what gets delivered by the optimizer. It's likely to see what you're seeing, a seek where a scan would serve better, under that hint. If I were beginning the modification of anything, I'd start with getting rid of that hint instead of the OPTIMIZE FOR UNKNOWN (although, I'd experiment with losing that too).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2014 at 9:16 am
Grant Fritchey (2/5/2014)
But the FAST (50) hint is also going to impact what gets delivered by the optimizer. It's likely to see what you're seeing, a seek where a scan would serve better, under that hint. If I were beginning the modification of anything, I'd start with getting rid of that hint instead of the OPTIMIZE FOR UNKNOWN (although, I'd experiment with losing that too).
Unfortunately Grant Dynamics NAV is serving up the queries, so I doubt the user has any control over it. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2014 at 9:30 am
TheSQLGuru (2/5/2014)
Grant Fritchey (2/5/2014)
But the FAST (50) hint is also going to impact what gets delivered by the optimizer. It's likely to see what you're seeing, a seek where a scan would serve better, under that hint. If I were beginning the modification of anything, I'd start with getting rid of that hint instead of the OPTIMIZE FOR UNKNOWN (although, I'd experiment with losing that too).Unfortunately Grant Dynamics NAV is serving up the queries, so I doubt the user has any control over it. 🙁
The gods do love code generated T-SQL. And by love I mean enjoy completely abusing.
I do love ORMs, but they are also a great mechanism for digging seriously deep holes, quickly.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2014 at 9:37 am
Grant Fritchey (2/5/2014)
TheSQLGuru (2/5/2014)
Grant Fritchey (2/5/2014)
But the FAST (50) hint is also going to impact what gets delivered by the optimizer. It's likely to see what you're seeing, a seek where a scan would serve better, under that hint. If I were beginning the modification of anything, I'd start with getting rid of that hint instead of the OPTIMIZE FOR UNKNOWN (although, I'd experiment with losing that too).Unfortunately Grant Dynamics NAV is serving up the queries, so I doubt the user has any control over it. 🙁
The gods do love code generated T-SQL. And by love I mean enjoy completely abusing.
I do love ORMs, but they are also a great mechanism for digging seriously deep holes, quickly.
I love both of those too!! I go "KACHIINNNGGG!!!" when I come across a new client that is using one or both! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 13, 2014 at 6:53 am
With plan guides just including a recompile hint the querries work pretty fast with only a few reads.
The like operator is now be evaluated as a seek predicate.
I'm wondering what happens to the OPTIMIZE FOR UNKOWN when the plan guide matches.
Can I see that somewhere in the plan?
When I don't use plan guides, but specify OPTIMIZE FOR UNKONW and RECOMPILE hints the query is slow (like operation not in seek preciates).
So it seems the the plan guide will do "something" with the OPTIMIZE FOR UNKNOWN (included in the orignal Navision query).
Now I have a lot of plan guides and I think with every Navision Update the querries will change a little bit, so the plan guides will no longer match.
Sounds like a really good solution 🙂
thank you all
best
Tobias
February 13, 2014 at 7:49 am
You can open the properties of the first operator of the plan when looking at the graphical plan. That will contain the information about which plan guides have been used, if any.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 13, 2014 at 7:53 am
sqlTobi (2/13/2014)
With plan guides just including a recompile hint the querries work pretty fast with only a few reads.The like operator is now be evaluated as a seek predicate.
I'm wondering what happens to the OPTIMIZE FOR UNKOWN when the plan guide matches.
Can I see that somewhere in the plan?
When I don't use plan guides, but specify OPTIMIZE FOR UNKONW and RECOMPILE hints the query is slow (like operation not in seek preciates).
So it seems the the plan guide will do "something" with the OPTIMIZE FOR UNKNOWN (included in the orignal Navision query).
Now I have a lot of plan guides and I think with every Navision Update the querries will change a little bit, so the plan guides will no longer match.
Sounds like a really good solution 🙂
thank you all
best
Tobias
Personally I don't like the OPTIMIZE FOR UNKNOWN very much. You are basically GUARANTEEING that you will get suboptimal performance in some fraction of the cases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 13, 2014 at 9:03 am
Can I also see wich query hints were used?
The query comes in with OPTIMIZE FOR UNKNOWN and FETCH 50, and the plan guide "adds" RECOMPILE.
Because I see parameter sniffing happening in the plan, it seems that the RECOMPILE plan guide "overwrites" OPTIMIZE FOR UNKNOWN.
Is that true? Or how does it work.
Thanks again
Tobias
February 13, 2014 at 9:13 am
Recompile will override, yes. You can't really see query hints through the execution plan. You can see them in the SQL text that is stored with the plan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 13, 2014 at 9:14 am
What I can't understand is why they made NAV adding hints to every query.
In the past they had some querries suffer from parameter sniffing. And when OPTIMIZE FOR UNKONOWN came available they add it to every query.
Querries from the UI also have FAST 50 as default.
Horror.
best
Tobias
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply