Wait Type: ASYNC_NETWORK_IO – It’s the network…right?

There are a few wait types that cause a large amounts of confusion, one of them being ASYNC_NETWORK_IO.  The name of the wait type is pretty misleading because the issue rarely has to do with the actual network.

The wait type could mean that SQL has sent data to the requesting (client) application and is waiting for it to acknowledge receipt.  When the client application inefficiently processes the data is receives, way may see occurrences of ASYNC_NETWORK_IO.  When you see this wait type, take a look at how efficiently the client application is processing the requested data.  There may be a cursor, badly designed join, or other poorly performing construct on the client side in play.  Additionally, check how many records are actually being sent back to the client, and if it is possible to filter down that result set.

This said, it may actually be the network that is the bottleneck, but it’s best to start with the above.

Tagged , , , ,

Quickly Moving tempDB

For performance reasons, it is important to have tempDB situated on a separate drive than your other SQL data/log files.  To move the location of your tempDB files after installation:

–Determine where your tempDB files are currently placed:

EXEC sp_helpfile

–Replace “T:\ and L:\” with the drive letter of you new tempDB drive:

USE master
(NAME = tempdev, FILENAME = ‘T:\tempdb.mdf’)
(NAME = templog, FILENAME = ‘L:\templog.ldf’)

One you run the above, restart SQL Server to move the files to your new location.  Remember, if this is a cluster, the destination disk must be visible to all cluster nodes.  I like to presize my data and log files to preempt future growth; it’s less impactful to do it now than in the middle of a processing day:

USE [master]

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 2560000KB , FILEGROWTH = 102400KB )
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’templog’, SIZE = 2560000KB , FILEGROWTH = 102400KB )

If you have a multicore of multiproc machine, the rule of thumb is to create 1/4 to 1/2 the number of tempDB data files as the number of cores you have.  So, if you 24 cores, you would want 6 to 12 files.   

USE [master]
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′, FILENAME = N’T:\MSSQL\DATA\tempdev2.ndf’ , SIZE = 10GB , FILEGROWTH = 0)

Tagged , , , , , ,

Why can’t I shrink my transaction log?

I needed to move a database with a 150+ GB transaction log between lab servers.  As this was just a test system with no RPO/RTO objectives in place, there was no need to preserve that huge transaction log.  I ran the following expecting its size to drop:

USE [master]
GO USE [myDatabase]

However, the transaction log size remained unchanged.  There were no transactions in flight, and zero open connections against this this database.  I recalled that on the system where this database originated, replication was in play.  I checked the following:

select log_reuse_wait_desc from sys.databases where name = ‘myDatabase’

This field tells us why the transaction log is not truncating as we would expect.  If the value returned by the above is ‘REPLICATION’, and the database is not involved in a replication setup, run: sp_removedbreplication.  Then run a DBCC SHRINKFILE (you may need to back up the log prior to this).

Tagged , , , , , ,

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.

Tagged , , ,

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.

Tagged , , , , ,

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.

Tagged , , , , ,

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.

Tagged , , , , , ,

After renaming the server, run the following:

--After renaming the physical server , run the following:
master.dbo.master.dbo.sp_dropserver 'oldServerName'
master.dbo.sp_addserver 'newServerName', 'local'
--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.
Tagged ,

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.

Tagged , , ,

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) + '.' +  
Tagged , ,