How to Find Performance related bottleneck

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

How to Find Performance related bottleneck

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2013-10-25 : 06:22:12

I have been instructed to monitor and perform tasks for various reasons for failing performance like TEMPDB rising suddenly; identifying the sessions causing memory drops; Identifying sessions making huge transactions for a longer time; Identifying Stored Procedures which drives the disk space to collapse; CPU functionality high; other memory structures rising unevenly; sudden growth in the MDF / NDF; LDF growth should it be good practice to trim in (FULL / SIMPLE) recovery modes;

I have been trying to use the System supported views but not accurately and are widely unhelpful. I have searched in the various forums and documentation for such performance issues being solved.

Perhaps anyone could help me in deducing with any documentation that would give some help either on using Query based or any SP driven or and Agent Job that can bring to notice about the solution. May be experts have better solutions and better procedures would be much helpful.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-25 : 08:31:09

I would first run a healthcheck on the system - http://www.brentozar.com/blitz/
Once you have done that it is the management views, perfmon and other system monitoring tools built into Windows and/or SQL that you would use unless you wanted to use third party monitoring tools.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-10-25 : 09:50:25

Here is 1 helpful query. Shows you what is actively running, how long for etc...

SELECT 
	r.Session_ID, 
	DB_Name(Database_ID) DatabaseName,
	r.Start_Time, 
	convert(decimal(10, 3), datediff(ms, r.start_time, getdate())/1000.0) Running_Time,
	left(s.text, 2000) Statement_Start, 
	object_name(objectID, database_id) ObjectName,
	SUBSTRING(text, (statement_start_offset/2) + 1,      
			((CASE WHEN statement_end_offset <= 0 THEN DATALENGTH(text) ELSE statement_end_offset END
			- statement_start_offset)/2) + 1) AS Statement_Text,  
	Blocking_Session_ID, 
	*
FROM 
	sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
order by 
	R.Start_Time

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2013-10-31 : 20:38:39

Thanks Denis for that it worked for the current transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources