Could be a page splits during the inserts due to uniqueidentifier data type.... Can you check it?

More over read this article as well

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008

Hi Oleoehoohoo,

This is because there  will be lot of page splits as the index is built on unique identifier column . This results in heavy fragmentation.

Check the fragmentation level with below query.

SELECT OBJECT_NAME(OBJECT_ID) As indexname, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'YourAuditDatabaseName'), NULL, NULL, NULL , 'SAMPLED') 
ORDER BY avg_fragmentation_in_percent DESC

Try to create the index on your 

PTID column and then check the fragmentation.Index on a unique identifier column is a bad practice.You should always choose a ever increasing column and less  byte size column for the index which will decrease the number of page splits and enhances your performance

Hello,

The dev team was doing bulk inserts and it takes 15 minutes to insert almost 6k rows and would timeout. After a few attempts the all the inserts succesfully get done in around 17 mins. This is our staging env. Here is a DDL of how the table looked before.

CREATE TABLE [dbo].[Vres](
	[VresID] [uniqueidentifier] NOT NULL,
	[Message] [varchar](max) NOT NULL,
	[PID] [uniqueidentifier] NOT NULL,
	[Segment] [int] NOT NULL,
	[MessageType] [nvarchar](50) NOT NULL,
	[PMID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_VresID] PRIMARY KEY CLUSTERED 
(
	[VresID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Vres]  WITH CHECK ADD  CONSTRAINT [FK_Vres_PPer] FOREIGN KEY([PID])
REFERENCES [dbo].[PPer] ([PID])
GO

ALTER TABLE [dbo].[Vres] CHECK CONSTRAINT [FK_Vres_PPer]
GO

The primary key on this table as shown above is on a GUID. However upon reaching me I suggested the devteam that adding an integer identity column as a primary key would fasten the inserts. The changes were made and upon testing we noticed that a multiple 6k row inserts were taking around 4mins.  Here is how the new DDL looks like.

CREATE TABLE [dbo].[Vres](
	[VresID] [uniqueidentifier] NOT NULL,
	[Message] [varchar](max) NOT NULL,
	[PID] [uniqueidentifier] NOT NULL,
	[Segment] [int] NOT NULL,
	[MessageType] [nvarchar](50) NOT NULL,
	[PMID] [uniqueidentifier] NULL,
	[PTID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_PTID] PRIMARY KEY CLUSTERED 
(
	[PTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Vres]  WITH CHECK ADD  CONSTRAINT [FK_Vres_PPer] FOREIGN KEY([PID])
REFERENCES [dbo].[PPer] ([PID])
GO

ALTER TABLE [dbo].[Vres] CHECK CONSTRAINT [FK_Vres_PPer]
GO



ALTER TABLE [dbo].[Vres] ADD  CONSTRAINT [PK_PTID] PRIMARY KEY CLUSTERED 
(
	[PTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

However the devteam is now saying that they dont want the primary key as the new identity column instead have a clustered index on the new columnn that I added  which is PTID but have the primary key as the VResID.

I am not sure if this is going to really help speed up the inserts here.

Experts please leave your valuable inputs here. Do you think it speeds up the inserts?

• Edited by oleolehoohoo Monday, September 07, 2015 6:58 AM

Hi Oleoehoohoo,

This is because there  will be lot of page splits as the index is built on unique identifier column . This results in heavy fragmentation.

Check the fragmentation level with below query.

SELECT OBJECT_NAME(OBJECT_ID) As indexname, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'YourAuditDatabaseName'), NULL, NULL, NULL , 'SAMPLED') 
ORDER BY avg_fragmentation_in_percent DESC

Try to create the index on your 

PTID column and then check the fragmentation.Index on a unique identifier column is a bad practice.You should always choose a ever increasing column and less  byte size column for the index which will decrease the number of page splits and enhances your performance

Can you use NewSequentialID() for your uniqueidentifier column as a default value? In this case the GUID will be sequential and so you can keep it clustered and would not need an extra key.

See also

http://blogs.msdn.com/b/dbrowne/archive/2012/07/03/how-to-generate-sequential-guids-for-sql-server-in-net.as

I agree with Naomi's suggestion to simply use NEWSEQUENTIALID().  Random key values are costly because of reduced buffer efficiency, which is especially costly against spinning media due to head movement.  See http://www.dbdelta.com/improving-uniqueidentifier-performance/ for a more detailed explanation.

Hi,

I agree with Dan's and Naomi's suggestion of using NEWSEQUENTIALID().That would help in decreasing the fragmentation level to a extent But I would suggest you to build the index on PTID column as it will give you advantage in terms of the size as it will be 4 bytes  whereas in case of unique identifier it will be 16 bytes.

The identity column is also a ever increasing column  which is another advantage.