Best way to copy database: learn

Showing posts with label learn. Show all posts

Thursday, February 16, 2012

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

best way to learn SQL Server

I am an experienced database developer who has worked primarily with
Unidata (a multi-value database). I wish to learn SQL Server, but will
need to do this at home on my own. The only machine I have available is
my laptop running Win XP sp2. I know I can download the SQL Server
2005 trial version or Express Edition, but I need to know if it's
possible to install either of these as a standalone on my laptop. (i.e.
my laptop would be both the server and client.)
If it is possible to run as a standalone? Do I need to install .net and
IIS? Has anyone successfully done this that can give me some pointers
or refer me to some good documentation on how to do this?
Thanks in advance!!
Tim> The only machine I have available
> is my laptop running Win XP sp2.
It could be good. How much RAM?
> I know I can download the SQL Server
> 2005 trial version or Express Edition, but I need to know if it's
> possible to install either of these as a standalone on my laptop.
> (i.e. my laptop would be both the server and client.)
Yes, you can. I did too.
> If it is possible to run as a standalone?
What do you mean? SQL Server installs as a system service, so you need a
client application to use it.
If you intend "may I have the server and the client on the same phisical
pc?", then that's ok.
> Do I need to install .net
Yes, version 2.0.
> and IIS?
This is necessary only if you want to install "SQL Server 2005 Express with
Advanced Services" and want to use Interation Services (e.g. Reporting Services).
> Has anyone successfully done this that can give me some
> pointers or refer me to some good documentation on how to do this?
I have few problems.
First, install .NET framework 2.0.
Then, install SQL Server 2005 Express (or Express with Advanced Services).
I suppose you will have few problems, even if you'll follow setup wizards.
If you need more infos, post again :-)
--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution|||Hi
You can install SQL Server (excluding Enterprise Edition) without issue on
Windows XP SP2. SQL Express is free to install does not come with all the
tools you would get with the other versions. You can also download SQL
Express Manager to manage your SQL Express instance. SQL Express can also be
installed as part of Visual Studio 2005.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx shows
which features are supported by which edition. Developer edition has all the
features of Enterprise Edition, but is not licenced for a production system.
This is only $50. System requirements can be found at
http://www.microsoft.com/sql/editions/developer/sysreqs.mspx or
http://www.microsoft.com/sql/editions/express/sysreqs.mspx
You will need to install .NET framework during or before (Express edition)
installing SQL Server if this is not already on your system.
John
"tmags23@.gmail.com" wrote:
> I am an experienced database developer who has worked primarily with
> Unidata (a multi-value database). I wish to learn SQL Server, but will
> need to do this at home on my own. The only machine I have available is
> my laptop running Win XP sp2. I know I can download the SQL Server
> 2005 trial version or Express Edition, but I need to know if it's
> possible to install either of these as a standalone on my laptop. (i.e.
> my laptop would be both the server and client.)
> If it is possible to run as a standalone? Do I need to install .net and
> IIS? Has anyone successfully done this that can give me some pointers
> or refer me to some good documentation on how to do this?
> Thanks in advance!!
> Tim
>|||Hi
I forgot to add, the virtual labs and webcasts on MSDN and Technet are
excellent sources of information for someone wishing to learn SQL Server.
John
"John Bell" wrote:
> Hi
> You can install SQL Server (excluding Enterprise Edition) without issue on
> Windows XP SP2. SQL Express is free to install does not come with all the
> tools you would get with the other versions. You can also download SQL
> Express Manager to manage your SQL Express instance. SQL Express can also be
> installed as part of Visual Studio 2005.
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx shows
> which features are supported by which edition. Developer edition has all the
> features of Enterprise Edition, but is not licenced for a production system.
> This is only $50. System requirements can be found at
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx or
> http://www.microsoft.com/sql/editions/express/sysreqs.mspx
> You will need to install .NET framework during or before (Express edition)
> installing SQL Server if this is not already on your system.
> John
> "tmags23@.gmail.com" wrote:
> > I am an experienced database developer who has worked primarily with
> > Unidata (a multi-value database). I wish to learn SQL Server, but will
> > need to do this at home on my own. The only machine I have available is
> > my laptop running Win XP sp2. I know I can download the SQL Server
> > 2005 trial version or Express Edition, but I need to know if it's
> > possible to install either of these as a standalone on my laptop. (i.e.
> > my laptop would be both the server and client.)
> >
> > If it is possible to run as a standalone? Do I need to install .net and
> > IIS? Has anyone successfully done this that can give me some pointers
> > or refer me to some good documentation on how to do this?
> >
> > Thanks in advance!!
> >
> > Tim
> >
> >|||Take a look into the below URL:-
http://msdn2.microsoft.com/en-us/library/ms143516.aspx
Thanks
Hari
<tmags23@.gmail.com> wrote in message
news:1166949097.123669.302670@.n51g2000cwc.googlegroups.com...
>I am an experienced database developer who has worked primarily with
> Unidata (a multi-value database). I wish to learn SQL Server, but will
> need to do this at home on my own. The only machine I have available is
> my laptop running Win XP sp2. I know I can download the SQL Server
> 2005 trial version or Express Edition, but I need to know if it's
> possible to install either of these as a standalone on my laptop. (i.e.
> my laptop would be both the server and client.)
> If it is possible to run as a standalone? Do I need to install .net and
> IIS? Has anyone successfully done this that can give me some pointers
> or refer me to some good documentation on how to do this?
> Thanks in advance!!
> Tim
>|||Thanks all for the great info. Sounds like I'm in business.
My laptop has 512MB of RAM, so that shouldn't be an issue. I downloaded
the .net framework and the Express edition. But I forgot to look for
the version with Advanced Services so I'll look for that instead.
Thanks also for the reminder about Express Manager. I'll grab that too.
One final question. Apparently IIS isn't supported in XP Home. Could I
still install it? (By the time I get to Reporting Services and need IIS
I'll probably have another box up and running with Windows 2000, but I
still would like to know if it's possible.)
Also I read there are some potential conflicts with Windows Firewall
blocking the port that SQL server listens on, but if I havve the server
and client on the same physical box that shoudn't be a problem, should
it?
Thanks again, and happy holidays!
Tim
Hari Prasad wrote:
> Take a look into the below URL:-
> http://msdn2.microsoft.com/en-us/library/ms143516.aspx
> Thanks
> Hari
> <tmags23@.gmail.com> wrote in message
> news:1166949097.123669.302670@.n51g2000cwc.googlegroups.com...
> >I am an experienced database developer who has worked primarily with
> > Unidata (a multi-value database). I wish to learn SQL Server, but will
> > need to do this at home on my own. The only machine I have available is
> > my laptop running Win XP sp2. I know I can download the SQL Server
> > 2005 trial version or Express Edition, but I need to know if it's
> > possible to install either of these as a standalone on my laptop. (i.e.
> > my laptop would be both the server and client.)
> >
> > If it is possible to run as a standalone? Do I need to install .net and
> > IIS? Has anyone successfully done this that can give me some pointers
> > or refer me to some good documentation on how to do this?
> >
> > Thanks in advance!!
> >
> > Tim
> >|||Hi Tim
If you are just working on the laptop as a standalone system then you should
have no problems with the firewall. If you wish to connect to the machine
from external machines then you will need specify the
DISABLENETWORKPROTOCOLS=0 on the command prompt for the installation see
http://msdn2.microsoft.com/en-us/library/ms143446.aspx SQLExpress with
Advanced Services only supports a subset of reporting services, so you will
be better trying that on different version.
Merry Christmas
John
"tmags" wrote:
> Thanks all for the great info. Sounds like I'm in business.
> My laptop has 512MB of RAM, so that shouldn't be an issue. I downloaded
> the .net framework and the Express edition. But I forgot to look for
> the version with Advanced Services so I'll look for that instead.
> Thanks also for the reminder about Express Manager. I'll grab that too.
> One final question. Apparently IIS isn't supported in XP Home. Could I
> still install it? (By the time I get to Reporting Services and need IIS
> I'll probably have another box up and running with Windows 2000, but I
> still would like to know if it's possible.)
> Also I read there are some potential conflicts with Windows Firewall
> blocking the port that SQL server listens on, but if I havve the server
> and client on the same physical box that shoudn't be a problem, should
> it?
> Thanks again, and happy holidays!
> Tim
> Hari Prasad wrote:
> > Take a look into the below URL:-
> >
> > http://msdn2.microsoft.com/en-us/library/ms143516.aspx
> >
> > Thanks
> > Hari
> >
> > <tmags23@.gmail.com> wrote in message
> > news:1166949097.123669.302670@.n51g2000cwc.googlegroups.com...
> > >I am an experienced database developer who has worked primarily with
> > > Unidata (a multi-value database). I wish to learn SQL Server, but will
> > > need to do this at home on my own. The only machine I have available is
> > > my laptop running Win XP sp2. I know I can download the SQL Server
> > > 2005 trial version or Express Edition, but I need to know if it's
> > > possible to install either of these as a standalone on my laptop. (i.e.
> > > my laptop would be both the server and client.)
> > >
> > > If it is possible to run as a standalone? Do I need to install .net and
> > > IIS? Has anyone successfully done this that can give me some pointers
> > > or refer me to some good documentation on how to do this?
> > >
> > > Thanks in advance!!
> > >
> > > Tim
> > >
>

Best way to learn SQL Mobile?

I am an experienced web developer using PHP and MySql have experience with VB.

What is the best way for me to learn programming pocket pc's? Is using sql server mobile the best offline database to use as I will be using MySql Compact framework to connect direcly to our Linux server.

Any help is much appreciated as I can't seem to find any useful tutorials on quering an sql databse to update, insert, read or delete. I have created a datebase however, I just don't know how to use it.

Thanks in advance.

Mark

Hi Mark,

The SQL Mobile is ideal for offline scenarios. You can find SQL Mobile related resource on MDSN. I am providing the links below

SQL Mobile page on MSDN - http://msdn.microsoft.com/mobility/sqlmobile/default.aspx

SQL Mobile page from SQL Server home: http://www.microsoft.com/sql/editions/sqlmobile/default.mspx

SQL Mobile Books Online - http://msdn2.microsoft.com/en-us/library/ms173053.aspx

For advanced topics like replication with Microsoft SQL Server backend databases you can visit the following links

Connectivity with Microsoft SQL Server - http://www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx

HOL - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp

Manish

|||I'm working on somthing simular too. I want to connect to a mysql server on the web.
What do you use to connect to the mysql server?

The only thing I found for CF is MySQLDirect...

could you please let me know.
Dennis|||

I think I use the same one, it is made by Core Lab. The download link is below.

http://crlab.com/mysqlnet/download.html

Thanks for the links but I still cannot find a tutorial that demonstrates some simple insert, delete, update and select queries.

Mark

|||

Hi Mark,

Below is a specific link to IBuySpy Sample app available on MSDN

http://msdn2.microsoft.com/en-us/library/ms174006.aspx

IBuySpy Delivery 2005 Sample Application (SQL Server Mobile) - "When you install IBuySpy Delivery, you install modified versions of the IBuySpy database and Web application, and the code specific to the IBuySpy Delivery 2005 smart device application."

Hope this helps

Regards

Manish

|||

Thanks for all this help Manish, but I think your going to hate me now. I am starting to think that I am either stupid, a complete novice or more likley both.

The sample app you gave me is overly complicated and requires MS Server 2005 to be installed on my computer. I have no intension of doing this as I don't need it. I just need a sample of an extreamely simple application that has its own database and doesn't sync with anything, it is stand alone.

I simply need to know how to select data from the database and store it in an array(?) or something similar, and to send sql command to the database. I ask to store the results of a quey in an array because every example app I have found shows the data as a datagrid, which is ugly and not suitable for my current project.

ie. With PHP I would explain the above like so.

1. Connect to database using a connect string.

2. get results of a query using something like : $queryResults = getQueryResults("SELECT * FROM table")

3. Use a loop to read the $queryResults array. Read the date using : $queryResults[0]['name'] <-- this would give you the stored value in the first row and the in the field 'name'.

Can development for the pocket pc be this easy? I have developed app's for the Palm OS and naver had as much trouble as this. Why is it so overly complicated?

Thanks for your patience and help.

Mark

Thursday, February 16, 2012

Best way to Learn T-SQL Performance Tuning

Best way to Learn T-SQL Performance Tuning

Best way to Learn T-SQL Performance Tuning

best way to learn SQL Server

Best way to learn SQL Mobile?

Best way to copy database

Blog Archive

About Me