how to optimized SQL Server Performance

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 optimized SQL Server Performance

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2012-07-26 : 00:28:19

Hi,
i am using SQL Server 2008 R2 Ent. SQL is being used for SharePoint, TFS and Reporting Services. Now response from DB is getting slow. Please guide me steps how to optimize SQL Server performance. What steps to follow.

Thanks.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-26 : 01:57:20

First run SQL Profiler and get slow running quries. Then for each query, get execution plan to find out which part is causing its slowness. If requried create proper indexes and refactor your code to help SQL Server Optimizer to select a better index.

--------------------------
http://connectsql.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-26 : 04:21:37

If you are unfamiliar with profiler it can be a bit of a steep learning curve. Also if you aren't 100% with execution plans then I can recommend this as a starting point:

http://assets.red-gate.com/community/books/assets/sql-server-execution-plans.pdf

Do you have any idea of what's currently going on?

you may get some mileage also by looking at the object execution stats report

Right click on db go to reports -> standard reports -> Object Execution Statistics...

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Ibid
Starting Member

4 Posts

Posted - 2012-08-02 : 01:41:00

Getting through all those profile events is a lot of work - but there are a few free tools out there that could help you. I think the 2 most popular ones are Qure Analyzer ([url]http://www.dbsophic.com/qure-analyzer.html[/url]) and ClearTrace. I highly recommend you use one of them to help you analyze those Profiler traces.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-08-02 : 08:55:27

Run the query below when the performance is poor. It will show you all actively running queries. It is ordered by StartTime so the culprit will likely be the first row returned. Also, if a Stored Procedure is running, this will show you both the Stored Procedure and which statment within the Stored Procedure SQL Server is currently having trouble with.


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 statement_end_offset WHEN -1 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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-02 : 11:47:22

Beyond suggestion made above - which are all recommended, afre you maintaining statistics? For example , is your data changing regularly . If so , some index management may be required
Also, have any changes happened recently on the system \ subsystem ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-03 : 16:52:09

Pretty broad topic...You could look at wait stats and see what your threads are waiting for, then once you have that narrowed down, you can drill deeper (If it is IO, or Blocking, or whatever)

-Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-03 : 17:01:23

Chad!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-03 : 17:05:54

Hi Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources