May 9, 2014 at 10:46 am
I will try to provide with as much information as I can
Problem: Sql server is running slow. On the application side, Console is loading very slowly. I checked the performance monitor but no luck (meaning cpu and memory looks good, no major spikes). I ran a query to check for any locks but no locks. No jobs are running. I also checked for index fragmentation and there are some indexes which are fragmented. The same problem happened last Friday around the same time but like I said, no job is running. I ran this query to find out fragmentation which took almost 3 minutes.
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30 and
Page_count > 1000
ORDER BY TableName
Also ran this to find out top 20 queries taking up CPU:
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = --Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = --Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = --Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
from
sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc
See the attachment. Result looked scary but I don't even know what those number are or what it means.
Any clue on what I should be looking for?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 9, 2014 at 11:07 am
So this is only at certain times on certain days? At those times have you ran activity monitor to get a view on the status of the system? anything being hammered? also have you checked task manager on the server to see if anything else is running on the server other than SQL?
May 9, 2014 at 11:14 am
cunningham (5/9/2014)
So this is only at certain times on certain days? At those times have you ran activity monitor to get a view on the status of the system? anything being hammered? also have you checked task manager on the server to see if anything else is running on the server other than SQL?
I restarted the service which helped. See the attachment for all the processes running.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 9, 2014 at 11:18 am
you mentioned there was index fragmentation, do you ever rebuild the indexes? do you update statistics? What are the server specifications in terms of memory and cpu and what are you sql memory settings ie max memory?
May 9, 2014 at 11:21 am
Yes I did rebuild all the indexes but I don't think that was the issue since the performance problem started today and restarting the service solved it.
32 GB of memory. 8 GB for this instance. We also have 2 more instances running on this VM.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 9, 2014 at 12:04 pm
Not enough information to really say for sure what it might be, but since restarting changed the behavior, I'm thinking one of two things. First, it's possibly you had resource contention or blocking. The query you ran sorts by total_worker_time. I'd go for elapsed_time instead, either total or max, to get the longest running queries. worker_time is just the CPU time. It doesn't include I/O (the slowest part of any system) or any other contention measures. elapsed_time does. And you're right, returning the plan_handle as part of your query is a waste of your time. It's meaningless. Instead, to check for blocking, query against sys.dm_exec_requests. That will show you what's currently executing and will include any blocking.
Second, you may be dealing with bad parameter sniffing. It's possible that a query runs normally fine but gets a parameter that generates a plan that is problematic. This is very difficult to troubleshoot. First, you have to identify the query that was causing the system to slow down (see above for a place to start). Then you need to capture the execution plan when this query is running poorly and when it's running well and compare them. If they're different, it's likely that you're seeing bad parameter sniffing.
But, both these are just guesses since we really don't have enough to go on at the moment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2014 at 12:18 pm
Sorry forgot to mention. This is another query I ran before I restarted the service, but couldn't find any blockage.
SELECT client_net_address as HOSTIP,Blocking.session_id as BlockingSessionId , Sess.login_name AS BlockingUser , BlockingSQL.text AS BlockingSQL , Waits.wait_type WhyBlocked , Blocked.session_id AS BlockedSessionId , USER_NAME(Blocked.user_id) AS BlockedUser , BlockedSQL.text AS BlockedSQL , DB_NAME(Blocked.database_id) AS DatabaseName FROM sys.dm_exec_connections AS Blocking INNER JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id RIGHT OUTER JOIN sys.dm_exec_sessions Sess ON Blocking.session_id = sess.session_id CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL ORDER BY BlockingSessionId, BlockedSessionId
I don't know if its a blockage issue since the same problem happened last Friday around the same time, and by restarting the service I was able to resolve it on a temporary basis. I have to find out if there is any job running.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 9, 2014 at 12:29 pm
The query against sys.dm_exec_requests will show all the queries running as well as any blocking. You can see the resources being used, how long a query has been running, pretty much everything you need to identify if one process is using up the resources.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2014 at 12:55 pm
Grant Fritchey (5/9/2014)
The query against sys.dm_exec_requests will show all the queries running as well as any blocking. You can see the resources being used, how long a query has been running, pretty much everything you need to identify if one process is using up the resources.
Thank you Sir.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply