10 Replies Latest reply on Apr 4, 2009 5:56 PM by neilmborilla

    SQL database questions

    worto03

      Hia,


       I've been using solarwinds for a while now but have never really needed to mess with the database, our NPM is getting slower & slower so I asked our SQL guy to have a look at the database and he has said.


      "'Your database is actually only 2.5 Gb. The files are bigger but are half empty. There are only a few tables of any significant size but it would appear that no index rebuilds are being done as they are very badly fragmented which can kill performance. I need to implement index rebuilds before I can go much further because although it may not completely cure the problem, if it isn't done we won't be able to see the wood for the trees. I will also need to know when the best time is to run the job (sometime at night?), which will be a scheduled task run weekly.


      The other thing which jumped out immediately was one large table ("NPM_ScheduledJobJournal" with 1.7 million rows) with no indexes at all. I would like to add at least a clustering index to this. Would that be OK? Other than that the database looks fairly well structured."


      and after I asked him if doing this would effect the 24\7 running of the application he said


      "When the index rebuild runs it will lock each table in turn as it is rebuilt. We usually schedule rebuilds at a time when there is little server activity, but if you're running 24x7 that won't be possible. What should happen is that any updates from the application will queue while the table is locked, but with the size of some of the tables in this database (there are two with over 6 million rows which actually comprise the majority of the database) each one could take a couple of minutes. If the application tries to update that table while it is being rebuilt, whether it will succeed or fail depends on how long a time-out period the application has set. The default is 5 minutes and if that is what it uses then we shouldn't see any issues, but it is entirely under the control of the application and if it has set a much shorter time out then we will see errors. There isn't really any way round this with the edition of SQL Server you are using (SQL Server 2005 Enterprise Edition can do on-line index rebuilds, but it comes at a cost).


      I wonder if it is worth you running this by the application vendor, as I'm sure they must come across this issue often if their software is designed for 24x7 operation."


       


       Sorry if this turned out to be a rather long post but has anyone here had similar problems and do we know the timeout for the application when the tables are locked?


      thanks for bothering to read this one!


      Cheers,


      worto.

        • Re: SQL database questions
          mark wiggans

          You didn't mention which version of Orion you have or the SQL version? Substantial DB changes have taken place in more recent versions. You may consider moving to a SQL Server running Standard or Enterprise edition, as that will not limit your database size as much. To reduce the current size of your database, you can try the following:


          Open the Orion System Manager, and go to File -> Orion NPM Settings. Under the database tab, you can choose how long to retain your statistics for - reduce these settings down to reduce the amount of polling and statistics data being retained.

          On the same tab, you can start off a "Nightly maintenance" - this will go through the data in your tables and 'groom out' the data you no longer want to retain. The nightly maintenance only summarizes the data, which in turn leaves behind blank space. Once this has completed, open the Database Manager tool (under database Utilities). Connect to your database server, right click on your database, and choose "Compact Database". This should reduce down the size of your database for you

            • Re: SQL database questions
              worto03

              Hi & thanks for the reply,


              Sorry I missed off some info :-


              we are on Orion 8.5.1 & once we are happy with the database we will be upgrading to 9


              The SQL version we use is SQL Server 2000


               Our SQL guy doesn't seem to think the size of the database is a problem as long as we add these indexes he's talking about, will adding these indexes and other things he has mentioned effect the solarwinds application in an adverse way at all?


               Thanks,


              Steve.

                • Re: SQL database questions
                  mark wiggans

                   Did the DBA mention which tables are fragmented? I would recommend doing a Compact on these tables individually.  Doing the entire DB at once might take a while, so for starters take the tables mentioned and perform a manual Compact/Rebuild Indexes on them.

                  I'll have to get back with you about the possible issues if your DBA adds these indexes and if this can be recommended.

                    • Re: SQL database questions
                      worto03

                      'Did the DBA mention which tables are fragmented?'


                      no he didn't but I can find out, he knows a lot about SQL but nothing about solarwinds so I'm just trying to check that his suggestions won't break anything application wise.  If you do have chance to check about the indexes that'll be great, let me know if you don't have time & I'll raise a ticket instead.


                      I don't really want to be doing reindexing once a week if it stops solarwinds wriitng to the tables while we are doing it so I could do with knowing how solarwinds reacts to locked tables, is the timeout 5mins which is default or a diferent value.


                      thanks for your help,


                      Steve.

                • Re: SQL database questions
                  casey.schmit

                  The other thing which jumped out immediately was one large table ("NPM_ScheduledJobJournal" with 1.7 million rows) with no indexes at all. I would like to add at least a clustering index to this. Would that be OK? Other than that the database looks fairly well structured."
                   

                  You can go ahead and delete the rows in this table.  There is an issue that this table will not be cleaned up like it should that will be resolved in the next release of APM.  The index shouldn't hurt, but we shouldn't be doing much beyond inserting into that table. 

                  As for other indexes, please let us know what your DBA suggests and we'll take a look to see if those will cause any problems...

                    • Re: SQL database questions
                      worto03

                      Hi,

                      Thanks for that he has siad he will setup a job to clear down that table (NPM_ScheduledJobJournal) every night instead of adding an index if the content is not used.

                      He also said that there are no other indexes he wants to add but he wanted to know :-

                      "how we should carry out index rebuilds (on the existing indexes) if the application is running 24x7. Do they have any experience of this with other customers? Is it likely to cause a problem if we do the rebuilds while the application is running?"

                      Is this something you know about?

                      Many-Thanks,

                      Steve.

                        • Re: SQL database questions
                          worto03

                          We have done the index rebuilds which took 20mins and we did have data loss in solarwinds while it was being carried out, is there any way round this as our DBA has suggested that we run the index rebuild job once a week?


                          do other people just not bother doing this or do you suffer with gaps in the data every now and again?


                          cheers,


                          Steve.

                            • Re: SQL database questions

                              I'm a DBA looking to assist our SolarWindsNPM admin in being able to reindex the NPM database without taking it offline....our company uses it to capture system uptime, in addition to standard functionality.

                              I'm searching for a recommended way to deal with this as we're currently getting errors such as the following when running SQL 2005 Maintenance Plans:

                              ----------

                              Failed:(-1073548784) Executing the query "ALTER INDEX [IX_NetFlowDetail_DestIPSort] ON [dbo].[NetFlowDetail_121_1375199] REORGANIZE WITH ( LOB_COMPACTION = ON )
                              " failed with the following error: "Cannot find the object "dbo.NetFlowDetail_121_1375199" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
                              --------------

                              Between the time the maintenance plan starts and finishes with the NPM database, it's creating and dropping temp tables which SQL Server is attempting to reindex and failing...

                              What's the best practice for reindexing NPM databases?