SQL Server Performance

ANSI_NULLS and Performance

Discussion in 'T-SQL Performance Tuning for Developers' started by sgovoni, Jun 10, 2008.

  1. sgovoni New Member

    Hi,
    I am developing an application that uses OLE DB to connect to SQL Server 2000; originally the application was using DB-Library to connect to SQL Server.
    To maintain compatibility with operators "=", ">=", etc. in comparison with the values NULL, I set to OFF (immediately after the connection) the database option ANSI_NULLS.
    After this setting users complain about slow running query, using SQL Profiler and Query Analyzer I compared the execution plan and the logical reads of slow query and observing this behavior:
    SET STATISTICS IO ON
    sp_executesql
    N'SELECT A.idA idA, Sum(B.Qta) Qta, Max(B.Date) Date FROM Table1 B Left Outer Join Table2 A On B.idA = A.id WHERE (B.idE= @P1) And (A.idA= @P2) GROUP BY A.idA', N'@P1 int,@P2 int', 35, 5624
    With ANSI_NULLS OFF (my current setting):
    Warning: Null value is eliminated by an aggregate or other SET operation.
    Table 'Table1'. Scan count 1, logical reads 7071, physical reads 0, read-ahead reads 0.
    Table 'Table2'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0. With ANSI_NULLS ON:
    Warning: Null value is eliminated by an aggregate or other SET operation.
    Table 'Table1'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
    Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
    6 logical reads against 7071 logical reads on my current settings [:O].
    With ANSI_NULLS set to OFF the execution plan shows an Index Scan, while with ANSI_NULLS set to ON the execution plan shows a Index Seek ... and I believe it is the difference here.

    Do you have any suggestions for improving the performance of queries in a situation like this ?
    Thanks in advance.
    Sergio
  2. techbabu303 New Member

    Are the users of application using DBlibrary or OLEDB ?
    If they are using Microsoft OLEDB or ODBC drivers the default option is SET ANSI NULL ON when they access in such cases this settings would return zero rows for NULL values as you are aware.
    Even after you turn the ANSI to OFF , the user connecting using the OLDEB will turn it back ON by default.If the users are using DB library with ANSI NULL OFF then I guess you have catch 22 since you have app code which does not ahere to SQL 92 standard.
    In relation to indexes used in predicates I have no clue how it relates to this setting , will need to dig deeper.
    Reference
    http://doc.ddart.net/mssql/sql70/set-set_5.htm
    Cheers
    Sat
  3. sgovoni New Member

    Hi techbabu303,
    and thanks for your reply.
    [quote user="techbabu303"]
    Are the users of application using DBlibrary or OLEDB ?
    [/quote]


    The application in version 2.0 uses DB-Library, while the application in version 3.0 uses OLE DB. Users complain of slow query only with version 3.0 and analyzing the execution of queries with SQL Profiler showed the situation that I described.
  4. sgovoni New Member

    We arrived to solving the problem [:D], summarized in this article.

    Thanks to all!
  5. techbabu303 New Member

    Thx for posting the article but the issue it is foreign language I dont undertsand. Do you have anything in English ?
    -Sat
  6. techbabu303 New Member

    <p>Translation using google tool bar hope it is right .........&nbsp;</p><p>&nbsp;</p><p>When the option ANSI_NULLS Affects optimizer and performance</p><p><o:p>&nbsp;</o:p></p><p>A topic already addressed earlier, in post ANSI_NULLS by default:Differences between OLE DB and BDE, to add some considerations on the behaviourdell'optimizer (and hence performance) when the value assigned to theANSI_NULLS is different from default. <br>Let's assume that you have removed all dependencies DB-Library to replace BDEwith OLE DB in an application written in <st1:place w:st="on">Delphi</st1:place>.<br>To maintain compatibility of operators equals (=) and other (&lt;&gt;) incomparison with the value NULL, immediately after establishing the connectionwith SQL Server we set off to the option ANSI_NULLS (SET ANSI_NULLS OFF). <br>After this setting users complain about a slowdown in the query. </p><p><br>Let's assume the following scenario: a user requires the extraction of somedata relating to sales orders, the table db:confused:rderHeader stores tested customerorders, while the table db:confused:rderDetail stores lines. We have thereforestructures of the following data: <br>• Table db:confused:rderHeader (250,000 lines) with the following structure:<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/></v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" alt="db:confused:rderHeader" style='width:243.75pt;height:158.25pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image001.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderHeader%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image002.jpg" style="height: 211px; width: 325px;" alt="db:confused:rderHeader" title="db:confused:rderHeader" v:shapes="_x0000_i1025" height="211" width="325"><!--[endif]--><o:p></o:p></p><ul type="disc"><li class="MsoNormal" style=""><span style="">&nbsp;</span>Table db:confused:rderDetail (250,000 lines) with the following structure::<o:p></o:p></li></ul><p><!--[if gte vml 1]><v:shape id="_x0000_i1026" type="#_x0000_t75" alt="db:confused:rderDetail" style='width:180.75pt;height:84pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image003.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderDetail%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image004.jpg" style="height: 112px; width: 241px;" alt="db:confused:rderDetail" title="db:confused:rderDetail" v:shapes="_x0000_i1026" height="112" width="241"><!--[endif]--><o:p></o:p></p><p style="margin-left: 0.75in; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol;"><span style="">·<span style="font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></span></span><!--[endif]-->Query data extraction (simplified):</p><p>Select count (*) From db:confused:rderHeader OH Left Outer Join db:confused:rderDetail ORON OD.OrderHeaderID = OH.OrderID <br>Where (OH.CustomerID = @ P1) and (OD.OrderHeaderID = @ P2) <br>Confrontiamo hour plans for implementing the query when it is running on aconnection with ANSI_NULLS ON = (default for connections OLE DB - SSMS) andwhen it is running on a connection with ANSI_NULLS = OFF (default forconnections DB-Library). <br>-- Plan for implementing the queries performed with ANSI_NULLS = OFF:<!--[if gte vml 1]><v:shape id="_x0000_i1027" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:525pt;height:119.25pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image005.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-OFF.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image006.jpg" style="height: 159px; width: 700px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1027" height="159" width="700"><!--[endif]--><o:p></o:p></p><p>Table 'OrderHeader'. Scan count 1, logical reads <b>2607</b>, physical reads0, read-ahead reads 0.<o:p></o:p></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0.<o:p></o:p></p><p>-- Plan for implementing the queries performed with ANSI_NULLS = ON:<!--[if gte vml 1]><v:shape id="_x0000_i1028" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:494.25pt;height:118.5pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image007.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-ON.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image008.jpg" style="height: 158px; width: 659px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1028" height="158" width="659"><!--[endif]--><o:p></o:p></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0. <br>Table 'OrderHeader'. Scan count 1, logical reads 5, physical reads 0,read-ahead reads 0. <br>&nbsp; <br>In the case of ANSI_NULLS = OFF are not used indexes, but runs a scan of thetables db:confused:rderHeader and db:confused:rderDetail with a consequent increase access tothe pages of memory. <br>Thanks to the valuable contribution of David Mauri, we observe that: <br>"The queries performed on the two connections are no longer equivalent ifthe behaviour of equality is different from the default, then the optimizer cannot apply in the case of ANSI_NULLS = OFF, the same optimizations that mayapply if ANSI_NULL = ON. <br>With a Left Outer Join you can still get NULL values and if the operator equals(=) must give the same results when applied to NULL values, it follows that theimplementation plan must necessarily be different, with the result that in thisIf the indices are no longer used.&nbsp;<o:p></o:p></p>
  7. techbabu303 New Member

    <p>Translation using google tool bar hope it is right .........&nbsp;</p><p>&nbsp;</p><p>When the option ANSI_NULLS Affects optimizer and performance</p><p><o:p>&nbsp;</o:p></p><p>A topic already addressed earlier, in post ANSI_NULLS by default:Differences between OLE DB and BDE, to add some considerations on the behaviourdell'optimizer (and hence performance) when the value assigned to theANSI_NULLS is different from default. <br>Let's assume that you have removed all dependencies DB-Library to replace BDEwith OLE DB in an application written in <st1:place w:st="on">Delphi</st1:place>.<br>To maintain compatibility of operators equals (=) and other (&lt;&gt;) incomparison with the value NULL, immediately after establishing the connectionwith SQL Server we set off to the option ANSI_NULLS (SET ANSI_NULLS OFF). <br>After this setting users complain about a slowdown in the query. </p><p><br>Let's assume the following scenario: a user requires the extraction of somedata relating to sales orders, the table db:confused:rderHeader stores tested customerorders, while the table db:confused:rderDetail stores lines. We have thereforestructures of the following data: <br>• Table db:confused:rderHeader (250,000 lines) with the following structure:<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/></v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" alt="db:confused:rderHeader" style='width:243.75pt;height:158.25pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image001.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderHeader%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image002.jpg" style="height: 211px; width: 325px;" alt="db:confused:rderHeader" title="db:confused:rderHeader" v:shapes="_x0000_i1025" height="211" width="325"><!--[endif]--><o:p></o:p></p><ul type="disc"><li class="MsoNormal" style=""><span style="">&nbsp;</span>Table db:confused:rderDetail (250,000 lines) with the following structure::<o:p></o:p></li></ul><p><!--[if gte vml 1]><v:shape id="_x0000_i1026" type="#_x0000_t75" alt="db:confused:rderDetail" style='width:180.75pt;height:84pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image003.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/OrderDetail%28ANSI_NULLS%29.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image004.jpg" style="height: 112px; width: 241px;" alt="db:confused:rderDetail" title="db:confused:rderDetail" v:shapes="_x0000_i1026" height="112" width="241"><!--[endif]--><o:p></o:p></p><p style="margin-left: 0.75in; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol;"><span style="">·<span style="font-family: 'Times New Roman'; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></span></span><!--[endif]-->Query data extraction (simplified):</p><p>Select count (*) From db:confused:rderHeader OH Left Outer Join db:confused:rderDetail ORON OD.OrderHeaderID = OH.OrderID <br>Where (OH.CustomerID = @ P1) and (OD.OrderHeaderID = @ P2) <br>Confrontiamo hour plans for implementing the query when it is running on aconnection with ANSI_NULLS ON = (default for connections OLE DB - SSMS) andwhen it is running on a connection with ANSI_NULLS = OFF (default forconnections DB-Library). <br>-- Plan for implementing the queries performed with ANSI_NULLS = OFF:<!--[if gte vml 1]><v:shape id="_x0000_i1027" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:525pt;height:119.25pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image005.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-OFF.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image006.jpg" style="height: 159px; width: 700px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1027" height="159" width="700"><!--[endif]--><o:p></o:p></p><p>Table 'OrderHeader'. Scan count 1, logical reads <b>2607</b>, physical reads0, read-ahead reads 0.<o:p></o:p></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0.<o:p></o:p></p><p>-- Plan for implementing the queries performed with ANSI_NULLS = ON:<!--[if gte vml 1]><v:shape id="_x0000_i1028" type="#_x0000_t75" alt="Execution Plan ANSI_NULLS = ON" style='width:494.25pt;height:118.5pt'> <v:imagedata src="file:///C:DOCUME~1WINXP~1LOCALS~1Tempmsohtml11clip_image007.png" o:href="http://community.ugiss.org/blogs/sgovoni/Immagini/Query-Plan-ANSI_NULLS-ON.bmp"/></v:shape><![endif]--><!--[if !vml]--><img src="file:///C:/DOCUME%7E1/WINXP%7E1/LOCALS%7E1/Temp/msohtml1/01/clip_image008.jpg" style="height: 158px; width: 659px;" alt="Execution Plan ANSI_NULLS = ON" title="Execution Plan ANSI_NULLS = ON" v:shapes="_x0000_i1028" height="158" width="659"><!--[endif]--><o:p></o:p></p><p>Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0,read-ahead reads 0. <br>Table 'OrderHeader'. Scan count 1, logical reads 5, physical reads 0,read-ahead reads 0. <br>&nbsp; <br>In the case of ANSI_NULLS = OFF are not used indexes, but runs a scan of thetables db:confused:rderHeader and db:confused:rderDetail with a consequent increase access tothe pages of memory. <br>Thanks to the valuable contribution of David Mauri, we observe that: <br>"The queries performed on the two connections are no longer equivalent ifthe behaviour of equality is different from the default, then the optimizer cannot apply in the case of ANSI_NULLS = OFF, the same optimizations that mayapply if ANSI_NULL = ON. <br>With a Left Outer Join you can still get NULL values and if the operator equals(=) must give the same results when applied to NULL values, it follows that theimplementation plan must necessarily be different, with the result that in thisIf the indices are no longer used.&nbsp;<o:p></o:p></p>
  8. sgovoni New Member

    [quote user="techbabu303"]
    Translation using google tool bar hope it is right .........
    [/quote]
    I was to do the same thing [:D].
    Bye
    Sergio

Share This Page