SQLTeam.com | Weblogs | Forums

Sys.sysprocesses running every 5 second


#1

one of my co workers wants to use excel with a sql connection and run sys.sysprocesses to run every 10 seconds to check for table lock is this process safe?


#2

This can be achieved by creating SQL connection in excel and execute the query.
Otherwise you can write a excel macro to achieve the task


#3

is this process safe for the SQL server


#4

It is a system view. It is safe to call.


#5

and have it refresh every 10 seconds?


#6

Is it safe to delete columns in excel when your connected to a SQL connection from that sheet?


#7

If it is reading and not trying to update, it is safe. However, when you try to refresh next time, the columns might get restored again, depending on how you are populating the excel sheet.

If you are trying to delete columns in the sys.sysprocesses that attempt will fail, so the macro or connection would throw an error.


#8

ok thanks but will running sys.sysprocesses every ten seconds kill the SQL server?


#9

No, it won't kill your server. Try the following query in an SSMS query window.

SET  STATISTICS IO ON;
SET  STATISTICS TIME ON;
SELECT * FROM sys.sysprocesses

The messages window will show you some info on how much time and how much IO it took. You will see perhaps a few milliseconds time and no IO. Even that time is probably used for sending the data to your client. You can test that using the following:

SET FMTONLY ON
SET  STATISTICS IO ON;
SET  STATISTICS TIME ON;
SELECT * FROM sys.sysprocesses

When you are done, turn off the diagnostics using the following, or you will not see any outputs in that query window.

SET FMTONLY OFF
SET  STATISTICS IO OFF;
SET  STATISTICS TIME OFF;