Sql Server Specs Question
-
Michael L John wrote: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?
Quoting Detective Columbo, "Just one more thing", doesn't it look odd when few typists can enter the information quicker than the import process?
😎
So what are the target tables, any constraints or processing or are you just inserting into a single table? Normally I would regard this question as secondary as there is still a lot we do not know about the process.
As my friend Michael said earlier, even the faster instance looks slow. One could understand that such inserts might take a while if there was a referential lookup for each column. More important is though the question he raised, what kind of import are you performing?
-
Faster:
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 0 0 32767 0 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
1579 backup compression default 0 0 1 0 Enable compression of backups by default 1 0
1580 filestream access level 0 0 2 0 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 0 0
1587 clr strict security 1 0 1 1 CLR strict security enabled in the server 1 1
16384 Agent XPs 1 0 1 1 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 0 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 0Slower:
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 0 0 2 0 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 1 0 1 1 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 -
it creates a new table every week when they import the file. The ceo wanted the server to not be on a network so the text file is placed on a flash drive and plugged into the server, file copied from flash to c drive on server and then the drop table, create table, bulk insert code is run. this is done weekly.
BULK INSERT DBO.MHS_RPT_test_A FROM 'c:\DataWarehouse\Test\test_table.TXT'
-
Even just doing a count(*) on table is different. Faster one takes 1 second and slower takes 16 seconds.
-
smattiko83 wrote:
Even just doing a count(*) on table is different. Faster one takes 1 second and slower takes 16 seconds.
At least we are getting somewhere, and now we know that the comparison is between large apples and small oranges.
😎
The difference in the count query points to memory pressure on the slower instance, how many rows are being returned?
-
It took 38 seconds to run it this time. 30896798.
-
smattiko83 wrote:
it creates a new table every week when they import the file. The ceo wanted the server to not be on a network so the text file is placed on a flash drive and plugged into the server, file copied from flash to c drive on server and then the drop table, create table, bulk insert code is run. this is done weekly.
BULK INSERT DBO.MHS_RPT_test_A FROM 'c:\DataWarehouse\Test\test_table.TXT'
Recovering model - is it simple?
Are there indexes?
And why not just truncate the table vs. drop and recreate?
Google sql bulk insert performance to gain some insight into some simple gains you might get.
-
david.edwards 76768 wrote:
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......
Aye... +1 Million for that post. I was reading through this and thought the same thing. I don't know if the OS is memory starved in either case but if the system starts doing stuff in the swap file, not much else is going to happen. Totally agreed on leaving at least 4GB for these two systems.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Does the target table on the "slow" server have many indexes more than the "fast" server"
-
My next question is if Instant File Initialization is enabled on the databases?
😎
The next thing we will then look into are the running processes and memory usage.
-
Is this it?
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply