Search results matching tag 'Indexed Views'

NOEXPAND query hint returns wrong results – CU fix now available

jamiet — Mon, 04 Feb 2013 10:24:00 GMT

Three days ago at my current gig we stumbled across a problem where use of the NOEXPAND query hint was causing different results compared to the same query without NOEXPAND. For those that do not know (which, until three days ago, included me) NOEXPAND governs the use of indexed views:

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX(index_value [ ,...n ] ) ) is specified.
http://msdn.microsoft.com/en-gb/library/ms181714.aspx

This screenshot demonstrates the problem:

Clearly this isn’t good. I put a comment out on Twitter hoping someone knew something about it and thankfully Gonçalo Ferreira was reading. He pointed me toward a Knowledge Base article entitled FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 that confirmed this as being a bug in SQL Server. The article is dated 24th January 2013 (that is only 11 days ago folks) and describes the bug rearing its head under these circumstances:

You create an indexed view for two tables that have a foreign key reference in Microsoft SQL Server 2008, in Microsoft SQL Server 2012 or in Microsoft SQL Server 2008 R2.

You update the base tables of the indexed view.

You run a query against the indexed view that uses the NOEXPAND hint.

In this scenario, you receive an incorrect result.

I don’t know about you but I read that as “If you’re using Indexed Views with NOEXPAND, they’re most likely giving you the wrong answer”. The latest cumulative update (CU) for:

SQL Server 2012
SQL Server 2012 SP1
SQL Server 2008 R2 SP2
SQL Server 2008 R2 SP1
SQL Server 2008 SP3

fixes the problem. If you’re using indexed views you may want to install the CU, sharpish!

@Jamiet

UPDATE. Paul White has posted a fantastic blog post where he explains how to reproduce the problem: Incorrect Results with Indexed Views (N.B. It occurs if you are using the MERGE statement) and Aaron Bertrand has followed with the equally readable If you are using indexed views and MERGE, please read this! where he offers some considered opinions and advice surrounding the issue. I highly recommend reading both.

Connect Digest : 2011-06-27

AaronBertrand — Mon, 27 Jun 2011 17:35:00 GMT

Sorry I have fallen off the Connect Digest wagon for the past few weeks; been a little swamped since returning from SQLCruise Alaska. Not sure I'll be able to assemble a digest every week, but I'll certainly try to keep a steady pace.

This week I wanted to highlight a few suggestions around indexed views. With the coming of SQL Server code-named "Denali" we will be pushed toward the new columnstore index as an alternative to indexed views. But this won't be for all cases, and it likely won't be available to all editions, either. So I hope that these requests don't start to get discarded with the simple workaround of upgrading to Denali, possibly upgrading to Enterprise Edition, and converting to the read-only columnstore index.

====================

Ralf Dietrich suggests relaxing some of the restrictions on indexed view creation; for example, the fact that an index can't be created on a view with non-deterministic functions. He doesn't want it to just suddenly start working, but to require a hint to make work.

#577305 : indexed view improvements

====================

Razvan Socol has a similar suggestion, that views with one or more unions could be indexed. His specific use case is not just for performance but also that he'd like to define a foreign key against the view.

#125968 : Indexes on views that contain UNION-s

====================

xor88 is asking for stacked (or "nested") indexed views. Essentially he wants to be able to create an index on a view that references another indexed view, in order to support persist aggregations over existing persisted aggregations.

#641364 : stacked indexed views

====================

Back in 2007, I asked for more aggregation support in indexed views - in addition to counts, for example, it would be helpful to have min/max support. More work for the engine, of course, but it could dramatically

#267516 : Expand aggregate support in indexed views (MIN/MAX)

====================

Adam Machanic (@AdamMachanic) filed this bug, concerning the fact that indexed view creation takes a schema modification lock on the base table (resulting in blocking both readers and writers), when it would be possible to take only a schema stability and shared table lock (blocking only writers).

#581056 : Indexed view creation causes queries on the base table to block

====================

A comment on the above item from Paul White (@SQL_Kiwi) made me think of this suggestion, to delay index creation so that it could be done in the background and have less impact on real-time operations:

#593032 : Indexed Views - Option for Delayed Seperate Index creation Process - Akin to Full Text Indexing

====================

That's it for this week's digest. Please let me know if you have suggestions for future focus areas!

Connect Digest : 2011-05-23

AaronBertrand — Mon, 23 May 2011 12:28:00 GMT

Next week I'll be up in Alaska for SQL Cruise, so I'll be skipping at least one digest. This week I gathered a few engine-related issues, two involving what the engine does while an index is being created. The most important one, though, at least IMHO, is the first one listed, involving a hashing algorithm fix that is in SQL Server 2008 R2 - but with no plans to fix it for SQL Server 2008. I wonder if someone should file a separate, 2008-specific bug?

#458091 : Change Lock Resource Hashing Algorithm to Reduce Likelihood of Collisions
#581056 : Indexed view creation causes queries on the base table to block
#478568 : SSMS Gets Blocked and Locks Up During Index Creation
#125549 : Poor execution plan with CTE

And finally, this issue ("high degrees of parallelism cause incorrect results to be observed more frequently" according to KB #981502) is one that has been fixed in SP1 for SQL Server 2008 R2, but it requires a trace flag (in Denali, it will be on by default, so you won't need a trace flag). I'm not telling you about this so you'll go and vote for this issue, but rather just to make you aware of it. The KB article states that the fix is in various CUs for 2005, 2008 and 2008 R2, but as the Connect item implies, the fix may not actually work in all cases, and requires the trace flag for others.

#642790 : Parallel query plan returns different results every time that you run the query

Connect Digest : 2011-05-16

AaronBertrand — Mon, 16 May 2011 11:19:00 GMT

This week we're going to take a look at issues with filtered indexes. This feature was a very welcome addition in SQL Server 2008 and has enjoyed widespread usage if conversations on twitter and forums are any indication. But their implementation is not perfect - bugs in SSMS and numerous restrictions on their use have certainly reduced their impact, or at least made much more work for folks using them.

SSMS failures

Several people have reported various cases where Management Studio trips over filtered index functionality. I would only expect these bugs to be fixed in either 2008 R2 SSMS or Denali (in some cases both) - don't hold out for your 2005 or even 2008 versions of SSMS to be fixed for filtered index or other "new feature" issues.
#526911 : Table designer fails to correctly script filtered index

#462053 : The filter expression of a filtered index is lost when a table is modified by the Table Designer

#362699 : Table designer doesn't script WHERE clause in filtered indexes

#330238 : There is no way of creating filtered index from SSMS while designing a new table

#521333 : View Dependencies fails with Transact-SQL Error 515

Engine failures

Well, there is only one of these outstanding at this time, at least that I could find. I suspect this has to do with the testing matrix - they added both filtered indexes and MERGE in SQL Server 2008, but did not thoroughly test them *together.* There is a discussion thread on the MSDN forums about this specific issue:
#596086 : MERGE statement bug when INSERT/DELETE used and filtered index

Missing functionality

Some of these items are categorized as bugs, but I believe they simply represent scope creep. One of the items actually says something along the lines of, "It's not a bug; it's a known gap in functionality." Personally I think they got the bare minimum into SQL Server 2008 (supporting unique constraints the ANSI standard way, where more than one row can be NULL), and had no intention of supporting all of the other things that could make filtered indexes even more useful. If you find any of these suggestions valuable, please comment and vote - hopefully we can convince them to invest some time into this feature area for the next version after Denali!
#454744 : Filtered index not used and key lookup with no output

#341891 : Filtered index creation failed with NOT IN clause

#341737 : Filtered index does not allow filters on disjunctions

#648551 : filtered index on indexed view

#666238 : Support for more complex WHERE clause in filtered indexes

#518328 : Should be possible to create a filtered index on a deterministic persisted computed column

#498009 : Allow filtered unique index to be a canditate key for a foreign key

#329805 : Enhancements to the missing indexes DMV's for filtered indexes

Mixing OLTP and reporting using indexed views

AaronBertrand — Tue, 29 Dec 2009 14:40:00 GMT

I'm not a big fan of denormalizing, nor of repeating redundant ~~redundant~~ information in a database when those facts can already be derived from other information. A classic example of the latter is when I see questions on newsgroups, forums or StackOverflow that ask how they can update a table's rank column to reflect the current rank based on some other criteria in the table. The problem with this is that you have to run the update every time any DML operation touches any row in the table, and if you can calculate that rank in an UPDATE query, you can also calculate that rank in a SELECT query, so why not just figure it out in real time? An example of the former is when we want to maintain a second table with aggregates from another table. This is where I want to spend my time today.

In reality, many of us deal with OLTP systems that must also serve as the reporting source, so it's not always feasible to calculate aggregates in real time against a constantly moving target. In several of these scenarios I've used indexed views, where we have tables that are inserted often, read often for statistics, but rarely or never updated... sure, you pay a little hit up front on the insert, but the benefit achieved during real-time reporting was worth it. Let me give you an idea of what I was dealing with initially, and how I have fixed it over time to perform even better.

One of our systems is an SaaS e-mail platform, where we offer the ability for our customers to distribute messaging to their customers (mostly via e-mail, but we also support fax, SMS, widgets/gadgets, and social media). For brevity, let's call these people "recipients." If you stripped the schema down to the bare essentials for metadata, you'd see this (I'm going to leave out the foreign key and other constraints, as they are either obvious or irrelevant):

CREATE TABLE dbo.Domains
(
  DomainID INT PRIMARY KEY,
  DomainName VARCHAR(255)
);

CREATE TABLE dbo.Recipients
(
  RecipientID INT PRIMARY KEY,
  DomainID INT,
  LocalPart VARCHAR(64)
  /* , ... other columns ... */
);

Obviously we store many other details about recipients, such as demographic information, preferences, opt-in status, subscriptions, etc. But for this discussion, the above is sufficient. Notice that for space savings we don't actually store the e-mail address of the recipient, though it is required and is enforced to be unique via a constraint on (DomainID, LocalPart); this information can easily be derived using a view (you can even make this an indexed view if you want quicker access to e-mail addresses, but I'll leave that for another day):

CREATE VIEW dbo.vRecipientDetails
AS
    SELECT
        r.RecipientID,
        EmailAddress = r.LocalPart + '@' + d.DomainName
    FROM
        dbo.Recipients AS r
    INNER JOIN
        dbo.Domains AS d
        ON r.DomainID = d.DomainID;

RecipientID is the primary key because it is used in a lot of related tables, most importantly, stats tables. We record every single transaction for a recipient: when a message is attempted to be sent to them; when a successful delivery occurs; when a bounce occurs; when the recipient opens the message or clicks on a link in the message; when the recipient changes their preferences; or, when the user declines to receive further communications from our customer. The thought of storing LocalPart + DomainID (or the fully composed e-mail address) in all of these other tables that would grow and grow over time made a surrogate representation a very easy choice for the primary key. I didn't want to make the discussion revolve around this, but I've seen several raised eyebrows in the past about surrogate primary keys and wanted to assure you that, in this system, I think it is the best choice. (If you want to debate that, let's have a different discussion, as long as it doesn't turn religious.)

Okay, so I mentioned this is an OLTP system, and I mentioned that we record all of the individual transactions for a recipient in various stats tables. Let's take a quick look at how one of these stats tables looked for the first, oh, 5 years of their existence:

CREATE TABLE dbo.Deliveries
(
  MessageID INT, -- references a table dbo.Messages
  RecipientID INT,
  EventDate SMALLDATETIME
  /* , ... other columns... */
);

So a customer would send out a message targeted to, say, 50,000 recipients. During the next few hours we would fill up this table with successful deliveries (and other similar tables with bounces, opens, clicks, etc). Soon after inception of the system, we found that our customers wanted to pull real-time statistics on how the messages were going... mostly on the lookout for high bounce rates (well, low delivery rates too, I guess). Of course this was very contentious if they pulled reports while the tables were loading. So in order to give a much more satisfactory experience to the user pulling reports in our web UI, we created indexed views that would automatically maintain the number of deliveries, bounces, etc.:

CREATE VIEW dbo.vMessageDeliveries
WITH SCHEMABINDING
AS
  SELECT
    MessageID,
    c = COUNT_BIG(*)
  FROM
    dbo.Deliveries
  GROUP BY
    MessageID;
GO

CREATE UNIQUE CLUSTERED INDEX m
  ON dbo.vMessageDeliveries(MessageID);

So now the inserts were a little more expensive but, as mentioned above, this was much better for overall system performance (at least perceived performance) and, more importantly, customer happiness.

Then customers wanted more details on their statistics. They wanted to see how their messages were getting into, say, Hotmail compared to AOL. We have this information, but it was quite expensive to retrieve. Remember earlier I noted that we don't typically store redundant information, but this means that in cases like this, we need to do a lot of joins. So for example, to provide a responsive report that showed real-time message status per domain, we created indexed views like this:

CREATE VIEW dbo.vMessageDomainDeliveries
WITH SCHEMABINDING
AS
  SELECT
    d.MessageID,
    r.DomainID,
    c = COUNT_BIG(*)
  FROM
    dbo.Deliveries AS d
  INNER JOIN
    dbo.Recipients AS r
    ON r.RecipientID = d.RecipientID
  GROUP BY
    d.MessageID,
    r.DomainID;
GO

CREATE UNIQUE CLUSTERED INDEX md
  ON dbo.vMessageDomainDeliveries(MessageID, DomainID);

Of course this again added more strain to the insert process on the deliveries table, since it now had to maintain two indexed views. In isolation, this still yielded better overall performance than essentially expanding that view and trying to get at the domain aggregates in real time. But it introduced a new problem to the system: severe blocking. Picture the case where deliveries are being recorded while a recipient is trying to update their preferences or change their e-mail address. Since both processes need to lock and potentially update the index on the indexed view, one has to wait for the other. This can lead to a sad face on an end user.

What I ended up doing was to recant my disdain for storing redundant information, and store the domain information in the deliveries table. Yes, this was an extra 4 bytes, but as we were moving the whole system to a new 2008 cluster with a much faster I/O subsystem, we would get much more than that back with page compression. So starting over, we still have the same Domains and Recipients tables, but now domain-based reports are driven from objects that look like these:

CREATE TABLE dbo.Deliveries
(
  MessageID INT,
  RecipientID INT,
  DomainID INT,
  EventDate SMALLDATETIME,
  /* , ... other columns ... */
);
GO

CREATE VIEW dbo.vMessageDomainDeliveries
WITH SCHEMABINDING
AS
  SELECT
    MessageID,
    DomainID,
    c = COUNT_BIG(*)
  FROM
    dbo.Deliveries
  GROUP BY
    MessageID,
    DomainID;
GO

CREATE UNIQUE CLUSTERED INDEX md
  ON dbo.vMessageDomainDeliveries(MessageID, DomainID);

The up-front cost of calculating the domain of each recipient during insert is likely very equivalent to the system doing the same lookup while maintaining the indexed view (except we can use snapshot isolation in our query, but can't really enforce the same during clustered index updates). And in addition to no longer causing blocking on updates to the recipients table, we also get a much more accurate representation of history: the delivery is marked with the domain the recipient had on that day, since their e-mail address could have changed 20 times since then. All in all it has turned out that storing the domain information multiple times has helped contribute to making a very busy system suddenly seem almost idle.

The primary lesson learned here: indexed views can be a very helpful tool in your arsenal, but they can bring you down if you try to over-use them. Another lesson learned: do not assume that redundancy is the devil. It can help out in many scenarios, even if it feels a little dirty.

Connect Digest : 2009-10-30

AaronBertrand — Fri, 30 Oct 2009 15:50:00 GMT

Light helping this week. I am busy closing out a few projects (or at least chapters within projects) in preparation for PASS next week.

========================================

#506453 : Lock escalation no longer kicks in for INSERTs in SQL Server 2008

Adam found a nasty lock escalation bug in SQL Server 2008. This one is actually serious enough that it has me really hesitant about my plans for moving to the new version in production, as we process a lot of data in bulk operations like this (loading millions of rows into staging tables, then inserting/updating existing tables).

========================================

#503207 : Bad query plan leads to wrong results (incorrectly uses indexed view)

In this one there is an indexed view matching problem which can produce incorrect results. The fix is in SP1 CU1, so this isn't a "go vote for this issue" mention, but rather encouragement to get up to SP1 + the latest CU (which was Cumulative Update 4 at the time of writing) if you're currently sitting at SP1 or lower.

========================================

#504631 : add RAISE syntax for propagating exceptions

#127228 : Rethrow last error

#125719 : Support Reraising Of Original Error In Catch In T-SQL

A lot of people want RERAISE() and/or RETHROW() among other enhancements to error handling. They are definitely looking at these changes for future versions of SQL Server, so have your opinion heard!

Connect digest : 2009-07-04

AaronBertrand — Sat, 04 Jul 2009 14:07:00 GMT

I know most readers are out polishing their grills, buying hamburger buns and preparing potato and macaroni salads. I am recovering from a pre-4th party, so here I am, groggy and online, finally not the host of Independence Day madness. :-)

This week I only have a few entries for the Connect digest:

=================================

#471201 : Incorrect query results with Enterprise edition indexed view substitution and NULL usage.

This is a new one filed this past week, which Microsoft believes they have fixed, in spite of the fact that several other people, myself included, have confirmed that it still exists in current builds. It is also an issue in SQL Server 2005, which they fail to acknowledge.

=================================

#472439 : Indexed View create via CREATE SCHEMA statement can produce error 8646 during updates

Another indexed view issue filed yesterday, and also involving NULLs, in this item the submitter shows how a simple indexed view with two rows can succumb to the 8646 error (unable to find index entry) and the ominous:

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

=================================

#257502 : Deprecation of sysprocesses - DMV's doesn't fully replace all columns

This is one that many of us have been complaining about since 2005 was released : because sys.dm_exec_requests only shows *active* sessions, and it is the primary place to obtain database context, it is very cumbersome to get database context for sessions that are not currently performing work. You also can't easily get the open transaction count from the new DMVs. This information was readily available in sysprocesses, of course, though we have been told that we should stop using this system table (actually now a backward-compatibility view) in favor of the new views. But Microsoft simply can't deprecate sysprocesses until they duplicate its functionality elsewhere.

=================================

#472266 : Install fails when choosing a path for tempdb two folders deep

This was an interesting one filed this week, where installation of SQL Server 2008 fails if you specify a folder for tempdb that contains the name "tempdb" (which is certainly a valid approach). The submitter did not get the symptom right; they think it has something to do with the number of folders in the path, but it is still an issue that Microsoft should look at.

=================================

SQL Management Studio 2008 - enter Data Type of datetimeoffset(0) reverts to datetimeoffset(7)

More issues with the table designer... it's like a never-ending stream of scenarios that weren't tested or were deemed "good enough." This time someone noticed that when you tab out of a DATETIMEOFFSET column's data type, after having changed the precision to 0, it reverts to 7. Some related Connect items:

#333445 : SSMS 2005 - Bug Table Designer substitues its own length , loses user input on changing data types

#366117 : RTM, SSMS: changing precision on datetime2 from default in table designer reverts back to 7

#124872 : Table Designer does not support newSequentialId()

#362699 : 2008 RTM, SSMS/Engine: Table designer doesn't script WHERE clause in filtered indexes

#124658 : SSMS Table Designer adds unwanted default to uniqueidentifier column

=================================

Sorry I couldn't collate more this week, but as New England is seeing the sun for the first time in weeks, I really feel the need to go out and enjoy the holiday!