2 Replies Latest reply on Jun 18, 2012 7:17 PM by lukeod

    Request: Look into improving performance of IPAM_spTouchGroupCounts Stored Procedure

    lukeod

      Hi,

       

      I've noticed for a while now that the time it takes for IPAM to make a modification to an IP Address seems to depend on how deep the IP Address is nested in the subnet hierarchy. Which is to say, performance is reasonable if the IP Address is only one or two Subnet/Supernet deep, but if it's 7 or 8 subnets deep, it can take up to 15 seconds to commit a change to a single entry. During this long commit time the CPU on the Database server is generally pegged at 100%.

       

      I've done the ususal troubleshooting, and am fairly convinced it's just the way IPAM works. The database server meets reccomended specs, and ipam performs well under all other circumstances.

       

      I did some digging with SQL Profiler etc, and came to the conclusion that the culprit (as in taking well over 90% of the duration of a commit) is the 'IPAM_spTouchGroupCounts' stored procedure. From what i can tell, this stored procedure recursively hops back up the tree structure in order to calculate the number of allocated/free IP's on every subnet/supernet along the tree. This makes sense - the larger the subnet/supernet tree the longer it takes as it will find more subnets to calculate.

       

      Not sure if much can be done about it, but it does cause dealing with bigger network topologies to be a pain. It's probably not something that most run into - this topology is pretty big. Approx 8000 rows in the IPAM_Group table and 450,000 in the IPAM_Node table.

       

      Perhaps there could be an option to disable IPAM from running this stored procedure after every change and for it to be put on a nightly/weekly schedule. I know that given the option between always accurate subnet counts and a responsive GUI, we would defiantly prefer a more responsive GUI.

       

      Regards,

       

      Luke