June 16, 2008 at 1:25 pm
No, wish it were that easy. I find it all very interesting, sadly, but the optimisers are so different that some queries run so much better and others run really badly. For example I'm currently tracking through a 2000 to 2005 upgrade - there's a particular process which can take a long time, the proc has thousands of lines of code so it's a very difficult to tune in the normal way. I ported the database to 2005 ( on the same hardware ) and ran the proc hoping to use the dmv's as an aid to tuning. Sadly the process ran in 25% of the time and did a different io pattern and didn't really need any extra indexes - ok so maybe that's an exception. One client had lots of problems - their application generated ad-hoc server side code - under 2005 it was a disaster and working with microsoft we deployed several hundred plan guides to aid performance. I won't go into some of the other things we did. However the indexing was rubbish and in every case of poor query I looked at creating new indexes solved the problems - there were a few exceptions - a query with a parameter which could return several hundred thousand rows or just a couple - tricky to gte the plan right. You just have to monitor and tets. There's some really good microsoft tools to help you on your upgrade you need the SQL Server Upgrade Assistant SSUA from scalability Experts - thye didn't really publicise this tool very well.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 16, 2008 at 1:36 pm
colin Leversuch-Roberts (6/16/2008)
No, wish it were that easy. I find it all very interesting, sadly, but the optimisers are so different that some queries run so much better and others run really badly. For example I'm currently tracking through a 2000 to 2005 upgrade - there's a particular process which can take a long time, the proc has thousands of lines of code so it's a very difficult to tune in the normal way. I ported the database to 2005 ( on the same hardware ) and ran the proc hoping to use the dmv's as an aid to tuning. Sadly the process ran in 25% of the time and did a different io pattern and didn't really need any extra indexes - ok so maybe that's an exception. One client had lots of problems - their application generated ad-hoc server side code - under 2005 it was a disaster and working with microsoft we deployed several hundred plan guides to aid performance. I won't go into some of the other things we did. However the indexing was rubbish and in every case of poor query I looked at creating new indexes solved the problems - there were a few exceptions - a query with a parameter which could return several hundred thousand rows or just a couple - tricky to gte the plan right. You just have to monitor and tets. There's some really good microsoft tools to help you on your upgrade you need the SQL Server Upgrade Assistant SSUA from scalability Experts - thye didn't really publicise this tool very well.
These issues are related only to the SQL 2005 upgrade though, correct?
ie. they are not related to the different memory configuration (going from 32- to 64- bit).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 18, 2008 at 5:10 am
Colin,
I don't wholy disagree with you on the 64-bit vs 32-bit. In fact in most cases, and for DB servers that have large DBs and require performance then 64-bit may well give inprovements, all the stuff about VAS, upgrades from 2000 to 2005, etc., excepted. But, IMHO I don't believe that the blanket statement/advice to '...forget x32 and move to x64...' is correct. There are a number of factors that should be considered before just up and moving to a 64-bit platform. Not all readers to this site work with high-end, high-performance systems and/or have gained the experience you may have had.
With regard to the article, I was trying to highlight that, for some very few situations (which you may not have come across), there may be a penalty by moving from 32-bit to 64-bit. Below is the quote from the article on the Microsoft SQL Programmability & API Development Team's blog:
'...The end result is applications which are not memory constrained on 32-bit (especially OLTP) may incur a performance penalty when running on 64-bit using the same hardware. We have observed approximately 10-15% degradation in most cases however the impact will depend on the characteristics of the workload and hardware being used...'
So, if you have a an application close in characteristics to the above, then there is no benefit and possibly a detrimental effect. Also, if someone has a server where 4GB of RAM is adequate for their needs, then I can't see how the 64-bit will benefit if the server is not memory-constrained.
As usual with any of this, testing is the way to go.
BTW, I too have 64-bit and 32-bit servers at home that I use for testing/development/pre-releases, and have been involved in the migration from 32 to 64-bit, and deploying both for several years without such issues...but just because I haven't come across it doesn't mean that I should ignore it, I still considered the above statement since coming across it in April of last year, whenever advising or planning such a migration. Hence, why I said '...its a case of knowing the work-type/load of the database you have...'
June 18, 2008 at 8:13 am
humbleDBA (6/18/2008)
Colin,I don't wholy disagree with you on the 64-bit vs 32-bit. In fact in most cases, and for DB servers that have large DBs and require performance then 64-bit may well give inprovements
couldnt agree more, i'm a fan of 64bit SQL but you cant write off 32bit as 64bit is not necessarily for everyone. Each case is different
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 18, 2008 at 9:44 am
I'd agree as well that 32bit isn't necessarily dead.
However, if you can consolidate servers, if you're considering virtualization, if you basically need bigger servers and more memory can help, you should really look at 64bit.
June 18, 2008 at 10:53 am
Yep, totally agree Steve...if you can consolidate, virtualise, etc. (though each of these brings their own issues and considerations for performance, availability, etc.), hence my caveat of '...all the stuff about VAS, upgrades from 2000 to 2005, etc., excepted...' 🙂
I haven't come across the detrimental performance issues, as mentioned by Microsoft SQL Programmability & API Development Team, following a move from 32-bit to 64-bit, but that doesn't mean it should be discounted arbitrarily, which was the point of my argument to the statement '...forget x32 and move to x64...'.
A good discussion, though.
June 18, 2008 at 11:00 am
Sorry, posted twice...daooooh! :blush:
June 18, 2008 at 11:01 am
The sticky point with all this is that because of all the potential issues surrounding a move to 64-bit, many companies will hesitate to go ahead with it.
Sure, one can say a 64-bit upgrade will need to be tested, but how many companies would be willing to invest in a 64-bit box for testing, only to find out that staying with 32-bit is better?
This is a problem that will need to be addressed by Microsoft (and hardware vendors) if 64-bit technology is to have the success it deserves.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 18, 2008 at 3:39 pm
even under virtualisation 64bit isnt necessarily the defacto platform
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2008 at 9:37 am
Brent Ozar writes this:
"First, that tricky checkbox that says “Enable AWE”. You might think you only have to check that box if you’re using a 32-bit server with more than 4 gigs of memory, but nooooo. According to a couple of Microsoft folks I’ve spoken with (and I don’t mean first line support), that checkbox causes SQL Server to handle memory differently - not just enable AWE. In fact, that box should be checked on 64-bit servers, too - any box with more than 4 gigs of memory!"
http://knol.google.com/k/brent-ozar/sql-server-2005-setup-checklist/1f7zsjm9jopw/5#
This is not my reading from other sources.
My scenario: Installing 64-bit SQL Server 2005 on 64-bit Windows Server 2003.
Enlightenment?
October 30, 2008 at 12:59 pm
Apologies if I should have created a new topic....
Having read thro this thread and a multitude of others on here and elsewhere, I must confess to still being confused....
We need to set up a new box and make best use of the 8GB RAM installed...not sure what is the best option and whether we need/should use PAE/AWE
SQL 2005 Std Ed....this has to be 32-bit, due to ODBC issues with legacy systems.
Windows 2008 Standard...can be either 32 or 64-bit
The server will also run SSAS 2005
This is used primarily for ETL from legacy systems into a SQL datastore with SSAS cubes, with some ad hoc SQL queries.
Should we run on Win2008 32 or 64bit?
Do we need to enable PAE /3GB in boot.ini?
If we use AWE in SQL what effect on SSAS...I dont think SSAS is AWE aware...???
Thanks for reading and any pointers will be most appreciated
gah
confusion will be my epitaph
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 30, 2008 at 1:14 pm
gah (10/30/2008)
Apologies if I should have created a new topic....Having read thro this thread and a multitude of others on here and elsewhere, I must confess to still being confused....
We need to set up a new box and make best use of the 8GB RAM installed...not sure what is the best option and whether we need/should use PAE/AWE
SQL 2005 Std Ed....this has to be 32-bit, due to ODBC issues with legacy systems.
Windows 2008 Standard...can be either 32 or 64-bit
The server will also run SSAS 2005
This is used primarily for ETL from legacy systems into a SQL datastore with SSAS cubes, with some ad hoc SQL queries.
Should we run on Win2008 32 or 64bit?
Do we need to enable PAE /3GB in boot.ini?
If we use AWE in SQL what effect on SSAS...I dont think SSAS is AWE aware...???
Thanks for reading and any pointers will be most appreciated
gah
confusion will be my epitaph
Here are answers to your questions (to the best of my knowledge):
- You are correct, SSAS is not AWE-aware, so it would be restricted to a max of 2 GB of phys. memory.
- /3GB is not necessary (not sure about PAE, it is required in some Windows OS versions and not in others, best to enable PAE, to be on the safe side)
- enable AWE to max memory of 4 GB, to enable the data buffer of your SQL instance to 'see' 4 GB of memory and leave 2 GB for the OS (the OS needs memory to manage the AWE allocation, among other things)
- I don't think it makes a difference whether you install Windows Server 32- or 64-bit (unless you wanted to install SQL 64-bit, which you don't in this case).
Hope this helps.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 31, 2008 at 12:43 am
Thanks for the reply Marios
- enable AWE to max memory of 4 GB, to enable the data buffer of your SQL instance to 'see' 4 GB of memory and leave 2 GB for the OS
should this be AWE to max 6GB, not 4GB...we have 8GB available?
gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 31, 2008 at 3:44 am
gah (10/31/2008)
Thanks for the reply Marios- enable AWE to max memory of 4 GB, to enable the data buffer of your SQL instance to 'see' 4 GB of memory and leave 2 GB for the OS
should this be AWE to max 6GB, not 4GB...we have 8GB available?
gah
Since you want to install SSAS on the same box, you have to allow for 2 GB to be used by SSAS. That leaves 4 GB for the database engine instance.
Regarding your previous question about whether you should install 32-bit or 64-bit OS. I recently found out that System Center Operations Manager 2007 - a Microsoft monitoring tool we use in our shop - does not work on machines where SQL is at 32-bit and OS at 64-bit (both SQL and OS have to be on the same version).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 31, 2008 at 4:39 am
Thank you very much Marios...that has clarified it.
regards gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 46 through 60 (of 64 total)
You must be logged in to reply to this topic. Login to reply