TechSapphire Saturday, 2024-12-21, 3:32 PM
Main | SQL server 100% CPU usage fix | Registration | Login Welcome Guest | RSS
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
    		•

    SQL server 100% CPU usage fix

    This issue is very common and commonly faced by DBA, developers and server management team on production server i.e. SQL server leads into 100% CPU usage and also start consuming all available memory. Now most of the time you have seen technical people restarting sql services, or killing long running processes and even restarting complete server, and they can avoid this situation. Before starting any thing I would like to discuss a common myth people have that this is happening because of long running queries only. Which is not all ways true, some times:

    • Longer running query can be problem
    • Query having maximum worker time are also problem
    • Query execution count can also cause problem

    Now in order to fix this issue you have to go through below video:

    In above video 2 methods are discussed to resolve this issue, both method are totally different from each other and also applied over 2 different situation, these methods are not solution for same problem, in spite both are different solution to different situation. Methods are:

    Fix Methods:
    Method 1 (traditional):
    Finding query with issue with traditional method. This involve tracing process on server which is running for long time and leading into this. And after tracing the process taking necessary actions in order to resolve situation.

    --Method 1
    --Checking for intense process running on server
    --Taking required actions
    Exec sp_who2
    DBCC INPUTBUFFER(SPID)
    KILL SPID


    Killing process or taking other necessary action required.

    Method 2:
    Going 1 level deeper
    Using Built in Dynamic Management View. Dynamic Management View are views provided by sql server itself so that people can check what is happening in server. In this method we will be finding Query with maximum total_worker_time or execution_count. And optimizing it if it is select query.

    • by creating index if suggested or required
    • by droping index if required
    • by creating partitions
    • by changing cardinality
    • by splitting long running update queries in smaller units


    --Method 2
    --Checking internal stats and execution plan
    --Stats to be considered
    --execution_count
    --last_worker_time
    --Internal check
    --Dynamic Management View
    select * from sys.dm_exec_query_stats
    select * from sys.dm_exec_query_stats order by creation_time desc
    select * from sys.dm_exec_query_stats order by total_worker_time desc
    select * from sys.dm_exec_query_plan(Plan)

    --here Plan is plan_handle from result of above query.
    select * from sys.dm_exec_procedure_stats order by total_worker_time desc

    For causing stress tool name SQL load generator is used it is free tool. You can download it from link here.

    They query used are mentioned below:

    select  * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail
    union all select * from sales.SalesOrderDetail

     

     
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2024