Monthly Archives: July 2015

Buffer pool extension gotcha

Don’t get caught out by this one.

We just got a big new server for our upgrade to SQL 2014, and I specially added a pair of SSDs to the spec so we would have a super-fast RAID 1 partition for the tempdb files and so we could use the new buffer pool extension feature. The server vendor insisted we had to use their approved, server-grade SSDs to be properly supported. These disks are far from cheap so we got the 120GB drives.

All was going well until I tried to enable the buffer pool extension:

Msg 868, Level 16, State 1, Line 2
Buffer pool extension size must be larger than the current memory allocation threshold 117894 MB. Buffer pool extension is not enabled.

I didn’t see that in any of the material announcing this feature. It’s in the (really) small print in Books Online so technically we have been warned. We have plenty of RAM so we would need to buy bigger SSDs to use buffer pool extensions.

Still, at least anything that spills to disk in tempdb is going to go way faster, which is better than nothing.

Advertisements

Missing SQL PerfMon counters

A server has just lost all the SQL Server PerfMon counters. They’ve all disappeared into thin air. It plays havoc with our monitoring tool! We’ve had a couple of instances of this lately. I haven’t found the cause and it’s starting to get on my nerves. Luckily the answer is fairly simple once you know. NB: This involves restarting the SQL Server service so you might want to do it out of hours.

The steps are:

  1. Run cmd.exe as Administrator.
  2. Go to the Binn directory. On my SQL 2014 instance this was cd C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn.
  3. unlodctr MSSQLSERVER (or unlodctr MSSQLSERVER$INSTANCENAME for a named instance).
  4. lodctr perf-MSSQLSERVERsqlctr.ini (or lodctr perf-MSSQLSERVER$INSTANCENAMEsqlctr.ini for a named instance).
  5. Restart the Remote Registry service if running.
  6. Restart the Performance Logs & Alerts service if running.
  7. Restart the SQL Server service.

There are some other strange things that can happen: Remote Registry service stopped, all PerfMon counters – or just SQL counters – disabled in the Registry.

Resources:

http://www.travisgan.com/2013/05/missing-sql-performance-counters.html

http://blog.sqlauthority.com/2014/11/07/sql-server-performance-counter-missing-how-to-get-them-back/

http://blogs.technet.com/b/pfelatam/archive/2011/08/08/sql-performance-counters-are-missing.aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/18/what-should-we-do-when-the-sql-server-performance-counters-are-missing.aspx

http://blogs.msdn.com/b/psssql/archive/2009/03/03/troubleshooting-sql-server-2005-2008-performance-counter-collection-problems.aspx