[Python] MSSQL 실행쿼리 모니터링

파이썬으로 MSSQL 실행쿼리 모니터링

 

반복문이나 특정 리스트의 요소값을 이용, 조건문을 추가하여 모니터링도 가능하다.

 

import pymssql

conn = pymssql.connect(host='', user='', password='', database='')
cur = conn.cursor()
cur.execute('''SELECT sqlt.TEXT, reqs.session_id, reqs.status, reqs.command, reqs.cpu_time, reqs.total_elapsed_time
FROM sys.dm_exec_requests reqs CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqlt''')

rows = cur.fetchall()

for r in rows:
    print('-'*150)
    print('session_id : %s' %r[1])
    print('status : %s' % r[2])
    print('command : %s' % r[3])
    print('cpu_TM : %s' % r[4])
    print('total_Elapsed_TM : %s' % r[5])
    print('query : %s' % r[0])

cur.close()
conn.close()

------------------------------------------------------------------------------------------------------------------------------------------------------
session_id : 53
status : running
command : SELECT
cpu_TM : 0
total_Elapsed_TM : 0
query : SELECT sqlt.TEXT, reqs.session_id, reqs.status, reqs.command, reqs.cpu_time, reqs.total_elapsed_time

FROM sys.dm_exec_requests reqs CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqlt