Category: Microsoft

SQL : View Running Trace Information

The following SQL can be used to identify any traces that are active on a SQL instance.

Use the query below to see how many running traces there are on the instance:

SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

The next query will return more detailed information about the active traces:

SELECT * FROM :: fn_trace_getinfo(default)

To halt the running trace use the fllowing query:

EXEC sp_trace_setstatus  1, @status = 0

To delete the trace definition from the database:

EXEC sp_trace_setstatus  1, @status = 2

SQL 2000 : Enabling AWE on Windows Server

On an x86/i386 system it is possible to use PAE and AWE to allow SQL to use more than 2GB of RAM. Windows Server Advanced 2000 x86 allows for up to 8GB of RAM using PAE and AWE, Windows Server 2003 Enterprise allows for up to 16GB.Datacenter editions allow for even greater ammounts of PAE/AWE assigned RAM.

First configure the /PAE switch in the operating system boot.ini file. You can also use the /3GB switch if not configuring more than 16GB of RAM.

Next, run the following SQL to enabled the instance to use AWE, and therefore the newly available RAM.

sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘awe enabled’, 1
RECONFIGURE
GO
sp_configure ‘max server memory’, 2048 — This Sets The Allocation To 2 Gigabyte
RECONFIGURE
GO 

You will have to restart the SQL instance for the change to become effective.

Considerations:

• The total sum of all SQL assignd RAM should not be greater than all of the memory in the server; you should removed 2GB from this total for the OS if not using the /3GB  switch, or 1GB if using the /3GB switch.
• You should configure the SQL service account to have ‘Lock Pages In Memory‘ permissions; this will prevent the AWE memory being paged to disk.
• On a failover cluster environment, the sum of ALL instance assigned AWE memory should be no greater than the total memory (taking the kernel reserved 2GB/1GB depending on /3GB switch) on a single node. If this is exceeded, any instance which starts on a node where all memory is assigned will start in dynamic mode with 128MB RAM, or my even fail to start.
• The ‘min server memory’ option is ignored when using AWE.
• You cannot monitor SQL server memory use when utilising AWE from Task Manager – this will simply show the SQL instance using the total amount of memory. The following SQL can be used to identify real memory usage:

select counter_name,cntr_value/1024 As MemoryUsedMB from master..sysperfinfo
where counter_name = ‘Total Server Memory (KB)’

• AWE is is an enabler allowing a 32-bit Operating System to address more than 4GB of physical memory.; there are obvious benifits however, there are performance considerations which should not be over looked when using AWE. For example, AWE memory cannot be swapped to the page file, therefore you should closely monitor application memory requirements after machine startup before allocating memory to SQL.