Direct NFS

You can get info from the following views:

— View servers accessed using Direct NFS
select * from v$dnfs_servers;

— View files currently open using Direct NFS
select * from v$dnfs_files;

— View open network paths or channels to servers for which Direct NFS is providing files
select * from v$dnfs_channels;

— View performance statistics for Direct NFS
select * from v$dnfs_stats;

Also you will see in the alert log on startup:

Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 2.0

Disable DNFS

Shutdown DB

On all hosts:

cd $ORACLE_HOME/lib
mv libodm11.so_bak libodm11.so
Restart Instance

For Completeness, to enable:

Enable DFNS

Shutdown DB

On all hosts:

cd $ORACLE_HOME/lib
mv libodm11.so libodm11.so_bak
ln –s libnfsodm11.so libodm11.so
Restart Instance

Useful SQL

— View servers accessed using Direct NFS
select * from v$dnfs_servers;

— View files currently open using Direct NFS
select * from v$dnfs_files;

— View open network paths or channels to servers for which Direct NFS is providing files
select * from v$dnfs_channels;

— View performance statistics for Direct NFS
select * from v$dnfs_stats;

Known Issue

Getting the following message in alert log and instance not starting:

Direct NFS: please check that oradism is setuid

To correct, the following permissions are needed on:

ls -la $ORACLE_HOME/bin/oradism
-rwsr-x— 1 root oinstall 72416 Sep 11  2008 /u01/app/oracle/product/11.1/db/bin/oradism

If NOT this, shutdown DB and change permission as follows:

sudo su
cd /u01/app/oracle/product/11.1/db/bin
chmod 750 oradism
chmod u+s oradism
Restart instance

CREATE DATABASE failed. Some file names listed could not be created.

CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘L:\test.mdf’.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5123)

This is usually a consequence of an incorrectly configured SQL Server. Ensure that the SQL Server Service has local admin rights and then restart the instance.

SOS_SCHEDULER_YIELD Wait class

According to BOL, SOS_Scheduler_Yield refers to the following:

• Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

The general consensus is that if you are seeing a lot of high SOS_SCHEDULER_YIELD waits, then there is a CPU bottleneck. In essence, the CPU(s) is having to switch tasks to accommodate requests. SOS_SCHEDULER_YIELD represents a SQLOS worker (thread) that has yielded the CPU, presumably to another worker.

If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

You can run the DMV query to confirm that:

— Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern.

If you see a high value for SOS_SCHEDULER_YIELD you should check perfmon counters to see if you also see sustained high values for % Processor Time and Processor Queue Length. You should also validate that it is indeed the SQL Server process that is causing the CPU pressure using the Process: % Processor Time counter, checking specifically for the SQL Server instance (or other processes responsible). You may also want to validate the number of workers in a runnable state in sys.dm_os_workers and sys.dm_os_schedulers, revealing if a significant number of tasks are waiting in line for their chance to run on a scheduler.

If you determine that SQL Server is responsible for the sustained CPU usage, then you should follow the standard CPU pressure troubleshooting path ( for example – query sys.dm_exec_query_stats ordering by total_worker_time, check for high compilations and recompilations, optimization time spent with sys.dm_exec_query_optimizer_info, and more).

SOS_SCHEDULER_YIELD is not necessarily a bad thing..on a busy, highly concurrent SQL Server instance, this is a good thing, as threads will voluntarily yield their time so that other tasks can execute.

SQL Server runs cooperatively (non-preemptive mode) for many activities, which means that SQL Server decides when a thread will yield – not the Operating System. With thread yielding being voluntary – if the thread was greedy, we could see a risk of the thread running until it was complete – however that is not the case. The SQLOS was designed in such a way that active threads should not starve out other runnable threads.

Clean up Scheduler History in MSDB

Depending on how you perform certain operations orphaned data can exist that will remain in your MSDB database until it is manually removed.

By completing some tasks in SSMS, you automatically get the option to “Delete backup and restore history information for databases” via a checkbox. For example, when dropping a database via SSMS, this option is checked by default.

When this checkbox is checked before the database is dropped SQL Server will run a system stored procedure called:

sp_delete_database_backuphistory

The sp_delete_database_backuphistory stored procedure will delete information about the specified database from the backup and restore history tables. 

Now that you have this information how can you go back and check to see if you have any orphaned backup/restore history rows?

SELECT database_name, COUNT(backup_set_id) as ‘OrphanedRows’
FROM   msdb.dbo.backupset
WHERE  database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name

The result set of this query will identify any databases that have backup/restore history that no longer exist on your SQL Server instance.  If this query returns any data you should investigate the databases listed and where appropriate utilize the sp_delete_database_backuphistory system stored procedure to clean up your MSDB database.