Month: October 2015

Missing SQL Server Performance Counters

8317

Server Error: 8317, Severity: 16, State: 1.
Server Cannot query value 'First Counter' associated with registry key 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL Server performance counters are disabled.Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033) Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

We use the “First Counter” value in the registry to identify where the counters for this SQL instance start. The “First Counter” value should map to “MSSQL$Instance:Buffer Manager” as this is the first Perfmon counter in the ini file
If it doesn’t, we throw the error mentioned above in SQL error logs.

Does your registry key look like mine?

registry

If your registry key is missing ‘First Counter’, ‘First Help’, ‘Last Counter’, ‘Last Help’ then perform the following:

Re-add extensible counters
1). Open a Command Prompt window.
2). Run:  cd %Systemroot%\System32
3). Run:  findstr drivername *.ini
4). Note the name of the .ini file for each drivername in the list.
5). At the command prompt, type the following command, and then press ENTER:
lodctr axperf.ini
— use *.ini in the each drivername list obtained in step 4
6). Repeat step 5 for each .ini file in the list.

7). Rebuild all Performance counters including extensible and third-party counters:
cd\windows\system32
lodctr /R

8). Re-load SQL performance counters then restart SQL service in a DOS command window:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> unlodctr MSSQL$MSSQLSERVER
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>lodctr perf-MSSQLSERVERsqlctr.ini
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>net stop MSSQL$MSSQLSERVER
C:\Program Files\M
icrosoft SQL Server\MSSQL.1\MSSQL\Binn>net start MSSQL$MSSQLSERVER

9). Check the SQL errorlog and confirm that there are no more errors

10). Check the registry key and confirm that ‘First Counter’, ‘First Help’, ‘Last Counter’, ‘Last Help’ key values are correct now
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$MSSQLSERVER\Performance

11). Check perfmon and confirm SQL Performance Counters are available

perfmon

Advertisements

How SQL Server has made being audited a little bit easier to bear…

Headbanging bear

The following server permissions were added in SQL Server 2014:

CONNECT ANY DATABASE Permission
Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.

SELECT ALL USER SECURABLES Permission
When granted, a login such as an auditor can view data in all databases that the user can connect to. When denied, prevents access to objects unless they are in the sys schema.

Another Reason not to use NOLOCK query hint

Just say 'No'

When there is corruption in a database (or) when scanning the data with the NOLOCK query hint (or) with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to have been deleted or moved by page splits caused by Inserts/Updates/Deletes making SQL Server not able to scan further and cause

Error 601: Could not continue scan with NOLOCK due to data movement.

This is a “by design” behavior. The error messages are being issued from SQL Server to alert the client that data in the query was updated while the read was executing, rendering the data inconsistent and invalid.  NOLOCK allows for data to be read  while another process updates the data. It was never designed to read data from a field while that specific field was being updated. “SNAPSHOT” isolation level will allow this, by creating a separate copy of the data in TempDB for the duration of the read. The other fix is to simply remove the NOLOCK query hint