sql employee pay in adventureworks database

SQL EMPLOYEE PAY IN ADVENTUREWORKS DATABASE UPDATE
SQL EMPLOYEE PAY IN ADVENTUREWORKS DATABASE MANUAL

Microsoft may periodically add enhancements to SQL Server without waiting for a major version change, through Cumulative Updates. , CAST(p.query_plan AS XML) AS query_plan SELECT CAST(event_data as xml) AS event_dataįROM sys.fn_xe_file_target_read_file('query_store_plan_forcing_user_change*.xel', null, null, null) WITH cte AS N'datetime') AS EventDatetime The XEvent data can be queried in SQL to be joined with DMVs to retrieve the query text for example: use AdventureWorks Select plan_id, query_id, query_plan_hash To get this information I think we have no choice but to use the sys.query_store_plan DMV. I am not aware of another XEvent which can collect the fact a plan has been forced by the Automatic Tuning and I think this is something that should also be available. As the name and the description mention, it only applies to user-triggered changes. It successfully force the best plan for that query but did not fire the XEvent. SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)

I enabled the automatic tuning feature which forces the last known good plan automatically. Same thing for Unforcing a plan: Is it working with automatic tuning? We know who has changed forced a plan (hostname, username, etc.), what plan it is, and for what query. We can retrieve from the Extended Event the query_id, plan_id, and the is_forced_plan field.

Forcing a planĪ plan can be forced with the following stored procedure or using the SSMS built-in report. In such cases, one might want to force the first plan (with a Scan) for all parameters. The second plan is to use a Nest Loops, it’s the best plan for a small result set, so the Switzerland parameter, but it’s a disaster performance-wise for a larger number of rows like the USA parameter. The first plan is an index Scan, it’s best for larger data sets like the USA parameter, and it’s fine for the Switzerland parameter. Looking at the Query Store we have 2 execution plans for the same query. alter database scoped configuration clear procedure_cacheĮxec dbo.GetAverageSalary dbo.GetAverageSalary 50Īlter database scoped configuration clear procedure_cache So when executing the stored procedure alternating the two countries for the parameter associated with a plan cache eviction (forced with the CLEAR PROCEDURE_CACHE command) we create a parameter sniffing scenario. To create a parameter sniffing scenario I have inserted way more employees in the USA than in Switzerland. Where Country = is a very basic employees table with a Salary column and a Country column. 'Switzerland Employee: ' + convert(varchar(5),Num),Ĭreate nonclustered index IDX_Employees_CountryĬreate proc dbo.GetAverageSalary varchar(64) ,Nums(Num) as (select row_number() over (order by (select null)) from N3)

'USA Employee: ' + convert(varchar(5),Num), Insert into dbo.Employees(ID, Number, Name, Salary, Country) ,Nums(Num) as (select row_number() over (order by (select null)) from N5) ,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) - 65,536 rows ,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) - 256 rows ,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) - 16 rows ,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) - 4 rows with N1(C) as (select 0 union all select 0) - 2 rows Here is the preparation script if you want to follow along: ALTER DATABASE AdventureWorks SET QUERY_STORE = ON ĭROP PROC IF EXISTS dbo.GetAverageSalary Ĭonstraint PK_Employees primary key clustered(ID) To test this extended event here is a simple demo of parameter sniffing using the AdventureWorks database. Parameter Sensitive plan (parameter sniffing) demo It is set to false when the user unforce a plan. The new extended event, query_store_plan_forcing_user_change, comes with a few fields related to the plan being forced, the query, and most importantly the “is_forced_plan” field. This new event is added to an already well-stocked list of extended events available around the Query Store. What this new event does is very simple, and it’s what is described in its KB:Īn Extended Event (XEvent), query_store_plan_forcing_user_change, is added to optionally track when users manually force or unforce a plan for a particular query in the Query Store.

SQL EMPLOYEE PAY IN ADVENTUREWORKS DATABASE MANUAL

There is a tiny KB dedicated for this new XEvent, see: KB5012964 – Improvement: Add an XEvent for tracking manual user plan forcing and unforcing

SQL EMPLOYEE PAY IN ADVENTUREWORKS DATABASE UPDATE

In this blog post, I will test this new XEvent.įor details about the latest CU see: KB5011644 – Cumulative Update 16 for SQL Server 2019 One of these improvements is the addition of an extended event to identify the users forcing or unforcing an execution plan via the Query Store. It brings many bug fixes and some small improvements. The latest Cumulative Update for SQL Server 2019 has been released this week on Monday.

Sql employee pay in adventureworks database