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/
|||On Aug 14, 4:04 pm, Alex Kuznetsov <AK_TIREDOFS...@.hotmail.COM> wrote:
> 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 MVPhttp://sqlserver-tips.blogspot.com/
Learn how to understand and read the Execution Plan for your SQL,
that's a great place to start.
Good Luck,
Mark
|||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.googlegrou ps.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.googlegr oups.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 it
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 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...
>
>