Archive for February, 2013

SQL Server–Plan Freezing February 26th, 2013

Vinod Kumar

This is in continuation to the posts around hints written earlier – SQL Server TABLE HINT and SQL Server FORCESEEK Hint. In a lot of places we did mention about Plan Guides and Plan freezing over those articles and didn’t show what it actually is. The use of Plan freezing is something similar to KEEPFIXED PLAN which we are used too before. But this is even more powerful than you can ever think. The series of posts are not to encourage you to use these techniques extensively in your environments but should be used as a last resort in your code. SQL Server does a more than decent job is choosing the right plan and sometimes we need to guide it in case of upgraded code, old behaviors, third party code that cannot be changed etc.

SQL Server first added the ability to create plan guides in SQL Server 2005. By applying a hint at the server side, a DBA could override the behavior for a specific problematic query. However, with the first release of plan guides it was recommended that you only create a plan guide when it was strictly necessary and keep the number of plan guides to a minimum.

The plan freezing framework in SQL Server 2008 builds on plan guides and is intended to “lock down” plans for all repeated queries against a database or the entire system as opposed to just a handful of queries. If the server handles a well defined, repeated set of parameterized queries such as queries from an OLTP-based application, then plan freezing allows an administrator to pick a time when the system is running well (i.e., they are happy with the query plans that are being used), “freeze” those plans, thereby ensuring that any subsequent compiles/recompiles don’t pick a different plan and introduce unpredictability into the system.

changes to plan guides and plan forcing in SQL 2008:

  1. In previous versions, if a plan guide applied a hint which resulted in a “no plan” error (8722), the error was returned to the client and the query did not run. In SQL Server 2008, if the hint results in a no plan error the error is caught internally and the query is re-optimized without the hints. You can monitor when this occurs by capturing the Plan Guide Unsuccessful trace event or by using the SQL Server:SQL Statistics\Misguided plan executions/sec performance counter.
  2. Management Studio now allows you to script out plan guides
  3. A new sys.fn_validate_plan_guide function is added which allows you to validate a plan guide and see any error(s) that might occur because of the hints that were applied
  4. The new sys.sp_create_plan_guide_from_handle procedure allows you to create a plan guide with a USE PLAN hint on a statement based on the current cached plan. This feature is what makes it possible to “freeze” plans for a whole database or system.

Knowing the fineprints

The hint in a plan guide created by sys.sp_create_plan_guide_from_handle is a USE PLAN hint (MSDN) based on the current Showplan XML for the statement. The Showplan XML output always contains fully qualified object names ([dbname].[schema].[owner]). When attempting to force a plan, the object names must be valid. If you freeze plans in a database, backup that database and then restore it with a different database name (such as on a test server, bring in a clone database, etc) all of these plan guides with the USE PLAN hints become invalid since the database name changed.

Because SQL 2008 no longer fails the query when the hints are invalid, any queries will run with whatever query plan the optimizer would normally create for that statement rather than with the plan specified in the USE PLAN hint. Fn_validate_plan_guide can be used to determine if there are validation errors of this nature (as long as the original database doesn’t also exist on this server—in which case the object does exists and fn_validate_plan_guide succeeds but the plan guide isn’t used because the object isn’t in the correct database context). The only way to fix this is to script out the plan guide, perform a find/replace on the database name and then recreate the plan guide(s).

Plan Freezing at work

In this section we will walk through freezing a plan, modifying the table so that the forced plan is no longer valid, then using the new trace events and fn_validate_plan_guide to view how we could determine why the plan guide is no longer successful.

1. I am doing this on a test environment and to ensure we get consistent results – we are going to clear the procedure cache using the following command. Please DONOT do this on your production environments.

DBCC FREEPROCCACHE

2. Next let us run a Dynamic SQL query that will insert into the cache.

EXEC SP_EXECUTESQL N’SELECT COUNT(*) as c

FROM Sales.SalesOrderHeader h

JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate BETWEEN @P1 AND @P2′,

N’@P1 DATETIME,@P2 DATETIME’, ‘20050730’, ‘20050731’

GO

3. Let us find the plan handle for the query that was run in the previous step.

SELECT plan_handle FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

WHERE text LIKE ‘%SalesOrderHeader%’

4. Using the Plan Handle from previous step, let us create a plan guide for the query.

sp_create_plan_guide_from_handle ‘My_Plan_Guide’,

0x06000E00A81E612F7008DA7D04000000010…

5. Let us run the query again now with SHOWPLAN XML enabled.

SET STATISTICS XML ON

GO

EXEC sp_executesql N’SELECT COUNT(*) as c

FROM Sales.SalesOrderHeader h

JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate BETWEEN @P1 AND @P2′,

N’@P1 DATETIME,@P2 DATETIME’, ‘20050730’, ‘20050731’

GO

SET STATISTICS XML OFF

Click on the XML hyperlink to view the query plan. Note in the Properties pane that it shows the plan guide was used. Also note that the query plan uses an index named: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

6. For illustration purposes let us DROP the index used in the query plan. Remember, we are doing this with-in a transaction so that we can rollback the same for learning purposes.

BEGIN TRAN

ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

7. Using Profiler, let us try to catch the Plan Guide UnSuccessful events. Below is the event from Profiler. Run the same query from Step 5 again to check if it now produces this event. The XML Showplan also verifies that it didn’t use the plan guide we created before.

image

8. To determine why the plan guide is not used, look up the plan guide ID and run fn_validate_plan_guide with this ID to see any errors are reported. Note that in this case it specifically indicates that the object referred to in the query is not present.

SELECT plan_guide_id FROM sys.plan_guides

WHERE name = N’My_Plan_Guide’;

SELECT * FROM sys.fn_validate_plan_guide(<plan_guide_id>)

It is interesting to see how the error gets reported. The message is as below.

Index ‘AdventureWorks2012.Sales.SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID’, specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

9. We can Disable, Enable or even Drop a plan guide using the following commands.

–Disable the plan guide.

EXEC sp_control_plan_guide N’DISABLE’, N’My_Plan_Guide’;

GO

–Enable the plan guide.

EXEC sp_control_plan_guide N’ENABLE’, N’My_Plan_Guide’;

GO

–Drop the plan guide.

EXEC sp_control_plan_guide N’DROP’, N’My_Plan_Guide’;

GO

We can use sp_control_plan_guide to disable ALL planguides for a given database too. Check the MSDN links for more information.

10. Since we started a transaction earlier, let us rollback the same.

ROLLBACK TRANSACTION

Final words

Hope these steps gave you a high level understanding of how plan freezing works inside SQL Server. Maybe in future posts, will also show how to use Plan Guides with a step by step as above. Do tell me if you are using these in your environments and if so would love to know what is the scenario for the same.

Continue reading...


 

SQL Server TABLE HINT February 25th, 2013

Vinod Kumar

Last week we discussed SQL Server FORCESEEK Hint and it had some interesting needs from an ISV application deployments. On the same lines, I wanted to also mention the extension to using TABLE hints. The TABLE HINT clause in SQL Server 2008 allows us to specify table-level hints as query hints in the OPTION clause of the query. This feature was added to provide greater flexibility for the set of hints that could be applied via a plan guide where the only choice is to apply a query hint. In all other situations it is preferable to specify the hint using existing syntax (i.e., after the table name).

Below is a classic example of using TABLE HINT in for a classic hint done in regular syntax:

CREATE TABLE tblTest (col1 INT NOT NULL PRIMARY KEY, col2 INT)

CREATE INDEX indCol2 ON tblTest (col2)

GO

SELECT * FROM tblTest WITH (FORCESEEK, INDEX(indCol2))

WHERE col2 = 100

GO

SELECT * FROM tblTest

WHERE col2 = 100

OPTION (TABLE HINT (tblTest, FORCESEEK, INDEX(indCol2)))

GO

The table name or alias is specified in the TABLE HINT clause followed by a list of hints to specify. Because the TABLE HINT was designed to be used with plan guides — use where the DBA needs to control the performance but doesn’t have control over the client application. It is important that any table hints must not change the semantics or correctness of the query.

The hints in the TABLE HINT clause are merged with those table hints specified in the query itself. If a semantic-affecting hint also appears in the query with a WITH clause then it is actually required to include the same hint again in the OPTION/TABLE HINT clause so as to avoid “removing” a semantic-affecting hint that the application actually depends on. If the semantic affecting hint is omitted then you’ll also get an error:

Msg 8722, Level 16, State 1, Line 1

Cannot execute query. Semantic affecting hint ‘nolock’ appears in the ‘WITH’ clause of object ‘tblTest’ but not in the corresponding ‘TABLE HINT’ clause. Change the OPTION (TABLE HINTS…) clause so the semantic affecting hints match the WITH clause.

In order to understand the same, the following queries show how to write this:

— Simple query to add FORCESEEK.

SELECT * FROM tblTest WITH (NOLOCK)

WHERE col1 = 0

GO

— This will generate error 8722 as we showed before

SELECT * FROM tblTest WITH (NOLOCK)

WHERE col1 = 0

OPTION (TABLE HINT (tblTest, FORCESEEK))

GO

— This is the correct way to add the FORCESEEK hint with nolock

SELECT * FROM tblTest WITH (NOLOCK)

WHERE col1 = 0

OPTION (TABLE HINT (tblTest, NOLOCK, FORCESEEK))

GO

You can also use table alias in your table hints. If the hint is not available with the query, then we will get the following error:

Msg 8723, Level 16, State 1, Line 1

Cannot execute query. Object ‘t’ is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

If the application developer originally specified a non-semantic affecting table hint (e.g. FORCESEEK, INDEX(name)) but did so in a way that it actually causes slower performance then it may be desirable to override/disable that hint through the plan guide. We can do this by specifying a TABLE HINT that specifies only the table name, as shown below:

SET SHOWPLAN_TEXT ON

GO

— Sample query that used index hint incorrectly

SELECT * FROM tblTest WITH (INDEX(indCol2))

WHERE col1 = 0

GO

— Empty TABLE HINT will disable the hint in WITH clause

SELECT * FROM tblTest WITH (INDEX(indCol2))

WHERE col1 = 0

OPTION (TABLE HINT (tblTest))

GO

The plans for the two queries above are shown below. Note how the plan for the first query used an index scan seek it is not possible to seek on column col1 since it is not indexed. The second plan shows that by overriding the index hint the optimizer chose the expected plan which seeks on col1.

StmtText
———————————————————————————
  |–Index Scan(OBJECT:([tempdb].[dbo].[tblTest].[indCol2]),  WHERE:([tempdb].[dbo].[tblTest].[col1]=(0)))

StmtText
———————————————————————————
  |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[tblTest].[PK__tblTest__357D0D3EB5A0DD91]), SEEK:([tempdb].[dbo].[tblTest].[col1]=(0)) ORDERED FORWARD)

I have seen this last technique as a awesome way to override wrong hint specified by developers. Use these hint techniques with care and I highly recommend you to test the same before using the same in your environments. Do feel free to pass me your comments.

Continue reading...


 

SQL Server FORCESEEK Hint February 22nd, 2013

Vinod Kumar

Generally I am not a big fan of using hints inside SQL Server. Having said that, have seen many customers use wrong hints and not knowing what they are used for. From SQL Server 2008, there were several interesting new hints introduced that are lesser known to developers. One such hint is around FORCESEEK. Please use this with care and after proper testing in your environment.

The FORCESEEK table hint instructs the optimizer to only use an index seek (clustered or non-clustered index) as the access path for a given table. If you also specify an index hint, it will only produce a query plan with a seek on the specified index. If an index seek plan is not found, or is not found using the specified index, the query compilation will fail with error 8622:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

With only an index hint we can force the use of a particular index but we cannot force an index seek. If you forced a particular index, the query might still perform a scan instead of an index seek. Here is below is one such example:

CREATE TABLE test (c1 INT NOT NULL PRIMARY KEY, c2 INT)

GO

CREATE INDEX i ON test (c2)

GO

SET SHOWPLAN_TEXT ON

GO

SELECT * FROM test WHERE c1 = 0 or c2 = 100

SELECT * FROM test WITH (INDEX(1)) WHERE c1 = 0 or c2 = 100

GO

SET SHOWPLAN_TEXT OFF

The output will show:

StmtText

————————————————————————–

  |–Index Scan(OBJECT:([tempdb].[dbo].[test].[i]),  WHERE:([tempdb].[dbo].[test].[c1]=(0) OR [tempdb].[dbo].[test].[c2]=(100)))

 

(1 row(s) affected)

 

StmtText

————————————————————————–

  |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[test].[PK__test__3213663B2D517BEF]), WHERE:([tempdb].[dbo].[test].[c1]=(0) OR [tempdb].[dbo].[test].[c2]=(100)))

Now if we try to use the FORCESEEK hint to the above query like:

SELECT * FROM test WITH (FORCESEEK)

WHERE c1 = 0 or c2 = 100

If the query doesn’t get satisfied using this hint, we will still get the error 8622. And the output is interesting to see for above query:

StmtText

————————————————————————-

  |–Stream Aggregate(GROUP BY:([tempdb].[dbo].[test].[c1]) DEFINE:([tempdb].[dbo].[test].[c2]=ANY([tempdb].[dbo].[test].[c2])))

       |–Merge Join(Concatenation)

            |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[test].[PK__test__3213663B2D517BEF]), SEEK:([tempdb].[dbo].[test].[c1]=(0)) ORDERED FORWARD)

            |–Index Seek(OBJECT:([tempdb].[dbo].[test].[i]), SEEK:([tempdb].[dbo].[test].[c2]=(100)) ORDERED FORWARD)

The FORCESEEK hint is useful to force seeks when the predicate may not be getting a good cardinality estimate, a seek is incorrectly costed as more expensive than a scan because of a number of reasons. It is also quite useful for forcing index union (e.g., test.c1 = x AND test.c2 = y) or index intersection (e.g., test.c1 = x OR test.c2 = y) plans for queries with multiple predicates on the same table, as shown in the example above.

I have always recommended to keep away from hints, but if it is a third party code and you don’t want to create any indexes etc but want to keep your logic separate from the systems source code, then using hints like these are surely something to be considered.

Continue reading...


 

SQL Collations mismatch after Upgrade / Consolidation February 14th, 2013

Vinod Kumar

Recently I was contacted by one of the customers with an interesting query. They upgraded from previous version of SQL Server and the DBA didn’t take  necessary precaution for collation in their new server. Now they need to deal with a mismatch between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS collations in their new database instance versus their old databases. This was a typical rare scenario and I had to help them.

Most DBA’s are oblivious to select an appropriate collation at the point of installation. This is a typical recipe for disaster and I thought to write about this in this blog post for people to use as reference.

What can go wrong here?

  1. Usage of Temp Tables is causing wrong results or worst is breaking now.
  2. In consolidation scenario, now 100’s of database will be in one instance there needs to be application change that needs to be enforced.
  3. If the collation was wrongly selected as case-sensitive then the stored procedure and application can go down easily as soon as the switch happens.

In my close to over a decade of working with SQL Server, I have never ever seen application developers use specific collation on their code. Under rare scenario with multiple localized content, I have seen people use COLLATE keyword in their code. That as I said is one in million sort of scenario. Would love to hear if you have used such code in your application for sure.

Do look at the bigger checklist for SQL Server 2012 if you are doing the same. The same list can be useful when moving from other versions too.

What went wrong? Why?

Most DBAs don’t pay attention to collation until it is too late. The crux of the issue is that SQL Server metadata collation follows the database collation unlike every other database – meaning the metadata or system tables are collated based on the database collation. So in a database with case-sensitive collation, the metadata is case-sensitive (object identifiers, names of tables, sorting of object names & so on).

In SQL Server 2012, we introduced the concept of a fixed catalog collation with partially contained database (CDB). So within the context of a partial CDB, the metadata collation is always fixed to a specific collation irrespective of the database collation or how the database was created on an instance with different defaults.

If you haven’t read about Contained DB, I highly recommend reading the same as it has multiple advantages like:

  1. Have case-sensitive (or other) collation behavior for user data without having to endure a case-sensitive catalog.
  2. Consolidate databases from servers with differing collations without having to rework the code to add explicit collation comparisons.
  3. And of course move a database from one server to another and have the TempDb collation behavior the same.

Know your collation

In SQL Server 2012, you can use the catalog_default keyword when defining your columns for temporary tables. This will prevent the collation conflict issue when joining temporary table with user table data or catalog view data. In older versions, you can use database_default keyword.

See example below (run below in plain database and partially contained to see difference in behavior):

DECLARE @myVar SQL_VARIANT = COLLATE catalog_default;

SELECT SQL_VARIANT_PROPERTY(@myVar, ‘collation’);

SET @myVar = COLLATE database_default;

SELECT SQL_VARIANT_PROPERTY(@myVar, ‘collation’);

Basically, in a partially contained database the catalog_default will return the fixed metadata collation and in plain database it will return database collation (COLLATE property of database). The database_default keyword always returns the database collation. If you use these keywords then you don’t need to specify the collation name explicitly in your code for the temporary table column definitions.

Using COLLATE database_default for column definitions in temporary tables will avoid the issue in older versions of mismatch. Or you can also use table variables instead where the column definition defaults to database collation if collate clause is absent.

Final words

Though we discuss the workarounds here. This is a postmortem work. I highly recommend that if you are an DBA, please ask what the collation should be. Ask what the application design requirements are. Ask if contained DB is being used. Worst case, if you have made the mistake of defaults – it is never too late to reinstall SQL Server afresh as an worst case scenario to avoid all these heart burns :). Please feel free to share your stories with me if you have anything interesting like this that did happen in your organization.

Continue reading...


 

Picture Post: Presentation Reality February 11th, 2013

Vinod Kumar

It takes one hour of preparation for each minute of presentation time. – Wayne Burgraff

Preparing for presentations is a tough job. I have done tons of presentations and every presentation doesn’t go as per the plan. We might rehearse, prepare and dry run a million times before we get on the stage. But the reality is, the situation and time we get on stage is completely a different story.

If you are a frequent presenter, you need to think on our feet before we get our chance. A number of times my presentations have been cut to half the time or even lesser by the organizers. It is tough and I thought of sharing what most presenters go through.

Do feel free to check my Presentation Tips that I wrote long time back. It is a long read but worth read if you want to become a regular presenter.

There are always three speeches, for every one you actually gave. The one you practiced, the one you gave, and the one you wish you gave. – Dale Carnegie

If you have tales to share, drop your comments. I am sure you would have also gone through such times in your presentation. Do tell me about your presentation bloopers.

No one ever complains about a speech being too short! – Ira Hayes

Even the best have their bad days :) – Have fun:

Continue reading...