We are upgrading our database hardware and software. The previous server is about 5 years old and is running SQL Server 2005 while the new one is SQL Server 2012. Every hardware component is faster in the new server.
Selects are faster and multiple inserts inside a transaction are around 50% faster, but raw inserts are actually slower by about 50%.
For example if I run the following code on each server the old setup is done in about 30 seconds and the new one takes 45. Throwing transactions around it results in old setup running in about 12 seconds and new setup running in about 6 seconds.
CREATE TABLE [dbo].[test] ( [testval] [varchar](50) NULL) ON [PRIMARY];
declare @id int
set @id = 1
while @id <= 250000
begin
insert into test (testval)
values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
set @id = @id + 1
end
Just not clear on why the difference exists. Any suggestions would be appreciated.
Hardware
Old
Dell PowerEdge 2970
Six-Core AMD Opteron 2427 2.19 GHz X 2
16 GB RAM
Microsoft Windows Server 2008 SP2 x64
4 X 300GB SAS 2.5 10K RAID5
New – 2 servers are actually involved with the same hardware. They are setup for clustering and asynchronous mirroring. We have turned mirroring off and there is no change in results
Dell R720
Intel Xeon CPU EU-2640 v2 @ 2.00 GHz X 2
64 GB RAM
Microsoft Windows Server 2012 R2 Standard x64
RAID 10 with 4x15k for log files, 4x10k for mdf files, 2x10 for OS
sys.configuration
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
Old
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 32767 0 Maximum worker threads 0 1
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
518 show advanced options 1 0 1 1 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) 20 0 2147483647 20 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 0 2147483647 65536 Maximum size of a text field in replication. 1 0
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism 1 1
1539 max degree of parallelism 0 0 64 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 16 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
1548 awe enabled 0 0 1 0 AWE enabled in the server 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 1 0 1 1 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 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
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
16386 Database Mail XPs 1 0 1 1 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
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
16391 Ad Hoc Distributed Queries 1 0 1 1 Enable or disable Ad Hoc Distributed Queries 1 1
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
New
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 1 0 1 1 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 4 0 32767 5 cost threshold for parallelism 1 1
1539 max degree of parallelism 16 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
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
Best Answer
I truly and desperately suck when it comes to Windows and SQL Server, but what I've seen from the past with $some_enterprise_hardware_vendor with $some_their_model was that the RAID controller had a dead battery. That lead to disabled RAID cache, which was NOT visible from the server hardware management interface, everything was green there.
Linux, on the other hand, somehow saw that the battery was dead (and RAID caches disabled due that) and informed that in its
dmesgoutput. The terrible I/O performance returned to expected after we changed the battery.So... perhaps some kind of hardware glitch you have not yet spotted?