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)
 How to Find Performance related bottleneck
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbalearner
Constraint Violating Yak Guru

262 Posts

Posted - 10/25/2013 :  06:22:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 10/25/2013 :  08:31:09  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 10/25/2013 :  09:50:25  Show Profile  Reply with Quote
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
Go to Top of Page

dbalearner
Constraint Violating Yak Guru

262 Posts

Posted - 10/31/2013 :  20:38:39  Show Profile  Reply with Quote
Thanks Denis for that it worked for the current transaction.
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.11 seconds. Powered By: Snitz Forums 2000