You are a database administrator on an instance of SQL Server 2008. You previously created a plan guide using the following Transact-SQL:
sp_create_plan_guide @name = N’SpecialInvTrxGuide’,@stmt = N’SELECT * FROM Inventory.TrxHistory AS h,Inventory.Product
AS p WHERE h.ProductID = p.ProductID AND TrxCategoryDesc = @CatDesc’,@type = N’OBJECT’,@module_or_batch =
N’Inventory.GetInventoryHistory’,
@params = NULL,@hints = N’OPTION (OPTIMIZE FOR (@CatDesc = N”Material Transaction”))’
You are evaluating the performance effectiveness of the plan guide. You want to disable the SpecialInvTrxGuide plan guide temporarily, but continue to use other plan guides that have been created.
Which statement should you execute?
A. EXEC sp_control_plan_guide N’DISABLE’, N’SpecialInvTrxGuide’
B. EXEC sp_control_plan_guide N’DROP’, N’SpecialInvTrxGuide’
C. ALTER GUIDE SpecialInvTrxGuide DISABLE;
D. sp_create_plan_guide @name = N’SpecialInvTrxGuide’,@stmt = N’SELECT *FROM Inventory.TrxHistory AS h,Inventory.Product
AS p WHERE h.ProductID = p.ProductID AND TrxCategoryDesc = @CatDesc’,@type = N’NULL’,@module_or_batch =
N’Inventory.GetInventoryHistory’,@params = NULL,@hints = N’OPTION (OPTIMIZE FOR (@CatDesc = N”Material Transaction”))’
Correct Answer: A
Explanation/Reference:
You should use the following statement:
EXEC sp_control_plan_guide N’DISABLE’, N’SpecialInvTrxGuide’
The sp_control_plan_guide system stored procedure can be used to drop or disable a single plan guide or to drop or disable all plan guides in the current database. The procedure accepts two parameters. The first parameter is the action to be taken, and the second parameter is the plan guide name. You may specify a value of DISABLE, DISABLE ALL, ENABLE, ENABLE ALL, DROP, or DROP ALL for the first parameter. In this scenario, you wanted to disable a single plan guide. Therefore, you should call the procedure, passing it the value of ‘DISABLE’ for the first parameter and the name of the plan guide as the second parameter. If you wanted to disable all the plan guides in the current database, you could include the DISABLE ALL value .
You should not use the statement that calls the sp_create_plan_guide system stored procedure, passing an @type parameter value of NULL. This statement will generate the following syntax error because NULL is not a valid value for the @type parameter:
Msg 10501, Level 16, State 1, Procedure sp_create_plan_guide, Line 20Cannot create plan guide ‘SpecialInvTrxGuide’ because type ‘NULL’ provided is not allowed.
You should not use the ALTER GUIDE statement because this is not a valid SQL statement. This statement will generate the following syntax error:
Msg 343, Level 15, State 1, Line 2Unknown object type ‘GUIDE’ used in a CREATE, DROP, or ALTER statement.
You should not use the statement that calls the sp_control_plan_guide system stored procedure with the DROP parameter value for the first parameter. This statement will permanently remove the plan guide from the database. In this scenario, you only wanted to disable the SpecialInvTrxGuide plan guide.
