August 31, 2009 at 5:44 pm
Slow performance on db … SQL Server usage spiking for long periods of time.What should I check, how can I find out what's wrong
August 31, 2009 at 6:00 pm
Hi
Execute the below query to see what are the SPIDs with more CPU
USE [master]
GO
SELECT * FROM sysprocesses
ORDER BY cpu DESC
And run DBCC INPUTBUFFER(SPID) or fn_get_sql() to find more about what SPID is executing.
Lets us know the results.
Thanks,
Raj
August 31, 2009 at 6:57 pm
Raj has a very good suggestion.
If you know when it is actually happening you can look at sp_who2 or else the Activity Monitor if you prefer a gui. Of course, if it is truly running slowly the activity monitor may take a long time to open or fail to pen at all.
Of course, do not forget to look at what other processes are running on that machine, especially if you are running SQL Server under a virtual instance.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 31, 2009 at 8:06 pm
Here is my results.What should I look for?
August 31, 2009 at 8:27 pm
vv
September 1, 2009 at 12:29 am
Was this captured during one of the slow periods? It shows very little running and does not seem to have any particularly long running queries at all (of course that is a relative term. For something meant to be executed dozens of times a minute a query taking an entire second could be far too long, while for other things that is more than acceptable).
Try to capture one during a slow period. If that does not give you a clue as to where to look next, consider using the profiler for a while. The profiler will itself cause a slow down while it is running, but it will give you a wealth of information if you can let it run for 24 hours (or other relevant time depending on your local business and usage cycle.)
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 1, 2009 at 1:05 am
As Timoty said Profiler uses resources..You can go for Server side Trace to track the issue.
September 1, 2009 at 5:58 am
Krasavita (8/31/2009)
Here is my results.What should I look for?
Looking at that data, I don't see anything running especially slowly. There are no blocked processes. Are you sure that the server is running slow? Was this query taken while it was running slow? Have you gathered performance metrics using PerfMon to look at things like page life expectancy (for memory bottlenecks) cpu or disk wait queues, etc? And yes, setting up a server side trace so you can capture the queries as they're run. You need a lot more information before a solution can be proposed.
"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
September 1, 2009 at 6:33 am
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
September 1, 2009 at 6:38 am
When I ran the query,it wasn't running slow
September 1, 2009 at 6:46 am
You said when you ran the query it wasnt slow? Who/what else ran the specific query (WinUI/Web app???)?
Did you run it @ the same time of day?
September 1, 2009 at 6:50 am
Krasavita (9/1/2009)
When I ran the query,it wasn't running slow
Ah, well, that's why there wasn't any useful information in the query. You need to gather the information when the slow performance is actually occurring.
"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
September 1, 2009 at 7:56 am
Yes, for sp_who2 to be useful you need to run it during the trouble. If that is not possible/impractical then consider either the server side trace or profiler. Also, if you happen to know that the problem revolves around one specific query/procedure than that is invaluable information.
The two articles Gail linked provide an excellent overview to locating causes for poor performance. One thing I think is worthy emphasizing from personal experience is also looking at what else is running on the server besides sql.
Ideally, SQL should run on a dedicated server, but that is not always possible so anything else being run on that server can affect its performance indirectly. Similarly, even if SQL is on a server by itself its apparent performance can be slowed down if there is heavy network traffic at the moment.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 1, 2009 at 10:06 am
timothyawiseman (9/1/2009)
Similarly, even if SQL is on a server by itself its apparent performance can be slowed down if there is heavy network traffic at the moment.
I've seen a SQL Server brought to its knees by an admin copying a large backup file off the server.
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
September 1, 2009 at 3:26 pm
GilaMonster (9/1/2009)
timothyawiseman (9/1/2009)
Similarly, even if SQL is on a server by itself its apparent performance can be slowed down if there is heavy network traffic at the moment.I've seen a SQL Server brought to its knees by an admin copying a large backup file off the server.
This reminds me of the last 3 major server outages we had. We had a network/server admin performing some "routine" tasks on the server and SAN.
Oops
Try gathering the information once again during a slow period and post the results again.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply