Jan 21

SQL Server Perfmon Counters

If not otherwise specified, these numbers are from a Microsoft webcast.

SQL Server: Buffer Manager

  • Page life expectancy should be > 300
  • If Page lookup/sec / Batch Requests / sec is > 100, then you’re doing too much I/O.
  • Lazy writes/sec should be 0. > 20 is bad, indicates memory pressure.
  • Free pages should be >= 640.

Memory manager

  • Memory grants pending should be 0

Latches

  • Avg Latch Wait Time(ms) should be less than 10

Locks

  • Total wait time (ms) > 60,000 ms is bad.

SQL Server Statistics

  • Compilations/sec should be less than 20% of batch requests.
  • Recompiles should be less than 10% of batch requests.

Threads

Jan 01

Passing Multiple Parameters to sp_executesql

The BOL rather unhelpfully doesn’t give an example of how to pass multiple parameters to sp_executesql, leaving one to wonder whether it’s

EXEC sp_executesql @sql, N'@start DATETIME', @start, N'@finish DATETIME', @finish

or possibly

EXEC sp_executesql @sql, N'@start DATETIME, @finish DATETIME', @start, @finish

(That N is important by the way — the documentation also doesn’t mention that the SQL statement and the parameter definitions must be NVARCHARs, not VARCHARs, and the error message that sp_executesql returns is only vaguely helpful.)

It turns out that #2 is the correct syntax, though you wouldn’t know it from the intelligible error returned by sp_executesql. Thanks again, Google!

Nov 20

How to retrieve the RETURN value from a stored procedure in .NET

If you need to return a single integer from a stored procedure, it’s more efficient to use the RETURN statement than SELECT since SELECT is a whole recordset with a cursor. To access the return value from your C# code, you add a “ReturnValue” parameter to your stored procedure call:

(Pretend you have an open SqlConnection called “conn”.)

SqlCommand sqlCommand = new SqlCommand(“webmail_getMessageCount”);
sqlCommand.Connection = conn;
sqlCommand.CommandType = CommandType.StoredProcedure;

SqlParameter messageCount = new SqlParameter(“@count”, SqlDbType.Int);
messageCount.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(messageCount);

sqlCommand.ExecuteNonQuery();

return (int)messageCount.Value;

Nov 06

Microsoft Log Parser

I use Microsoft’s Log Parser to collect events that happened in the time surrounding job failures in case they contain helpful data. All the examples of doing this from within SQL Server require you to put the folder containing the Log Parser executable in the server’s PATH environment variable, and you have to bounce SQL Server to get it pick up the change. When the operations team forgets to install it during the server configuration, you can’t just bounce a production server in the middle of the day when you find out.

I wished therefore not to use an environment variable. I tried specifying the full path to the executable in my call to xp_cmdshell, with and without quotes. No dice. I finally hit upon using the DOS “short names” (the ancient 8.3 names from the bad old days of Windows), which works.

Presuming the default installation location (c:Program FilesLog Parser 2.2), the DOS short path is

c:PROGRA~1LOGPAR~1.2LogPar~1.exe

on an x86 server, and

c:PROGRA~2LOGPAR~1.2LogPar~1.exe

on an x64 server (Progra~2 is the “Program Files (x86)” directory).

Oct 21

Cumulative update package 4 for SQL Server 2005 Service Pack 2

Microsoft’s getting a little punchy!

An access violation occurs in the “sqlservr!BufferPartition::Steal” function, and SQL Server stops responding.

Committing an access violation is the definition of stealing, no? So this seems like perfectly normal behavior for a steal function!

I like that SQL Server appears to get mad and ignore you when you the buffer manager starts stealing. This confirms my suspicion that SQL Server is actually an obnoxious child.