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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 How to Find Performance related bottleneck
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

272 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

3873 Posts

Posted - 10/25/2013 :  08:31:09  Show Profile  Reply with Quote
I would first run a healthcheck on the system -
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

Aged Yak Warrior

596 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...

	DB_Name(Database_ID) DatabaseName,
	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,  
	sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
order by 
Go to Top of Page

Constraint Violating Yak Guru

272 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  
 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.03 seconds. Powered By: Snitz Forums 2000