Just enable it so better tools can use it… for example “The Mouse and the Elephant”
Enable script.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
;WITH dbs AS
(
SELECT name
FROM sys.databases
WHERE database_id > 4 -- exclude master, model, msdb, tempdb
AND state_desc = 'ONLINE' -- only online DBs
AND is_distributor = 0 -- skip replication distributor DB
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
'ALTER DATABASE [' + name + N'] SET QUERY_STORE = ON;
ALTER DATABASE [' + name + N'] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
'), CHAR(10) + CHAR(10))
FROM dbs;
PRINT @sql; -- for audit visibility
EXEC (@sql); -- execute
Check Erik Darlings Github Repo’s you just search on the Module name ..
/* The Elephant and the Mouse, or, Parameter Sniffing in SQL Server */
-- https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
/*
SalesOrderDetailID OrderQty
42912 3
41781 1
*/
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 945;
/*
SalesOrderDetailID OrderQty
41231 2
41469 4
.. 257 rows
111133 3
110953 14
*/
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 870;
/*
.. 4.688 rows
*/
CREATE PROCEDURE Get_OrderID_OrderQty
@ProductID INT
AS
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;
/* Test 1 */
-- First
EXEC Get_OrderID_OrderQty @ProductID=897
-- Second
EXEC Get_OrderID_OrderQty @ProductID=870
dbcc freeproccache
/* Test 2 */
-- First
EXEC Get_OrderID_OrderQty @ProductID=870
-- Second
EXEC Get_OrderID_OrderQty @ProductID=897
dbcc freeproccache
Lesson: the elephant execution plan is ok for the mouse dataset, the mouse execution plan not so fine for the elephant dataset.
And in SSMS it look like this, where is the rest of the information?
