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 been on-site with clients this week and that topic came up again. I thought it would be useful to do a quick blog post showing how to use the diskpart and wmic tools. Google them for lots of info from the Microsoft site - but be careful not to play around with any of the destructive options on productions systems. The options I'm using below will not alter the disks in any way.

Note: This stuff applies to MBR disks, not GPT or dynamic disks. Although these require correct alignment too, I don't have any information on how to do it for those disks. The SQLCAT team will be publishing some guidelines but has not yet done so, AFAIK. Check out the SQLCAT team whitepaper Disk Partition Alignment Best Practices for SQL Server for full details on this topic.

Bring up a command prompt and type diskpart. You'll see something like:

C:\Users\Administrator>diskpart

Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: MONKEY

DISKPART>

Next you need to list the logical disks that Windows knows about. Type list disk. You'll see something like:

DISKPART> list disk

  Disk ###  Status      Size     Free     Dyn  Gpt
  --------  ----------  -------  -------  ---  ---
  Disk 0    Online       136 GB  1434 MB
  Disk 1    Online      1116 GB      0 B
  Disk 2    Online      2036 GB      0 B

DISKPART> 

Disks 1 and 2 are two RAID arrays I'm using right now for the performance benchmarking series I'm doing. Notice that the numbers in the Free column aren't correct - not sure why not. 

To see the partitions on a disk, you need to set the diskpart focus to be that disk. Type select disk X, where X is the disk you want to focus on. You'll see something like:

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART>

And now you can list the partitions on the disk using list partition. You'll see something like:

DISKPART> list partition

  Partition ###  Type              Size     Offset
  -------------  ----------------  -------  -------
  Partition 1    Primary           1116 GB  1024 KB

DISKPART>

This is the output from one of my Windows Servr 2008 servers, where the default partition offset is 1MB - which doesn't lead to perf issues.

On another Windows XP system, I get the following output:

DISKPART> select disk 0

Disk 0 is now the selected disk.

DISKPART> list partition

  Partition ###  Type              Size     Offset
  -------------  ----------------  -------  -------
  Partition 1    Primary            119 GB    32 KB

DISKPART>

This disk isn't aligned correctly. If this was a RAID array, I'd pay a perf penalty every time a read or write straddled a RAID stripe offset. See the blog post link at the top of this post for more details.

Unfortunately, diskpart isn't always the best tool to use to get partition offsets, as it rounds up the values, and when there are multiple partitions, it can be hard to tell exactly what's what, especially whtih lots of disks where you need to select each one and then list the partitions.

In this case, use wmic to get the exact numbers. The command is as follows:

wmic partition get BlockSize, StartingOffset, Name, Index

For my server, I get the following output:

BlockSize  Index  Name                   StartingOffset
512        0      Disk #1, Partition #0  1048576
512        0      Disk #2, Partition #0  1048576
512        0      Disk #0, Partition #0  1505755136 

For dynamic disks, use:

dmddiag.exe -v 

Now - go out to your servers and check the partition alignment - fixing this can give you up to 30-40% I/O performance boost!!

How do you fix it? Well, that's the downside - fixing it means reformatting the disk to have the correct partition offset or moving the data to a disk that already has the correct partition offset. Remember - although Windows Server 2008 creates disks with the correct offset, taking a disk that was created on Windows Server 2003 and attaching it to Windows Server 2008 will have no effect on the existing partition offset.

It's very commonly known that you can use the Script Wizard in Management Studio to script out tables or even an entire database. It's NOT commonly known that in SQL Server 2008, the wizard was upgraded and you can now script out the data too. The feature isn't enabled by default, which is why not many people know about it, and I couldn't find it described in Books Online either, further contributing to it's obscurity - but it's definitely there.

If you go into the wizard, and go down to the Table/View Options, there's a setting 'Script Data'. Set that to True and you'll get a bunch of INSERT statements in the resulting script too. Very cool.

Here's a screenshot showing what I mean (click it for a 1024x768 version).

Enjoy!

It's lunchtime for my class - time for a blog post! This is an interesting one that crops up every so often (just an hour ago on SQL Server Central) and is not known very widely at all.

There's a misconception that you cannot run DMVs in databases that have compatibility levels of 80 or less. It's not true.

Here's an example:

USE master;
GO
EXEC sp_dbcmptlevel AdventureWorks, 80;

USE AdventureWorks;
GO

SELECT [object_id], [index_id]
FROM sys.dm_db_index_physical_stats (
    DB_ID ('AdventureWorks'), NULL, NULL, NULL, NULL)
WHERE [avg_fragmentation_in_percent] > 30;
GO

You get the incredibly useful and explanatory message:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

What the heck does that mean? It really means that the query processor refused to recognize the DMV name because the database is running in 80 or lower compatibility level.

Here's the trick - execute the DMV in the context of database that's in 90 compatibility level or higher, and it will let you do it - even if you're actually targeting a database in a lower compatibility level.

USE master;
GO

SELECT [object_id], [index_id]
FROM sys.dm_db_index_physical_stats (
    DB_ID ('AdventureWorks'), NULL, NULL, NULL, NULL)
WHERE [avg_fragmentation_in_percent] > 30;
GO

object_id   index_id
----------- -----------
18099105    2
30623152    2
30623152    3
30623152    4
66099276    1
66099276    2

...

Note that I executed the DMV from the context of master, but targeted AdventureWorks, which is in the 80 compatibility level. 

Have fun!

Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes that are corrupt then you need to see all the errors - as it may look like all the errors are (relatively) benign but there's one error you didn't see that says a clustered index data page is corrupt.

That option is all well and good *except* when you run the DBCC command through SSMS. It will only show the first 1000 errors (long story, but let's just says it involves me being able to create corruption cases during 2005 development that would generate so many errors it would cause SSMS to crash) and there's no way to get around this. So, if you have more than 1000 errors, as in a case I'm helping with today where there are 19000+ errors, you can't use SSMS and expect to get them all back. Problem is, you don't know that you've got that many errors until you run it - so if you used SSMS and find there are more than 1000 errors, AND you want to do some analysis of them, you'll need to go and run it again using a different connection to the server.

So - something I advise is to run DBCC from the command line. Either osql or sqlcmd will do the trick. For instance:

osql -E -Q"DBCC CHECKDB (master) WITH ALL_ERRORMSGS, NO_INFOMSGS" -oC:\outputfile.txt

This will run the DBCC command and store the output in a file for later analysis. You can substitute sqlcmd for osql in the command above if you want.

Last week's survey was two-fold - what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009.

As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of respondents have a database over 100GB, with half of those being 500GB or more. One lucky (or unlucky, depending on how you look at it!) person gets to play with a 20+TB database. There some truly huge SQL Server databases out there - for instance the 1.1 petabyte astronomical database described here, which adds 1.4TB every night. Back in December 2003, the largest SQL Server database was a 5TB Verizon database. Now multi-TB databases seem quite common when talking to large customers.

The distribution curve for the number of databases also seems unsurprising, except for the uptick at the 150+ databases mark. Fully 1/4 of respondents are responsible for more than 200 databases! Even if the majority of the databases are small, that's still a huge amount of management to consider and context to have to know about.

So what was the point of this survey? Well, I've heard from quite a few people this year that some of their DBA colleagues have been let go and they've had to pick up more responsibility, whether they want to or not. Just last week, Microsoft laid off 3000 more people worldwide, including some in their various IT departments. In the DBA class we were teaching last week, it was obvious that some of the attendees were affected and had a lot more on their plates.

With the average size of databases growing, and the number of databases any one person is responsible for also growing, it's imperative that the overall environment is as easily managed as possible. What does this mean? Here's a 2-minute list of stuff off the top of my head that I think helps make an environment more manageable:

• Setting up each database so that the important parts can be recovered as quickly and easily as possible
• Setting up each database so that there are no performance bottlenecks (these two were both kind of addressed by my previous survey Physical database layout vs. database size)
• Making sure each database has the right backup strategy (from last week's survey Importance of having the right backups)
• Making sure that each database has the correct monitoring set up. For instance:
  • SQL Agent alerts on the instance for high-severity errors (see Easy monitoring of high-severity errors: create Agent alerts)
  • Monitoring data and log file sizes to avoid auto-growths
  • Monitoring index fragmentation levels
  • Pro-active monitoring for things like high disk-queue lengths or runaway queries
  • Regular consistency checks to find corruption
• Security is setup appropriately (see TechNet Magazine: feature article on Common SQL Server Security Issues and Solutions)
• Maintenance jobs are automated as much possible (e.g. backups, consistency checks, index maintenance, and statistics maintenance). There are a bunch of scripts people have published to help with this: Tara Kizer has a lot on her blog, Ola Hallengren has a good one here, as do many others (no offense to anyone else with scripts, these are just the two that spring to mind - by all means reply with a comment to let me know of your popular script)
• Database settings adhere to common best-practices (e.g. auto-shrink off, auto-grow on (and managed!), auto-create and auto-update statistics on, page checksums on)

And I'm barely touching the tip of the iceberg here. Most of these are just database maintenance best practices - with the idea that the healthier a database is, the less likely something will go wrong that demands DBA attention. And in an environment where potentially hundreds of databases are under the control of a single person, avoiding problems is of paramount importance.

Ok - cup of coffee later and I have more stuff to add to the list:

• Consistency of settings and scripts across database and instances, so what you're familiar with in one database context translates to all others
• Environment-wide run-book that covers all aspects of day-to-day operations, so all DBAs do the same thing and a DBA can help out or pick up databases easily
• Environment-wide disaster recovery guide - written by the most senior DBA and tested by all DBAs down to the most junior
• T-SQL source code control
• Management of access to databases
  • Physical access to servers is limited
  • Network access to servers is limited
  • SA access is limited
  • Developers can't deploy new code without going through QA first
  • And so on
• High-availability technologies in place to aid recoverability, with plenty of practice of failing over applications
• Use of tools to increase productivity. Most major 3rd-party tool vendors have tools that can help in a myriad of DBA activities - I'm not going to name any in particular as I don't want to favor any over any of the others.

Talking about tools, SQL Server 2008 itself has some stuff built in that can increase productivity. Kimberly did a great post last November on Central Management Servers - see SQL Server 2008 Central Management Servers - have you seen these?. There's also Policy-Based Management, which is a good first step along the road to centralized policy setting, testing, and enforcement (although it has some nits that need to be fixed in v2), and Performance Data Collection which can be used to easily collect perf data (same kind of stuff that Activity Monitor has) for multiple instances and stored in a single location - Kimberly will be blogging more about these two features in the future.

Overall, having a haphazard way of setting up and maintaining databases and instances is going to make it really hard to scale up size and number of databases in an environment without investing heavily in more people. Taking a more disciplined, planned approach, learning from others - using others' scripts and practices, and developing streamlined processes will lead to increased productivity, decreased stress, and happier, stronger DBAs. No-one wants to be in constant fire-fighting mode.

This has been a bit of a ramble, but that's the point of these editorial-style posts. I'm sure you know a lot of this, and I'm sure I've missed some obvious points, so feel free to comment with more tips, tricks, and links!

Next up - this week's survey!

Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is part of the FILESTREAM subsystem, and it's what fools the rest of the Storage Engine into accepting the FILESTREAM data as if it was real varbinary(max) data. If you look in the Books Online entry for sys.dm_os_wait_stats, which defines all the different wait types, it explains that the wait should only show up when FILESTREAM I/Os are occuring. However, this wait type shows up *all* the time - clearly a bug.

Turns out that it's a known bug that's been fixed - see KB 958942.

Not much to do here in Tokyo while waiting for the Bangkok flight except read and blog, and I've already done enough reading for one day on the flight here - so that leaves blogging!

Here's an interesting case that I got involved in on SQLServerCentral (here's the original thread, linked here with permission of the original poster). The problem was that every night the maintenance job would run and end up producing a stack dump, from a 211 error (which always indicates metadata corruption causing the Engine to trip over). I guessed system table corruption but the consistency checking portion of the maintenance job was apparently running fine. So I asked for one of the stack dumps, the output from the maintenance plan, and the latest SQL error log to have a look at (I have a weakness for these things, as you well know).

After having a look, I saw some strange behavior in the error log - every night there 10 or so DBCC CHECKDBs of master within about 2 minutes - corresponding to the consistency checking part of the maintenance job that was clearing running DBCC CHECKDB against all the system and user databases. Then I remembered that vanilla 2005 SP2 has two nasty maintenance plan bugs - one of which caused the consistency checking part of the maintenance plan to malfunction when run, and run DBCC CHECKDB against master every time instead of the database specified. Fixing that would then lead to the being able to properly run consistency checks and find the corrupt database.

So - moral of the story is to make sure you're not running vanilla 2005 SP2 (or one of the builds close to it, 3150 to 3158) otherwise you could get into problems with maintenance plans - worse still, you may think you've been running consistency checks all this time on your user databases, but if no-one's checking the SQL error logs, you could just be repeatedly consistency checking master.

The KB that describes the bug is 934459 (available at http://support.microsoft.com/kb/934459), or you can install SP3 (available at http://support.microsoft.com/?kbid=955706).

The May 2009 TechNet Magazine is now available online, and it's the annual security issue. In there is an article I wrote highlighting 10 common security issues (and solutions) you should worry about if you're not a security-savvy DBA. It covers:

• Physical security
• Network security
• Attach surface minimzation
• Service accounts
• Restricting use of administrator privileges
• Authentication
• Authorization
• SQL injection
• Disaster recovery
• Auditing

There are also two screencasts of me demonstrating Transparent Data Encryption and SQL Server Audit, both in SQL Server 2008. 

To quote myself from the end of the article:

As far as takeaways from this article are concerned, I want you to realize that there are some steps you need to go through to ensure the data you are storing in SQL Server is as secure as you need it to be. This is especially important when you inherit a SQL Server instance that someone else has been managing. It's just like buying a house from someone—you need to ask if the alarm works, if the yard is fenced in, and who has copies of the keys. Running through the list I've given in this article is a good start, but make sure you dig deeper in areas that are relevant to you.

Check it out at http://technet.microsoft.com/en-us/magazine/2009.05.sql.aspx.

Enjoy!

I came across a good post over at the Data Management section of Less Than Dot, describing how to scan through a network looking for unsecure SQL instances. It makes use of the free SQL Ping tool and describes automating the process using SSIS. This is pretty interesting to me as I've just finished writing an article on SQL Server security for the May issue of TechNet Magazine. You may be surprised to find what unmanaged or unsecure SQL instances exist on your corporate network...

For some reason I couldn't c&p the sqlping command line from that post - some control character I suspect. Here's an example command I ran on our home WLAN:

Sqlping3cl.exe -scantype range -startIP 192.168.1.0 -endIP 192.168.1.254 -output instances.csv

Checkout the post at http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/scan-network-for-sql-server-instances.

Back in 2005 Kimberly produced two very popular webcast series - an 11-part webcast series for TechNet called SQL Server 2005 for the IT Professional and a 10-part webcast series for MSDN called A Primer for Proper SQL Server Development. The webcast links and blog posts were broken for quite a while but now they're all fixed up and working again. I've created some web pages that link to all the webcasts and blog posts, along with abstracts. I've also included some more recent ones too and will be adding to the list over the next few weeks.

There's over 30 hours of good stuff to watch - check them out at http://www.sqlskills.com/webcasts.asp