Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Have you used any monitoring tools to see if you're IO or CPU bound? –  Phil May 10 at 19:56
    
We had performance monitor up and saw CPU usage under 5% and disk activity between 3MB/s and 100MB/s. Not sure how to test if the disk write is the cause but with transactions the newer setup was certainly faster. –  Dan Roberts May 10 at 19:58
    
What is the hardware on both machines? Significantly more RAM on the new box? –  Max Vernon May 10 at 21:40
1  
What does select * from sys.configurations show on both machines? –  Max Vernon May 10 at 21:41
1  
Can you post query plans for identical queries from the old and new boxes? –  Max Vernon May 11 at 2:01

2 Answers 2

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 dmesg output. 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?

share|improve this answer
    
dell open manager is installed which has shown raid battery issues in the past but does not show one here. I will look into it further though –  Dan Roberts May 13 at 13:39

It may be the case that something somewhere (the RAID controller, Windows, or SQL Server) in the new environment is configured to be more paranoid (or just less aggressive) about buffering - being more likely to wait until a write action is committed to disk before letting the current process move on. This would generally affect many small transactions more than it would affect on large one.

Without any explicit transaction around the whole lot you may be ending up with an implicit transaction around each of the INSERT statements meaning you see extra commit-to-hardware latency between each individual one (so with an explicit transaction around the loop you have one transaction affecting 250,000 rows - without it you have 250,000 sequences of "affect one row then commit"). This is why the performance difference between with and without the explicit transaction may seem counter intuitive.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.