May 9, 2011 at 5:14 pm
We have a in-house developed application that uses sql server db.
Recently we found out from our clients who say the application responsed very slow when they are doing a search in the applcation, what happened is when one person is searching an employee, the other person has to wait for the first person done then he can get the result.
Also happened in another area , when one person is doing a process of override, the other one has to wait for him done, and then he can do it.
What could be the problem, and how can we troubleshooting this?
Thanks
May 9, 2011 at 8:50 pm
Easiest way would be to do a dirty read, if you are not concerned about selecting uncommited data. But you will still need to resolve the underlying issue, which could be down to numerous issues. Check your sp_lock information, or run a profiler trace on locks, to discover what types of locks are being placed on the data you are selecting.
May 9, 2011 at 9:00 pm
May 9, 2011 at 9:03 pm
I know I can use sp_who2 and sp_who to find which ProcessID is blocking another.
Is there a way that I can find which query or stored Procedure is caused blocking? Thanks
May 9, 2011 at 10:31 pm
Check this would help you or not!!!
Its always good at looking thw wait types occured in your production
use master
go
Select A.[SQL text],A.Session_id,A.wait_duration_ms,a.wait_type,a.blocking_session_id,B.Status,B.statement_executing,B.DatabaseName,B.CPU_time,B.total_elapsed_time,b.reads,b.writes,b.logical_reads From (
SELECT st.text AS [SQL Text],
w.session_id,
w.wait_duration_ms,
w.wait_type, w.resource_address,
w.blocking_session_id,
w.resource_description FROM sys.dm_os_waiting_tasks AS w
INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))
AS st WHERE w.session_id > 50
AND w.wait_duration_ms > 0 ) A
Inner Join
(
SELECT r.session_id ,
r.[status] ,
r.wait_type ,
r.scheduler_id ,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS [statement_executing] ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
Object_NAME(qt.objectid) AS [ObjectName] ,
r.cpu_time ,
r.total_elapsed_time ,
r.reads ,
r.writes ,
r.logical_reads ,
r.plan_handle
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
) B On A.Session_id = B.session_id
May 10, 2011 at 12:34 am
First check the same query on the server side SSMS(SQL Server Management Studio)
if resposnse is good then check the query on the client side with the SSMS(SQL Server Management Studio) with client statistics option in SSMS if response is good then go for application Server configuration like session memory etc
OR
If response is poor then check first those tables using by the query have a clustered index must be
OR
SELECT * FROM SYS.dm_db_missing_index_details , create required indexes
OR
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
order by sp.cpu desc
this query will provide you waitresource and bottleneck on queries
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 9:22 am
Could you please explain a little what the two queries do,
One is sqlzealot-81 query.
The other is
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
order by sp.cpu desc
Do I have to run this when there is a blocking or locking?
I need to reproduce that and do a test, so shall I run it right after the locking happened?
Thanks
May 10, 2011 at 9:52 am
First will tell you about the missing indexes on the database and the other one will tell you about resources usage of queries,locks,wait etc,these queries are not harmfull just execute it and then check the result
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 10:08 am
Thank you, so I guess the second query I can get what query could cause the block, thanks.
May 10, 2011 at 10:14 am
Also can I setup a performance monitor to trace what could cause the bottle neck or blocking?
If so, what events shall i choose?
Thanks
May 11, 2011 at 2:24 am
Please, please, please don't just create any index in the missing index DMV. It's a suggestion, not a command. There are often near-duplicate indexes, massively wide indexes, etc.
It's a great place to start, emphasis start. It's terrible if that's all you use. Take each index recommendation, check to see if there are existing indexes that can be altered (missing indexes doesn't take that into consideration), see if multiple index suggestions can be consolidated (they often can), see if the index makes sense at all (not too large), then test and make sure that it really does improve things. Only once you've done all that and the index does help and doesn't hinder data changes, then consider implementing it.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply