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_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 parallelism00327670maximum 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
1579backup compression default0010Enable compression of backups by default10
1580filestream access level0020Sets 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 scripts00
1587clr strict security1011CLR strict security enabled in the server11
16384Agent XPs1011Enable 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 PolyBase00
16396remote data archive0010Allow the use of the REMOTE_DATA_ARCHIVE data access for databases10
16397allow polybase export0010Allow INSERT into a Hadoop external table10Slower:
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 level0020Sets 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 XPs1011Enable 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 -
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) -
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