Performance tuning it so critical can any one plz figure it out urgent

Question

Post reply

Performance tuning it so critical can any one plz figure it out urgent

naga.rohitkumar

SSCertifiable

Points: 5557
More actions
September 19, 2014 at 2:50 am

#397372

Hi all,
Performance tuning it so critical can any one figure it out [there are no clients its a own prodcut company]
Application and users are hanging and server is down due to performance issue it got slowed
How to resolve this issue and identify which particular login is accessing the session [1000 session are running when we fetch sp_who2] no blocking , no deadlocks ,
Thanks
Naga.RohitKumar
Thanks
Naga.Rohitkumar

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

http://www.amazon.com/Server-Query-Performance-Tuning-Experts/dp/1430242035/

If it's critically urgent, hire someone to help you.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

naga.rohitkumar SSCertifiable Points: 5557 More actions · Answer 2

iam not asking you the links i want to konw the particular login running from 1000 users seeesions

Thanks
Naga.Rohitkumar

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

This will get you a list of the sessions running, including the one you use to run the query.

select r.session_id,

r.blocking_session_id,

r.total_elapsed_time,

r.reads,

r.logical_reads,

s.host_name,

s.login_name,

p.query_plan,

db_name(r.database_id)

from sys.dm_exec_requests r

inner join sys.dm_exec_sessions s ON s.session_id = r.session_id

cross apply sys.dm_exec_sql_text(r.sql_handle) t

cross apply sys.dm_exec_query_plan(plan_handle) p

order by r.total_elapsed_time desc;

However, it sounds like you have a pretty serious performance problem, so I would start by reading the articles that Gail provided. Performance problems generally build up over time unless you've had a recent release that caused something serious. You're going to need to get to the bottom of it and fix the root cause of the problem.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Open the Object Explorer, connect to the instance, right click on the instance, select "reports", follow your nose. If the server is so locked up that doesn't work, then you can try something like the query that Ed posted above.

This should also serve as a warning that you need to spend some time preparing for when the next time this happens. Don't put it off. Do it right after you get through this problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)