Pages

Friday 21 September 2012

SQL Server: Part 2 : Approaching Database Server Performance Issues

In the Part 1, we have seen how quickly we can check the runnable task and I/O pending task on an SQL server instance. This  is very light weight script and it will give the result even if the server is under pressure and will give an over all state of the server at that moment.

The next step (Step2)  in my way of diagnosing is to check the session that are waiting of any resources. Below script will help us. This query required a function as prerequisite,  which will help us to display the SQL server agent job name if the session started by SQL server agent.

/*****************************************************************************************
           PREREQUISITE FUNCTION
******************************************************************************************/

USE MASTER
GO 
CREATE FUNCTION ConvertStringToBinary  ( @hexstring  VARCHAR(100)
)  RETURNS BINARY(34)  AS
BEGIN

   RETURN
(SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )''varbinary(max)')
  
FROM (SELECT CASE SUBSTRING(@hexstring, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos))  

END
/***************************************************************************************
STEP 2: List the session which are currently waiting for resource
****************************************************************************************/

SELECT node.parent_node_id AS Node_id,
es.HOST_NAME,
es.Login_name,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
        
(
          
SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=
          MASTER
.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
          )
    
ELSE es.program_name END  AS [Program Name] ,

DB_NAME(er.database_id) AS DatabaseName,
er.session_id
wt.blocking_session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.NoThread ,
er.command,
er.status,
er.wait_resource,
er.open_transaction_count,
er.cpu_time,
er.total_elapsed_time AS ElapsedTime_ms,
er.percent_complete ,
er.reads,
er.writes,
er.logical_reads,
wlgrp.name AS ResoursePool              ,
SUBSTRING   (sqltxt.TEXT,(er.statement_start_offset/2) + 1,          
            ((
CASE WHEN er.statement_end_offset = -1          
            
THEN LEN(CONVERT(NVARCHAR(MAX), sqltxt.TEXT)) * 2          
            
ELSE er.statement_end_offset          
            
END - er.statement_start_offset)/2) + 1) AS [Individual Query]

sqltxt.TEXT AS [Batch Query]                
FROM (SELECT session_id, SUM(wait_duration_ms) AS 
wait_duration_ms,wait_type,blocking_session_id,COUNT(*) AS NoThread 
FROM  SYS.DM_OS_WAITING_TASKS  GROUP BY session_id, wait_type,blocking_session_id) wt 
INNER JOIN SYS.DM_EXEC_REQUESTS  er ON wt.session_id=er.session_id INNER JOIN SYS.DM_EXEC_SESSIONS es ON es.session_id= er.session_id
INNER JOIN SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS wlgrp ON wlgrp.group_id=er.group_id          
INNER JOIN  (SELECT  os.parent_node_id ,task_address FROM SYS.DM_OS_SCHEDULERS  OS INNER JOIN 
SYS.DM_OS_WORKERS  OSW ON OS.scheduler_address=OSW.scheduler_address 
WHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node   
ON node.task_address=er.task_address
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) AS sqltxt
WHERE sql_handle IS NOT NULL AND wt.wait_type NOT IN ('WAITFOR','BROKER_RECEIVE_WAITFOR')
GO

The Description of the columns in the result are given below. 


Column Name Description
Node Id NUMA node id . Can be mapped to the node id of the scheduler query.
Host_Name Name of the computer from the connection is originated.
Login Name Login used in the session to connect the database server
Program Name Name of the program/application using this session. You can set the application name in the connection string. If this session is part of SQL server agent job, it will show the job name 
Database Name Current database  of the session
Session Id The session id
Blocking Session id Session id blocking statement 
wait_duration_ms Total wait time for this wait type, in milliseconds. This time is inclusive of signal wait time 
wait_type Name of the wait type like SLEEP_TASK,CXPACKET etc
No of Thread No of threads running on this session. If the session is in parallel execution
Command Identifies the current type of command that is being processed like Select,insert,update,delete etc
Status Status of the request. This can be of the following: Background,Running,Runnable,Sleeping and Suspended
Wait Resource  Resource for which the request is currently waiting
Open Transaction count Number of transaction opened in this session
Cpu Time CPU time in milliseconds that is used by the request.
Total Elapsed Time Total time elapsed in milliseconds since the request arrived
Percent_Complete Percent of work completed for certain operations like backup,restore
rollback etc.
Reads Number of reads performed by this request.
Writes Number of writes performed by this request.
logical_reads Number of logical reads performed by this request.
ResoursePool Name of of Resource Governor Pool
Individual Query current statement of the batch running on this session.
Batch Query Current batch (procedure/set of sql statement) running on this session.

If there is a session with very long wait_duration_ms and  not blocked by any other session and  not going away from the list in the subsequent execution of the same query, I will look into the program name,host name,login name and the statement that is running which will give me an idea about the session.Based on all these information, I might decide to kill that session and look into the implementation of that SQL batch. If the session is blocked, I will look into the blocking session using a different script which I will share later.(Refer this post)

The next step (Step 3)  is to list all session which are currently running on the server. I use below query to do that.

/***************************************************************************************
STEP 3: List the session which are currently waiting/running
****************************************************************************************/
SELECT node.parent_node_id AS Node_id,

es.HOST_NAME,
es.login_name,
CASE WHEN es.program_name LIKE '%SQLAgent - TSQL JobStep%' THEN
(SELECT 'SQL AGENT JOB: '+name FROM msdb..sysjobs WHERE job_id=ADMIN.DBO.ConvertStringToBinary (LTRIM(RTRIM((SUBSTRING(es.program_name,CHARINDEX('(job',es.program_name,0)+4,35)))))
)
ELSE es.program_name END  AS program_name ,

DB_NAME(er.database_id) AS DatabaseName,
er.session_id
wt.blocking_session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.NoThread ,
er.command,
er.status,
er.wait_resource,
er.open_transaction_count,
er.cpu_time,
er.total_elapsed_time AS ElapsedTime_ms,
er.percent_complete ,
er.reads,er.writes,er.logical_reads,
wlgrp.name AS ResoursePool              ,
SUBSTRING (sqltxt.TEXT,(er.statement_start_offset/2) + 1,                
((CASE WHEN er.statement_end_offset = -1                
THEN LEN(CONVERT(NVARCHAR(MAX), sqltxt.TEXT)) * 2                
ELSE er.statement_end_offset                
END - er.statement_start_offset)/2) + 1) AS [Individual Query],
sqltxt.TEXT AS [Batch Query]                
FROM 
SYS.DM_EXEC_REQUESTS  er INNER JOIN SYS.DM_EXEC_SESSIONS es ON es.session_id= er.session_id
INNER JOIN SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS wlgrp ON wlgrp.group_id=er.group_id          
INNER JOIN  (SELECT  os.parent_node_id ,task_address FROM SYS.DM_OS_SCHEDULERS  OS 
INNER JOIN SYS.DM_OS_WORKERS  OSW ON OS.scheduler_address=OSW.scheduler_address
WHERE os.status='VISIBLE ONLINE' GROUP BY os.parent_node_id ,task_address ) node ON node.task_address=er.task_address
LEFT JOIN 
(SELECT session_id, SUM(wait_duration_ms) AS 
wait_duration_ms,wait_type,blocking_session_id,COUNT(*) AS NoThread 
FROM  SYS.DM_OS_WAITING_TASKS  GROUP BY session_id, wait_type,blocking_session_id) wt 
ON wt.session_id=er.session_id
CROSS apply SYS.DM_EXEC_SQL_TEXT(er.sql_handle) AS sqltxt
WHERE sql_handle IS NOT NULL AND ISNULL(wt.wait_type ,'') NOT IN 
('WAITFOR','BROKER_RECEIVE_WAITFOR')
ORDER BY er.total_elapsed_time DESC

GO


The columns are same as we discussed in step 2 . I used to analyse the sessions with  more total_elapsed_time and take appropriate actions like killing the session and look into the implementation. In most of the scenario (where server was running perfectly but all off sudden it become standstill) , I will be able fix the issue by following these steps.  In the next part let us discuss  about blocking session and session with open transaction which is not active.


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

45 comments:

  1. Nice article. What's the different between step 2 and step 3? The queries look the same with minor difference.

    ReplyDelete
  2. Try using minidba from www.minidba.com to make the most of sql server dmvs without having to roll your own code. #hugetimesaving

    ReplyDelete



  3. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

    ReplyDelete
  4. I want to say that all the information you have given In this post is awesome. Great and nice blog thanks for sharing you Knowledge.
    Oracle Fusion Financials Training

    ReplyDelete
  5. Interesting post! This is really helpful for me. I like it! Thanks for sharing!
    seo lüdenscheid

    ReplyDelete
  6. سماك هو برنامج محاسبة عبر الإنترنت للشركات الصغيرة والمتوسطة لإدارة أعمالهم وزيادة الإنتاجية. لدى سماك خمس

    وحدات رئيسية لإدارة العمليات التجارية لأي مؤسسة. وقد استفادت برامج محاسبة من سماك العديد من المؤسسات الصغيرة

    والمتوسطة، وتحديدا الشركات الناشئة عن طريق خفض التكاليف السنوية والسماح لهم بالتركيز الكامل على تطوير الأعمال الأساسية وعمليات الأعمال بدلا من أنها عثرت في مشاكل البنية التحتية لتكنولوجيا المعلومات

    والقضايا.


    ومن الفوائد الرئيسية الأخرى برامج محاسبة السحابية من سماك أن الترقيات مجانية تماما ومتكررة وفورية ولكن من ناحية

    أخرى فإن دورات تطوير البرمجيات داخل المؤسسة طويلة جدا بالمقارنة مع برمجيات المحاسبة المستندة إلى الحوسبة السحابية.

    تحميل برامج المحاسبة
    افضل برنامج محاسبة
    برامج محاسبة
    برنامج محاسبة
    نظام نقاط البيع
    محاسبة مالية
    سماك

    ReplyDelete
  7. tutu app
    tutu app download
    tutu app free
    tutuapp vip
    TuTu App is an emerging popular app store alternative available on both platforms, iOS and Android.

    ReplyDelete
  8. مخاطر الحشرات كثيرة ولو تريد حل قوي وفعال في التخلص من الحشرات في منزلك ليس امامك افضل من شركة مكافحة حشرات بجدة وفي حاجة إلى شركة رائدة تخلصك من الحشرات المنتشرة حولك فأليك الأن أقوى شركات منطقة جدة ومكة والطائف شركة مكافحة حشرات بجدة وكذلك في مكة المكرمة لدينا شركة مكافحة حشرات بمكة المكرمة ولدينا شركة رش مبيدات بالطائف متخصصة و شركة مكافحة حشرات بالطائف تعمل على مكافحة الحشرات المختلفة في المنازل والمزارع والحدائق والشوارع والفلل والقصور وغيرها من الأماكن المختلفة حيث توفر لك الخدمات اللازمة في حل مشاكلك الصعبة المتعلقة بالحشرات المنزلية.

    ReplyDelete
  9. تنظيف المنازل والبيوت من المهام الصعبة على ربات البيوت ونحن نقدم لكم تلك الخدمات
    شركة تنظيف منازل بجدة
    شركة تنظيف منازل بمكة
    افضل شركة تنظيف بجدة
    شركة تنظيف منازل

    ReplyDelete
  10. I found such amazing information on this blog. Visit Thecorporategift for Promotional Products and Corporate Diwali Gift.
    Promotional Products

    ReplyDelete
  11. Nice blog, keep it up for more updates. Visit Sarswati Enterprises for Flip off Seals Machinery and ROPP Caps Making Machinery Manufacturer in Delhi, India.
    ROPP Caps Making Machinery

    ReplyDelete
  12. Amazing blog, thanks for sharing with us. Book Shimla Manali Tour Package from Delhi at best price.
    Shimla Manali Tour Package from Delhi

    ReplyDelete
  13. Nice blog, thank you so much for sharing this. Get Noble IVF wide array of services with advance facility and well experienced fertility doctors.
    IVF Centre in Aligarh

    ReplyDelete
  14. I never comment on blogs but your article is so best that I never stop myself to say something about it. You’re amazing Man, I like it WP-Database Issues ... Keep it up

    ReplyDelete
  15. hotmail.com signup


    full information with FAQs are given.......

    ReplyDelete
  16. Amazing! This article is jam-pressed brimming with helpful data. The focuses made here are clear, succinct, meaningful, and powerful. I actually like your composing style.


    SEO services in kolkata
    Best SEO services in kolkata
    SEO company in kolkata
    Best SEO company in kolkata
    Top SEO company in kolkata
    Top SEO services in kolkata
    SEO services in India
    SEO copmany in India

    ReplyDelete
  17. Identify which of the clients being mentioned as references are actually referencable Salesforce training in Hyderabad

    ReplyDelete
  18. It's fantastic that you are getting ideas from this post as well as from our discussion made here.I have enjoyed reading your articles. It is well written. It looks like you spend a large amount of time and effort in writing the blog. I am appreciating your effort.Augurs GmbH

    ReplyDelete
  19. مدونة ممتازة ، لقد أحببتها كثيرًا لدرجة أنني عدت إلى هنا لتقديم ملاحظات. شكرا على كل حال.
    تحقق أيضًا من COC MOD Apk

    ReplyDelete
  20. Thank you for the detailed article on Database Server Performance Issues. I appreciate it. Download COC Mod APK

    ReplyDelete
  21. I am very impressed with your post, thanks for sharing. Keep sharing stuff like this in future. Regards COC MOD Apk.

    ReplyDelete
  22. I want to say that all the information you have given In this post is awesome. Great and nice blog thanks for sharing you Knowledge.

    ReplyDelete
  23. This was very helpful in my school project, Thanks for sharing this with me. GBWhatsapp 2022 APK

    ReplyDelete
  24. It's definitely the most important blog for me right now. You have shared very helpful blog on SQL that i was having issue with. Thanks a lot. fmwhatsapp

    ReplyDelete
  25. CSS Founder Pvt. Ltd. is known as the best website designing company in Ghaziabad. You can visit our website so that you can get a uniqe and cost-effective website from us. We are located in Dubai, you can visit here any time.

    ReplyDelete
  26. We Carry More Than Just Good Coding Skills
    Solutions that exceed your expectations with Phenix System
    Perfect Solution for your business!

    ReplyDelete
  27. Phenix offers the ability to monitor your business wherever you are, with powerful mobile applications

    ReplyDelete
  28. This comment has been removed by the author.

    ReplyDelete
  29. فينيكس
    يدعم فينيكس امكانية طباعة وقراءة الباركود البسيط ,والمدمج الذي يضم مجموعة من مواصفات المادة, كما يدعم امكانية تعدد الباركود على مستوى المادة وواحدات المادة

    ReplyDelete
  30. Thank you for sharing this useful blog. I like it.
    https://techupnew.com/ffh4x-injector/
    you can check out my new blog

    ReplyDelete
  31. Thanks for sharing valuable and informative piece of content.
    Software testing Training Course in Ghaziabad

    ReplyDelete
  32. Buen artículo, espero que escribas más artículos. También quiero presentarles la página https://apktodo.net/es/ para que se diviertan.

    ReplyDelete