cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Capture.JPG

Labels (1)
Tags (2)
Attachments
Comments

This is great. We use a corporate SQL Server in our company and It's managed by an administrator in the Server Department. I actually went over the maintenance plan with him twice to make sure the EPO database was being properly maintained. I always suspected SQL performance issues, but couldn't prove it because I don't administer the server. So you can imagine my surprise when I ran this script and found gobs of fragmentation and excessive pages. So I ran the script again in write mode, and one more time in read to access the results. It worked great! The dashboard loads faster now. I recommend everyone run this EVEN if your system administrator insures you that he/she is running a comprehensive maintenance plan on your database.

Thank you djjava9 .

the ePO i'm administering is ver 5.1.3 , running on win2008r2 sp1 and mssql express 2008r2 sp2 - i'm running this SQL script for some days but now it is failing due to a table index - here is the msg

OPERATION: REORGANIZE

EXECUTING: ALTER INDEX [IX_EPODataChannelData_CreatedOn] ON [dbo].[EPODataChannelData] REORGANIZE;  UPDATE STATISTICS [dbo].[EPODataChannelData] [IX_EPODataChannelData_CreatedOn];

Msg 2552, Level 16, State 1, Server DUBROVNIK, Line 1

The index "IX_EPODataChannelData_CreatedOn" (partition 1) on table "EPODataChannelData" cannot be reorganized because page level locking is disabled.

i verified and yes for this index the option "Use page locks when accessing the index" is not selected

since i inherited the administration of this ePO environment - could someone tell me if for this index this option is set correctly ?

without "Use page locks when accessing the index" selected the index cannot be reorganized . i randomly verified other indexes of ePO tables but for them this option is enabled. is it an exception required by McAfee/Intel or it is something to correct/modify?

thanks in advance

Version history
Revision #:
1 of 1
Last update:
‎09-19-2011 07:04 PM
Updated by:
 

Community Help Hub

    New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.

  • Find Forum FAQs
  • Learn How to Earn Badges
  • Ask for Help
Go to Community Help

Join the Community

    Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

  • Get helpful solutions from McAfee experts.
  • Stay connected to product conversations that matter to you.
  • Participate in product groups led by McAfee employees.
Join the Community
Join the Community