WITH (TABLOCK) - Minimal logging while insert

In order to improve performance for an insert statement,

We can consider use WITH (TABLOCK) option:

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME) SELECT ...

Whet it will do is to minimize the writes to the log.

Try it:

Run insertion statement with and without the TABLOCK and check the log:

INSERT INTO dbo.testItai (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b

INSERT INTO dbo.testItai WITH (TABLOCK) (id, NAME)

SELECT TOP 5000 ABS(CHECKSUM(NEWID())) % 100,

       char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) +char(65+ ABS(CHECKSUM(NEWID())) % 26)

FROM master..sysobjects a cross join master..sysobjects b 

Check the log using

select * from fn_dblog(null, null)

You will see that after the insertion without the TABLOCK 5000 records will be written in the log, and much less with the TABLOCK.

WITH (TABLOCK) has few constraints:

1. The destination table must be empty or without clustered index.

2. There is no non-clustered index on the destination table.

Or conditional join

The case:

SELECT ...

FROM TableA a

JOIN TableB b ON (a.ID = b.ID OR a.ID = b.ParentID)

or:

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

Of course it's not forbidden to use conditional join, 

but most of the time it won't be good to the performance of the query.

There are few possible solutions.

0. Keep use the conditional join

For cases the the performance are not get Worse (example: small tables),

or when the 'complecated' cases are uncommon.

1. UNION

Good for 'simple' cases:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON (a.ID = b.ID OR a.ID = b.ParentID)

use:

SELECT ...

FROM TableA a

JOIN TableB b ON a.ID = b.ID

UNION

SELECT ...

FROM TableA a

JOIN TableB b ON a.ID = b.ParentID

or similar case:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = a.AColumn OR b.BColumn = a.BColumn)

Use:

       SELECT ...

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

                                  AND b.AColumn = a.AColumn

UNION

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    b.BColumn = a.BColumn

2. filter in the "where":

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                                  AND    (b.AColumn = @AColumn OR @AColumn = 0)

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

WHERE (b.AColumn = @AColumn OR @AColumn = 0))

3. 2 queries:

For cases that the JOIN is depended on a parameter:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

Use:

IF @AColumn = 0

       SELECT ...

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

ELSE

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    b.AColumn = @AColumn -- without consider the case of @AColumn = 0

4. Insert data to temp table

It's good for similar cases like in point 2, but for queries with a lot of joined tables

What we'll do is to store the data for the conditional join in a temp table and use it in the main query:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

JOIN TableC c .....

JOIN TableD d .....

JOIN TableE e .....

Use:

CREATE TABLE #TempCond (

INSERT INTO #TempCond ()

       SELECT ... -- take the relevant columns

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

                                  AND    (b.AColumn = @AColumn OR @AColumn = 0)

--Or, if required, use one of the solutions in points 1,2 to this temp insertion

SELECT ...

FROM #TempCond

JOIN TableC c .....

JOIN TableD d .....

JOIN TableE e .....

Get sysadmin logins

SELECT *

FROM master.sys.server_principals

WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1

ORDER BY name

Space in drives query

exec sys.xp_fixeddrives

Trace Skipped Records

An interesting issue that i saw it first time this week when using the profiler:

Trace Skipped Records....

Apparently that's what it sounds: the profiler was too "busy" or loaded, so it skipped few records...

"Busy"/loaded can be few things:

Long text that was caught and the profiler can't display it in TextData column, busy or loaded server or something in the machine where the profiler isd executing.

This thong is a known issue in Microsoft:

https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch

I didn't fing a lot about it, here 2 useful links:

http://blog.consultdba.com/2010/09/trace-skipped-records-what-is-this-and.html

https://scarydba.wordpress.com/2009/10/28/snags-with-profiler-gui/

select ... into ... union

declare @a table (aint int)

declare @b table (bint int)

insert into @a (aint) values (1), (2), (3)

insert into @b (bint) values (1), (2), (4), (6)

If you need to insert an union select into a temp table, like this:

select * into #tmp

from (select aint from @a union select bint from @b) t

You can also do it like this:

select aint

into #tmp

from @a

union

select bint

from @b

Check it:

select * from #tmp

(and also drop the temp table :) drop table #tmp )