Search results matching tag 'Bugs'

Incorrect Results with Indexed Views

Paul White — Tue, 05 Feb 2013 22:18:11 GMT

Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results.

Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to:

SQL Server 2012
SQL Server 2008 R2
SQL Server 2008

The Knowledge Base article does not go into much detail, or provide a reproduction script, but this blog entry does :)

The KB does say that reproducing the bug requires these features:

An indexed view on two tables that have a foreign key relationship
An update performed against the base tables
A query executed against the indexed view using a NOEXPAND hint

There are two important details I would like to add right away:

The NOEXPAND hint is not required to reproduce the bug on Enterprise Edition
The update must be performed by a MERGE statement

The fix is available in the following cumulative update packages:

Cumulative Update 2 for SQL Server 2012 SP1 [build 11.0.3339]
Cumulative Update 5 for SQL Server 2012 RTM [build 11.0.2395]
Cumulative Update 4 for SQL Server 2008 R2 SP2 [build 10.50.4270]
Cumulative Update 10 for SQL Server 2008 R2 SP1 [build 10.50.2868]
Cumulative Update 8 for SQL Server 2008 SP3 [build 10.00.5828]

No service pack contains this fix, you must apply one of the hotfix packages above.

Steps to Reproduce

The first thing we will need is two tables:

     CREATE TABLE dbo.Parent 

    (

        parent_id integer IDENTITY NOT NULL,

        value varchar(20) NOT NULL,

        CONSTRAINT PK_Parent_id 

            PRIMARY KEY CLUSTERED (parent_id)

    );

    GO

    CREATE TABLE dbo.Child

    (

        child_id integer IDENTITY NOT NULL,

        parent_id integer NOT NULL,

        CONSTRAINT PK_Child_id 

            PRIMARY KEY CLUSTERED (child_id)

    );

And a few rows of data:

    INSERT dbo.Child (parent_id)

    SELECT New.parent_id 

    FROM 

        (

        INSERT Parent 

        OUTPUT inserted.parent_id 

        VALUES 

            ('Apple'), 

            ('Banana'), 

            ('Cherry')

        ) AS New;

The tables now look like this (parent first):

We can now add the required FOREIGN KEY relationship:

    ALTER TABLE dbo.Child

    ADD CONSTRAINT FK_Child_Parent

    FOREIGN KEY (parent_id)

    REFERENCES dbo.Parent (parent_id);

Next, a very simple indexed view that joins the two tables (the view could contain more complex features like aggregates):

    CREATE VIEW dbo.ParentsAndChildren

    WITH SCHEMABINDING 

    AS

    SELECT 

        p.parent_id, 

        p.value, 

        c.child_id

    FROM dbo.Parent AS p

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

    GO

    CREATE UNIQUE CLUSTERED INDEX cuq 

    ON dbo.ParentsAndChildren (child_id);

The final step is to use a MERGE statement to make some changes to the Parent table:

    DECLARE @ParentMerge AS TABLE

    (

        parent_id integer PRIMARY KEY, 

        value varchar(20) NOT NULL

    );

    INSERT @ParentMerge

        (parent_id, value)

    VALUES

        (1, 'Kiwi Fruit'),

        (4, 'Dragon Fruit');

    MERGE dbo.Parent AS p

    USING @ParentMerge AS s ON 

        s.parent_id = p.parent_id

    WHEN MATCHED THEN 

        UPDATE SET value = s.value

    WHEN NOT MATCHED THEN 

        INSERT (value) VALUES (s.value)

    OUTPUT 

        $action, 

        inserted.parent_id, 

        deleted.value AS old_value, 

        inserted.value AS new_value;

This MERGE performs two actions:

Updates the value column of parent row 1 from Apple to Kiwi Fruit
Adds a new parent row 4 for Dragon Fruit

The statement includes an OUTPUT clause to show the changes it makes (this is not required for the repro):

This confirms that the changes have been made as we requested: parent row 1 has changed; and row 4 has been added. The changes are reflected in the base tables:

    SELECT * FROM dbo.Parent AS p;

    SELECT * FROM dbo.Child AS c;

As highlighted, row 1 has changed from Apple to Kiwi Fruit and row 4 has been added.

We do not expect to see row 4 in the indexed view because there are no child records for that row, and the indexed view uses an inner join. Checking the indexed view using the NOEXPAND table hint (required in non-Enterprise SKUs to use indexes on a view):

    SELECT *

    FROM dbo.ParentsAndChildren 

    WITH (NOEXPAND);

The results are incorrect. They show the old value of the data for parent row 1.

Now we try using the EXPAND VIEWS query hint to force SQL Server to access the base tables rather than reading view indexes:

    SELECT *

    FROM dbo.ParentsAndChildren

    OPTION (EXPAND VIEWS);

This query produces correct results.

On SQL Server Enterprise Edition, the optimizer chooses whether to access the indexed view or the base tables. For following query, without any hints, the optimizer chooses not to expand the view. It reads the view index and produces incorrect results:

    -- Enterprise Edition ONLY

    SELECT * 

    FROM dbo.ParentsAndChildren;

Perhaps adding a child row to match the new parent row 4 will somehow fix things up?

    INSERT dbo.Child (parent_id) VALUES (4);

    GO

    SELECT * FROM dbo.ParentsAndChildren WITH (NOEXPAND);

    SELECT * FROM dbo.ParentsAndChildren OPTION (EXPAND VIEWS);

No. The query plan that accesses the view index still returns an incorrect value for row 1. It seems MERGE has corrupted our indexed view.

Analysis using DBCC CHECKTABLE

Checking the view with DBCC CHECKTABLE returns no errors:

    DBCC CHECKTABLE (ParentsAndChildren);

Unless we use the EXTENDED_LOGICAL_CHECKS option:

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

The damage is repairable:

    ALTER DATABASE Sandpit 

    SET SINGLE_USER 

    WITH ROLLBACK IMMEDIATE;

    GO

    DBCC CHECKTABLE (ParentsAndChildren, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    ALTER DATABASE Sandpit SET MULTI_USER;

You probably do not want to set your database to SINGLE_USER mode and run a DBCC repair after every MERGE statement, however. We could also rebuild the indexed view’s clustered index manually, of course.

Cause

For the MERGE statement above, the query optimizer builds a plan that does not update the indexed view (click to enlarge):

In a version of SQL Server with the fix applied, the same MERGE statement produces a plan that does maintain the indexed view:

The plan operators used to keep the view index in step with the base tables are highlighted. Without these operators, the changes to the base table are not correctly written to any indexes defined on the view. The root cause is related to the same simplification that allows the optimizer to remove the reference to the Parent table in this query:

    SELECT 

        COUNT_BIG(*)

    FROM dbo.Parent AS p 

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

The FOREIGN KEY relationship and NOT NULL constraints on the referencing column together mean that the join to Parent cannot affect the result of the query, so the join is simplified away. In SQL Server 2012, we can see when this simplification is performed because the following message appears when undocumented trace flags 8619 and 3604 are enabled during compilation:

The same message is emitted when a MERGE statement contains a WHEN MATCHED THEN UPDATE clause and either a WHEN NOT MATCHED THEN INSERT or WHEN MATCHED … THEN DELETE clause. These conditions combine such that the optimizer incorrectly concludes that a table reference can be removed, when in fact it is needed later on when the update side of the plan is built.

Other details of the query and database can affect whether the simplification can be misapplied. For example, if the FOREIGN KEY constraint contains an ON DELETE CASCADE clause, and the MERGE contains a DELETE clause, the simplification is not performed, the TF 8619 message does not appear, and the bug does not manifest.

The key to determining whether a particular query is vulnerable to this bug (TF 8619 aside) is to check whether the query plan includes operators to maintain the indexed view. At a minimum, you should see a update operator for the view:

SQL Sentry Plan Explorer identifies the operator as applying to a view explicitly, in SSMS you need to click on the graphical operator and inspect the Properties window.

Summary

The updated conditions for incorrect results are:

An indexed view that joins tables
Two of the tables have a single-column FOREIGN KEY constraint
A MERGE statement contains an UPDATE action that affects one of the tables
The MERGE statement also contains an INSERT or DELETE action (or both)
The optimizer applies a simplification that removes a table reference based on the FK relationship and other metadata
As a result, the MERGE execution plan does not contain the operators necessary to correctly maintain the indexed view
A subsequent query plan accesses an index on the view, either explicitly or via indexed-view matching (Enterprise Edition)

Note:

The simplification is not applied in tempdb
The simplification is not applied to multi-column foreign key constraints

Under these conditions, the indexes on the view do not reflect the state of the base tables and incorrect results are returned. Once the hot fix is applied, the optimizer does not misapply the simplification so the correct indexed view maintenance features are built into execution plans.

Update: I am adding this based on Ian Yates’ great question in the comments: my expectation is that applying this hotfix will not remove any existing indexed view corruption. You would need to test the hotfix as usual, apply it, and then either rebuild all affected indexed views manually, or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option (which could take a long time).

Twitter: @SQL_Kiwi
Email: SQLKiwi@gmail.com

MERGE Bug with Filtered Indexes

Paul White — Sun, 09 Dec 2012 17:54:00 GMT

A MERGE statement can fail, and incorrectly report a unique key violation when:

The target table uses a unique filtered index; and
No key column of the filtered index is updated; and
A column from the filtering condition is updated; and
Transient key violations are possible

Example Tables

Say we have two tables, one that is the target of a MERGE statement, and another that contains updates to be applied to the target. The target table contains three columns, an integer primary key, a single character alternate key, and a status code column. A filtered unique index exists on the alternate key, but is only enforced where the status code is ‘a’:

     CREATE TABLE #Target 

    (

        pk          integer NOT NULL, 

        ak          character(1) NOT NULL,

        status_code character(1) NOT NULL,

        PRIMARY KEY (pk)

    );

    CREATE UNIQUE INDEX uq1

    ON #Target (ak)

    INCLUDE (status_code)

    WHERE status_code = 'a';

The changes table contains just an integer primary key (to identify the target row to change) and the new status code:

    CREATE TABLE #Changes

    (

        pk          integer NOT NULL,

        status_code character(1) NOT NULL,

        PRIMARY KEY (pk)

    );

Sample Data

The sample data for the example is:

    INSERT #Target 

        (pk, ak, status_code)

    VALUES 

        (1, 'A', 'a'),

        (2, 'B', 'a'),

        (3, 'C', 'a'),

        (4, 'A', 'd');

    INSERT #Changes

        (pk, status_code)

    VALUES

        (1, 'd'),

        (4, 'a');

         Target                     Changes
╔════╦════╦═════════════╗    ╔════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣
║ 1 ║ A ║ a           ║    ║ 1 ║ d           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ a           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝
║ 4 ║ A ║ d           ║
╚════╩════╩═════════════╝

The target table’s alternate key (ak) column is unique, for rows where status_code = ‘a’. Applying the changes to the target will change row 1 from status ‘a’ to status ‘d’, and row 4 from status ‘d’ to status ‘a’. The result of applying all the changes will still satisfy the filtered unique index, because the ‘A’ in row 1 will be deleted from the index and the ‘A’ in row 4 will be added.

Merge Test One

Let’s now execute a MERGE statement to apply the changes:

    MERGE #Target AS t

    USING #Changes AS c ON 

        c.pk = t.pk

    WHEN MATCHED 

        AND c.status_code <> t.status_code 

        THEN UPDATE SET status_code = c.status_code;

The MERGE changes the two target rows as expected. The updated target table now contains:

╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣
║ 1 ║ A ║ d           ║ <—changed from ‘a’
║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║
║ 4 ║ A ║ a           ║ <—changed from ‘d’
╚════╩════╩═════════════╝

Merge Test Two

Now let’s repopulate the changes table to reverse the updates we just performed:

    TRUNCATE TABLE #Changes;

    INSERT #Changes

        (pk, status_code)

    VALUES

        (1, 'a'),

        (4, 'd');

This will change row 1 back to status ‘a’ and row 4 back to status ‘d’. As a reminder, the current state of the tables is:

         Target                        Changes
╔════╦════╦═════════════╗    ╔════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣
║ 1 ║ A ║ d           ║    ║ 1 ║ a           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ d           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝
║ 4 ║ A ║ a           ║
╚════╩════╩═════════════╝

We execute the same MERGE statement:

    MERGE #Target AS t

    USING #Changes AS c ON 

        c.pk = t.pk

    WHEN MATCHED 

        AND c.status_code <> t.status_code 

        THEN UPDATE SET status_code = c.status_code;

However this time we receive the following message:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#Target' with unique index 'uq1'. The duplicate key value is (A).
The statement has been terminated.

Applying the changes using UPDATE

Let’s now rewrite the MERGE to use UPDATE instead:

    UPDATE t

    SET status_code = c.status_code

    FROM #Target AS t

    JOIN #Changes AS c ON

        t.pk = c.pk

    WHERE

        c.status_code <> t.status_code;

This query succeeds where the MERGE failed. The two rows are updated as expected:

╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣
║ 1 ║ A ║ a           ║ <—changed back to ‘a’
║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║
║ 4 ║ A ║ d           ║ <—changed back to ‘d’
╚════╩════╩═════════════╝

What went wrong with the MERGE?

In this test, the MERGE query execution happens to apply the changes in the order of the ‘pk’ column.

In test one, this was not a problem: row 1 is removed from the unique filtered index by changing status_code from ‘a’ to ‘d’ before row 4 is added. At no point does the table contain two rows where ak = ‘A’ and status_code = ‘a’.

In test two, however, the first change was to change row 1 from status ‘d’ to status ‘a’. This change means there would be two rows in the filtered unique index where ak = ‘A’ (both row 1 and row 4 meet the index filtering criteria ‘status_code = a’).

The storage engine does not allow the query processor to violate a unique key (unless IGNORE_DUP_KEY is ON, but that is a different story, and doesn’t apply to MERGE in any case). This strict rule applies regardless of the fact that if all changes were applied, there would be no unique key violation (row 4 would eventually be changed from ‘a’ to ‘d’, removing it from the filtered unique index, and resolving the key violation).

Why it went wrong

The query optimizer usually detects when this sort of temporary uniqueness violation could occur, and builds a plan that avoids the issue. I wrote about this a couple of years ago in my post Beware Sneaky Reads with Unique Indexes (you can read more about the details on pages 495-497 of Microsoft SQL Server 2008 Internals or in Craig Freedman’s blog post on maintaining unique indexes). To summarize though, the optimizer introduces Split, Filter, Sort, and Collapse operators into the query plan to:

Split each row update into delete followed by an inserts
Filter out rows that would not change the index (due to the filter on the index, or a non-updating update)
Sort the resulting stream by index key, with deletes before inserts
Collapse delete/insert pairs on the same index key back into an update

The effect of all this is that only net changes are applied to an index (as one or more insert, update, and/or delete operations). In this case, the net effect is a single update of the filtered unique index: changing the row for ak = ‘A’ from pk = 4 to pk = 1. In case that is less than 100% clear, let’s look at the operation in test two again:

         Target                     Changes                   Result
╔════╦════╦═════════════╗    ╔════╦═════════════╗    ╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║    ║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣    ╠════╬════╬═════════════╣
║ 1 ║ A ║ d           ║    ║ 1 ║ d           ║    ║ 1 ║ A ║ a           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ a           ║    ║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝    ║ 3 ║ C ║ a           ║
║ 4 ║ A ║ a           ║                            ║ 4 ║ A ║ d           ║
╚════╩════╩═════════════╝                            ╚════╩════╩═════════════╝

From the filtered index’s point of view (filtered for status_code = ‘a’ and shown in nonclustered index key order) the overall effect of the query is:

Before           After
╔════╦════╗    ╔════╦════╗
║ pk ║ ak ║    ║ pk ║ ak ║
╠════╬════╣    ╠════╬════╣
║ 4 ║ A ║    ║ 1 ║ A ║
║ 2 ║ B ║    ║ 2 ║ B ║
║ 3 ║ C ║    ║ 3 ║ C ║
╚════╩════╝    ╚════╩════╝

The single net change there is a change of pk from 4 to 1 for the nonclustered index entry ak = ‘A’. This is the magic performed by the split, sort, and collapse. Notice in particular how the original changes to the index key (on the ‘ak’ column) have been transformed into an update of a non-key column (pk is included in the nonclustered index). By not updating any nonclustered index keys, we are guaranteed to avoid transient key violations.

The Execution Plans

The estimated MERGE execution plan that produces the incorrect key-violation error looks like this (click to enlarge in a new window):

The successful UPDATE execution plan is (click to enlarge in a new window):

The MERGE execution plan is a narrow (per-row) update. The single Clustered Index Merge operator maintains both the clustered index and the filtered nonclustered index. The UPDATE plan is a wide (per-index) update. The clustered index is maintained first, then the Split, Filter, Sort, Collapse sequence is applied before the nonclustered index is separately maintained.

There is always a wide update plan for any query that modifies the database. The narrow form is a performance optimization where the number of rows is expected to be relatively small, and is not available for all operations. One of the operations that should disallow a narrow plan is maintaining a unique index where intermediate key violations could occur.

Workarounds

The MERGE can be made to work (producing a wide update plan with split, sort, and collapse) by:

Adding all columns referenced in the filtered index’s WHERE clause to the index key (INCLUDE is not sufficient); or
Executing the query with trace flag 8790 set e.g. OPTION (QUERYTRACEON 8790).

Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible). Either change will produce a successfully-executing wide update plan for the MERGE that failed previously.

Conclusion

The optimizer fails to spot the possibility of transient unique key violations with MERGE under the conditions listed at the start of this post. It incorrectly chooses a narrow plan for the MERGE, which cannot provide the protection of a split/sort/collapse sequence for the nonclustered index maintenance.

The MERGE plan may fail at execution time depending on the order in which rows are processed, and the distribution of data in the database. Worse, a previously solid MERGE query may suddenly start to fail unpredictably if a filtered unique index is added to the merge target table at any point.

Connect bug filed here

Bug reproduced on the following SQL Server versions (all x64 Developer Edition):

2012 SP1 CUI (build 11.0.3321 – November 2012)
2008 R2 SP2 CU3 (build 10.50.4266 – October 2012)
2008 SP3 CU8 (build 10.0.5828 – November 2012)

Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

Cardinality Estimation Bug with Lookups

Paul White — Mon, 15 Oct 2012 06:25:05 GMT

Estimated row counts on key or RID lookups where a filtering predicate is applied can be wrong in SSMS execution plans. This error does not affect the optimizer’s ultimate plan selection, but it does look odd. There are other cases where estimated row counts are inconsistent (for defensible reasons) but the behaviour shown in this post in certainly a bug.

SQL Server 2005

The following AdventureWorks sample query displays TransactionHistory information for ProductID #1 and the last three months of 2003 (if you are using a more recent version of AdventureWorks, you will need to change the year from 2003 to 2007):

     SELECT

        th.ProductID,

        th.TransactionID,

        th.TransactionDate

    FROM Production.TransactionHistory AS th 

    WHERE 

        th.ProductID = 1 

        AND th.TransactionDate BETWEEN '20030901' AND '20031231';

The query plan is:

The execution flow is pretty straightforward. The plan seeks a non-clustered index on the ProductID column to find rows where ProductID = 1. This non-clustered index is keyed on ProductID alone, but the index also includes the TransactionID clustering key (to point to the parent row in the base table). The index does not cover the query (the TransactionDate column is not present in the index) so a Key Lookup is required for the TransactionDate column. The nested loops join drives a look up process such that each row from the Index Seek on ProductID = 1 results in a lookup in the clustered index to find the TransactionDate value for that row. A final Filter operator passes only rows that meet the date range condition.

The next diagram shows the same plan with cardinality estimates and extra details for the Key Lookup:

The cardinality estimate at the Index Seek is exactly correct. The table is not very large, there are up-to-date statistics associated with the index, so this is as expected. If you run a query to find all rows in the TransactionHistory table for ProductID #1, 45 rows will indeed be returned.

The estimate for the Key Lookup is exactly correct. Each lookup into the Clustered Index to find the Transaction Date is guaranteed to return exactly one row (each non-clustered index entry is associated with exactly one base table row). The Key Lookup is expected to be executed 45 times (shown circled).

The estimate for the Inner Join is exactly correct – 45 rows from the seek joining to one row each time from the lookup, gives a total of 45 rows.

The Filter estimate is also good: SQL Server estimates that of the 45 rows entering the filter, 16.9951 rows will match the specified range of transaction dates. In reality, only 11 rows are produced by this query, but that small difference in estimates is quite normal and certainly nothing to worry about here. You might like to keep that estimate of 16.9951 rows in mind, however.

SQL Server 2008 onward

The same query executed against an identical copy of AdventureWorks on SQL Server 2008 (or R2, or 2012) produces a quite different execution plan:

Instead of an explicit Filter to find rows with the requested date range, the optimizer has pushed the date predicate down to the Key Lookup (the Key Lookup now has a predicate on Transaction Date). This is a good optimization in general terms; it makes sense to filter rows as early as possible. Unfortunately, it has made a bit of a mess of the cardinality estimates in the process. The post-Filter estimate of 16.9951 rows seen in the 2005 plan has been moved to the Key Lookup. Instead of estimating one row per lookup, the plan now suggests that 16.9951 rows will be produced by each clustered index lookup – clearly not right!

To my way of thinking, the execution plan cardinality estimates should look something like this:

As shown, I personally prefer to see the inner side of a nested loops join estimate the number of rows per execution, but I understand I may be in a minority on this point. If the estimate were aggregated over the expected number of executions, the inner-side estimate would be also be 16.9951 (45 executions * 0.37767 per execution).

Cause

The query tree produced by the 2008+ optimizer looks much the same as the 2005 version – the explicit Filter is still present. However, a post-optimization rewrite occurs in the ‘copy out’ phase that removes the Filter and incorporates it in the Key Lookup seek, resulting in a residual predicate on that operator. This rewrite applies to regular scans and seeks too (so all residual predicates on scans and seeks are a result of this late rewrite).

I would like to thank Dima Piliugin (twitter | blog) for introducing me to undocumented trace flag 9130, which disables the rewrite from Filter + (Scan or Seek) to (Scan or Seek) + Residual Predicate. Enabling this flag retains the Filter in the final execution plan, resulting in a SQL Server 2008+ plan that mirrors the 2005 version, with correct estimates.

The bug is that this rewrite that does not correctly update cardinality estimates when the filter is pushed down to a lookup. I should stress that the rewrite occurs after all cost-based decisions have been made, so the incorrect estimate just looks odd and makes plan analysis harder than it ought to be. Cardinality estimates with regular scans and seeks appear to work correctly, as far as I can tell. The bug applies to both RID lookups and Key Lookups where a residual predicate is applied.

Workarounds

Using the trace flag is not a workaround because (a) it is (very) undocumented and unsupported; and (b) it results in a less efficient plan where rows are filtered much later than is optimal. One genuine workaround is to provide a covering non-clustered index (avoiding the lookup avoids the problem):

    CREATE INDEX nc1 

    ON Production.TransactionHistory (ProductID) 

    INCLUDE (TransactionDate);

With the Transaction Date filter applied as a residual predicate in the same operator as the seek, the final estimate is again as expected:

We could also force the use of the ultimate covering index (the clustered one) with a suitable table index hint:

    SELECT

        th.ProductID,

        th.TransactionID,

        th.TransactionDate

    FROM Production.TransactionHistory AS th WITH (INDEX(1))

    WHERE 

        th.ProductID = 1 

        AND th.TransactionDate BETWEEN '20030901' AND '20031231';

Again, the estimate is 16.9951 as expected.

Fixed in SQL Sentry Plan Explorer build 7.2.42.0

After this post was published on October 15 2012 I was contacted by the SQL Sentry people to see if a good workaround could be incorporated in their free Plan Explorer product. I was happy to provide a little testing and general feedback during a process that ultimately resulted in a new build being released on 31 October 2012. If only SSMS limitations could be addressed so quickly! Once you upgrade to the new version, the plan displayed for our test query is:

This is exactly the representation we would expect if the SQL Server bug did not exist (note that Plan Explorer aggregates estimated inner-side executions for you, and rounds to integer). Well done to the SQL Sentry team, especially Brooke Philpott (@MacroMullet).

Summary

Providing a covering non-clustered index to avoid lookups for all possible queries is not always practical, and scanning the clustered index will rarely be the optimal choice either. Nevertheless, these are the best workarounds we have today in SSMS.

Watch out for poor cardinality estimates when a predicate is applied as part of a lookup.

This particular cardinality estimation issue does not affect the final plan choice (the internal estimates are correct) but it does look odd and will confuse people when analysing query plans in SSMS. If you think this situation should be improved, please vote for this Connect item. It will be interesting to see how long it takes to catch up with Plan Explorer.

Connect Digest : 2011-12-20

AaronBertrand — Tue, 20 Dec 2011 16:55:00 GMT

Make SSMS start faster

A couple of years ago, I blogged about some ways to make Management Studio start faster. With the latest builds of SQL Server 2012, it seems to be slower than ever, at least when starting SSMS for the first time after a reboot or install. So I've asked for them to do something in the background on Windows start-up to cover whatever initialization costs have to be paid on first launch. This is not something I expect to happen in this release, and perhaps it will be better by the time RTM comes around. But if you find it slow as well, in addition to voting, please post - in the comments on the Connect item - your machine config and how long it takes between clicking on the shortcut and being able to work. With enough stats in there they can probably extrapolate how much time is being lost waiting for the application to load. :-)

#714652 : SSMS : Background helper / pre-loader

Let me opt out of product updates during setup

I am a big fan of streamlining slipstreaming (say that 5 times fast) - in other words, letting SQL Server setup check for product updates *before* installation, rather than the current manual process of cobbling together a setup package with a service pack and/or a cumulative update. In SQL Server 2012 we finally have this. Unfortunately, it is currently not an option; so, if your machine is not connected to the Internet for whatever reason, it hangs indefinitely on that step, trying desperately to go find those updated files. This needs to be an option so that you can proceed, even if you want to install RTM (e.g. for testing purposes) or you can't get to the Internet (now or ever). Sadly most software companies think that high speed Internet is enjoyed by all, when in fact a lot of folks can't get it or intentionally block their servers from any kind of external access.

#714661 : Scan for Product Updates hangs on isolated system

Add a time limit option to index reorg

Greg Low had an interesting idea to add a time limit to index reorg. Since the most work you'll ever lose upon cancelling is the reorganization of a single page, unlike a rebuild which has to roll back ALL of the work, it is a common practice to do a little bit of reorg at a time. But it is quite tedious to set up some kind of watchdog or to wait and kill the process manually. I can see how the syntax could work - a simple TIME_LIMIT option with the same input format as WAITFOR DELAY. So, for example, if you wanted to allow this reorg to work for up to 45 minutes and then stop:

ALTER INDEX foo 
  ON dbo.bar
  REORGANIZE WITH (TIME_LIMIT = '00:45:00');

The Connect item is currently closed as won't fix, and I think that's partially because it's had only 2 votes in over a year. Please vote and add a comment indicating why this could be useful in your environment:

#611158 : Index Reorganize time limit

Gosh, I wish I could re-use that CTE

Several folks have filed suggestions to make it easier to work with the same query multiple times. Two of the popular ideas are temporary views and module-level table expressions. The current workaround of dumping data into a #temp table has significant overhead, and can still lead to errors if the same joins and where clauses need to be repeated over and over again. I like Erland Sommarskog's module-level table expressions idea best, but temporary views have a strong use case as well. I'm hoping to see some more comments and use cases on these items so that Microsoft can evaluate

#343067 : Module-level table expressions
#640863 : Please allow creation of temporary views
#714617 : [T-SQL] : Please provide temporary views

Truncate a table with foreign keys

We all know that you can't truncate a table that has foreign keys, but why should that be the case if all the referencing tables are empty (or have nullable referencing columns that are all NULL)? They've closed this one as won't fix, but if you can see the value in this for your environment, please add a comment indicating your use case, as they seem more than willing to revisit it...

#312074 : Permit TRUNCATE TABLE when referencing tables are empty

Want your bug fixed? File a good bug!

AaronBertrand — Tue, 20 Sep 2011 14:45:00 GMT

In some circles I'm known as "the Connect guy" because I've filed a heck of a lot of suggestions and bugs that I've envisioned or encountered, and I'm often willing to file an issue on someone else's behalf. In other circles, for very much the same reasons, I'm probably known as "that annoying Connect guy." On and off over the past few years, I've assembled what I've called "Connect Digests" here on this blog, calling out some of the issues that have been filed that I've deemed as deserving attention. At some points these digests have been more regular than others, and that is totally driven by travel, schedule and workload.

Today I thought I would share a little bit of wisdom with my fellow (and potential) bug-filers. I do see a lot of bugs that get closed as Not Reproducible ("no-repro") or Won't Fix, and I don't think that they all had to be closed that way. Part of the problem can often be pointed back to the quality of the bug itself. I'm not going to name names or call out any specific "bad" bugs, but just wanted to give some hints about what they are looking for. Note that for bugs you should be able to re-open them even if they get closed, but only do so if you have new information to add, not just because you didn't like the decision.

If your bug comes back as Not Reproducible

Try to ensure that you have included complete information about the repro. If there is a crash, capture the detailed stack trace information from the event log or the application's dialog (e.g. SSMS has these details). If screen shots help with the repro, include them. If there are certain "non-standard" aspects of your environment, such as a non-default collation, different compatibility mode, database name that is a keyword, different operating system language, etc., it is possible that those differences are influential in manifesting the bug and, without that information, it might not be possible for Microsoft to repro (because they aren't going to walk through your steps using a full matrix of every possible test combination). Without full, consistent and reproducible steps to exhibit the problem, it is also impossible to know whether you've fixed the problem, and can also be difficult to determine how effective the fix is and whether or not it caused regression elsewhere.

If your bug/suggestion comes back as Won't Fix

Hopefully there is a comment that goes along with it, and the comment will explain why the issue won't be fixed. Sometimes it is just not practical to fix a bug or implement a suggestion, or to do so in the current development cycle. The following factors are all used to guide where a bug or suggestion ends up on the pipeline:

how to reproduce the problem
the frequency the problem occurs
the likelihood of the problem occurring (number of customers potentially affected)
the actual impact on users
the root cause
the cost and risk of fixing it (e.g. backward compatibility)
available work-arounds

Yes, they certainly understand that an issue might be important to you; however, there are many other factors to consider. Depending on the reason(s) stated in the comment(s) by Microsoft, you might want to come back armed with more compelling information, or more people stating their business case qualitatively (see below).

If you haven't filed your bug/suggestion yet (or it's not yours)

Please keep the above in mind. Also note that the qualitative information is at least as important, and often more so, than the quantitative information. In other words, if you just state that x is broken, needs to be fixed, and look at all these votes that support it, that is not necessarily enough to convince Microsoft that it needs to be fixed. In the description of your bug, or in a comment on someone else's, state *why* it needs to be fixed. A real business case that demonstrates significant impact on a customer goes a heck of a lot further than a few extra votes. If your arguments involve standards compliance or keeping up with a competitive RDBMS product, it certainly can't hurt to include that information as well. If you have additional information that explains why the issue hurts, or how the workaround is painful, this can be factored into the decision (unlike an extra up-vote, which to Microsoft is just an <aol>me too</aol>). Including this data can only make it more likely that the item will be seriously considered.

Summary

Connect can be an invaluable tool in getting feedback back to Microsoft and affecting change in SQL Server. It can also be quite frustrating if the flow of information - in either direction - is not the highest in quality. I hope I've given you some things to think about to at least improve one direction of that flow.

Connect Digest : 2011-04-25

AaronBertrand — Mon, 25 Apr 2011 10:54:00 GMT

I wanted to change it up a little this week. In every digest, I've pointed out a set of Connect items that I believe deserve more attention. What I fail to point out every week is the number of Connect items that actually get resolved with a fix. These are even less visible than the active requests, but I think they deserve some attention as well. Why? Well, almost every single comment on Connect is a neutral or negative comment. I think we could all be better about going back to some of these items and thanking Microsoft - not so much for the bugs, which they should be fixing regardless, but more so for the suggestions that we make.

Here are a few items where the issue has been addressed. I call these "Connect wins":

#424800 : SSMS : Expose "Connect to Server" MRU list to users

#484732 : sqlps and PowerShell V2 Issues (granted, fixed for 2008, but no word on 2008 R2 yet)

#615766 : [Denali SSMS] : Execution plan tooltip causes very slow behavior
#615767 : [Denali Engine] : Regression in query hint behavior
#621445 : Extended Events to Trace Mapping Tables in sys schema in Master
#621656 : [SSIS Denali] Synchronous Executions (though closed as fixed, no official comment from Microsoft)

#631373 : Denali - Transaction_Log Extended Event Returning Incorrect Data

Now, there is still plenty of Connect activity that seems to get ignored completely, and several cases where the last comment (four years ago) was, "We'll look at that for the next release." Still others (like #524769) have lots of dialog, but almost all of it is useless, and of course there are countless items that have been closed as "Won't Fix" or "By [questionable] Design." But for the vast amount of content these folks have to sort through, I think it's worthwhile to thank them even for small victories.

Have a Connect win to share? Please post in the comments!

A better way to search Connect

AaronBertrand — Thu, 06 Jan 2011 14:21:00 GMT

I recently spotted a comment from Microsoft on a Connect item with 13 total up-votes. The comment went something like, "wow, due to the explosive response to this issue, we're going to deal with it right away." Okay, it wasn't that emphatic, it was actually: "I've brought the MVP customer vote count to the attention of dev, and a new owner of this DMV says he will dig up some info for us." Still, knowing that I had seen other items with a much stronger response and barely a note of acknowledgment (never mind a pledge to actually act in any way), I performed a search. I started with a search for "SSMS" in my own feedback and was overloaded. So I used the advanced search to whittle it down. I tried over 100 votes and didn't get any. I started getting results (well, 1) when I scaled back to 70:

Then I scaled back to 50, and nothing significantly changed. When I scaled back to 30, though, I saw a really, well, interesting result:

Why is this interesting? Well, the item with 78 up-votes doesn't show up in a search for >= 70. It also doesn't show up in a search for >= 50. But when you lower the threshold to 30, it suddenly appears. Another interesting thing is that no matter what number I put into the vote count, items (such as #311079) with more than 100 votes never show up:

This is just the most recent bit of fascinating search logic I've discovered on Connect; there are many others over the past few years that have really made me shake my head. I've asked multiple times if we could get an API into the data to write our own searches, or be able to store backups of the data so that we can load them on our own systems and run our own searches. The response was always, predictably, "no, not cool." And they wonder why activity on Connect has dwindled (well, there are several reasons for that, which I may highlight in future blog posts).

Fast forward to last week, when Aaron Nelson (blog | twitter) and Nic Cain (blog | twitter) let me in on a little secret - they were loading Connect's SQL Server data into their own SQL Server database, via the RSS feed. Nic is starting a blog series about it, but the important points are:

You can now get more direct access to the data. Rob Farley (blog | twitter), for example, has loaded up the Denali bugs into a pivot collection, allowing you to do lots of real-time visualization against a wide variety of filters. You can also click through to items that interest you.
Aaron has posted a PowerShell script (you weren't expecting anything other than PowerShell, were you?) that will give you read-only access to the data, empowering you to write your own queries against it. I've already used it to run the following, where I was much happier with the results:

SELECT * FROM dbo.ConnectItems
  WHERE Author = 'aaronbertrand'
  AND UpVoteCount >= 30;

Note, though, that the data is not currently complete - the loading is still in process. So I'm not 100% happy with the results, but I know they'll get there.

If you want to stay on top of Connect items for SQL Server, I strongly recommend adding the new items and recently modified items feeds to your favorite RSS reader. In combination with easier searching, I find this quite useful.

An Interesting MERGE Bug

Paul White — Tue, 03 Aug 2010 17:30:00 GMT

Summary: Investigating an optimiser transformation that exposes a bug in SQL Server’s MERGE implementation.

I came across a Connect item today (by fellow SQL Server Central member ‘ALZDBA’) describing how using a combination of relatively new features can produce incorrect results or even an access violation inside SQL Server.

Reproducing the bug

We’ll look first at how MERGE can produce incorrect an output. We’ll need two tables: one that contains two rows of existing data; and a second that contains four rows of change information. The overall process looks like this:

This is a classic MERGE (or “upsert”) requirement. If the row already exists, the new value is added to it, otherwise a new row is inserted. Here’s the code to generate the sample tables and data:

DECLARE @Target
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        value   INTEGER NOT NULL
        );
 
DECLARE @Delta
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        delta   INTEGER NOT NULL
        );
 
-- Existing records (1 & 3)
INSERT  @Target
        (row_id, value)
VALUES  (1, 1),
        (3, 3);
 
-- Change table:
-- Updates rows 1 & 3
-- Inserts rows 2 & 4
INSERT  @Delta
        (row_id, delta)
VALUES  (1, 10),
        (2, 20),
        (3, 30),
        (4, 40);

We can easily write a MERGE query to perform the actions needed. Let’s also include an OUTPUT clause to show the effects on each row:

MERGE   @Target T
USING   @Delta D
        ON  D.row_id = T.row_id
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED BY TARGET THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

If we examine the @Target table after executing this query, we get exactly the results shown in the diagram. All good so far. The problem comes when we look at the rows produced by the OUTPUT clause:

When the action is ‘INSERT’, the new_value column contains an incorrect value. Instead of reporting the value actually inserted into the target table, it repeats the value associated with the last UPDATE operation. If we change the data so that multiple INSERTs are performed after the UPDATE, all the INSERTs get the same new_value associated with the prior UPDATE. The @Target table always contains the correct data – it’s just that the OUTPUT clause disagrees.

The Query Plan

The original Connect item states that this issue only affects table variables, but there’s more to see here. Let’s start by taking a look at the execution plan for the sample MERGE statement above (click to enlarge):

I have omitted several Compute Scalar iterators to make it easier to see what’s going on. There’s a scan of the @Delta table, followed by an Insert to the @Target table, and finally a Merge into the @Target table. If you are familiar with the sort of plans MERGE normally produces, you might find that arrangement a bit odd. Even if MERGE is new to you, you might think that an Insert followed by a Merge is surprising.

In a normal MERGE, we would expect to see an outer join between the source and target tables, followed by a Merge:

SQL Server performs an outer join so the Clustered Index Merge knows whether the current row already exists in the target table or not. If it exists, it is updated, otherwise a new row is inserted.

If you’ve been following my recent series on optimiser internals, you might suspect that the optimiser has applied a transformation to get the join-less plan, and the two plans are somehow logically identical. You would be right – the rule in question is LOJPrjGetToApply (Left Outer Join, Project Get to Apply).

The Optimisation

The idea behind the optimisation is to read a row from the @Delta table and immediately try to insert it into the @Target table. If that succeeds, the row didn’t already exist, and no further work is required.

If the row did exist, the PRIMARY KEY on @Target will cause a duplicate key violation to occur. The relational engine suppresses that error, and execution continues on down to the Clustered Index Merge iterator, which performs the necessary UPDATE.

This is a very useful (and perfectly safe) query optimisation if the conditions are right. There are rather a lot of conditions, the most important of which are:

The target of the merge must have a suitable clustered unique or primary key constraint
The changes (delta) table must have some sort of unique constraint (can be non-clustered) on the join column
The plan must be already using a nested loops join
The MERGE must include a NOT MATCHED condition plus at least one MATCHED clause
Equivalent columns in the two tables must have exactly the same type – no implicit conversions allowed
There can be no chance of a constraint violation – including NOT NULL constraints
The query must not update the clustered key

If any of the required conditions do not apply, the optimisation is not performed, and the query plan retains the outer join.

In order for the MERGE to return incorrect results:

The MERGE target must be a temporary table (the delta table can be anything)
The optimiser must produce a final plan that includes the transformation described above
The MERGE statement must include an OUTPUT clause that references the inserted pseudo table
The query has to perform an INSERT row action after an UPDATE (in clustered index order)

Importantly, it is only rows that result in an INSERT that cause problems. If only UPDATE or DELETE operations result from the MERGE, everything works perfectly – even with a table variable as the target.

Producing an Access Violation

The example data given for the @Delta table was carefully constructed to ensure that incorrect results were returned. With different sample data, we will get an Access Violation (AV) which terminates the connection and produces a stack dump on the SQL Server.

The simplest way (based on the test rig above) is to change the @Delta table contents to just include a single row that will result in an INSERT:

INSERT  @Delta
        (row_id, delta)
VALUES  (2, 2);

There is no row in @Target with row_id = 2, so this results in an INSERT.

To expand our test rig to encompass DELETE operations, we need to add an extra condition to the original MERGE statement. The new code will delete a record if its value is zero after applying the deltas:

MERGE   @Target T
USING   @Delta D
        ON  D.row_id = T.row_id
WHEN    MATCHED             -- New --
AND     T.value = -D.delta  -- New --
THEN    DELETE              -- New --
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED BY TARGET THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

With that modification in place, the following values in the @Delta table will update the row with row_id = 1, and delete the one with row_id = 3:

INSERT  @Delta
        (row_id, delta)
VALUES  (1, 10),
        (3, -3);

This combination works correctly because there is no INSERT operation. Any number of UPDATEs and DELETEs can be performed without issue, so long as no INSERTs occur.

Workarounds

The easiest workarounds involve preventing the query optimisation from happening in the first place. This is quite easy since there are so many conditions for it to apply. One way is to prevent the plan from using a nested loops join with an OPTION (HASH JOIN, MERGE JOIN) hint. There are many other alternatives in the same vein, of course.

A second workaround is to apply Trace Flag 8758 – unfortunately this disables a number of optimisations, not just the one above, so it’s not really recommended for long term use.

Third, we could turn off the LOJPrjGetToApply rule (again, see my previous optimiser posts) but that could also negatively affect other plans that benefit from the optimisation.

Final Thoughts

This isn’t a bug that will affect everyone, but it does show that your chances of turning up a bug increase as you push the limits of the optimiser and fairly new features like MERGE.

It will be interesting to see how the Connect item goes: will Microsoft fix this, or will they just say that MERGE was never intended for use with table variables? We’ll have to wait and see.

My thanks to ‘ALZDBA’ for reporting this problem. Personally, I see the prospect of returning incorrect data from the OUTPUT clause as more serious than the access violation. The OUTPUT clause is often used to record audit information, so producing incorrect data with no error seems undesirable. I would encourage you all to form your own view on this bug, and vote on it accordingly.

SSMS : Which parts do you love? Which parts do you hate?

AaronBertrand — Wed, 23 Jun 2010 00:56:00 GMT

I find myself categorizing a subset of SSMS features ranging from "must have" features to "cringe when they are mentioned." I'll try to summarize the standouts for you here, then ask you these questions: Which parts of SSMS do you rely on? Which do you wish were not there at all?

The "I almost always use it to get the task done." variety

SQL Server Agent's Job / Job Step Editor. While not pretty, the wizard does make quick work of creating jobs with several steps, multiple schedules, and various execution paths. (Let's just pretend the 2008 R2 version isn't a no-op due to a debilitating bug.)
Profiler. Similar to the job procedures, I am unlikely to ever get the hang of scripting a server-side trace from memory. So, I use the profiler UI to set up the trace details, then save the script for future use. If I have to run a slightly different server-side trace, I can use the same script with minimal changes; but in many cases it is just as quick to use the UI again and start from scratch. (Technically, I guess this isn't part of SSMS, but it is certainly a management UI that has had its share of criticism over the years.)
Right-click object > Modify, or Script as ALTER to New Window. This is by far the quickest way for me to get at the *current* version that is deployed, and when I am done testing my revisions against the dev server, I can then move the changes to source control. I see a lot of people using sp_helptext or manually selecting from syscomments / sys.sql_modules / OBJECT_DEFINITION(), but this can involve several more steps to get the code into suitable (and more importantly, maintainable) shape.
Registered servers. These are a god-send, especially since I've learned to export carefully constructed nodes and share them across all machines. I wish the tool in general was more consistent at remembering passwords depending on how I've initiated or switch a connection, but that's another story I suppose.

The "I will use it if I have to, but usually feel icky and prefer better alternatives." variety

Table Designer - occasionally I will use this tool (right-click a table, Design), but only at the beginning of projects, where I want to slightly adjust the column order in a new table. Even in that case, more often than not, I'll drop and re-create the table. For tables already in use, I always use DDL commands like ALTER TABLE, knowing that any new columns end up at the end of the table. And that's okay with me.
SQL Server Agent > Job History. This dialog has some real quirkiness; in our environment, it is always confused because it is being run on a desktop in one time zone against servers in another. I have been complaining about this since before SQL Server 2005 was released (though first officially documented in 2006; see Connect #124841). I also find the interface cumbersome to use and drill down - usually I get more complete and consumable information, and more immediately, from SQL Sentry's Event Manager - which also has the ability to store more history in the repository without bogging down production instances of msdb.
Select Top N Rows. This is a quick way to grab an arbitrary 1000 rows (or whatever number you customize) from a table. Sadly, it just blindly executes the query without giving you the opportunity to modify the column list, where clause or isolation level (personally, I prefer Script > As SELECT To > New Query Editor Window). Oh, and it won't necessarily put you in the context of the right database; instead, it uses a three-part name for the table, which can be frustrating if you try to add a simple join or subquery and forget which database you're really in.

The "I will not touch it with a 10-foot pole"! variety:

Activity Monitor. While this replacement of the useless view of the same name in previous iterations of the tool shows a lot of potential, and its motivation is in the right spot, it is in its infancy and has a lot of quirky behaviors. If I want a good look at what is going on in my server, I will use SQL Sentry's Performance Advisor - which does a great job of warning me about issues even when I'm not paying attention. For a really quick pulse of current activity or in environments where I haven't convinced them to use Performance Advisor, then I turn to Adam Machanic's sp_whoIsActive. For a really quick look, yes, Activity Monitor can be useful... but for very little incremental effort you can get to a lot more information without memorizing all of the DMVs.
This will be ridiculously shocking, but any of the features that allow you to shrink a database or a file. I acknowledge that there are some cases where shrinking a file is necessary, and I don't want to get into a religious battle about it. I just wish that the task was much more difficult and thought-provoking to complete. Rather than one button or checkbox, imagine if the process to shrink a file were as convoluted as setting up SQL Server? There is one case where I'd applaud a longer and more cumbersome set of steps to accomplish a simple task.
Most other designers / wizards. Including, but not limited to:

View Designer
Query Designer
Edit Top N Rows (formerly Open Table)
New/Edit Database
New/Edit Login

Go ahead, search Connect. There are dozens of bugs against these dialogs, and few if any will ever be fixed. I can quote myself on twitter from earlier tonight: "Never send a UI to do a DDL's job." As I've explained above, this isn't always true; but in most cases the DDL is going to be more predictable and also infinitely more repeatable.

The "Wait, why isn't that feature there?" variety

I'm cheating here a little bit, but there are some noticeable omissions from Management Studio that I can't even complain about because they never bothered to create them. UIs to support features that are cumbersome to set up using code alone, such as Service Broker and Extended Events (they are gradually getting to others such as Mirroring and of course the new DACPAC stuff). Or things that are long overdue but are otherwise covered by great 3rd party add-ins, like Mladen Prajdic's SSMS Tools Pack.

The "Oh yeah, I didn't even think of that one!" variety

It is a pretty vast application and there are probably several features I didn't even think to mention. So again, I'll ask: which parts of SSMS can you not live without? Which ones would you throw off a pier if you were in control?

Your work may not be lost

roman — Thu, 28 Jan 2010 15:22:00 GMT

One of the things that I found dissapointing in Management Studio 2008 is that in the event of a crash (and in addition to crashing in the first place), is that on a restart it doesn't show me a dialog with a list of auto-saved files, like my SSMS 2005 did. Yesterday as I was doing some houskeeping on my disk, I noticed a Backup folder under My Documents\SQL Server Managamnet Studio. I looked to see what's there and I saw a bunch of ~AutoRecover.~vs*.sql files, one for each query window in SSMS I was working with. This morning my SSMS crashed and when I reopened, there was no dialog for potential recovery. So I went to that folder and luckily, all the files were still there. I haven't searched Connect yet but I will, and report this as a bug. If there are any files in that folder, the user should be prompted with the option to recover.