when check the error log i had seen the msg
"A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Duration: 0 seconds. Working set (KB): 10864, committed (KB): 91356, memory utilization: 11%." what was the rason for this error message.....
Looks like you are using AWE, locked at 10.9 GB. Is this correct?
It's possible that large table scans are flushing the memory.
Check for missing indexes:
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage
, migs.* FROM sys.dm_db_missing_index_group_stats migs
) AS migs_adv
sys.dm_db_missing_index_groups AS mig
migs_adv.group_handle = mig.index_group_handle
sys.dm_db_missing_index_details AS mid
mig.index_handle = mid.index_handle
migs_adv.index_advantage > 10000
Look for most costly queries by IO:
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
As a last resort, run SQL Profiler on your system, choose the tuning template, add the Reads column and filter out SQL statements with under 1000 reads. Run the trace for some time, and collect the data into a table. Focus on queries with the largest no. of Reads. These are likely the queries bringing down your system.
The other possibility is that you do need more RAM for your system. Here are some questions:
(1) How large is your database(s)?
(2) Are you on 32-bit or 64-bit?
(3) What is the RAM on your machine?
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]