Is Dynamic SQL ALWAYS Bad? - Follow Up

Last week, after a chat with Kimberley Tripp, I wrote this post. Kimberley has turned this conversation into two SQLMag articles, which probably explain the situation slightly more elequantly that I did...

See here and here. :)

How Clever Is your Index?

If you Seek an index, would you expect SQL to seek the index once for each query, or once for each operator, or once for each value? You would hope that it was once for each query, but is that true? Well, it depends. I've created a rather contrived example, to demonstrate SQL's behavior.

Firstly, I created and populated a table, and then create an index on it using the following script...

CREATE TABLE LogicalReadsTbl (Col1 INT)
DECLARE @i INT
SET @i = 1

WHILE @i < 100000
BEGIN
     INSERT INTO LogicalReadsTbl
     VALUES(@i)
     SET @i = @i + 1
END

CREATE NONCLUSTERED INDEX IX1 ON LogicalReadsTbl(col1)

...I have created a nonclustered index, but for this particular example, the behavior would be identical with a clustered index (although I will let you prove that for yourself)

Next, I need to make sure that my queries are going to span rows that are stored in multiple pages, so I ran a simple query, and included %%physloc%% (see this post) to see how my rows were organised.

Based on this, I choose to use values between 619 and 626, as the page break was on 623/623.

Next, I ran sys.dm_db_index_physical_stats against my table, to double check the number of levels in the index. As suspected (because of size), there were 2 levels.

So the next thing to do, was turn on IO statistics. (Please bare in mind that because I ran a query including %%physloc%%, I am already expecting all pages of this table to be in the buffer cache, and hence I should see no physical reads, only logical reads) Just for fun, I also turned on Include Actual Execution Plan, then I ran the following query, and checked out the results...

SELECT COL1
FROM LogicalReadsTbl
WHERE Col1 BETWEEN 619 AND 626

Table 'LogicalReadsTbl'. Scan count 1, logical reads 2

...Perfect! SQL performed an index seek, and then, as the pages were next to each other, it read them as one block.

Ok, so now lets look at another query. Now this query will only return 2 rows, not 8, and read exactly the same pages, so we should have an identical query plan, and identical IO stats...right???

SELECT
COL1
FROM LogicalReadsTbl
WHERE Col1 = 619 OR Col1 = 626

Table 'LogicalReadsTbl'. Scan count 2, logical reads 4

...Ok, that's not so good, despite the same execution plan, and the same environmental conditions, and despite the fact that this time, we are reading 1/4 of the number of rows, the IO has doubled. SQL has performed 1 seek, for each page it required.

So be careful, sometime SQL is not as clever as we like to think it might be!!!

Do you find this kind of thing interesting? Please let me know, as it's good to know how to use my blogging time!!!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Why Not To Shrink A Database

In my last post, about maintenance plans, see here I spoke about the fragmentation caused by shrinking a database, and promised a follow-up post, with a demo of the issue. Well, this is it!

The problem arrises from the way the shrink occurs, and this applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and of course auto shrink. Basically, what happens is that SQL Server goes to the end of the file, picks up pages one-by-one and moves them to the first available space in the file. This can essentially reverse the order of your pages, turning perfectly defragmented indexes and perfectly fragmenting them.

For this demo, I am going to use use a table I created in my post here about why not to use GUIDs as Primary Keys, as it gives me a near perfectly fragmented set of indexes that I can defrag, and then mess up again!

So here is the fragmentation (found using sys.dm_db_index_physical_stats) before I have done anything...

 (No column name)   index_type_desc                 avg_fragmentation_in_percent avg_page_space_used_in_percent
  FragWithNewID     CLUSTERED INDEX            98.80952381                              72.04514455
  FragWithNewID     NONCLUSTERED INDEX    96.96969697                              78.59694836
  FragWithNewID     NONCLUSTERED INDEX    98                                             64.22041018

...Almost perfect fragmentation, so lets rebuild these indexes, and check the stats again...

ALTER INDEX [PK_Table_1] ON [dbo].[FragWithNewID] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )
GO
USE [GUIDFragmentation]
GO
ALTER INDEX [IX_Col1] ON [dbo].[FragWithNewID] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )
GO
USE [GUIDFragmentation]
GO
ALTER INDEX [IX_Col2] ON [dbo].[FragWithNewID] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = NONE )
GO

(No column name) index_type_desc                avg_fragmentation_in_percent   avg_page_space_used_in_percent
FragWithNewID    CLUSTERED INDEX           0                                                 99.2190140845071
FragWithNewID    NONCLUSTERED INDEX   1.88679245283019                       97.881504818384
FragWithNewID    NONCLUSTERED INDEX   3.03030303030303                       97.3163825055597

...Ahhh, that's better. Now lets shrink the database and try again...

DBCC SHRINKDATABASE(N'GUIDFragmentation' )
GO

(No column name)    index_type_desc                avg_fragmentation_in_percent avg_page_space_used_in_percent
FragWithNewID       CLUSTERED INDEX           73.7704918032787                     99.2190140845071
FragWithNewID       NONCLUSTERED INDEX   45.2830188679245                     97.881504818384
FragWithNewID       NONCLUSTERED INDEX   72.7272727272727                     97.3163825055597

...Uh, oh dear! Lets not do that again!

The World's Worst Maintenance Plan

Below is the world's worst Maintenance Plan. Please try to ignore the fact that it is in Denali, and hence looks sexy!

This is a pretty standard maintenance plan. You might even have one like it in your environment, so what's so bad about it? Well, there are 3 issues, and they get progressively worse.

1) The first, is the fact that the Index Rebuild Task in SQL Maintenance Plans in "dumb". Or in other words, it blindly does through and rebuilds every index, regardless of whether it needs to be rebuilt or not. This is Ok if you have a nice big maintenance window, but if you in something approaching a 24/7 environment, as lots of us are these days, then you are likely to want to reduce your maintenance window as much as possible. Also, if you have Database Mirroring set-up (especially in SQL 2005) you might be flooding the network with your log stream. Even if you you don't have either of these considerations, I am still a great believer in not using more resources than you need to.

2) The second issue relates to the Update Stats Task. Now, not many people realise this, but when you rebuild your indexes, SQL Server takes advantage of having 100% stats at it's disposal, and updates the index stats based on a 100% sample. So at best, your Update Stats Task will be set to use 100% sample, and you are merely doing the work twice. In the worst case, you will be using a 10% or 20% sample to update your stats, and not only will you be doing the work twice, but you are going to end up with worse stats than you had originally.

3) Shrink is evil - Do not use! I mean it. The smallest problem with shrinking your database is that you should be managing your space pro-actively, and shrinking the files will just mean they have to grow again. The much worse side effect is that shrinking your files will cause hugh amounts of fragmentation, thus undoing all of the work your Index Rebuild has done.

In my next post, I will do a demo of the fragmentation caused by shrinking a database.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

To GUID Or Not To GUID?

Sometime, I come across people who are in the habit of using GUIDs as Primary keys (and building a Clustered index on that primary key).

Well, the idea of a Primary Key is that it uniquely identifies records in a table, and the idea of a GUID is that it's unique, so that's fine, right? Wrong!

There are a couple reasons for this, the first of which is fragmentation. If you create you GUID values by using a DEFAULT Constraint that calls the NEWID() function, then it creates the 16-Byte numbers in a random order, which means that in order to maintain the logical order of the rows, (remember that a clustered index does NOT maintain physical order of rows see this post) then it is going to have to shift the logical order of rows in order to insert the new values. This can lead to page splits, and thus fragmentation.

But it gets worse than this if you have non-clustered indexes on the table, because each of these indexes will need to have their pointers to the new CI pages updated, leading to extra overhead, and fragmentation of those indexes as well. You know I like to prove things, so lets take a look at an example...

The first thing I did, was create a database, with one table using the following script...

CREATE TABLE FragWithNewID
 (
 ID uniqueidentifier NOT NULL,
 Col1 nchar(10) NOT NULL,
 Col2 int NOT NULL
 )  ON [PRIMARY]
GO
ALTER TABLE FragWithNewID ADD CONSTRAINT
 DF_Table_1_ID DEFAULT NEWID() FOR ID
GO
ALTER TABLE FragWithNewID ADD CONSTRAINT
 PK_Table_1 PRIMARY KEY CLUSTERED
 (
 ID
 )
CREATE NONCLUSTERED INDEX [IX_Col1] ON [dbo].[FragWithNewID]
(
 [Col1] ASC
)
CREATE NONCLUSTERED INDEX [IX_Col2] ON [dbo].[FragWithNewID]
(
 [Col2] ASC
)

 ...Next, a simple script to insert some values into the table...

INSERT INTO FragWithNewID (Col1, Col2)
VALUES('aaaaaa', 1)
GO 10000

...So now, lets check out the fragmentation. To do this, I will use the DMV sys.dm_db_index_physical_stats (Yes, I know it's actally a DMF, but like everybody else, I tend to use the term DMV collectivly)...

SELECT OBJECT_NAME(object_id), index_type_desc, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('GUIDFragmentation'),OBJECT_ID('FragWithNewID'),NULL,NULL,'DETAILED')
WHERE index_level = 0

...The results are staggering! Almost perfect fragmentation of all 3 indexes...

 (No column name)   index_type_desc                   avg_fragmentation_in_percent    avg_page_space_used_in_percent
  FragWithNewID     CLUSTERED INDEX           98.80952381                              72.04514455
  FragWithNewID     NONCLUSTERED INDEX   96.96969697                              78.59694836
  FragWithNewID     NONCLUSTERED INDEX   98                                             64.22041018


...So what about if you use NEWSEQUENTIALID(), rather than NEWID()? Well, it is the better of the two evils, as it will not cause anything like as much fragmentation, because the GUIDs will be generated in order, but you are still using a 16-Byte value, rather than a 4-Byte INT column, with the IDENTITY property set! This means that your rows will take up more space on the page, meaning you will have move pages, and thus less IO performance. Also, remember that the clustered key is not just stored in your CI, it is stored in your non-clustered index as well, so all indexes on the table will be less efficient.

I'm not saying UNIQUEIDENTIFIERs don't have their place, they certainly do, but that place is not in a clustered index! I saw one instance where a third party application insisted that the PK was a GUID, the recommendation I made was to have the PK constraint on the UNIQUIEIDENTIFIER, and then create the clustered index on a different column with a UNIQUE constraint. This column was an INT with the IDENTITY property set!

There is always a way around things! :)

Why Should I Rebuild My Clustered Index? Part II

Following on from my post, regarding columns not being physically deleted until a clustered index is rebuild (see here), I just wanted to mention that somewhat surprisingly, the same applies if you increase the width of a column.

If you expand a column's width, SQL does not alter the existing column, it creates a new one. Then, similarly to deleting a column from a table, the original column is not deleted until the CI has been rebuilt.

Ok, ok, lets prove it! :)

First off, I'll create a table to use as a test...

...Now, lets see which page is storing the data, by using %%physloc%% (see here and yes, for those of you following my blog, this is my new favourite function. Thanks Charles! :-) )...

...So now we have a page number, (26901 in my case) we will have a look inside it using DBCC PAGE...

...When I ran this, I saw the following (partial) results...


Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x8 Length 6000 Length (physical) 6000
Big_Col1 = Big text


Slot 0 Column 3 Offset 0x1778 Length 400 Length (physical) 400
Big_Col2 = More text
...So now, lets resize the column, and then look inside the page again...

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x8 Length 6000 Length (physical) 6000
Big_Col1 = Big text


Slot 0 Column 67108865 Offset 0x1778 Length 0 Length (physical) 400
DROPPED = NULL
Slot 0 Column 3 Offset 0x190f Length 400 Length (physical) 400
Big_Col2 = More text
...As you can see from the output above, the column has been marked as a "Ghost Column", but has not been physically deleted. It will remain this way until the CI is rebuilt (or created).

Can this get worse? Well...yes it can! What happens if I expand this column again, this time to 1000 characters. In theory, we have a 4-byte int column, a 6000-byte nchar and a 2000-byte nchar. The problem is, we will also have the 2 400-byte "Ghost" columns, making the row size 8804-bytes. This is a problem because the maximum row size is 8060-bytes. So what will happen?

Let's alter the table again, this time increasing the size of Big_Col_1 and then look  at the Page again...

ALTER TABLE dbo.ResizeColumnDemoSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x8 Length 6000 Length (physical) 6000
Big_Col1 = Big text


Slot 0 Column 67108865 Offset 0x1778 Length 0 Length (physical) 400
DROPPED = NULL
Slot 0 Column 3 Offset 0x190f Length 400 Length (physical) 400
Big_Col2 = More text
...So where is the old version of Big_Col_1? Physically deleted? No! Lets look at some system tables...

...The output below, clearly shows that it has pushed the original version of the column off the page, into the "Small LOB" store. This means that not only are we still storing the original column, we are also storing a 24-byte pointer to it in the ROW_OVERFLOW_DATA allocation unit!

object_id               rows           type_desc                                       total_pages
311672158           1                 IN_ROW_DATA                               2
311672158           1                 ROW_OVERFLOW_DATA               2

...The reason that each allocation unit shows a value of 2 for total pages, is because each allocation unit has a separate IAM page.

     DROP CONSTRAINT DF_Table_1_Big_Col1
GO
ALTER TABLE dbo.ResizeColumnDemo
     DROP CONSTRAINT DF_Table_1_Big_Col2
GO

ALTER TABLE dbo.ResizeColumnDemo
     ALTER COLUMN Big_Col1 NVARCHAR(4000) NOT NULL
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col1 DEFAULT (N'Big text') FOR Big_Col1
GO
ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col2 DEFAULT (N'More text') FOR Big_Col2
GO

SELECT object_id, rows, type_desc, total_pages
FROM sys.partitions p
INNER JOIN sys.allocation_units a
     ON p.partition_id = a.container_id
WHERE object_id = OBJECT_ID('dbo.ResizeColumnDemo')

ALTER TABLE dbo.ResizeColumnDemo
     DROP CONSTRAINT DF_Table_1_Big_Col1
GO

ALTER TABLE dbo.ResizeColumnDemo
     DROP CONSTRAINT DF_Table_1_Big_Col2
GO

ALTER TABLE dbo.ResizeColumnDemo
     ALTER COLUMN Big_Col2 NVARCHAR(500) NOT NULL
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col1 DEFAULT (N'Big text') FOR Big_Col1
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col2 DEFAULT (N'More text') FOR Big_Col2
GO

DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2008',1,26901,3)

SELECT sys.fn_PhysLocFormatter(%%physloc%%), *
FROM ResizeColumnDemo

CREATE TABLE dbo.ResizeColumnDemo
 (
 ID int NOT NULL IDENTITY (1, 1),
 Big_Col1 nchar(3000) NOT NULL,
 Big_Col2 nchar(200) NOT NULL
 )  ON [PRIMARY]
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col1 DEFAULT N'Big text' FOR Big_Col1
GO
ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col2 DEFAULT N'More text' FOR Big_Col2
GO

INSERT INTO dbo.ResizeColumnDemo DEFAULT VALUES
GO

Logical Order NOT Physical Order

There is a common misconception, that your data will physically be stored in the order determined by your Clustered Index, but this is not true. A CI in SQL will logically order your data, but not physically order it. Take the table below as an example...

CREATE TABLE LogicalOrderDemo
(
Entry_ID  INT,
Large_Column NCHAR(500)
);

CREATE CLUSTERED INDEX CI__LogicalOrderDemo ON LogicalOrderDemo(Entry_ID);

INSERT INTO LogicalOrderDemo
VALUES (2,'This is a large column'),
(3,'This is a large column'),
(4,'This is a large column'),
(5,'This is a large column');

...If we now use sys.fn_physlocformatter (see this post for more details) to find the page number, and then look inside the page using DBCC PAGE, to view the row offset table...

SELECT sys.fn_PhysLocFormatter(%%physloc%%), *
FROM LogicalOrderDemo;

DBCC TRACEON(3604)

DBCC PAGE(Adventureworks2008,1,29012,1);

...If you scroll to the bottom of the output, you will see a row offset table, similar to the one below. The row offset table tells SQL the physical offset of each slot, from the beginning of the page. A slot is a logical container for a row. The offsets are in reverse order, so Slot 0 holds the first row (With an Entry_ID of 2)...

...So now, lets go ahead and insert another row. This time, we will insert a row with an Entry_ID value of 1. As we have a Clustered Index on this column, then we know that SQL will insert it into Slot 0, in order to enforce a logical order to the rows, but will it physically re-order the data on the page? Lets see...

...No! You can see from the offset table that it has inserted the row at the end of the page. You can interpret this because Slot 0 will contain our new data, but its offset value is the largest in the table, meaning that it is furthest away from the beginning of the page.

In reality, it has inserted it in the first free row space on the page, but as we have not deleted any data, in our case, that is at the end.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Why Should I Rebuild My Clustered Index? Part I

I am currently working in an environment, where end users are able to create their own tables, and then subsequently add and remove columns from those tables, through a GUI. (I know, I know - I didn't design it!!!)

The point is, that a lot of these tables are pretty large, and because of a lack of Clustered Index rebuilds, they are a lot larger than they need to be. What do I mean? Well, you may be surprised to know, that when you drop a column, it does not actually delete the column from the pages that store the table. All it does is delete the metedata describing the column. (And yes, of course I'll prove it!)

Take the following table as an example...

 ...Now we have a table, populate with a few rows, we need to look inside a page of this table, and see how the data is stored. We will do this using DBCC PAGE, where we will pass in the database, the file, the page ID and finally the print option that we want. We will choose option 3, which gives us the most detail. (Note that if you run this script, your page ID will almost certainly be different to mine)

 CREATE TABLE AlterTableDemo
(
Entry_ID  INT   IDENTITY,
Large_Column NCHAR(500)
);

CREATE CLUSTERED INDEX CI__AlterTableDemo ON AlterTableDemo(Entry_ID);

INSERT INTO AlterTableDemo (Large_Column)
VALUES ('This is a large column');
GO 5

Before we do that however, we need to find out the page ID that our table is stored on. To do this, we will use the undocumented fn_physlocformatter() function. (Please see this post for more info)...

SELECT sys.fn_PhysLocFormatter(%%physloc%%), *
FROM AlterTableDemo;

DBCC TRACEON(3604)

DBCC PAGE(Adventureworks2008,1,29014,3);

...A portion of the DBCC PAGE output is shown below...

...You can see from this output, that as expected, slot 0 (the first logical row) contains our 2 columns. So now, lets delete our text column...

ALTER TABLE AlterTableDemo
 DROP COLUMN Large_Column;

...So now, if we look inside the page again, surly there will only be one column? Or will there? Lets have a look...

...From this output, you can clearly see, that although the column has been marked as dropped, the physical length is still 100 bytes, meaning that the column has not been physically removed. This is done as a performance optimization, and in fact, this column will not be physically removed, until we rebuild the Clustered Index on the table.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Contention On The Heap

I was reading a very interesting blog post from Paul Randal, called Lock Logging and Fast Recovery. The theme was log internals, but it set my train of though to how much concurrency you can achieve on a heap, with no non-clustered indexes, and the results may (or may not surprise you).

I created a table called ConcurrencyTest, and populated it, with the following script...

USE Adventureworks2008
GO

CREATE TABLE ConcurrencyTest
 (
 PKCol int NOT NULL IDENTITY (1, 1),
 TextCol varchar(50) NOT NULL
 )  ON [PRIMARY]

GO
ALTER TABLE ConcurrencyTest ADD  CONSTRAINT DF_ConcurrencyTest_TextCol  DEFAULT ('Test Data') FOR [TextCol]
GO

CREATE UNIQUE CLUSTERED INDEX Clust ON ConcurrencyTest(PKCOL)
GO

INSERT INTO ConcurrencyTest DEFAULT VALUES;
GO 10000

...I then picked 2 rows at random, and used %%physloc%% to see what pages the rows were stored on. (See my post What Files and Pages Is My Table Stored On? for more info).

The results below, show that the 2 rows are on separate pages...

...I then opened 2 new query windows, and SQL assigned them the session IDs 53 and 57 respectively.

Next, in each of the query windows, I began an explicit transaction (in order to make SQL hold the locks) and ran an update statement against the 2 rows I choose a moment ago...

(Session 53)
BEGIN TRANSACTION
UPDATE ConcurrencyTest
SET TextCol = 'UpdatedValue'
WHERE PKCol = 1

(Session 57)
BEGIN TRANSACTION
UPDATE ConcurrencyTest
SET TextCol = 'UpdatedValue'
WHERE PKCol = 5000

...I set both queries running, and a quick glance at sys.dm_exec_requests...

SELECT session_id, blocking_session_id, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0

...showed that there were no blocks (as you would expect). The same would hold true if I dropped the Clustered index and created a non-clustered index on the TextCol column (Although I will let you prove that for yourself)

Looking at sys.dm_tran_locks, shows that exclusive locks have been taken on the keys, and Intent exclusive locks have been taken on the pages and also the table...

SELECT * FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
GO

...Again, this is exactly as you would expect. The interesting behavior occurs when all indexes are removed from the table. I dropped the clustered index, rolled back the 2 transactions, and then ran queries again. This time, when I looked at sys.dm_exec_requests, I saw the following results...

...As you can see, this time there is a resource wait on the RID of the first row, even though the where clauses would prevent the same row being updated by both queries.

A look at sys.dm_tran_locks, shows slightly different locks and holds the reason for this contention...

...Instead of locking an index key, SQL has had to take out locks on the RIDs, which are an internal unique identifier found on heaps.

Basically, if there is no clustered index on a table, and the column being updated is not included in the key of a non-clustered index, then SQL has no key to lock and instead locks the RIDs. However, if there is no index, then the data can be stored in any order, which means that a row can move between slots on a page, or if the page is full, could even move to a different page. Therefore, SQL can not allow concurrency across the 2 updates, because although unlikely, there is no guarantee that the 2 updates will not conflict with each other, and SQL Server does not support lost updates.

The moral of the story? Always use indexes....!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom