tag:blogger.com,1999:blog-36590718907951667422024-03-08T08:36:42.066-08:00Efficient SQL Server 2008Performance tuning in sql server 2008Sinson francishttp://www.blogger.com/profile/16325588240161167152noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-3659071890795166742.post-90267224914797046602012-08-08T05:33:00.000-07:002013-11-09T22:10:37.228-08:00Perfromance Tuning in SQL Server 2008<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
</div>
<span style="color: #073763;"><br /></span>
<br />
<div class="MsoNormal">
<span style="color: #073763;"><b>Query Optimization</b></span></div>
<div class="MsoNormal">
<span style="color: #073763;"><br /></span></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>1.<span style="font-size: 7pt;">
</span><span style="background: white; font-family: "Verdana","sans-serif"; font-size: 9.0pt; line-height: 115%;">Remove *
from SELECT and use columns which are only necessary in code</span></b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>2.<span style="font-size: 7pt;">
</span><span style="background: white; font-family: "Verdana","sans-serif"; font-size: 9.0pt; line-height: 115%;">Remove any
unnecessary joins from table</span></b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>3.<span style="font-size: 7pt;">
</span>Always use WHERE Clause in SELECT Queries while
we don’t need all the rows to be returned.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>4.<span style="font-size: 7pt;">
</span>Avoid SELECT * FROM OrderTable WHERE
LOWER(UserName)='telsa' , because case insensitive.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>5.<span style="font-size: 7pt;">
</span>The operators shown below are in their
decreasing order of their performance.</b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><b> =<o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><b> >,>=,<, <=<o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><b> LIKE<o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><b> <><o:p> </o:p></b></span><br />
<span style="color: #073763;"><b><o:p> ie, better to go for '=' rather than 'LIKE' or '<>'</o:p></b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><br /></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>6.<span style="font-size: 7pt;">
</span>NOT IN, then this is going to offer poor
performance, this can be avoided by using EXISTS or NOT EXISTS.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>7.<span style="font-size: 7pt;">
</span>When there is a choice to use IN or EXIST, we
should go with EXIST clause for better performance.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>8.<span style="font-size: 7pt;">
</span>SELECT * FROM OrderTable WHERE Status = 1 AND
OrderID IN (222,444,111,555)</b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><b>Takes more time than </b></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="color: #073763;"><b>SELECT * FROM OrderTable with (INDEX=IX_OrderID)
WHERE Status = 1 AND OrderID IN (</b>
<b style="text-indent: -24px;">222,444,111,555</b> <b>6)</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>9.<span style="font-size: 7pt;">
</span>While there is case to use IN or BETWEEN clauses
in the query, it is always advisable to use BETWEEN for better result.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>10.<span style="font-size: 7pt;">
</span>Always avoid the use of SUBSTRING function in
the query.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>11.<span style="font-size: 7pt;">
</span>Provide the least likely true expressions first
in the AND. Because if the first condition is false the result also false, ie it never want to check the second condition of AND operation . </b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>12.<span style="font-size: 7pt;">
</span>It is sometimes better to combine queries using
UNION ALL instead of using many OR clauses.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>13.<span style="font-size: 7pt;">
</span>SELECT * FROM CustomerTable WHERE City =
'Wichita' OPTION(FAST n). In some scenario's we have to display only 10-15 result set in the web page . But in database it having more than Ten Thousand row. In this time the data base will give the 'n' result set very fast, and rest of the data will update soon. </b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>14.<span style="font-size: 7pt;">
</span>Avoid Expensive Operators Such as NOT LIKE</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>15.<span style="font-size: 7pt;">
</span>WITH NOLOCK and WITH READUNCOMMITTED</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>16.<span style="font-size: 7pt;">
</span>Avoid Long-Running Transactions. it has to do by the sys admin.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>17.<span style="font-size: 7pt;">
</span>Use Set NOCOUNT ON in stored procedures.</b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b><span style="font-family: 'Segoe UI', sans-serif; font-size: 10pt; line-height: 115%;">18.<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><span style="font-family: 'Segoe UI', sans-serif; font-size: 10pt; line-height: 115%;">You can rewrite a subquery to use JOIN and achieve better
performance.</span><o:p></o:p></b></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="color: #073763;"><b>19.<span style="font-size: 7pt;">
</span>Performance increase by adding a unique key for
table. (it can index the table by unique key)</b></span></div>
<div class="MsoListParagraphCxSpLast">
<br /></div>
</div>
Sinson francishttp://www.blogger.com/profile/16325588240161167152noreply@blogger.com7