My Production environment has been running a little slow lately, and today it really became sluggish. For example, I have another DB that has a few stored procedures in it that query the production database. That sproc usually takes about 4-5 seconds to run before spitting out the results of the query (it is only doing SELECT statements with some joins…nothing too resource intensive at all). When I run that sproc today it is taking upwards of 30-40 seconds to run.
I can’t find anything that is causing it to be so slow, and I wasn’t sure if it was DB related or hardware related, so I did what any sensible person would do…I made a backup of our production database, and then restored it to the same SQL server w/a different DB name and filename. The path of the DB and log files stayed the same though. This way it is as close to identical as possible.
I then duplicated my sproc and changed all references to the production DB to point to the copy of it that I just restored. I was expecting the same abysmal speeds as the sproc and DB are identical, with only the DB name changing, but instead it finished in 4 seconds.
Then I realized that the new DB is not being utilized…so I took my Pilot AppServer and pointed it at the newly restored DB and tried again…same results in 4 seconds flat.
I’ve looked at table sizes, free space, checking for any long running transactions, etc. Nothing at all stood out. Even on the Epicor side there were no stale tasks consuming CPU/Memory. Heck, the CPU/MEM of the server itself is quite low at around 7% of CPU utilization and barely 40GB (out of 128GB) of memory used. The NIC traffic was only pushing out a couple dozen Kb as well.
I looked to see if there were any unusual schema changes, of which there were none.
And with all of that being said, I would expect that the cloned version of the DB would behave the same was as the original, but it does not.
I didn’, no. One of the main reasons being that I wasn’t sure if would actually do anything. I mean, I didn’t rebuild the indexes on the cloned DB I connected…(Maybe it does that on the backend during the backup/restore process?)
When was the last time that SQL server instance was restarted? You may have some bad query plans in the procedure cache. The reason you see an improvement in the new db on the same server is that there will nothing in there initially. Hope that makes sense. The other thing is that if the server has not been restarted the temp db will have a lot of artifacts in it from the prod db which might also be adding to issues, restarting SQL clears the temp db.
After restarting SQL you may see a bit of a performance dip until all the caches have been repopulated, but based on what you said I think it might help, at worst you at least know all the caches are fresh.
A final thought are there any differences the database options of the prod and the restored database?
One of the other common issues I’ve seen and this probably is not related, but thought I would mention is having the auto grow settings set with a too small a % over time as the database grows it takes longer and longer each growth event occurs, and this can cause intermittant slowness.
