December 30, 2008 at 11:33 am
I've been using this script to find the worst performing queries ind SQL 2005:
SELECT TOP 100 [OBJECT_NAME] = OBJECT_NAME(ST.OBJECTID),
CREATION_TIME,
LAST_EXECUTION_TIME,
TOTAL_CPU_TIME = TOTAL_WORKER_TIME / 1000,
– MICROSECOND
to millisecond
avg_cpu_time = (total_worker_time / execution_count) / 1000,
min_cpu_time = min_worker_time / 1000,
max_cpu_time = max_worker_time / 1000,
last_cpu_time = last_worker_time / 1000,
total_time_elapsed = total_elapsed_time / 1000 ,
avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
min_time_elapsed = min_elapsed_time / 1000,
max_time_elapsed = max_elapsed_time / 1000,
avg_physical_reads = total_physical_reads / execution_count,
avg_logical_reads = total_logical_reads / execution_count,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,(
(
CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
) /2
) + 1
) as statement_text
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
Object_Name(st.objectid) IS NOT NULL
AND DB_NAME(st.dbid) = ‘Your_Database_Name’
ORDER BY
db_name(st.dbid),
total_worker_time / execution_count DESC
This code will not run in (Compatability mode 80) due to the CROSS APPLY. Here's what I was think of doing:
select sys.dm_exec_query_stats fields into temp table 1
create temp table 2 with sys.dm_exec_sql_text fields and a field for the sql_handle
cycle through sql_handle's from temp table 1 running inserts into temp table 2 using sys.dm_exec_sql_text
Finnally
select joining temp table 1 and temp table to on the sql_handle
Is there a better way to do this?
Is there a way to do this in SQL 2000, or are we limited to trace files?
December 30, 2008 at 12:48 pm
Nah. Just replace any appearance of "st.{col_name}" with:
(Select {col_name} From sys.dm_exec_sql_text(qs.sql_handle))
It's not as efficient as your orginal query, but it should still work just fine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 2:20 pm
Thanks for the hint. I've re-written like so:
SELECT TOP 100 [OBJECT_NAME] = OBJECT_NAME((SELECT [OBJECTID]
FROM SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE ))),
CREATION_TIME,
LAST_EXECUTION_TIME,
AVG_CPU_TIME = (TOTAL_WORKER_TIME / EXECUTION_COUNT) / 1000,
MIN_CPU_TIME = MIN_WORKER_TIME / 1000,
MAX_CPU_TIME = MAX_WORKER_TIME / 1000,
LAST_CPU_TIME = LAST_WORKER_TIME / 1000,
TOTAL_TIME_ELAPSED = TOTAL_ELAPSED_TIME / 1000,
AVG_TIME_ELAPSED = (TOTAL_ELAPSED_TIME / EXECUTION_COUNT) / 1000,
MIN_TIME_ELAPSED = MIN_ELAPSED_TIME / 1000,
MAX_TIME_ELAPSED = MAX_ELAPSED_TIME / 1000,
AVG_PHYSICAL_READS = TOTAL_PHYSICAL_READS / EXECUTION_COUNT,
AVG_LOGICAL_READS = TOTAL_LOGICAL_READS / EXECUTION_COUNT,
EXECUTION_COUNT,
SUBSTRING((SELECT [TEXT]
FROM SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE )),
(QS.STATEMENT_START_OFFSET / 2) + 1,((CASE STATEMENT_END_OFFSET
WHEN -1 THEN DATALENGTH((SELECT [TEXT]
FROM SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE )))
ELSE QS.STATEMENT_END_OFFSET
END - QS.STATEMENT_START_OFFSET) / 2) + 1) AS STATEMENT_TEXT
FROM SYS.DM_EXEC_QUERY_STATS QS
WHERE OBJECT_NAME((SELECT OBJECTID
FROM SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE ))) IS NOT NULL
AND DB_NAME((SELECT DBID
FROM SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE ))) = 'Your Database Name'
ORDER BY DB_NAME((SELECT DBID
FROM SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE ))),
TOTAL_WORKER_TIME / EXECUTION_COUNT DESC
This runs just fine in Compatability mode 90.
However in Compatability mode 80 I get messages like this one:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
I have this error on every line that I have used (Select [field] From sys.dm_exec_sql_text(qs.sql_handle))
December 30, 2008 at 2:26 pm
One thing you can try - might work, is to prefix all calls to the system views with the database and run the script from within the context of a 90 database.
Example:
SELECT {columns}
FROM MyDB.sys.dm_exec_query_stats
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 3:15 pm
Yeah, it's some Mode-80 syntax restriction, but I do not understand why. Maybe someone more conversant in SQL 2000 syntax can comment.
You could also try what Jeffery is suggesting, might need to address the OBJECT_NAME() functions and such though.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 4:14 pm
In SQL Server 2000 it is not possible to pass a table column as parameter for a table-valued function.
December 31, 2008 at 8:26 am
At this point it looks like I'm back to my original plan of action. I'll repost my questions, please refer to the first post:
Is there a better way to do this?
Is there a way to do this in SQL 2000, or are we limited to trace files?
December 31, 2008 at 11:41 am
Jon.Morisi (12/31/2008)
At this point it looks like I'm back to my original plan of action. I'll repost my questions, please refer to the first post:Is there a better way to do this?
On SQL 2005 You can do it from "master":
USE master
GO
SELECT TOP 100 [OBJECT_NAME] = OBJECT_NAME(H.OBJECTID, H.DBID),
CREATION_TIME,
LAST_EXECUTION_TIME,
AVG_CPU_TIME = (TOTAL_WORKER_TIME / EXECUTION_COUNT) / 1000,
MIN_CPU_TIME = MIN_WORKER_TIME / 1000,
MAX_CPU_TIME = MAX_WORKER_TIME / 1000,
LAST_CPU_TIME = LAST_WORKER_TIME / 1000,
TOTAL_TIME_ELAPSED = TOTAL_ELAPSED_TIME / 1000,
AVG_TIME_ELAPSED = (TOTAL_ELAPSED_TIME / EXECUTION_COUNT) / 1000,
MIN_TIME_ELAPSED = MIN_ELAPSED_TIME / 1000,
MAX_TIME_ELAPSED = MAX_ELAPSED_TIME / 1000,
AVG_PHYSICAL_READS = TOTAL_PHYSICAL_READS / EXECUTION_COUNT,
AVG_LOGICAL_READS = TOTAL_LOGICAL_READS / EXECUTION_COUNT,
EXECUTION_COUNT,
SUBSTRING(H.[TEXT], (QS.STATEMENT_START_OFFSET / 2) + 1,
((CASE QS.STATEMENT_END_OFFSET
WHEN -1 THEN DATALENGTH(H.[TEXT])
ELSE QS.STATEMENT_END_OFFSET
END - QS.STATEMENT_START_OFFSET) / 2) + 1) AS STATEMENT_TEXT
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE ) H
WHERE OBJECT_NAME(H.OBJECTID, H.DBID) IS NOT NULL
AND DB_NAME(H.DBID) = 'Your Database Name Here'
ORDER BY DB_NAME(H.DBID), TOTAL_WORKER_TIME / EXECUTION_COUNT DESC
* Noel
December 31, 2008 at 12:19 pm
thanks noeld, that certainly was the easier way to do it!
So, SQL 2000, are we stuck with using the trace files?
January 5, 2009 at 8:16 am
Jon.Morisi (12/31/2008)
thanks noeld, that certainly was the easier way to do it!So, SQL 2000, are we stuck with using the trace files?
Yes. Manageability, is in my opinion one of the highest winners for SQL 2005!
Only when you see what you can do with DMVs you start wondering how was it possible for you to live with SQL 2000 🙂
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply