34 Replies Latest reply on Nov 21, 2016 7:18 PM by cahunt

    Orion NPM Architecture, Speed, and SQL

    SomeClown

      In my last post, "What NPM Tips and Tricks do You Have?" I asked about tips and tricks, expecting a mashup of different things from all over the NPM world and to a certain extent that's what happened. Interestingly, however, a large section of the thread turned into a discussion about two things: maps and speed.

       

      There were certainly a lot of good map tips, and you can find more at Solarwinds Labs.  In fact, you can even find out how to make your boss happy with a Big Green Button.

       

      The speed issue is particularly intriguing to me since there are a lot of times where, let's be honest here, NPM is a bit of a dog when it comes to response. The web interface is notoriously slow, and gets even worse when you have a ton of custom widgets, do-dads, and whatchamacallits loading on a screen. Several people mentioned that a lot of speed can be picked up by getting in at the database level and pre-packaging certain things.

       

      ZachM wrote:

      Stored Procedures and custom Views created in the DB save us countless man hours and, in my experience, working directly in the DB can really expand your knowledge of the architecture of NPM overall. I highly recommend every SolarWinds engineer to challenge themselves to learn more SQL. I am by no means a DBA, but I can pull every bit of data you can get from the website, and I can do it faster 90% of the time.

       

      NPM is an incredibly flexible and extensible product, especially in recent revisions, and offers a lot of opportunity for people willing to really dig in behind the scenes. As usual, I have more questions:

       

      * What SQL version and architecture are you using (separate database, named instances, etc.)?

      * What architecture have you found helps in the speed department?

       

      As an example of what I'm interested in: we run Cisco UCS servers, with VMware as the hypervisor layer, backed by NetApp FAS3240 fully licensed arrays, with Flash Cache, etc. We tier our storage manually and have full production SQL and Oracle instances virtualized.  The storage is connected to the UCS with an aggregated 80GB, and the UCS to the core at 160GB.

        • Re: Orion NPM Architecture, Speed, and SQL
          njoylif

          I've recently spent a lot of time and effort on this, even consulting with Atlantic Digital, Inc., and Solarwinds directly.

          the outcome is we are focusing on the SAN and the Data Stores presented to the DB Server.

           

          I was able to initially noticeably increase my web performance by moving to 4 SSDs (purchased at Fry's - so they are SATA SSDs) in Raid 10 format for redundancy.

          Here, I had the LOG .LDF file on a SAN drive and the DB .MDF file on the local SSD array.

           

          I had Atlantic Digital out for training and consultation and they had me move the LOG .LDF file onto the SSD drives.  This alone further DOUBLED my performance.

           

          We are about to do a migration from Big Brother to SAM, so we decided to go enterprise and get a dedicated SAN.  We are getting a Dell PV with 10 SAS SSDs that will have 3 separate "drives" on the DB Server.

          One for the DB, one for Log and Temp, and one for Netflow [FileGroup].

           

          I believe Solarwinds is working on a document on this very subject.

          1 of 1 people found this helpful
            • Re: Orion NPM Architecture, Speed, and SQL
              SomeClown

              Yeah, disk I/O on the database servers is always a big performance bottleneck/opportunity.  My Oracle DBA and I work together on a lot of these same issues (moving certain mounts to certain disk arrays, etc.) to squeeze as much performance as possible out of the databases.  I haven't spent as much time as I'd like on the SQL Server side, but the benefits are the same, if executed a little differently.

               

              Good stuff, thanks!

              • Re: Orion NPM Architecture, Speed, and SQL
                Deltona

                Hi,

                 

                Just some friendly advice.

                 

                You really shouldn't RAID SSDs. This will more than halve the amount of bytes that can be written to the SSD in its lifetime as you're wearing out the flash memory. You're also maxing bandwidth on internal IO instead of using them on read/write IOPS. I'll give your SSDs max 3 years until they die abruptly. 3 months until you start losing performance.

                 

                The only supported RAID format for SSDs is RAID 0. Any other RAID format will severely impair the internal maintenance protocols built into the SSD BIOS. The protocols or plans rather, include Garbage Collection and TRIM command compatibility. Garbage Collection takes care of data that can be deleted (data can not be overwritten on SSDs, unlike on spindle disks. Instead they need to be moved to an empty block on the SSD). TRIM is an OS side command that forces the data to be nulled and by doing so, allows IO operations to continue without having to wait for Garbage Collection to complete. This spares your internal SSD IO capacity. RAID controllers nowadays still do not support TRIM commands passed on by the OS (unless RAID 0 is in use) Furthermore, for successful Garbage Collection and TRIM commands to be passed on to the SSD you'll need    at    least   10% of unpartitioned space on the SSD. Using 100% of SSD capacity is the worst you can do, whether using it as a single drive or not.

                 

                If you're going to use SSDs on an SQL server then use it for transaction logs which writes and reads data sequentially. This is where SSDs shine, in sequential data access, NOT random data access. Random data access performance is actually the benefits of raiding multiple disks.

                 

                Only use 1 SSD for logs or 2, but only if they are in RAID 0.

                Use traditional spindle disks for Data. Hybrid disks are better, though.

              • Re: Orion NPM Architecture, Speed, and SQL
                michael stump

                In my experience, NPM performance issues are due to a misconfigured SQL server. It's similar to a VDI project: things work well up to a certain point, at which you hit the IOPS limit on your LUN and all of your desktops grind to a halt. NPM is the same, where an initial deployment with little to no stored data works well, even if your SQL server isn't configured to best practices (CPU / memory allocations, storing logs and databases on separate volumes, creating a sane maintenance plan, et cetera). But once you collect a few months worth of data, and maybe turn on syslog, and take the plunge to capture flows, SQL can't keep up. Build a solid SQL server, wrap it in proper maintenance, and NPM should be happy.

                 

                I've also used another server for the web GUI to reduce load on the NPM server, but I did this in concert with migrating the NPM database to a proper MSSQL cluster, so I'm not certain how much performance benefit can be attributed to the web console being broken out. But I ended up with a traditional three-tiered web app architecture, and performance was no longer a problem.

                 

                All were vSphere VMs, EMC backend with separate LUNs for each volume on the SQL server, 2003 R2 for all nodes (I admit, this was a while ago. ). Maintenance plan is key, and tuning your NPM data retention settings is a big part of it, too. The old the data is, the more it should be summarized. YMMV.

                  • Re: Orion NPM Architecture, Speed, and SQL
                    familyofcrowes

                    What data retention settings are you using?  We are using Detail=30, Hourly=180 and Daily=780.  Our DB is 120Gb and response is NOT optimal.  We built the DB server dedicated to Orion, RAID 10, 15K drives, separate partition/drives for MDF, Log  file, etc. 

                     

                    We run NPM, SAM, NTA, UDT, IPAM, VNQM, WPM, and NCM.  The web server is on the main poller with two additional pollers.

                     

                    Response time has been our biggest issue with Orion.  I am hoping SAM 6.0 will help us determine where our issues are on the SQL server.

                     

                    I am NOT a DBA can know very little SQL.

                     

                    It's great hearing what you guys are doing to get good ideas.  SSD drives sound great, but is there a down side to SSD's?

                      • Re: Orion NPM Architecture, Speed, and SQL
                        njoylif

                        SSD downside: not so much any more, besides cost. 

                        The failure rates seem to be greatly reduced from when they first appeared. 

                        SAS over SATA will make a difference and within SAS, you have MCL and SLC, of which the SLC is better performance.

                         

                        We run all of those modules as well but are just starting full SAM deployment.  retention for Netflow is minimal to reduce load.  Expect I will bump up when move that filegroup to another drive though.

                        otherwise, we run 7,90, 365.  Depending on trap volume, we are anywhere between 40 and 100G.

                        I also run 3 additional web servers because our current mapping has nearly EVERY network device/interface on a map at some level, which all has to be queried to paint the map.

                        I am only one that uses primary NPM web server and don't monitor the maps.

                         

                        Like I said above though, even 4 SSD SATA drives in raid 10 (so only writing to 2 "heads") made a HUGE difference in performance for us.

                        Good Luck!

                        • Re: Orion NPM Architecture, Speed, and SQL
                          Network_Guru

                          My retention settings are exactly half of yours 'murder'.

                          Another big hitter for performance not yet mentioned is number of elements polled and polling interval.

                          Status polling is mostly innocuous, but statistics polling intervals are the heavy hitter.

                          The majority of our polled elements are interfaces and these are configured to use the default 9 minute interval.

                          For Backbone and Distribution Router interfaces we set this to 5 minutes, and for International WAN links on our PE's (about 2 dozen) I set it to 1 minute intervals.
                          This provides good performance for most pages, except for the large detailed National network map pages which take up to 20 seconds to load.

                          This brings up the additional question - how many elements do you put on your largest map & when do you start nesting maps for optimal performance?

                          Along the same lines, now that the node details page can be broken up into smaller chunks, how many tabs do you use?

                          These are a couple of ways of improving NPM performance, along with optimizing the DB.

                            • Re: Orion NPM Architecture, Speed, and SQL
                              michael stump

                              It's now occurring to me that, if you really want to dig into performance problems with your SQL server, you could always point SAM at it and load up the SQL performance counters. Could be a quick way for non-SQL people to learn where their bottlenecks are.

                               

                              Other thing to note: I mentioned keeping your SQL databases and logs on separate volumes. Make sure they're on separate, high-IOPS LUNs, too, so you're getting the most performance out of your storage system.

                          • Re: Orion NPM Architecture, Speed, and SQL
                            SomeClown

                            Good tips for sure.  We've thought about moving the web front-end for a while now... just haven't gotten to it yet. 

                              • Re: Orion NPM Architecture, Speed, and SQL
                                netlogix

                                One other thing that I found was to make sure your roll up is happening.  I had something break once in the nightly roll-up, and it was a month or two till I noticed it.  So it was an extra month or two of full detail which really slowed things down for me.  So watch c:\ProgramData\Solarwinds\Logs\Orion\swdebugMaintenance.log for "] Error "

                                 

                                familyofcrowes - The biggest downside of SSD is GB/$. SSD seams *really* expensive when compared to 15k disks, but when you look at it as IOPS/$, SSD usually beats 15k, especial if you add IOPS/kwH and/or IOPS/BTU.

                            • Re: Orion NPM Architecture, Speed, and SQL
                              Network_Guru

                              Another issue that can cause poor performance is index fragmentation.

                              Solarwinds has added a warning for this in the events view in the last few releases.
                              It might even be worthwhile adding an e-mail alert for this message until you get this issue resolved.

                               

                              I had my dba follow the instructions here, but am still receiving this message.

                              I opened a case with Solarwinds support - Ticket# 511730

                              I have been asked to run additional SQL scripts, but this involves stopping all Orion services first.
                              This is not something I normally consider, just for troubleshooting, so I have not yet scheduled an outage for this.

                              2 of 2 people found this helpful
                              • Re: Orion NPM Architecture, Speed, and SQL
                                zackm

                                First of all, thanks for the mention

                                 

                                As to your questions:

                                 

                                * What SQL version and architecture are you using (separate database, named instances, etc.)?

                                     - SQL 2008 Standard x64 using MS Failover Clustering

                                     - Named Instances; separate Warehouse and Production DBs

                                * What architecture have you found helps in the speed department?

                                     - We are running physical clusters that have something like 16 cores and 64 GB memory with gigabit connections to the network

                                     - To date, I have not personally seen any performance or speed issues that would warrant us looking at improving performance. Our average setup is 6 polling engines, all at 6,500 elements or more, and we have not had an issue yet. Heck, we haven't even seen a problem with capacity that would make us want to start planning for an issue in the future.

                                 

                                Maybe we're just lucky, but SQL has not been a performance bottleneck for us yet.

                                • Re: Orion NPM Architecture, Speed, and SQL
                                  802jr

                                  We use SQL 2008 Standard on a dedicated sserver. This server is running with 15K SAS 6Gbps Hard Drive, 48GB RAM and 24 cores. Only Solarwinds Named Instances will ever be on this SQL Server.

                                  I do not think we are going to run into issues here. We may start to see issues on what is actually moniter, but hey this is why we have Solarwinds NTA. To figure out which devices need to be upgraded so our backbone and edge better handle traffic better or assign the right QoS policies throughout the network if needed.

                                  • Re: Orion NPM Architecture, Speed, and SQL
                                    fcpsolaradmin

                                    Just uped my sql server to 24GB of ram from 12gb, It helped a bit.  SQL Specs:

                                    sql.PNG

                                     

                                    Web server specs:

                                    polling.PNG

                                    Sometimes the site is quick,but for the most part it is fairly slow

                                     

                                    According to appinsight:

                                     

                                    compilations/recompilations are high (1.06)

                                    work files created/sec are high (65.93)

                                    worktables created/sec are high (19.49)

                                      • Re: Orion NPM Architecture, Speed, and SQL
                                        michael stump

                                        That looks like a lot of memory. Is your db server hosting more than the Orion NPM database? Are they VMs? Might want to look through Resource Monitor on your SQL server to find out how much memory it's using, and adjust accordingly. (I'm always suspicious of VMs with high memory requirements.)

                                         

                                        One other trick to speed up Orion NPM is to remove all of the thwack and community elements from each page. Unless you find them useful, you're making calls to an external site everytime you load a page with those elements enabled. I always get rid of them (but don't worry, I have Thwack opened in a browser tab ) and it does make a difference in page load times.

                                          • Re: Orion NPM Architecture, Speed, and SQL
                                            fcpsolaradmin

                                            Its a physical box dedicated to just solarwinds (I hijacked a old exchange mailbox server ) I did remove the thwack web parts. The part that gets me is sometimes its really fast. Just the other day we were on it during a meeting and myself along with another admin couldn't believe how fast it was!

                                             

                                            I will try to get some solid states ordered, since we spent so much on all the different sw parts, why not spend a few more thousand for new hardware.

                                        • Re: Orion NPM Architecture, Speed, and SQL
                                          byrona

                                          Having fast hardware and storage throughput is also part of our strategy as many others have also suggested.  We also use a separate dedicated web server and also try not to overload the platform system by offloading more polling to dedicated polling engines.  Syslog and traps can also kill your system, I am sure to keep a close eye on how both the syslog/trap rules and capacity are impacting my system.

                                           

                                          We are running our entire architecture on Windows Server 2008 R2 in a completely virtualized environment (VMWare) with a SQL Server 2008 R2 using MS Failover Clustering.  Within the next month we will be moving the database to a dedicated system with a 1.5 TB Solid State RAID array but still virtualized for the flexibility.

                                          • Re: Orion NPM Architecture, Speed, and SQL
                                            boomshine

                                            Hello Everyone,

                                             

                                            Is it better to have a dedicated server for Solarwinds DB, or is it OK if we will put the DB into the cloud and this DB will be joining a lot of virtual machines.

                                             

                                            One factor I'm against the cloud is that the I/O  for my DB will be affected since a lot of VMs also run in our private cloud.

                                            • Re: Orion NPM Architecture, Speed, and SQL
                                              sea_pancake

                                              Even though this thread is long dead it turns up on first page Google so rather than starting a new thread I figured I would add my experience with speed issues around the web console, be *VERY* aware that this was just our experience and you will have to assess against your install but considering how much pain this issue was hopefully it'll save someone their sanity.

                                               

                                              * You can run queries on all nodes on your summary page, we were told it'd slow down performance significently

                                              * Solarwinds constant insistence that 'Server is too slow' is their way of saying they don't know what the issue is. That said more RAM on Web Console server is always good

                                              * Even though 11.5.2 was a massive pain to upgrade to it helped with performance (I know, Im surprised as well)

                                              * Just because Database Maintenance should be performing re-indexing doesn't mean it does, this single command pretty much resolved our performance issue, one of the Solarwinds engineers suggested it. Page loads went from 25 seconds per node to pretty much 0-3 seconds

                                               

                                              Exec sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ("?")'

                                               

                                              * You'll have seen this before but yes definately exclude AV from scanning Solarwinds directories, it does make a difference

                                              * Check any servers for disk paging especially on SQL servers, perfmon with disk IO stats can help identify issues where the DB is waiting on the disk

                                              * You can virtualize all servers at least at our levels (11,000 elements / NPM+NCM+NTA3+VNQM), I understand why Solarwinds don'e like it due to contention for things like disk IO and over-subscription and we were told over and over not to do it but it works without issue for us

                                              * Orion Hubble is always good to check out if you want to know the web console page load breakdown, especially for the SQL queries

                                               

                                              As always YMMV

                                                • Re: Orion NPM Architecture, Speed, and SQL
                                                  dfollis

                                                  I've been fighting this for over a year.  My use of Orion has suffered as a result.  About once a month I spend a day trying to optimize with minimal results.  Some type of a healthcheck/benchmark process would be helpful.  I received the same "it is your hardware" response from support.  Well, I keep throwing vCPUs and RAM at it, and I even have it running on a DAS SSD array to no avail.  I have Exchange and SharePoint running in the same private cloud and those are fine.  We have a great VMWare infrastructure with good SANs and hosts so I'm pretty convinced at this point it is a DB/Config/bad coding issue as you suggest.  Maybe part of this problem is the age of my install.  I'm going on almost 7 years and all of those upgrades/migrations (everything I'm running is latest ver) must have left a lot of detritus in the DB which can't be helping.  I'm about ready to start over, but I don't want to lose my history.

                                                   

                                                  I tried your DB reindex command, seems to have helped a bit but still lagging.  I've rebuilt my DB Server to 2012R2 with SQL 2014, but perhaps time to rebuild the NPM Web/Polling Server also.

                                                   

                                                  It would be nice if SW Support was more willing to assist with performance issues.  I think SAM is the main culprit.  Once that started being extended, things went downhill from there.  As you can see, I do not have a large install:

                                                   

                                                  Orion

                                                   

                                                  Module Name Orion Platform

                                                  Version 2015.1.2

                                                  Service Pack None

                                                  Nodes currently monitored 179

                                                  Total nodes in license 1210

                                                  Volumes currently monitored 190

                                                  Total volumes in license 1210

                                                   

                                                   

                                                  NPM

                                                   

                                                  License Production

                                                  Product Name Network Performance Monitor

                                                  Version 11.5.2

                                                  Service Pack None

                                                  Current number of interfaces 459

                                                  Allowed number of interfaces 500

                                                   

                                                   

                                                  SAM

                                                   

                                                  Product Name Server & Application Monitor

                                                  Version 6.2.1

                                                  Service Pack None

                                                  License Production

                                                  Allowed Number of Component Monitors 700

                                                  Total Number of Component Monitors 376

                                                  Licensed Component Monitors 376

                                                  Unlicensed Component Monitors 0

                                                  Available Component Monitors 324

                                                   

                                                   

                                                  Toolset

                                                   

                                                  Product Name Toolset

                                                  Version 11.0.1

                                                  Service Pack None

                                                  License Production

                                                  Number of Activated Licenses 2

                                                  Nodes in License(s) 10

                                                  Volumes in License(s) 10

                                                  Seats in License(s) 2

                                                  Seats Taken 2

                                                  Seats Available 0

                                                   

                                                   

                                                  IVIM

                                                   

                                                  License 

                                                  Product Name Integrated Virtual Infrastructure Monitor

                                                  Version 2.1.0

                                                  Service Pack None

                                                  Allowed number of sockets 0

                                                  License Type Unknown

                                                   

                                                   

                                                  DPA

                                                   

                                                  Product Name Database Performance Analyzer

                                                  Version 9.2.0

                                                  Service Pack None

                                                   

                                                   

                                                  Packet Analysis Sensors

                                                   

                                                  License . Free Commercial

                                                  Purchase Additional Licenses

                                                  Version 2.0

                                                  Service Pack None

                                                  Server Packet Analysis Sensors currently used 4

                                                  Total QoE Server Sensors 10

                                                  Network Packet Analysis Sensors currently used 0

                                                  Total QoE Network Sensors 1

                                                   

                                                   

                                                   

                                                  NTA

                                                   

                                                  License Production

                                                  Module Name NetFlow Traffic Analyzer

                                                  Version 4.1.1

                                                  Service Pack None

                                                    • Re: Orion NPM Architecture, Speed, and SQL
                                                      familyofcrowes

                                                      I have a very similar issue, although larger install with NPM, SAM, IPAM, NCM, Toolset, NTA, UDT and WPM.  My main server is  32 core, 32Gb hardware beast and my 3 additional pollers are VM and have 4 cores and 8Gb (being upgraded to 10Gb and 6 cores tomorrow).

                                                      We have constant issues, low virtual memory requiring reboots, services stop, you name it.

                                                      I am getting another poller to see if that helps, but support says we look great.

                                                       

                                                      Our DB response time seems very slow.  We also have had this system upgraded since version 8.

                                                       

                                                      A total rebuild would be impossible with the hundreds of custom apps, UnDP's and who knows what.

                                                       

                                                      I will try the reindex as well.

                                                       

                                                      Update any new findings here and maybe we can track down our slowness  :-)

                                                      • Re: Orion NPM Architecture, Speed, and SQL
                                                        sea_pancake

                                                        How much RAM do you have assigned, how many servers for the roles?

                                                         

                                                        One thing we did (But in our case was ultimately made no difference) was to track the issue down ourselves.

                                                         

                                                        * Enable Hubble

                                                        * Confirm where the execution delay is

                                                        * If issue is Database confirm server isn't maxed on the usual (CPU, memory)

                                                        * Drill down to Disk IO (We use Newrelic for this)

                                                        * Drill down to the split up for the IO (Read, write, etc)

                                                        * Look at what is happening at the IO (Is it blocked, queued, in some other wait state?)

                                                         

                                                        The above might help give you at least a localisation for the issue although this did lead us down the wrong pat of thinking our SAN was oversubscribed and Solarwinds continual insistence it was the issue didn't help. I enlisted our Infrastructure team and had them look at the SAN directly and we tracked the IO performance where it leaves the Compute and traverses to the Storage and could see no issue.

                                                         

                                                        Overall in my experience with Solarwinds the following is a *general* trend

                                                         

                                                        * CPU is always high (At least on our servers as we don't have infinite vCPUs)

                                                        * If you are near capacity for RAM you WILL see issues (Probably paging to disk)

                                                        * If you do something stupid (Massive queries, complex views) it will let you, it's flexibility can be it's undoing if you don't understand what it's doing

                                                        * Queries can sometimes go nuts, have seen it happen more so on 11.5.2 where I assume SQL ends up blocked

                                                         

                                                        There is a performance integration function in the Administration area but I haven't touched it, not sure if its worth turning on?

                                                    • Re: Orion NPM Architecture, Speed, and SQL
                                                      byrona

                                                      For those of you experiencing problems and have tried reindexing your database; here are the SQL commands that SolarWinds provided me that I have been using to maintain my database and they have been working extremely well.  The first 3 will delete all Syslog and Traps to help speed up the following commands so if you heavily rely on those you can skip the first 3 commands.  The 3rd one reindexes the database and the last one cleans up a bunch of orphans.  Before you do any of this I would recommend you take a backup of your database.

                                                       

                                                      --SQL Query--
                                                      TRUNCATE TABLE SYSLOG

                                                      --SQL Query--
                                                      TRUNCATE TABLE TRAPS

                                                      --SQL Query--
                                                      TRUNCATE TABLE TRAPVARBINDS

                                                      --SQL Query--
                                                      Exec sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ("?")'

                                                      --SQL Query--
                                                      SET NOCOUNT ON;
                                                      DECLARE @tablename VARCHAR(128);
                                                      DECLARE @execstr VARCHAR(255);
                                                      DECLARE @objectid INT;
                                                      DECLARE @indexid INT;
                                                      DECLARE @frag decimal;
                                                      DECLARE @maxfrag decimal;
                                                      -- Decide on the maximum fragmentation to allow for.
                                                      SELECT @maxfrag = 1.0;
                                                      -- Declare a cursor.
                                                      DECLARE tables CURSOR FOR
                                                      SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
                                                      +'.'+CAST(TABLE_NAME AS VARCHAR(100))
                                                      AS Table_Name
                                                      FROM INFORMATION_SCHEMA.TABLES
                                                      WHERE TABLE_TYPE = 'BASE TABLE';
                                                      -- Create the table.
                                                      CREATE TABLE #fraglist (
                                                      ObjectName CHAR(255),
                                                      ObjectId INT,
                                                      IndexName CHAR(255),
                                                      IndexId INT,
                                                      Lvl INT,
                                                      CountPages INT,
                                                      CountRows INT,
                                                      MinRecSize INT,
                                                      MaxRecSize INT,
                                                      AvgRecSize INT,
                                                      ForRecCount INT,
                                                      Extents INT,
                                                      ExtentSwitches INT,
                                                      AvgFreeBytes INT,
                                                      AvgPageDensity INT,
                                                      ScanDensity decimal,
                                                      BestCount INT,
                                                      ActualCount INT,
                                                      LogicalFrag decimal,
                                                      ExtentFrag decimal);
                                                      -- Open the cursor.
                                                      OPEN tables;
                                                      -- Loop through all the tables in the database.
                                                      FETCH NEXT
                                                      FROM tables
                                                      INTO @tablename;
                                                      WHILE @@FETCH_STATUS = 0
                                                      BEGIN;
                                                      -- Do the showcontig of all indexes of the table
                                                      INSERT INTO #fraglist
                                                      EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
                                                      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
                                                      FETCH NEXT
                                                      FROM tables
                                                      INTO @tablename;
                                                      END;
                                                      -- Close and deallocate the cursor.
                                                      CLOSE tables;
                                                      DEALLOCATE tables;
                                                      -- Declare the cursor for the list of indexes to be defragged.
                                                      DECLARE indexes CURSOR FOR
                                                      SELECT ObjectName, ObjectId, IndexId, LogicalFrag
                                                      FROM #fraglist
                                                      WHERE LogicalFrag >= @maxfrag
                                                      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
                                                      -- Open the cursor.
                                                      OPEN indexes;
                                                      -- Loop through the indexes.
                                                      FETCH NEXT
                                                      FROM indexes
                                                      INTO @tablename, @objectid, @indexid, @frag;
                                                      WHILE @@FETCH_STATUS = 0
                                                      BEGIN;
                                                      PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
                                                      ' + RTRIM(@indexid) + ') - fragmentation currently '
                                                      + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
                                                      SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
                                                      ' + RTRIM(@indexid) + ')';
                                                      EXEC (@execstr);
                                                      FETCH NEXT
                                                      FROM indexes
                                                      INTO @tablename, @objectid, @indexid, @frag;
                                                      END;
                                                      -- Close and deallocate the cursor.
                                                      CLOSE indexes;
                                                      DEALLOCATE indexes;
                                                      -- Delete the temporary table.
                                                      DROP TABLE #fraglist;
                                                      GO

                                                      • Re: Orion NPM Architecture, Speed, and SQL
                                                        juanel

                                                        Out of interest what's the largest configuration of SW's people have got. We have performance issues on a regular basis and it always appears to be around the Main poller services and a restart resolves this.

                                                         

                                                        Our platform is made up of the following:

                                                         

                                                        2-Node SQL cluster (Physical Box, Windows 2012, 2.20Ghz (2 sockets, 16 cores, 32 logical processes), 128GB Mem - SQL Limited at 112GB)

                                                        1 Primary Poller, (Physical Box, Win 2012,  2.00Ghz (2 sockets, 12 cores, 24 logical processes), 128GB Mem)

                                                        8 additional Pollers (Made up of Physical boxes spec'd as above and numerous VM's)

                                                        2 Web Servers

                                                        DPA (installled on the Primary Poller)

                                                        SRM

                                                        VIM.