Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql2008, buffer pool usage at db level

Posted on 2012-03-14
3
Medium Priority
?
329 Views
Last Modified: 2012-03-20
I know the query below will display current buffer pool usage at each db instance in SQL. But my questions is how do I drop those buffer pool let says from [DB_1] and let only those size  available in [DB_2]. The reasons why is because [DB_1] is no longer active in production but must be make available in SQL instance.

SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [database_id], [is_modified];
GO
0
Comment
Question by:motioneye
[X]
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
  • 2
3 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 600 total points
ID: 37721857
The easyest way I can think of is to restart the SQL Server service and if that database is not used anymore the buffer pool should be low.
In SQL 2008 you have a "Resource Governor" that could help you do more detailed SQL resource management.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37721879
When that cache isn't used anymore it will be replaced by new pages that are demanded on that moment ... indifferent for what db.
0
 
LVL 25

Accepted Solution

by:
jogos earned 1400 total points
ID: 37722009
Don't do this on production
<<The easyest way I can think of is to restart the SQL Server service and if that database is not used anymore the buffer pool should be low.>>
DROPCLEANBUFFERS (after checkpoin) is easyer
http://msdn.microsoft.com/en-us/library/ms187762.aspx

Warning : That is for all databases and the db you want to give priority will get punished as hard as the others.

Check the "Resource Governor" suggestion from first comment to have some management.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

618 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