In the last three parts, we have discussed about different queries that can be used to list the current state of the database server. In this post let us discuss about listing execution stats from the plan cache.
The below query will help us to list the CPU intensive queries from your plan cache.
/*****************************************************************************************
List heavy query based on CPU/IO. Change the order by clause appropriately
******************************************************************************************/
SELECT TOP 20
DB_NAME(qt.dbid) AS DatabaseName
,DATEDIFF(MI,creation_time,GETDATE()) AS [Age of the Plan(Minutes)]
,last_execution_time AS [Last Execution Time]
,qs.execution_count AS [Total Execution Count]
,CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Total Elapsed Time(s)]
,CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average E
xecution time(s)],CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)]
,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU]
,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS
DECIMAL(28, 2)) AS [% Waiting],CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time
average (s)],CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Physical
Read],CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical
Reads],CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical
Writes],max_physical_reads
,max_logical_reads
,max_logical_writes
, SUBSTRING (qt.TEXT,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset =
-1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.TEXT AS [Batch Statement]
, qp.query_plan
FROM SYS.DM_EXEC_QUERY_STATS qs
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY
[Total CPU time (s)]
--[Avg Physical Read]
--[Avg Logical Reads]
--[Avg Logical Writes]
--[Total Elapsed Time(s)]
--[Total Execution Count]
DESC
Let us discuss the output columns.
Column Name
Description
DatabaseName Database Context of the execution plan.
Age of the Plan(Minutes)
Age of the plan in the plan cache in minutes.
Last Execution Time
Date and Time of the last execution of this plan.
Total Execution Count Total number of execution after the last compilation
Or Total number of execution in the last [Age of the Plan(Minutes)]
(since it was last compiled)
Total Elapsed Time(s)
Total time (second) took to execute this plan [Total Execution Count] times
Average
Execution time(s)
Average Time(seconds) took for single execution of this plan
Total CPU time (s)
Total CPU time(seconds) took to
execute this plan [Total Execution Count] times
% CPU
Percentage of CPU time compared to [Total Elapsed Time(s)]
% Waiting
Percentage of waiting time(wait for resource) compared to [Total Elapsed Time(s)]
CPU time
average (s)
Average CPU time (Seconds) used for single exection
Avg Physical Read
Average number of Physical read for single execution
Avg Logical Reads
Average number of Logical read for single execution
Avg Logical Writes
Average number of Logical writes for single execution
max_physical_reads
Maximum number of physical reads that this plan has ever performed during a
single execution.
max_logical_reads
Maximum number of logical reads that this plan has ever performed during a single execution.
max_logical_writes
Maximum number of logical writes that this plan has ever performed during a single execution.
Individual Query
Part of Batch Statement
Batch Statement
Batch Query
query_plan
XML execution . On clicking this we can see the graphical execution plan
I used to analyse the first five to ten records ( in each category by changing the order by clause) to see procedures implementation. Most of the time I used to find some thing wrong with the procedure like unnecessary temp table usage, distinct clause , cursors, table joining with out proper joining condition,no proper indexing etc. The other issue usually happen is, enormous calls to database for single procedure (CPU cost and execution time might less). This might be a wrong implementation, which should be fixed by discussing with development team. Usually this can be fixed by adding some types of caching in application.Some time calls to the database is only to check if there is any change in the result data. Some thing like if there is new records in a table, it should be processed immediately. To achieve this, application might be querying the table to find the unprocessed record multiple time in a second, This can be fixed by implementing some kind of asynchronous call to the application by the application which inserts the data into this table or by implementing the notification event using SqlDependency available in the .Net frame work.
Hello Nelson,
ReplyDeleteI've been using the above query in sql server 2008 for couple of years without any issue. We recently upgraded to SQL Server 2014 standard edition and I migrated this query to 2014. Now this query takes really long time. What could be the reason?
Thanks!
The problem seems to be with CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle).
Deleteشركة نقل عفش بالخرج
ReplyDeleteشركة مكافحة حشرات بجدة
شركة رش مبيدات بالرياض
شركة تنظيف واجهات زجاج
شركات مقاولات
شركات المبيدات الزراعية فى السعودية
تنسيق حدائق المنزل
شركة تسليك مجاري بالدمام
شركة كشف تسربات المياه بالدمام
شركة تنظيف منازل بالدمام
شركة مكافحة الفئران بالدمام
شركة رش مبيدات بالدمام
شركة مكافحة حشرات ورش المبيدات
شركة مكافحة الفئران بالرياض
شركة تسليك مجارى بجدة
شركة كشف تسربات المياه بالرياض
شركة تنظيف بيارات بجدة
شركة تنظيف خزانات بجدة
ReplyDeleteI loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune
https://challenges.openideo.com/profiles/1119874714489381863241486229946090
ReplyDeletehttp://www.dead.net/member/khairyayman
https://vimeo.com/user54212503
https://mootools.net/forge/profile/naklafshdmam
http://bionumbers.hms.harvard.edu/bionumber.aspx?&id=113190
Your website content nice nice and interesting to observe.
ReplyDeleteInternet Marketing Dienstleistungen
really good
ReplyDeleteJobvermittlung
شحن عفش من المدينة لمصر
ReplyDeleteارخص نقل عفش بمكة ارخص نقل عفش بمكة
نقل عفش من جدة الى الدمام نقل عفش من جدة الى الدمام
نقل عفش من الرياض الى المدينة المنورة نقل عفش من الرياض الى المدينة المنورة
نقل عفش من الدمام الى مكة نقل عفش من الدمام الى مكة
نقل عفش من الرياض الى الدمام نقل عفش من الرياض الى الدمام
شركة مكافحة حشرات بجدة
ReplyDeleteشركة مكافحة الصراصير بجدة
شركة مكافحة البق بجدة
شركة رش مبيدات بالطائف
شركة مكافحة حشرات بالقنفذة
شركة مكافحة النمل الابيض بجدة
شركة غسيل خزانات بجدة
شركة تنظيف خزانات بالمدينة المنورة
شركة عزل خزانات بجدة
شركة اصلاح خزانات بالمدينة المنورة
شركة تنظيف خزانات
This blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.
ReplyDeleteBlockchain online training
I just loved your article on the beginners guide to starting a blog.If somebody take this blog article seriously in their life, he/she can earn his living by doing blogging.thank you for thizs article. blockchain online training
ReplyDeleteThanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
ReplyDeleteblockchain online training
best blockchain online training
top blockchain online training
As in the case of a limited liability company, the owners of the company's shares can be both legal entities and individuals. But, unlike a limited liability company, shares of a JSC can be bought and sold publicly. The maximum number of shares is not limited and more shares may be issued during the life of the company. There are several types of shares, and usually the rights of shareholders to vote and receive dividends depend on the category of shares. Generally, shareholders have the right to express their opinions on the direction of the business and other matters such as distribution of profits and appointment of the council. All decisions of shareholders are made at the meeting of shareholders. http://www.confiduss.com/en/info/blog/article/latvia-jsc-company-incorporation/
ReplyDeleteIt's always good to hear, "buy one and get one free". But with Seers Cookie Consent you can get many more. I will gladly suggest Seers for its tools to generate a complaint Cookie Policy assignment doer. Do check out its Cookie Consent Banner; it indicates the level of concern Seers possess for its customer's data.
ReplyDeleteWe have severe approaches against literary theft issues and inauthentic assets. We try to convey the best quality task composing that meets every one of your prerequisites. We utilize the Buy Essay Paper Online most dependable counterfeiting distinguishing programming to filter each finished task to prohibit the possibilities of inauthentic text. We submit impeccable last papers, ensuring that it merits the cash you have contributed with us.
ReplyDeleteHoodies and justin bieber varsity jacket mens custom are both comparative for all intents and purposes. The principal contrast between the two is that a hoodie has a hood on top and it is commonly without a zipper. Then again, coats might possibly have a hood. They have zippers or fastens at the front.
ReplyDeleteit has high water-resistant proprieties that ensureCEMENT TREATED BASE services in USA that it maintains adequate moisture content so as to not being not affected by extreme weather conditions, such as freezing temperatures and heavy rainfalls
ReplyDeleteWe are convinced that a professionally developed single-page web app can offer impressive and secure web experience. By designing and developing Web Development dubaiinteractive SPAs, we ensure responsiveness and cross-browser compatibility, as well as live up to users’ high expectations in agility and performance.
ReplyDeletea simple, flexible, and all-inclusive code compliance system. Simpleelectra air curtain suppliers in uae modifications to colour temperature and intensity can be made to meet tenant tastes and needs.
ReplyDeleteMost fluids can be transferred using hydrocarbons or in hazardous areas. The air driven mechanism makes it suitable for use in almost any environment. needstransfer pumps in Dammam of our clients. Through our special designed and manufactured pump systems, Combined Pumps provides a one-of-a-kind pumping solution.
ReplyDeletewith the paperwork required to open accounts, weWorking Capital in Dubai assist clients in narrowing down their selection of suitable banks. We will complete the procedure as rapidly as is practical with the support of the customer and our trained operations staff.
ReplyDeletewe are best Industry Leading Degassing Solution Hasten Cleanse
ReplyDeleteNew Evolution Inter deco is a company that specializes in RENOVATION commercial renovation companies dubai If you're looking for an experienced partner to help you renovate your office space New Evolution Inter decor is always there to help you :)
ReplyDelete