What version and service pack am I running?

A quick tip- how to find out what version and service pack of SQL Server a server is running:

SELECT SERVERPROPERTY(‘ProductVersion’) AS SqlServerVersion, SERVERPROPERTY( ‘ProductLevel’) AS ServicePack

Here are the versions and their corresponding build numbers for 2000 and 2005.

RTM	2000.80.194.0
SQL Server 2000 SP1	2000.80.384.0
SQL Server 2000 SP2	2000.80.534.0
SQL Server 2000 SP3	2000.80.760.0
SQL Server 2000 SP3a	2000.80.760.0
SQL Server 2000 SP4	2000.8.00.2039

RTM	2005.90.1399
SQL Server 2005 Service Pack 1	2005.90.2047
SQL Server 2005 Service Pack 2	2005.90.3042

Find all tables without triggers

In our production environment, every user table has an auto-generated trigger that facilitates auditing.  Since trigger generation is an automated process, it occasionally fails to run for a plethora a reasons; this it is a good practice to periodically scan for tables that are missing triggers.  The following query will return tables that fit this condition by using a LEFT JOIN between sysobjects and itself.

SELECT   sys1.name
FROM     sysobjects sys1
LEFT JOIN sysobjects sys2
ON sys1.id = sys2.parent_obj
AND sys2.xtype = ‘TR’
WHERE    sys2.name IS NULL
AND sys1.xtype = ‘U’
ORDER BY sys1.name

max worker threads Options

What does it do?

The max worker threads option controls the number of worker threads available to SQL Server.  One thread typically handles one connection, and it used to handle a batch of SQL statements as the arrive from the client.

Viewing current settings:

Run EXEC sp_configure, and look at the ‘max worker threads’ option.  If you only see a limited list of options, you need to enable the ’show advanced option’ setting, followed by RECONFIGURE.

EXEC sp_configure ’show advanced option’, ‘1′;
RECONFIGURE;
EXEC sp_configure;

As long as the number of connections is less than or equal to the number of threads available, each connection will get a single thread.  After that,  SQL Server will create a pool of worker threads to handle a larger number of clients.

Suggested settings:

For a 32 bit system, Microsoft recommends:

<= 4 processors = 256 max worker threads
8 processors = 288 max worker threads
16 processors = 352 max worker threads
32 processors = 480 max worker threads

SQL Server Interview Questions

In case you’re looking for some quick questions to screen a prospective candidate working with SQL Server, here is a link to some SQL Server Interview Questions (from SQLAuthority.com).

Finding the space used by a particular table

A quick tip today: how to find the space used by a particular table:

EXEC sp_spaceused 'tCompany'
GO