1

Topic: How to force a SQL Server to tighten the data in a cache?

There is a server, on it it is a lot of RAM.
SQL Server process uses only 5 % from the RAM.
How to force it to use it is more than RAM?

2

Re: How to force a SQL Server to tighten the data in a cache?

p-server wrote:

SQL Server Process uses only 5 % from the RAM.

Where looked, in ? It does not show.
Look counters in .

3

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
Item 1 to look not in taskmgr, and in resmon/perfmon
Item 2 to look how many costs sp_configure ' max server memory'

4

Re: How to force a SQL Server to tighten the data in a cache?

alexeyvg wrote:

it is passed...
Where looked, in ? It does not show.
Look counters in .

I look Working Set for process sqservr.

5

Re: How to force a SQL Server to tighten the data in a cache?

In .

6

Re: How to force a SQL Server to tighten the data in a cache?

TaPaK wrote:

item 2 to look how many costs sp_configure ' max server memory '

There are 150 Gb, eats 4 Gb.
On the server more 250 , sqlServer unique application.

7

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
Screenshots with resmon + sp_configure ' max server memory'
Your gastronomic descriptions are not interesting

8

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
So can to it more and it is not necessary? Drained in all "the operative data" and chews them without hurrying up...
Basis what on the size?

9

Re: How to force a SQL Server to tighten the data in a cache?

  - Eh wrote:

the p-server;
So can to it more and it is not necessary? Drained in all "the operative data" and chews them without hurrying up...
Basis what on the size?

5

10

Re: How to force a SQL Server to tighten the data in a cache?

TaPaK wrote:

the p-server;
Screenshots with resmon + sp_configure ' max server memory'
Your gastronomic descriptions are not interesting

Screenshots will not be. It is necessary to take a word.

11

Re: How to force a SQL Server to tighten the data in a cache?

Again crew of Kozlovyh?

12

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
And how many from them are necessary in a permanent job to store them in a cache? So at me in garage too tools three cabinets and a small box, but more often I use a cross screw-driver under a head 2 and a key on 10...

13

Re: How to force a SQL Server to tighten the data in a cache?

Interesting that at process SQLServer virtual bytes 7 * 10^11
While I try to understand that it means.

14

Re: How to force a SQL Server to tighten the data in a cache?

  - Eh wrote:

the p-server;
And how many from them are necessary in a permanent job to store them in a cache? So at me in garage too tools three cabinets and a small box, but more often I use a cross screw-driver under a head 2 and a key on 10...

And tell, how it is possible to look at what data ?
Really not too it is a lot of probably operative data.

15

Re: How to force a SQL Server to tighten the data in a cache?

Instead of 32 the bit version of the server costs, hour?

16

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
It is possible to look at statistics index buffer cache hit ratio. The it will be closer to 100 %, the the cache is more effectively used. And if on that volume that  ate, the index will be 98-99 % then, I whine  memory size under a cache of the data and it is not required for to add there - there is simply nothing... All data necessary for operation and so already in storage...

17

Re: How to force a SQL Server to tighten the data in a cache?

iii2 wrote:

Instead of 32 the bit version of the server costs, hour?

x64 2012EE

18

Re: How to force a SQL Server to tighten the data in a cache?

  - Eh wrote:

the p-server;
It is possible to look at statistics index buffer cache hit ratio. The it will be closer to 100 %, the the cache is more effectively used. And if on that volume that  ate, the index will be 98-99 % then, I whine  memory size under a cache of the data and it is not required for to add there - there is simply nothing... All data necessary for operation and so already in storage...

Thanks.
Index and truth the high.
I.e. it turns out not to accelerate at the expense of a cache any more operation?

19

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
At the expense of a cache - already precisely is not present.
If only to look aside inmemory.
And in what, actually, at you a problem? Semiology what? Users complain? Or any means of monitoring produce indexes not arranging you?

20

Re: How to force a SQL Server to tighten the data in a cache?

  - Eh wrote:

the p-server;
At the expense of a cache - already precisely is not present.
If only to look aside inmemory.
And in what, actually, at you a problem? Semiology what? Users complain? Or any means of monitoring produce indexes not arranging you?

The chief kicks, tells that on the server  storages, and SQLServer it does not use sad((

21

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
Well here to tell...
Suggest the chief to descend in an epicure and to eat all that there is on sale... smile
And that of products the full shop, and he, you see does not eat them...

22

Re: How to force a SQL Server to tighten the data in a cache?

  - Eh wrote:

the p-server;
Well here to tell...
Suggest the chief to descend in an epicure and to eat all that there is on sale... smile
And that of products the full shop, and he, you see does not eat them...

And it is thought.... smile))

23

Re: How to force a SQL Server to tighten the data in a cache?

p-server wrote:

it is passed...
And tell, how it is possible to look at what data ?
Really not too it is a lot of probably operative data.

Here so it is possible to look in general, and in particular - see more in detail sys.dm_os_buffer_descriptors
[spoiler what bases in a cache]

SELECT
CASE database_id
WHEN 32767 THEN ' ResourceDb'
ELSE db_name (database_id)
END AS [Database];
convert (numeric (10,2), sum (case when is_modified=0 then 1 else 0 end)/128.) [Clean_MB];
convert (numeric (10,2), sum (case when is_modified=1 then 1 else 0 end)/128.) [Dirty_MB];
convert (numeric (10,2), sum (1)/128.) [Total_MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY convert (numeric (10,2), sum (1)/128.) DESC
option (maxdop 1, recompile)

[/spoiler]

24

Re: How to force a SQL Server to tighten the data in a cache?

The p-server;
If a sequel storage long
Does not wish to occupy
At the heads is not present
On it to look and wait
The you a script with the Hindu code
On a server suddenly
Also write more soon on 
,  a sediment

25

Re: How to force a SQL Server to tighten the data in a cache?

p-server wrote:

How to force it to use it is more than RAM?
The chief kicks, tells that on the server  storages, and SQLServer it does not use

If want to force Sql Server with a great effort that the chief was happy use the big pages (trace flag 834).
Then at start Sql Server should capture at once max server memory. Also start will be decelerated, at once it will be clear that it is serious system, instead of nonsense any smile