September 2, 2017 at 9:21 pm
muhammadaftab25 - Saturday, September 2, 2017 1:40 PMJeff Moden - Saturday, September 2, 2017 11:10 AMmuhammadaftab25 - Saturday, September 2, 2017 12:01 AMJeff Moden - Friday, September 1, 2017 9:00 AMHold the phone a minute... the original post says that the problem occurs every 5-6 minutes. I've found that wait stats are normally a symptom rather than a cause. Someone first needs to find what's running every 5-6 minutes and what resources it is using. Until that is determined, you're usually chasing ghosts.Hi Jeff
It is SQL Server taking up 100% CPU usage after every 5-6 minutes. So I think that best way would be to see the list of most expensive queries? Is there any other way to see what SQL Server is looking for during this period?
Yes but you want to see the list during the incursion. The stored procedure is a little ditty I wrote a while back to help me with these types of this. I install it in my master database and, using the instructions in the header, turn it into a system stored procedure so during a panic, I don't need to worry about which database I'm in.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_WhatsRunning] Script Date: 11/7/2015 11:58:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_WhatsRunning]
/**********************************************************************************************************************
Purpose:
This stored proc returns what's currently running across the entire server in some great detail. The output is
sorted by CPU usage in seconds in descending order. Both the query and the "intended" execution plan are "clickable"
for easy and complete viewing.Please see the code
Programmer's Notes:
1. This stored procedure should live in the Master database.
2. After building the stored procedure, you need to run the following code to convert it into a "system" stored
procedure that can be executed from database.
USE MASTER;
EXEC sp_ms_marksystemobject 'sp_WhatsRunning';
SELECT name, is_ms_shipped
FROM sys.objects
WHERE name = 'sp_WhatsRunning'
;
Usage:
sp_WhatsRunning --(from any database)
... OR ...
EXEC sp_WhatsRunning --(from any database)
Revision History:
Rev 00 - 29 Feb 2013 - Jeff Moden
- Formalize this script for regular usage as a "system proc" that can be called from anywhere.
Rev 01 - 08 Aud 2013 - Jeff Moden
- Change the output of the "Query" column to make it so all formatting is preserved to make readability much
easier. Just click on the BLUE XML in the result set to see the query/proc with all formattinng preserved.
**********************************************************************************************************************/
AS
--===== Environmental Presets
SET NOCOUNT ON;
--===== Collect and display the information about what's running this instant.
SELECT Query = (SELECT N'--'+NCHAR(10)+t.text AS [processing-instruction(fgCode_SQL)] FOR XML PATH(''), TYPE) --Rev 01
, CPU = CONVERT(VARCHAR(20),CONVERT(MONEY,r.cpu_time/1000.0),1)
, Duration = CONVERT(VARCHAR(20),CONVERT(MONEY,r.total_elapsed_time/1000.0),1)
, PhysicalReads = REPLACE(CONVERT(VARCHAR(20),CONVERT(MONEY,r.reads),1),'.00','')
, Reads = REPLACE(CONVERT(VARCHAR(20),CONVERT(MONEY,r.logical_reads),1),'.00','')
, Writes = REPLACE(CONVERT(VARCHAR(20),CONVERT(MONEY,r.writes),1),'.00','')
, Rows = REPLACE(CONVERT(VARCHAR(20),CONVERT(MONEY,r.row_count),1),'.00','')
, SPID = r.session_id
, BlockingSpid = r.blocking_session_id
, Memory = r.granted_query_memory
, HostName = s.host_name
, ProgramName = s.program_name
, ClientInterface = s.client_interface_name
, LoginName = s.login_name
, StartTime = r.Start_Time
, RunStatus = r.Status
, Command = r.Command
, DBID = r.database_id
, DBName = DB_NAME(r.database_id)
, UserID = r.user_id
, WaitType = r.wait_type
, WaitTime = CONVERT(VARCHAR(20),CONVERT(MONEY,r.wait_time/1000.0),1)
, LastWaitType = r.last_wait_type
, WaitingOn = r.wait_resource
, OpenTrans = r.open_transaction_count
, IsManagedCode = r.executing_managed_code
, p.query_plan
, r.statement_start_offset
, r.statement_end_offset
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE r.session_id <> @@SPID --Keeps this proc from appearing in the output.
ORDER BY r.cpu_time DESC
;
GOSince it will be difficult and take a bit to log into the server during the times when you're having problems, I'd keep an SSMS session open to the box (I actually always have an RDC open to my production box with an SSMS session up an running. It has saved my butt more than once and doesn't take much memory).
You'll also notice quite a similarity between the code above and the code that Chris Harshman posted. That's because it's a tried and true method for interrogating the server during times of trouble. The difference is the he uses query stats and I use query requests as the source. His shows what HAS happened and mine shows what IS happening.
Hi Jeff & Eirikur and all others
Thank you very much for your kind help. Jeff's query which shows "What Is Happening" showed us the sql query when CPU usage was going to 80%. This query was part of an automated alert comparing data of 2 years and it was failing due to some reason. It was being called by continuously and failing continuously every few minutes. As soon as we disabled this alert, server came back to normal situation. I will thank you all and I would like to ask what basic base line data or db information we should have in our hands to use as a baseline. Frankly speaking, i did not exactly knew what was CPU usage in normal days in our server.
Just one thing... PERFMON charts. Taking daily samples (Print Screen) at different times of the day will give you a great visual baseline that takes just a couple of seconds to look at to see if something's wrong. I always keep a session running on my "money maker" server so that I can snap right to it and see a half hour of history. One picture truly is worth a thousand words. Combine that with the sp_WhatsRunning proc I gave you and you're able to find out what, if anything, is wrong in just seconds.
And thank you for the awesome feedback. Very happy to have been able to help on this one.
And you should still post that UPATE statement you found because that's also a problem waiting to happen. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2017 at 9:51 pm
p.s. To save you some time, I've attached a file called "PerfMon.txt". After you upload it, change the name to "PerfMon.msc" and save it to the desktop of your server (or any machine to try it... even your laptop will work with it). It will appear as a Red toolbox icon. It'll Start it just by double clicking on it. I have it setup with the scales for each line (the normal lines I have activated... extra troubleshooting lines are available but not displayed until you check the box for them) that I expect a server that's in good order to have. For example, if the thin Green (File Read Bytes/sec) or thin Blue (File Write Bytes/sec) are off the scale, then you have some serious issues that are normally manifested by bad code. If they don't go off the chart during a backup or restore, then you have some serious issues either with hardware or the "pipe" between your server and wherever your backups are stored. If you want to know what all the other lines are, please Google them. The chart also shows when jobs run and when blocking is occurring, not to mention % of total CPU.
And, yes, this is a copy of the one that I actually use in production. I keep it running 24/7 as well as an RDC connection to the more important public facing server just in case things go hay-wire. Like I said before, it's save my butt several times when no one else could get in when something went wrong. Neither take very much memory or CPU. Just do it. You'll someday be thankful that you did.
Remember that your original baseline may actually suck. The Perfmon chart can give you yet another documentation point for when you fix something. Great pride can be taken by the entire team when you isolate a single bit of problem code, fix it, and see CPU drop from (for a real life example that I've personally experienced more than once) from an average of 22% with wild swings to 40%-80% down to a nice, cool, calm, 4-8%. It does make for one of those holiday "Martha Stuart Moments" and, since it's a before'n'after picture, is easy to share with little explanation that even managers can understand.
It'll help you earn the right to wear a T-Shirt that says "I'm a DataBase Administrator... I solve problems you don't know you have in ways you can't understand." 😉
https://www.sunfrog.com/Data-Base-Administrator--SOLVE-192312050-Black-Guys.html?15435
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2017 at 9:41 am
Hi Jeff
Started saving performance monitor screenshots. I feel more confident after using it.
Here is the update query. It is from a third party application which moves data from stores to head office and vice versa.
Update X
Set X.SendSeqNumber = 0
From tblTransaction X Left outer Join tblMapDB Y ON X.SourceType = Y.ObjectID
Where TKey not in
(
Select DumpKey From tblReplication
)
And X.SourceKey > 0
And (X.SendSeqNumber IS NULL OR X.SendSeqNumber > @SeqNo)
And DATEDIFF(MINUTE, processdate, @SystemDateTime) > CASE WHEN @SiteID = 1 THEN 2 ELSE -100 END
And Y.TableType = 0 And ISNULL(rtrim(X.StoreId),'1') = '1'
For time being, we are happy with the performance but real load will be tested after few days when our holidays end and office reopens.
September 3, 2017 at 3:44 pm
muhammadaftab25 - Sunday, September 3, 2017 9:41 AMHi Jeff
Started saving performance monitor screenshots. I feel more confident after using it.Here is the update query. It is from a third party application which moves data from stores to head office and vice versa.
Update X
Set X.SendSeqNumber = 0
From tblTransaction X Left outer Join tblMapDB Y ON X.SourceType = Y.ObjectID
Where TKey not in
(
Select DumpKey From tblReplication
)
And X.SourceKey > 0
And (X.SendSeqNumber IS NULL OR X.SendSeqNumber > @SeqNo)
And DATEDIFF(MINUTE, processdate, @SystemDateTime) > CASE WHEN @SiteID = 1 THEN 2 ELSE -100 END
And Y.TableType = 0 And ISNULL(rtrim(X.StoreId),'1') = '1'For time being, we are happy with the performance but real load will be tested after few days when our holidays end and office reopens.
Thanks for the feedback on PerfMon.
On the UPDATE code... it does NOT contain the problem that I was talking about. It does, at first glance, have several other problems. Let me see what I can science out for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2017 at 5:04 pm
Concerning the update code you posted...
1. Both tables need to use the 2 part naming convention of schemaname.tablename.(Small performance gain + data safety/integrity).
2. The "TKey" and "ProcessDate" column names in the WHERE clause need to be prefixed with table aliases. (Readability for troubleshooting + bulletproofing).
3. The formula for "ProcessDate" in the WHERE clause needs to be rewritten to be SARGable. You should almost never use a function on a column in the WHERE clause. (SARGability {ability to use an index seek} for performance)
4. RTRIM in the WHERE clause can be removed if if it's CHAR or NCHARunless someone screwed around with the ANSI NULL settings. (Reduce unnecessary code clutter for readability).
5. There's a design flaw in the SendSeqNumber column of the dbo.tblTransaction table. It should not allow NULLs because it requires "OR" or "ISNULL" in this type of query, which makes things slow. (Performance)
6. There's a design flaw in the StoreId column of dbo.tblTransaction table. It should not allow NULLs because it requires "OR" or "ISNULL" in this type of query, which makes things slow. (Possible Performance/SARGability unless the other columns produce a very close seek)
7. I don't know the data or have any clue concerning the DDL, Constraints, or Indexes for either table to be able to comment on the effectiveness of the LEFT OUTER JOIN. (Possible performance issue)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply