SQL Blocking

When running SQL SELECT statements, include the (NOLOCK) statement.

Right:

SELECT cst_key, cst_sort_name_dn FROM dbo.co_customer (NOLOCK) WHERE...

Wrong:

SELECT cst_key, cst_sort_name_dn FROM dbo.co_customer WHERE...

In .NET code. Right:

string szSQL = "select cst_key from dbo.co_customer " + DataUtils.NoLock() + " where cst_key='" + szKey + "'";

Wrong:

string szSQL = "select cst_key from dbo.co_customer where cst_key='" + szKey + "'";

In SQL: Look in SP, View, UDF, scheduled job

In metadata, look in Visibility SQL, child form SQL, and eWeb List Content Details.

Use DBO qualifier

Right:

SELECT cst_key, cst_sort_name_dn FROM dbo.co_customer (NOLOCK) WHERE...

execute dbo.SomeSPName

Wrong:

SELECT cst_key, cst_sort_name_dn FROM co_customer (NOLOCK) WHERE...

execute SomeSPName

Optimizing SQL Jobs

If you have a SQL Job that works with massive amounts of data (example, update 100K rows in the customer extender table with a denormalized field, or insert tons of records in some log table) then you can run into problems with the SQL transaction log filling up and crashing. To avoid this, add these lines into the SQL that your SQL Job runs:

declare @dbname sysname
SELECT @dbname=db_name()
 
exec ('ALTER DATABASE '+@dbname+' SET RECOVERY SIMPLE')
 
-- do some work...
 
exec ('ALTER DATABASE '+@dbname+' SET RECOVERY FULL')