Fantastic DMV in SQL Server 2014, sys.dm_exec_query_profiles

You can check the progress of query execution using this DMV, you can get information of each operator used in Actual Query Plan in detail through this dmv.

Lets play around with dm_exec_query_profiles.

Test 1

I opened a SSMS and run my query in session 1

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

At the same time I opened another query window(another session, session 2) and run my dm_exec_query_profiles

select * from sys.dm_exec_query_profiles

(0 row(s) affected)

What I found is zero record though a heavy query is running in session1 but it didn’t capture the query progress.

Test 2

I found that to get the result from this DMV you need to trigger this. And to trigger this DMV you need to SET STATISTICS PROFILE ON

Again in session1

SET STATISTICS PROFILE ON

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

Session 2

select * from sys.dm_exec_query_profiles

select session_id,physical_operator_name,node_id,row_count,

estimate_row_count,first_row_time,last_active_time,cpu_time_ms

from sys.dm_exec_query_profiles

So here lot of interesting information we captured through this DMV, all the information and progress of running query including the time taken by each physical operator, row count etc.

But only point of disappointment is it need either actual Query plan on or Set profiler on the running query for which we want to capture the progress of query.

My code is my fire. Fire and individual cannot sleep, when I will get prospect of sleep!!!

 

Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimation - IV CE -Running your Query under Legacy Cardinality Estimator Compatibility Level i.e Under Compatibility Level 110 in SQL Server 2014 with CL 120

Let see how we will be retrieving the legacy SQL Server 2008 compatibility level. First lets turn your SQL Server 2014 to 120 Compatibility level

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 120i.e. the highest compatibility level of SQL Family.

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 120

go

Command(s) completed successfully.

Lets fire the query in both the environment one under  SQL Server 2014 compatibility level 120 and other on SQL Server 2012 Compatibility level 120 by enabling TRACE Flag to 9481

use AdventureWorks2012

go

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 9481);

Lets see both the query plan to the check the Estimated Row Count.

Query with Compatibility Level of 120 (SQL Server 2014)

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Now here you can see, in first legacy plan the Estimated No of Rows has 1 count whereas in the second New Cardinality Estimator Plan the Estimated No of Rows has 3 counts to 2312.

Your code is warm, filled up sparks; bring into play to alleviate the world of business!!!

 

Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimator Series III- Enabling New Cardinality Estimator (SQL Server 2014) with TRACE FLAG 2312, when your DB Compatibility level set to Legacy Server i.e. SQL Server 2012,2008..

Lets first set the database SQL SERVER 2014 to the legacy compatibility level of 110 i.e. SQL Server 2008 and with TRACE FLAG we will see the new Query Plan.

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 110 i.e. SQL Server 2012

use master

go

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 110

go

Command(s) completed successfully.

Lets fire the query in both the environment one under  SQL Server 2012 compatibility level 110 and other on SQL Server 2014 Compatibility level 120 by enabling TRACE Flag to 2132

use AdventureWorks2012

go

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 2312);

Lets see both the query plan to the check the Estimated Row Count.

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Now here you can see, in first legacy plan the Estimated No of Rows has 1 count whereas in the second New Cardinality Estimator Plan the Estimated No of Rows has 3 counts to 2312.

Blaze up your code and people will come from miles to see your code burning!

 


Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimator Series II: Learning Predicates

Predicate: Predicates are the expression which evaluates to TRUE or FALSE or UNKNOWN. They comes in two form

·         Filter Predicate :

·         Join Predicate: 

Filter Predicate: The expression that comes in WHERE or HAVING clause.

Example 1.

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

So here the Filter predicates are StateProvinceID =79 and  City = 'Bothell'

Example 2.

select StateProvinceID, Count(*) as 'MoreThanOneCity'

from Person.Address

group by StateProvinceID

having count(*) > 1

Here the filter predicate is HAVING with a condition of count(*) > 1.

Join Predicate: The join condition that comes in FROM Clause of select query are considered as JOIN Predicate.

Example:

select be.BusinessEntityID,a.AddressLine1 from Person.BusinessEntity be

inner join Person.BusinessEntityAddress bea on be.BusinessEntityID = bea.BusinessEntityID

inner join Person.Address a on bea.AddressID = a.AddressID

Here the Joining condition are considered as Join predicate.

i.e. be.BusinessEntityID = bea.BusinessEntityID

bea.AddressID = a.AddressID

Code like your code shut others mouth!

Post Reference: Vikram Aristocratic Elfin Share