31. March 2008 by Bennett.

There is an EMC whitepaper titled Using diskpar and diskpart to align partitions on Windows Basic and Dynamic Disks. This paper describes how legacy Windows issues cause a single disk I/O to span multiple tracks. This puts an unnecessary load on the SAN and reduces performance. The fix is to align the partition. Below is a sample session using the DISKPART utility. Comments are in red.

Invoke Diskpart

C:\>diskpart

Microsoft DiskPart version 5.2.3790.3959

Copyright (C) 1999-2001 Microsoft Corporation.

On computer: MYTESTBOX

You must select the disk to act on

DISKPART> select disk 5

Disk 5 is now the selected disk.

Create a partition using the EMC-recommended alignment offset

DISKPART> create partition primary align=64

DiskPart succeeded in creating the specified partition.

Select the partition / volume that you just created

DISKPART> select volume=0

Volume 0 is the selected volume.

Assign a drive letter

DISKPART> assign letter=o

DiskPart successfully assigned the drive letter or mount point.

Exit Diskpart

DISKPART> exit

Leaving DiskPart…

C:\>

You can now use the disk management MMC to format the partition

Posted in Windows OS, Performance | Print | 4 Comments »

30. March 2008 by Bennett.

FCIV.EXE is a handy little utility to snap a MD5 hash of a single file or multiple files. Optionally it can recurse directories and produce XML output. http://support.microsoft.com/kb/841290

MD5SUM.EXE is a more commonly used utility that runs on a number of different platforms. A good Wikipedia article with links can be found here and a Win32 binary can be found here.

Posted in Utilities | Print | No Comments »

28. March 2008 by Bennett.

Here’s a very basic log filtering /review script that you can run on a daily basis to review your SQL Server logs. It shows the last two days of log entries filtering out logon and backup entries.

– daily log review script
SET nocount ON
CREATE TABLE #Errors
(
LogDate DateTime,
ProcessInfo NVARCHAR(40),
ErrorText NVARCHAR(2048)
)
INSERT INTO #Errors
EXEC master..sp_readerrorlog -1

SELECT * FROM #Errors
WHERE DATEDIFF(DAY, LogDate, GETDATE())<2
AND ProcessInfo <> ‘Logon’
AND ProcessInfo <> ‘Backup’
DROP TABLE #Errors

I’m currently cooking up some VBScript to filter and e-mail log entries on a daily basis. I’ll publish that soon.

Posted in T-SQL | Print | No Comments »

4. March 2008 by admin.

/*
Name: Inventory.sql
Description: Second stab at inventory using systables
Author: Bennett Scharf
Compatibility: SQL Server 2005
*/
USE master
CREATE TABLE #tempresults
(
[name] sysname,
db_size NVARCHAR(13),
[owner] sysname,
[dbid] smallint,
created NVARCHAR(11),
[status] NVARCHAR(600),
compatibility_level tinyint
)
CREATE TABLE #tempresults2
(
database_name NVARCHAR(128),
backup_start_date datetime
)
INSERT INTO #tempresults
EXEC sp_helpdb
INSERT INTO #tempresults2
SELECT database_name, backup_start_date
FROM msdb.dbo.backupset AS b1
WHERE backup_start_date =
(
SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset AS b2
WHERE b1.database_name = b2.database_name
AND type = ‘D’
)
SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on,
state_desc, recovery_model_desc, page_verify_option_desc,
tr2.backup_start_date AS ‘last backup’
FROM sys.databases db
JOIN #tempresults tr
ON db.name =tr.name
LEFT OUTER JOIN #tempresults2 tr2
ON db.name = tr2.database_name
WHERE db.database_id >4 –This clause will exclude system DBs,master, model, msdb, and tempdb
ORDER BY db.name
DROP TABLE #tempresults
DROP TABLE #tempresults2

Posted in SQL Server | Print | No Comments »

3. March 2008 by Bennett.

Performance logging will not start on its own at boot time. Here’s how to get around that problem.

 

Applies to: W2k3, XP

1. Using the Performance MMC, create a set of counter logs.
2. Use a specific name to save the settings, e.g., SQLPERF.
3. At the command prompt, try starting and stopping performance logging using the logman.exe utility:
   1. logman start sqlperf
   2. logman stop sqlperf
4. Set the Windows registry to automatically start performance counters at system startup
   1. Open the Registry Editor, regedit.exe.
   2. Navigate to HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
   3. Add a string value. A good name would be logman.
   4. Set the value equal to logman start sqlperf (or the command in step 4.a. that successfully started performance logging).

References:

1. http://support.microsoft.com/kb/303133
2. http://www.jsifaq.com/SF/Tips/Tip.aspx?id=4455

Posted in Windows OS, Performance | Print | No Comments »