Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

URGENT: configure/optimize SQL Server w/read-only data

Posted on 2004-03-23
5
215 Views
Last Modified: 2008-03-06

we have about 200MB of data in our database that is only used for reporting (it isn't updated).  we would like to configure SQL Server to take advantage of that.  since it's so small i imagine that SQL Server could read all of the tables into memory and never go back to disk and never worry about locking

anyone know how to configure SQL Server to do this?
0
Comment
Question by:spathiphylum
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10660036
If the data is read that frequently thats what will happen anyway...

let the server  decide what its best to keep in memory at any one time...

0
 
LVL 34

Expert Comment

by:arbert
ID: 10660282
Agree with lowfatspread.    You can issue a DBCC PINTABLE statement to put the tables into memory.  However, this really isn't a good idea.

How much memory is in the server.  You have the database properties switched to read-only right?
0
 

Author Comment

by:spathiphylum
ID: 10660589
there is 1GB of memory.   what is DBCC PINTABLE?  why isn't it a good idea?  

if i change the database to read-only, will this tell SQL Server that it can read the whole thing into memory and it doesn't need to worry about locking?  is there microsoft documentation that support that?
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 175 total points
ID: 10660732
No, if you aren't making changes to the data, you can change the properties to READ-ONLY and sql server won't take locks on the data that it normally would.

Pntable usually isn't a good idea because it can hog all the memory the server has (and the times that I've used it, we had problems unpinning the table and it made the server unstable).  Just because you have a gig of memory doesn't mean you can fit 200mb of data into it.

Have you looked at your server workload to see if/where you're having problems?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 325 total points
ID: 10660735
read-only doesn't tell it to read the databse into memory it just specifies that the data in the database can't be modified

so it shouldn't need to worry about locks...

with 1GB of memory you want SQL Server to utilise one fifth of this for the table data in one of your databases....

so the other 800 mb will be used for the

OS
SQL Server itself
The Stored Procedure/SQL Statement Cache
TEMPDB  ( SORT SPACE)

Pinttable from BOL
Remarks
DBCC PINTABLE does not cause the table to be read into memory. As the pages from the table are read into the buffer cache by normal Transact-SQL statements, they are marked as pinned pages. SQL Server does not flush pinned pages when it needs space to read in a new page. SQL Server still logs updates to the page and, if necessary, writes the updated page back to disk. SQL Server does, however, keep a copy of the page available in the buffer cache until the table is unpinned with the DBCC UNPINTABLE statement.

DBCC PINTABLE is best used to keep small, frequently referenced tables in memory. The pages for the small table are read into memory one time, then all future references to their data do not require a disk read.



Caution  Although DBCC PINTABLE can provide performance improvements, it must be used with care. If a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately. If a table larger than the buffer cache is pinned, it can fill the entire buffer cache. A member of the sysadmin fixed server role must shut down SQL Server, restart SQL Server, and then unpin the table. Pinning too many tables can cause the same problems as pinning a table larger than the buffer cache.

hth
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question