April 8, 2009 at 6:10 am
CH&HU (4/8/2009)
We have an audit table which stores the details of the errors on application. As you said the users are facing TimeOut errors and save profile errors, update errors....There in that audit table i can see nearly 40 - 70 dead locks and 50+ timeout errors daily. And the total error count goes to 500+ in a day when i check EOD.
Please suggest me...
Oh that's too many. First Turn on trace flag 1222 on your server which will give you a nice Deadlock graph in your errorlog and form there you can identify the queries that are involved in the deadlock scenario and then you can troubleshoot the queries. Post the results of your log here in a new post, we would be able to help you out.
But before this, identify the current queries that are running on your server.
Use this to look at the queries which are running at the moment:
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
You can use this one to find out which are holding locks and which are being 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
April 8, 2009 at 6:25 am
Thanks for your quick response....
As i have no idea about trace flag , what you have said. As i have never used profiler or tracing as there were no such errors or cause came to me to take action of tracing.....
Can you please let me know, what do this trace flag do... in brief and how to apply that, and remove or delete that... for my present situations...
April 8, 2009 at 6:37 am
Hi,
Check out BOL for trace flags description and the defnition.
you can turn on a trace flag globally using DBCC TRACEON(1222,-1) or through start up parameters but that needs a restart.
This trace flag will write any deadlock events to your log file and also the statements that are causing deadlocks.
Do you need anymore info?
April 8, 2009 at 6:39 am
you can turn off the trace flag using DBCC TRACEOFF.
April 8, 2009 at 6:41 am
Thanks much for your reply...
Will check if that can be possible on our servers to take action of running trace profiler...
As this server reaches to 98% in CPU utilization and in performance level as well in RAM size.... 🙁
Let me know if anything about my presence was understood and how can i take action without load on server.
April 8, 2009 at 6:49 am
CH&HU (4/8/2009)
As this server reaches to 98% in CPU utilization and in performance level as well in RAM size.... 🙁
Let me know if anything about my presence was understood and how can i take action without load on server.
Hi,
Fist and foremost thing that you can do for temporary results is to identify the current queries that are running against your server. sp_who2 identify the expensive queries and tune them up.
Did you look at Gails article, gives you a good insight into the problem, read it when you are free and from there you take it and please do not hesitate to post any problems you have as you can see we are here to help each other round:
check this out and ther are two parts:
April 8, 2009 at 10:25 pm
Thanks and am Glad with your words of helping...
Will check the articles and will do reply .......
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply