0

This is my first ever question on dba.stackexchange. I am an experienced DBA, and I just changed company.

My problem: On any SQLServer in this new shoppe, I find the output times really slow. I mean, I am on the local machine, I bcp a table that I have checked is fully loaded into the buffer pool (physical reads = 0) into a local file, and I barely hit 30 Mb/s into the loading of the file. The query is a Select * from table. The table is 5.7GB big, totally 15 million lines. With SSMS, of course it is way slower. The problem is not BCP or SSMS, the problem is I find that output really slow compared to the beast I have as a server. My storage is capped at around 300Mo/s - but in this case it is only really used by the output file as the table datapages are cached.

When I check wait times, of course my main wait type is ASYNC_NETWORK_IO, which was pretty predictible, since it is only scrolling throught the pages in memory and shouting them out to the client. What is NOT normal in my opinion, is to have 75% of the total elapsed time which consists of waiting for the client. The client being a local BCP, I expected to hit a way bigger throuput.

It is SQLServer 2019, patched to CU 18, on a Win2019 server VM, hosted on a VMWare Esxi 7. [EDIT ADD] 16 CPUs, 84GB RAM. Min & Max server memory are properly set, lock pages in memory as well. I don't see anything odd or funny in the SQL configuration, even though I have been installing SQLServers since version 7 (4cds version)... [/EDIT]

Some details & tests I did:

  1. Inserting the table into a #tmp table -> 6 seconds elasped, it goes parallel. Selecting the #tmp table in local SSMS takes forever (30s per million) and does not go parallel.
  2. Same test as 1, but on my SSD laptop, with a quick and dirty developper edt install and a restore from the same DB on the laptop and running the same BCP query on it: Inserting into #tmp takes double the time (12 seconds), but selecting takes half as long! BCP goes twice as fast on my laptop, even though SQLServer can't cache the whole table due to memory pressure.

What do you guys think?

  1. Do you think those times are abnormal like me or not?
  2. Do you know any way to tweak the output buffer ? I know how to look at it, DBCC OUTPUTBUFFER (session_id), but I know no way of making it go faster.

Thank you guys, don't hesitate if you need any more information.

EDIT: I did some VMWare checks and everything looks right, no ballooning, the resources are reserved everywhere (cpu, ram and disk are not overallocated and have priorities set for the server). Storage tests of file copies show 300Mb/s copy rates.
Another important piece of news: backups are made to the storage speed, over 200MB/s.

EDIT: Table definition:

CREATE TABLE [dbo].[OERS](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Created] [smalldatetime] NOT NULL,
    [Modified] [smalldatetime] NOT NULL,
    [IDReio] [int] NULL,
    [Datebegin] [date] NULL,
    [Dateend] [date] NULL,
    [IDProduct] [bigint] NULL,
    [IDjiu] [int] NULL,
    [CodePrice] [varchar](10) NULL,
    [Price] [numeric](16, 9) NULL,
    [min] [numeric](16, 9) NULL,
    [IDpkny] [int] NULL,
    [IDpknyo] [bigint] NULL,
    [IDRaze] [bigint] NULL,
    [CodeRaze] [varchar](30) NULL,
    [Codepkny] [varchar](30) NULL,
    [IDTi] [int] NULL,
    [CodeTi] [varchar](100) NULL,
    [IDGrTi] [int] NULL,
    [CodeGrpTi] [varchar](30) NULL,
    [CodexOP] [varchar](30) NULL,
    [ProductR4Px] [int] NOT NULL,
    [rownum] [bigint] NOT NULL,
    [Deleted] [smalldatetime] NULL,
    [offerNoTag] [bit] NULL,
    [PrixA] [numeric](16, 9) NULL,
    [IDTA] [int] NULL,
    [online] [bit] NULL,
    [Qite] [decimal](10, 2) NULL,
    [is_available] [bit] NULL,
    [dlvr_start_date] [date] NULL,
    [x_end_date] [date] NULL,
    [x_day] [bigint] NULL,
    [x_fran] [bit] NULL,
    [qity_increment] [decimal](10, 3) NULL,
    [x_offline] [bit] NULL,
    [x_type] [int] NULL,
    [x_supplier_reference] [varchar](30) NULL,
    [x_qy] [decimal](10, 3) NULL,
    [x_qy_warehouse] [decimal](10, 2) NULL,
    [x_qy_company] [decimal](10, 2) NULL,
    [x_qy_max_warehouse] [decimal](10, 2) NULL,
    [x_o_type] [varchar](20) NULL,
    [x_IDProductle] [int] NULL,
    [x_UUIDProduitModele] [varchar](36) NULL,
    [x_UUIDProduct] [varchar](36) NULL,
 CONSTRAINT [PK_OERS] PRIMARY KEY CLUSTERED 
(
    [ID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

BCP command:

BCP DB.dbo.oers out v:\BCP\BCP_export.txt -T SMyMachine\MyInstance -c 

I tried to changed block size for a negligible gain (up to -a 32765, over that it stays at this value). It goes around 30% faster in native (-n) as opposed to -c.

But again, the goal is not to make BCP go faster, I just use it to get a reasonably fast client to make the problem appear in full.

I also tried a few things regarding to network, like changing to IP connection, activating and deactivating shared memory, to no avail.

[EDIT] CrystalDiskMark Logs

All tests are on 4GB file size.

For TempDB drive:

[Read]
  SEQ    1MiB (Q=  8, T= 1):   468.702 MB/s [    447.0 IOPS] < 17858.99 us>
  SEQ    1MiB (Q=  1, T= 1):   375.410 MB/s [    358.0 IOPS] <  2790.82 us>
  RND    4KiB (Q= 32, T= 1):   277.995 MB/s [  67869.9 IOPS] <   471.05 us>
  RND    4KiB (Q=  1, T= 1):    26.057 MB/s [   6361.6 IOPS] <   156.77 us>

[Write]
  SEQ    1MiB (Q=  8, T= 1):   454.628 MB/s [    433.6 IOPS] < 18359.41 us>
  SEQ    1MiB (Q=  1, T= 1):   316.649 MB/s [    302.0 IOPS] <  3307.75 us>
  RND    4KiB (Q= 32, T= 1):   105.947 MB/s [  25866.0 IOPS] <  1235.40 us>
  RND    4KiB (Q=  1, T= 1):     8.168 MB/s [   1994.1 IOPS] <   500.34 us>

For the SQLServer binaries & system DBs:

[Read]
  SEQ    1MiB (Q=  8, T= 1):   445.932 MB/s [    425.3 IOPS] < 18767.37 us>
  SEQ    1MiB (Q=  1, T= 1):   355.461 MB/s [    339.0 IOPS] <  2947.26 us>
  RND    4KiB (Q= 32, T= 1):   266.955 MB/s [  65174.6 IOPS] <   490.53 us>
  RND    4KiB (Q=  1, T= 1):    21.148 MB/s [   5163.1 IOPS] <   193.24 us>

[Write]
  SEQ    1MiB (Q=  8, T= 1):   373.743 MB/s [    356.4 IOPS] < 22318.30 us>
  SEQ    1MiB (Q=  1, T= 1):   262.976 MB/s [    250.8 IOPS] <  3981.13 us>
  RND    4KiB (Q= 32, T= 1):    92.627 MB/s [  22614.0 IOPS] <  1413.03 us>
  RND    4KiB (Q=  1, T= 1):     8.627 MB/s [   2106.2 IOPS] <   473.77 us>

For the DB MDF &log drive (DB in simple recovery model):

[Read]
  SEQ    1MiB (Q=  8, T= 1):   453.642 MB/s [    432.6 IOPS] < 18441.01 us>
  SEQ    1MiB (Q=  1, T= 1):   326.284 MB/s [    311.2 IOPS] <  3211.51 us>
  RND    4KiB (Q= 32, T= 1):   273.126 MB/s [  66681.2 IOPS] <   479.42 us>
  RND    4KiB (Q=  1, T= 1):    25.585 MB/s [   6246.3 IOPS] <   159.63 us>

[Write]
  SEQ    1MiB (Q=  8, T= 1):   451.667 MB/s [    430.7 IOPS] < 18484.86 us>
  SEQ    1MiB (Q=  1, T= 1):   304.498 MB/s [    290.4 IOPS] <  3436.18 us>
  RND    4KiB (Q= 32, T= 1):   109.393 MB/s [  26707.3 IOPS] <  1196.74 us>
  RND    4KiB (Q=  1, T= 1):     7.529 MB/s [   1838.1 IOPS] <   542.39 us>
13
  • I keep thinking that it's disk I/O. Try outputting to a file that's on the same drive/lun as your tempDB and see if that resolves it. Feb 12 at 16:38
  • Thank you for your answer. I put TempdB on a ramdisk that outputed in the 10GB/s, with no luck whatsoever. Moreover, I would not understand the physical reads =0 , it may be tempdb, it's STILL a physical read if it has to hit the disk for a page that is not in the buffer pool, no?
    – YADBA
    Feb 12 at 16:55
  • Please provide the full BCP command you’re using, and table definition. Feb 12 at 18:28
  • Edited with your demands. Thank you so much for your time!
    – YADBA
    Feb 12 at 18:52
  • Perhaps if you ran bcp with the out to NUL, you could determine whether it's the disk that is slow, or just bcp talking to the server...
    – mustaccio
    Feb 12 at 21:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.