April 28, 2009 at 7:19 am
Alexander Kuznetsov (4/28/2009)
Here is very good article about datetime by Tibor:
Ooo! Alexander Kuznetzov!
(and he's a 'grasshopper' :-D)
Thanks for the link, Alex.
Cheers,
Paul
April 28, 2009 at 7:21 am
Kurt W. Zimmerman (4/28/2009)
My approach is quite simple. I've established this as a standard development practice and seems to work just fine.
This type of solution has already been posted, and the same discussion concerning parameter sniffing applies here too.
Paul
April 28, 2009 at 7:22 am
Tao Klerks (4/28/2009)
2) What is the relationship between query plan caching and parameter sniffing, eg in a stored procedure? Isn't parameter sniffing essentially doing the opposite of query plan caching? Are there circumstances under which "sniffed" parameters will cause a stored procedure to be recompiled? (or can multiple query plans be cached for a single proc, and be selected based on sniffed parameters?)
Parameter sniffing is a side effect of plan caching.
There will only ever be a single plan in cache for a specific procedure (unless some SET options change or the objects can differ depending on the user running the query). When the procedure is executed and there is no matching plan in cache, the optimiser will compile and optimise. At compile time the value of the parameters can be seen by the optimiser, but values of local variables cannot 1
Because the optimiser knows the values of the parameters, it will use those values in combination with the column statistics to work out approximately how many rows would be present for each operator in the query plan. Hence it will come up with a plan that's optimal for those parameters.
That's not a problem at all. The problem comes when the proc gets executed again with a different set of parameters that result in a radically different number of rows. This is usually resultant from sever data skew, when certain values in the table are far more prevalent than others.
When the proc gets run again with a different set of parameters, SQL will fetch and use the cached plan (providing nothing's happened to make it invalid). If the number of rows is radically different from the ones that it was compiled for, the plan can be really, really poor and the query run really slowly.
I wrote a series on parameter sniffing. It needs some rework but may still be of interest.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/
(1) The optimiser can (iirc) see the value of local variables if a statement-level recompile occurs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 7:27 am
Kurt W. Zimmerman (4/28/2009)
My approach is quite simple. Often times I am passing in a date range into a sproc for selection criteria. What I do is the following
set @SubmitDateFrom = cast(convert(varchar(20), @SubmitDateFrom, 101) + ' 00:00:00' as datetime)
set @SubmitDateTo = cast(convert(varchar(20), @SubmitDateTo, 101) + ' 23:59:59' as datetime)
Just be careful if you're modifying the value of parameters. That can lead to a different form of parameter sniffing, one that's quite hard to find or spot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 7:34 am
Thanks Gail, your articles clarified all the questions I had!
The last one also brought to light a potential issue we have with a number of procedures in one of my environments, where we check for a Null parameter value and reset to something useful - as you noted, the query plan could be compiled/cached for a Null value which would in fact never be useful at all!
(that said, I guess it's really it's just a special case of the "first execution of a stored procedure with skewed values can cause later issues" problem)
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 28, 2009 at 7:36 am
Why would you be storing the a "Join Date" or "Hire Date" with a time component?
When storing the date, "zero out" the time portion. Then in your SP/Query all you have to do is 'fix' the @StartDate and @EndDate rather than running a function on every row.
Also, why not just use "WHERE @JoinDate BETWEEN @StartDate AND @EndDate" rather than >= and <=
April 28, 2009 at 7:41 am
Tao Klerks (4/28/2009)
The last one also brought to light a potential issue we have with a number of procedures in one of my environments, where we check for a Null parameter value and reset to something useful - as you noted, the query plan could be compiled/cached for a Null value which would in fact never be useful at all!
Yup. That's a subtle one that is so hard to notice. Bad query plans with no obvious cause even with an empty proc cache and updated stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 7:41 am
Alright, I lied - one follow-up:
As I understand it now, for stored procedures "parameter sniffing" will only happen (or be relevant) if there is not already a full compiled execution/query plan for the stored procedure.
Does the same apply to auto-parameterized queries? Is it possible to get severely sub-optimal query plans on dynamic SQL statements because an auto-parameterized query plan is reused?
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 28, 2009 at 7:45 am
Dallas Martin (4/28/2009)
Hmm... You may suffer a huge performance hit if you use DATEDIFF().SQL SVR may have to perform a full table scan. Not exactly what one
desires in a production system.
I would suggest creating a varchar(8) column and populating it with the
YYYYMMDD format using the CONVERT() function. One could write a trigger to perform this task.
You would then index this varchar() column to maximize performance
in WHERE clauses.
Using the CONVERT(varchar(8),getdate(),112) will return a date in the
YYYYMMDD format.
This is NOT good advice. If you are going to do this (which is actually a common practice in data warehousing date dimensions) it should be an integer, not a varchar. Oh, and if you DID make it character based, it should be a char(8), not a VARchar(8) since the length is fixed at 8 for every value. Why pay the overhead of a variable field when it is not necessary to do so?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 28, 2009 at 7:46 am
Tao Klerks (4/28/2009)
Interesting approach - we use Convert, relying on the fact that DateTime values are internally stored as floating-point values, with every whole number representing a day:
Just be careful with that. It's not a documented feature and as such is subject to changing without warning. It also doesn't work on any of the new SQL 2008 datetime data types (DATETIME2, DATETIMEOFFSET)
Any idea which is faster? I don't have time to test right now, but I'm quite curious 🙂
http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 7:50 am
Tao Klerks (4/28/2009)
As I understand it now, for stored procedures "parameter sniffing" will only happen (or be relevant) if there is not already a full compiled execution/query plan for the stored procedure.
No, other way around. Parameter sniffing occurs when there a cached plan, the cached plan is reused and is not optimal for the current set of parameters
Does the same apply to auto-parameterized queries? Is it possible to get severely sub-optimal query plans on dynamic SQL statements because an auto-parameterized query plan is reused?
Yup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 8:00 am
GilaMonster (4/28/2009)
Tao Klerks (4/28/2009)
As I understand it now, for stored procedures "parameter sniffing" will only happen (or be relevant) if there is not already a full compiled execution/query plan for the stored procedure.No, other way around. Parameter sniffing occurs when there a cached plan, the cached plan is reused and is not optimal for the current set of parameters
OK, now I'm more confused than ever. From your first article on the subject:
parameter sniffing is the ability of the SQL Server optimiser to know the values of parameters passed to a stored proc at the point that it compiles the procedure.
I can see how the parameter sniffing "problem" (which is actually that the parameters are not sniffed, they are ignored in terms of the query plsn) occurs when there is a cached plan, the cached plan is reused and is not optimal for the current set of parameters; but the actual sniffing "process" surely only happens in the plan compilation process?
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 28, 2009 at 8:03 am
I count myself fortunate that I'm using SQL08.
select * from etl.exceptions
where convert( date, datetimestamp ) between '2009-01-01' and '2009-02-06'
Not only returns the correct results, but does so with an index seek.
April 28, 2009 at 8:07 am
Tao Klerks (4/28/2009)
OK, now I'm more confused than ever.
Sorry, my bad.
parameter sniffing is the ability of the SQL Server optimiser to know the values of parameters passed to a stored proc at the point that it compiles the procedure.
I can see how the parameter sniffing "problem" (which is actually that the parameters are not sniffed, they are ignored in terms of the query plsn) occurs when there is a cached plan, the cached plan is reused and is not optimal for the current set of parameters; but the actual sniffing "process" surely only happens in the plan compilation process?
Sniffing occurs during compile, problems occur due to reuse. You are 100% correct.
Often the term 'parameter sniffing' is incorrectly used to refer to the problem, not to the process during compile. I was using it in the incorrect sense in the last post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 8:15 am
Edit: Never mind....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31 through 45 (of 110 total)
You must be logged in to reply to this topic. Login to reply