Data and Procedure Cache...

Question

Data and Procedure Cache...

PiMané

SSChampion

Points: 13965
More actions
October 19, 2009 at 11:39 am

#227269

Hi,
I've a table that has lots of records and it's used many times.
Is it possible to "tell" SQL SERVER to put all that table's data in it's data cache?
In SQL 7 there was DBCC PINTABLE but in 2005 this was deprecated.
Thanks,
Pedro

If you need to work better, try working less...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

No there isn't a way to currently do this in SQL Server 2005 or 2008. Your only way to attempt it, and it isn't guaranteed, would be to setup a job in SQL Agent that is called every minute doing a SELECT * from the table, which would keep its use count high and minimize the decrement by the clock hand sweeps which result in it being removed from cache. A large enough hit to the cache could still cause it to be flushed though.

PiMané SSChampion Points: 13965 More actions · Answer 2

Also thought of, since the data on that table doesn't change very often and we know when it does (data from a web service call by us), building a CLR with the data in a List, Collection, what ever, and have a method so we could use it like a table.

But probably it's best o let SQL do the management itself..

Thanks,

Pedro

If you need to work better, try working less...

Ken McKelvey SSCoach Points: 18941 More actions · Answer 3

You could also look at using ASP caching:

http://msdn.microsoft.com/en-us/library/ms178604.aspx

etc

PiMané SSChampion Points: 13965 More actions · Answer 4

Thanks, we'll look into that...

What we really are waiting for is MS Velocity... http://msdn.microsoft.com/en-us/data/cc655792.aspx

When it'll come out?!?! no one knows just yet... MS is always saying "tomorrow" 😀

Pedro

If you need to work better, try working less...