SQL Server에서 실행되는 쿼리 나열각 데이터 포인트에서 다른 텍스트가있는 matplotlib 산점도

SQL Server에서 실행되는 쿼리 나열

---

MS SQL Server에서 현재 실행중인 쿼리 (Enterprise Manager 또는 SQL을 통해) 및 / 또는 누가 접속했는지를 나열하는 방법이 있습니까?

데이터베이스 서버 중 하나에서 매우 오래 실행되는 쿼리가 실행되고 있다고 추적하고 추적을 중지하고 싶습니다 (또는 계속 시작하는 사람).

---

SQL 2000 또는 SQL 2005 서버에서 가장 오래 실행되는 SPID가 표시됩니다.

select
    P.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

결과에서 주어진 spid에 대해 SQL이 실행되고 있는지 확인하려면 다음과 같이 사용하십시오.

declare
    @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = XXX -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    sys.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING(  text,
            COALESCE(NULLIF(@stmt_start, 0), 1),
            CASE @stmt_end
                WHEN -1
                    THEN DATALENGTH(text)
                ELSE
                    (@stmt_end - @stmt_start)
                END
        )
FROM ::fn_get_sql(@sql_handle)

---

SQL Server 2005 또는 2008을 실행하는 경우 DMV를 사용하여이를 찾을 수 있습니다.

SELECT  *
FROM    sys.dm_exec_requests  
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)  

• sys.dm_exec_requests 에 대한 추가 정보
• sys.dm_exec_sql_text 에 대한 추가 정보

---

You can run the sp_who command to get a list of all the current users, sessions and processes. You can then run the KILL command on any spid that is blocking others.

---

I would suggest querying the sys views. something similar to

SELECT * 
FROM 
   sys.dm_exec_sessions s
   LEFT  JOIN sys.dm_exec_connections c
        ON  s.session_id = c.session_id
   LEFT JOIN sys.dm_db_task_space_usage tsu
        ON  tsu.session_id = s.session_id
   LEFT JOIN sys.dm_os_tasks t
        ON  t.session_id = tsu.session_id
        AND t.request_id = tsu.request_id
   LEFT JOIN sys.dm_exec_requests r
        ON  r.session_id = tsu.session_id
        AND r.request_id = tsu.request_id
   OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL

This way you can get a TotalPagesAllocated which can help you figure out the spid that is taking all the server resources. There has lots of times when I can't even bring up activity monitor and use these sys views to see what's going on.

I would recommend you reading the following article. I got this reference from here.

---

There are various management views built into the product. On SQL 2000 you'd use sysprocesses. On SQL 2K5 there are more views like sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.

There are also procedures like sp_who that leverage these views. In 2K5 Management Studio you also get Activity Monitor.

And last but not least there are community contributed scripts like the Who Is Active by Adam Machanic.

---

Actually, running EXEC sp_who2 in Query Analyzer / Management Studio gives more info than sp_who.

Beyond that you could set up SQL Profiler to watch all of the in and out traffic to the server. Profiler also let you narrow down exactly what you are watching for.

For SQL Server 2008:

START - All Programs - Microsoft SQL Server 2008 - Performance Tools - SQL Server Profiler

Keep in mind that the profiler is truly a logging and watching app. It will continue to log and watch as long as it is running. It could fill up text files or databases or hard drives, so be careful what you have it watch and for how long.

---

SELECT
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
    p.program_name, text 
FROM
    sys.dm_exec_requests AS r,
    master.dbo.sysprocesses AS p 
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
    p.status NOT IN ('sleeping', 'background') 
AND r.session_id = p.spid

---

In the Object Explorer, drill-down to: Server -> Management -> Activity Monitor. This will allow you to see all connections on to the current server.

---

As a note, the SQL Server Activity Monitor for SQL Server 2008 can be found by right clicking your current server and going to "Activity Monitor" in the context menu. I found this was easiest way to kill processes if you are using the SQL Server Management Studio.

---

here is a query that will show any queries that are blocking. I am not entirely sure if it will just show slow queries:

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
JOIN      master..syslogins l ON p.sid = l.sid
WHERE     p.blocked = 0
AND       EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

---

The right script would be like this:

select 
p.spid, p.status,p.hostname,p.loginame,p.cpu,r.start_time, t.text
    from sys.dm_exec_requests as r, sys.sysprocesses p 
    cross apply sys.dm_exec_sql_text(p.sql_handle) t
    where p.status not in ('sleeping', 'background')
    and r.session_id=p.spid

---

You can use below query to find running last request:

SELECT
    der.session_id
    ,est.TEXT AS QueryText
    ,der.status
    ,der.blocking_session_id
    ,der.cpu_time
    ,der.total_elapsed_time
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est

Using below script you can also find number of connection per database:

SELECT 
    DB_NAME(DBID) AS DataBaseName
    ,COUNT(DBID) AS NumberOfConnections
    ,LogiName 
FROM sys.sysprocesses
WHERE DBID > 0
GROUP BY DBID, LogiName

For more details please visit: http://www.dbrnd.com/2015/06/script-to-find-running-process-session-logged-user-in-sql-server/

---

in 2005 you can right click on a database, go to reports and there's a whole list of reports on transitions and locks etc...

---

Try with this:

It will provide you all user queries. Till spid 50,it's all are sql server internal process sessions. But, if you want you can remove where clause:

select
r.session_id,
r.start_time,
s.login_name,
c.client_net_address,
s.host_name,
s.program_name,
st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st where r.session_id  > 50

---

SELECT 
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, t.text
FROM
    sys.dm_exec_requests as r,
    master.dbo.sysprocesses as p
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) t
WHERE
    p.status NOT IN ('sleeping', 'background')
AND r.session_id = p.spid

And

KILL @spid

---

Use Sql Server Profiler (tools menu) to monitor executing queries and use activity monitor in Management studio to see how is connected and if their connection is blocking other connections.

---

You should try very usefull procedure sp_whoIsActive which can be found here: http://whoisactive.com and it is free.

참고URL : https://stackoverflow.com/questions/941763/list-the-queries-running-on-sql-server

각 데이터 포인트에서 다른 텍스트가있는 matplotlib 산점도

---

산점도를 만들고 목록에서 다른 숫자로 데이터 포인트에 주석을 달려고합니다. 예를 들어 y 대 x를 플로팅하고 n의 해당 숫자로 주석을 달고 싶습니다.

y = [2.56422, 3.77284, 3.52623, 3.51468, 3.02199]
z = [0.15, 0.3, 0.45, 0.6, 0.75]
n = [58, 651, 393, 203, 123]
ax = fig.add_subplot(111)
ax1.scatter(z, y, fmt='o')

어떤 아이디어?

---

배열이나 목록을 취하는 플로팅 방법을 모르지만 annotate()의 값을 반복 하는 동안 사용할 수 있습니다 n.

y = [2.56422, 3.77284, 3.52623, 3.51468, 3.02199]
z = [0.15, 0.3, 0.45, 0.6, 0.75]
n = [58, 651, 393, 203, 123]

fig, ax = plt.subplots()
ax.scatter(z, y)

for i, txt in enumerate(n):
    ax.annotate(txt, (z[i], y[i]))

에 대한 많은 서식 옵션이 annotate()있습니다. matplotlib 웹 사이트를 참조하십시오 .

---

matplotlib 2.0 이전 버전에서는 ax.scatter마커없이 텍스트를 플롯 할 필요가 없습니다. 버전 2.0에서는 ax.scatter텍스트의 적절한 범위와 마커를 설정 해야 합니다.

y = [2.56422, 3.77284, 3.52623, 3.51468, 3.02199]
z = [0.15, 0.3, 0.45, 0.6, 0.75]
n = [58, 651, 393, 203, 123]

fig, ax = plt.subplots()

for i, txt in enumerate(n):
    ax.annotate(txt, (z[i], y[i]))

이 링크 에서 3D로 예제를 찾을 수 있습니다.

---

누구나 위의 솔루션을 .subplot () 대신 .scatter ()에 적용하려는 경우,

다음 코드를 실행하려고했습니다.

y = [2.56422, 3.77284, 3.52623, 3.51468, 3.02199]
z = [0.15, 0.3, 0.45, 0.6, 0.75]
n = [58, 651, 393, 203, 123]

fig, ax = plt.scatter(z, y)

for i, txt in enumerate(n):
    ax.annotate(txt, (z[i], y[i]))

그러나 "반복 불가능한 PathCollection 객체의 압축을 풀 수 없습니다"라는 오류가 발생했습니다. 오류는 특히 코드 라인 fig, ax = plt.scatter (z, y)를 가리 킵니다.

결국 다음 코드를 사용하여 오류를 해결했습니다.

plt.scatter(z, y)

for i, txt in enumerate(n):
    plt.annotate(txt, (z[i], y[i]))

.scatter ()와 .subplot () 사이에 더 잘 알고 있어야 할 차이점이 있다고 생각하지 않았습니다.

---

사용할 수도 있습니다 pyplot.text( 여기 참조 ).

def plot_embeddings(M_reduced, word2Ind, words):
""" Plot in a scatterplot the embeddings of the words specified in the list "words".
    Include a label next to each point.
"""
for word in words:
    x, y = M_reduced[word2Ind[word]]
    plt.scatter(x, y, marker='x', color='red')
    plt.text(x+.03, y+.03, word, fontsize=9)
plt.show()

M_reduced_plot_test = np.array([[1, 1], [-1, -1], [1, -1], [-1, 1], [0, 0]])
word2Ind_plot_test = {'test1': 0, 'test2': 1, 'test3': 2, 'test4': 3, 'test5': 4}
words = ['test1', 'test2', 'test3', 'test4', 'test5']
plot_embeddings(M_reduced_plot_test, word2Ind_plot_test, words)

참고 URL : https://stackoverflow.com/questions/14432557/matplotlib-scatter-plot-with-different-text-at-each-data-point