Performance problem MDX, dependent on location in the code

Hello!

I have MDX in my cube in the tab Calculation. The same MDX perform very differently dependent on where it is located in the code.

Example:

This is in the beginning:

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods33] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]})

,FONT_FLAGS = '1';

And this is in the middle:

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods22] AS Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]})

,FONT_FLAGS = '1';

[5111 Sales of Goods22]is very slow.

[5111 Sales of Goods33] is a little bit slower than select [Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]from the hiearki.

Why is it like this?

How do I solve my performance problem?

Look forward to any solution.

Best regards,

Tina

Hello!

I give a new example (This is all that exists in my MDX):

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]}

)

,FONT_FLAGS = '1';

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[520 Subtotal ; Production] AS

Aggregate({ [Account].[AccountOGIS].[OGIS3Digit].&[T]&[5]&[52]&[520]}

* {[CostCenter].[OGIS CostCenter Id Name].&[1000]})

, FONT_FLAGS = '1';

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5112 Income Service] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5112]})

,FONT_FLAGS = '1';

[5111 Sales of Goods] is fast.

[520 Subtotal ; Production] and [5112 Income Service] is slow.

[5112 Income Service] becomes slow when it is located after [520 Subtotal ; Production].

[520 Subtotal ; Production] seems to bee slow due to it use two diffrent dimensions.

Why is [5112 Income Service] slow when it is located after [520 Subtotal ; Production]?

Best regards,

Tina

|||

Hello again!

I get no answers, but I don't give up, here comes additional information:

The MDX in the Cube (it exists only this code):

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[?5111 Sales of Goods] AS

--Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]}

{[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]}

,FONT_FLAGS = '1';

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[?5112 Income Service] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5112]})

,FONT_FLAGS = '1';

Query 1 - Very fast:
SELECT { { { [Time].[Time].[Year].&[2006], [Time].[Time].[Year].&[2007] } * { [Version].[Version].[Actual], [Version].[Version].[Budget] } } } ON COLUMNS ,

NON EMPTY { { { DESCENDANTS( [Customer].[CustomerCountry].[All Customer Country], [Customer].[CustomerCountry].[Country] ) } * { DESCENDANTS( [Customer].[CustomerGroup].[All Customer Group], [Customer].[CustomerGroup].[Group Omya] ) } * { DESCENDANTS( [Product].[ProductGroup].[All ProductGroup], [Product].[ProductGroup].[Product] ) } } } ON ROWS

FROM [Complete]

WHERE ( [Account].[AccountOGIS].[?5111 Sales of Goods], [Measures].[Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

Query 2 - Very Slow:

SELECT { { { [Time].[Time].[Year].&[2006], [Time].[Time].[Year].&[2007] } * { [Version].[Version].[Actual], [Version].[Version].[Budget] } } } ON COLUMNS ,

NON EMPTY { { { DESCENDANTS( [Customer].[CustomerCountry].[All Customer Country], [Customer].[CustomerCountry].[Country] ) } * { DESCENDANTS( [Customer].[CustomerGroup].[All Customer Group], [Customer].[CustomerGroup].[Group Omya] ) } * { DESCENDANTS( [Product].[ProductGroup].[All ProductGroup], [Product].[ProductGroup].[Product] ) } } } ON ROWS

FROM [Complete]

WHERE ( [Account].[AccountOGIS].[?5112 Income Service], [Measures].[Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

(The Queries are created from ProClarity).

If I switch place between CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods] and CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[520 Subtotal ; Production] then Query 1 becomes very slow and Query 2 becomes very fast. WHY?

Best regards,

Tina N M?rnstam

Performance of RS email delivery VS .NET code email with SMTP

Hi,
I have 2 options of implementing sending data drivent email to customers,
one is coding .NET with SMTP, the other is using RS data driven subscription
with email delivery.
My question is, comparing to .NET code by building email body using
StringBuilder, how is the performance of RS, by making HTML format of email
from Report Definition Languge, XML, I know it's compiled, but XML processing
is alwasy slow, as I understand. Thanks.
TonyWhat sort of volume are you talking about? Two things are happening:
rendering and sending the email. Rendering of the report will be what takes
the longest and will be the same regardless of whether you send it via your
app or use Reporting Services to email it. So, if you plan on emailing the
reports, my guess is the act of sending the email will not make much if any
difference as a percentage of the total time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:3C91238E-5618-4C23-9C1C-7C8BA7199AC1@.microsoft.com...
> Hi,
> I have 2 options of implementing sending data drivent email to customers,
> one is coding .NET with SMTP, the other is using RS data driven
> subscription
> with email delivery.
> My question is, comparing to .NET code by building email body using
> StringBuilder, how is the performance of RS, by making HTML format of
> email
> from Report Definition Languge, XML, I know it's compiled, but XML
> processing
> is alwasy slow, as I understand. Thanks.
> Tony|||Hi Bruce,
Thanks for your response. My question is rendering reporting from Report
Definition Languge comparing to building email body with .NET application. My
guess is XML processing time, hopfully not DOM, but XML stream, comparing to
stringbuilder inside .NET code. In your response, did you mean sending email
or rending will be the same regardless of 2 options? Sorry about can not
understand the sentence clearly.
Regards,
Tony
"Bruce L-C [MVP]" wrote:
> What sort of volume are you talking about? Two things are happening:
> rendering and sending the email. Rendering of the report will be what takes
> the longest and will be the same regardless of whether you send it via your
> app or use Reporting Services to email it. So, if you plan on emailing the
> reports, my guess is the act of sending the email will not make much if any
> difference as a percentage of the total time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:3C91238E-5618-4C23-9C1C-7C8BA7199AC1@.microsoft.com...
> > Hi,
> > I have 2 options of implementing sending data drivent email to customers,
> > one is coding .NET with SMTP, the other is using RS data driven
> > subscription
> > with email delivery.
> > My question is, comparing to .NET code by building email body using
> > StringBuilder, how is the performance of RS, by making HTML format of
> > email
> > from Report Definition Languge, XML, I know it's compiled, but XML
> > processing
> > is alwasy slow, as I understand. Thanks.
> >
> > Tony
>
>|||Are you integrating RS into your app? If that is the case then the only
difference is whether your app sends it on or Reporting Services does. I
can't tell what you are planning to do. If you are thinking of writing your
own reports in your dotnet app then that is another matter.
I think you are over analyzing this. If you needs are extremely simple then
you can do it yourself. If, however, your reporting is complex at all then
the effort to create your own is huge. RS is very fast when rendering to
HTML and CSV. If you are concerned about performance then send the report in
one of these two format. PDF and Excel is much much slower to render.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:899B34C4-46C3-472F-B283-60C51D365707@.microsoft.com...
> Hi Bruce,
> Thanks for your response. My question is rendering reporting from Report
> Definition Languge comparing to building email body with .NET application.
> My
> guess is XML processing time, hopfully not DOM, but XML stream, comparing
> to
> stringbuilder inside .NET code. In your response, did you mean sending
> email
> or rending will be the same regardless of 2 options? Sorry about can not
> understand the sentence clearly.
> Regards,
> Tony
> "Bruce L-C [MVP]" wrote:
>> What sort of volume are you talking about? Two things are happening:
>> rendering and sending the email. Rendering of the report will be what
>> takes
>> the longest and will be the same regardless of whether you send it via
>> your
>> app or use Reporting Services to email it. So, if you plan on emailing
>> the
>> reports, my guess is the act of sending the email will not make much if
>> any
>> difference as a percentage of the total time.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:3C91238E-5618-4C23-9C1C-7C8BA7199AC1@.microsoft.com...
>> > Hi,
>> > I have 2 options of implementing sending data drivent email to
>> > customers,
>> > one is coding .NET with SMTP, the other is using RS data driven
>> > subscription
>> > with email delivery.
>> > My question is, comparing to .NET code by building email body using
>> > StringBuilder, how is the performance of RS, by making HTML format of
>> > email
>> > from Report Definition Languge, XML, I know it's compiled, but XML
>> > processing
>> > is alwasy slow, as I understand. Thanks.
>> >
>> > Tony
>>|||My report will be sent with HTML format. It's not integrated into my
application. My quesiton was which option is faster at rendering email
message. I think I don't need to worry performance about HTML format, since
you mention it's fast for HTML and CVS. Thanks.
Tony
"Bruce L-C [MVP]" wrote:
> Are you integrating RS into your app? If that is the case then the only
> difference is whether your app sends it on or Reporting Services does. I
> can't tell what you are planning to do. If you are thinking of writing your
> own reports in your dotnet app then that is another matter.
> I think you are over analyzing this. If you needs are extremely simple then
> you can do it yourself. If, however, your reporting is complex at all then
> the effort to create your own is huge. RS is very fast when rendering to
> HTML and CSV. If you are concerned about performance then send the report in
> one of these two format. PDF and Excel is much much slower to render.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:899B34C4-46C3-472F-B283-60C51D365707@.microsoft.com...
> > Hi Bruce,
> > Thanks for your response. My question is rendering reporting from Report
> > Definition Languge comparing to building email body with .NET application.
> > My
> > guess is XML processing time, hopfully not DOM, but XML stream, comparing
> > to
> > stringbuilder inside .NET code. In your response, did you mean sending
> > email
> > or rending will be the same regardless of 2 options? Sorry about can not
> > understand the sentence clearly.
> >
> > Regards,
> > Tony
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What sort of volume are you talking about? Two things are happening:
> >> rendering and sending the email. Rendering of the report will be what
> >> takes
> >> the longest and will be the same regardless of whether you send it via
> >> your
> >> app or use Reporting Services to email it. So, if you plan on emailing
> >> the
> >> reports, my guess is the act of sending the email will not make much if
> >> any
> >> difference as a percentage of the total time.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:3C91238E-5618-4C23-9C1C-7C8BA7199AC1@.microsoft.com...
> >> > Hi,
> >> > I have 2 options of implementing sending data drivent email to
> >> > customers,
> >> > one is coding .NET with SMTP, the other is using RS data driven
> >> > subscription
> >> > with email delivery.
> >> > My question is, comparing to .NET code by building email body using
> >> > StringBuilder, how is the performance of RS, by making HTML format of
> >> > email
> >> > from Report Definition Languge, XML, I know it's compiled, but XML
> >> > processing
> >> > is alwasy slow, as I understand. Thanks.
> >> >
> >> > Tony
> >>
> >>
> >>
>
>

Performance of CLR

I have stored procedure in database. The procedure have near 2000 lines of
code, it implement state machine. No access to data in the procedure, only
lots of "if else" to find next state. The procedure is called for every
change of some objects, from tens of other SPs. From BOL it looks like it is
good candidate for moving it to C#. I restored database from SQL2k in SQL2k5.
Next I created C# function, which do nothing except accept same parameters
and return value. The value is constant, no any code in the C# function. Next
I comment all code in TSQL procedure, and add call of CLR procedure in the
SP. Next I measure performance of other SPs which call the SP. From my
results - it become worse, duration increased. Execution path in callers are
remain same. Why it is so? Is cost of CLR function call so high?The cost of the function call to a CLR procedure from inside the SQL Server
environment is indeed going to be higher than the cost of calling a TSQL
function/procedure. But once inside the CLR procedure computation will be
much faster.
The reason for the slowdown is that you are switching from one environment
to another with CLR while in TSQL you are staying in the same environment
across the call boundary.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:DC10BABA-32E1-4FA4-BA69-061B49F93F9E@.microsoft.com...
>I have stored procedure in database. The procedure have near 2000 lines of
> code, it implement state machine. No access to data in the procedure, only
> lots of "if else" to find next state. The procedure is called for every
> change of some objects, from tens of other SPs. From BOL it looks like it
> is
> good candidate for moving it to C#. I restored database from SQL2k in
> SQL2k5.
> Next I created C# function, which do nothing except accept same parameters
> and return value. The value is constant, no any code in the C# function.
> Next
> I comment all code in TSQL procedure, and add call of CLR procedure in the
> SP. Next I measure performance of other SPs which call the SP. From my
> results - it become worse, duration increased. Execution path in callers
> are
> remain same. Why it is so? Is cost of CLR function call so high?|||"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The cost of the function call to a CLR procedure from inside the SQL
> Server environment is indeed going to be higher than the cost of calling a
> TSQL function/procedure. But once inside the CLR procedure computation
> will be much faster.
Actually, that does not appear to be true based on my tests. I just ran
a test comparing a T-SQL UDF against an identical CLR UDF. Both do nothing
more than return an input parameter. The CLR version is approximately 27%
faster over the course of 1,000,000 iterations, on my test server... I'd be
interested in seeing some other results. Don't forget to compile the
assembly in release mode so that you get more optimized code.
The two functions are:
CREATE FUNCTION ReturnMe(@.Input INT)
RETURNS INT
AS
BEGIN
RETURN(@.Input)
END
...and...
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
{
return(Input);
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Yeah that is the same I have seen in our tests. Scalar functions blow TSQL
UDF's out of the water, see SQLCLRPERF project which is part of the demo
files of a recent performance evaluation session, it compares some string
manipulation, results when using the UDF
set statistics time on
go
print 'call CLR UDF'
select dbo.GetMemberLength(StringValue, 2) from dbo.MyStringTable
print 'call T-SQL UDF'
select dbo.GetMemberLength_TSQL(StringValue, 2) from dbo.MyStringTable
set statistics time off
Passing in 19614 rows
call CLR UDF
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 108 ms.
call T-SQL UDF
SQL Server Execution Times:
CPU time = 6650 ms, elapsed time = 15671 ms.
See http://sqldev.net/download/conf/SQLConnections-2005-11-USA/SAV303.zip
for the demo files and
http://sqldev.net/download/conf/SQLConnections-2005-11-USA/SAV303%20-%20SQL-CLR%20A%20Performance%20Evaluation.zip
for the presentation
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:unUEMeY6FHA.2176@.TK2MSFTNGP14.phx.gbl...
> "Hal Berenson" <hberenson@.predictableit.com> wrote in message
> news:ORYGWLX6FHA.808@.TK2MSFTNGP09.phx.gbl...
>> The cost of the function call to a CLR procedure from inside the SQL
>> Server environment is indeed going to be higher than the cost of calling
>> a TSQL function/procedure. But once inside the CLR procedure computation
>> will be much faster.
> Actually, that does not appear to be true based on my tests. I just
> ran a test comparing a T-SQL UDF against an identical CLR UDF. Both do
> nothing more than return an input parameter. The CLR version is
> approximately 27% faster over the course of 1,000,000 iterations, on my
> test server... I'd be interested in seeing some other results. Don't
> forget to compile the assembly in release mode so that you get more
> optimized code.
> The two functions are:
> CREATE FUNCTION ReturnMe(@.Input INT)
> RETURNS INT
> AS
> BEGIN
> RETURN(@.Input)
> END
>
> ...and...
>
> [Microsoft.SqlServer.Server.SqlFunction]
> public static SqlInt32 ReturnMe_CLR(SqlInt32 Input)
> {
> return(Input);
> }
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Just to add my experience with that. This week I wrote a performace
measurement function which executed some really easy UDAs.
(Based on a million records, summing up integer values)
-A SUM used in TSQL ran about 500 ms
-A SUM within a UDA ran 800 ms
-A Cursor ran 35000 ms !!!
Jens Suessmeyer.|||After more investigation I found the problem was caused by broken state
machine. Execution path in calling SP was changed. After fix of the problem,
I see ~10% increase in performance.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related
. The third party system we have is archaic and code is written for SQL Serv
er 6.5. It has indices for multiple fields and all the data and index are in
one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The
system created numerous table locks and one part of the system totally locks
out if a user is deleting from a table and other users are trying to access
the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we h
ave more than 1100 connections. Since its a third party tool we have limited
control over code change and they never do any support. The management is a
sking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices al
so into a file. Move the tempdb into another file and keep in a different ra
id array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almost 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Searches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.