ASAS 2000 versus 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ASAS 2000 versus 2005

Hello Can anybody tell me what extra performance ASAS 2005 (SQL server 2005) gives compared to 2000? The question only regards software not hardware. The scenario is that we have to servers with the same hardware (with more than minimum requerements for SQL 2005 – not 64 bit). On server one we install SSAS 2000 and on server two SSAS 2005. We must be able to get the same information through the cubes on both servers. This question also regards the following statement from Microsoft: "OLAP user experience is directly correlated to the query performance. As noted, there are two factors that contribute most to the SSAS efficiency — the optimized query engine and the cube multidimensional model. SSAS 2005 brings additional performance enhancements. Most of them are related to the fact that SSAS 2005 cubes are not limited to having one fact table anymore. What this means to you is that you don#%92t have to use virtual cubes anymore. Another cause of grievance in the past was that SSAS 2000 required all dimensions to be loaded in memory. To address this issue, SSAS 2005 loads dimensions in memory on as-needed basis." This means than on server with SSAS 2005 we will have fewer but larger cubes which should enhance performance. Can this extra performance be quantified (perhaps a benchmark)? Thanks in advanced.
Interesting choice among the available forums for posting your question. So what does the winner receive?[<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
Please post the thread in relevant forums (moved from Forums contest section). With regard to the subject I would suggest to go thru the SQL Server books online for the differences between 2 versions. I believe there are many changes between the version on the subject. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Hi,
There are lots of performance related benefits over AS2000.
As you have already mentioned, there will not be any virtual cubes.
Also you can design your own aggregations in XML and append them for added performance.
MDX creation has been improved and many new functions are introduced.
Best thing is you can run AS profiler to check for query performance as well.
Creating calculated measures has been quite simpler.
Browsing the whole cube is quick and even deployment is far simpler.
Through DSV you can define complete database relationships.
On top of all this, you can have two separate cubes in same database pointing to different source all together.
You can read all other changes in more detail in books online.
]]>