Hackviking He killed Chuck Norris, he ruled dancing so he took up a new hobby…

7Jul/140

Microsoft SQL Server Performance Basics (I/O Performance)

There are a lot of settings that you can tweak to get higher performance out of your Microsoft SQL Server. The most basic one is IO performance, i.e. disk performance. Usually when I talk to people about this I get the response that this is an art form and something that most techs don’t know about or feel that they don’t understand. Most people rely on the SAN team to take care of this but if you don’t understand this and can inform the SAN team what you need you will get the standard. Most SAN system are optimized for  There are always more tweaks that can be applied but in most cases the further you come along this line the smaller impact the changes have. In this article I would like to point out the most basic, and important, performance issues with Microsoft SQL Server that are easy to address. These are independent of size of the solution or underlying hardware e.g. local attached discs or SAN.

Background

To understand why this is so important you need to know a little about how Microsoft SQL Server reads from the disk. To simplify Microsoft SQL Server reads pages, pages contains a number of rows with you corresponding data. The pages with extents are 64kb in size. So the goal here is to read (or write) the page with as few disc IO’s as possible.

Stripe Unit Size

The stripe size is the smallest chunk of data that can be addressed within the RAID. So make sure you are using at least 64KB stripe size. If it’s a larger number like 128KB or 256KB that only means that you can write several more pages in the same stripe, this can actually benefit performance of the read ahead function in Microsoft SQL Server.

File allocation unit size / Disc cluster size

This setting is on the file system level. Microsoft SQL Server is designed for the NTFS file system and the default NTFS disc cluster size is 4KB. Again this should be 64KB for best performance, it enables SQL server to do less IO than a smaller cluster size does. There is a correlation between cluster size and stripe unit size that needs to be meet for optimal performance:

Stripe Unit Size ÷ File Allocation Unit Size = an integer

If possible you should try to meet this formula. However that isn’t always possible due to different storage systems. The most important thing for performance in that case is to use the 64KB cluster size! The formula for partition alignment below is however not optional for performance!

Partition alignment (partition offset)

When I have been talking to people about this most people look at me like I’m crazy. A system that was setup from a clean install of Microsoft Windows Server 2008 and later doesn’t suffer from this, these versions do an automatic alignment of the partition. If the partition isn’t aligned your server will end up splitting the read and write IO into two or more IO’s. This is very bad for performance.

Role of thumb here is:

Partition Offset ÷ Stripe Unit Size = an integer

Old systems prior to Microsoft Windows Server 2008 could end up with a 31.5KB offset (63 hidden sectors * 512b sectors). Doesn’t matter what stripe unit size you have 4,8,16,32,64,128…. It will never make the equation spit out an integer! Therefor bad for performance!

So if your system is prior to Microsoft Windows Server 2008 or have disk partitions created by an earlier version, check the partition offset! It’s easily done by running this command:

wmic partition get BlockSize, StartingOffset, Name, Index

To check the stripe size you have to refer to your storage controller. Standard offset in Microsoft Windows Server 2008 and later is 1024KB and it doesn’t really matter what stripe unit size you have, you will still end up with an integer.

Log files

For SQL server log files you should use RAID 1 both for best read/write performance but also for the extra data security. In a raid one you can lose 50% of your disks without losing data, neither RAID 5 or RAID 10 can guaranty this data safety. It will however cost you half of the storage space.

Do you want to read more?
http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
Written by Jimmy May, Denny Lee and goes deeper into the techniques.

3Mar/120

SQL error after upgrading from express

A few days ago I helped a client update there SQL 2008 Express installation to a real SQL 2008 R2 installation. They have been hitting the wall for the 4Gb database size limit for some time so it was really time. I installed the new instance on the server and all went well. I just don't understand why SQL 2008 Express uses the default instance name MSSQLSERVER. Last time I had anything to do with the express version off SQL I think I remember the instance was named .\SQLEXPRESS or something similar. This isn't really a problem except that the default instance can't be accessed unless you name it. Some programs doesn't solve that issue as well as others and the default instance name of MSSQLSERVER I think should be reserved for the real installation.

How ever the move of the databases was really easy as well. Just took them offline, deattached them from the express installation and reattached them to the new SQL 2008 server install. Then when it was all done I uninstalled the express installation. That was when the problem started. I couldn't use the SQL Server 2008 Configuration Manager anymore. It just gave my this error:

Cannot Connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

Cannot Connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

After a little digging around I figured out that the WMI namespace had been uninstalled by the express version uninstall. From Microsoft I got the information that the 32-bit express installation shared files should still be on disk and that I could reuse them to fix the issue. According to Microsoft I should be able to do this:

mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

How ever the installation wasn't in that folder and I realized that the information was wrong. Part of the installation was actually there but at an other path. So I ended up with this:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

That solved the problem and I was able to run the SQL Server Configuration Manager again.

20Apr/100

SQL transaction logs growing and growing and …

Problems with growing transaction logs on SQL servers is a common problem. But many admins doesn't think twice about the problem, they just add more disk space. The transaction log is only needed when you want to do a restore to an other point then the last backup. So in many cases they are not needed right after a backup. Or you want to take a backup on them and then free up the space on you raid 10 disks that are more expensive then your backup media. To free up all the space used you need to set the database in simple recovery mode and then do a shrink on the DB. When that is done you place the DB back in the FULL RECOVERY MODE. So why not do this automatically ones a week right after the backup?

Just add an T-SQL section to your maintenance plan and run the code below:

EXEC sp_MSForEachDB
'ALTER DATABASE [?] SET RECOVERY SIMPLE;
DBCC SHRINKDATABASE (?, 10, TRUNCATEONLY);
ALTER DATABASE [?] SET RECOVERY FULL;'

Or if you want to build a custom list of databases, so you don't run this on all the DB's run this code to generate the T-SQL:

EXEC sp_MSForEachDB
'PRINT "ALTER DATABASE [?] SET RECOVERY SIMPLE";
PRINT "GO";
PRINT "DBCC SHRINKDATABASE (?, 10, TRUNCATEONLY);";
PRINT "GO";
PRINT "ALTER DATABASE [?] SET RECOVERY FULL;"
PRINT "GO";'

The important thing is to set TRUNCATEONLY, if you don't you will fragment the entire DB file. When you run the command above without the TRUNCATEONLY parameter it will take the last record in the DB file and place in the first hole of empty space in the DB file and so on. So you will create a fragmentation if you do so.
Another way to do it, which is slower and created more disk IO but can be done, is to do a transaction log backup and then just delete the transaction file backup.

MS documentation: http://msdn.microsoft.com/en-us/library/ms190488.aspx