Tag Archives: performance

SQL Performance Counters Missing. Load the Performance objects manually

On one of the Servers hosting SQL Server, the Performance Objects for SQL Server were missing in perfmon. Here is what was done to fix it.

Open Command Prompt and type

cd c:\windows\system32
lodctr /S:C:\PerfCounterBackup.ini

The “lodctr /S” is to backup the existing performance counter, in case of a disaster. After that entered the below command to load the SQL Performance Objects from the sqlctr.ini

lodctr /R:C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlctr.ini

If for any reasons, the objects loaded above needs to be unloaded, the following command needs to be issued.

unlodctr MSSQLServer

The MSSQLServer is the driver parameter in the unloctr command. If the Performance objects for a named instance needs to be unloaded, then the command will be

unlodctr MSSQLServer$InstanceName

Hypothetical Indexes

When Database Engine Tuning Advisor runs on a database, it makes some recommendations. For all the recommendations it actually creates the objects on the database. The recommendations can be Indexes, Stats etc. They are called Hypothetical Indexes/Statistics.

If Database Engine Tuning Advisor is closed normally, it ensures that all the objects that it had created are cleaned up. If for some reason, it does not exit normally, then these objects will still remain on the database.

Since these objects are hypothetical, they can be removed manually. These objects’ name will start with “_dta”. They can also be identified with the below query.

SELECT * FROM sys.indexes WHERE is_hypothetical = 1

Sometimes, we may want to retain the objects created by Database Engine Tuning Advisor. Then ensure to rename those objects properly, so that the clean up of obsolete objects created when DTA runs next time, becomes easy !

Database 150% Full | sp_spaceused reports negative unallocated space

A while ago, the monitoring tool reported that one of the databases was 150% full ! Logged on to the server and issued sp_spaceused command against that database. The output was as below.

As highlighted, the unallocated space was showing negative values. This was because the space usage information was not updated. After running DBCC UPDATEUSAGE against that database, the unallocated space value updated correctly. Alternatively the following command can also be executed to fix this issue.

sp_spaceused @updateusage = 'TRUE'

The below command can be used to update the usage of all the databases in one go.

EXEC SP_MSFOREACHDB @COMMAND1 ='use [?] print db_name() DBCC UPDATEUSAGE(0)'

Using Process Explorer to resolve SQL Server CPU Spikes

As a DBA, one usually comes across a situation when the CPU usage on a server spikes because of SQL Server. The knee jerk reaction would be to check sysprocesses or sys.dm_exec_requests to check which process is consuming more CPU time. The value of CPU column in sysprocesses is cumulative in nature, hence may not give the right picture everytime.

The Process Explorer utility from Microsoft also comes in handy in such situations. Here is how it can be done.

Open Process Explorer and navigate to sqlservr.exe

Right click on sqlservr.exe and select Properties. Click on Threads tab and sort by CPU.

Make a note of the TID which is consuming more CPU. In this case it is 11744. Open SSMS and get the SPID. The query can be something like this.

select SPID from sysprocesses where kpid=11744

The details of the SPID can be obtained by issuing

DBCC INPUTBUFFER(SPID)

Based on the output of the Inputbuffer, a call to KILL the SPID can be taken.

Not enough server storage is available to process this command

When trying to access a shared items on a Windows NT server (which is still in use!), the following error is thrown.

Not enough server storage is available to process this command.

Even though this issue is not directly related to SQL Server, this may affect where Log Shipping is configured on a server running Windows NT. To fix this issue, the value of IRPStackSize DWORD needs to be increased in the registry. This article on Microsoft Support site explains it better.