SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 11/13/2012 :  13:56:17  Show Profile  Reply with Quote
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
6062 Posts

Posted - 11/13/2012 :  14:58:37  Show Profile  Reply with Quote
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
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 11/13/2012 :  15:48:33  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/13/2012 :  16:22:23  Show Profile  Reply with Quote
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
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 11/13/2012 :  17:32:40  Show Profile  Reply with Quote
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..
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/13/2012 :  22:09:34  Show Profile  Reply with Quote
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
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 11/14/2012 :  14:00:51  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/14/2012 :  16:06:24  Show Profile  Reply with Quote
What index do you have?
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 11/14/2012 :  16:35:17  Show Profile  Reply with Quote
Clustered Index on SALESID.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/14/2012 :  16:40:58  Show Profile  Reply with Quote
What are you actually trying to do ? Give us requirement. you donot need self join.
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 11/14/2012 :  17:11:46  Show Profile  Reply with Quote
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...
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/14/2012 :  23:26:49  Show Profile  Reply with Quote
It is still not clear what you need. Please explain clearly and people can help you optimize the query
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000