A simple query to check for server liveliness

Question

In order to the determine the 'liveliness' of a remote SQL-server, I need to periodically address the server with some query.

I am looking for a query which will generate minimal server work, and minimal communications overhead (both for the query and the reply).

The query should work on SQL-Server 2005 and 2008 at least, and should not assume existance of some database or table, and should not generate errors.

Answer 1

select count(*) from "smallest table you have access to"

Answer 2

SELECT 1+1

though you should be able to check whether the server is responding without performing a query.

Answer 3

How about:

select 1

Answer 4

select spid from master..sysprocesses

Answer 5

SELECT GetDate()

Answer 6

Andrejs Cainikovs answer of select 1 is good. No database or table dependencies, minimal data, etc.

A couple of days ago, I ran a SQL Profiler on a SQL Server 2005 server, and one of the SQL Statements that I saw every few secons was: SELECT N'Testing Connection...' which has the advantage of documentation for someone observing the server. So maybe something like:

select 'Testing connection from server Y for application X'

A little more data would need to be put on the wire, but it would allow anyone profiling the server to know exactly why those selects were showing up.

Answer 7

SELECT @@SERVERNAME as ServerName