| Author |
 Topic  |
|
|
Sonu619
Posting Yak Master
193 Posts |
 Posted - 11/13/2012 : 13:56:17
|
Hi guys,
I am running below query to find out top 100 qures that running slow.
SELECT TOP 100
total_worker_time/execution_count AS Avg_CPU_Time
,execution_count
,total_elapsed_time/execution_count as AVG_Run_Time
,(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END -statement_start_offset)/2
) FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC
and i am getting
Avg_CPU_Time, Execution_Count, Avg_Run_Time, Query_Text
My question is which colums (above) i have to be more focus on for Optimize query.
Thank You.. |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/13/2012 : 14:58:37
|
Avg_CPU_Time and Execution_Count will help you rank relative importance for which statements (Query_Text) you should optimize first.
Be One with the Optimizer
TG |
Edited by - TG on 11/13/2012 14:59:09 |
 |
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 11/13/2012 : 15:48:33
|
Thanks for your reply.
Quick qeustion, Which one should i go first
For E.g
AVG_CPU_TIME, EXECUTION_COUNT
755502211 3
12134723 3480
Thank You. |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/13/2012 : 16:22:23
|
The second one is much more popular - that would give you more "bang for the buck". The first one is probably just an adhoc statement because it was called only 3 times.
Be One with the Optimizer
TG |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 11/13/2012 : 17:32:40
|
Once again thank you for your help. Here is my second query
ACG_CPU_TIME, EXECUTION_COUNT
12134723 3480
Here is the query
SELECT
b.lId,
a.JId
INTO #tempL
FROM Sale a, Custom b
WHERE a.[Message] = b.[Message]
AND a.Jd IS NOT NULL
AND b.JId IS NULL
Need guidence how i can improve this query.
Thank You.. |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 11/13/2012 : 22:09:34
|
script out the definition of [sale] and [custom] tables including keys and indexes and post it here. How many rows in each table.
Be One with the Optimizer
TG |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 11/14/2012 : 14:00:51
|
SELECT
b.lId,
a.JId
INTO #tempL
FROM Sale a, Sale b
WHERE a.[Message] = b.[Message]
AND a.Jd IS NOT NULL
AND b.JId IS NULL
Sorry it was typo mistake (Joining sale table "Self Join") and Sale Table has 3172014 Records. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/14/2012 : 16:06:24
|
| What index do you have? |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 11/14/2012 : 16:35:17
|
| Clustered Index on SALESID. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/14/2012 : 16:40:58
|
| What are you actually trying to do ? Give us requirement. you donot need self join. |
|
|
|
Sonu619
Posting Yak Master
193 Posts |
Posted - 11/14/2012 : 17:11:46
|
Thanks for your help..
I ran top 100 query that running slow
SELECT TOP 100
total_worker_time/execution_count AS Avg_CPU_Time
,execution_count
,total_elapsed_time/execution_count as AVG_Run_Time
,(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END -statement_start_offset)/2
) FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC
and i am getting
ACG_CPU_TIME, EXECUTION_COUNT
12134723 3480
This sql statment is part of S.P
What i need here if Message is same i need LID AND JID...
SELECT
b.lId,
a.JId
INTO #tempL
FROM Sale a, Sale b
WHERE a.[Message] = b.[Message]
AND a.Jd IS NOT NULL
AND b.JId IS NULL
Thank you Please let me know if you want more info...
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/14/2012 : 23:26:49
|
| It is still not clear what you need. Please explain clearly and people can help you optimize the query |
|
|
| |
Topic |
|
Query Help
