April 9, 2009 at 10:26 pm
hi,
suddenly sqlserver become slow, and the application is giving time out error.
what i need to consider regarding this.
thanks,
🙂
April 9, 2009 at 10:36 pm
vrabhadram (4/9/2009)
hi,suddenly sqlserver become slow, and the application is giving time out error.
what i need to consider regarding this.
thanks,
Do you see any locking/blocking issues on your server? You need to check the current queries running at the moment. you can either use sp_who2 to quickly identify them or you can use this:
SELECT a.session_id, c.dbid,
SUBSTRING(c.text, (a.statement_start_offset/2)+1,
((CASE WHEN a.statement_end_offset < 1
THEN DATALENGTH(c.text)
ELSE (a.statement_end_offset -
a.statement_start_offset)/2
END)) + 1) as statement_text
FROM sys.dm_exec_requests a
CROSS APPLY fn_get_sql (a.sql_handle) c
Check the highest CPU time and do you have any deadlocks? You need to monitor your server to include locks and any deadlocks.
Use this query to see what queries are holding locks and what are blocked:
SELECT
t1.resource_type,
'database' = DB_NAME(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
t2.wait_duration_ms,
(SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
(CASE WHEN t3.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE t3.statement_end_offset
END - t3.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
t2.resource_description
FROM
sys.dm_tran_locks AS t1,
sys.dm_os_waiting_tasks AS t2,
sys.dm_exec_requests AS t3
WHERE
t1.lock_owner_address = t2.resource_address AND
t1.request_request_id = t3.request_id AND
t2.session_id = t3.session_id
Tun on flag 1222 which gives you any deadlock events occurring on your server and if they are tune the queries. Usually time out errors occur due to excessive blocking and deadlocking.
April 10, 2009 at 5:26 am
consider running a profiler trace to see if you have queries consuming
excessive resources. look for high duration and/or reads values.
all it takes is 1 missing index.
on that note...do you have any kind of maintenance running to defrag your indexes ?
April 10, 2009 at 5:35 am
It really could be almost anything. Since you don't know, you need to investigate systematically. I like the recommendation to capture a trace. That's something I always do. I'd also suggest reading through this Microsoft white paper on waits and queues. Identifying what is causing things to slow down and where is the first step towards solving the issue.
I also know of a book that might help a bit. 😉
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 10, 2009 at 5:39 am
Grant Fritchey (4/10/2009)
I also know of a book that might help a bit. 😉
😀
I have got that too
April 10, 2009 at 5:52 am
The first thing that you need to do is to answer this question: "Is everything generally slow? Or is it one specific thing or a group of related or similar things?"
The answer to that question will usually indicate what your next step should be.
[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]
April 10, 2009 at 7:28 am
became slow suddenly? has anything been changed? Ask this question twice if they say no, people have strange ideas about what does or doesn't constitute a change.
Hey Barry, your avatar has changed to a black square, has Darth had his lights punched out?
---------------------------------------------------------------------
April 10, 2009 at 7:46 am
This is a fairly good Microsoft white paper on performance troubleshooting. I've used it more than once as a starting point.
April 10, 2009 at 8:33 am
george sibbald (4/10/2009)
Hey Barry, your avatar has changed to a black square, has Darth had his lights punched out?
I would not normally be on-line today, but I am home sick. My avatar will be back tomorrow. 🙂
[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]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply