none
How indexing affect performance on Analyses Service Cube? RRS feed

  • Question

  • Is indexing of database table relavent when using SQL Server 2012 AS and Reporting Services? We have 100'000-4'000'000 rows on tables.


    Kenny_I

    Friday, March 22, 2013 10:06 AM

Answers

  • Hi Kenny,

    there are times when proper index creation/redesign reduces the query execution time from several minutes to a few seconds. For example, as you can see the two queries below, the first one selects from DimEmployee table which has a clustered index and takes only 32% of the total cost in comparison with another query from DimEmployeeHeap table, which is a heap and takes 68% of the total cost as it uses the SORT physical operator. This is a very small table with just 296 records, now consider a table with millions of records how much different this could be.

    So the point is, create appropriate indexes on source tables to improve the performance of the query which SSAS fires while processing the cube or while retrieving data from the source. If you have access to the source data, you can use this DMV to identify missing indexes or you can use the Index Tuning Advisor for identifying and creating missing indexes on the source.

    A word of caution, no doubt creating appropriate indexes improves the performance but creating too many indexes puts an extra overhead on SQL Server for maintaining these indexes and slows down the write/load operations. Therefore it's prudent to keep in mind "relational query performance vs extra storage/maintenance" when creating new indexes.

    Often, while loading data in a relational data warehouse (if the data volume is huge), we drop indexes and recreate them after the data load. If you are following this approach, you should create indexes with FILL_FACTOR = 100, this means reading less pages and hence less I/O. If you are not following this approach, you should occasionally check the fragmentation level of the indexes and rebuild them if the level goes beyond a certain range. To learn more about checking fragmentation levels and rebuilding indexes, click here.

    Regards,

    Zaim Raza


    http://zaimraza.wordpress.com/


    • Edited by Zaim Raza Friday, March 22, 2013 10:22 AM
    • Marked as answer by Kenny_I Friday, March 22, 2013 12:03 PM
    Friday, March 22, 2013 10:21 AM

All replies

  • Hi Kenny,

    there are times when proper index creation/redesign reduces the query execution time from several minutes to a few seconds. For example, as you can see the two queries below, the first one selects from DimEmployee table which has a clustered index and takes only 32% of the total cost in comparison with another query from DimEmployeeHeap table, which is a heap and takes 68% of the total cost as it uses the SORT physical operator. This is a very small table with just 296 records, now consider a table with millions of records how much different this could be.

    So the point is, create appropriate indexes on source tables to improve the performance of the query which SSAS fires while processing the cube or while retrieving data from the source. If you have access to the source data, you can use this DMV to identify missing indexes or you can use the Index Tuning Advisor for identifying and creating missing indexes on the source.

    A word of caution, no doubt creating appropriate indexes improves the performance but creating too many indexes puts an extra overhead on SQL Server for maintaining these indexes and slows down the write/load operations. Therefore it's prudent to keep in mind "relational query performance vs extra storage/maintenance" when creating new indexes.

    Often, while loading data in a relational data warehouse (if the data volume is huge), we drop indexes and recreate them after the data load. If you are following this approach, you should create indexes with FILL_FACTOR = 100, this means reading less pages and hence less I/O. If you are not following this approach, you should occasionally check the fragmentation level of the indexes and rebuild them if the level goes beyond a certain range. To learn more about checking fragmentation levels and rebuilding indexes, click here.

    Regards,

    Zaim Raza


    http://zaimraza.wordpress.com/


    • Edited by Zaim Raza Friday, March 22, 2013 10:22 AM
    • Marked as answer by Kenny_I Friday, March 22, 2013 12:03 PM
    Friday, March 22, 2013 10:21 AM
  • Thanks!

    1) Could verify me that indexing of database would affect not only prosessing of cube, but also how fast reports are displayd to end-user in the reporting service report?

    2) "Using SQL Server DMVs to Identify Missing Indexes" article suggest that SELECT * FROM sys.dm_db_missing_index_details is good way to analyze index candites.

    Is it so that best time to create indexes is after we have created reports so that we know more information about reporting requirements and user behavior?


    Kenny_I


    • Edited by Kenny_I Friday, March 22, 2013 11:53 AM
    Friday, March 22, 2013 11:03 AM
  • Hi Kenny,

    It's all depends upon your requirments. for your information, index creation is the recursive process. it's good idea to build the index through the Database engine tunning advisor.

    Thanks,

    Zaim Raza.


    http://zaimraza.wordpress.com/

    Friday, March 22, 2013 11:26 AM