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.
