SQL Server 64 bit Performance Testing [modified]
-
Recently I’ve upgraded a production DB Server from the 32 bit versions of Windows Server 2003 and SQL 2005 to the 64 bit versions. The primary reason for this upgrade was the ability to utilize the 8gb of RAM available. Before I did this upgrade I ran a benchmark test (see below) by inserting 1 million rows into a table. I ran this test 5 times to flatten out the true performance. The benchmark results: 32 BIT RESULTS 1.) 20:24 2.) 17:12 3.) 10:10 4.) 10:24 5.) 10:51 Average of top 3: 10:28 After the upgrade I ran the same test, here are the results: 64 BIT RESULTS 1.) 2:30 2.) 2:31 3.) 2:25 4.) 2:26 5.) 2:23 Average of top 3: 2:25 The 64 bit version SMOKED the 32 bit version; even I wasn’t prepared for this kind of improvement! All this for no increase in cost; both Windows Server and SQL Server 64 bit versions cost the same as the 32 bit versions. The Test: CREATE TABLE [dbo].[SpeedTest] ( ID int IDENTITY (1, 1) NOT NULL , Description varchar(50) NOT NULL , Account float NULL , IsTrue bit NOT NULL , Quantity int NULL, CreationDate datetime ) GO DECLARE @Counter INT SET @Counter = 1 WHILE @Counter < 1000000 BEGIN INSERT dbo.SpeedTest(Description, Account, IsTrue, Quantity, CreationDate) VALUES ('My really long description', 7654321, 1, @Counter, getdate()) SET @Counter = @Counter + 1 END It's good to be alive modified on Friday, December 07, 2007 9:15:58 AM
-
Recently I’ve upgraded a production DB Server from the 32 bit versions of Windows Server 2003 and SQL 2005 to the 64 bit versions. The primary reason for this upgrade was the ability to utilize the 8gb of RAM available. Before I did this upgrade I ran a benchmark test (see below) by inserting 1 million rows into a table. I ran this test 5 times to flatten out the true performance. The benchmark results: 32 BIT RESULTS 1.) 20:24 2.) 17:12 3.) 10:10 4.) 10:24 5.) 10:51 Average of top 3: 10:28 After the upgrade I ran the same test, here are the results: 64 BIT RESULTS 1.) 2:30 2.) 2:31 3.) 2:25 4.) 2:26 5.) 2:23 Average of top 3: 2:25 The 64 bit version SMOKED the 32 bit version; even I wasn’t prepared for this kind of improvement! All this for no increase in cost; both Windows Server and SQL Server 64 bit versions cost the same as the 32 bit versions. The Test: CREATE TABLE [dbo].[SpeedTest] ( ID int IDENTITY (1, 1) NOT NULL , Description varchar(50) NOT NULL , Account float NULL , IsTrue bit NOT NULL , Quantity int NULL, CreationDate datetime ) GO DECLARE @Counter INT SET @Counter = 1 WHILE @Counter < 1000000 BEGIN INSERT dbo.SpeedTest(Description, Account, IsTrue, Quantity, CreationDate) VALUES ('My really long description', 7654321, 1, @Counter, getdate()) SET @Counter = @Counter + 1 END It's good to be alive modified on Friday, December 07, 2007 9:15:58 AM
I must confess that I'm slightly surprised by this result. I would have expected that query to be bounded by the I/O to the log file. Did you perhaps use
tempdbfor the second run, whose log is not flushed synchronously with committing a transaction? Also, your test data set doesn't come near to exercising the amount of memory fitted: it's only about 60 bytes per row, giving a total dataset of 60MB. The 32-bit versions will easily (obviously) fit it into the virtual address space available. It appears my laptop is better than your server: the 32-bit SQL Server 2005 Developer Edition, with SP2 installed, gave 7:48 against a fresh test database (initialized with 50MB data and log files, so a small amount of data space growth was required, as I guessed how much space was needed). For comparison, against tempdb it ran in 34 seconds. I also looked into setting NOCOUNT to ON to stop it having to return a million resultsets to the client (to give the '1 row(s) affected' message) but this didn't seem to make a lot of difference. It's possible that the SQL parser has been improved but to be honest, signs point to misconfiguration of the 32-bit server or the test. I would have expected a slight improvement on x64 but not an order of magnitude. Perhaps your disk was just very fragmented with little contiguous space available?
DoEvents: Generating unexpected recursion since 1991 -
Recently I’ve upgraded a production DB Server from the 32 bit versions of Windows Server 2003 and SQL 2005 to the 64 bit versions. The primary reason for this upgrade was the ability to utilize the 8gb of RAM available. Before I did this upgrade I ran a benchmark test (see below) by inserting 1 million rows into a table. I ran this test 5 times to flatten out the true performance. The benchmark results: 32 BIT RESULTS 1.) 20:24 2.) 17:12 3.) 10:10 4.) 10:24 5.) 10:51 Average of top 3: 10:28 After the upgrade I ran the same test, here are the results: 64 BIT RESULTS 1.) 2:30 2.) 2:31 3.) 2:25 4.) 2:26 5.) 2:23 Average of top 3: 2:25 The 64 bit version SMOKED the 32 bit version; even I wasn’t prepared for this kind of improvement! All this for no increase in cost; both Windows Server and SQL Server 64 bit versions cost the same as the 32 bit versions. The Test: CREATE TABLE [dbo].[SpeedTest] ( ID int IDENTITY (1, 1) NOT NULL , Description varchar(50) NOT NULL , Account float NULL , IsTrue bit NOT NULL , Quantity int NULL, CreationDate datetime ) GO DECLARE @Counter INT SET @Counter = 1 WHILE @Counter < 1000000 BEGIN INSERT dbo.SpeedTest(Description, Account, IsTrue, Quantity, CreationDate) VALUES ('My really long description', 7654321, 1, @Counter, getdate()) SET @Counter = @Counter + 1 END It's good to be alive modified on Friday, December 07, 2007 9:15:58 AM
It's funny - I was just talking about this. We have a client who run a BI system on 32 bit SQL Server, and queries that take a couple of hours on their system run in just a few minutes on our 64 bit server. SQL 64 bit definitely rocks. I should explain that the 64 bit hardware is better specced and performs better than the 32 bit hardware, so it helps to give that boost.
Deja View - the feeling that you've seen this post before.
modified on Friday, December 07, 2007 10:32:20 AM
-
Recently I’ve upgraded a production DB Server from the 32 bit versions of Windows Server 2003 and SQL 2005 to the 64 bit versions. The primary reason for this upgrade was the ability to utilize the 8gb of RAM available. Before I did this upgrade I ran a benchmark test (see below) by inserting 1 million rows into a table. I ran this test 5 times to flatten out the true performance. The benchmark results: 32 BIT RESULTS 1.) 20:24 2.) 17:12 3.) 10:10 4.) 10:24 5.) 10:51 Average of top 3: 10:28 After the upgrade I ran the same test, here are the results: 64 BIT RESULTS 1.) 2:30 2.) 2:31 3.) 2:25 4.) 2:26 5.) 2:23 Average of top 3: 2:25 The 64 bit version SMOKED the 32 bit version; even I wasn’t prepared for this kind of improvement! All this for no increase in cost; both Windows Server and SQL Server 64 bit versions cost the same as the 32 bit versions. The Test: CREATE TABLE [dbo].[SpeedTest] ( ID int IDENTITY (1, 1) NOT NULL , Description varchar(50) NOT NULL , Account float NULL , IsTrue bit NOT NULL , Quantity int NULL, CreationDate datetime ) GO DECLARE @Counter INT SET @Counter = 1 WHILE @Counter < 1000000 BEGIN INSERT dbo.SpeedTest(Description, Account, IsTrue, Quantity, CreationDate) VALUES ('My really long description', 7654321, 1, @Counter, getdate()) SET @Counter = @Counter + 1 END It's good to be alive modified on Friday, December 07, 2007 9:15:58 AM
Witch version of SQL ? enterprise ? maybe 32bits uses 2Gb of RAM and 64Bit 6Gb, and cache can make the difference... Can yoy specify the environments ? It seems impossibile a 5 to 10 times difference Davide
-
Witch version of SQL ? enterprise ? maybe 32bits uses 2Gb of RAM and 64Bit 6Gb, and cache can make the difference... Can yoy specify the environments ? It seems impossibile a 5 to 10 times difference Davide
Server has 2 Quad Intel Xeons connected to a SAN. In fact 32 bits can only use 2gb out of the box, but can use 3gb when AWE is configured. I had not configured AWE for the 32 bit test. 64 bits can access all 8gb. The network manager commented there is a 70% improvement in I/O against the SAN when using 64 bits. Being that the test was 1 million inserts, I/O played a significant factor in the performance. In both cases the server didn't have anything loaded beyond SQL Server 2005 Enterprise. For the 32 bit test IIS was installed. In both cases the server was idle besides running these tests. Is their a compound affect of switching from 32 bit to 64 bit for both the OS and SQL?
It's good to be alive
-
It's funny - I was just talking about this. We have a client who run a BI system on 32 bit SQL Server, and queries that take a couple of hours on their system run in just a few minutes on our 64 bit server. SQL 64 bit definitely rocks. I should explain that the 64 bit hardware is better specced and performs better than the 32 bit hardware, so it helps to give that boost.
Deja View - the feeling that you've seen this post before.
modified on Friday, December 07, 2007 10:32:20 AM
In this case it was the same box. I flattened it and installed 64 bit OS and SQL.
It's good to be alive
-
Recently I’ve upgraded a production DB Server from the 32 bit versions of Windows Server 2003 and SQL 2005 to the 64 bit versions. The primary reason for this upgrade was the ability to utilize the 8gb of RAM available. Before I did this upgrade I ran a benchmark test (see below) by inserting 1 million rows into a table. I ran this test 5 times to flatten out the true performance. The benchmark results: 32 BIT RESULTS 1.) 20:24 2.) 17:12 3.) 10:10 4.) 10:24 5.) 10:51 Average of top 3: 10:28 After the upgrade I ran the same test, here are the results: 64 BIT RESULTS 1.) 2:30 2.) 2:31 3.) 2:25 4.) 2:26 5.) 2:23 Average of top 3: 2:25 The 64 bit version SMOKED the 32 bit version; even I wasn’t prepared for this kind of improvement! All this for no increase in cost; both Windows Server and SQL Server 64 bit versions cost the same as the 32 bit versions. The Test: CREATE TABLE [dbo].[SpeedTest] ( ID int IDENTITY (1, 1) NOT NULL , Description varchar(50) NOT NULL , Account float NULL , IsTrue bit NOT NULL , Quantity int NULL, CreationDate datetime ) GO DECLARE @Counter INT SET @Counter = 1 WHILE @Counter < 1000000 BEGIN INSERT dbo.SpeedTest(Description, Account, IsTrue, Quantity, CreationDate) VALUES ('My really long description', 7654321, 1, @Counter, getdate()) SET @Counter = @Counter + 1 END It's good to be alive modified on Friday, December 07, 2007 9:15:58 AM
We found exactly the same thing when we moved to 64bit. It's awesome
cheers, Chris Maunder
CodeProject.com : C++ MVP
-
Recently I’ve upgraded a production DB Server from the 32 bit versions of Windows Server 2003 and SQL 2005 to the 64 bit versions. The primary reason for this upgrade was the ability to utilize the 8gb of RAM available. Before I did this upgrade I ran a benchmark test (see below) by inserting 1 million rows into a table. I ran this test 5 times to flatten out the true performance. The benchmark results: 32 BIT RESULTS 1.) 20:24 2.) 17:12 3.) 10:10 4.) 10:24 5.) 10:51 Average of top 3: 10:28 After the upgrade I ran the same test, here are the results: 64 BIT RESULTS 1.) 2:30 2.) 2:31 3.) 2:25 4.) 2:26 5.) 2:23 Average of top 3: 2:25 The 64 bit version SMOKED the 32 bit version; even I wasn’t prepared for this kind of improvement! All this for no increase in cost; both Windows Server and SQL Server 64 bit versions cost the same as the 32 bit versions. The Test: CREATE TABLE [dbo].[SpeedTest] ( ID int IDENTITY (1, 1) NOT NULL , Description varchar(50) NOT NULL , Account float NULL , IsTrue bit NOT NULL , Quantity int NULL, CreationDate datetime ) GO DECLARE @Counter INT SET @Counter = 1 WHILE @Counter < 1000000 BEGIN INSERT dbo.SpeedTest(Description, Account, IsTrue, Quantity, CreationDate) VALUES ('My really long description', 7654321, 1, @Counter, getdate()) SET @Counter = @Counter + 1 END It's good to be alive modified on Friday, December 07, 2007 9:15:58 AM
Very cool. Any idea why the 32 bit version showed such a large variance between slowest and fastest results?
"On one of my cards it said I had to find temperatures lower than -8. The numbers I uncovered were -6 and -7 so I thought I had won, and so did the woman in the shop. But when she scanned the card the machine said I hadn't. "I phoned Camelot and they fobbed me off with some story that -6 is higher - not lower - than -8 but I'm not having it." -Tina Farrell, a 23 year old thicky from Levenshulme, Manchester.