March 23, 2009 at 7:49 am
Hi SQL Expert:
Suppose, user complains that it is timed out each time he/she tries to access the database from application. How can we find out what is the problem in this situation ? What are the steps to address this issue?
Also, if i want to look at the queries and stored procedure to know if they are performing slow, say there are thousands of stored procedure and queris, how can i know that particular stored procedure or query is performing slow? How shouldI i be able to know that right procudure which is causing performance issue?
I really appreciate your help on this.
March 23, 2009 at 8:00 am
Please post in an appropriate forum in the future. The Website upgrade forum is for discussions and problems with the SQL Server Central web site.
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
March 23, 2009 at 8:49 am
Moved to perf tuning
March 23, 2009 at 8:56 am
Steve, how to know where to post it? Suppose if it is performance tunining, T-SQL, SSIS, etc issues. How to know which is the right forum? Please suggest me!!! Thanks for your help.
March 23, 2009 at 9:03 am
There are Performance tuning forums for both SQL 2000 and SQL 2005, likewise T-SQL. There's a forum for SSIS. Pick the forum that matches the version that you're using and is the best fit for the question you're asking.
No one minds if it's slightly wrong (a performance question in the administering forum for eg).
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
March 23, 2009 at 9:08 am
Thank you Gail Shaw. It is greatly appreciated!!!
March 25, 2009 at 5:51 pm
Any of you have any solution for this please?
March 25, 2009 at 6:51 pm
Abi Chapagai (3/25/2009)
Any of you have any solution for this please?
Can you elaborate your situation so that any one can help please?
March 25, 2009 at 6:56 pm
Suppose, user complains that it is timed out each time he/she tries to access the database from application. How can we find out what is the problem in this situation ? What are the steps to address this issue?
Also, if i want to look at the queries and stored procedure to know if they are performing slow, say there are thousands of stored procedure and queris, how can i know that particular stored procedure or query is performing slow? How shouldI i be able to know that right procudure which is causing performance issue?
March 25, 2009 at 7:10 pm
I thought you gone through the article. The best way to know issues on your serve is to monitor it.
Abi Chapagai (3/25/2009)
Suppose, user complains that it is timed out each time he/she tries to access the database from application.
this could be due to excessive blocking on your server. trace it
How can we find out what is the problem in this situation ? What are the steps to address this issue?
Have you got proper indexes designed? Do you track your index usage?
Queries to know which transactions are holding locks and which 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
Also, if i want to look at the queries and stored procedure to know if they are performing slow, say there are thousands of stored procedure and queris, how can i know that particular stored procedure or query is performing slow? How shouldI i be able to know that right procudure which is causing performance issue?
Server side tracing. Go through the article by Gail which is a pretty straight forward explanation and set up a trace to track the desired events for over a day or a desired duration and identify the Queries that are taking most of the time on your server and try to tune them up which will give you considerable performance.
any questions post them in here, someone would be able to give you a hand.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply