?
Solved

Performance Tuning

Posted on 2009-12-28
8
Medium Priority
?
369 Views
Last Modified: 2012-06-21
What is Performance Tuning? What are its basics and how it will be achieve in T-sql. Please explain with example.
0
Comment
Question by:sarabhai
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26136179
0
 
LVL 9

Expert Comment

by:teebon
ID: 26136185
Hi sarabhai,

There are several aspects to look at in performance tuning, you can get some basic one here:

http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx
0
 
LVL 9

Expert Comment

by:teebon
ID: 26136209
Oops, i'm 3 minutes late with the same link.

If you find that too basic for you, you might want to check out articles at SQLServerCentral:

http://www.sqlservercentral.com/tags/T-SQL/Performance+Tuning/

I personally find SQLServerCentral among the best place to get really good advice on SQL performance.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Accepted Solution

by:
Reza Rad earned 1000 total points
ID: 26136237
0
 
LVL 9

Author Comment

by:sarabhai
ID: 26136392
In general for SQL query tuning  which factors are to be used? like in your query don't use not exist other factor that take more time should replace with other equivalent alternative.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26136431
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26138608
Sarabhai -

The provided links are all great. Also look at Inside SQL Server T-SQL Querying book from Itzik Ben Gan this goes over the basics of how the optimizer thinks about a query and walks through different constructs.

Then learn through empirical evidence and experimentation. Run query with the actual execution plan included. Look up the operators in books online to understand why they are used where they are and what they mean.

Before you run a query run SET STATISTICS IO ON and look at the messages tab. Which tables had the highest reads? Look at the execution plan and ask yourself why they were higher based on the operators chosen.

Then look at the query and ask how can you better express what you are asking for. How can you help ensure an index is used (think of how you search an index.. you can easily find last_name begins with 'WAL' in the phone book with a seek or perhaps an ordered scan... You can't find "last name contains 'als' unless you scan every single last name to see if it has that string... the seek means less reading and smaller number of rows to join).

Make sure you have the right indexes based on the plans. Compare the total subtree cost of the plan and the reads in the IO Statistics before and after tuning. Did you make a difference?
0
 
LVL 9

Author Closing Comment

by:sarabhai
ID: 31670678
some performance steps need to required for it
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question