Sql Server Specs Question
-
Hi,
I have two different servers that I do a bulk insert on the exact same text file. One will take 3 hours to do a bulk insert on the file and the other one will take about 45 minutes. Is it due to the specs on the server itself or is there something I could change? They both are 64 bit operating systems. Below are the specs on the servers. Thanks
Server 1: Is the one that takes 3 hours
Processor - Intel Xeon Bronze 3104 CUP @1.70GHz
Installed RAM - 24.0 GB
Server 2: Is the one that takes 45 min
Processor - Intel Xeon CPU E5-2630 v4 @ 2.20 GHz
Installed RAM - 64.0 GB
-
Bare metal or VMs?
Network specs?
Where is the file? Local hard disk/SAN/other? Are the drives configured identically? Have you benchmarked/ & compared performance for the drives from which you're pulling the file? For the data and log files of the database into which you're inserting?
What % is CPU at during the bulk insert? The 2630's performance is double that of the 3104... but I wouldn't necessarily expect that to be the bottleneck for a bulk insert. Do you see the 3104 maxing out during the bulk insert?
What else is installed and running on those servers in addition to SQL Server (especially during bulk insert)?
What else is competing for resources on SAN/network (if applicable)?
-
File is in a folder on C drive for both. How do I configure the drives and compare performane? Sorry, I'm not a dba at all and got handed this issue. The faster server is not even on a network, it is a stand alone machine with only office installed besides ssms and sql server. The slower machine has office and crystal reports/server. Nothing else is being done on them during the bulk insert.
-
How much total memory does each machine have? What are the minimum & maximum memory settings in SQL Server (right-click the server in SSMS & select Memory)?
Do servers only have a C drive? Or are data and log files on different drive(s)?
CrystalDiskMark is a simple free utility that you can run to test read and write speeds. Run on each server/drive and compare the results for the relevant drives.
Error checking (chkdsk.exe) can tell you if the disk has issues. Disk errors can drastically impact performance.
Why do the servers have office installed?
-
smattiko83 wrote:
Hi,
I have two different servers that I do a bulk insert on the exact same text file. One will take 3 hours to do a bulk insert on the file and the other one will take about 45 minutes. Is it due to the specs on the server itself or is there something I could change? They both are 64 bit operating systems. Below are the specs on the servers. Thanks
Server 1: Is the one that takes 3 hours
Processor - Intel Xeon Bronze 3104 CUP @1.70GHz
Installed RAM - 24.0 GB
Server 2: Is the one that takes 45 min
Processor - Intel Xeon CPU E5-2630 v4 @ 2.20 GHz
Installed RAM - 64.0 GB
How large are the text files and what kind of parsing complexity are we looking at?
😎
There are many factors that can affect the performance, please tell us the "full story" as the difference in the hardware (RAM/CPU) does not justify the difference in the execution time.
This could potentially become a lengthy conversation, let's try to keep it SCSP, you give us all the details and we'll provide the answers/options
-
They have office installed because this place uses the server like a big desktop. The faster one has a Local Disk C drive and a DATA D drive. The slower only has the C.
Slower is the first two images.
Faster images below:
-
Sure, I'm just not sure what all you need. The text file size is 2995902. and it is around 30 columns. Both servers are running the exact same bulk insert code and importing the same text file.
-
smattiko83 wrote:
Sure, I'm just not sure what all you need. The text file size is 2995902. and it is around 30 columns. Both servers are running the exact same bulk insert code and importing the same text file.
To start with, is it 2995902 bits, bytes, MB, GB, TB?
😎
Those "small details" can make a difference. We will help you as much as we can, but it does entirely depend on the information you provide. There are hundreds of factors that can potentially affect the performance, one hint in the information you posted is that the faster server has two drives, on that server, are the data files on the secondary (D;) drive?
-
It is KB.
No the files are on the C drive only for both servers.
-
smattiko83 wrote:
It is KB.
No the files are on the C drive only for both servers.
So somewhat shy of 3GB?
😎
Something is not right here, my tablet regularly processes two times that data in less than half an hour, running the data of an SD card (2GB RAM, 2 Core ARM 1GHz).
Must say that I find this problem interesting!
Can you list the output of these queries on both servers:
SELECT * FROM sys.configurations;
SELECT @@VERSION; -
Silly question... Have you checked the anti-virus?
I've seen major issues multiple times when the anti-virus exclusion list gets changed (file types removed or folders removed) and suddenly the thing we know is safe is getting scanned every time we access it. Although usually I see this problem hitting database files, which should be excluded, verses regular files which shouldn't.
But it's something you should check. If your .mdf or .ldf are being scanned on one server, it could be slowing down all sorts of processing. If the file itself is being caught by the scanner on one server vs the other, it would also explain the difference.
-
How do I show the output for the SELECT * FROM sys.configurations query? Can I attach an excel spreadsheet?
Faster - Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )
Slower - Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) Oct 28 2019 19:56:59 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
-
Doubtful the cause of your pain, but.... the SQl Server Max Memory is set at the Default, out of the box value. This is usually bad - SQL will usually consume all memory it is allowed (as it should for performance), unfortunately the default of over 2Billion megabytes is more that most people will actually have in their server, so the server OS struggles to run.
You might want to see about reducing that to leave at least 4GB free, so 20480MB and 61440MB respectively for your 24 and 64GB memory servers.
Actually, if you have Office and all sorts of other stuff on there needing memory......
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now? -
In SSMS, run the query, then select all the results and use CTRL+SHIFT+C to copy the results with headers, then in your reply in this forum, click "Insert Code Sample", select Plain and paste the results.
😎
Example:
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in minutes 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from triggers 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
116 server trigger recursion 1 0 1 1 Allow recursion for server level triggers 1 0
117 remote access 1 0 1 1 Allow remote access 0 0
124 default language 0 0 9999 0 default language 1 0
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1 0
503 max worker threads 0 128 65535 0 Maximum worker threads 1 1
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
518 show advanced options 0 0 1 0 show advanced options 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1 0
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1126 default full-text language 1033 0 2147483647 1033 default full-text language 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create sorts (kBytes) 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1519 remote login timeout (s) 10 0 2147483647 10 remote login timeout 1 0
1520 remote query timeout (s) 600 0 2147483647 600 remote query timeout 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
1532 set working set size 0 0 1 0 set working set size 0 1
1534 user options 0 0 32767 0 user options 1 0
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1536 max text repl size (B) 65536 -1 2147483647 65536 Maximum size of a text field in replication. 1 0
1537 media retention 0 0 365 0 Tape retention period in days 1 1
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism 1 1
1539 max degree of parallelism 4 0 32767 4 maximum degree of parallelism 1 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per query (kBytes) 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query memory (s) 1 1
1543 min server memory (MB) 0 0 2147483647 16 Minimum size of server memory (MB) 1 1
1544 max server memory (MB) 2147483647 128 2147483647 2147483647 Maximum size of server memory (MB) 1 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost allowed by query governor 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight pooling 0 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
1549 affinity64 mask 0 -2147483648 2147483647 0 affinity64 mask 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 0 affinity64 I/O mask 0 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text query 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text protocol handler (s) 1 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in full-text indexing 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text notifications buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text notifications buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl buffers 1 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl buffers 1 1
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
1569 blocked process threshold (s) 0 0 86400 0 Blocked process reporting threshold 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions with unknown outcome 1 1
1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are allowed from remote clients 1 0
1577 common criteria compliance enabled 0 0 1 0 Common Criteria compliance mode enabled 0 1
1578 EKM provider enabled 0 0 1 0 Enable or disable EKM provider 1 1
1579 backup compression default 0 0 1 0 Enable compression of backups by default 1 0
1580 filestream access level 2 0 2 2 Sets the FILESTREAM access level 1 0
1581 optimize for ad hoc workloads 0 0 1 0 When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload. 1 1
1582 access check cache bucket count 0 0 65536 0 Default hash bucket count for the access check result security cache 1 1
1583 access check cache quota 0 0 2147483647 0 Default quota for the access check result security cache 1 1
1584 backup checksum default 0 0 1 0 Enable checksum of backups by default 1 0
1585 automatic soft-NUMA disabled 0 0 1 0 Automatic soft-NUMA is enabled by default 0 1
1586 external scripts enabled 0 0 1 0 Allows execution of external scripts 1 0
1587 clr strict security 1 0 1 1 CLR strict security enabled in the server 1 1
1588 column encryption enclave type 0 0 2 0 Type of enclave used for computations on encrypted columns 0 0
1589 tempdb metadata memory-optimized 0 0 1 0 Tempdb metadata memory-optimized is disabled by default. 0 1
1591 ADR cleaner retry timeout (min) 0 0 32767 0 ADR cleaner retry timeout. 1 1
1592 ADR Preallocation Factor 0 0 32767 0 ADR Preallocation Factor. 1 1
16384 Agent XPs 0 0 1 0 Enable or disable Agent XPs 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation Procedures 1 1
16390 xp_cmdshell 0 0 1 0 Enable or disable command shell 1 1
16391 Ad Hoc Distributed Queries 0 0 1 0 Enable or disable Ad Hoc Distributed Queries 1 1
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
16393 contained database authentication 0 0 1 0 Enables contained databases and contained authentication 1 0
16394 hadoop connectivity 0 0 7 0 Configure SQL Server to connect to external Hadoop or Microsoft Azure storage blob data sources through PolyBase 0 0
16395 polybase network encryption 1 0 1 1 Configure SQL Server to encrypt control and data channels when using PolyBase 1 0
16396 remote data archive 0 0 1 0 Allow the use of the REMOTE_DATA_ARCHIVE data access for databases 1 0
16397 allow polybase export 0 0 1 0 Allow INSERT into a Hadoop external table 1 0
16398 allow filesystem enumeration 1 0 1 1 Allow enumeration of filesystem 1 1
16399 polybase enabled 0 0 1 0 Configure SQL Server to connect to external data sources through PolyBase 1 0 -
Eirikur Eiriksson wrote:smattiko83 wrote:
It is KB.
No the files are on the C drive only for both servers.
So somewhat shy of 3GB?
😎
Something is not right here, my tablet regularly processes two times that data in less than half an hour, running the data of an SD card (2GB RAM, 2 Core ARM 1GHz).
Must say that I find this problem interesting!
Can you list the output of these queries on both servers:
SELECT * FROM sys.configurations;
SELECT @@VERSION;I agree. Even on the faster server, 45 minutes is way too long.
Curious, how are you performing a bulk insert?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply