Using diskpart and wmic to check disk partition alignment

(Check out our Pluralsight online training course: SQL Server: Improving Storage¬†Subsystem Performance.)¬† A while ago I blogged about disk partition alignment, and how the default alignment of 31.5Kb on Windows Server 2003 can lead to enormous I/O performance problems (see Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?). We’ve […]

Sample corrupt databases to play with

The very worst piece of advice I ever saw on the Internet was in response to someone asking on a SQL newsgroup ‘how can I create a corrupt database?’. The first response was: When I want to corrupt a database to play with, I go into the data center, find a hard-drive and flick the […]

Benchmarking: 1-TB table population (part 3: separating data and log files)

Blog posts in this series: For the hardware setup I'm using, see this post. For the baseline performance measurements for this benchmark, see this post. For the increasing performance through log file IO optimization, see this post. In the previous post in the series, I optimized the log block size to get better throughput on the transaction […]

Script: open transactions with text and plans

[Edit 2017: this query is still entirely applicable on all current versions of SQL Server.] Here’s a little script I knocked up this afternoon to tell me who has open transactions on the server – not just the single oldest active transaction that DBCC OPENTRAN returns. It gives back: session ID login name database context […]