Databases Reference
In-Depth Information
LISTING 15-22:
Login count information
-- Get logins that are connected and how many sessions they have
SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);
-- This can help characterize your workload and
-- determine whether you are seeing a normal level of activity
This query is one way to gauge whether you are seeing a normal level of activity on your database
server. You can look at the number of connections for each login (especially if you use application-
level logins) to determine whether you are seeing a normal workload. For example, if one of your
logins typically sees about 150 active connections but you are seeing 350 connections for that login,
then you probably have good reason to suspect that your workload has changed and your database
server may be working harder than usual.
This query is also good for troubleshooting and coni rming database connectivity. If you are seeing
active connections for particular logins, then you know that at least some web or application servers
are able to connect using that login. You can also use the old DBA trick of using a Microsoft Data
Link (.
udl
i le) to verify connectivity from a remote server.
You can easily create a Microsoft Data Link i le on any machine running Windows 2000 or newer
by creating a new, empty text i le and then changing the i le extension from .txt to .udl. Then you
can double-click on the .udl i le and you will open a Data Link Properties dialog. After you enter
valid login credentials for the database server and database that you want to connect to, you can
click on the Test Connection button to verify that you can make a database connection to that
database on that server. This is a good troubleshooting tool that you can use on a web server or
application server that does not require any development tools to be installed to verify connectivity
from that server to the database server.
Next, using the query shown in Listing 15-23, you will take a look at some current task and
pending I/O count information.
LISTING 15-23:
Average Task Count information
-- Get Average Task Counts (run multiple times)
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
-- Sustained values above 10 suggest further investigation in that area
-- High Avg Task Counts are often caused by blocking or other resource contention
-- High Avg Runnable Task Counts are a good sign of CPU pressure
-- High Avg Pending DiskIO Counts are a sign of disk pressure
Search WWH ::
Custom Search