ORACLE : Sqldeveloper - MSVCR71.DLL was not found

Attempting to start SQLDeveloper on my XP desktop today I was faced with this error. This application failed to start because MSVCR71.dll was not found

I had already installed the Java VM and hence some searching led me to Michel Belor's post on how to resolve the error. His solution is linked below and involves adding a couple of entries to the registry.

SQLDeveloper : MSVCR71.DLL not found error

Windows 7 : Disable Windows Search

Disabling Windows Search may be a little off topic for a sql blog, but I have found it to be worthwhile when creating a Windows 7 Virtual Machine. The Search Service constantly indexes the drives which has quite an overhead on the virtual machine.

Removing the RDP Client Server History List

Is easily accomplished via Regedit.

Delete entries from this key to achieve this >

HKEY_CURRENT_USER\Software\Microsoft\Terminal Server Client\Default

Selectively Clearing the Plan Cache - FLUSHPROCINDB

DBCC FREEPROCCACHE clears the plan cache for the whole instance.

If you have databases from different applications / vendors on the same server however , you might need to do so for just one database. That is where DBCC FLUSHPROCINDB comes in.

1) Use DB_ID() to get the database ID for the current database.
2) Use that as a parameter for FLUSHPROCINDB 

SELECT DB_ID()

DBCC FLUSHPROCINDB(10)

2011

In common with a lot of technical bloggers I have been publicly setting and reviewing goals over the past few years. I find that having long term goals outside of the workplace keeps me focused and increases the breadth of my skill set.

That is all very well and has worked in the past for me. In 2011 however I fell short of achieving all of  the tasks I set myself. Anyway, here is my autopsy of my goal list from last year.

A new role

I achieved this goal and started in April 2011. The role has been challenging in terms of the volume of work and has forced me to up my game re; communication skills. Technically however, the technologies are old and simple issues are repeated over geographically separate client sites. In terms of skills I have still been able to utilize a mixture of administration and development.

Blogging

My output to this site has been less than half that of previous years and has occurred in bursts rather than the usual trickle. I found myself constantly returning to old posts and scripts last year so my efforts have paid dividends. I need to index the site better however so that will be on this years list! Rather bizarrely a lot of content has been focused on SQL 2000 which I thought I had long seen the back of! The client is always right (or skint) however...

Community

I did manage to publish more scripts although only on my site (no further SSC contributions this year). As for publishing articles (to other sites) I totally failed on this goal.
In terms of SQL Server events I managed to attend 3 in 2011. In April there was SQLBits 8 in Brighton and September saw me attend (and help out at) SQLBits 9 in Liverpool.
The 3rd event I attended was Gavin Payne's October SQL Server in the Evening event where I made my speaking debut. In line with recent experiences I presented my approach to auditing SQL Server systems. Public speaking wasn't in the plan but I am grateful for the opportunity to conquer a demon.

Learning

I am a little disappointed not to have had the chance for a major project that requires SSAS, SSIS or .NET development so these will remain on my list. In terms of reading, my book backlog is not being helped by the volume of quality SQL content the community is producing. Time has not been on my side of late and my reading list is being joined by a viewing list of awesome free training videos!

SQL Server Quick Check - Notes

Checking a SQL Server over? Some notes on how to approach this.
Examine aspects in this order (Memory > Storage > CPU) as one issue can be a symptom of another.

Memory

Examine using Performance Monitor (Perfmon). Look at these counters -

Memory:Available MBytes
How much memory is available for Windows?
Stop SQL consuming too much by setting maximum memory (recommended tip).

SQLServer:Memory Manager/Target Server Memory (KB)
How much memory SQL wants.

SQLServer:Memory Manager/Total Server Memory (KB)
How much memory SQL has.
If this is less than the value it wants then more memory is needed to be allocated or installed.

SQLServer:Buffer Manager:Page Life Expectancy
300 seconds (5 minutes) for an OLTP system.
90 seconds (1 & 1/2 minutes) from a data warehouse.

The old method was SQLServer:Buffer cache hit ratio (ideal of 99%) but data volumes mean this counter has become meaningless now.


Storage

Examine these Windows counters using Perfmon for the Windows view of Storage performance

LogicalDisk:Avg.Disk sec/Transfer
Should be < 20ms (0.020 seconds) for volumes hosting sql data files

For further problems look for differences between
LogicalDisk:Avg.Disk sec/Read & LogicalDisk:Avg.Disk sec/Write
Could show issues with controller or RAID (e.g. slow write on RAID5).


For a SQL view of Storage performance, some TSQL to help out ...

On SQL 2000 looking at I/O statistics is achieved using fn_virtualfilestats

SELECT
   d.name as DBName
   ,RTRIM(b.name) AS LogicalFileName
   ,a.NumberReads
   ,a.NumberWrites
   ,a.BytesRead
   ,a.BytesWritten
   ,a.IOStallReadMS
   ,a.IOStallWriteMS
   ,CASE WHEN (a.NumberReads = 0) THEN 0 ELSE a.IOStallReadMS / a.NumberReads END AS AvgReadTransfersMS
   ,CASE WHEN (a.NumberWrites = 0) THEN 0 ELSE a.IOStallWriteMS / a.NumberWrites END AS AvgWriteTransfersMS
FROM ::fn_virtualfilestats(-1,-1) a
INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
INNER JOIN sysdatabases d ON d.dbid = b.dbid
ORDER BY a.NumberWrites DESC

For SQL 2005+, sys.dm_io_virtual_file_stats is a available.
This is a dynamic management function that shows how SQL is using the data files.

SELECT
    DB_NAME(filestats.database_id) AS DBName
    ,files.name AS LogicalFileName
    ,num_of_reads AS NumberReads
    ,num_of_writes AS NumberWrites
    ,num_of_bytes_read AS BytesRead
    ,num_of_bytes_written AS BytesWritten
    ,io_stall_read_ms AS IOStallReadMS
    ,io_stall_write_ms AS IOStallWriteMS
   ,CASE WHEN (num_of_reads = 0) THEN 0 ELSE io_stall_read_ms / num_of_reads END AS AvgReadTransfersMS
   ,CASE WHEN (num_of_writes = 0) THEN 0 ELSE io_stall_write_ms / num_of_writes END AS AvgWriteTransfersMS
FROM sys.dm_io_virtual_file_stats(-1,-1) filestats
INNER JOIN sys.master_files files
        ON filestats.file_id = files.file_id
        AND filestats.database_id = files.database_id
ORDER BY num_of_writes DESC

Further info on here c/o David Pless

If Disk Performance is an issue, consider these aspects

• Auditing disk configuration
• Physical file fragmentation - contig.exe 
• Is the RAID configuration appropriate for volume of transactions.

CPU

Performance Monitor (Perfmon) counters to determine processor use are -

Processor:% Processor Time

How busy is the processor?
Should be < 70%

Processor:% Interrupt Time 

Percentage of time spent servicing hardware interrupt requests.
Should be < 20%

Processor:Processor Queue Length

How many tasks are waiting for processor time?
Should be < 4 per CPU

For Processor counters, it may be desirable to monitor separate instances (different cores) in addition to monitoring the _Total instance. The _Total instance provides average readings and therefore disguises individual overworked or under-utilized processors / cores.
The processor counters are for everything installed on the system, not just SQL Server.

Foreign Keys without Indexes

Here are some scripts that provide index creation statements for foreign keys without indexes. NB : I am not advocating creating indexes on every foreign key. Their use depends on application design, (the sql it runs) and whether other covering indexes are present. SQL 2000 Version

/*
adapted from http://stackoverflow.com/questions/1406119/how-can-i-find-unindexed-foreign-keys-in-sql-server
Uses my NCI_tablename-indexname index naming convention
*/

DECLARE 
    @SchemaName varchar(255),
    @TableName varchar(255),
    @ColumnName varchar(255),
    @ForeignKeyName sysname

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT  cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE   ic.CONSTRAINT_TYPE = 'FOREIGN KEY'

CREATE TABLE #temp1(    
    SchemaName varchar(255),
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName

WHILE @@FETCH_STATUS = 0  
BEGIN

    IF ( SELECT COUNT(*)
    FROM        sysobjects o    
        INNER JOIN sysindexes x ON x.id = o.id
        INNER JOIN  syscolumns c ON o.id = c.id 
        INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
    WHERE       o.type in ('U')
        AND xk.keyno <= x.keycnt
        AND permissions(o.id, c.name) <> 0
        AND (x.status&32) = 0
        AND o.name = @TableName
        AND c.name = @ColumnName
    ) = 0
    BEGIN
        INSERT INTO #temp1 SELECT @SchemaName, @TableName, @ColumnName, @ForeignKeyName
    END


    FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName
END  
CLOSE FKColumns_cursor  
DEALLOCATE FKColumns_cursor 

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = '''
       + 'NCI_' + TableName + '_' + ColumnName + ''') '
       + ' CREATE INDEX [NCI_' + TableName + '_' + ColumnName + '] ON [' + SchemaName + '].[' + TableName + ']([' + ColumnName +'])'
FROM #temp1 
ORDER BY TableName

DROP TABLE #temp1 

SQL 2005 Version

/*
adapted from http://encodo.com/en/blogs.php?entry_id=173
Uses my NCI_tablename-indexname index naming convention
Have added table schemas and 'IF EXISTS' checks to detect if index is already present
*/

SELECT
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['
+ IndexSchemas.[name]
+ '].['
+ IndexTables.[name]
+ ']'') AND name = N''IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ '] ON ['
+ IndexSchemas.[name] + '].[' + IndexTables.[name] 
+ ']( ['
+ IndexColumns.[name]
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
FROM sys.foreign_keys ForeignKeys
INNER JOIN sys.foreign_key_columns ForeignKeyColumns
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id
INNER JOIN sys.columns IndexColumns
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id
INNER JOIN sys.tables IndexTables
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id
INNER JOIN sys.schemas IndexSchemas
  ON IndexTables.schema_id = IndexSchemas.schema_id
ORDER BY IndexTables.[name], IndexColumns.[name]