Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 8:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 273, Visits: 986
Hi,


We have sql server 2008 r2 prod server,on which user is facing slowness on every early Monday Morning.At that time Sql is taking 100% cpu.

I have checked at that time for any schedule jobs that were running ,i found that some daily maintenance jobs(like db report backup status,failed jobs) were running on that time frame.These are running daily and i think this jobs will not cause any issues as it running daily and other days performance is normal at that time.

Is their any other way were i can find exact cause of the issue?
Will any other things like( anti virus,..etc,user activity at that time,application activity etc) will cause performance?

Your suggestions and help is much appreciated.
Many Thanks.






Post #1569899
Posted Monday, May 12, 2014 9:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 42,492, Visits: 35,559
Maybe start with this:
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1569901
Posted Monday, May 12, 2014 10:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 80, Visits: 329
You could run a trace, this will track what is occurring on the database around the time the user is reporting a performance issues.

However, for the CPU to peak at 100% around the same time each week and there isn't a SQL agent job scheduled then this doesn't sound like a SQL issue (unless there is a rogue user!). By the sounds of things it could be an infrastructure issue. I would ask your Infrastructure team to identify what type of updates occur around this time and request to be excluded from them. If that's not fruitful look into the logs - [start][administrative tools][event viewer]




--------------------------------------------

Laughing in the face of contention...
Post #1569969
Posted Monday, May 12, 2014 4:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
While it's incredibly primative, I've found it to be very effective. You've already identified WHEN the high CPU usage occurs. Open TaskManager and find out WHAT is using the most CPU at that time.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1570083
Posted Monday, May 12, 2014 11:49 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:21 AM
Points: 33, Visits: 325
Based on time you've identified - attempt to track down the main symptoms - which appear to be high CPU. use this information to find other pottential root causes, examples are: Anti Virus scanning database files , has there just been an ETL process and the statistics are out of date

Jack Vamvas
sqlserver-dba.com
Post #1570123
Posted Tuesday, May 13, 2014 2:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 1,362, Visits: 15,266
arrjay (5/12/2014)
You could run a trace, this will track what is occurring on the database around the time the user is reporting a performance issues.


That could potentially make a bad situation worse. A little more investigation is required before using this as an option.
Post #1570183
Posted Tuesday, May 13, 2014 10:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 273, Visits: 986
Thank you all for your valuable info,

a)From last two days i have observed that when i run the SP_who2 ,for one of the databases(reporting db that application facing performace issue) ,so may SPID is runnning on it and they are in sleeping status and Awaiting command.

and when ran the below command

select * from sysprocesses where cpu>0 order by cpu desc

i could see one spid that is taking high cpu and waitype is Pageiolatch.

Lastwaittype Waitresorce dbid cpu phycical_io

PAGEIOLATCH_SH 12:1:613705 726765 182888


b)One more thing i observed is ,Windows server is a virtual server and has only 1 CPU (1core) ,


will this two be the reason for cpu problem

Please suggest me.

Many Thanks
Post #1570628
Posted Wednesday, May 14, 2014 12:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 42,492, Visits: 35,559
Follow the articles I referenced, identify the queries with the highest CPU usage overall (not the one high one at a time you look at the server), tune them to reduce their CPU requirements.
Also, consider adding more CPUs to the VM



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1570655
Posted Friday, May 23, 2014 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 273, Visits: 986
Hi All,

I observered that some ssis activity(queries) from application side is going on one of the reporting database due to which cpu is going high ,but this activity is ruining daily based (it completes in 20 mins) but on sunday and monday ,this activy is taking more than 3 hours .

i have check there is no sql jobs running on the server at that time.

Could any one help me how to troubleshoot t know the reasion for taking more time to run sunday and monday.?

your thoughts or any quires are much appreciated.

May thanks.
Post #1574133
Posted Sunday, May 25, 2014 8:17 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:21 AM
Points: 33, Visits: 325
Check to see if there is any other activity on Sunday, for example, are there backups going on at the same time?

Jack Vamvas
sqlserver-dba.com
Post #1574387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse