The Skinny on Statistics

Bad or missing statistics can lead to poor choices by the query optimizer. Statistics are created when an index is created, using a full scan of the data, and table statistics are created when a column is referenced in a filtering clause such as WHERE, HAVING, JOIN, etc.

Statistics are updated based on the following formula (if set to auto-update):

* When a table with no rows gets a row
* When 500 rows are changed to a table that is less than 500 rows
* When 20% + 500 are changed in a table greater than 500 rows

DBCC SHOW_STATISTICS displays information about the statistic in question:

Automatic statistics updates work well in most situations, except when you have outlier tables in your database that are irregularly updated and may not trigger a statistics update. In these cases, manual updates will be necessary. There are two ways to update statistics:

sp_updatestats – will update based on a random data sampling; less accurate than those built with a full scan.
UPDATE STATISTICS WITH FULL SCAN – performs a full scan of data. Localized to a single table, index, or set of stats. The WITH FULL SCAN portion is optimal.

Index reorganizations do not modify statistics.

How do I know when to update stats?
By tracking the behavior of your queries to determine when the optimizer begins making suboptimal decisions.- not always an easy task.

The NORECOMPUTE option when creating or updating statistics prevents them from being automatically updated. STATISTICS_NORECOMPUTE option can be used when you create an index.

Why Prefix a String With ‘N’?

You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

How to Recover From Being Locked Out of a SQL 2005/2008 Server

1. Stop SQL Server 

2. Start in Single User Model (-m startup parameter)

3. Connect locally (on the server itself) with an account that is a local administrator on the server.  This user will be treated as a member of the sysadmin fixed role.

SQL Profiler – Duration in Milliseconds, or Microseconds…Which is it?

From MSDN:

Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

After renaming the server, run the following:

--After renaming the physical server , run the following:
-----------------------------------------------------------------------------------------------------
master.dbo.master.dbo.sp_dropserver 'oldServerName'
GO
master.dbo.sp_addserver 'newServerName', 'local'
GO
--Restart the SQL instance at this point.
-----------------------------------------------------------------------------------------------------

--After restarting, run the following to confirm that the previous was successful. Is should return the new server name.
-----------------------------------------------------------------------------------------------------
SELECT @@SERVERNAME AS 'Server Name'
-----------------------------------------------------------------------------------------------------

ERwin Data Modeler Error: Unable to locate client connectivity software

When first trying to connect to SQL Server from an installation of ERwin (regardless if it’s 32 or 64 bit), you may receive the message:  Unable to locate client connectivity software 

There is a very simple fix to this seemingly common problem.

Locate the file “ntwdblib.DLL” , and copy the dll file to “AllFusion ERwin Data Modeler” folder.

Grant EXECUTE permissions to all stored procedures and functions

Often times we need to grant EXECUTE rights to all stored procedures and functions in a database to a specific user.  Replace ‘YourUserName’ with the name of your login, and run on the database in question.  It will produce a line of SQL for each securable that requires a GRANT.

declare @username varchar(255)  
set @username = 'YourLoginName'  
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +  
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES  
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0   
and ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')