{"id":466,"date":"2012-04-03T12:30:00","date_gmt":"2012-04-03T12:30:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Microsoft-Certified-Masters-its-like-chainsaw-art!.aspx"},"modified":"2013-02-23T09:16:21","modified_gmt":"2013-02-23T17:16:21","slug":"understanding-lob-data-20082008r2-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/understanding-lob-data-20082008r2-2012\/","title":{"rendered":"Understanding LOB data (2008\/2008R2 & 2012)"},"content":{"rendered":"

I know it’s been a while since I last blogged here on SQLskills. It’s been a busy few months with Immersion Events, conferences and even a bit of personal travel in there. And, we’ve also been blogging very regularly (weekly) at SQLServerPro here: Kimberly & Paul: SQL Server Questions Answered<\/a>. And, this is what’s brought me to write this post. A few days ago, I blogged What about moving LOB data?<\/a>\u00a0and in part I discussed some of the issues moving SQL Server LOB data using OFFLINE operations. In part II (What about moving LOB data while keeping the table online?<\/a>), I’m continuing the discussion with some additional points for ONLINE moves.<\/p>\n

However, I also wanted to make sure that people fully understood how SQL Server LOB data works with regard to the physical location within the database… So, I created a script to walk you through it. This post will show you the specifics…<\/p>\n

First, we’ll create a TestLOB database. Please note that this script drops TestLOB if it exists. If you already have a database with this name, it will be removed. Additionally, you will probably need to change the directory structure to something more appropriate on your system. These paths are the default data\/log locations for SQL Server 2012 running as a default instance (MSSQLServer).<\/p>\n

\r\nSET NOCOUNT ON\r\nGO\r\n\r\nUSE [master]\r\nGO\r\n\r\nIF DATABASEPROPERTYEX('TestLOB', 'status') IS NOT NULL\r\nBEGIN\r\n    ALTER DATABASE [TestLOB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\n    DROP DATABASE [TestLOB];\r\nEND\r\nGO\r\n\r\nCREATE DATABASE [TestLOB]\r\nCONTAINMENT = NONE\r\nON\u00a0 PRIMARY\r\n(\u00a0\u00a0 NAME = N'TestLOBPrimary'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\TestLOBPrimary.mdf'\r\n, SIZE = 5120KB , FILEGROWTH = 1024KB ),\r\nFILEGROUP [FG1]\r\n(\u00a0\u00a0 NAME = N'FG1File1'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\FG1File1.ndf'\r\n, SIZE = 61440KB , FILEGROWTH = 20480KB ),\r\n(\u00a0\u00a0 NAME = N'FG1File2'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\FG1File2.ndf'\r\n, SIZE = 61440KB , FILEGROWTH = 20480KB ),\r\nFILEGROUP [FG2]\r\n(\u00a0\u00a0 NAME = N'FG2File1'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\FG2File1.ndf'\r\n, SIZE = 61440KB , FILEGROWTH = 20480KB ),\r\n(\u00a0\u00a0 NAME = N'FG2File2'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\FG2File2.ndf'\r\n, SIZE = 61440KB , FILEGROWTH = 20480KB ),\r\nFILEGROUP [FG3]\r\n(\u00a0\u00a0 NAME = N'FG3File1'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\FG3File1.ndf'\r\n, SIZE = 61440KB , FILEGROWTH = 20480KB ),\r\n(\u00a0\u00a0 NAME = N'FG3File2'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\FG3File2.ndf'\r\n, SIZE = 61440KB , FILEGROWTH = 20480KB )\r\nLOG ON\r\n(\u00a0\u00a0 NAME = N'TestLOBLog'\r\n, FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\TestLOBLog.ldf'\r\n, SIZE = 102400KB , FILEGROWTH = 10240KB);\r\nGO\r\n\r\nUSE TestLOB;\r\nGO\r\n<\/pre>\n
Next, I’ll make the FG1 filegroup the default filegroup. For all objects that are not explicitly defined (using the ON clause), they will be created in FG1.<\/p>\n
\r\nALTER DATABASE TestLOB\r\nMODIFY FILEGROUP FG1 DEFAULT;\r\nGO\r\n<\/pre>\n
We’ll start by creating the TestLOBTable table within the FG1 filegroup<\/p>\n
\r\nCREATE TABLE TestLobTable\r\n(\r\nc1\u00a0 int identity,\r\nc2\u00a0 char(8000)\u00a0\u00a0\u00a0\u00a0\u00a0 default 'this is a test',\r\nc3\u00a0 varchar(max)\u00a0\u00a0\u00a0 NULL\r\n); -- will be created on FG1\r\nGO\r\n<\/pre>\n
We can verify the filegroup location for this object by using sp_help (review the results of the 5th grid returned).<\/p>\n
Next, we’ll add 1000 rows to the table. Each row is basically 8kb because of the size of the c2 column. For these inserts, we will insert data for c1 and c2 but the value for c3 is NULL.<\/p>\n
\r\nINSERT dbo.TestLobTable DEFAULT VALUES;\r\nGO  1000 -- create an 80MB table\r\n<\/pre>\n
To review the size (as well as the location) of this data, we’ll run a quick query against sys.dn_db_file_space_usage (which works for ALL databases in SQL Server 2012 prior to 2012 this DMV only returned space usage information for tempdb [DBID = 2]).<\/p>\n
Since there are no other objects in the database this very generic view of the location will be just fine:<\/p>\n
\r\nSELECT *\r\nFROM sys.dm_db_file_space_usage;\r\n<\/pre>\n
Or, if you’re on a version prior to SQL Server 2012 – you can look at the data space id set for the table (from sys.tables). Here’s the query you need:<\/p>\n
\r\nSELECT t.name AS [Table Name]\r\n, lob_data_space_id AS [Data Space ID]\r\n, ds.name AS [Data Space Name]\r\nFROM sys.tables AS t\r\nINNER JOIN sys.data_spaces AS ds\r\nON t.lob_data_space_id = ds.data_space_id;\r\nGO\r\n<\/pre>\n
Even if we create a clustered index on the table, the data doesn’t change filegroup. However, SQL Server does reorder the data within FG1 to match the key order defined by the clustering key. So, this operation can take quite bit of time and log space (depending on recovery model).<\/p>\n
\r\nCREATE UNIQUE CLUSTERED INDEX TestLobTableCL\r\nON TestLobTable (c1);\r\nGO\r\n<\/pre>\n
Again, we can verify that the object still lives on the FG filegroup by using sp_help (5th grid).<\/p>\n
Now, things will get a bit more interesting…<\/p>\n
In SQL Server 2012, you can rebuild a table even if it has LOB data. So, you might think that the following will move the entire table… let’s see:<\/p>\n
\r\nCREATE UNIQUE CLUSTERED INDEX TestLobTableCL\r\nON TestLobTable (c1)\r\nWITH (DROP_EXISTING = ON, ONLINE = ON)\r\nON FG2;\r\nGO\r\n<\/pre>\n
Now, check the results of sp_help. sp_help says it lives on FG2.\u00a0However, we curently don’t have any data in the LOB portion of this table. What if we add some??<\/p>\n
\r\nUPDATE TestLobTable\r\nSET c3 = REPLICATE (convert(varchar(max), 'ABC'), 8000) -- creates a 24 row\r\nWHERE c1 % 17 = 0; -- only 58 rows\r\nGO\r\n<\/pre>\n
Now, re-check the results from both sp_help and the LOB query. sp_help still thinks we’re writing to FG2 but that’s correct because sp_help ONLY returns information about the in_row portion of the table. Even if you had specified a location for LOB data using TEXTIMAGE_ON when creating the table… sp_help would still ONLY reference the location for the in_row structure.
\nThe only way to know where SQL Server LOB data is going is to use the query above. And, nothing you do can move the LOB data using just a standard rebuild.<\/p>\n
So, let’s try to rebuild the object again…<\/p>\n
\r\nCREATE UNIQUE CLUSTERED INDEX TestLobTableCL\r\nON TestLobTable (c1)\r\nWITH (DROP_EXISTING = ON, ONLINE = ON)\r\nON FG3;\r\nGO\r\n<\/pre>\n
And, recheck with our queries. Again, our in_row data has moved but not our LOB data.<\/p>\n
My SQLServerPro blog post mentioned that you do have one option using partitioning but a great point is that this requires Enterprise Edition. So, it’s not ideal. If you are running on Enterprise Edition (or Enterprise Eval\/Developer Edition), the scripts continue and show how to go throught he move process using a partition function\/partition scheme.<\/p>\n