August 30, 2008 at 1:44 am
What are the things need to be checked when the server is slow and how .
Thanks in Advance .
August 30, 2008 at 2:00 am
What do you mean by slow? Queries taking longer than the should? Queries timing out? All operations (including remote desktop) slower than normal? CPU maxed out?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2008 at 2:14 am
example normally my query wont take so much of time but due to some issue in server its taking so much of time In such a case what i need to check ...
August 30, 2008 at 9:05 am
You can check sp_who2 to look for blocking, check the CPU as another query might be taking up resources. If the server was recently restarted, the first query can be slow as the cache is primed.
August 31, 2008 at 11:19 pm
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.....
August 31, 2008 at 11:39 pm
It means that the OS has forced a lot of SQL's memory into the swap file. As the message says, it's near guranteed to result in poor performance.
How much memory is on the server?
What's sQL's max memory setting?
What else in running on the server?
Standard or enterprise version of SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2008 at 11:56 pm
Hi,
I have come across the below link, check if this helps:
-Rajini
September 1, 2008 at 5:13 am
sudhakara (8/31/2008)
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:
SELECT
index_advantage
,user_seeks
,last_user_seek
,avg_total_user_cost
,avg_user_impact
, equality_columns
,included_columns
,[statement]
FROM
(SELECT
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
inner join
sys.dm_db_missing_index_groups AS mig
ON
migs_adv.group_handle = mig.index_group_handle
inner join
sys.dm_db_missing_index_details AS mid
ON
mig.index_handle = mid.index_handle
WHERE
migs_adv.index_advantage > 10000
ORDER BY
migs_adv.index_advantage desc
Look for most costly queries by IO:
--http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
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]
September 3, 2008 at 12:18 am
How much memory is on the server? 16 GB
What's sQL's max memory setting? 10 GB
What else in running on the server? Nothing
Standard or enterprise version of SQL? Standard
September 3, 2008 at 9:41 am
How much memory is on the server? 16 GB
What's sQL's max memory setting? 10 GB
What else in running on the server? Nothing
Standard or enterprise version of SQL? Standard
September 3, 2008 at 9:45 am
sudhakara (9/3/2008)
How much memory is on the server? 16 GBWhat's sQL's max memory setting? 10 GB
What else in running on the server? Nothing
Standard or enterprise version of SQL? Standard
Raise the max memory to 13 GB. You have 6 GB dedicated to OS right now, that's a waste.
You will need to check for costly queries, large table scans, missing indexes. Try some of the above recommendations to troubleshoot those.
__________________________________________________________________________________
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]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply