Memory

Question

Memory

Mairim Odara

SSC Enthusiast

Points: 103
More actions
January 25, 2005 at 3:09 pm

#92123

Hi,
I'm using a query on -table- open- query.
My tables have 2 indexes. The first, in "CLIENT" colummn is clustered. The second, in "DATE" is not clustered.
I have Administration Access.
I observed some situations when I try to execute queries:
1. When I start a system to execute, for example, this query:
SELECT CLIENT, CAST(MONTH(DATE) AS VARCHAR(2)) + '/' + CAST(YEAR(DATE) AS CHAR(4)) AS MONTHYEAR, VT AS VRT
FROM R713 GROUP BY CLIENT, CAST(MONTH(DATE) AS VARCHAR(2)) + '/' + CAST(YEAR(DATE) AS CHAR(4)), VRT
the system acuse a lot physical memory usage, more then 200 MB.
2. During execution of the queries, Sqlservr.exe uses 800 MB physical memory. After the execution or "timeout expired" message appears, sqlservr.exe keeps using 800MB(more or less) of the physical memory and don't available it for another processes.
Can you help me, again, please??????
Thanks, very much
Mairim

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

This is an expected feature on sqlserver. it won't release the memory that it adquires. If you need to restrict the memory you could set up the max server memory of the sqlserver process. But before you do that

1. Have you tried to set a WHERE clause on that query?

2, Do you really need to scan the whole table?

HTH

* Noel

Mairim Odara SSC Enthusiast Points: 103 More actions · Answer 2

Noel,

thank you. I have other examples to the same situation. when i execute views, sqlserver takes memory a lot and don't release it. do you now how do it?

thanks

Mairim

noeld SSC Guru Points: 96590 More actions · Answer 3

You could use EM right click on the server select properties and on the memory tab limit the maximum amount. Or use sp_configure to change max server memory.

this is an example of the use of the proc:

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

--Set the maximum server memory to 500 MB

EXEC sp_configure 'max server memory (MB)', 500

RECONFIGURE WITH OVERRIDE

-- Display the new configuration

EXEC sp_configure 'max server memory (MB)'

-- Reset 'show advanced options'

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Again, you should look at where clauses and indexes on your tables before you do that

HTH

* Noel

Steve Jones - SSC Editor SSC Guru Points: 734841 More actions · Answer 4

SQL Server will be slow to release memory. Once it takes it, it tends to hold on. Usually this is mostly for the data cache. You can limit as above or in Enterprise Manager by selecting the server, properties, then the memory tab.

Mairim Odara SSC Enthusiast Points: 103 More actions · Answer 5

Hi,

thank you (two - Noel and Steve)

I just do this - limited on memory properties - and now I have memory for another processes. Thanks again.

Noel,

In columns "CLIENTE", on my tables, I created indexes clustered and in columns "DATE", indexes non clustered. Now, when I execute views I have no problems. What you think?

thank you

Mairim

noeld SSC Guru Points: 96590 More actions · Answer 6

The indexes if used correctly will reduce the amount of work necessary to perform drastically. So just make sure that the ones set up are being used and keep fragmentation as low as possible (with maintenace &nbsp

hth

* Noel

Suresh Kumar Maganti Default port Points: 1429 More actions · Answer 7

I feel the date column need not be indexed. Clustered index on the first column, CLIENT is okay. This way you avoid memory intensive bookmark lookups.

No need to use convert (year, char (4)) etc. You can do it direcly without converting to char(4). Dattime to varchar andf varchar to datetime is implicit in SQL Server. This way, you reduce some more utilization of resources.

Finally, setting min and max memory to a value between 60% to 70% of your total RAm would help. Remember, both min and max server memory need to have the same value in your case.

Kindest Regards,

M Suresh Kumar

Mairim Odara SSC Enthusiast Points: 103 More actions · Answer 8

Hi,

thank you!

I need to consult values ("CLIENT" AND "VRT") grouping by month/year. The only way I could be it was using convert. Even so, some problems occur when a view, with a filter (month/year), is executed. For example, the user insert a value for CLIENT and a initial value for month/year and a value for final month/year. The goal is to get values grouping by month/year about that "CLIENT". But, the user get all values by month/year about that "CLIENT". I think the cause is the convert. Are you agree?

thanks again

Mairim