[Dev Tip] SQL Server: Understanding and Controlling Connection

Thấy hay, lưu lại 🙂

sudo man

I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor.

The server hosts more than 50 databases that serve an ASP.NET application hosted on some servers in a web farm. These servers issue connections to the databases in a distributed manner to balance the web application load. I tried to discover what these connections were doing and to what databases they were connected. Connections grouped by database:

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid != @@spid
group by  db_name(dbid)
order by count(*) desc

This showed some databases having more than 300 connections associated with them.

What about logins used?

select loginame , nt_username, count(*) 'Connections count' from master..sysprocesses…

Xem bài viết gốc 2 430 từ nữa

Trả lời

Điền thông tin vào ô dưới đây hoặc nhấn vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Đăng xuất /  Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất /  Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất /  Thay đổi )

Connecting to %s