SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Performance		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

583 Posts
Posted - 01/04/2013 :  13:09:06  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 01/04/2013 :  21:15:16  Show Profile  Reply with Quote
use this for tracking long running queries

http://blog.extreme-advice.com/2012/10/22/capture-long-running-query-in-sql-server/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/04/2013 :  21:16:10  Show Profile  Reply with Quote
Also look for any blocking issues while accessing the application pages concurrently by multiple users

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1765 Posts
Posted - 01/05/2013 :  10:48:08  Show Profile  Visit jackv's Homepage  Reply with Quote
Use this query to identify current executing queries. It also has information about any blocking session ids.
http://www.sqlserver-dba.com/2012/09/sql-server-current-queries-executing-on-sql-server.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

583 Posts
Posted - 01/07/2013 :  15:11:27  Show Profile  Reply with Quote
Thanks a lot for the answers..
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

583 Posts
Posted - 01/07/2013 :  15:12:34  Show Profile  Reply with Quote
I have one another question.

How to resolve the blocking issue.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/07/2013 :  23:33:38  Show Profile  Reply with Quote
Search for it. There are lots of post for it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/08/2013 :  00:01:05  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

srimami
Posting Yak Master

151 Posts
Posted - 01/16/2013 :  09:44:09  Show Profile  Reply with Quote
Like visakh said, use sp_who2 and kill the sessions that are blocked with Kill session_id
Go to Top of Page

djj55
Yak Posting Veteran

USA
84 Posts
Posted - 01/16/2013 :  13:45:31  Show Profile  Reply with Quote
Killing sessions will free the blocks but will not fix the underlying problems. That can be a challenge to experienced DBA's


djj
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1765 Posts
Posted - 01/17/2013 :  01:44:13  Show Profile  Visit jackv's Homepage  Reply with Quote
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 tuning
Stored procedure versus adhoc queries
Analyse 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 locator

Row versioning



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000