Pages

Tuesday, 18 September 2012

SQL Server: Part 1 : Approaching Database Server Performance Issues

When you work as DBA, many people will approach you with a complaint like "Application is taking ages to load the data on a page,could you please check something going wrong with database server?" There might be hundred of other reason for slowness of the page.It might be a Problem with application server,network issues,really a bad implementation or problem with database server due to generation of huge report /job running at that moment. What ever be the issue, database gets the blame first. Then it is our responsibility to cross check the server state. 

Let us discuss how we can approach this issue. I use following script to diagnose  the issues. The first script which I will run against server is given below:

SELECT 
parent_node_id                  AS Node_Id,
COUNT(*)                        AS [No.of CPU In the NUMA],
SUM(COUNT(*)) OVER()            AS [Total No. of CPU],
SUM(runnable_tasks_count )      AS [Runnable Task Count], 
SUM(pending_disk_io_count)                    AS [Pending disk I/O count],
SUM(work_queue_count)          AS  [Work queue count]
FROM sys.dm_os_schedulers WHERE status='VISIBLE ONLINE' GROUP BY parent_node_id


This will give following information. 
  • Number of records in the output will be equal to number of NUMA nodes (if it is fetching only one record , it is not a NUMA supported server)
  • Node_id : NUMA node id . Can be mapped into the later scripts.
  • No.of CPU in the NUMA : Total number of CPU assigned to the specific NUMA node or the number of schedulers.
  • Total No. of CPU : Total No. of CPU available in the server.If you have set the affinity mask, total number of CPU assigned to this instance.
  • Runnable Task Count: Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue. Is not nullable. In short number of request in runnable queue.To understand more about Runnable queue , read my earlier post.
  • Pending disk I/O count : Number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed.
  • Work queue count: Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up.
I have scheduled this scrip to store the output this query in a table for two days in the interval of 10 minutes. That will give baseline data about what is normal in your environment. In my environment people will start complaining once the Runnabable Task Count of  most of the nodes goes beyond 10 consistently. In normal scenario, the value of Runnabable Task Count will be always below 10 on each node and never seen a value greater than 0 for work queue count field.This will give a picture of current state of the system.If the output of this step is normal, we are safe to an extent, the slow response might be issue which might be beyond our control or a blocking and slow response is only for a couple of screens(sessions) not for entire system. 

This is the first step in my way of diagnosing. I will explain the further steps in my next post.

If you liked this post, do like my page on FaceBook



26 comments:

  1. First time I commented in a blog! I really enjoy it. You have an awesome post. Please do more articles like this. I'm gonna come back surely. God bless.

    Rica
    www.imarksweb.org

    ReplyDelete
  2. افضل صيانة في مصر مصر باقل الاسعار مع صيانة كريازي بصيانة بقطع غيار اصلية لما لدينا فريق فنيين متميز

    ReplyDelete
  3. لصيانة اجهزتك بامان يمكنك مع مركز صيانة صيانة كلفينيتور
    اجهزتك باقل الاسعار وصيانة بقطع غيار اصلية

    ReplyDelete
  4. يمكنك الان قضاء عطلتك باقل الاسعار مع اقوى عروض رحلات الغردقة باعلى خدمة مقدمة من شركة اجازتنا

    ReplyDelete
  5. افضل خدمة من مؤسسة العتيق من تصميم مشبات باقل الاسعار واعلى الخدمات المميزة

    ReplyDelete
  6. today with
    pest control company you can get rid of any type of insects Easily just call us and we will help you
    Contact Us on phone number ::

    02-6650399

    To communicate through the website:

    http://www.mazayapestcontrol.com/

    ReplyDelete
  7. اهلا بكم فى صيانه ترين بالاسماعياليه
    http://maintenancetrineismailia.blogspot.com/

    ReplyDelete
  8. مرحبا بكم فى مركز صيانه وات ويل الاسماعيليه
    http://maintenanceandwhitewhaleismailia.blogspot.com/

    ReplyDelete
  9. اهلا بكم بى توكيل كاريازى بلا الاسماعيليه
    http://kiriaziismailia.blogspot.com

    ReplyDelete
  10. مرحبا بكم فى توكيل يورك بالاسماعيليه خدمات متميزه جدا
    http://yorkismailia.blogspot.com/

    ReplyDelete
  11. مركز صيانه امريكول بيرحب بكم ويجود خدمات جيدا وقطع الغيار اصلايه ويجود ضمان
    http://maintenanceamrakolismailia.blogspot.com

    ReplyDelete
  12. توكيل يونيون اير بيرحب بكم
    http://maintenanceunionairismailia.blogspot.com

    ReplyDelete
  13. مركز صيانه امريكول بيرحب بكم ويجود خدمات جيدا وقطع الغيار اصلايه ويجود ضمان
    http://maintenanceamrakolismailia.blogspot.com

    ReplyDelete
  14. مرحبا بكم فى توكيل كاريير ويوجد تخفيضات جيدا
    http://carriermaintenanceismailia.blogspot.com

    ReplyDelete
  15. اهلا بيكم توكيل شارب بى الاسماعيليه
    http://maintenancesharpismailia.blogspot.com/

    ReplyDelete
  16. مرحبا بيكم صيانه يورك بى اللساحل الاشمالى
    http://maintenanceyorknorthcoast.blogspot.com

    ReplyDelete
  17. تم افتتاح صيانه يونيون اير بل الساحل الشمالى اليوم
    http://maintenanceunionairnorthcoast.blogspot.com/

    ReplyDelete
  18. اليوم يتم خدمات متميزه جدا فى مركز صيانه ترين بى الساحل الشمالى
    http://maintenancetrinenorthcoast.blogspot.com

    ReplyDelete
  19. اليوم يوجد قطع غيار اصليه وضمان شامل صيانه كاريير بى الساحل الشمالى
    http://carriermaintenancenorthcoast.blogspot.com

    ReplyDelete
  20. مرحبا بيكم فى توكيل كاريير فى العين السخنه
    http://carrierainsukhna-ar.blogspot.com/

    ReplyDelete
  21. اهلا بيكم فى مركز صيانه يورك بى العين السخنه يوجد خدمات متميزه
    http://yorksokhnamaintenance.blogspot.com

    ReplyDelete
  22. اهلا وسهلا بيكم صيانه ترين فى بورسعيد
    http://trine-maintenance-in-portsaid.blogspot.com/

    ReplyDelete
  23. اليوم افتتاح صيانه يونيون اير بى بورسعيد يوجد ضمان
    http://unionerppursaidmaintenance.blogspot.com/

    ReplyDelete
  24. اهلا بيكم فى توكيل شارب بى بورسعيد يوجد ضمان وخدمات جيدا
    http://maintenance-sharp-in-portsaid.blogspot.com/

    ReplyDelete
  25. http://maintenance-amrakolinportsaid.blogspot.com/ صيانة امريكول
    http://yorkemaintenanceinportsaid.blogspot.com/ صيانة يورك فى مصر الوكيل الوحيد
    http://carrier-inport-said.blogspot.com/ صيانة كاريير بورسعيد
    http://kiriazimaintenance-inportsaid.blogspot.com/ كريازى بورسعيد
    http://toshibaportsaid.blogspot.com/ توشيبا
    http://zanussiinportsaid.blogspot.com/ زانوسى بورسعيد ترحب بكم
    https://franchisezanussiportsaid.wordpress.com/ زانوسى تتم الفتتاح لاهلى بورسعيد
    https://kiriazihireinportsaid.wordpress.com/ كريازى لان كريازى بورسعيد ترحب بكم

    ReplyDelete