Preview of the Table Size Advisor

As I get the Database Health project ready to release as a beta, more of the components are coming together.  For instance shown here is the Table Size Advisor which can be accessed from the Disk Space Report.

In this example you can quickly see that the Demographics column in the Person table is taking up 13MB or roughtly 88% of the entire table space.

Remember if you want to be part of the Beta available on September 1st, just register on this website and you will be informed when then Beta download is available.

Should I Use A Unique Identifier As A Primary Key?

Take the following sample code.    Four similar tables with an INT IDENTITY, BIGINT IDENTITY, and two with UNIQUEIDENTIFIERS, one using newid() and the other with newsequentialid().


CREATE TABLE [dbo].[intIdentityTest](
 [id] [int] IDENTITY(-2147483647,1) NOT NULL,
 [sometext] [varchar](200) NULL
) ON [PRIMARY];

GO
CREATE TABLE [dbo].[bigIntIdentityTest](
 [id] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
 [sometext] [varchar](200) NULL
) ON [PRIMARY];

GO
CREATE TABLE [dbo].[uniqueidentiferIdentityTest](
 [id] [uniqueidentifier] NOT NULL default newid(),
 [sometext] [varchar](200) NULL
) ON [PRIMARY];

GO
CREATE TABLE [dbo].[sequentialUniqueidentiferIdentityTest](
 [id] [uniqueidentifier] NOT NULL default NEWSEQUENTIALID ( ),
 [sometext] [varchar](200) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[intIdentityTest] DEFAULT VALUES;
INSERT INTO [dbo].[bigIntIdentityTest] DEFAULT VALUES;
INSERT INTO [dbo].[uniqueidentiferIdentityTest] DEFAULT VALUES;
INSERT INTO [dbo].[sequentialUniqueidentiferIdentityTest] DEFAULT VALUES;
GO 100000
-- insert 100,000 rows

Once the tables are created and we insert 100,000 rows into each, they are very similar, but they take up very different amounts of disk space.

Using the Database Health Reports application, we can easily check in on the disk space used.

From this you can see that the total spaced used by either of the uniqueidentifiers is 31% larger than the BIGINT examples.

But looking back at the sample code you might argue that it isn’t a very realistic example because there is not primary key with a clustered index (common practice).

So lets try it again with a primary key with a clustered index on each of the tables.


CREATE TABLE [dbo].[intIdentityTest](
 [id] [int] IDENTITY(-2147483647,1) NOT NULL,
 [sometext] [varchar](200) NULL,
 CONSTRAINT [PK_intIdentityTest] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

GO

CREATE TABLE [dbo].[bigIntIdentityTest](
 [id] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
 [sometext] [varchar](200) NULL,
 CONSTRAINT [PK_bigIntIdentityTest] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

GO

CREATE TABLE [dbo].[uniqueidentiferIdentityTest](
 [id] [uniqueidentifier] NOT NULL default newid(),
 [sometext] [varchar](200) NULL,
 CONSTRAINT [PK_uniqueidentiferIdentityTest] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

GO

CREATE TABLE [dbo].[sequentialUniqueidentiferIdentityTest](
 [id] [uniqueidentifier] NOT NULL default NEWSEQUENTIALID ( ),
 [sometext] [varchar](200) NULL,
 CONSTRAINT [PK_sequentialUniqueidentiferIdentityTest] PRIMARY KEY CLUSTERED
 (
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[intIdentityTest] DEFAULT VALUES;
INSERT INTO [dbo].[bigIntIdentityTest] DEFAULT VALUES;
INSERT INTO [dbo].[uniqueidentiferIdentityTest] DEFAULT VALUES;
INSERT INTO [dbo].[sequentialUniqueidentiferIdentityTest] DEFAULT VALUES;
GO 100000
-- insert 100,000 rows

Again, using the Database Health Reports application, I check in on the disk space used, which shows the following:

Here you can see that the amount of space taken up by the uniqueidentifier compared to BIGINT is over twice the size.  This additional size is mostly accounted for in the fragmentation caused by clustering on a uniqueidentifier.

 

Lets take a look at the index fragmentation, again using the Database Health Reports.

Here you can see that the uniqueidentifer test table is 99.4% fragmented.  As you continue to add more to this table, it will continue to be defragmented.

 

Summary

Using uniqueidentifers as primary keys, and as clustered indexes can lead to trouble over time.

1.  I suggest that if you can avoid it, you should avoid a uniqueidentifier for a primary key.

2.  If you must use a uniqueidentifier, the use NEWSEQUENTIALID ( ) to generate the unique identifer, not NEWID().

 

 

TSQL to Determine Plan Cache Size

In SQL Server 2012, there was a change to the dynamic management view that reports on the plan cache, so the query has changed. Here are the before and after queries to determine the plan cache size in SQL Server 2012, and prior to SQL Server 2012.

SQL 2012 Plan Cache Size

-- How big is the plan cache SQL 2012
select name, sum(pages_kb) /1024.0 MBUsed
from sys.dm_os_memory_clerks
where name = 'SQL PLans'
group by name;

Pre-SQL 2012 Plan Cache Size

-- How big is the plan cache pre SQL 2012
select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed
from sys.dm_os_memory_clerks
where name = 'SQL PLans'
group by name;

For more details on the plan cache, one time use queries and queries needing parameterization, see the Database Health Project.

Index Defragementation and Management

As I have been creating the Database Health project over the last couple of months, I have discovered that my favorite part of the whole project is the index defragmentation part of the project.  This has turned out to be more challenging that I originally expected, but a lot more interesting than I expected as well.

Yesterday I discovered a competitor to the index defragmentation part of Database Health, available from SQL Sentry, the Index Fragmentation Manager, for $795 per instance.  So to compare the Database Health project, with the beta being offered for free, with the ability to monitor hundreds of instances, it is possible that the Database Health application could save you (hundreds of instances time $795 per instance) a lot of money.

Here is a quick preview of the index defragmentation part of the Database Health project.

Just remember the  Database Health project will be available for the free Beta starting September 1, 2012.  Just register on my website, and you will be notified when the Beta is available for download.