Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

In what situation can inserting a simple record (the table has got some 10 columns, two of them are xml) use parallelism?

CREATE TABLE [dbo].[PackageSessions](
    [PackageSessionId] [int] IDENTITY(1,1) NOT NULL,
    [PackageId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
    [StartDateTime] [datetime] NULL,
    [StopDateTime] [datetime] NULL,
    [Score] [float] NOT NULL,
    [ScoreMax] [float] NOT NULL,
    [CompletionStatus] [int] NOT NULL,
    [ReviewPlayerContextId] [int] NOT NULL,
    [PlayerContextId] [int] NOT NULL,
    [ReducedScore] [float] NOT NULL,
    [ReducedScoreMax] [float] NOT NULL,
    [PackageSnapShot] [xml] NULL,
    [InterfaceLanguageId] [int] NOT NULL,
    [Data] [xml] NULL,
    [PackageSessionLanguageId] [int] NOT NULL,
 CONSTRAINT [PackageSessions_PK] PRIMARY KEY CLUSTERED 
(
    [PackageSessionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
share|improve this question

1 Answer 1

up vote 1 down vote accepted

Inserting a single simple straight-forward record such as:

INSERT INTO PackageSessions VALUES (1, 2, 3, NULL, NULL, 1.0, 2.0, 3, 4, 5, 6.0, 7.0, NULL, 1, NULL, 2)

will never be parallelized. SQL server detects where jobs can be split, say if you were doing an update on a table it might divide the table in half and give each half to two different processor. If the job can't be fairly easily split, it runs without parallelization.

(Schema-Nazi note: table names are supposed to be singluar)

share|improve this answer
    
Schema-Nazi note, great, ;-). Legacy, but I'm cleaning up! And what if there are two xml columns and both xml needs to be validated when inserting, can that be split over two cpu's? –  Lieven Cardoen Mar 23 '10 at 20:27
    
I'm not sure about that one, I believe validation is always serial within each record. But I haven't tried it. –  Chris S Mar 24 '10 at 0:40
    
Is there a way to check this with sql profiler? –  Lieven Cardoen Mar 24 '10 at 7:52
1  
It would depend a bit on the complexity of the XSD. If it's fairly simple the time difference with parallelization turned on and off would probably be too small to notice the difference. If it's a more complex XSD you should notice easily by profileing. –  Chris S Mar 24 '10 at 12:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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