NVarchar(4000) vs. NVarchar(x). O anche: “Ti piace vincere facile?”

Article
11/28/2012

Certo il titolo non è dei più chiari, però correggo subito la domanda con la risposta (provocatoria):

“Ti piace perdere facile! ”.

Vengo allo scenario.

Ogni colonna all’interno del database, che andrà a contenere del testo, viene modellata come NVarchar(4000) .

Perchè? Perchè così sono sicuro di poter memorizzare al suo interno sempre tutto ciò che mi arriva e non avrò problemi.

Parlando di performance la domanda nasce spontanea:

Sei sicuro di questa scelta?

Non c’è alcuna differenza tra avere una modellazione come questa ed avere una modellazione che definisca la larghezza delle colonne secondo ciò che queste andranno veramente a memorizzare?

Continuando a sottolineare che:

un database performante deve essere modellato per essere performante;
possiamo avere tutto l’HW di questo mondo, ma se il db è modellato male sposto solo il problema che si ripresenterà all’aumentare delle dimensioni;

mi sono preso la briga di generare alcune istruzioni che possano dimostrare, numeri alla mano, come sia sbagliato lo scenario in cui ci si trova.

Nel mio test effettuo alcune tra le più normali operazioni (SELECT, INSERT, UPDATE, DELETE, like, rebuild, …) su quattro differenti tabelle che hanno tutte lo stesso identico contenuto.

Il test prevede che tutte le tabelle abbiano:

stesso numero di righe
stesso contenuto memorizzato
stesso numero di colonne testuali (3)

Come si può evincere dal titolo delle tabelle stesse, cambia solo e soltanto la dimensione delle colonne NVarchar (impostate come MAX, 4000 o 35) e, nel quarto esempio, il tipo: Varchar (con una dimensione = 35).

Di seguito i tempi di risposta su due scenari: il primo con 1 milione di righe (parliamo di uno storage pari a nemmeno 55 MB), il secondo con 2 milioni di righe.

Alla fine del post trovate anche lo script per rifare la demo.

Questa la griglia di riepilogo dei tempi (in millisecondi) riscontrati:

Mi concentro soprattutto sulla differenza dei tempi che esiste tra NVarchar(4000) e NVarchar(35) .

Credo sia chiaro come, portando la larghezza della colonna ad un numero più vicino alla realtà (da 4000 a 35), si possa ottenere un beneficio, in termini di tempo, di tutto rispetto!

Morale (sempre la stessa): spendere tempo nella corretta modellazione delle vostre tabelle vi farà risparmiare tempo (molto tempo) dopo.

Resta da capire meglio il perchè…

Se avete ancora voglia di leggere, proseguo con un’altra domanda.

Ma siamo sicuri che le strutture che sono state create per gestire le tabelle - mi concentro ancora sulla NVarchar(4000) e sulla NVarchar(35) - siano davvero uguali?

Cioè, visto che il numero di righe ed il contenuto è assolutamente lo stesso, ho le stesse identiche strutture dietro le quinte?

No, perchè essendo la modellazione diversa ho una gestione diversa.

Vediamo:

 SELECT OBJECT_NAME( object_id )AS Name , 
       *
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar4000' ) , NULL , NULL , 'DETAILED' )s
UNION
SELECT OBJECT_NAME( object_id )AS Name , 
       *
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar35' ) , NULL , NULL , 'DETAILED' )s;
GO

E’ immediato vedere che, per la tabella che memorizza gli NVarchar(4000) esiste una riga in più legata ad unità di allocazione di tipo “row overflow”.

Perchè, visto che le righe contenendo pochi caratteri per colonna non vanno veramente in overflow, c’è questa gestione?

Perchè, alla creazione di ogni tabella, il database engine verifica se esiste la possibilità che le righe che si andranno a memorizzare potranno andare in overflow (quindi avere una dimensione che ecceda quella di una datapages, cioè un numero di bytes maggiore di 8060).

Se questa possibilità esiste, viene immediatamente creata un'unità di allocazione di tipo ROW_OVERFLOW_DATA.

Non basta.

Poichè devo gestire due tipologie di unità di allocazione (IN_ROW_DATA e ROW_OVERFLOW_DATA) mi aspetto anche due pagine IAM (Index Allocation Map) che possano informare l’engine (la faccio breve) di come sono strutturati i dati e dove si trovano.

Per conoscenza, ricordo che tre sono le diverse tipologie di allocazione:

IN_ROW_DATA (internamente chiamate "hobt allocation unit", cioè "Heap Or B-Tree")
LOB_DATA
ROW_OVERFLOW_DATA (chiamate anche SLOB - Small LOB)

Quindi, in totale, quante datapages mi aspetto siano gestite sulla tabella NVarchar(4000)?

Il numero di pagine che memorizzano i dati + 2 IAM.

Vado a contare le pagine dati:

 SELECT  OBJECT_NAME( object_id )AS Name, index_id , 
       index_depth AS 'Depth' , 
       index_level AS 'Index Level' , 
       record_count AS 'Record Count' , 
       page_count AS 'Page Count' , 
       avg_page_space_used_in_percent AS 'Page Space Used' , 
       min_record_size_in_bytes AS 'Min Record Size' , 
       max_record_size_in_bytes AS 'Max Record Size' , 
       avg_record_size_in_bytes AS 'Avg Record Size'
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar4000' ) , NULL , NULL , 'DETAILED' )

Quindi: 6964 + 12 + 1 + 1 = 6978, a cui aggiungo ancora le 2 IAM = 6980

Verifico se è vero utilizzando il comando (non documentato) DBCC IND:

 DBCC IND (test, testTableNVarchar4000, 1)
select @@ROWCOUNT

Le pagine gestite sulla tabella testTableNVarchar35, che si possono controllare con lo stesso comando, sono invece 6978.

Alla fine il punto è tutto qui.

SQL Server gestisce tre tipologie di storage: in-row, LOB e row-overflow e la tipologia in-row è quella più performante.

Per approfondire: Table and Index Organization.

Di seguito lo script per eseguire i test di cui ho riportato i risultati all’inizio del post.

 USE test;
GO


SET NOCOUNT ON;
SET STATISTICS TIME OFF;
DECLARE @t datetime;

PRINT '-->  Test NVARCHAR(MAX) INSERT';
SET @t = GETDATE( );
SELECT TOP 1000000 
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( max ))AS  Col1,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( max ))AS  Col2,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( max ))AS  Col3 INTO testTableNVarcharMAX
  FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(4000) INSERT';
SET @t = GETDATE( );
SELECT TOP 1000000 
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 4000 ))AS  Col1,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 4000 ))AS  Col2,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 4000 ))AS  Col3 INTO testTableNVarchar4000
  FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(35) INSERT';
SET @t = GETDATE( );
SELECT TOP 1000000 CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col1,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col2,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS nvarchar( 35 ))AS  Col3 INTO testTableNVarchar35
  FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test VARCHAR(35) INSERT';
SET @t = GETDATE( );
SELECT TOP 1000000 CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS varchar( 35 ))AS  Col1,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS varchar( 35 ))AS  Col2,
    CAST( ROW_NUMBER( )OVER( ORDER BY C.Object_ID )AS varchar( 35 ))AS  Col3 INTO testTableVarchar35
  FROM Master.sys.All_Columns C CROSS JOIN Master.sys.All_Columns C2;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO



SET STATISTICS TIME OFF;
DECLARE @t datetime;
SET @t = GETDATE( );
ALTER TABLE testTableNVarcharMAX
ADD ColInt int NOT NULL
               IDENTITY( 1 , 1 )
               PRIMARY KEY;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
SET @t = GETDATE( );
ALTER TABLE testTableNVarchar4000
ADD ColInt int NOT NULL
               IDENTITY( 1 , 1 )
               PRIMARY KEY;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
SET @t = GETDATE( );
ALTER TABLE testTableNVarchar35
ADD ColInt int NOT NULL
               IDENTITY( 1 , 1 )
               PRIMARY KEY;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
SET @t = GETDATE( );
ALTER TABLE testTableVarchar35
ADD ColInt int NOT NULL
               IDENTITY( 1 , 1 )
               PRIMARY KEY;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO



SET STATISTICS TIME OFF;
DECLARE @t datetime;
SET @t = GETDATE( );
ALTER INDEX ALL ON testTableNVarcharMAX REBUILD WITH( FILLFACTOR = 100 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
SET @t = GETDATE( );
ALTER INDEX ALL ON testTableNVarchar4000 REBUILD WITH( FILLFACTOR = 100 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
SET @t = GETDATE( );
ALTER INDEX ALL ON testTableNVarchar35 REBUILD WITH( FILLFACTOR = 100 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
SET @t = GETDATE( );
ALTER INDEX ALL ON testTableVarchar35 REBUILD WITH( FILLFACTOR = 100 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO



/* test: SELECT */
SET STATISTICS TIME OFF;
DECLARE @t datetime;
DECLARE @valMAX nvarchar( max ) , 
        @val4000 nvarchar( 4000 ) , 
        @val35 nvarchar( 35 ) ,
       @val35_2 varchar( 35 );
SET STATISTICS TIME ON;
PRINT '-->  Test NVARCHAR(MAX) SELECT';
SET @t = GETDATE( );
SELECT @valMAX =  Col1
  FROM testTableNVarcharMAX;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(4000) SELECT';
SET @t = GETDATE( );
SELECT @val4000 =  Col1
  FROM testTableNVarchar4000;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(35) SELECT';
SET @t = GETDATE( );
SELECT @val35 =  Col1
  FROM testTableNVarchar35; 
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test VARCHAR(35) SELECT';
SET @t = GETDATE( );
SELECT @val35_2 =  Col1
  FROM testTableVarchar35; 
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
 GO



/* test: UPDATE */
SET STATISTICS TIME OFF;
DECLARE @t datetime;
SET STATISTICS TIME ON;
PRINT '-->  Test NVARCHAR(MAX) UPDATE';
SET @t = GETDATE( );
UPDATE testTableNVarcharMAX
SET  Col1 =  Col1;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(4000) UPDATE';
SET @t = GETDATE( );
UPDATE testTableNVarchar4000
SET  Col1 =  Col1;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(35) UPDATE';
SET @t = GETDATE( );
UPDATE testTableNVarchar35
SET  Col1 =  Col1;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test VARCHAR(35) UPDATE';
SET @t = GETDATE( );
UPDATE testTableVarchar35
SET  Col1 =  Col1;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO



/* test: LIKE */
SET STATISTICS TIME OFF;
DECLARE @t datetime;
DECLARE @i int;
PRINT '-->  Test NVARCHAR(MAX) LIKE';
SET @t = GETDATE( );
SELECT @i = COUNT( 1 )
  FROM testTableNVarcharMAX
  WHERE Col1 LIKE '9900%'
  OPTION( MAXDOP 1 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(4000) LIKE';
SET @t = GETDATE( );
SELECT @i = COUNT( 1 )
  FROM testTableNVarchar4000
  WHERE Col1 LIKE '9900%'
  OPTION( MAXDOP 1 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(35) LIKE';
SET @t = GETDATE( );
SELECT @i = COUNT( 1 )
  FROM testTableNVarchar35
  WHERE Col1 LIKE '9900%'
  OPTION( MAXDOP 1 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test VARCHAR(35) LIKE';
SET @t = GETDATE( );
SELECT @i = COUNT( 1 )
  FROM testTableVarchar35
  WHERE Col1 LIKE '9900%'
  OPTION( MAXDOP 1 );
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO


/* test: EQUALS */
SET STATISTICS TIME OFF;
DECLARE @t datetime;
DECLARE @valMAX nvarchar( max ) , 
        @val4000 nvarchar( 4000 ) , 
        @val35 nvarchar( 35 ) , 
        @val35_2 varchar( 35 );
PRINT '-->  Test NVARCHAR(MAX) EQUALS';
SET @t = GETDATE( );
SELECT @valMAX =  Col1
  FROM testTableNVarcharMAX
  WHERE  Col1 = '8765';
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(4000) EQUALS';
SET @t = GETDATE( );
SELECT @val4000 =  Col1
  FROM testTableNVarchar4000
  WHERE  Col1 = '8765';
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(35) EQUALS';
SET @t = GETDATE( );
SELECT @val35 =  Col1
  FROM testTableNVarchar35
  WHERE  Col1 = '8765';
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test VARCHAR(35) EQUALS';
SET @t = GETDATE( );
SELECT @val35_2 =  Col1
  FROM testTableVarchar35
  WHERE  Col1 = '8765';
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO



/* test: DELETE */
SET STATISTICS TIME OFF;
DECLARE @t datetime;
PRINT '-->  Test NVARCHAR(MAX) DELETE';
SET @t = GETDATE( );
DELETE FROM testTableNVarcharMAX; 
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(4000) DELETE';
SET @t = GETDATE( );
DELETE FROM testTableNVarchar4000;
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test NVARCHAR(35) DELETE';
SET @t = GETDATE( );
DELETE FROM testTableNVarchar35; 
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );

PRINT '-->  Test VARCHAR(35) DELETE';
SET @t = GETDATE( );
DELETE FROM testTableVarchar35; 
SELECT DATEDIFF( MILLISECOND , @t , current_timestamp );
GO




/* la struttura fisica delle tabelle su cui concentro l'analisi */
SELECT OBJECT_NAME( object_id )AS Name , 
       *
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar4000' ) , NULL , NULL , 'DETAILED' )s
UNION
SELECT OBJECT_NAME( object_id )AS Name , 
       *
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar35' ) , NULL , NULL , 'DETAILED' )s;
GO


SELECT  OBJECT_NAME( object_id )AS Name, index_id , 
       index_depth AS 'Depth' , 
       index_level AS 'Index Level' , 
       record_count AS 'Record Count' , 
       page_count AS 'Page Count' , 
       avg_page_space_used_in_percent AS 'Page Space Used' , 
       min_record_size_in_bytes AS 'Min Record Size' , 
       max_record_size_in_bytes AS 'Max Record Size' , 
       avg_record_size_in_bytes AS 'Avg Record Size'
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar4000' ) , NULL , NULL , 'DETAILED' )
UNION ALL
SELECT  OBJECT_NAME( object_id )AS Name, index_id , 
       index_depth AS 'Depth' , 
       index_level AS 'Index Level' , 
       record_count AS 'Record Count' , 
       page_count AS 'Page Count' , 
       avg_page_space_used_in_percent AS 'Page Space Used' , 
       min_record_size_in_bytes AS 'Min Record Size' , 
       max_record_size_in_bytes AS 'Max Record Size' , 
       avg_record_size_in_bytes AS 'Avg Record Size'
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'testTableNVarchar35' ) , NULL , NULL , 'DETAILED' );
GO




IF EXISTS( SELECT *
             FROM sys.objects
             WHERE object_id = OBJECT_ID( N'[dbo].[myDBCCIND]' ) AND type IN( N'U' ))
    BEGIN
        DROP TABLE dbo.myDBCCIND
    END;
GO
CREATE TABLE myDBCCIND( PageFID tinyint , 
                        PagePID int , 
                        IAMFID tinyint , 
                        IAMPID int , 
                        ObjectID int , 
                        IndexID tinyint , 
                        PartitionNumber tinyint , 
                        PartitionID bigint , 
                        iam_chain_type varchar( 30 ) , 
                        PageType tinyint , 
                        IndexLevel tinyint , 
                        NextPageFID tinyint , 
                        NextPagePID int , 
                        PrevPageFID tinyint , 
                        PrevPagePID int );

INSERT INTO myDBCCIND
EXEC ( 'DBCC IND (test, testTableNVarchar4000, 1)' );
GO

SELECT *
  FROM myDBCCIND
  ORDER BY IndexLevel DESC , PrevPagePID;
GO



/* pulizia */
SET STATISTICS TIME OFF;
DROP TABLE testTableNVarchar4000;
DROP TABLE testTableNVarcharMAX; 
DROP TABLE testTableNVarchar35;
DROP TABLE testTableVarchar35;
DROP TABLE myDBCCIND;
GO

Enjoy.

NVarchar(4000) vs. NVarchar(x). O anche: “Ti piace vincere facile?”

Additional resources