SQL Server Performance

"Online Index" in SQL Server 2000

Discussion in 'Performance Tuning for DBAs' started by BikeBoy, Oct 14, 2005.

  1. BikeBoy New Member

    Why so much fuss about Yukon's online indexing capability, can't we now (in sql 2000) just use indexed views on top of base tables to build indexes during the day?<br /><br />What am I missing here?[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />
  2. satya Moderator

    Check these 2 links and find it out :
    http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx

    In case the database is big enough and complicated hash joins then building indexes every day proves to be costly.

    The usability of indexed views is limited. While all editions of SQL Server can create and consume an indexed view, it is only the Enterprise Edition and Developer Edition that will make use of them without the addition of the query hint NOEXPAND.
    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. indshri Member

    Correct me if I am wrong -- but I think online indexing in Yukon means, the system provides you facility to reindex tables with less impact rather than locking the underlying tables completely. I think they are achieving this by locking at lower granularity like pages etc instead of full tables. This is not the case in SQL 2000.
    Indexed view means creating CI ( and non CI ) on views and this is feature is available in SQL 2000 and SQL 2005 , though I am not aware of any inherent differences if any.
    Am I correct ?
  4. satya Moderator

    Yes you're right about online indexing, in SQL 2000 the table will be locked until the duration of indexing.

    BOL documents IV
    For a standard view, the overhead of dynamically building the result set for each query that references a view can be substantial for views that involve complex processing of large numbers of rows, such as aggregating large amounts of data, or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored.


    Notes on SQL 2005 IVhttp://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx link.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. dtipton New Member

    quote:Originally posted by indshri

    Correct me if I am wrong -- but I think online indexing in Yukon means, the system provides you facility to reindex tables with less impact rather than locking the underlying tables completely.

    Can't you do this in SQL 2000 using the DBCC INDEXDEFRAG statement?

  6. Haywood New Member

    quote:Originally posted by dtipton
    Can't you do this in SQL 2000 using the DBCC INDEXDEFRAG statement?

    Indexdefrag is not as thorough as the RebuildIndex command. Currently the rebuildindex drops & creates the entire index which in 2000 is an 'offline' operation due to the table-lock being aquired for the index rebuild.

    IndexRebuild in 2K5 does not aquire the table lock for the duration of the index rebuild...


    Edit:

    I would be scared silly by anyone who wanted to rebuild indexes on views during the day... [B)]
  7. indshri Member

    dtipton -- yes , I was wrong there. Online defrag is available in both 2000 as well as 2005. However I don't understand what bikeboy meant -- "indexed views and online indexing ". Thats what I was trying to explain as well.
  8. satya Moderator

    In SQL 2005 defragmentation options has been optimized to take care of fragmentation, in SQL 2000 INDEXDEFRAG is online operation but work to a certain extent and by default you must use DBCC DBREINDEX for optimum performance by rebuilding indexes.

    BOL has clear notes between DBREINDEX & INDEXDEFRAG.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. dtipton New Member

    quote:Originally posted by satya

    by default you must use DBCC DBREINDEX for optimum performance by rebuilding indexes.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    From an article you referenced in another thread:

    Even with this limitation, the tests showed that DBCC INDEXDEFRAG can be as effective at improving performance of workloads as DBCC DBREINDEX. In fact, the tests showed that even if you can rebuild indexes so that there is minimal interleaving, this does not have a significant effect on performance. Reducing the logical fragmentation levels had a much greater impact on workload performance. This is why it is recommended that you focus on logical fragmentation and page density levels when examining fragmentation on your indexes.


  10. Adriaan New Member

    Nice move, quoting Satya against himself - except that the performance he is talking about there refers to response times after the DBCC operation, not to DBCC execution time in a database that is being accessed by users.
  11. satya Moderator

    That depends on the load ofdata and both the statements from that article and BOL are quite interleaving.

    DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. dtipton New Member

    I stand corrected!

    Thanks for the follow up.
  13. satya Moderator

    I don't see any contradictory statements above, even I too depend upon INDEXDEFRAG few times and only when the real thing is needed then I would go for DBREINDEX. DBCC SHOWCONTIG is the best source to decide whether to go for DBREINDEX or continue with INDEXDFRAG.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. BikeBoy New Member

    indshri, what I mean is build a view on a table, and build an index on the view. You can do this without locking the actual table.
  15. druer New Member

    I would caution you to be careful about Indexed Views. Creating an indexed view causes SQL Server to change the Set Arith Abort option to on, which may or may not break existing application code that assumes it can safely divide by zero. I wish I could say that I had thoroughly researched the issue, before trying it, but the reality is that I followed the Index Wizard selection, created an Indexed View and voila, the application crashed because it was expecting to be able to safely divide by zero, which I then broke by creating the Indexed View. Just be careful.
  16. Adriaan New Member

    We had a client who created an indexed view in our system, with the same results. I suspect they created the view using EM: when I scripted out their objects, there was a terrible mess of SET statements ON and OFF, between the object scripts - among which ARITH_ABORT.

    I would expect that the error does not occur when you write out the view and index definitions in QA, as in most installations the SET options will conform to the SQL Server settings.

    The error was occurring in an ODBC client app, in that you would get a warning on INSERT or UPDATE about the ARITH_ABORT setting being incorrect. However, when doing the INSERT or UPDATE through QA, there was no error - presumably because QA connects with correct settings - so perhaps that would produce a indexed view without the problem.
  17. druer New Member

    Thanks for the excellent suggestion Adriaan. In my situation I had indeed allowed the Index Wizard to create the Indexed View, and perhaps doing it by hand could have avoided the problem I encountered. I'll have to keep that in mind in the future.

Share This Page