SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: What Data is in SQL Server's Memory?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/23/2007 :  07:38:59  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

SQL Server memory is primarily used to store data (buffer) and query plans (cache). In this article I'll show how much memory is allocated to the data buffer (or RAM). I'll explain how to determine what tables and indexes are in the buffer memory of your server.




Read What Data is in SQL Server's Memory?

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 07/23/2007 :  09:26:09  Show Profile  Visit spirit1's Homepage  Reply with Quote
nice.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

mrDBA
Starting Member

1 Posts

Posted - 08/03/2007 :  15:55:15  Show Profile  Reply with Quote
Good info. I've been needing to know how our server's memory is allocated among the databases and this article is a great start. Just a small note regarding the queries in the article. If anyone uses the queries in the article and run across arithmetic overflows, just change the count(*) to count_big(*) and all should be well.

A good start on sql mem usage and I'm curiously waiting on the next articles!




He is no fool that gives that which he cannot hope to keep to gain that which he can never lose.
Go to Top of Page

RandallE
Starting Member

2 Posts

Posted - 08/06/2007 :  15:36:09  Show Profile  Reply with Quote
Apologies for what's probably an obvious question.......

Should red flags pop up when you see a the entire clustered index of a large table show up in the buffer? Is it time for additional indexes? Or a candidate for clustering on a different column(s)?

thx,
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/06/2007 :  15:44:22  Show Profile  Visit spirit1's Homepage  Reply with Quote
well i'd say that that's good since you have practicaly the whole table in memory.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

RandallE
Starting Member

2 Posts

Posted - 08/06/2007 :  16:19:35  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

well i'd say that that's good since you have practicaly the whole table in memory.





But that signals a tablescan, no? Shouldn't proper indexing pull just tiny slices of the data pages into memory?
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 08/06/2007 :  16:58:25  Show Profile  Visit graz's Homepage  Reply with Quote
It doesn't signal a table scan. It just says that SQL Server had enough memory to keep all (or almost all) or the table in memory. SQL Server will determine what pages it's most efficient to keep in memory. In your case it happened to be most of a table.

It *may* have ended up in memory because of a table scan but just being in memory doesn't imply a table scan.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

henrik staun poulsen
Starting Member

4 Posts

Posted - 08/08/2007 :  02:22:27  Show Profile  Visit henrik staun poulsen's Homepage  Reply with Quote
SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count_big(*)AS Buffered_Page_Count,
count_big(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC
Go to Top of Page

urdba
Starting Member

5 Posts

Posted - 03/18/2008 :  17:03:02  Show Profile  Reply with Quote
quote:
Originally posted by AskSQLTeam

<p>SQL Server memory is primarily used to store data (buffer) and query plans (cache). In this article I'll show how much memory is allocated to the data buffer (or RAM). I'll explain how to determine what tables and indexes are in the buffer memory of your server.</p>


Read <a href="/article/what-data-is-in-sql-server-memory">What Data is in SQL Server's Memory?</a>



hi,

Read all three articles about data buffer, procedure cache, and parametrization. Very good information for SS 2005.

Do you have similar queries/views for how to look at info in data buffer and procedure cache for SQL Server 2000?

Thanks.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 03/19/2008 :  08:20:19  Show Profile  Visit graz's Homepage  Reply with Quote
You may be able to get some of the same information from DBCC MEMORYSTATUS (http://support.microsoft.com/kb/271624) but it won't be nearly as good. These DMV's are one of the cool new features of SQL Server 2005.





=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000