AWE working?

Hi all,

This is a question related to AWE settings. I am on windows 2003 ent version with SQL Server 2005 std version. I have configured SQL Server to use AWE and min memery as 22G and max as 26G (I have 32G in total as physical memory). I do see the message in log as "Windows Address ... Enabled", but when I am using performance monitor to view total memory in kb for SQL Server, it never starts at 22G (usually at 1G). And I doubt why? The account I am running SQL Server is an admin account, so it can definitely locked down the pages in memory. Also, I do think AWE is working somehow, after several hours in running, the total memory will up to 8, 9 or 11G. So, apparently, it is over 2G per process limitation. I still want to know.

1. Is my AWE setting working?

2. If not, how to fix that?

3. How to confirm how many memory is actaully using by SQL Server?

Thanks,

NIng

(The comment below assumes 32 bit OS and 32 bit SQL Server.)

1. Since you see that SQL Server's memory usage grows up to 11GB, it is obvious that AWE is working. SQL Server only requests memory from the OS as it is needed -once allocated, it usually keeps it.

2. Nothing to do

3. Performance Monitor, as you are using, indicates SQL Server's memory usage. For more detailed memory usage analysis, you can look at the various SQL Server Memory counters in Performance Monitor, and you can use some the the system functions that will give you memory data. Look at the System Views. (Click on Database, Views, System Views.) Try:

SELECT * FROM sys.dm_os_performance_counter

Looking specifically at the SQL Server Memory Manager objects.

|||Are you sure this is by design?

My performance is awful after I have upgraded. SQL Server 2000 used to locked the memory as it starts, so the production server (4 cpus and 32 G memory) was doing a lot better than my development server with (1cpu and 2G memory). However, after upgrade, the performance level of the production server is awful. For example, a query need 9 seconds on test server used to run only 2 seconds on live server. Now it needs 8 seconds ...

|||

After upgrading, it is NECESSARY to rebuild ALL the indexes, and update the statistics. If you have not done that, your performace will often be signicantly worse.

And even then, some queries will run faster on SQL 2005, and some (albeit few) will run slower. It may be necessary to 'revise' queries that are signicantly slower in order to use the new ways that SQL 2005 produces execution plans.

Avoid Cross Database Views in Data Warehouse

We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –

1) Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –

a. The size of tw will grow tremendously.
b. We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.

2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.

As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.

Thank you all in advance,

sajmeraWe have a relational database (rd) and a data warehouse (dw).
No you don't. You have a datamart. If you had a real data warehouse schema you would not have these issues.

The DBA wants me to remove the look up views.
Why?

2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
A data warehouse (or datamart) should be self-contained, and not rely upon pulling dynamic data from other systems. Maintenance and support are necessary requirements of a data stores.