Power BI Index Strategies
Power BI is an amazing tool and is being used more and more by small and medium-sized businesses, but something that arises regularly, is the eventual sluggishness of both Power BI, as well as the databases it is connected to. While this can be caused by many factors, I would like to go over the one I see the most, particularly with companies that do not have a dedicated DBA.
Note: Check out EPC Group’s Data Architecture Consulting Practice site for more information.
Index refreshing and rebuilding is one of those little processes that often either get forgotten or are not known about. Many off the shelf CRM and Account Software that uses a SQL database will often have a large number of tables. Many of these tables, over an extended period, can become fragmented since indexes are used in almost every query to a database. The erosion of these indexes can start to become a major bottleneck in your processes.
So, what are indexes, what do they do, and how can one keep them clean? In the next few paragraphs, I will give a quick overview and a few different solutions that can be used to help keep your business running quickly.
An index is exactly what it sounds like; it is a way for your database to locate data quickly by skipping over large chunks of a table that would otherwise have to be read line by line. It does this the same way you would look at the index of a book and find a word or phrase and skip to that page of the book. However, if the book keeps growing and words and phrases are just added to the end and not incorporated into the primary layout of the index then it becomes gradually more useless, and the time saving is lost. That is what rebuilding or refreshing an index does. It moves those entries from the back of the index to their proper location, thus restoring functionality.
Now that we know what the problem is, we will review three ways to remedy the situation. Please note that any index cleaning should be done during a non-peek time or off-hours, especially if you are not using enterprise editions of SQL Server.
1. Run the built-in software from the manufacturer. Often CRM and Accounting software will include a maintenance utility that will fix this issue. However, many of those software’s are designed for enterprise solutions and not standard or express editions and running them during normal business hours will do one of two things. Either they will not do the maintenance requested, and mark it as a success, or will bring your organization’s database to a stop.
2. Manually checking index fragmentation of key tables. This is the slowest method but does provide the most pinpointed effectiveness. If you know which tables are constantly being updated or written into, then you can expand the table and right-click on the “Index”.
- Select the rebuild all indexes option, you will then be brought to the rebuild indexes wizard
- From there, all you must do is click “OK.” If the fragmentation is less than 15% to 30%, then it will just refresh; however, if it is greater, it will rebuild the index, thus restoring functionality. Because I have a process that cleans all my indexes weekly the fragmentation is normally pretty light as the picture above shows.
3. SQL Statements. The last is more complex and will require someone with a strong knowledge of SQL statements and job creation. For this to work, you will need to write a stored procedure that goes through the system files of the database and looks for index fragmentation anything greater than 15% retrieves the index name and runs the SQL statement below for each index.
- ALTER INDEX [index name here] ON [Database].[Schema].[Table] REBUILD;
- Then you will want to create a job that runs this script over during your off-time hours. Then you will want to create a job that runs this script over during your off-time hours.
No matter which option you choose, it vital that a regular maintenance schedule is upheld so that performance can always be at its best.