August 13, 2010 at 1:01 pm
The same query was ran on each of the below servers, Indexes and stats updated or rebuilt after restoring from the production server to each of the test boxes
The query that I'm running is rather lenghy and involved, and returns 16 rows.
ProductionSQL 2005 (X32)Windows 2003 (x32) 8gb Ram 3 Disk Raid 5
CPU: 531
READS: 67037
WRITES: 22
DURATION: 532
DevelopmentSQL 2005 (X32)Windows 2003 (x32) 8gb Ram 3 Disk Raid 5
CPU: 766
READS: 86491
WRITES: 0
DURATION: 774
LOCALHOST SQL 2005 (X64)Windows 7 (x64) 8gb Ram, single Disk
CPU: 11013
READS: 351640
WRITES: 105
DURATION: 11037
New ServerSQL 2008 (x64)Windows 2008 (x64) 128gb Ram, 3 Disk Raid 5, also tried with 4 disk Raid 1+0
( I noticed slightly better performance with Raid 1+0, which I'll go back to when / if I can get the main performance issue resolved )
@@Version, Microsoft SQL Server 2008 (SP2) - 10.0.3798.0 (X64) Jun 18 2010 16:37:39 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Min Server Memory, 81928gb
Max Server Memory, 122880120gb
Minimum Memory Per Query: 2048
The New server results were very similar with my localhost, prior to SP2, both are far below the performance of the 32 bit 2005 machines.
Before installing 2008 SP2, the query was running in 11 seconds, after installing SP2 (SQLServer2008SP2-KB979450-x64-ENU), 1 minute and 45 seconds
I've also tried Lock Pages in Memory, no change.
CPU: 79638
READS: 351880
WRITES:113
DURATION:100097
Installing SP2, seemed to have compounded the issue. (I reindexed after Q2).
I'm all out of suggestions as things to try, short removing 2008 (x64) and installing 2008 (x32)
Suggestions of additional things to check or try would be appreciated. I simply can't put this new server into production like this.
I would have expected a significant gain in performance on the new server, especially since our current production server has 8gb ram and the new server has 128gb. Something has to be seriously wrong somwhere, I've just not found it yet.
August 13, 2010 at 3:21 pm
Can someone explain this one.
I had created 12 mdf files for tempdb, one per processor core, dual core 6 core procs.
I initially created the mdf files for tempdb at 1 gig each. I started going digging through the SP that I was testing with and wiriting #Temp was fine, but when I attempted to read from them, where bla, that's when the reads and preformance when way, way down hill.
I set all the tempdb mdf files to be 256 initial size, restarted the services, and now the same SP executes 186 MS, where it was taking 12 seconds. Just resizing the tempdb mdf files, made that much difference. This is quiet interesting.
Results after making the above changes.
CPU : 171
Reads: 66075
Writes: 10
Duration: 186
How can this minor change on the size of tempdb's mdf files, make that much of a difference?
August 13, 2010 at 3:33 pm
I just had to try to see if I could reproduce the same results to get the slow performance.
I re-set all my initial tempdb mdf files back to 1024, restarted services, same results, performance was terrible.
I guess with the file sizes being that big, it's not utilizing the multiple files, unless it's over the size of the initial file.
Now to test yet another initial size, I set all the initial files sizes to 128, performance was really, really bad now. I reset back to 256, and all is well again.
What's going to happen with my perforformance if one of the tempdb files grows now?
August 13, 2010 at 3:39 pm
my 2 cents...
two questions ..
1) what is the block size on the disk where tempdb is being created?
2) is tempdb sharing the same disk as your data file?
other general comments
unless you have seen the need for having 16 data files in tempdb; don't go over 8. there is performance hit you'll take, this is from Microsoft pfe. I have yet to test this theory, but staying at 8 have seem to been safe for me.
second why are you setting each tempdb file to 1gb; totaling of 16gb. Are you finding your tempdb is growing to 16gb everytime you run sql server? have you seen that need? If not I should set it for the need + 50% over 8 files only; with setting file growth to 250mb/growth if you are using auto growth.
check out kpi on your server..
Disk read/sec
Disk write/sec
Disk Read Queue Length
Disk Write Queue Length
Hopefully that gives you some more ideas...
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 13, 2010 at 3:49 pm
If I run the below select, with the 256 mg tempdb mdf's it takes 11 seconds to run, 4 on the production box, same table
Select *
into #temp
from table For a tabl that has a high number of records
If I increase the inititial size of tempdb to 1 gig, the above query is much faster on the new server, but the sp that use tempdb, performance is terrible.
So, I believe I have narrowed down my performance issues to tempdb.
All default settings on tempdb, nothing changed from defaults, other than the initial size. dedicated raid 1 for tempdb.
Good performance in one situation if the size is 1 gig, very bad performance on SP's that us tempdb.
Good Performance on SP's if smaller and very bad performance on select into
Interesting enough, that is is only true with 2008 (x64) and not with 2005 (x32)
I may have to put 2008 (x32) on the new server, and not use 2008 (x64)
August 16, 2010 at 12:56 am
To me this sounds like you are getting different execution plans. Have you checked the actual plans for your test procedure both when it executes fast and when it executes slowly ?
Could you post the actual plans here so we can take a look ?
August 16, 2010 at 9:20 am
The performance issue is related to tempdb, or at least it appears to be.
Tempdb, is on a dedicated RAID10, 64K Stripe element size, 64k cluster size when formated.
I've included an image attachments of the query plans for a simple query.
I've tested multiple instances of 2008 (64) and each that I've tested has major performance issues with tempdb.
I'd be curious if someone else could run a simple test on the same DB 2005 (x32) and 2008 (x64) to see if they see majore performance issues with 2008 (x64) as well.
August 16, 2010 at 12:15 pm
Perhaps the drive partitions housing the tempdb files are not aligned? See "Disk Partition Alignment Best Practices for SQL Server" at http://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspx
SQL = Scarcely Qualifies as a Language
August 16, 2010 at 1:41 pm
I've already verified the alignment, it all looks good to me.
BlockSize Index Name StartingOffset
512 0 Disk #0, Partition #0 32256
512 1 Disk #0, Partition #1 82837504
512 2 Disk #0, Partition #2 3304062976
512 0 Disk #1, Partition #0 1048576 D DRIVE (tempdb)
512 0 Disk #2, Partition #0 1048576 E DRIVE (SQL DB FILES)
512 0 Disk #3, Partition #0 1048576 F DRIVE (SQL DB FILES)
May 3, 2011 at 8:17 am
Are you still having the same issues? I am seeing the same loss of performance.
We are about to plan an upgrade and I am looking to move the the latest and greatest.
Test Box:
Windows Server 2008 R2 and SQL 2008 R2
DL580 G7 (4 8 core processor with hyper threading)
64 GB RAM.
Raid 1 + 0
I am running a query that I captures through SQL profile that our EPR runs.
Duration: 36 seconds.
Production Box:
Windows 2003 Enterprise (32) SQL 2005 Enterprise (32)
DL580 G5 (4 4 core processor)
64 GB RAM.
Raid 5
Running same query as above.
Duration: 2 seconds.
I have tried many different options and I even have calls into HP.
Any help would be appreciated.
Jeremy
May 3, 2011 at 9:06 am
We ended up changing out the MotherBoard and all the memory via Dell. The box is now performing as expected.
May 3, 2011 at 9:14 am
Really, we ordered 2 of the HP DL580 G7 (twin servers)
One to replace or production server and the other for our DR site.
Both servers are acting the same exact way, so I do not know if we ordered a thrid we would see an improvment.
May 3, 2011 at 9:27 am
Ours was a Dell server, and the performance issues was drastic, dispite all the config changes I made, it made no difference.
Since you have two new servers, I'd look more at configuration versus the server itself.
May 3, 2011 at 9:59 am
Currently we have Windows 2008 with SQL 2008 (all x32) on one server and Windows 2008 R2 and SQL 2008 R2 on the other.
The x32 server is running so much better, it is amazing how many posts there are online about people complaining about the speed of x64. I am not sure if it is the OS or SQL or both.
May 3, 2011 at 12:11 pm
I did see some majore performance degredation on some specific queries on x64, specifically those that include NOT IN (SUBQUERY).
check your queires/sp's in question to see if that might be an issue.
If so, you can re-write those to be outer joins
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply