Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajemessage
Starting Member
17 Posts |
 Posted - 2013-12-13 : 01:38:25
|
| Hi,(sqlsever 2005) I noticed that when sp(stored procs) are run second time they get data from buffer so they are run fast on second time.on the other side i noticed that database never gets bigger than 2.5 gb, and the ram on system is 4 gb.there is only one situation , when server is restarted it flushs the buffer and cahce.(db does not get insert update when it is online,we transfer data at offpick time and it takes quite less time)above was the only situation when sp will become slow.q1) So please tel me , what i am thinking is correct or not , that is my sp will be slow for first time and then they will be alywas fast and all data will be in memory. untill unless server is restarted.q2) Is there any technique to bring all tabels data in buffer manually so that sp can become fast even on first time , similar to the execution of second time? yours sincerley |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-13 : 12:58:11
|
| Yes things are slower when they aren't in cache as memory access is faster that disk access.If you want it faster even on the first time, then you need to make your disks faster. You could setup a job that runs on startup that starts loading things in cache, but I don't think this is necessary if you have adequate hardware.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
rajemessage
Starting Member
17 Posts |
Posted - 2013-12-14 : 08:36:45
|
| Q1) Is buffering is done for each user or once the table is buffered then it can be used by other users also.Q2) I would like to know one opposite thing, is it possible that buffering of few tables or a database can be stoped. So each request should get it from hard disk.yours sincerly |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-16 : 11:44:46
|
| 1. It's at the query level.2. You can drop the cache, but I'm not sure it's possible for a few tables.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-12-17 : 09:20:10
|
quote:
Originally posted by tkizer
1. It's at the query level.
There is buffering for both queries and data. With queries, it is not the result that is cached, it is the execution plan.SQL Server will check if the execution plan exists in cache when you run a query. If it is, it will use it from the cache (not always in the case, certain events can trigger a recompile). When it creates the execution plan/compiles a query, it will save it in the cache. So a little time can be saved when you run a query or sp the second time because of this.When it executes the query, it is a similar idea for data. Data is stored in chunks called pages, if the required pages are not in the cache, SQL has to fetch them from disk first and bring the required pages in the cache. This is also why queries can be faster when you run them a second time. Also if you run a different query on the same table, time may be saved if the data is already in the cache as a result of the first query.There are queries to see which queries are currently in your cache. And there are queries to see how many pages from indexes or tables are currently in your cache. Also, the cache for execution plans and for data storage are separate. There are queries to see how large each of these are and how much free space they have. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-12-17 : 09:30:31
|
quote:
q2) Is there any technique to bring all tabels data in buffer manually so that sp can become fast even on first time , similar to the execution of second time? yours sincerley
It's an interesting idea. I've never tried it. But It could be tricky. Even if you did a select * from every table, what about your indexes?And if you brought all the data into your cache, there is still the issue that stored procedures execution plans would not be in cache. Perhaps you could run everyone of them? |
|
|
|
|
|
|
|
|
is it possible to keep all tables data in buffer