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.
>|||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 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...
> 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.|||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:
> > 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 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...
> 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.
>>|||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:
>> 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 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 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:
>> 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/
>|||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...
> > 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.
> >>
>
>|||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|||Below are some resources that will help.
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
Query Recompilation in SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902682(SQL.80).aspx
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
SQL Server Performance Engineering Team Blog at
http://blogs.msdn.com/sqlperf/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Dragon wrote on Tue, 14 Aug 2007 12:32:41 -0700:
D> Hi,
D> I want to learn how to get the most performance out of T-SQL code.
D> essentially I want to learn to be able to rewrite T-SQL code to get
D> better performance out of stored procedures. What would you
D> recommend is the best
D> AND fastest way to learn that? I am more interested in leaning
D> performance tuning of the code rather than SQL server itself.
D> Any recommendations on the site, books, software, etc would be good.
D> Thank you.|||Hi Dragon
Query tuning has very little to do with re-writing code. Whilst it is true
that sometimes you do need to change code, it is crucial to understand that
SQL Server doesn't execute "code", it analyses code, turns it into execution
plans & executes the plans. The #1 rule for getting execution plans out of
SQL Server is that it can only work with the indexes you give it. Without
good indexes, SQL Server has no option other than to fully scan tables.
Once you understand these basic concepts, you will understand:
(a) The tuning game is mainly about indexes
and
(b) Re-writing SQL code is mainly about making sure that SQL Server
generates plans that use the correct indexes in the most efficient order.
This usually means using join or index hints and force ordering by
re-ordering names of tables in statements.
Structurally changing query expressions can also yield benefits, but only
because the change has influenced a more efficient execution plan. It is
also very common that changing the structure of query expressions will at
least slightly alter the actual meaning of the query & therefore potentially
the output of the query. This also means that tuning by changing query
syntax also creates more testing overhead than via indexing.
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
> 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.
>|||On Wed, 15 Aug 2007 11:02:39 +1000, "Greg Linwood"
<g_linwood@.hotmail.com> wrote:
>Once you understand these basic concepts, you will understand:
>(a) The tuning game is mainly about indexes
>and
...
Aaargh! It drives me nuts when people say that. Hey, half my
"tuning" work is running the wizard and restoring the PKs that have
mysteriously been dropped from major tables, but even so! :)
And half the rest is putting on new covering indexes for particular
expensive queries, but still! :)
The tuning game is as much about making sure your data model is
correct in general so that it even makes sense to build the indexes
that will let the compiler and optimizer have a chance at giving you a
good plan.
It's also about learning to use profiler (etc) just to see where the
friggin' thing is wasting its time, then you can work on the model or
the indexes or the coding and stand a chance of turning a profit.
I've outlined a SQLServer Performance cookbook, but I put it aside
because I needed some more hands-on with SQL2005. Maybe in six months
I'll be in sync with the releases and give it a shot.
J.|||I think that we tend to look at "Query tuning" from a perspective where you tune a query. This is
what many of the answers here has been focusing on. But lets not forget the slightly bigger picture,
say at the stored procedure level.
I'm thinking about things like cursors, populating a temp table and then select from it (can be a
good thing or a bad thing). Or just doing things in several TSQL statements where the logic can be
combined into one or fewer TSQL statements. Here the optimizer of course cannot re-write that logic
for us.
...just one additional thing to keep in mind.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi Dragon
> Query tuning has very little to do with re-writing code. Whilst it is true that sometimes you do
> need to change code, it is crucial to understand that SQL Server doesn't execute "code", it
> analyses code, turns it into execution plans & executes the plans. The #1 rule for getting
> execution plans out of SQL Server is that it can only work with the indexes you give it. Without
> good indexes, SQL Server has no option other than to fully scan tables.
> Once you understand these basic concepts, you will understand:
> (a) The tuning game is mainly about indexes
> and
> (b) Re-writing SQL code is mainly about making sure that SQL Server generates plans that use the
> correct indexes in the most efficient order. This usually means using join or index hints and
> force ordering by re-ordering names of tables in statements.
> Structurally changing query expressions can also yield benefits, but only because the change has
> influenced a more efficient execution plan. It is also very common that changing the structure of
> query expressions will at least slightly alter the actual meaning of the query & therefore
> potentially the output of the query. This also means that tuning by changing query syntax also
> creates more testing overhead than via indexing.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
> news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> 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.
>|||On Aug 15, 1:46 am, thejamie <theja...@.discussions.microsoft.com>
wrote:
> 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.- Hide quoted text -
> - Show quoted text -
The problem is BOL don't come with the manuals like they used to
before. Now they have internet and all that and you don't have those
books anymore. I would recommend picking up manuals (T-SQL)
specifically catered to 'Query Optimization and Performance Tuning'.
It's a huge and very complex concept and I can barely can scratch it.|||Microsoft and other entities have classes dedicated to perf tuning. That
will give you your quickest return I think.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
> 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.
>|||Karen Delaney is working on a book
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621969/ref=pd_bbs_sr_2/002-3675746-5484807?ie=UTF8&s=books&qid=1187195868&sr=8-2
Also Itzik Ben Gan and Tom Moreau did an excellent job in their book for SQL
2000.
http://www.amazon.com/Advanced-Transact-SQL-SQL-Server-2000/dp/1893115828/ref=sr_1_1/002-3675746-5484807?ie=UTF8&s=books&qid=1187195908&sr=1-1
You also might want to look at:
http://www.amazon.com/Server-Performance-Tuning-Distilled-Second/dp/1590594215/ref=sr_1_1/002-3675746-5484807?ie=UTF8&s=books&qid=1187195950&sr=1-1
relevantNoise - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
> 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 Tibor.
This is exactly what I am trying to learn. Most of our SQL usage is
stored-procedure based and my goal is work look at the existing as well as
new SPs and make sure they are not causing any table scan, they don't use
cursor unless they have to and they use temp tables where appropriate.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OgXrI2w3HHA.4712@.TK2MSFTNGP04.phx.gbl...
>I think that we tend to look at "Query tuning" from a perspective where you
>tune a query. This is what many of the answers here has been focusing on.
>But lets not forget the slightly bigger picture, say at the stored
>procedure level.
> I'm thinking about things like cursors, populating a temp table and then
> select from it (can be a good thing or a bad thing). Or just doing things
> in several TSQL statements where the logic can be combined into one or
> fewer TSQL statements. Here the optimizer of course cannot re-write that
> logic for us.
> ...just one additional thing to keep in mind.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi Dragon
>> Query tuning has very little to do with re-writing code. Whilst it is
>> true that sometimes you do need to change code, it is crucial to
>> understand that SQL Server doesn't execute "code", it analyses code,
>> turns it into execution plans & executes the plans. The #1 rule for
>> getting execution plans out of SQL Server is that it can only work with
>> the indexes you give it. Without good indexes, SQL Server has no option
>> other than to fully scan tables.
>> Once you understand these basic concepts, you will understand:
>> (a) The tuning game is mainly about indexes
>> and
>> (b) Re-writing SQL code is mainly about making sure that SQL Server
>> generates plans that use the correct indexes in the most efficient order.
>> This usually means using join or index hints and force ordering by
>> re-ordering names of tables in statements.
>> Structurally changing query expressions can also yield benefits, but only
>> because the change has influenced a more efficient execution plan. It is
>> also very common that changing the structure of query expressions will at
>> least slightly alter the actual meaning of the query & therefore
>> potentially the output of the query. This also means that tuning by
>> changing query syntax also creates more testing overhead than via
>> indexing.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> Benchmark your query performance
>> http://www.SQLBenchmarkPro.com
>> "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
>> news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> 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 have to say I really disagree with that Greg!
Tuning to me is about "best practice", although of course this means lots of
things, I believe well designed databases and code will perform.
Try the MSDN SQL Server best practice site:
http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
I could mention many techniques I consider to be best practice, I'll try a
few:-
Break it up - avoid massive procs, huge queries. It's easier to identify
bottlenecks ( eg using the Profiler Duration template )
Write "good" SQL - avoid CURSORs if at all possible, avoid overuse of
subqueries, overly complex expressions, know when to use temp tables and temp
variables, avoid use of VIEWs on VIEWs on VIEWs the optimizer isn't great
with these. Nice compact statements, well formatted and well commented ( it
matters! ). When to use the different constructs at your disposal eg LEFT
JOIN, IF ... ELSE, the new stuff in 2005 eg INTERSECT and EXCEPT, CTEs (
they're really cool ! ).
Know your data - I see so much use of DISTINCT / GROUP BY or ORDER BY when
there's no need.
Something really common I see is use of cursors to call stored procs
multiple times. "This is the only way to do this" I hear said. I love
converting these to set logic and seeing them run 10 times faster. Any data
which is available to a cursor to call a proc many times must surely be
available in a set.
Of course indexes.
Space - got your database file and log sizes right? Growths are costly!
Hardware - got it set up right? Got your tempdb set up right?
So as you can see "best practice" is a combination of all sorts of things.
Finally, it should include knowing your tools - I count knowing how to read
execution plans and knowing when to use the different Profiler templates
under that heading.
Hope some of that helps!
wBob|||On Wed, 15 Aug 2007 12:40:32 -0400, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:
>Karen Delaney is working on a book
>http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621969/ref=pd_bbs_sr_2/002-3675746-5484807?ie=UTF8&s=books&qid=1187195868&sr=8-2
Well, that should be worth waiting for!
J.|||In article <#WIrUs13HHA.1164@.TK2MSFTNGP02.phx.gbl>,
hilary.cotter@.gmail.com says...
> You also might want to look at:
> http://www.amazon.com/Server-Performance-Tuning-Distilled-Second/dp/1590594215/ref=sr_1_1/002-3675746-5484807?ie=UTF8&s=books&qid=1187195950&sr=1-1
>
>
Excellent resource to walk hands-on through to learn some principles. I
found it especially helpful for relative newbies as a learning tool.
The code samples are also all available online.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
MCT2007|||Kalen Delaney is also working on a book... ;-)
Actually, she is done working on it, and it is in the hands of the printers
now...
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:qcq8c3tehip3oj5fa5441f4et3o14c3u6g@.4ax.com...
> On Wed, 15 Aug 2007 12:40:32 -0400, "Hilary Cotter"
> <hilary.cotter@.gmail.com> wrote:
>>Karen Delaney is working on a book
>>http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621969/ref=pd_bbs_sr_2/002-3675746-5484807?ie=UTF8&s=books&qid=1187195868&sr=8-2
> Well, that should be worth waiting for!
>
> J.
>|||You got a lot of really good advice in this thread, in particular:
Tibor's comment:
>I'm thinking about things like cursors, populating a temp table and then
>select >from it (can be a good thing or a bad thing). Or just doing things
>in several TSQL statements >where the logic can be combined into one or
>fewer TSQL statements. Here the optimizer of course >cannot re-write that
>logic for us.
Note he only referenced stored procedures, this was the meat.
Mark's comment:
>Learn how to understand and read the Execution Plan for your SQL,
>that's a great place to start.
Just start by doing it.
Gert-Jen's comment:
>Using this approach, you mostly have to learn the methodology, and a
>good judgment of different approaches towards the most cost effective
>action.
Yes, methodology is possibly the most important.
Greg's comment:
>(b) Re-writing SQL code is mainly about making sure that SQL Server
>generates plans that use the correct indexes in the most efficient order.
>This usually means using join or index hints and force ordering by
>re-ordering names of tables in statements.
JXStern's comment:
>The tuning game is as much about making sure your data model is
>correct in general so that it even makes sense to build the indexes
>that will let the compiler and optimizer have a chance at giving you a
>good plan.
True, very very true.
>It's also about learning to use profiler (etc) just to see where the
>friggin' thing is wasting its time, then you can work on the model or
>the indexes or the coding and stand a chance of turning a profit.
The profiler can be a bitch to learn effectively, but it is your window into
the system where you get to see what is happening.
...
Beyond all of that fantastic advice, you were pointed to some really good
resources. Hey, I may buy Kalen's book.
What it all comes down to though is: can look at something and "see" what
kind of things are wrong. If you can do that, you will achieve your goal.
The two items I can think of that weren't highlighted were: improper, or
excessive use of temp tables (note Tibor's comment) and use of functions in
the WHERE clause.
Good Luck,
Good Skill,
Good Studying,
Jay
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
> 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'm really eager for that Kalen! :)
When will it be published? What's its name?
Thanks,
Leila
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uu15SQg4HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney is also working on a book... ;-)
> Actually, she is done working on it, and it is in the hands of the
> printers now...
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:qcq8c3tehip3oj5fa5441f4et3o14c3u6g@.4ax.com...
>> On Wed, 15 Aug 2007 12:40:32 -0400, "Hilary Cotter"
>> <hilary.cotter@.gmail.com> wrote:
>>Karen Delaney is working on a book
>>http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621969/ref=pd_bbs_sr_2/002-3675746-5484807?ie=UTF8&s=books&qid=1187195868&sr=8-2
>> Well, that should be worth waiting for!
>>
>> J.
>|||Must be this one:
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621969
available September 29, 2007.
--
Gert-Jan
Leila wrote:
> I'm really eager for that Kalen! :)
> When will it be published? What's its name?
> Thanks,
> Leila
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uu15SQg4HHA.4476@.TK2MSFTNGP06.phx.gbl...
> > Kalen Delaney is also working on a book... ;-)
> >
> > Actually, she is done working on it, and it is in the hands of the
> > printers now...
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> > www.InsideSQLServer.com
> > http://sqlblog.com
> >
> >
> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> > news:qcq8c3tehip3oj5fa5441f4et3o14c3u6g@.4ax.com...
> >> On Wed, 15 Aug 2007 12:40:32 -0400, "Hilary Cotter"
> >> <hilary.cotter@.gmail.com> wrote:
> >>
> >>Karen Delaney is working on a book
> >>
> >>http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621969/ref=pd_bbs_sr_2/002-3675746-5484807?ie=UTF8&s=books&qid=1187195868&sr=8-2
> >>
> >> Well, that should be worth waiting for!
> >>
> >>
> >> J.
> >>
> >
> >|||Hey All,
I would like to be able to write efficient SQL Statements (queries for
reporting purposes and sprocs for DB maintenance as well). I may, or may not
have the option of creating/modifying indicies. I understand that there may
be many ways to design a query to solve a problem, so I would like to be in a
position where I am aware of all my options and able to select the most
efficient design strategies to test. I suppose this means having a
theoretical understanding of how SQLServer processes SQL statements.
Can someone recommend a good resource I could use?
Thanks,
"Tibor Karaszi" wrote:
> I think that we tend to look at "Query tuning" from a perspective where you tune a query. This is
> what many of the answers here has been focusing on. But lets not forget the slightly bigger picture,
> say at the stored procedure level.
> I'm thinking about things like cursors, populating a temp table and then select from it (can be a
> good thing or a bad thing). Or just doing things in several TSQL statements where the logic can be
> combined into one or fewer TSQL statements. Here the optimizer of course cannot re-write that logic
> for us.
> ...just one additional thing to keep in mind.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
> > Hi Dragon
> >
> > Query tuning has very little to do with re-writing code. Whilst it is true that sometimes you do
> > need to change code, it is crucial to understand that SQL Server doesn't execute "code", it
> > analyses code, turns it into execution plans & executes the plans. The #1 rule for getting
> > execution plans out of SQL Server is that it can only work with the indexes you give it. Without
> > good indexes, SQL Server has no option other than to fully scan tables.
> >
> > Once you understand these basic concepts, you will understand:
> >
> > (a) The tuning game is mainly about indexes
> >
> > and
> >
> > (b) Re-writing SQL code is mainly about making sure that SQL Server generates plans that use the
> > correct indexes in the most efficient order. This usually means using join or index hints and
> > force ordering by re-ordering names of tables in statements.
> >
> > Structurally changing query expressions can also yield benefits, but only because the change has
> > influenced a more efficient execution plan. It is also very common that changing the structure of
> > query expressions will at least slightly alter the actual meaning of the query & therefore
> > potentially the output of the query. This also means that tuning by changing query syntax also
> > creates more testing overhead than via indexing.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> > http://blogs.sqlserver.org.au/blogs/greg_linwood
> > Benchmark your query performance
> > http://www.SQLBenchmarkPro.com
> >
> > "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
> > news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
> >> 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 like to be able to write efficient SQL Statements (queries for
> reporting purposes and sprocs for DB maintenance as well). I may, or may
> not
> have the option of creating/modifying indicies. I understand that there
> may
> be many ways to design a query to solve a problem, so I would like to be
> in a
> position where I am aware of all my options and able to select the most
> efficient design strategies to test.
Proper schema design and indexing is the most important part of query
performance. Much of efficient query development involves leveraging
existing indexes.
> I suppose this means having a
> theoretical understanding of how SQLServer processes SQL statements.
> Can someone recommend a good resource I could use?
A good books is "Inside SQL Server 2005: T-SQL Querying".
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hafeez Esmail" <HafeezEsmail@.discussions.microsoft.com> wrote in message
news:3707829A-5E4A-4B30-932D-4464CDEC1283@.microsoft.com...
> Hey All,
> I would like to be able to write efficient SQL Statements (queries for
> reporting purposes and sprocs for DB maintenance as well). I may, or may
> not
> have the option of creating/modifying indicies. I understand that there
> may
> be many ways to design a query to solve a problem, so I would like to be
> in a
> position where I am aware of all my options and able to select the most
> efficient design strategies to test. I suppose this means having a
> theoretical understanding of how SQLServer processes SQL statements.
> Can someone recommend a good resource I could use?
> Thanks,
> "Tibor Karaszi" wrote:
>> I think that we tend to look at "Query tuning" from a perspective where
>> you tune a query. This is
>> what many of the answers here has been focusing on. But lets not forget
>> the slightly bigger picture,
>> say at the stored procedure level.
>> I'm thinking about things like cursors, populating a temp table and then
>> select from it (can be a
>> good thing or a bad thing). Or just doing things in several TSQL
>> statements where the logic can be
>> combined into one or fewer TSQL statements. Here the optimizer of course
>> cannot re-write that logic
>> for us.
>> ...just one additional thing to keep in mind.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> > Hi Dragon
>> >
>> > Query tuning has very little to do with re-writing code. Whilst it is
>> > true that sometimes you do
>> > need to change code, it is crucial to understand that SQL Server
>> > doesn't execute "code", it
>> > analyses code, turns it into execution plans & executes the plans. The
>> > #1 rule for getting
>> > execution plans out of SQL Server is that it can only work with the
>> > indexes you give it. Without
>> > good indexes, SQL Server has no option other than to fully scan tables.
>> >
>> > Once you understand these basic concepts, you will understand:
>> >
>> > (a) The tuning game is mainly about indexes
>> >
>> > and
>> >
>> > (b) Re-writing SQL code is mainly about making sure that SQL Server
>> > generates plans that use the
>> > correct indexes in the most efficient order. This usually means using
>> > join or index hints and
>> > force ordering by re-ordering names of tables in statements.
>> >
>> > Structurally changing query expressions can also yield benefits, but
>> > only because the change has
>> > influenced a more efficient execution plan. It is also very common that
>> > changing the structure of
>> > query expressions will at least slightly alter the actual meaning of
>> > the query & therefore
>> > potentially the output of the query. This also means that tuning by
>> > changing query syntax also
>> > creates more testing overhead than via indexing.
>> >
>> > HTH
>> >
>> > Regards,
>> > Greg Linwood
>> > SQL Server MVP
>> > http://blogs.sqlserver.org.au/blogs/greg_linwood
>> > Benchmark your query performance
>> > http://www.SQLBenchmarkPro.com
>> >
>> > "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
>> > news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> >> 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.
>> >>
>> >
>> >
>>|||You didn't state your initial level of training/experience. If total
newbie, check out some of the beginner series books from WROX dealing with
sql server and transact sql. If knowledgeable, try some of the books by
Itzik Ben-Gan.
If your company will hire a professional for some performance
tuning/mentoring work your skills will grow by leaps and bounds very quickly
and the company will benefit at the same time.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hafeez Esmail" <HafeezEsmail@.discussions.microsoft.com> wrote in message
news:3707829A-5E4A-4B30-932D-4464CDEC1283@.microsoft.com...
> Hey All,
> I would like to be able to write efficient SQL Statements (queries for
> reporting purposes and sprocs for DB maintenance as well). I may, or may
> not
> have the option of creating/modifying indicies. I understand that there
> may
> be many ways to design a query to solve a problem, so I would like to be
> in a
> position where I am aware of all my options and able to select the most
> efficient design strategies to test. I suppose this means having a
> theoretical understanding of how SQLServer processes SQL statements.
> Can someone recommend a good resource I could use?
> Thanks,
> "Tibor Karaszi" wrote:
>> I think that we tend to look at "Query tuning" from a perspective where
>> you tune a query. This is
>> what many of the answers here has been focusing on. But lets not forget
>> the slightly bigger picture,
>> say at the stored procedure level.
>> I'm thinking about things like cursors, populating a temp table and then
>> select from it (can be a
>> good thing or a bad thing). Or just doing things in several TSQL
>> statements where the logic can be
>> combined into one or fewer TSQL statements. Here the optimizer of course
>> cannot re-write that logic
>> for us.
>> ...just one additional thing to keep in mind.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> > Hi Dragon
>> >
>> > Query tuning has very little to do with re-writing code. Whilst it is
>> > true that sometimes you do
>> > need to change code, it is crucial to understand that SQL Server
>> > doesn't execute "code", it
>> > analyses code, turns it into execution plans & executes the plans. The
>> > #1 rule for getting
>> > execution plans out of SQL Server is that it can only work with the
>> > indexes you give it. Without
>> > good indexes, SQL Server has no option other than to fully scan tables.
>> >
>> > Once you understand these basic concepts, you will understand:
>> >
>> > (a) The tuning game is mainly about indexes
>> >
>> > and
>> >
>> > (b) Re-writing SQL code is mainly about making sure that SQL Server
>> > generates plans that use the
>> > correct indexes in the most efficient order. This usually means using
>> > join or index hints and
>> > force ordering by re-ordering names of tables in statements.
>> >
>> > Structurally changing query expressions can also yield benefits, but
>> > only because the change has
>> > influenced a more efficient execution plan. It is also very common that
>> > changing the structure of
>> > query expressions will at least slightly alter the actual meaning of
>> > the query & therefore
>> > potentially the output of the query. This also means that tuning by
>> > changing query syntax also
>> > creates more testing overhead than via indexing.
>> >
>> > HTH
>> >
>> > Regards,
>> > Greg Linwood
>> > SQL Server MVP
>> > http://blogs.sqlserver.org.au/blogs/greg_linwood
>> > Benchmark your query performance
>> > http://www.SQLBenchmarkPro.com
>> >
>> > "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
>> > news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> >> 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.
>> >>
>> >
>> >
>>|||Thanks for the advice guys.
I've been writing SQL statements in Access (not the GUI query
builder...actually writing the SQL) for a few years now. I've also read the
Learn T-SQL in 10 minutes book (I think it was SAMS), which, other than
introducing me to triggers and views, didn't teach me anything new as far as
understanding which query designs are fastest and why.
Hiring a professional to do a personal lesson would be my favourite option,
but that would require flying someone into town...and that alone is way too
expensive.
"TheSQLGuru" wrote:
> You didn't state your initial level of training/experience. If total
> newbie, check out some of the beginner series books from WROX dealing with
> sql server and transact sql. If knowledgeable, try some of the books by
> Itzik Ben-Gan.
> If your company will hire a professional for some performance
> tuning/mentoring work your skills will grow by leaps and bounds very quickly
> and the company will benefit at the same time.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Hafeez Esmail" <HafeezEsmail@.discussions.microsoft.com> wrote in message
> news:3707829A-5E4A-4B30-932D-4464CDEC1283@.microsoft.com...
> > Hey All,
> >
> > I would like to be able to write efficient SQL Statements (queries for
> > reporting purposes and sprocs for DB maintenance as well). I may, or may
> > not
> > have the option of creating/modifying indicies. I understand that there
> > may
> > be many ways to design a query to solve a problem, so I would like to be
> > in a
> > position where I am aware of all my options and able to select the most
> > efficient design strategies to test. I suppose this means having a
> > theoretical understanding of how SQLServer processes SQL statements.
> > Can someone recommend a good resource I could use?
> >
> > Thanks,
> >
> > "Tibor Karaszi" wrote:
> >
> >> I think that we tend to look at "Query tuning" from a perspective where
> >> you tune a query. This is
> >> what many of the answers here has been focusing on. But lets not forget
> >> the slightly bigger picture,
> >> say at the stored procedure level.
> >>
> >> I'm thinking about things like cursors, populating a temp table and then
> >> select from it (can be a
> >> good thing or a bad thing). Or just doing things in several TSQL
> >> statements where the logic can be
> >> combined into one or fewer TSQL statements. Here the optimizer of course
> >> cannot re-write that logic
> >> for us.
> >>
> >> ...just one additional thing to keep in mind.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
> >> > Hi Dragon
> >> >
> >> > Query tuning has very little to do with re-writing code. Whilst it is
> >> > true that sometimes you do
> >> > need to change code, it is crucial to understand that SQL Server
> >> > doesn't execute "code", it
> >> > analyses code, turns it into execution plans & executes the plans. The
> >> > #1 rule for getting
> >> > execution plans out of SQL Server is that it can only work with the
> >> > indexes you give it. Without
> >> > good indexes, SQL Server has no option other than to fully scan tables.
> >> >
> >> > Once you understand these basic concepts, you will understand:
> >> >
> >> > (a) The tuning game is mainly about indexes
> >> >
> >> > and
> >> >
> >> > (b) Re-writing SQL code is mainly about making sure that SQL Server
> >> > generates plans that use the
> >> > correct indexes in the most efficient order. This usually means using
> >> > join or index hints and
> >> > force ordering by re-ordering names of tables in statements.
> >> >
> >> > Structurally changing query expressions can also yield benefits, but
> >> > only because the change has
> >> > influenced a more efficient execution plan. It is also very common that
> >> > changing the structure of
> >> > query expressions will at least slightly alter the actual meaning of
> >> > the query & therefore
> >> > potentially the output of the query. This also means that tuning by
> >> > changing query syntax also
> >> > creates more testing overhead than via indexing.
> >> >
> >> > HTH
> >> >
> >> > Regards,
> >> > Greg Linwood
> >> > SQL Server MVP
> >> > http://blogs.sqlserver.org.au/blogs/greg_linwood
> >> > Benchmark your query performance
> >> > http://www.SQLBenchmarkPro.com
> >> >
> >> > "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
> >> > news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
> >> >> 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.
> >> >>
> >> >
> >> >
> >>
> >>
>
>|||Hafeez Esmail (HafeezEsmail@.discussions.microsoft.com) writes:
> I've been writing SQL statements in Access (not the GUI query
> builder...actually writing the SQL) for a few years now. I've also
> read the Learn T-SQL in 10 minutes book (I think it was SAMS), which,
> other than introducing me to triggers and views, didn't teach me
> anything new as far as understanding which query designs are fastest and
> why. Hiring a professional to do a personal lesson would be my favourite
> option, but that would require flying someone into town...and that
> alone is way too expensive.
With that background, the "Inside SQL Server 2005" books by Itzik Ben-Gan
and Kalen Delaney should definitely be a good option.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||also you may browse through the articles on this site:
http://www.sql-server-performance.com/
--
http://elsasoft.org
"Hafeez Esmail" <HafeezEsmail@.discussions.microsoft.com> wrote in message
news:3707829A-5E4A-4B30-932D-4464CDEC1283@.microsoft.com...
> Hey All,
> I would like to be able to write efficient SQL Statements (queries for
> reporting purposes and sprocs for DB maintenance as well). I may, or may
> not
> have the option of creating/modifying indicies. I understand that there
> may
> be many ways to design a query to solve a problem, so I would like to be
> in a
> position where I am aware of all my options and able to select the most
> efficient design strategies to test. I suppose this means having a
> theoretical understanding of how SQLServer processes SQL statements.
> Can someone recommend a good resource I could use?
> Thanks,
> "Tibor Karaszi" wrote:
>> I think that we tend to look at "Query tuning" from a perspective where
>> you tune a query. This is
>> what many of the answers here has been focusing on. But lets not forget
>> the slightly bigger picture,
>> say at the stored procedure level.
>> I'm thinking about things like cursors, populating a temp table and then
>> select from it (can be a
>> good thing or a bad thing). Or just doing things in several TSQL
>> statements where the logic can be
>> combined into one or fewer TSQL statements. Here the optimizer of course
>> cannot re-write that logic
>> for us.
>> ...just one additional thing to keep in mind.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:uj$Ppft3HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> > Hi Dragon
>> >
>> > Query tuning has very little to do with re-writing code. Whilst it is
>> > true that sometimes you do
>> > need to change code, it is crucial to understand that SQL Server
>> > doesn't execute "code", it
>> > analyses code, turns it into execution plans & executes the plans. The
>> > #1 rule for getting
>> > execution plans out of SQL Server is that it can only work with the
>> > indexes you give it. Without
>> > good indexes, SQL Server has no option other than to fully scan tables.
>> >
>> > Once you understand these basic concepts, you will understand:
>> >
>> > (a) The tuning game is mainly about indexes
>> >
>> > and
>> >
>> > (b) Re-writing SQL code is mainly about making sure that SQL Server
>> > generates plans that use the
>> > correct indexes in the most efficient order. This usually means using
>> > join or index hints and
>> > force ordering by re-ordering names of tables in statements.
>> >
>> > Structurally changing query expressions can also yield benefits, but
>> > only because the change has
>> > influenced a more efficient execution plan. It is also very common that
>> > changing the structure of
>> > query expressions will at least slightly alter the actual meaning of
>> > the query & therefore
>> > potentially the output of the query. This also means that tuning by
>> > changing query syntax also
>> > creates more testing overhead than via indexing.
>> >
>> > HTH
>> >
>> > Regards,
>> > Greg Linwood
>> > SQL Server MVP
>> > http://blogs.sqlserver.org.au/blogs/greg_linwood
>> > Benchmark your query performance
>> > http://www.SQLBenchmarkPro.com
>> >
>> > "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
>> > news:eDBQinq3HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> >> 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.
>> >>
>> >
>> >
>>|||"Hafeez Esmail" <HafeezEsmail@.discussions.microsoft.com> wrote in message
news:3707829A-5E4A-4B30-932D-4464CDEC1283@.microsoft.com...
> Hey All,
> I would like to be able to write efficient SQL Statements (queries for
> reporting purposes and sprocs for DB maintenance as well). I may, or may
> not
> have the option of creating/modifying indicies. I understand that there
> may
> be many ways to design a query to solve a problem, so I would like to be
> in a
> position where I am aware of all my options and able to select the most
> efficient design strategies to test. I suppose this means having a
> theoretical understanding of how SQLServer processes SQL statements.
> Can someone recommend a good resource I could use?
if you cannot create or modify indices this may prove to be an *extremely*
frustrating exercise, if not an impossible one; that said, one piece of
advice: learn to read and understand execution plans ... they will tell you
a lot about how SQL Server is processing your query ... and they will also
serve as a segue into "theoretical understanding" if you are inclined to go
down that road ...
but really, if you are being constrained by company policy re
creation/modification of indices, either use your own machine or ask if your
DBA can set up an offline database where you *can* create and modify indices