Post reply

Performance issue

  • May 12, 2014 at 8:53 am

    #291147

    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.

  • May 12, 2014 at 9:00 am

    #1712718

    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, MVP, M.Sc (Comp Sci)
    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

  • May 12, 2014 at 10:35 am

    #1712772

    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...

  • May 12, 2014 at 4:18 pm

    #1712876

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 12, 2014 at 11:49 pm

    #1712899

    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

  • May 13, 2014 at 2:49 am

    #1712938

    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.

  • May 13, 2014 at 10:53 pm

    #1713307

    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

  • May 14, 2014 at 12:57 am

    #1713323

    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, MVP, M.Sc (Comp Sci)
    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

  • May 23, 2014 at 10:11 am

    #1716163

    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.

  • May 25, 2014 at 8:17 am

    #1716383

    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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply