Improving ePO Performance by Optimizing SQL

    One of the questions we get is "How do I improve the performance of my ePO server?"  How does someone even know if their ePO server is running optimally?  It's difficult to know if your performance is normal for your hardware and your environment or if it can be improved.  Believe it or not one of the most common causes of ePO slow down is your SQL performance.  This can manifest itself in the multiple ways, especially the rendering of dashboards and queries. 


    Fragmentation is always the biggest culprit when it comes to a slow performing SQL database.  Fragmentation is a "natural" process in the database lifecycle. If the ePO database is frequently updated via INSERT, UPDATE, or DELETE statements (which occurs very often with ePO) we can expect it to become fragmented over time. Let's start with some theory so we can better understand what fragmentation is and how it affects database performance. There are two different types of fragmentation in SQL Server: Internal and External. Internal fragmentation is the result of index pages taking up more space than needed. It is like having a book where some of the pages are left blank; we do not know what pages are blank until we read the entire book and the same applies for SQL Server, which has to read all the pages in the index wasting extra-time and server resources in the empty pages. External fragmentation occurs when the pages are not contiguous on the index. Following the book analogy, it is like having a book where pages are not ordered in a logical way (page 1, then page 2, then page 3 and so on) causing you to go back and forward to compound the information and make sense of the reading. Heavily used tables (large ePO environments) that contain fragmented indexes will impact your database performance.


    So now that we know the impact of fragmentation what do we do about it?  You can either rebuild or reorganize your indexes.  Index rebuild works by re-creating the index internally again and when that has been achieved, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.  Microsoft recommends you reorganize your index if fragmentation is > 10 % but < 30% and number of pages > 1000.  You should completely rebuild your index if fragmentation is > 30% and number of pages > 1000.  Hopefully you have been doing some kind of maintenance;  if you use the SQL maintenance plan wizard it will rebuild or reindex or both and does not offer the intelligence of deciding for you when to rebuild or reorganize.  To that avail, attached is a SQL script that processes this logic for you following the Microsoft recommendations.  It also has a report only feature so you can see  what changes its going to make before it actually makes them.  As you can see below, it lists the exact tables and the percentage of fragmentation and what it plans to do to fix it.  In my case, my Policy Auditor tables are approaching 30% fragmentation and only a reorganize is necessary.  This is a lightly used ePO database with only 30 nodes reporting to it.  If you have several thousand nodes and you never maintained your database, I can assure you your numbers will be much higher.  SQL script is attached.  Read the comments are if you want to tweak any variables.