5

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

  • The article was talking about average values over some period of time. Perfmon graphs instantaneous values. The average over time for a metric is shown in the numeric boxes below the graphs. We can see that it's 0.082 for the Page Splits, but we cannot see what the value is for BatchRequests in you screenshot (you have to select it). – RBarryYoung Sep 11 '15 at 15:16
  • I added two more screenshots @RBarryYoung, I hope this give a better idea. Thanks – frankieta Sep 11 '15 at 15:40
  • In the second and third screenshots it looks like Avg BatchRequests/Sec is 182.274 and avg PageSplits/sec is 182,274. Maybe I'm getting something wrong with the scale? thanks – frankieta Sep 11 '15 at 15:47
  • Ah, right, I missed the third perfmon. hang on... – RBarryYoung Sep 11 '15 at 15:49
  • OK, so Avg BatchRequests/Sec is 217.431 and avg PageSplits/sec is 182.274. That would make PageSplits about 85% of BatchRequests, so according to the article, that could be too high. (it really depends on the situation though) – RBarryYoung Sep 11 '15 at 15:52
1

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.

  • 1
    I added the create script for a big table of ours, is it enough? Thanks – frankieta Sep 11 '15 at 15:41
  • If you're stuck with GUIDs, changing the fill factor of the indexes should reduce those page splits at the expense of space. – FilamentUnities Sep 11 '15 at 21:30
  • 1
    Thanks @datagod, I think you're Right. Maybe the 36 varchar GUID we use foR EVERY table is in partly causing those page splits. I think changing this can be a lot of work, so we will try to use a fill factor of 80 (can it be a good value? Now we use 0, so every page is filled to the rim) and maybe we will continue to investigate in indexing. We're still starting now to study more in this field. I will accept your answer since it does seem like a good advice. SQL server automatically create a clustered index on all of our guids too and on all Fks (these are all randomly generate guids) – frankieta Sep 12 '15 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.