This isn't covered by worker's comp

This checkup is about learning as much as it is fixing servers. What have I learned? What is it like to be a consultant, to be deep-diving into a variety of environments?

...
You should sign up for our free upcoming webcasts now.

iSCSI, NFS, FC, and FCoE Basics

iSCSI means you map your storage over TCPIP.  You typically put in dedicated Ethernet network cards and a separate network switch.  Each server and each storage device has its own IP address(es), and you connect by specifying an IP address where your drive lives.  In Windows, each drive shows up in Computer Manager as a hard drive, and you format it.  This is called block storage.

NFS means you access a file share like \\MyFileServerName\MyShareName, and you put files on it.  In Windows, this is a mapped network drive.  You access folders and files there, but you don’t see the network mapped drive in Computer Manager as a local drive letter.  You don’t get exclusive access to NFS drives.  You don’t need a separate network cable for NFS – you just access your file shares over whatever network you want.

Fibre Channel is a lot like iSCSI, except it uses fiberoptic cables instead of Ethernet cables.  It’s a separate dedicated network just for storage, so you don’t have to worry as much about performance contention – although you do still have to worry.

Fibre Channel Over Ethernet runs the FC protocol over Ethernet cables, specifically 10Gb Ethernet.  This gained niche popularity because you can use just one network (10Gb Ethernet) for both regular network traffic and storage network traffic rather than having one set of switches for fiber and one set for Ethernet.

Now that you’re armed with the basics, check out VMware’s PDF guide, then read on for my thoughts.

What I See in the Wild

1Gb iSCSI is cheap as all get out, and just as slow.  It’s a great way to get started with virtualization because you don’t usually need much storage throughput anyway – your storage is constrained by multiple VMs sharing the same spindles, so you’re getting random access, and it’s slow anyway.  It’s really easy to configure 1Gb iSCSI because you’ve already got a 1Gb network switch infrastructure.  SQL Server on 1Gb iSCSI sucks, though – you’re constrained big time during backups, index rebuilds, table scans, etc.  These large sequential operations that can easily saturate a 1Gb pipe, and storage becomes your bottleneck in no time.

NFS is the easiest way to manage virtualization, and I see a lot of success with it.  It’s probably an easy way to manage SQL clusters, too, but I’m not about to go there yet.  It’s just too risky if you’re using the same network for both data traffic and storage traffic – a big stream of sudden network traffic (like backups) over the same network pipes is a real danger for SQL Server’s infamous 15 second IO errors.  Using 10Gb Ethernet mitigates this risk, though.

Fibre Channel is the easiest way to maximize performance because you rule out the possibility of data traffic interfering with storage traffic.  It’s really hard to troubleshoot, and requires a dedicated full time SAN admin, but once it’s in and configured correctly, it’s happy days for the DBA.

...
You should sign up for our free upcoming webcasts now.

Over the years I’ve worked on a lot of backup and restore plans with SQL Server, and I learned the hard way that filegroup level restores don’t do exactly what you might think.

What if he only eats a FEW tables?

In this post, we’ll talk through a sample problem. I’ll explain while filegroup level backups don’t fit the bill to solve this problem, and then talk through a couple of alternatives that may make life easier.

Our Problem: We Need to Restore the Data for Some of the Tables in a Database

Let’s say you manage a database named ImportantDB. Some tables are updated daily in ImportantDB, and other tables are only updated once per month. The tables updated once per month are updated by a legacy application and you don’t have access to the source code.

Periodically, the monthly load will fail due to issues outside of SQL Server. When this occurs, the recommended procedure is to reset the monthly tables to the state they were in prior to the monthly load job beginning, and restart the process. (Essentially, you start over.)

Restoring the entire database takes a long time, and might remove changes to the “daily” tables as well.

Attempt 1: Filegroup Level Backups

In SQL Server we have the ability to back up and restore individual filegroups. In Enterprise Edition, SQL Server also has the ability to have the Primary and select other filegroups online and responding to queries while you are restoring individual filegroups.

For situations like this, it seems like filegroup level backups and restores will be the answer to our problem. The plan we make looks like this:

• Move the monthly tables into a filegroup named MONTHLYTABLES
• Set the filegroup as READ_ONLY

Whenever we need to load data into the filegroup, we plan to:

• Take a filegroup level backup of MONTHLYTABLES
• Set the MONTHLYTABLES filegroup as READ_WRITE
• Start the process to change data
• If there are any issues with the data load, restore over the filegroup from the READ_ONLY backup

Why Filegroup Level Restores Don’t Solve This Problem

The restore sequence described above in SQL Server won’t work, by design. SQL Server’s mission with filegroup level restores is described in Books Online here:

Each filegroup-restore sequence restores and recovers one or more offline filegroups to a point consistent with the database.

As soon as the MONTHLYTABLES filegroup is made READ_WRITE, we can no longer restore over it with a backup made from a prior point in time and bring it online. We can perform the filegroup level RESTORE if we’re in the appropriate recovery model, but in order to bring it online we also have to restore additional log backups to bring it to a point in time that is consistent with the rest of the database. (Don’t believe me? Check out Tibor Karaszi’s fine blog on the topic with sample code. Tibor’s examples are for the full recovery model— play around with things and you’ll see that you can’t make it work in the simple recovery model, either.)

Why does it have to be this way? It’s because SQL Server doesn’t track transaction status by filegroups, and its mission is to ensure transactional consistency within a database. If we could restore individual filegroups to different points in time and bring everything online, we wouldn’t have a way to ensure consistency.

Alternative 1: Database Snapshots

If we’re running SQL Server Enterprise Edition, we can automate a solution to our process by using a database snapshot. Our monthly load procedure would look like this:

• Create a database snapshot
• Start the process to change data
• If there are any issues with the data load, either:
  • Revert to the snapshot (this will impact the whole database)
  • Truncate and reload the monthly tables using the snapshot as a data source

This solution will work, but depending on the size of the monthly tables, it may have some performance problems. The database snapshot will use IO for all the data changed in the monthly tables. If the monthly tables are very large, we will end up spending a lot of writes against our snapshot file as well as the database we are repairing.

If it’s OK to lose any other data that has changed since the snapshot was taken, the revert option may work for us— provided that we don’t mind dropping any full text catalogs and rebuilding the transaction log.

The only time I really like a database snapshot option is when I’m 100% sure that other processes have not been updating data. That just isn’t the case for a lot of databases, so we may need to seek other options.

Alternative 2: Views

We could also solve this problem by moving the monthly tables into a new database named ImportantMonthly. Then, we would replace the original tables in ImportantDB with views. A simple view of “SELECT [columnnames] from ImportantMonthly.schema.table” would be put in place for each table.

Important DB is safe... and possibly has a friend.

With the views defined in this way, they would function just like tables and allow insert, update, delete, and select statements to be run against the base tables.

This would allow the process to become:

• Take a full database backup of ImportantMonthly
• Start the process to change data
• If there are any issues with the data load, restore over the ImportantMonthly database

The main gotcha to be aware with using views in this manner is that if the definition of the tables in ImportantMonthly changes, you may be required to refresh the metadata for the views with the procedure sp_refreshview.

Alternative 3: Synonyms

Similar to the view solution, we could move the tables into ImportantMonthly, and then replace the original tables in ImportantDB with synonyms.

Synonyms in SQL Server don’t have the same metadata issue as views, but there is one important thing to know: you cannot reference a synonym that is on the other side of a linked server.

The Devil is in the Details

This example highlights one thing to me: when you try to solve a problem with just one kind of tool, you have to make compromises. To build a strong, reliable solution and mitigate the risks, database administrators need the collaboration of whomever controls the application code for the database, whether that be a vendor or an in-house development team.

...
You should sign up for our free upcoming webcasts now.

This talk is aimed toward DBAs and developers who have worked with SQL Server >= 1 year. I cover LOTS of dynamic management views and tools to view table structures– it’s OK if you haven’t used them all before, you’ll still be able to get the concepts.

Want the scripts used in the talk? Scroll on down– scripts are included in this post below the video.

--Author: Brent Ozar PLF, LLC
--
--Purpose: Demo some of the peculiarities of heaps in SQL Server.
--This script is for use on TEST servers (not production). It creates
--and drops a database and uses undocumented stored procedures.
--
--References: We run this script in our video demo on heaps. Check out the
--video for intepretation of the script results and more.
--
--Usage notes: This demo uses both undocumented and documented procedures
--in SQL Server.
--
--The undocumented procedures are NOT meant to be run against
--production-- they are widely used, but be careful with all undocumented procedures
--and use at your own risk. ("Undocumented" means Microsoft doesn't officially
--support them.)
--
--This demo also uses sys.dm_db_database_page_allocations, which is a new undocumented
--DMV in  SQL Server 2012. If you're testing these scripts against an earlier version of
--SQL Server, you can use DBCC IND (also undocumented) to see the same information.
--We include an example of DBCC IND with the syntax in the demo.
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
 
--Recreate the HeapsOfHeaps database for testing.
--This drops the database-- use with care.  (Hopefully you
--don't have production databases with that name.)
 
WHILE @@TRANCOUNT > 0
    BEGIN
        ROLLBACK
    END
 
IF DB_ID('HeapsOfHeaps') IS NOT NULL
    BEGIN
        USE master;
        ALTER DATABASE HeapsOfHeaps SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE HeapsOfHeaps;
    END
GO
 
--Create and use the database.
--This creates it in the default location with default sizes-- change if you wish.
 
CREATE DATABASE HeapsOfHeaps;
GO
 
USE HeapsOfHeaps;
SET NOCOUNT ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
 
--Let's create a heap!
CREATE TABLE dbo.DataPile
    (
      datapileid BIGINT IDENTITY  NOT NULL,
      col1 VARCHAR(1024) NOT NULL
    );
GO
 
--Insert 1000 rows.
 
DECLARE @i INT = 1;
BEGIN TRAN
WHILE @i <= 1000
    BEGIN
        INSERT  dbo.dataPile (col1)
                SELECT  REPLICATE('A',200) ;
        SELECT  @i = @i + 1;
    END
COMMIT;
GO
 
--Here's a query that will show if you have heaps.
--You want to look for Index_Id = 0.
 
SELECT  sc.name AS [Schema Name] ,
        so.name AS [TABLE_NAME]
FROM    sys.indexes si
        JOIN sys.objects so ON si.object_id = so.object_id
        JOIN sys.schemas sc ON so.schema_id = sc.schema_id
WHERE   so.is_ms_shipped = 0  /* Not shipped by Microsoft */
        AND si.index_id = 0 /* Index Id 0 = A Heap */
        AND so.TYPE = 'U'; /* User table */
GO
 
--You can also look at the table and TRY to find indexes
EXEC sp_helpindex 'DataPile';
GO
 
--Let's look at the structure of our heap with DBCC IND.
--This is a special system command (undocumented!).
--PageType 10= an IAM page (Index Allocation Map).
 
--Parameters: DatabaseName, TableName, IndexID
 
DBCC IND('HeapsOfHeaps', 'DataPile',0)
GO
 
--In SQL 2012 we can also query a new DMV to see allocations!
--This DMV is also undocumented.
--Parameters: DatabaseId, ObjectId, IndexId, PartitionId, mode
 
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(),object_id('DataPile'),0,NULL,'DETAILED');
GO
 
--We can also get information about the table's fragmentation.
--This is a documented procedure, but I'm running it in detailed mode so it's going to look
--at all the pages in the active table. Be very careful if you run this in detailed mode
--against a production database.
 
SELECT alloc_unit_type_desc,
	index_depth, page_count, avg_page_space_used_in_percent,
	record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('DataPile'), 0, NULL,'detailed');
GO
 
--sys.dm_db_partition_stats looks at the metadata.
 
SELECT used_page_count, in_row_used_page_count, reserved_page_count, ROW_COUNT
FROM sys.dm_db_partition_stats
WHERE object_name(object_id) = 'DataPile';
 
--Let's count how many reads we have to do to scan the table.
--Run the select, then go to the Messages tab.
--Look at the number of "logical reads" we did.
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
SELECT *
FROM dbo.dataPile;
GO
 
--Let's make half of the rows have larger values in them.
 
UPDATE dbo.DataPile
SET col1=REPLICATE('B',1000)
WHERE dataPileid % 2 = 0;
GO
 
--How many reads does it take to scan the table now?
--Run the select, then go to the Messages tab.
--Look at the number of "logical reads" we did.
 
SELECT *
FROM dbo.dataPile;
GO
 
--Why did we do so many more logical reads????
--Let's find some information by looking at the table's fragmentation now.
--Note: look at the record_count column-- we didn't insert any records!
--It's counting wrong by the number of forwarded records.
--(That's documented in Books Online, by the way.)
 
SELECT alloc_unit_type_desc,
	index_depth, page_count, avg_page_space_used_in_percent,
	record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('DataPile'), 0, NULL,'detailed');
GO
 
--We can see something special happened reading, as well.
--Check out forwarded_fetch_count.
 
SELECT leaf_insert_count, leaf_update_count, forwarded_fetch_count
FROM sys.dm_db_index_operational_stats(db_id(),object_id('DataPile'),0,NULL);
GO
 
--What about deletes?
 
--Let's delete all of the rows from the table, except for five.
DELETE FROM dbo.datapile
WHERE datapileid > 5;
GO
 
--OK, we only have FIVE rows in this table now. We started with 1000.
--How many reads does it take to read now?
--Run the select, go to the messages tab, and look at logical reads.
 
SELECT *
FROM dbo.dataPile;
GO
 
--What changed?
--We got rid of MOST of the forwarded record pointers.
--But we still have 80+ pages allocated! For only five rows!
 
SELECT alloc_unit_type_desc,
	index_depth, page_count, avg_page_space_used_in_percent,
	record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('DataPile'), 0, NULL,'detailed');
GO
 
--sys.dm_db_partition_stats agrees that we still have LOTS of pages.
--It gets the row count right, by the way.
 
SELECT used_page_count, in_row_used_page_count, reserved_page_count, ROW_COUNT
FROM sys.dm_db_partition_stats
WHERE object_name(object_id) = 'DataPile';
GO
 
--Let's create a nonclustered index on our heap.
 
CREATE UNIQUE NONCLUSTERED INDEX ix_datapile_nc_datapileid ON dbo.DataPile (datapileid);
GO
 
--Let's look at what pages are being used for the nonclustered index.
--We're using the undocumented new DMV again.
--We have an INDEX page type instead of a DATA page type. */
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(),object_id('DataPile'),3,NULL,'DETAILED');
GO
 
--Now, let's take the index page ID:
--This is the allocated_page_page_id for the row where page_type_desc=INDEX_PAGE
--We'll plug it into DBCC PAGE.
--This is another undocumented procedure that lets us look at page data.
--Check out the values in the HEAP RID Column and record them.
 
DBCC TRACEON (3604);
DBCC PAGE (HeapsOfHeaps, 1, 284,3)
GO
 
--Now let's rebuild the heap.
--We can do that-- we can rebuild a heap! (On SQL Server 2008 and above.)
--Prior to SQL Server 2008, the most popular way to de-fragment a heap is to
--add and remove a clustered index--- but it has the same impact we'll see here.
 
ALTER TABLE dbo.DataPile REBUILD;
GO
 
--Let's check out our nonclustered index...
 
SELECT allocated_page_page_id, page_type_desc
FROM sys.dm_db_database_page_allocations(db_id(),object_id('DataPile'),3,NULL,'DETAILED');
GO
 
--Hey, wait a second, the PAGE IDs are different for the Non-Clustered index.
--And we didn't rebuild the non-clustered index--- we just asked for a rebuild of the heap!
--But yet our non-clustered index shows us that it is on totally new pages now.
--What's up with that????
 
DBCC TRACEON (3604);
DBCC PAGE (HeapsOfHeaps, 1, 370,3)
GO
 
-- Compare those HEAP RIDS to what you recorded before.
--We explain why this is and what this means in the video.
 
--Wow, rebuilding a big heap with nonclustered indexes could
--cause LOTS and LOTS of unexpected IO.
--Really, the times when we want to have a heap in SQL Server are the exception rather than the rule.
--We like to default to clustered indexes in SQL Server---
--unless we have performance tests showing that heaps are better for that use case!

...
You should sign up for our free upcoming webcasts now.