Best way to Learn T-SQL Performance Tuning

Hi,
I want to learn how to get the most performance out of T-SQL code.
essentially I want to learn to be able to rewrite T-SQL code to get better
performance out of stored procedures. What would you recommend is the best
AND fastest way to learn that? I am more interested in leaning performance
tuning of the code rather than SQL server itself.
Any recommendations on the site, books, software, etc would be good.
Thank you.http://www.sql-server-performance.com/transact_sql.asp
Regards,
Jamie
"Dragon" wrote:

> Hi,
> I want to learn how to get the most performance out of T-SQL code.
> essentially I want to learn to be able to rewrite T-SQL code to get better
> performance out of stored procedures. What would you recommend is the best
> AND fastest way to learn that? I am more interested in leaning performance
> tuning of the code rather than SQL server itself.
> Any recommendations on the site, books, software, etc would be good.
> Thank you.
>
>|||The problem you face in learning tuning of the code, rather than of
SQL Server, is that the biggest part of tuning is in the database
design and the indexing. The optimizer often turns many different
coding approaches into the same thing internally. What remaind for
tuning the code means understanding the optimizer. That is a bit of a
moving target as each new release and even service pack changes the
rules to some degree.
Roy Harvey
Beacon Falls, CT
On Tue, 14 Aug 2007 12:32:41 -0700, "Dragon"
<noSpam_baadil@.hotmail.com> wrote:

>Hi,
>I want to learn how to get the most performance out of T-SQL code.
>essentially I want to learn to be able to rewrite T-SQL code to get better
>performance out of stored procedures. What would you recommend is the best
>AND fastest way to learn that? I am more interested in leaning performance
>tuning of the code rather than SQL server itself.
>Any recommendations on the site, books, software, etc would be good.
>Thank you.
>|||Thank you Roy.
the main reason I want to lean code optimization is because we are facing a
lot of slow code. for example, we recently encounter a stored procedure that
processed a particular function in over 60 hours. When it was rewritten with
some additional indexes and temp tables, total time came down to 30 minutes.
I want to learn this so that I can help in future occurrences of the same.
Thanks.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7i14c35a2f46914f27j6gft8fh170k82cl@.
4ax.com...[vbcol=seagreen]
> The problem you face in learning tuning of the code, rather than of
> SQL Server, is that the biggest part of tuning is in the database
> design and the indexing. The optimizer often turns many different
> coding approaches into the same thing internally. What remaind for
> tuning the code means understanding the optimizer. That is a bit of a
> moving target as each new release and even service pack changes the
> rules to some degree.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 14 Aug 2007 12:32:41 -0700, "Dragon"
> <noSpam_baadil@.hotmail.com> wrote:
>|||On Aug 14, 2:32 pm, "Dragon" <noSpam_baa...@.hotmail.com> wrote:
> Hi,
> I want to learn how to get the most performance out of T-SQL code.
> essentially I want to learn to be able to rewrite T-SQL code to get better
> performance out of stored procedures. What would you recommend is the best
> AND fastest way to learn that? I am more interested in leaning performance
> tuning of the code rather than SQL server itself.
> Any recommendations on the site, books, software, etc would be good.
> Thank you.
I would recommend "Inside SQL Server 2005" book series. These books
come from authors with well established reputations and the books are
great. You do not want to waste your time reading less than perfect
and/or incorrect stuff that is is published on some web sites.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/|||Thank you. That link did not work but I have been to that site before. It is
an excellent site with tons of usefull information. I think the only problem
is that it is more geared towards someone who is either looking for
particular information or is already an expert and needs confirmation. I am
not sure if it is good for a start-to-finish learning method. I could be
wrong.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A60F1359-98FB-41F2-9578-CA86E5A165DF@.microsoft.com...[vbcol=seagreen]
> http://www.sql-server-performance.com/transact_sql.asp
>
> --
> Regards,
> Jamie
>
> "Dragon" wrote:
>|||Thank you Mark. Any recommendation on a good source for leanring about
Execution Plans?
<mygolf51@.gmail.com> wrote in message
news:1187122016.610745.92110@.22g2000hsm.googlegroups.com...
> On Aug 14, 4:04 pm, Alex Kuznetsov <AK_TIREDOFS...@.hotmail.COM> wrote:
> Learn how to understand and read the Execution Plan for your SQL,
> that's a great place to start.
> Good Luck,
> Mark
>|||Thank you Alex.
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1187121848.781330.112210@.i38g2000prf.googlegroups.com...
> On Aug 14, 2:32 pm, "Dragon" <noSpam_baa...@.hotmail.com> wrote:
> I would recommend "Inside SQL Server 2005" book series. These books
> come from authors with well established reputations and the books are
> great. You do not want to waste your time reading less than perfect
> and/or incorrect stuff that is is published on some web sites.
> Alex Kuznetsov, SQL Server MVP
> http://sqlserver-tips.blogspot.com/
>|||Actually it sounds like you have already learned one of the most important
lessons. When you are optimizing long running queries, look for slow views
and especially slow views that are called repeatedly. Something else to be
aware of is whether the tables being called are wide tables. Faster views
consist of fewer fields. I have run across times when I could optimize a
view somewhat by joining only specific fields - for example instead of a
union of a current table with a history tables using select * from, change i
t
to select myID,CreateDate,myrequiredfield, myjoinfield from union (ditto) in
archive table. Other speedups may consist of regular rewrites of table
indexes (reindexing which defrags the index), creating non-clustered indexes
that are more pertinent to the larger queries and are wide enough to include
all the columns of a slower view - actually, this is something the tuning
optimizer (profiler) will give you when you run it.
--
Regards,
Jamie
"Dragon" wrote:

> Thank you Roy.
> the main reason I want to lean code optimization is because we are facing
a
> lot of slow code. for example, we recently encounter a stored procedure th
at
> processed a particular function in over 60 hours. When it was rewritten wi
th
> some additional indexes and temp tables, total time came down to 30 minute
s.
> I want to learn this so that I can help in future occurrences of the same.
> Thanks.
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:7i14c35a2f46914f27j6gft8fh170k82cl@.
4ax.com...
>
>|||Dragon wrote:
> Hi,
> I want to learn how to get the most performance out of T-SQL code.
> essentially I want to learn to be able to rewrite T-SQL code to get better
> performance out of stored procedures. What would you recommend is the best
> AND fastest way to learn that? I am more interested in leaning performance
> tuning of the code rather than SQL server itself.
> Any recommendations on the site, books, software, etc would be good.
> Thank you.
I am not sure what you are after.
One approach is to approach performance optimizing generically, as if it
were a job. Using this approach, you learn how to find out where
performance bottlenecks are, and where there is the most to gain. This
could be anywhere. It could be the use of the database by the
application(s), the way the client connects to the database, the network
traffic, the database structure, the way the queries are written, the
way the hardware is configured, etc. etc.
Using this approach, you mostly have to learn the methodology, and a
good judgement of different approaches towards the most cost effective
action.
The other approach is to specialize in a specific area, such as writing
efficient SQL statements. In poorly written code, a lot can be gained
with rewrites. The same is true for database tuning. In poorly designed
or poorly indexed database, a lot can be gained.
Obviously, you can't be an expert overnight. If you want to write good
SQL statements, you need to know SQL and the theory behind it and behind
RDBMS's and know things like how to change procedural code (such as
cursors) into set oriented code (SQL). If you want to write the most
efficient SQL statements, then you need to know about SQL Server
architecture, the query optimizer, the storage engine, know why UDF's
work the way they do, know about indexes and statistics, know about the
available physical operators, join methods, index strategies, etc. etc.
Gert-Jan