Two Hoots Software SQL Tips

Try to use derived tables over temporary tables to gain better performance. A derived table is the result of using another SELECT statement in the FROM clause of a SELECT statement. i.e. SELECT MIN(Salary) FROM (SELECT TOP 5 Salary FROM Employees ORDER BY Salary Desc)
If real-time replication is not required then don't use continuous replication. Schedule replication to occur at regular intervals to reduce server overhead.
If you are not replicating to Microsoft Jet 4.0 databases then turn this feature off to use the faster BCP native format for the Snapshot Agent.
Avoid replicating TEXT, NTEXT or IMAGE columns to reduce overhead.
In SELECT statements only return fields you require and don't use 'SELECT * FROM table'. In addition it prevents the use of covered indexes impacting query performance.
Triggers use two special tables called inserted and deleted. The inserted table contains the data of the insert prior to being committed to the database. Likewise the deleted table contains data before is it deleted from the database. Both tables are used when an update statement is issued. The inserted table contains the new data and the deleted table contains the data being updated.
Use the T-SQL Debugger Visual Basic Add-In to debug Stored Procedures. From Visual Basic's Add-Ins Menu select T-SQL Debugger.
There are two kinds of error in SQL Server, fatal and non-fatal. Fatal errors cause a stored procedure to abort processing and terminate the connection. Non fatal errors continue on the line of code that follows the one that caused the error.
The @@ERROR system function contains the error ID of the last SQL statement executed. It has a value of 0 when the statement is successful. You need to check its value immediately after the statement to be checked as it's value is reset on the next statement.
Use the RAISERROR statement to produce ad-hoc error messages.
Use the following script to detail foreign key constraints on the specified table.

SELECT object_name(constid) AS constraint_name,
object_name(fkeyod) AS table_name,
col_name(fkeyid, fkey) AS column_name,
object_name(rkeyid) AS referenced_table_name,
col_name(rkeyid, rkey) AS referenced_column_name,
FROM sysforeignkeys
WHERE object_name(fkeyid) = 'Table Name Goes Here'
ORDER BY constraint_name, table_name, referenced_table_name, keyno

Use the following script to detail the name of the constraints defined on the specified table.

SELECT constraint_name,
column_name,
ordinal_position,
FROM information_scheme.key_column_usage
WHERE constraint_catalog=db_name()
AND table_name = 'Table Name Goes Here'
ORDER BY constraint_name, ordinal_position

When choosing a primary key for a table, don't select a field that can be null, stick to a single column if possible, choose a column with no embedded spaces or special characters or differential capitalisation, either integer or fixed length character data types as SQL server has to decompress variable length character data before processing, choose a field that never changes.
An orphaned session is a session that remains open on the server then the client application has been disconnected. Execute the sp-lock procedure to find locks and the spid. A spid value of -2 indicates a connectionless or orphaned transaction. To find the process locking the records select records from the sysprocesses for the spid in question. Use the KILL command to kill the process.
To stop queries returning the number of rows affected use SET NOCOUNT ON. This adds a significant performance improvement. Turning this option on eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.
Standardise on a coding and naming convention, be consistent and stick to it.
As with all programming use lots of comments to aid readability.
Don't use wildcard characters such as '%' at the beginning of the search string as it results in a full table scan.
Also avoid using not equals operators such as <> and NOT as they result in table and index scans.
Don't prefix your stored procedures with 'sp_' as SQL looks in the Master database first. An 'sp_' procedure is reserved for system stored procedures.
Use the CHAR data type when the field can't be set to NULL. If it is set to NULL it still takes the same space. So for example a variable CHAR(100) will still be 100 bytes when Null.
Always name your columns in am INSERT statement to avoid problems when adding or dropping a column.
Add a @Debug parameter to stored procedures. This can be a BIT data type. When passing a value of 1, print all the intermediate results, variable values using SELECT or PRINT statements, and print nothing when 0 is passed. This helps debug stored procedures.
Use the Xp_sendmail utility to send e-mail messages directly from SQL Server. It's syntax is Xp_sendmail 'name@company.com', 'message'.
When creating an index with a composite key ensure that the most selective columns are leftmost in the key. (A composite key is one composed of more than one column).
Don't use the DISTINCT clause on all your queries as it impacts performance.
Avoid using 'IS NULL', 'OR', '<>', '!=', '!>', '<', 'NOT', 'NOT EXISTS', 'NOT IN', 'NOT LIKE', 'LIKE %10' as the optimizer will not use an index to perform the search.
When upgrading from MSDE to SQL Server clear the SQL Data Root and SQL Data Path registry entries as they can conflict with the SQL Installation.
When rebuilding clustered index on a table that has non-clustered indexes, use the DROP_EXISTING option with the CREATE INDEX command. The DROP_EXISTING option ensures that none of the non-clustered indexes are rebuilt twice.
On lookup tables that change little use the index option 'SP_INDEXOPTION' to lock the table rather than individual rows. The following two commands turn off both row and page locking allowing only table locking.

SP_INDEXOPTION 'tablename','AllowRowLocks',FALSE
P_INDEXOPTION 'tablename','AllowPageLocks',FALSE

If all your subscribers in replication are SQL server then don't select the option to allow a subscriber to be Microsoft Jet 4.0. This ensures that replication uses BCP native format rather than BCP character format which is quicker.
SQL Performance monitor includes a Performance Monitor Object called the USer Settable Object, which is a set of ten performance monitor counters that you can customise for your own purpose.
Use SQL's Profiler tool to capture all events between your application and SQL. You can identify performance problems due to application design and watch how long events take.
Ensure that you have a clustered index on tables that have many INSERTS even though the table may not really need one. An insert on a table without a clustered index adds the new record at the end of the table causing contention when there are many inserts.
If the DBCC CHECKDB is taking too long use the NOINDEX option to not check non-clustered indexes. The same is true for the Check Database Integrity option when the 'Exclude indexes' option can be selected.
Use the SQL Profiler to find out if SQL Server is running efficiently as it can.
An update trigger runs whenever it's related table is updated. You can use the COLUMNS_UPDATED() function to check which columns have been updated and only execute the trigger on selected column changes.
To list any traces at the client level run the DBCC TRACESTATUS(-1) command in the Query Analyser.
Don't add identity columns to your tables to provide a primary key when an existing column in the table is unique.
When accessing linked servers via Enterprise Manager over a slow network amend the Login time-out value. This can be found on the Tools-Options Connection Tab.
If linked servers have the same character set and sort order as the local server set the SP_SERVEROPTION 'collation compatible' to true to reduce overhead and boost performance.
Run queries in the Query Analyser and look at the query plan when running a remote query against a linked server to find out which parts of the query are performing on the remote server and which parts are performing on the local server.
Capture Profiler results to a text file as using a SQL server table could impact performance of the server you are profiling.
When a transaction affects many tables and some of the tables are heavily accessed then try to move these tables towards the end of the transaction to reduce the duration of table locks during the transaction.
Use the Profiler option SP_Recompile event to trace when stored procedures are being recompiled. If a stored procedure is being recompiled often investigate and correct the problem.
Use the command line tool SQLDIAG.exe to collect loads of information about SQL server and writes it to a text file in the \mssql\log folder. Details captured include error log text, system stored procedure outputs, registry information and the last 100 queries if you have the query history trace running.
The Index Tuning wizard creates indexes in the sysindexes table with names starting with "hind_%". If the wizard is interrupted before completing these indexes and associated tables can be left undeleted. You can either delete these manually or run the script found at http://support.microsoft.com/support/kb/articles/q293/1/77.asp.
Amend the verbose setting of the log reader agent, merge agent and snapshot agent to reduce the amount of data these processes log. These settings are amended by using the agents utility programs located in the \mssql\binn directory.
If you need replication agents to run frequently, say every minute or so then set them to run continuously. This reduces the overhead of starting and stopping the every minute.
For frequently run queries that use the WHERE, ORDER BY, GROUP BY, TOP or DISTINCT consider adding indexes on all columns accessed, reducing the possibility of a table scan.
Use the 'SELECT @@Version' query to check the version of SQL Server currently running. This will return the version number and using the details below you can determine which service pack you are using.

2000.8.00.532 SQL Server 2000 SP2
2000.8.00.384 SQL Server 2000 SP1
2000.8.00.194 SQL Server 2000 "gold" release, no SP
7.00.1063 SQL Server 7.0 SP4
7.00.961 SQL Server 7.0 SP3
7.00.842 SQL Server 7.0 SP2
7.00.835 SQL Server 7.0 SP2 Beta
7.00.699 SQL Server 7.0 SP1
7.00.689 SQL Server 7.0 SP1 Beta
7.00.677 MSDE in Office 2000, Office Developer
7.00.623 SQL Server 7.0 "gold" release, no SP
6.50.479 SQL Server 6.5 with post SP5a update
6.50.416 SQL Server 6.5 SP5a
6.50.415 SQL Server 6.5 SP5 (Do not use as bad SP)
6.50.339 SQL Server 6.5 "Y2K" Hot-fix
6.50.297 SQL Server 6.5 included in Site Server 3
6.50.281 SQL Server 6.5 SP4
6.50.259 SQL Server 6.5 on SBS only
6.50.258 SQL Server 6.5 SP3
6.50.252 SQL Server 6.5 SP3 (Do not use as bad SP)
6.50.240 SQL Server 6.5 SP2
6.50.213 SQL Server 6.5 SP1
6.50.201 SQL Server 6.5 "gold" release

Don't use the shrink database option in your maintenance plan. This is because SQL Server will have to use more resources when creating more space.
To help you identify any replication performance issues track the following Performance Monitor counters :

Dist : Delivered Cmds/sec : Tracks the number of commands per second sent to subscribers from the distributor

Dist : Delivered Trans/sec : Tracks the number of transactions per second sent to subscribers from the distributor

Dist : Delivery Latency : The average amount of time it takes for a transaction to be moved from the distributor to a subscriber

To clear out SQL Servers data cache (buffer) and stored procedure cache run the DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE commands. To clear out the procedure cache for a single database run the DBCC FLUSHPROCINDB command.
The system table syscachedobjects lists details of cached execution plans. The cachedobjtype shows the type of object in the cache, for stored procedures look for Compiled Plan and Executable Plan. The objtype column shows the type of object which is Proc for stored procedures.
When viewing Query Execution Plans the thicker the arrow that connects icons then the greater the relative cost of that operation. Therefore pay special attention to these thick arrows to improve performance.
The Red icon text in the Query Execution plan means that the related table is missing statistics to help the Query Optimiser come up with a better execution plan.
When executing a stored procedure include the owner in front of the stored procedure to increase performance. i.e. EXEC dbo.usp_GetClients.
Remove indexes that are never used by the Query Optimiser as they slow data modifications and waste space. Use the Index Wizard to help identify indexes that are not being used.
Non-clustered indexes are best for queries that return few rows and where the index has a good selectivity (above 95%). Clustered indexes perform better for a large range of queries.
Consider using the Transact-SQL ALTER command to modify table schemas for very large tables. Using Enterprise Manager sometimes recreates a new table based on your new schema and them moves the data from the old table into the new table.
For investigating SQL Server installation errors check the following files :

sqlstp.log located in the \Windows or \WINNT directory
errorlog file located in the \MSSQL\Log directory
cnfgsvr.out file located in the \MSSQL\install directory

Performing the WHERE clause on a column causes the index on that column not to be used. Rewrite the WHERE clause so that the column and function are separate, for example :

Function acts directly on column, and index cannot be used

SELECT member_number, first_name, last_name
FROM members
WHERE DATEDIFF(yy, dateofbirth, GETDATE())>21

Function has been separated from column and an index can be used

SELECT member_number, first_name, last_name
FROM members
WHERE dateofbirth < DATEADD(yy, -21, GETDATE())

To determine which user thread has to wait for over a second run the following query :

SELECT spid, waittime, lastwaittime
FROM master..sysprocesses
WHERE waittime > 1000

Use the SET STATISTICS IO ON to force SQL to report actual I/O activity on executed transactions. Disable the option with SET STATISTICS IO OFF.
Use the SET STATISTICS TIME ON to show the actual elapsed time and CPU utilisation for every following query. Executing SET STATISTICS TIME OFF suppresses the option.
Use the DBCC SHOW_STATISTICS command to show how effective the specified index of a particular table is.
Use DBCC SQLPERF LOGSPACE command to show the percentage of transaction log space used.
Avoid Inserting, Updating or Deleting large numbers of records in a single transaction as all the records affected by your action will be locked until the transaction is done.
Use a Profiler trace to capture and view the traffic being sent from a client application to SQL Server. A Profiler trace can easily identify this type of traffic assisting in investigating performance problems.
Use the Process Info window in Enterprise manager to identify which SPID is causing blocking locks. Right click on the SPID and choose Properties to display the offending query. You can also enter DBCC INPUTBUFFER (spid) into SQL Query Analyses to reveal the query causing the block.

top

To transfer a diagram to a different server or database. follow the following steps after you have created a duplicate database :-

Check that you can make modifications to system tables by checking the properties of the server and check 'Allow modifications to system catalogs'.
Change the type of the system table 'dtproperties' to user to trick SQL into allowing the IDENTITY_INSERT flag to be set to ON, by running the query 'UPDATE sysobjects SET xtype='U' WHERE name='dtpropertes''.
Set the IDENTITY_INSERT property of the 'dtproperties' table to true to override the ID field by running the query 'SET IDENTITY_INSERT dtproperties ON'.
Decide which diagrams you want to transfer by listing the fields of the dtproperties table - 'SELECT id, objectid, property, value, lvalue, version FROM database.dbo.dtproperties'. To determine which table to transfer look for 'DtgSchemaName' value in the property column. These rows will have a value in the value column which is the diagram name. Note the objectid for the row.
All the records for this diagram have the same objectid, so you need to transfer all the records with this objectid, first ensuring that the destination database does not already have an entry with this objectid.
Once this is done use the following syntax to transfer the diagram :-

INSERT INTO databasenew.dbo.dtproperties
(id, objectid, property, value, lvalue, version)
SELECT id, objectid, property, value, lvalue, version
FROM database.dbo.dtproperties

Set the IDENTITY_INSERT property of the 'dtproperties' table back to false by running the query 'SET IDENTITY_INSERT dtproperties OFF'.
Change the type of the system table 'dtproperties' back to a system by running the query 'UPDATE sysobjects SET xtype='S' WHERE name='dtpropertes''.
Reset the flag allowing modifications to system tables by checking the properties of the server and uncheck 'Allow modifications to system catalogs'.

top

Sometimes a table may contain duplicate rows, due to a number of reasons. If there are only a few number of duplicate rows then run the following query for each row :-

SET ROWCOUNT n /* Where n is the number of duplicate rows - 1 so leaving a single row */
DELETE FROM Table1
WHERE Column1=value

If there are many duplicate rows then follow the following steps :-

Select the duplicate key values into a holding table, for example :-

SELECT column1, column2, column3=COUNT(*)
INTO HoldingKeyTable
FROM Table1
GROUP BY column1, column2
HAVING COUNT(*)>1

Select the duplicate rows into a holding table,removing the duplicates in the process, for example :-

SELECT DISTINCT Table1.*
INTO HoldingDuplicatesTable
FROM Table1, HoldingKeyTable
WHERE Table1.Column1 = HoldingKeyTable.Column1
AND Table1.Column2 = HoldingKeyTable.Column2

Now the HoldingDuplicatesTable should have the unique records.
Now delete the duplicate rows from the original table :-

DELETE Table1
FROM Table1, HoldingKeyTable
WHERE Table1.Column1 = HoldingKeyTable.Column1
AND Table1.Column2 = HoldingKeyTable.Column2

Now put the unique rows back into the original table :-

INSERT Table1 SELECT * FROM HoldingDuplicatesTable

top

When performing calculations be aware that SQL Server keeps intermediate results in the same data type as the operands in the equation. If the two operands have differing data types, SQL Server converts the intermediate and final result into the higher precedence data type. For example dividing an integer by a decimal results in a decimal because decimal is higher than integer in the precedence.

Highest - datetime
smalldatetime
float
real
decimal
money
smallmoney
int
smallint
tinyint
bit
ntext
text
image
timestamp
nvarchar
nchar
varchar
char
varbinary
binary
lowest - uniqueidentifier

Always ensure that you use varchar local variables or parameters to contain strings when you use the LIKE clause as char variables can cause problems as the values are padded with spaces to the length of the string.
Be aware that float and real data types can store very large numbers and sometimes store a close approximation of a value. For example 1.1 as a float may be stored as 1.1000000000000001.
Datetime and Smalldatetime variables store the time as well as the date. If no time is specified then 00:00 is used, while if no date is specified January 1, 1900 is used as the default. The best way to get data based on these fields is to use the between operator in your query.

top

Here are some useful additional stored procedures and DBCC Commands.

Procedure	Description	Example
sp_columns_rowset	Returns specified table column description.	EXEC sp_columns_rowset 'customer'
sp_MSexists_file	Check to see if a file exists on the specified path.	EXEC @retcode = sp_MSexists_file 'C:\MSSQL7\BIN', 'sproc.sql'
sp_MSforeachdb	Loop through all the databases on the server.	EXEC sp_MSforeachdb @Command="print '?' DBCC CHECKDB ('?')"
sp_MSforeachtable	Loop through all the tables in the database.	EXEC sp_MSforeachtable @Command="print '?' DBCC REINDEX ('?')"
sp_MShelpcolumns	Returns table schema.	EXEC sp_MShelpcolumns 'customer'
sp_MShelpindex	Returns index descriptions of specified table.	EXEC sp_MShelpindex 'customer'
sp_MSindexspace	Returns index size in KB.	EXEC sp_MSindexspace 'customer'
sp_MStablespace	Returns number of rows and size of table.	EXEC sp_MSindexspace 'customer'
sp_tempdbspace	Returns size of tempdb.	EXEC sp_tempdbspace
sp_who2	Returns information on current users and processes.	EXEC sp_who2 'sa' or no login for all active users
xp_fixeddrives	Check for free disk space in servers fixed disks.	Master..xp_fixeddrives
xp_getfiledetails	Another method to confirm a file exists	Master..xp_getfiledetails "c:\mssql\binn\sqlservr.exe"
DBCC STACKDUMP	Dumps SQL Server's stack to the SQL00001.dmp file in SQL Server \log folder


Performance Monitor	top

Use SQL's Performance Monitor to keep an eye on your Servers performance. Use the following key counters -

Memory - Pages/Sec : View how much paging the server is doing.

Network Interface - Bytes Total/Sec : Network Activity.

Physical Disk - %Disk Time - Total : How busy the disk drives are.

Physical Disk - Current Disk Queue Length : Another indication of how busy the disk drives are.

System - % Total Processor time : See how busy the CPU's are.

System - Processor Queue Length : Another indication of how busy the CPU's are.

SQLServer - General Statistics - User Connections : See how many connections and users are using the server.

SQLServer - Buffer Manager - Buffer Cache Hit Ratio : See of there is enough memory in the server.

SQL Server - Memory Manager - Total Server Memory (KB) : How much memory the mssqlserver server is currently using.

SQL Server - Memory Manager - Target Server Memory (KB) : How much memory SQL Server would like to have in order to operate effectively.

top

To set up a scheduled job to monitor the SQL Server Error log files for specific error strings i.e. "table corrupt" and "DBCC" and then send an email to the DBA follow the following steps"

Create a text file comprising of the various strings you want to include ("DBCC"), for example. Save the file as search.txt
Create a T-SQL job using this script

EXEC xp_sendmail lname, fname 
@subject = ' Search Errorlog'
@query = "declare @result varchar(125)
EXEC @result = xp_cmdshell 'findstr /i /g:c:\mssql7\log\search.txt _
  c:\mssql7\log\errorlog*.* '"

Make sure you have SQL Mail and SQL Server Agent running and that a valid MAPI Profile exists. Then schedule to run this job as a recurring job.

top

When a stored procedure recompiles, it places a lock on any objects it references which in turn may create blocking.
Stored procedures will normally recompile before execution for a number of reasons including: dropping and recreating the stored procedure, using the WITH RECOMPILE clause, changing the schema of any referenced objects, running the sp_recompile system stored procedure against a table being referenced, restoring the database containing the stored procedure or the plan dropping from the cache.
Reference all objects in the stored procedure with the owners name. This will not stop recompiles, but will stop SQL Server from placing a COMPILE lock on the procedure while it determines of all objects being referenced have the same owners as the objects in the current cached procedure plan.
Follow the following steps if you have a problem with stored procedures :

1. Start Profiled.
2. Start a new trace.
3. Connect to your server.
4. Specify a trace name on the General Tab
5. On the Events Tab, remove all default events and add SP:Recompile, SP:Starting, SP:Completed under Stored Procedure Events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and SP:StmtCompleted.
6. If you are tracing only one stored procedure you can filter by its name under the Text-Like filter.

top

An identity column creates a numeric sequence. The column has a 'seed' which is the starting value and an 'increment' which determines how the value is incremented.
When you insert into a table with an identity column you don't out a value into the identity column.
If you want to see the identify value you just inserted use 'SELECT @@IDENTITY as NewRec'.
Deleting all the records from a table won't reset the identity
If you want to insert a value into an identity column use the SET IDENTITY_INSERT statement :

SET IDENTITY_INSERT Client ON
INSERT Client (ClientID, ClientName) VALUES (23, "SMITH")
SET IDENTITY_INSERT Client OFF

top

Use the SUBSTRING function to return part of a string. i.e. 'SET code = SUBSTRING(phone, 1, 4).
Use the CHARINDEX function to return the position of one string within another. i.e. SELECT CHARINDEX(',', 'A,B,C') returns 2.
Use the REPLACE function to replace one string with another. i.e. REPLACE('friday','fri','mon') returns 'monday'.
Use the STUFF function to replace one string with another at a specific position. i.e. STUFF('friday', 1, 3, 'mon') again returns 'monday'.
Use the QUOTENAME function to return a string enclosed by another character. i.e. QUOTENAME(monday,'{') returns '{monday}'.