Author Archives: John Perry

This sums it up.

But beware of the 4GB bug, which isn’t fixed until SQL Server 2012.

How to size a logfile in increments.

If you are seeing Context Switches/sec higher than 5000 per physical processor you should strongly consider turning off hyper-threading on your system and retesting performance.

DBCC CheckDB
– Minimize Impact

Use Hallengren system.

On a SAN, don’t worry about “OS Average Disk Queue Length Per Disk”. Explained.

What counters in Windows Performance Monitor show the physical disk latency? 
“Physical disk performance object -> Avg. Disk sec/Read counter” -  Shows the average read latency. 
“Physical disk performance object -> Avg. Disk sec/Write counter” -  Shows the average write latency. 
“Physical disk performance object -> Avg. Disk sec/Transfer counter” - Shows the combined averages for both read and writes. 
The “_Total” instance is an average of the latencies for all physical disks in the computer. 
Each other instance represents an individual Physical Disk.

Within SQL:

 /*
Quickly see where the read and write hot spots are and then drill into a database to see what’s going on, and if nothing out of the ordinary, ask the SAN admin to move those hot spot files to dedicated and/or faster storage.

Filter on read or write latencies and it joins with sys.master_files to get database names and file paths.
*/
SELECT
 --virtual file latency
 [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0
 ELSE ([io_stall_write_ms] / [num_of_writes])
 END,
 [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0
 ELSE ([io_stall_read_ms] / [num_of_reads])
 END,
 [Latency] = CASE WHEN ([num_of_reads] = 0
 AND [num_of_writes] = 0
 ) THEN 0
 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes]))
 END,
 --avg bytes per IOP
 [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0
 ELSE ([num_of_bytes_read] / [num_of_reads])
 END,
 [AvgBPerWrite] = CASE WHEN [io_stall_write_ms] = 0 THEN 0
 ELSE ([num_of_bytes_written] / [num_of_writes])
 END,
 [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0
 AND [num_of_writes] = 0
 ) THEN 0
 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes]))
 END,
 LEFT([mf].[physical_name], 2) AS [Drive],
 DB_NAME([vfs].[database_id]) AS [DB],
 --[vfs].*,
 [mf].[physical_name]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
 AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

Checklist.

Builtin Administrators.

Download Sysinternals zip file Coreinfo. This explains how to use it.

Here is how we got into this mess:

• Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server
• And Here
• and Brent

1. First error in Windows Application log file
FCB::Open failed: Could not open file D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDB.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).

2. Second error.
Error: 17204, Severity: 16, State: 1.

The fix (for me)

ALTER DATABASE MyDB SET ONLINE

See also this.
And this and this command “alter database set emergency dbcc checkdb (repair_allow_data_loss)”.

Script to list all the schemas for a database and then construct the code to change it to [dbo].

See here for more information.

USE MyDatabase
go

-- Get the associated schema
SELECT  *
FROM    information_schema.schemata

DECLARE @vcUser VARCHAR(128),
    @vcSQL VARCHAR(8000)
SELECT  @vcUser = 'any non dbo username causing the problem'

IF EXISTS ( SELECT  s.name
            FROM    sys.schemas s
            WHERE   s.principal_id = USER_ID(@vcUser) )

-- Now replace the result name in following script:
    SELECT  @vcSQL = 'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(s.name) + ' TO dbo;'
    FROM    sys.schemas s
    WHERE   s.principal_id = USER_ID(@vcUser)
            
PRINT @vcSQL
IF 1 = 1
    EXEC (@vcSQL)
    

-- Get the associated schema
SELECT  *
FROM    information_schema.schemata