You say you enabled AWE on SQL Server but did you activate /PAE in the boot.ini file?
PAE enables Large Memory support.
Activate PAE in Windows 32Bit Enterprise
c:\boot.ini
[boot loader]
timeout=30
default=multi(0)disk(2)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
http://support.microsoft.com/kb/283037
Lock Pages in Memory
As part of enabling AWE, you would also need to give the SQL Server Service user the right to Lock Pages in Memory. Enabling this option in the Group Policy dialog (gpedit.msc), prevents Windows from paging memory out of the SQL Server working set for its own needs, especially if memory starts to run low on Windows.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Monitoring AWE Memory Usage
To test AWE usage, I setup a Windows 2003 Enterprise SP2 with PAE enabled in boot.ini. I used SQL Server 2005 32bit Standard SP4.
The total physical memory is 8192Mb. The max memory setting for SQL Server is configured at 8192 and the minimum is at 2048.
Does SQL Server see AWE on startup.
2012-02-09 07:27:51.35 Server Address Windowing Extensions is
enabled. This is an informational message only; no user action is
required.
Is SQL Server using Locked Pages for the Buffer Pool
According to some blogs, I should be seeing this message, but I'm not.
Using locked pages for buffer pool.
This is an informational message only; no user action is required.
Memory being allocated through AWE
SELECT SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM sys.dm_os_memory_clerks ;
[3648]
Memory allocated to Multi-Page memory.
select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks
[14]
Multi-page memory can not use AWE allocated memory
Memory being used outside the buffer pool
SELECT sum(multi_pages_kb
+ virtual_memory_committed_kb + shared_memory_committed_kb) AS
[Memory used outside BPool, mb]
FROM sys.dm_os_memory_clerks
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL'
[24]
Overall memory allocation by component
SELECT type, (single_pages_kb)/1024 as Single_Pages_MB, (multi_pages_kb)/1024
AS Multi_Pages_MB, (awe_allocated_kb)/1024 as AWE_allocated_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC
This will show you how single-page and multi-page memory is being used by each component. It will also tell you which one is able to benefit from AWE.
Mutli-page : when request exceeds 8Kb
Single-page : when request is less than or equal to 8Kb
As a side note, I have found sp_whoisactive very helpful in finding slow queries, memory hogs and just about everything that's going on in SQL Server. Here is a link that Brent Ozar provides on how to set it up and use it.
http://www.brentozar.com/archive/tag/sp_whoisactive/
Looking at your RAM availablity for this particular server and you are running several databases ranging from 30GB to 5 GB, you definitely need more RAM on this server.
You have not mentioned that this is a stand alone instance or this server is having more than one instance of sql server running.
Your MAX Memory settings seems OK for a server having 8GB RAM. See these suggested best practice settings from Glenn Berry.
I would highly recommend you to do a baseline of your environment using below PERFMON counters to get a good value of your memory configuration :
- SQL Server:Buffer Manager\Page Life Expectancy
- SQL Server:Buffer Manager\Page reads/sec
- Physical Disk\Disk Reads/sec
- Memory\Available Mbytes
- SQL Server: Memory Manager - Total Server Memory
- SQL Server: Memory Manager - Target Server Memory
Total Server Memory: Amount of memory currently allocated to Buffer Pool and not the total amount of memory to SQL Server
Target Server memory: Ideal Size of the buffer pool corresponding to max memory for the instance.
Note: If Total Server Memory > Target Server memory, then it suggests memory pressure.
Below script will help you find LOW or HIGH Memory notifications from sys.dm_os_ring_buffers
- system health session :
SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
--and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC
Some good references :
Best Answer
It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion.
There are 2 possible base reasons for your slow down.
Let's take a look at part No. 1
It might be that your SQL Server configuration might be broken. This can cause serious problems regarding your Server speed and the disc usage.
Please check in the first instance your basic server settings. Those basic settings are
max server memory
,affinity I/O mask
,affinity mask
andmax degree of parallelism
. You may need to enable the advanced options usingshow advanced options
.Here is a complete script:
Compare the result with your documented values in your installation steps. Are they still the same?
It may have many reasons why your server behaves so strange. I would normally bet, that your
max server memory
is just wrong. This will cause your SQL Server permanently swapping data pages. He can't hold everything in his memory. This means he need to read the pages from the disc, update it, write it instantly back. If another update comes along and uses the same page for an update, it can't be read from the memory. Instead the server needs to read it again from the disc. Just swapping...Another problem can be a to high affinity on disc or processes. If you used a shared Server (SQL Server + other services) with a dedicated disc for SQL Server (which may be a rare case, but it could be), this could be your problem. Your server normally used to have for example 3 cpus for processes and one for I/O. The other 12 cpus are used for other services. In this case your affinity mask is wrong and uses for example an automatic configuration. This means your Server uses all the 16 cores for processes and I/O dynamically. If you have huge processes running, they can put a huge load on the disc, which it may not handle. But in fact, I don't believe that this is your case. It would be faster (even if just a bit) if this would apply, but your case is a slow down.
Another problem may be a too high degree of parallelism. Which means you have too many threads idling on one partial of a query. This could also cause a huge slow down if the parallelism don't work as expected. But this won't describe your high I/O in total.
Now let's take a look at part No. 2 too
You load a bunch of rows into your system. Even if this is a regular job, it could raised a limit in which your query plans escalate. It could be even the case that your insert in combination with SQL Server produces this behavior.
Yοu mentioned that you already tried to migrate your indices to another disc, which seems to help. This can be the happened just to the fact that you split the load on two different discs.
It may be that your indices were fractured, that your plans were fractured or that your statistics are just outdated.
1. lets check the statistics last update You can do this manually over the interface for each single statistic element. Which would be a pain. Or you can try this code:
This will give you a complete information over each index (and heap) and the statistics behind them. Even if you run
sp_updatestats
it doesn't mean that the statistics were updated. The part when an update is quite tricky, even if you runsp_updatestats
or even ifauto update statistics
is enabled, the statistics won't be updated just in time. Here are some edge points, when an update is needed/generated:This means, your statistics may be outdated even if you run the update.
You can take a look at the query above. If you find some pretty old statistics in some tables, you may want to run an manual statistic update for this table:
After that, you may want to give your server a kick in the ass to throw away all old plans.
If you just want to clean all caches, you might want to run this instead:
This will clean up all caches, not just the plan cache. I would normally warn, to use this on a production server in production phase. But as your server don't work currently, you can't harm them too much. It might slow down for some seconds maybe 1-2 minutes as he needs to rebuild all caches, but after that he should run with the correct plans.
Another reason can be totally fragmented indices. This can be checked on the whole server using this statement:
If the fragmentation is very high, you might need to reorganize (fragmentation < 20%) or totally rebuild (>20%) it. This may take more pressure on your disc and cause trouble. On the other hand, if the indices are that bad, it probably would help in the end more than it harms.
Beside those two reasons, there still may be a third problem
It maybe that your server is configured probably, you haven't changed any code in this time, just added a few rows. All statistics are updated and all caches are rebuild. All your indices are reorganized in the way you need them, but still - nothing works. It just might be that you reached the limit of available memory in your processes. Maybe you need more. You can simply check if there is any process which tries to get more memory than you have.
You can check this using this command:
It will provide you a list of all sessions which consuming memory. There might be some query which is still waiting to get memory. Those queries can be easily filtered. All sessions where
granted_memory_kb IS NULL
. These are sessions which requested memory but don't get it. Another thing can be an granted memory which may be to low. You can compare the columnsrequested_memory_kb
withgranted_memory_kb
. Requested shows how much memory the process needs to run optimal while granted shows up the memory which is enable for the process. If a process needs 2GB to run but only gets 2MB... you might get it on your own. ;-)Another way is to check the
RESSOURCE_SEMAPHORE
:You can take a look at the
waiter_count
and thegrantee_count
. If the waiter is above 0, you have pressure on your memory, which may cause swapping and may cause the disc pressure seen by you in the perfmon.