Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Where are all those SQL Server sessions from?

After launching SSMS (2008 R2) on my dev machine, which I launch with

"D:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -nosplash -S localhost -d testdata

without doing anything,
in Activity Monitor I observe some sessions (TestData is my default database)

alt text

Details of session 51:

select @@spid;
select SERVERPROPERTY('ProductLevel');

Details of session 52:

DBCC INPUTBUFFER(52)  

Details of session 53:

SELECT
CAST(serverproperty(N'Servername') AS sysname) AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/JobServer' AS [Urn]
ORDER BY
[Name] ASC

Details of session 54:

SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_request_count bigint;
DECLARE @current_collection_time datetime;
DECLARE @current_request_count bigint;
DECLARE @batch_requests_per_sec bigint;
DECLARE @interval_sec bigint;

-- Get the previous snapshot's time and batch request count
SELECT TOP 1 @previous_collection_time = collection_time, @previous_request_count = request_count 
FROM #am_request_count
ORDER BY collection_time DESC;

-- Get the current total time and batch request count
SET @current_collection_time = GETDATE();
SELECT @current_request_count = cntr_value 
FROM sys.sysperfinfo
WHERE counter_name = 'Batch Requests/sec' COLLATE Latin1_General_BIN;

SET @interval_sec = 
    -- Avoid divide-by-zero
    CASE
        WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) = 0 THEN 1
        ELSE DATEDIFF (second, @previous_collection_time, @current_collection_time)
    END;

-- Calc the Batch Requests/sec rate for the just-completed time interval. 
SET @batch_requests_per_sec = (@current_request_count - @previous_request_count) / @interval_sec;

-- Save off current batch count
INSERT INTO #am_request_count (collection_time, request_count) 
VALUES (@current_collection_time, @current_request_count);

-- Return the batch requests/sec rate for the just-completed time interval. 
SELECT ISNULL (@batch_requests_per_sec, 0) AS batch_requests_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_request_count WHERE collection_time < @current_collection_time; 

If to launch SSMS (connecting to nameless instance by Windows authentication) without options then I do not have session corresponding to above show as 52

What had I done to have all those sessions to be launched ?
I just do not remember all what I had been doing in my dev SQL Server 2008 R2 before...

Update:
I restored the same options to SSMS.exe (-nosplash -S localhost -d testdata), relaunched SSMS and now I have different details corresponding to session 51 details:

DECLARE @edition sysname; 
SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); 
select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud' 

Why did not I have it before?

like image 813

People also ask

How can I see all SQL instances on my network?

Get-ChildItem cmdlet To list all the SQL instances, navigate to the root directory and run the Get-Childitem to get the list. Note: The path SQLSERVER:\SQL\<ServerName> has all the installed SQL instances of the listed server.

What database is SQL Server session stored?

The session state is stored in the ASPState database. The advantage of this method is that the data is persisted even if you restart the SQL server. Custom storage: Both the session state data and the stored procedures are stored in a custom database.

Where are SQL Server databases located?

The SQL Server database files are typically located on the same server as the Blackbaud Management Console (Blackbaud Management Console). However, if using SQL Server Standard or Enterprise Edition, the database files can be located on the server where SQL Server is running.


1 Answers

Those sessions are being used to pull data into the Activity Monitor.

like image 175
Joe Stefanelli Avatar answered Oct 19 '22 08:10

Joe Stefanelli