| Author |
Topic |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2013-01-04 : 13:09:06
|
| one application is working fine until yesterday and now the developers starting complaining about performance issue which is on database server side..Please suggest how to find the which query is taking time and how to resolve it. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 21:16:10
|
| Also look for any blocking issues while accessing the application pages concurrently by multiple users------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-01-07 : 15:11:27
|
| Thanks a lot for the answers.. |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-01-07 : 15:12:34
|
| I have one another question.How to resolve the blocking issue. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-07 : 23:33:38
|
| Search for it. There are lots of post for it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 00:01:05
|
quote:
Originally posted by sqlfresher2k7
I have one another question.How to resolve the blocking issue.
have a look at sp_who2 system procedure to start of------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-01-16 : 09:44:09
|
| Like visakh said, use sp_who2 and kill the sessions that are blocked with Kill session_id |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-01-16 : 13:45:31
|
| Killing sessions will free the blocks but will not fix the underlying problems. That can be a challenge to experienced DBA'sdjj |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-17 : 01:44:13
|
| An approach to solving the underlying problems is to identify queries with excessive locking delay issues. Use the Blocked Process Report - http://www.sqlserver-dba.com/2012/10/sql-server-find-queries-causing-lock-timeouts-per-second.html , set the relevant details.Once you've identified the queries - there are various methods to optimise - such as: Query tuningStored procedure versus adhoc queriesAnalyse indexes – for example , if you have a heavily updated column don’t include it in a Clustered Index as it will will also lock the non clustered index via the row locatorRow versioning Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
Performance