Solved

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

Posted on 2004-03-23
5
212 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now