August 19, 2013 at 10:01 am
Hi Team,
am having a online application, while doing some actions like (select drop down, select any item from list), application is taking long time (10 seconds), previously it was done with in fraction of seconds.
how to identify the query which is taking time, using below query but am not getting the exact result,
SELECT TOP 10 a.total_worker_time/a.execution_count AS [High CPU Ave],
SUBSTRING(b.text,a.statement_start_offset/2, (CASE WHEN a.statement_END_offset = -1
then len(convert(nvarchar(max), b.text)) * 2 ELSE a.statement_END_offset END -a.statement_start_offset)/2)
AS SQL_Text, db_name(b.dbid) AS DatabaseName,
object_name(b.objectid) AS ObjectName
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
ORDER BY [High CPU Ave] DESC
GO
August 19, 2013 at 10:28 am
Minnu (8/19/2013)
Hi Team,am having a online application, while doing some actions like (select drop down, select any item from list), application is taking long time (10 seconds), previously it was done with in fraction of seconds.
how to identify the query which is taking time, using below query but am not getting the exact result,
What do you mean are not getting the exact result? Do you know that the cause of the your website slow response is because of sql? How do you know?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 11:20 am
High CPU isn't the catch-all for things running slowly. I side with Sean on this one - what makes you think it's SQL?
If you do suspect SQL, then consider creating a filtered trace that runs say capturing only durations > 10 seconds for the specific DB you want to track, paying close attention to the number of logical reads (this can often pinpoint bad things going on in your execution plan(s) or prompt you to consider checking index fragmentation)...
You could refine your query to add in these columns as well (which I consider helpful for tracking down problematic TSQL SELECT
execution_count, total_physical_reads, total_logical_reads, text, [qp] .[query_plan]
FROM
sys. dm_exec_query_stats qs
CROSS APPLY sys. dm_exec_sql_text(qs .plan_handle) st
CROSS APPLY sys. dm_exec_query_plan(qs .plan_handle) AS qp
OUTER APPLY sys. dm_exec_plan_attributes(qs .plan_handle) pa
WHERE
attribute = 'dbid'
AND DB_NAME(CAST (pa. value AS int)) = 'YOURDB'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy