April 9, 2009 at 4:54 am
hi
can any one tell me how database level caching can be implemented in sql server.
Nothing Is Impossible
April 9, 2009 at 6:26 am
No, that't not possible if you are asking you can cache a single database. Query optimizer will chose the frequently access objects. More the memory the more objects it can cache. But you cant tell query optimizer to cache a particular database/table.
April 9, 2009 at 8:32 am
Vivek (4/9/2009)
hican any one tell me how database level caching can be implemented in sql server.
You can't cache a database..Caching is at the table level.
Check out the below link
http://www.mssqltips.com/tip.asp?tip=1317
Can you tell us what is your actual requirement?? and what do you want to cache.
April 9, 2009 at 8:45 am
Vijaya Kadiyala (4/9/2009)
Vivek (4/9/2009)
hican any one tell me how database level caching can be implemented in sql server.
You can't cache a database..Caching is at the table level.
Check out the below link
As the article you refer to correctly says, this functionality has been removed in SQL 2005.
So even on a table level you have to rely on SQL Servers internal memory management.
[font="Verdana"]Markus Bohse[/font]
April 9, 2009 at 11:00 pm
Hi All
Thanks for the replies
My problem is this I have made an procedure which fetch data from a table which contain records in lakhs .Now should I use DBCC pintable command in begin to keep the record of the table in the memory and in the end unchache it by using the command DBCC UNPINTABLE command?
Is that a useful solution ? If there is any other solution then can you please tell me.
If i dont UNPIN it then will stay there for ever?
Is DBCC comand is also applicable in SQL SERVER 2005?
Thanks in advance
Vivek
Nothing Is Impossible
April 9, 2009 at 11:10 pm
Vivek (4/9/2009)
My problem is this I have made an procedure which fetch data from a table which contain records in lakhs
that is 1,00,000 for everyone to understand.
If you have proper indexes on the base table and if these indexes are used in your queries that shouldn't be a problem. You dont have to use the DBCC command which will not give you any performance benifit at all.
From BOL:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.
If you are still not able to work it out. Post your table definition and also the sp that is running against this base table in a new post, we would be able to help you out.
April 9, 2009 at 11:20 pm
Indexes are not implemented in the database by the client so there are no indexes implemented on the database.
Nothing Is Impossible
April 9, 2009 at 11:27 pm
Vivek (4/9/2009)
Indexes are not implemented in the database by the client so there are no indexes implemented on the database.
And how many rows your return!!?? Why doesn't your client implement any indexing? how bad idea is that?
April 10, 2009 at 1:05 am
Vivek (4/9/2009)
Indexes are not implemented in the database by the client so there are no indexes implemented on the database.
Why?????
Will millions of rows in the table and no indexes, you are asking for dead-slow queries. Post the table, post the queries and we'll recommend some good indexes for you.
Without adding indexes, there's no practical way to improve performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply