SQL Server Performance – Comparing PageSplits/sec with Batch Requests/Sec

perfmonsql server

I read here (http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm) that having a high value of PageSplits/sec compared with that of BatchRequests/sec (in the link I posted there's a reference value of 20%) it's a bad thing and a possible I/O problem).

I tried to track this two values in a production system using performance monitor and this is what I got:

Performance Monitor

In RED it's the PageSplits/sec., the other is the BatchRequests/sec.

The scale for both it's 1,0 (I hope I'm not failing in comparing them).

Is there a problem?

EDIT:

Since Datagod asket for a description of the tables involved I post here the creation script for the biggest table and a probable culprit for our performance issues. This table holds something like 3.5 million rows and is usually both very read intensive and write intensive

/****** Object:  Table [dbo].[e1_tur_ordini_giorno]    Script Date: 11/09/2015 17:31:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[e1_tur_ordini_giorno](
    [id] [varchar](36) NOT NULL CONSTRAINT [DF__e1_tur_ordin__id__5E7FE7D2]  DEFAULT (N'0'),
    [dt_ordine_giorno] [date] NULL CONSTRAINT [DF__e1_tur_or__dt_or__5F740C0B]  DEFAULT (NULL),
    [n_ore] [decimal](10, 2) NULL CONSTRAINT [DF__e1_tur_or__n_ore__60683044]  DEFAULT (NULL),
    [hh_inizio] [varchar](10) NULL CONSTRAINT [DF__e1_tur_or__hh_in__615C547D]  DEFAULT (NULL),
    [hh_fine] [varchar](10) NULL CONSTRAINT [DF__e1_tur_or__hh_fi__625078B6]  DEFAULT (NULL),
    [overload] [int] NULL CONSTRAINT [DF__e1_tur_or__overl__63449CEF]  DEFAULT (NULL),
    [nota] [varchar](255) NULL CONSTRAINT [DF__e1_tur_ord__nota__6438C128]  DEFAULT (NULL),
    [fk_e0_turno] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__652CE561]  DEFAULT (NULL),
    [fk_e2_per_dipendente] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e2__6621099A]  DEFAULT (NULL),
    [fk_e1_tur_orario_servizio] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e1__67152DD3]  DEFAULT (NULL),
    [fk_e0_gg_operativo] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__68FD7645]  DEFAULT (NULL),
    [fk_e0_gg_paghe] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__69F19A7E]  DEFAULT (NULL),
    [fk_parent_ordine_giorno] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_pa__6AE5BEB7]  DEFAULT (NULL),
    [fk_e0_tipo_rischio] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__6BD9E2F0]  DEFAULT (NULL),
    [fk_e0_utente_inserimento] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__6CCE0729]  DEFAULT (NULL),
    [fk_e0_utente_modifica] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__6DC22B62]  DEFAULT (NULL),
    [dt_modifica] [datetime2](0) NULL CONSTRAINT [DF__e1_tur_or__dt_mo__6EB64F9B]  DEFAULT (NULL),
    [dt_inserimento] [datetime2](0) NULL CONSTRAINT [DF__e1_tur_or__dt_in__6FAA73D4]  DEFAULT (NULL),
    [fk_e0_prof_nodo] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e0__709E980D]  DEFAULT (NULL),
    [riga] [varchar](20) NULL CONSTRAINT [DF__e1_tur_ord__riga__7192BC46]  DEFAULT (NULL),
    [dt_paghe] [date] NULL CONSTRAINT [DF__e1_tur_or__dt_pa__7286E07F]  DEFAULT (NULL),
    [fl_bloccato] [bigint] NULL CONSTRAINT [DF__e1_tur_or__fl_bl__737B04B8]  DEFAULT ((0)),
    [fl_temporaneo] [smallint] NULL CONSTRAINT [DF__e1_tur_or__fl_te__746F28F1]  DEFAULT ((0)),
    [tipo_riga] [varchar](12) NULL CONSTRAINT [DF__e1_tur_or__tipo___75634D2A]  DEFAULT (N'N'),
    [hh_ordinarie] [float] NULL CONSTRAINT [DF__e1_tur_or__hh_or__76577163]  DEFAULT ((0)),
    [tipo_modifica] [varchar](12) NULL CONSTRAINT [DF__e1_tur_or__tipo___774B959C]  DEFAULT (N'N'),
    [fk_e1_tur_servizio] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e1__783FB9D5]  DEFAULT (NULL),
    [codice_importazione] [varchar](45) NULL CONSTRAINT [DF__e1_tur_or__codic__7933DE0E]  DEFAULT (NULL),
    [fk_e1_tur_testata] [varchar](36) NULL CONSTRAINT [DF__e1_tur_or__fk_e1__7A280247]  DEFAULT (NULL),
    [fl_annullato] [smallint] NULL CONSTRAINT [DF__e1_tur_or__fl_an__7B1C2680]  DEFAULT ((0)),
    [fl_compensativo] [smallint] NULL CONSTRAINT [DF__e1_tur_or__fl_co__7C104AB9]  DEFAULT ((0)),
    [descrizione] [varchar](255) NULL,
    [fl_recupero] [tinyint] NULL,
    [fk_e0_segmento_dip] [varchar](36) NULL,
    [fl_confermato] [tinyint] NULL CONSTRAINT [DF_e1_tur_ordini_giorno_fl_confermato]  DEFAULT ((0)),
    [fk_e1_tur_pian_reperibilita] [varchar](36) NULL,
    [dt_competenza] [date] NULL,
    [fl_scavallo] [tinyint] NULL,
CONSTRAINT [PK_e1_tur_ordini_giorno_id] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_1] FOREIGN KEY([fk_e0_utente_inserimento])
REFERENCES [dbo].[users] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_1]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_10] FOREIGN KEY([fk_e0_gg_paghe])
REFERENCES [dbo].[e0_conf_gg_paghe] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_10]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_11] FOREIGN KEY([fk_e1_tur_servizio])
REFERENCES [dbo].[e1_tur_servizi] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_11]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_12] FOREIGN KEY([fk_e1_tur_testata])
REFERENCES [dbo].[e1_tur_ordini_giorno_testata] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_12]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_2] FOREIGN KEY([fk_e0_utente_modifica])
REFERENCES [dbo].[users] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_2]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_3] FOREIGN KEY([fk_e0_prof_nodo])
REFERENCES [dbo].[e0_prof_nodi] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_3]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_4] FOREIGN KEY([fk_e0_turno])
REFERENCES [dbo].[e0_conf_turni] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_4]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_5] FOREIGN KEY([fk_e2_per_dipendente])
REFERENCES [dbo].[e2_per_dipendenti] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_5]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_6] FOREIGN KEY([fk_e1_tur_orario_servizio])
REFERENCES [dbo].[e1_tur_orari_servizi] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_6]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_7] FOREIGN KEY([fk_e0_gg_operativo])
REFERENCES [dbo].[e0_conf_gg_operativi] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_7]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_8] FOREIGN KEY([fk_parent_ordine_giorno])
REFERENCES [dbo].[e1_tur_ordini_giorno] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_8]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_9] FOREIGN KEY([fk_e0_tipo_rischio])
REFERENCES [dbo].[e0_decod_tipi_rischi] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [e1_tur_ordini_giorno$FK_e1_tur_ordini_giorno_9]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [FK_e1_tur_ordini_giorno_e0_prof_nodi] FOREIGN KEY([fk_e0_segmento_dip])
REFERENCES [dbo].[e0_prof_nodi] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [FK_e1_tur_ordini_giorno_e0_prof_nodi]
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno]  WITH CHECK ADD  CONSTRAINT [FK_e1_tur_ordini_giorno_e1_tur_pian_reperibilita] FOREIGN KEY([fk_e1_tur_pian_reperibilita])
REFERENCES [dbo].[e1_tur_pian_reperibilita] ([id])
GO

ALTER TABLE [dbo].[e1_tur_ordini_giorno] CHECK CONSTRAINT [FK_e1_tur_ordini_giorno_e1_tur_pian_reperibilita]
GO

Thenhere are two more screenshots of the performance monitor (taken at different instants though) where I select first the batchRequests/sec and then the PageSplits/sec

BatchRequests/sec

PageSplits/sec

Best Answer

Is there a problem? That depends on your definition. Page splits occur because SQL is inserting records into a page that is already full. Full pages are great for reading, as the data is densely packed. Inserts require that page to be split into two, each one becoming 50% full. The new record is then inserted into the appropriate new page.

As you can imagine, this requires more I/O than merely adding the record to an existing page that has enough room.

Why are page splits occurring? Check the table that is the target of the insert. How is the data physically sorted? Does a clustered index exist, and if so, what data type is it?

Again, this is only really a problem if the current system experiences performance problems, or if you intend to scale.

Please post the DDL of the table along with indexes, and I will comment further.

FURTHER COMMENT

Based on the analysis of your table, the ID key is likely some type of GUID. By nature, GUIDS are random. Clustering on a random value causes page splits. You are telling SQL Server to physically sort randomly generated values.

This is terrible for performance, if those GUIDS are not sequentially generated.

This is a relatively large value for a clustered index, and will magnify performance problems as the clustered index becomes part of each non-clustered index.

Related Question