16. February 2011 by admin.

The people over at SQLSkills.com have made their MCM training videos available online for free.  They’re pretty incredible.  Subject areas include database internals and performance, high availability and disaster recovery, performance tuning,  security and development support.  Link here:  http://www.sqlskills.com/T_MCMVideos.asp

There are also a plethora of wonderful videos over at SQLBits.com.  One of my favorites is a talk by Thomas Kejser on designing I/O systems for SQL Server.

SQLWorkshops.com has a collection of wonderful videos on advanced query tuning.   The instructor provides live demos with  runtime statistics to fully demonstrate his tuning techniques.  Free and very worthwhile.

Posted in Performance, SQL Server | Print | No Comments »

1. April 2008 by Bennett.

Here is an interesting Microsoft Knowledgebase article titled Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000.

It explains how a partition misalignment can cause significant I/O by causing reads and writes to straddle track boundaries. The fix is to use the diskpart.exe utility to create an alignment offset when you first create the partition.

This is similar to what you do with SAN storage to align the starting offset to “significant” boundaries. See the 3/31/08 post for more details.

Posted in Performance, SQL Server | Print | 1 Comment »

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 | 6 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 »