Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-13 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-13 : 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

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-13 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-13 : 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

202 Posts

Posted - 2012-11-13 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-13 : 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

202 Posts

Posted - 2012-11-14 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-14 : 16:06:24

What index do you have?

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-14 : 16:35:17

Clustered Index on SALESID.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-14 : 16:40:58

What are you actually trying to do ? Give us requirement. you donot need self join.

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-11-14 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-14 : 23:26:49

It is still not clear what you need. Please explain clearly and people can help you optimize the query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources