September 5, 2011 at 1:08 am
Hi,
----–Queries taking longest elapsed time:
SELECT TOP 50
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_id()
ORDER BY average_seconds DESC;
output results are most of the query
Average Seconds - 15.18457
Total Seconds - 30.36914
--Queries doing most I/O:
SELECT TOP 50
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;
average_IO - 3307525
total_IO - 6615051
those seconds are query taking more time for completed transaction, so how to reduced these time seconds and IO? Is it possible for create missing index those tables and reduce most elapsed time and IO? please suggestion me.
what is perfected values for elapsed time and IO? I think both should be 0.0 seconds.
thanks
September 5, 2011 at 7:56 am
Dream on.
https://sqlroadie.com/
September 5, 2011 at 8:10 am
You can't get zero i/o, ever. You have to have some i/o or you're neither querying or returning any data. So that goal is right out the window.
As far as how fast queries should run, again, zero isn't possible. But, you do want a query to run as fast as it can. But you need to weigh the cost and time of reducing query time in smaller and smaller increments as the amount of tuning you can do becomes less & less over the frequency of the calls of the query and it's importance overall in the system. There is a point at which, you might be able to squeeze another millisecond or two out of a query, but at the cost of spending weeks working on it when another query could benefit more for less work.
But zero, no, that's not a realistic target.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2011 at 9:31 am
ananda.murugesan (9/5/2011)
what is perfected values for elapsed time and IO? I think both should be 0.0 seconds.
For a query that does absolutely nothing, queries no tables, returns no data that's possible. For a useful query... not so much. Imagine how valuable a book would be if I told you that whenever you read it you must read 0 pages and take 0 seconds to do so.
p.s. IO is not measured in seconds.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2011 at 10:09 pm
Thanks for reply...
I understand for IO never become 0.0.
Could you tell me, what is actual value output for most IO query and what is generally perfered value of IO in SQL SERVER 2008?
For the most costing query, if created missing index then average & total seconds will be reduce? I have checked through script for recommeded index of the database, it tells more then 150 index have to created.
thanks
September 6, 2011 at 3:26 am
ananda.murugesan (9/5/2011)
Could you tell me, what is actual value output for most IO query and what is generally perfered value of IO in SQL SERVER 2008?
Depends on what the query is doing. The more data it needs, the higher the IO will be
For the most costing query, if created missing index then average & total seconds will be reduce? I have checked through script for recommeded index of the database, it tells more then 150 index have to created.
Maybe, if whatever is suggesting the missing indexes is reliable and accurate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2011 at 5:04 am
ananda.murugesan (9/5/2011)
Thanks for reply...I understand for IO never become 0.0.
Could you tell me, what is actual value output for most IO query and what is generally perfered value of IO in SQL SERVER 2008?
For the most costing query, if created missing index then average & total seconds will be reduce? I have checked through script for recommeded index of the database, it tells more then 150 index have to created.
thanks
There really is no "correct" number for I/O. You have to have enough I/O to return the data requested in the query. Whatever that is, is what it is. Now, if you have a table scan where you could have an index seek, then you're doing too much I/O, but that's situational and there's still no actual number that anyone can provide for you.
You cannot trust the missing index information in SQL Server. Look upon them as suggestions or starting points for investigations.
It sounds like you're really just barely getting started on this sort of thing. Could I suggest reading Gail's articles[/url] on performance tuning[/url]? After reading those you might want to consider taking a look at my book.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply