Sarah Henwood

Validating What is Stored in a Partition/Filegroup

sarahhen — Mon, 23 Jul 2007 13:13:00 GMT

How do you validate what is stored in a specific partition and the filegroup the partition resides on? I have found this handy when designing a partition strategy and to double check it is behaving as I expect and my data is stored correctly for the requirements. It also can help if you document as part of your disaster recovery planning, you can then quickly refer to your documentation and identify the specific filegroup you need to restore to recover specific partitions.

For example, you have a data warehouse that stores previous fiscal year data by quarter for Fiscal Year 2005 through 2006. Your partition function and scheme are defined as follows (see end of notes if you want the filegroup creation so you can step through these commands):

create partition function pf_MyFyQuarters (datetime) as

range right for values

(

'2004-10-01 00:00:00','2005-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00', -- 2005

'2005-10-01 00:00:00','2006-01-01 00:00:00','2006-04-01 00:00:00','2006-07-01 00:00:00') -- 2006

create partition scheme ps_MyFyQuarters

as partition pf_MyFyQuarters

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9

fgAfter2006) -- partition 10

When SQL creates the partition scheme, it maps in order each partition to the filegroup(s) listed and each are numbered in order as the comments demonstrate above. In this example, because the partition function is defined with a 'range right' - partition 1 (assigned to file group fgBefore2005) stores all values less than 10/1/2004. Partition 2 (assigned to file group fg2005Quarter1) stores values >= 10/1/2004 and < 01/01/2005 and so on through partition 10 (assigned to fgAfter2006) that stores values >= 07/01/2006.

Next, to validate the scenario, create a table on the partition scheme with some data:

CREATE TABLE [dbo].[PartitionTable]

(

[DateColumn] [datetime] NULL

)

ON ps_MyFyQuarters (DateColumn)

insert PartitionTable

Values('09/30/2004') -- This will go on partition 1 (file group fgBefore2000)

insert PartitionTable

Values('09/30/2005') -- partition 5 (file group fg2005Quarter4)

insert PartitionTable

Values('10/01/2005') -- partition 6 (file group fg2006Quarter1)

insert PartitionTable

Values('03/31/2006') -- partition 7 (file group fg2006Quarter2)

insert PartitionTable

Values('07/04/2006') -- partition 9 (file group fg2006Quarter4)

You can then use the $PARTITION function to validate what is actually stored by the partition number and the corresponding filegroup based on what you defined in your partition scheme earlier:

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 1

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 5

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 6

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 7

SELECT * FROM PartitionTable

WHERE $PARTITION.pf_MyFyQuarters(DateColumn) = 9

You can also query from the DMV to list out the partition/scheme and validate what is stored on your filegroups. For example, another administrator created another partition and scheme - but accidentally stores it on the same filegroups. Note what the DMV query returns after you create another partition function and scheme:

create partition function pf_MyFyQuarters_B (datetime) as

range right for values

(

'2002-10-01 00:00:00','2003-01-01 00:00:00','2003-04-01 00:00:00','2003-07-01 00:00:00', -- 2003

'2003-10-01 00:00:00','2004-01-01 00:00:00','2005-04-01 00:00:00','2005-07-01 00:00:00') -- 2004

create partition scheme ps_MyFyQuarters_B

as partition pf_MyFyQuarters_B

(fgBefore2005, -- partition 1

fg2005Quarter1,fg2005Quarter2,fg2005Quarter3,fg2005Quarter4, -- partition 2, 3, 4, 5:

fg2006Quarter1,fg2006Quarter2,fg2006Quarter3,fg2006Quarter4, -- 6, 7, 8, 9:

fgAfter2006) -- partition 10:

-- this DMV query will list out what schemes and partition numbers are stored on your filegroups

-- note the different scheme and partition on the same filegroups

select ds.name AS [Filegroup Name], ds.type, destination_id AS [partition number], dds.partition_scheme_id, ps.name as [partition scheme]

from sys.data_spaces ds

join sys.destination_data_spaces dds

on (ds.data_space_id = dds.data_space_id)

join sys.partition_schemes ps

on (ps.data_space_id = dds.partition_scheme_id)

order by ds.name, ps.name ASC

--------- File/Filegroup Script For Examples Above --------------

create database my_db

-- Add filegroups that will contains partitioned values

alter database my_db add filegroup fgBefore2005;

alter database my_db add filegroup fg2005Quarter1;

alter database my_db add filegroup fg2005Quarter2;

alter database my_db add filegroup fg2005Quarter3;

alter database my_db add filegroup fg2005Quarter4;

alter database my_db add filegroup fg2006Quarter1;

alter database my_db add filegroup fg2006Quarter2;

alter database my_db add filegroup fg2006Quarter3;

alter database my_db add filegroup fg2006Quarter4;

alter database my_db add filegroup fgAfter2006;

-- Add files to filegroups

alter database my_db add file (name = 'fF05Q1', filename = 'C:\fF05Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter1;

alter database my_db add file (name = 'fF05Q2', filename = 'C:\fF05Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter2;

alter database my_db add file (name = 'fF05Q3', filename = 'C:\fF05Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter3;

alter database my_db add file (name = 'fF05Q4', filename = 'C:\fF05Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2005Quarter4;

alter database my_db add file (name = 'fF06Q1', filename = 'C:\fF06Q1.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter1;

alter database my_db add file (name = 'fF06Q2', filename = 'C:\fF06Q2.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter2;

alter database my_db add file (name = 'fF06Q3', filename = 'C:\fF06Q3.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter3;

alter database my_db add file (name = 'fF06Q4', filename = 'C:\fF06Q4.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fg2006Quarter4;

alter database my_db add file (name = 'fBefore05', filename = 'C:\fBefore05.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgBefore2005;

alter database my_db add file (name = 'fAfter06', filename = 'C:\fAfter06.ndf', size = 2 MB ,filegrowth = 2 MB ) to filegroup fgAfter2006;

How to: Partial Restore After Media/Drive Failure

sarahhen — Mon, 23 Jul 2007 13:06:00 GMT

Recently I had a few questions about how to go about partial restores if you have media failure that affects only specific filegroups, and your database is inaccessible because of the missing file(s). The steps while simple, are not readily apparent in example form in books online.

In this example you have a multi filegroup database in bulk_logged recovery model. The database consists of read/write and read only filegroups. I throw these factors in to add a little bit more variance to the example.

-- create example database and filegroups

CREATE DATABASE my_db

ALTER DATABASE my_db

SET RECOVERY bulk_logged

alter database my_db add filegroup fg_static_A;

alter database my_db add filegroup fg_static_B;

alter database my_db add filegroup fg_static_C;

alter database my_db add filegroup fg_readwrite_A;

alter database my_db add file (name = 'f_static_A', filename = 'C:\f_static_a.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_A;

alter database my_db add file (name = 'f_static_B', filename = 'C:\f_static_b.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_B;

alter database my_db add file (name = 'f_static_C', filename = 'C:\f_static_c.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_C;

alter database my_db add file (name = 'f_readwrite_A', filename = 'C:\f_readwrite_A.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_readwrite_A;

You have done some bulk loads of static data to partitions in specific filegroups. You then mark these filegroups as read only:

ALTER DATABASE my_db

MODIFY FILEGROUP fg_static_A READ_ONLY

ALTER DATABASE my_db

MODIFY FILEGROUP fg_static_B READ_ONLY

Your next scheduled full backup occurs. The full backup captures both read only and read/write filegroups:

BACKUP DATABASE my_db

TO DISK='C:\my_db_full.bak' WITH INIT

You then do some more bulk loads of static data and mark that specific filegroup as read only:

ALTER DATABASE my_db

MODIFY FILEGROUP fg_static_C READ_ONLY

You don't have the maintenance window right now to do perform a differentail or full file backup, and decide to do a transaction log backup. At this point, you will want to switch to full recovery before your next transaction log backup, take the transaction log backup and then switch back to bulk_logged. Otherwise, your bulk logged extents will keep getting backed up for your read only filegroups.

ALTER DATABASE my_db

SET RECOVERY FULL

BACKUP LOG my_db

TO DISK='c:\my_db_log.bak' with init

ALTER DATABASE my_db

SET RECOVERY BULK_LOGGED

Your database continues to be used and data is inserted/modified in the read/write filegroups. You then perform a partial backup of just your read/write filegroups:

BACKUP DATABASE my_db

READ_WRITE_FILEGROUPS

TO DISK='C:\my_db_read_write.bak' with init

More data modification activity occurs on the database and then unfortunately you have drive failure that results in one of your read/write filegroups to disappear. SQL Server was restarted and queries on the database fail because the file is missing.

To simulate this, stop SQL, move file f_read_write_A somewhere else and restart SQL. Try quering the database, it should fail because of the missing file.

What do you do next if you want to just restore the read/write filegroup in question and get your database back online as quick as possible?

-- #1 back up the tail of the log with no_truncate. You *must* do this before you can do anything else.

BACKUP LOG my_db

TO DISK='c:\my_db_log_tail.bak' wth init

with NO_TRUNCATE

-- #2 then restore just the damaged file(s)/filegroup from the *partial* backup you took earlier

RESTORE DATABASE my_db FILE='f_readwrite_A' FROM DISK='C:\my_db_read_write.bak'

WITH NORECOVERY

-- #3 restore all transaction log backup sequences ending with the latest tail you backed up.

RESTORE LOG my_db

from DISK='c:\my_db_log.bak'

with norecovery

RESTORE LOG my_db

from DISK='c:\my_db_log_tail.bak'

with recovery

Your database is now back online.

However, what if you lost one of your read only filegroups instead? For example, FILEGROUP fg_static_C. In this scenario, keep in mind that you've not yet performed a differentail or full backup since populating that filegroup and marking it read only. What steps would be required to restore that filegroup?

-- #1 back up the tail of the log with no_truncate. You *must* do this before you can do anything else.

BACKUP LOG my_db

TO DISK='c:\my_db_log_tail.bak'

with NO_TRUNCATE, init

-- #2 Start your restore sequence from the last full backup. In this scenario, you must go back to the full backup because the partial only captured the readwrite filegroups.

RESTORE DATABASE my_db FILE='f_static_C' FROM DISK='C:\my_db_full.bak'

WITH NORECOVERY

-- #3 restore all transaction log backup sequences ending with the latest tail you backed up.

RESTORE LOG my_db

from DISK='c:\my_db_log.bak'

with norecovery

RESTORE LOG my_db

from DISK='c:\my_db_log_tail.bak'

with recovery

Your database is now back online and ready to go!

Connection Failures, SQL 2005 Appears Unresponsive

sarahhen — Mon, 23 Jul 2007 12:30:00 GMT

Here is a situation one of my client's recently encountered on SQL Server 2005. It was incredibly painful and a challenge to determine what was happening, plus it's an issue that is supposed to be fixed in SP2. So wanted to share the details of the symptoms in case you experience it too.

My client is on a SP2+ build. They recently conducted a successfull migration from SQL Server 2000 to 2005. They have a very high heavy rate of OLTP activity. Things were chugging along just fine for a couple of hours after migration, and then all of a sudden web servers started throwing connectivity errors. A local connection to the server could not even be made. After establishing a connection using the DAC, first thing checked was the state of the SPID's on SQL - I fully expected to see excessive waits due to blocking or some other type of wait. However, in this case it was strange - everything looked as I would expect for a normal functioning system - a small handful of SPID's were waiting on something with short waits you would expect to see at any period in time, but nothing excessive or abnormal looking (no high cpu, no indication of any runaway or expensive queries etc.) The majority of SPID had a zero waittype and none were waiting on working thread. This is a very key symptom of the issue we encountered - that SPID do not look problematic and you do not have a blocking or other wait type of bottleneck. There also are no network connectivity issues or anything else that is typical of this type of symptom.

We then issued a DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') and then immediately, the issue cleared and connections could be made to SQL and processing continue. (Some notice resolution with DBCC FREEPROCCACHE - it is because freeing the procedure cache also dumps this store. For this issue, you only need to dump the tokenandpermuserstore.) I was very surprised since my client was already on SP2. So it appears there still is a different flavor of this issue out there with some varying symptoms. (RE: http://support.microsoft.com/kb/927396)

My client has a support case open with Microsoft and is still being investigated and debugged. In the meantime, the workaround was to monitor the size of the TokenAndPermUserStore and free it when it got over 40MB. Initially we started with a size of 100MB but still sporadically encountered the problem. (I suspect it may be more related to number of entries, but size is what we were able to work around with.) Below is a query you can use that will report the size and number of objects in the store if you want to track and trend. You can add in additional logic if you need to free the cache if it is over a certain size or number of objects.

SELECT SUM(single_pages_kb + multi_pages_kb) as CacheSize, entries_count,entries_in_use_count
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'

Hope this helps! And I will post with any new updates.

Introduction

sarahhen — Mon, 23 Jul 2007 12:10:00 GMT

Hello, I wanted to take a moment to introduce myself with my first blog and give you a bit of background about me. While I am new to blogging, I am not new to SQL Server. I started with SQL Server 4.2 back in the early nineties. I was supporting an application that was written in PowerBuilder that did screen scraping off of a CICS IBM mainframe, and it had a SQL Server database as its back end. One of my many different computer related responsibilities at the time was writing adhoc green bar reports in assembly on the mainframe. Once I learned the power of results set programming with T-SQL, I then wrote the minimum amount of assembly needed to create a text file and import it to SQL - then did all the rest of my scrubbing and report writing on SQL. No more moving registers around and packing and unpacking bytes for me!

My passion for SQL continued from there. I eventually went to work for Microsoft in 1998 because I wanted to really know the internals - especially corruption and performance. Since then I have worked with SQL Server in many different applications in the enterprise and have been through consulting with my clients on 4.2, 6.5, 7.0, 2000 and 2005. My focus has especially been on performance and tuning in the enterprise. This last May I finally decided to break out on my own and start my own consulting and explore the product again from the industry.

I am excited to start blogging. One of my passions is making concepts clear, understandable and easy to digest. My goal with my blogging is to bring to you examples, tips and tidbits from real world situations that are easy to understand, repeatable and have real world application. I expect to focus primarily in the area of performance and scalability, especially the nuances and changes from 2000 to 2005 and things you may see during or after migration. I hope you find my posts helpful and I look forward to hearing any comments and feedback from you!

-Sarah