Basics SQL Performance Tuning - Queries and Tips

Basics of Performance Tuning

1. Check for Cost of Joins, Seek, Scans (Join Types listed in previous blog entries)
2. Seek is always better than scan, Look for possibilities where covering index can convert scans into seeks
3. When there is Key lookup for large number of data sets create a index, This is avoid lookup costs

DBCC FREEPROCCACHE --clears entire plan cache

DBCC DROPCLEANBUFFERS --clears all clean data cache pages

SET STATISTICS IO ON –- To see physical/logical read counts

Why should I create an index?

--Tip #1
--• Checking the reads and writes generated by the query using

SET STATISTICS IO ON

--• Checking the running time of the query using

SET STATISTICS TIME ON

select * from employees

sp_spaceused employees

--Tip #2

--Query Execution Time

DECLARE @start_time DATETIME

SELECT @start_time = GETDATE()

--<>

SELECT 'Elapsed Time, sec' = DATEDIFF( second, @start_time, GETDATE() )

GO

--Tip #3

SET SHOWPLAN_TEXT ON

SET SHOWPLAN_ALL ON

select * from employees

--Tip #4

/*

Seeks go directly, or at least very quickly, to the needed records while scans read the whole object (either table, clustered index, or non-clustered index). Thus, scans usually consume lots more resources than seeks.

*/

--Tip #5

SET NOCOUNT ON

/*

Reduces the amount of information passed from the server to the client. Therefore, it helps to lower network traffic and improves the overall response time of your transactions

*/

• Performance Tuning Tips From the Consultant Perspective
• SQL Server 2005/2008 database tuning - picking low hanging fruits - part I
• SQL Server 2008 Indexing Best Practices
• OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
• Foreign Keys are our friends...
• How to automate Update Statistics - Cindy Gross Blog
• Finding Tables Without Referential Integrity

More Reads

• "How to detect and avoid memory and resources leaks in .NET applications"
• How to detect and avoid memory and resources leaks in .NET applications

Notes - Updated

• The SQL Swiss Army Knife #2 - Scripting Logins
• The SQL Swiss Army Knife #3 - View I/O per file
• The SQL Swiss Army Knife #5 - Checking Autogrow times
• SQL Swiss Army Knife #7 - Adaptive Index Defrag
• SQL Swiss Army Knife #8 - Find a Maintenance Window   
• Adaptive Index Defrag - Performs intelligent defrag on one or more indexes for one or more databases
• About Maintenance Plans – grooming SQL Server
• Master List - SQL Swiss Army Knife Series is indexed

Happy Reading!!