Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access denied running PowerPivot -SQL Server 2014 on Windows Server 2012 10 30
SQL Server Designer 19 43
SQL Help 27 45
hyperlink data type in SQL 3 28
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

810 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