//
you're reading...
Database, IT-Center, Oracle, SQL Server

Find Slow/Worst Query in Database

1. SQL Server
Sql Server haven’t monitoring GUI for finding worst query may be need optimized, but can use this sql script

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

or

SELECT TOP 10
 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
--pick your criteria
ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC
Sugesstion Index for table problem, these rocomendation index for your table if there have slowly running:
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_idAS [TableName],
'CREATE INDEX [IX_' +OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL ANDdm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
' ON ' dm_mid.statement
' (' ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL ANDdm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
ISNULL (dm_mid.inequality_columns'')
+ ')'
ISNULL (' INCLUDE (' dm_mid.included_columns ')'''ASCreate_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle dm_mid.index_handle
WHERE dm_mid.database_ID DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

2. Oracle

Oracle have management monitorin in GUI web based, but this query can help you :

SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    child_number,
    disk_reads,
    executions,
    first_load_time,
    last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/

or

SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText, 
   SQL_FullText SQLFullText 
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text, 
      SQL_FullText, Operation, Options, 
      Row_Number() OVER 
         (Partition By sql_text ORDER BY Disk_Reads * Executions DESC) 
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads, 
          Max(Executions) OVER (Partition By sql_text) Executions, 
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM v$sql t, v$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS' 
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   ) 
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=5;

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

October 2013
M T W T F S S
« Sep   Apr »
 123456
78910111213
14151617181920
21222324252627
28293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: