I have been getting a message in errorlog stating that "02:00000:00123:2005/01/12 11:46:24.60 server Increase the config parameter 'number of open indexes' to avoid descriptor reuse. Reuse may result in performance degradation."
Today I changed it after checking with sp_countmetadata.
Another error message was "03:00000:00153:2005/12/02 09:29:13.71 server Increase the config parameter 'number of open databases' to avoid descriptor reuse. Reuse may result in performance degradation."
and I have changed config value from 12 to 15 as our total dbs number is 13, including sys dbs.
Problem is that we have been facing performance problem in one of our overnight process which takes abt 3-3.5 hours for completion. But for one last month or so, this overnight process is behaving quite abnormally as sometimes it gets complted within 3-3.5 hrs and sometimes it takes like 8-9 hours with same load on the database server. There could be slight change on the load due to some variables but we a difference of 5 hours in not explainable.
My question here is, whether the first or/and second message can be a culprit for slow database operations ?
What other things I can look to improve database performance. Real problem is that I can not work on optimizing Database objects...I have time only to work on database server configuration, locking schemes..i mean small changes which can be done quickly.
These messages won't help performance, obviously, but they are most likely not the main culprits for the slow performance.
Look at the queries that run in your overnight job, add some timings and find out where it is spending most of its time. Then look at the query plans that are generated during those long requests. Once you know where most time is being spent you can try to find ways to improve performance.