Performance Improvement in Analysis Services 2005

Hi,

I am new to Analysis Services and was wondering if some one could
assist me with the issues I am having.

Basically we have one single table on which I need to build a cube so
that managemet can view the data in excel and have good perofrmance. I
started with AS 2000 but ran into the issue of having more than 64,000
members for a dimension level which were not unique.I tried various
suggestions given on the forum but none worked.

So I installed AS 2005 and migrated the database from AS 2000 and build the cube. However I am running into major performance issues, What I have are 2 dimensions with hierarchies Products and Time . The lowest dimesion in Products in something called Instrument Id and it is the 4th level in my hierarchy, the Time dimension has 3 levels, month being the lowest.

I read the articles which described the difference between hierarchies and attribute relationships in AS 2000 and AS 2005. So I defined atributes in my cube design for the above mentioned dimensions. However for the lowest level of detail ( Instument Id across Months ) the query takes for ever. I have the aggregation set at 70%

I know there is an issue with my design but with my limited knowledge of Analysis Serivces I am unble to focus in the right direction. Can someone help me with this

Thanks

For the clues on how to deal with performance problems and the for the better desing ideas take a look at the showcase study; project REAL

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Particularly in your case I can think of some reasons for slower performance.

For one, the query you are trying to run is going to scan your entire fact and is probably going to bring very big result. Try and make sure you provide a slice for your query. Select only few Instrument Id's not entire level.

Second, try and partition your measure group. Having several partitions would help to speed up your query. This is because Analysis Server can detect which partitions hold the data for the particular Instument Id's you query for and will only scan these partitions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks for the input, just a couple of quick follow up questions

1. When you say "make sure you provide a slice for your query. Select only few Instrument Id's not entire level" I am not sure how i would go about doing it.

2. As far a partitions I was trying to create partitions for each month of data using the query binding feature. I am not supposed to create a poartition against a dimension. and how would I go about creating partitions across a measure group.

Thanks

|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.

Performance Improvement in Analysis Services 2005

Hi,

I am new to Analysis Services and was wondering if some one could
assist me with the issues I am having.

Basically we have one single table on which I need to build a cube so
that managemet can view the data in excel and have good perofrmance. I
started with AS 2000 but ran into the issue of having more than 64,000
members for a dimension level which were not unique.I tried various
suggestions given on the forum but none worked.

So I installed AS 2005 and migrated the database from AS 2000 and build the cube. However I am running into major performance issues, What I have are 2 dimensions with hierarchies Products and Time . The lowest dimesion in Products in something called Instrument Id and it is the 4th level in my hierarchy, the Time dimension has 3 levels, month being the lowest.

I read the articles which described the difference between hierarchies and attribute relationships in AS 2000 and AS 2005. So I defined atributes in my cube design for the above mentioned dimensions. However for the lowest level of detail ( Instument Id across Months ) the query takes for ever. I have the aggregation set at 70%

I know there is an issue with my design but with my limited knowledge of Analysis Serivces I am unble to focus in the right direction. Can someone help me with this

Thanks

For the clues on how to deal with performance problems and the for the better desing ideas take a look at the showcase study; project REAL

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Particularly in your case I can think of some reasons for slower performance.

For one, the query you are trying to run is going to scan your entire fact and is probably going to bring very big result. Try and make sure you provide a slice for your query. Select only few Instrument Id's not entire level.

Second, try and partition your measure group. Having several partitions would help to speed up your query. This is because Analysis Server can detect which partitions hold the data for the particular Instument Id's you query for and will only scan these partitions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks for the input, just a couple of quick follow up questions

1. When you say "make sure you provide a slice for your query. Select only few Instrument Id's not entire level" I am not sure how i would go about doing it.

2. As far a partitions I was trying to create partitions for each month of data using the query binding feature. I am not supposed to create a poartition against a dimension. and how would I go about creating partitions across a measure group.

Thanks

|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.

Performance

Hi
I have a sql server 2000 running with SP4.
My goal is the improvement of server prformance! Can i install server
performance advisor and get about SQL?
Regards
Chico
Start with
http://www.sql-server-performance.com/
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>
|||Run profiler for 20 minutes or so during a period of high activity. Store
the trace in a SQL table. Index the trace table by cpu, then duration, then
reads, then writes - ie 4 seperate indexes.
Then do queries like this
select top 1000 textdata from tracetable order by duration desc
GO
select top 1000 textdata from tracetable order by writes desc
GO
select top 1000 textdata from tracetable order by Reads desc
Go
select top 1000 textdata from tracetable order by CPU desc
Now you know the most expensive queries or stored procedures. From here on
end its a matter of improving the performance of the queries, procs
identified above. Study the execution plans and see if you can't improve the
code itself, or improve the performance by well placed indexes.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>

Performance

Hi
I have a sql server 2000 running with SP4.
My goal is the improvement of server prformance! Can i install server
performance advisor and get about SQL?
RegardsChico
Start with
http://www.sql-server-performance.com/
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>|||Run profiler for 20 minutes or so during a period of high activity. Store
the trace in a SQL table. Index the trace table by cpu, then duration, then
reads, then writes - ie 4 seperate indexes.
Then do queries like this
select top 1000 textdata from tracetable order by duration desc
GO
select top 1000 textdata from tracetable order by writes desc
GO
select top 1000 textdata from tracetable order by Reads desc
Go
select top 1000 textdata from tracetable order by CPU desc
Now you know the most expensive queries or stored procedures. From here on
end its a matter of improving the performance of the queries, procs
identified above. Study the execution plans and see if you can't improve the
code itself, or improve the performance by well placed indexes.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>

Performance

Hi
I have a sql server 2000 running with SP4.
My goal is the improvement of server prformance! Can i install server
performance advisor and get about SQL?
RegardsChico
Start with
http://www.sql-server-performance.com/
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>|||Run profiler for 20 minutes or so during a period of high activity. Store
the trace in a SQL table. Index the trace table by cpu, then duration, then
reads, then writes - ie 4 seperate indexes.
Then do queries like this
select top 1000 textdata from tracetable order by duration desc
GO
select top 1000 textdata from tracetable order by writes desc
GO
select top 1000 textdata from tracetable order by Reads desc
Go
select top 1000 textdata from tracetable order by CPU desc
Now you know the most expensive queries or stored procedures. From here on
end its a matter of improving the performance of the queries, procs
identified above. Study the execution plans and see if you can't improve the
code itself, or improve the performance by well placed indexes.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chico" <Chico@.discussions.microsoft.com> wrote in message
news:7015EDDF-A5D5-403A-BEFD-6FFD003A0D26@.microsoft.com...
> Hi
> I have a sql server 2000 running with SP4.
> My goal is the improvement of server prformance! Can i install server
> performance advisor and get about SQL?
> Regards
>

Performance

Hello,
How can i be shure that my server needs improvement?
I have done some analisys and get information as
-sometimes some questions gets a lot of cpu usage
-sometimes i have a lot of write usage
-sometimes i have a lot of read usage
How much cpu usage is good to get better performance?
What is worst : -so much Read / -so much Write / -so much CPF usage ?
Thanks
Leandro Loureiro dos Santos
Vitoria - ESHi,
If the CPU usage is in a average of 60 - 70 % then your processor
utilization is normal. In case if it always 100 % , u may need to budget for
a additional CPU.
If you see your disk write is always 100 % , defenetely u may need to add 1
more disk controller and place the database and transaction log file spress
acros multiple controllers. This will reduce the DISK Write contention.
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:eIuy2#YuDHA.4056@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i be shure that my server needs improvement?
> I have done some analisys and get information as
> -sometimes some questions gets a lot of cpu usage
> -sometimes i have a lot of write usage
> -sometimes i have a lot of read usage
> How much cpu usage is good to get better performance?
> What is worst : -so much Read / -so much Write / -so much CPF usage ?
> Thanks
> Leandro Loureiro dos Santos
> Vitoria - ES
>