| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-07-23 : 07:38:59
|
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
11752 Posts |
Posted - 2007-07-23 : 09:26:09
|
| nice._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
mrDBA
Starting Member
1 Post |
Posted - 2007-08-03 : 15:55:15
|
| 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. |
|
|
|
RandallE
Starting Member
2 Posts |
Posted - 2007-08-06 : 15:36:09
|
| 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, |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-06 : 15:44:22
|
| well i'd say that that's good since you have practicaly the whole table in memory._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
RandallE
Starting Member
2 Posts |
Posted - 2007-08-06 : 16:19:35
|
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? |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-08-06 : 16:58:25
|
| 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. |
|
|
|
henrik staun poulsen
Starting Member
4 Posts |
Posted - 2007-08-08 : 02:22:27
|
| 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_MBFROM sys.dm_os_buffer_descriptorsGROUP BY db_name(database_id) ,database_idORDER BY Buffered_Page_Count DESC |
|
|
|
urdba
Starting Member
5 Posts |
Posted - 2008-03-18 : 17:03:02
|
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. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2008-03-19 : 08:20:19
|
| 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. |
|
|
|
|
Article: What Data is in SQL Server's Memory?