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_idnamevalueminimummaximumvalue_in_usedescriptionis_dynamicis_advanced
101recovery interval (min)00327670Maximum recovery interval in minutes11
102allow updates0010Allow updates to system tables10
103user connections00327670Number of user connections allowed01
106locks0500021474836470Number of locks for all users01
107open objects0021474836470Number of open database objects01
109fill factor (%)001000Default fill factor percentage01
114disallow results from triggers0010Disallow returning results from triggers11
115nested triggers1011Allow triggers to be invoked within triggers10
116server trigger recursion1011Allow recursion for server level triggers10
117remote access1011Allow remote access00
124default language0099990default language10
400cross db ownership chaining0010Allow cross db ownership chaining10
503max worker threads0128655350Maximum worker threads11
505network packet size (B)4096512327674096Network packet size11
518show advanced options0010show advanced options10
542remote proc trans0010Create DTC transaction for remote procedures10
544c2 audit mode0010c2 audit mode01
1126default full-text language1033021474836471033default full-text language11
1127two digit year cutoff2049175399992049two digit year cutoff11
1505index create memory (KB)070421474836470Memory for index create sorts (kBytes)11
1517priority boost0010Priority boost01
1519remote login timeout (s)100214748364710remote login timeout10
1520remote query timeout (s)60002147483647600remote query timeout10
1531cursor threshold-1-12147483647-1cursor threshold11
1532set working set size0010set working set size01
1534user options00327670user options10
1535affinity mask0-214748364821474836470affinity mask11
1536max text repl size (B)65536-1214748364765536Maximum size of a text field in replication.10
1537media retention003650Tape retention period in days11
1538cost threshold for parallelism50327675cost threshold for parallelism11
1539max degree of parallelism40327674maximum degree of parallelism11
1540min memory per query (KB)102451221474836471024minimum memory per query (kBytes)11
1541query wait (s)-1-12147483647-1maximum time to wait for query memory (s)11
1543min server memory (MB)00214748364716Minimum size of server memory (MB)11
1544max server memory (MB)214748364712821474836472147483647Maximum size of server memory (MB)11
1545query governor cost limit0021474836470Maximum estimated cost allowed by query governor11
1546lightweight pooling0010User mode scheduler uses lightweight pooling01
1547scan for startup procs0010scan for startup stored procedures01
1549affinity64 mask0-214748364821474836470affinity64 mask11
1550affinity I/O mask0-214748364821474836470affinity I/O mask01
1551affinity64 I/O mask0-214748364821474836470affinity64 I/O mask01
1555transform noise words0010Transform noise words for full-text query11
1556precompute rank0010Use precomputed rank for full-text query11
1557PH timeout (s)601360060DB connection timeout for full-text protocol handler (s)11
1562clr enabled0010CLR user code execution enabled in the server10
1563max full-text crawl range402564Maximum crawl ranges allowed in full-text indexing11
1564ft notify bandwidth (min)00327670Number of reserved full-text notifications buffers11
1565ft notify bandwidth (max)100032767100Max number of full-text notifications buffers11
1566ft crawl bandwidth (min)00327670Number of reserved full-text crawl buffers11
1567ft crawl bandwidth (max)100032767100Max number of full-text crawl buffers11
1568default trace enabled1011Enable or disable the default trace11
1569blocked process threshold (s)00864000Blocked process reporting threshold11
1570in-doubt xact resolution0020Recovery policy for DTC transactions with unknown outcome11
1576remote admin connections0010Dedicated Admin Connections are allowed from remote clients10
1577common criteria compliance enabled0010Common Criteria compliance mode enabled01
1578EKM provider enabled0010Enable or disable EKM provider11
1579backup compression default0010Enable compression of backups by default10
1580filestream access level2022Sets the FILESTREAM access level10
1581optimize for ad hoc workloads0010When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.11
1582access check cache bucket count00655360Default hash bucket count for the access check result security cache11
1583access check cache quota0021474836470Default quota for the access check result security cache11
1584backup checksum default0010Enable checksum of backups by default10
1585automatic soft-NUMA disabled0010Automatic soft-NUMA is enabled by default01
1586external scripts enabled0010Allows execution of external scripts10
1587clr strict security1011CLR strict security enabled in the server11
1588column encryption enclave type0020Type of enclave used for computations on encrypted columns00
1589tempdb metadata memory-optimized0010Tempdb metadata memory-optimized is disabled by default.01
1591ADR cleaner retry timeout (min)00327670ADR cleaner retry timeout.11
1592ADR Preallocation Factor00327670ADR Preallocation Factor.11
16384Agent XPs0010Enable or disable Agent XPs11
16386Database Mail XPs0010Enable or disable Database Mail XPs11
16387SMO and DMO XPs1011Enable or disable SMO and DMO XPs11
16388Ole Automation Procedures0010Enable or disable Ole Automation Procedures11
16390xp_cmdshell0010Enable or disable command shell11
16391Ad Hoc Distributed Queries0010Enable or disable Ad Hoc Distributed Queries11
16392Replication XPs0010Enable or disable Replication XPs11
16393contained database authentication0010Enables contained databases and contained authentication10
16394hadoop connectivity0070Configure SQL Server to connect to external Hadoop or Microsoft Azure storage blob data sources through PolyBase00
16395polybase network encryption1011Configure SQL Server to encrypt control and data channels when using PolyBase10
16396remote data archive0010Allow the use of the REMOTE_DATA_ARCHIVE data access for databases10
16397allow polybase export0010Allow INSERT into a Hadoop external table10
16398allow filesystem enumeration1011Allow enumeration of filesystem11
16399polybase enabled0010Configure SQL Server to connect to external data sources through PolyBase10 - 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