Kalen Delaney Virtual Workshop: SQL Server 2008 Indexes – Internals and Best Practices – This Friday
Feb. 25, 2011 – Along with a good database design I would say that Indexes are a cornerstone to good database performance. There are a lot of other skills you can develop in relational databases; but without these two skills, you’re going to find yourself in a heap of trouble. The SSWUG.org’s virtual workshop, conducted by Microsoft SQL Server MVP Kalen Delaney, will give you the skills you need to understand indexes, evaluate their effectiveness and make the necessary adjustments for performance. Register today to save your spot.
$$SWYNK$$
Featured Article(s)
Fault Tolerance Options, Overview and Features and Techniques (Part 3 of 3)
In the event something goes wrong, being able to have another database server up and running very quickly and in some cases even with automatic failover capabilities available. A lot of things that can be done in a database mirroring perspective to increase the availability of the system. Database mirroring was designed for one particular reason and that was that clustering itself is fairly expensive and requires very specialized hardware to get you the ability to do automatic failover. Database mirroring is a less expensive way to have automatic failover without requiring the specialized hardware that clustering requires.
Featured White Paper(s)
Essential Performance Tools for SQL Server DBAS
Optimizing SQL Server performance can be a daunting task. Especially so for an increasing number of reluctant DBAs faced with… (read more)
Featured Script
Calculate total rows and disk space
Calculates the total rows and disk space used for each table, then provides a summary of the totals…. (read more)
Size Matters
And it always has. Before you are convinced I’m being rude, I am talking about the block size you use when you format disks for SQL Server Database Storage.
It’s interesting that the block size you use when formatting a disk impacts the performance of your database, regardless of using Direct Attached Storage, External Storage, or even SAN storage. How much the impact will be depends on the kind of hardware storage used. I haven’t found any performance metrics for block size when using Solid State Disks.
As you know SQL Server stores data 64k Extents broken up into 8k pages. So, for some databases you find better performance aligning the disk format to 8k or 64k blocks. The operating system reads and writes from the disk in block chunks. So, if your blocks are synchronized with the access patterns of your database, performance is enhanced. It’s simple logic that if I need an 8k page from the disk and don’t have to read more, I will get faster performance. Likewise, if I am reading entire extents most of the time, I will get better performance with a single 64k block size read than 8-8k reads.
There are lots of good articles out there on the web speaking to this performance tuning technique. I find a lot of great resources with a Google search of "SQL Server Block Size.:"
Do you have any other simple optimizing tips you’d like to share. Drop me a line at btaylor@sswug.org.
Cheers,
Ben