Anyone pre-cache?
-
I had posted this question on another board and have basically gotten no responses...someone please throw me a bone!!
**************************************************************
I am relatively new at my current job and had a situation come up last week...
Two weekends ago, we added some space to our production server. This required a reboot. Everything was fine until Monday when the users started using the database and performance was slow. By the end of the day, performance was much better. Part of the reason for the slow down was that SQL Server had to re-cache the queries that are run during the course of a normal business day since what was cached was flushed out when the server was rebooted. I will admit that a large part of the slowdown is not only that things were lost from the cache but that the queries and indexes are poorly designed. I am currently working on this issue but have only just begun and have not made a significant dent because of the sheer volume of queries.
So, the question was asked if there is a way to have a script of queries that can be run in the event of a reboot that will pre-cache things so that when the users start using the database, they won't experience such a bad slowdown.
Now, I am certain that I can do this if I use profiler to capture the most frequently run queries and I can be available to run them when the server is rebooted. My question is...Is pre-caching a normal thing to do? Does anyone else have to do this? I have not had to do this at any other jobs that I have had.
Thanks!!!
--Lori
-
If you have a steady enough workload, i.e., not too many ad hoc queries, then this will work. It typically hasn't been an issue with my systems, but we have done this in the past. Profiler is a good way to go, but in one system we just did a select * from to load current inventory into the data cache. Since there was lots of older inventory on the system, this helped initial performance for the first few people.
-
Part of my test plans for development includes unit testing my stored procedures with known parameters and comparing the expected results with the actual results.
OK, in a production environment you can't be running update/delete/insert tests unless you REALLY know what you are doing but you can run all the stored procedures that do SELECTs.
I keep a script file for this very purpose. All it has in it is
exec dbo.usp_getCustomer @var1, @var2 ..etc
exec dbo.usp_getProduct @var1, @var2 ..etc.
and so on.
Obviously on a production server I specify arguments that are going to return very few records per procedure.
-
I had to do this for a user that used to be the first one in the office every morning and she ran a query that only 2-3 people used each day. Because we had 200-300 users, this query wasn't high on the list to allocate resources to fix just for the single user.
-
Once in a while we need to perform (due to the slowness) a non-scheduled SQL Server reboot during business hours, but we never experienced a problem right after that. We run Profiler all the time and this is a good hint on what is going on with your queries. Also I would suggest to run perf. counters, store result in a table and analyze it later; so it might not only your queries which adds to the known issues.
-
All that said, you are already exploring the indexes. I would also suggest looking at the IO subsystem (hard drives, etc) to see if you can rebalance things to squeeze extra performance. Also, make sure you have enough hardware to support the system as is.
I currently have one production system with 1GB of ram working 30+ GBs of database size across 54 databases hit by 100+ applications and reports constantly while moving in data from call center switches. We see some speed issues but most times tweaking the indexes fixes as well as optimizing the queries themselves. Very few complaints of speed except when a specific reprot is ran for a large range which we are aware of. Even after reboots very few items take more than a second or so to return.
However, precaching may help keep your speeds up on a reboot and might be worth at least exploring for you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply