April 9, 2012 at 10:15 pm
Comments posted to this topic are about the item Indexed Views In Non-Enterprise Editions of SQL Server
April 10, 2012 at 1:09 am
Thanks for this article, sknox. It's disconcerning to see that, apparently, such grave misconceptions about indexed views exist.
In the introduction, you write that someone told you that NOEXPAND is not availabla as of SQL Server 2008R2. I don't know who wrote that and where he/she got this information, but I can tell you that this is completely wrong (trying hard to avoid stronger language here). The NOEXPAND hint is fully supported in all versions, including SQL Server 2008R2 and SQL Server 2012. This hint is also not documented as being deprecated, so there are no plans to remove support in a future edition. And to the best of my knowledge, there are no plans to deprecate this hint in a future version.
I also want to provide some feedback on a few minor points that I noticed in your article.
At one point, you write: "the percentages aren't definitive because of the random nature of the data I'm using" - I just wanted to point out that the percentages are NOT a measure of the actual cost of queries. The percentages shown in the execution plan are always based on the estimated execution plan (even if you are running the query with the option to include the actual execution plan). You can see some of the differences between estimates and reality by hovering your mouse over various parts of the execution plan, but neither total cost of an iterator or entire query, nor query cost relative to the batch support that - the most important measures that you can get an actual vs estimate on are row counts for all of the data streams (arrows), and both row counts and numbers of executions for iterators.
The percentages on the execution plan are often a good measure, but the caveats are that (1) if the optimizer miscalculates because of incorrect assumptions or outdated stats, you won't see it; and (2) they are based on query cost, which is based on assumptions that might no hold for you (e.g. when your database is already in cache - the query cost calculation is based on the assumption that most I/O will be non-cached; or when elapsed time is the only thing that's important to you, even at the cost of increased I/O; or ......)
When I compare perfomance of queries, I always use SET STATISTICS TIME to check elapsed time (or CPU time if that's what I want to minimize), and SET STATISTICS IO to check how much I/O the query used. Or I use extra code to put the datetime at the start of the query in a variable, then calculate the DATEDIFF in milliseconds at the end of the query and return it to the client or save it in a table for later analysis. (The latter method is very useful when I let the computer run overnight to run lots of long-running tests, or to repeat the same test multiple time to average out random spikes).
In your conclusion, you mention some conditions where an indexed view will not be used. This is absolutely true for queries with no hint running on Enterprise Edition. But when you use the NOEXPAND hint, then (in ANY edition!) SQL Server no longer has any option - the term hint is extremely misleading; it is the same kind of hint parents give to their children to clean up their rooms: "you might want to ..." - but if you don't, they'll just ground you for a week. If you use the NOEXPAND hint, the optimizer WILL choose a plan that uses at least one of the indexes defined on the view - even if other plans would have been cheaper.
Finally - I just saw the link to where you found the misinformation about the NOEXPAND hint. I tried to comment on the article, but I was forced to log in to some site I've never heard of and I am still contemplating if this is worth getting yet another account for. I also checked a few other articles on that site by the same author, and I can only conclude that this man is dangerous. He has no idea what he's talking about, and yet dares to write in an authorative tone, happily spreading misinformation all over the world. Please, whenever you rean an article by him, double check other sources before assuming that he's right.
April 10, 2012 at 4:01 am
NOEXPAND hint is also available in SQL server 2005 (including express edition). I use it in my applications and works very well.
April 10, 2012 at 5:28 am
I discovered this a few months back after an SQL Server Trainer told me they were only in Enterprise edition. Running nicely on my website running 2008 Express.
When using the indexed view, you can avoid having to put with (noexpand) throughout your stored procedures/code by wrapping the indexed view in a normal view.
The example taken from a database running on SQL 2000 Standard.
CREATE VIEW [dbo].[vw_Country_idx] WITH SCHEMABINDING
AS
SELECT cg_ct_id, cg_id, c_id, cg_name, c_Name, c_shortcode
FROM dbo.country_group
INNER JOIN dbo.country_group_list ON cg_id = cgl_cg_id AND (cgl_Archived = 0 OR cgl_Archived IS NULL)
INNER JOIN dbo.country ON c_id = cgl_c_id AND (c_Archived = 0 OR c_Archived IS NULL)
WHERE (cg_Archived = 0 OR cg_Archived IS NULL)
GO
CREATE UNIQUE CLUSTERED INDEX [idx_vw_Country] ON [dbo].[vw_Country_idx]
( [c_id] ASC, [cg_ct_id] ASC)
GO
CREATE VIEW [dbo].[vw_Country]
AS
SELECT *
FROM vw_Country_idx WITH (NOEXPAND)
GO
April 10, 2012 at 5:58 am
In fairness, http://msdn.microsoft.com/en-us/library/cc645993.aspx for 2012 does show that "Direct query of indexed views (using NOEXPAND hint)" (in the RDBMS Manageability section) is not available in Express editions (though it certainly is). The 2008 R2 version (http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx) suggests at least Standard is required (i.e. not Web, Workgroup or Express) though again it does work on all 2008 R2 versions. The 2008 version (http://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx) doesn't list NOEXPAND explicitly at all.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2012 at 4:24 am
Thanks for this. I thought it was only available in Enterprise Edition. 😉
===============================
= Ignorance is the choice not to know =
===============================
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply