What does parallelism look like in SQL? How would you know when SQL is doing it parallel style? When looking at sys.sysprocesses you might see something like the below,


Here we see many instances of the CXPACKET waittype running seemingly from one spid. The CXPACKET waittype pops up whenever a thread running in parallel is waiting on another thread to complete before the overall process can complete. If you’re seeing CXPACKETs then you’re seeing parallelism. If you see CXPACKETs persist, then there might be an issue slowing down your query, but thats for another story . . . (more info on this waittype here)

The most obvious way to see parallelism is in the query plan. As shown below,


You see this, you’re seeing parallelism :) Painless.

Its important for security to monitor unused logins on SQL. Only logins which need to be there should be there. There are a number of ways to monitor the which logins are using used but these methods vary across the versions of SQL, it can be handy to have a basic standard method which a DBA is confidence possesses interoperability across all versions of SQL. Below i’ve outlined a really simple way the sys.sysprocesses table can be used to capture logon data which could be used to find logins which aren’t being used

1) Create a table to hold the audit data

create table record_login_user_behaviour (

loginame         varchar(50 ),
cmd              varchar(200 ),
database_name    varchar(50 ),
frequency        int,
date_captured    datetime


2) Create a stored procedure to capture login names of currently running processes. I included a count of the commands being used and the time, you may want to tweak for your needs.

USE <<database>>



CREATE procedure mon_record_login_user_behaviour

set nocount on

insert into record_login_user_behaviour
select distinct loginame,cmd , DB_NAME (DBID) as database_name , COUNT(loginame ) as frequency, GETDATE() as date_captured
from sys .sysprocesses
where loginame <> 'sa'
group by loginame, cmd , dbid

3) Attach capture procedure to job to regularly grab what logins are being used.

4) The script below will compare what logins have been captured as being used against the logins on SQL and produces a list of what what logins haven’t been used.

select distinct name
from sys .server_permissions as spe
inner join sys. server_principals as spr
on spe. grantee_principal_id  = spr .principal_id
where name not in (select distinct loginame from record_login_user_behaviour )

Any ways of improving this activity would be gratefully recieved :-)