does SQL use AWE memory whenever possible?

I've got a 32-bit server with 16GB memory. In SQL Server, AWE is enabled. Does SQL use AWE memory for everything possible? I've also got SSAS installed on that same box, and SSAS can't use AWE memory. So I want SQL to use AWE memory as much as possible to leave as much free for SSAS as possible.

As I understand it, when you set the max server memory setting for SQL Server, it prevents the sum of normal and AWE memory from going over that limit you've set. Is there any way to tell SQL server to use as much AWE memory as it wants, but be use as little normal memory as it can?

SQL Server uses AWEonly for data cache pages in the buffer pool. As far as I know this can't be changed (in any supported fashion).

Also, I could be wrong here, but I believe the 3 GB limit is per process, meaning you could have two individual processes that don't support AWE each using 3 GB on a 16 GB system - they wouldn't be limited to a grand total of 3 GB between them. Where AWE comes in is allowing a single process to use more than 3 GB by itself. I'm sure the OS guys can correct me if my assumptions about AWE are incorrect.
|||

We're on the right track to answering my question. Do all pages that it caches use AWE memory exclusively? (Does it use AWE whenever possible?) Will it ever cache pages using normal memory if AWE is on and there is still AWE memory available?

I've put a 1GB limit on SQL so as to leave plenty of room for SSAS. I'm wondering if removing that limit on SQL will cause it to use more normal memory, or just more AWE memory.

I'm pretty sure it's 3GB total across all processes, not per process.

|||Well, there's the buffer cache and the procedure cache. The buffer cache, which holds actual data, will use AWE if it's enabled. The procedure cache doesn't use that, but I don't believe it'll end up nearly as large as the buffer cache under normal circumstances.

As for whether or not non-AWE applications are limited to 3 GB each, or 3 GB total, I think we'll need a more authoritative answer from someone on that, since it has a significant effect on how to handle the situation. This KB article makes me think it's 3 GB each, but I'm not 100% certain: http://support.microsoft.com/default.aspx/kb/283037

To get an idea of how memory is being allocated, compare the Performance Monitor counter SQLServer:Memory Manager\Total Server Memory (KB) to the amount of memory that Task Manager reports (which doesn't include AWE allocations).
|||

If you set max server memory, it limits how much memory, total that the database engine can use, whether it is AWE or not. I have not heard of any way to try to force SQL Server to use AWE memory first.

If you are concerned about memory, and have 16GB of RAM, you should seriously consider running 64-bit, with the "Lock Pages in Memory" right granted to the SQL Server service account. That is assuming your processors are 64-bit capable (which they should be if they are less than about two years old). I have some good DMV queries here:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!526.entry

sql

Does it store all the results to tempdb database when I query against a large table which joins

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

It doesn't store all the results - but it may store some intermediate results if it needs to.

You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.

If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.

Rob|||

Hi, Rob,

Thank you for your kind advices.

But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.

I am looking forward to hearing from you further for your kind advices.

With best regards,

Yours sincerely,

|||

And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?

Please give me some more advices for this.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.

ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')

Just make sure you do the log in addition to all the data files you have.
|||

Hi, davidbrit2,

Thank you for your kind attention.

But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.

Any more ideas? Thanks a lot in advance.

With best regards,

Yours sincerely,

|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.

Hope this helps,

Rob|||

Hello Helen999888,

Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||

Hi, HS_DJ,

Thanks a lot for your very kind and helpful advices. Got it done now.

With best regards,

Yours sincerely,

Documenting triggers

I believe in avoiding triggers whenever possible. However, in the
circumstance that a trigger is the "right" solution, I'd be curious how
people document/diagram triggers so that a successor could actually find
them aside from just having a document that says "by the way, there are some
triggers on these tables ...". That's the type of document that will be
lost/forgot about in a hurry. Moreover, triggers don't fall nicely into the
graphically easy to find categories of tables, views, stored procedures,
etc..
Thanks in advance for your suggestions.
MarkThere is no way to do this in SQL Server (only through quering the
information_schmea /systables), but if you have Visio you can easily load
them from your database via Reverse Engineering. Thats the way a documenting
i use, because if something changes i just "reload" the database in Visio
and Voil.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mark" <Mark@.nowhere.com> schrieb im Newsbeitrag
news:ue%23F8noSFHA.3188@.TK2MSFTNGP09.phx.gbl...
>I believe in avoiding triggers whenever possible. However, in the
>circumstance that a trigger is the "right" solution, I'd be curious how
>people document/diagram triggers so that a successor could actually find
>them aside from just having a document that says "by the way, there are
>some triggers on these tables ...". That's the type of document that will
>be lost/forgot about in a hurry. Moreover, triggers don't fall nicely into
>the graphically easy to find categories of tables, views, stored
>procedures, etc..
> Thanks in advance for your suggestions.
> Mark
>|||See if this helps:
Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Mark" wrote:

> I believe in avoiding triggers whenever possible. However, in the
> circumstance that a trigger is the "right" solution, I'd be curious how
> people document/diagram triggers so that a successor could actually find
> them aside from just having a document that says "by the way, there are so
me
> triggers on these tables ...". That's the type of document that will be
> lost/forgot about in a hurry. Moreover, triggers don't fall nicely into t
he
> graphically easy to find categories of tables, views, stored procedures,
> etc..
> Thanks in advance for your suggestions.
> Mark
>
>