Analysis server cube Performance through Excel 2007

Hello Everybody

I am accessing Analysis server cube -2005 through Excel 2007 for building reports. When ever i select a row or column right click and select Filter > Hide Selected Items, it will show "running query on the OLAP server" at bottom and will not respond after that.

Ran the MDX generated by Excel 2007 directly on analysis server, even then also it takes lot of time and finally it doesnt respond. I read in the forum that the sql generated by Excel 2007 is different. how can i tweak in so that cube performance improves ?

Thanks
Kiran

Hi Kiran,

Do you have old AS 2000 to compare performance? The reason I am asking this because you can check if you have same aggregation setup in AS 2005 cube. If you don't have old cube to compare try creating aggregation on AS 2005 cube. Also you can check profiler, performance counter etc.

-Ashok

sql

Analysis Server 90, Performance Counters

Hi,
I have installed SQL Server 2005. Now, in the system performance viewer
I can see some new performance counters added. But I am not able to see
any performance counter related to "Anlysis Server 90".
Is "Anlysis Server 90" is the new performance object and how to get
that in the sysperfmon?
If anybody knows about it, please let me know ASAP.
Thanks and Regards,
Pramod.I believe the performance objects all start with "MSAS 2005".|||Thanks Kevin,
Yes, all the performance counters for Analysis Server starts with MSAS
2005.
Can you tell me more about Notification Services (NS). How to access
performance counters of the Notification Services? If you can please
visit the following link,
http://msdn2.microsoft.com/en-us/library/ms172479.aspx
It shows the way to access the performance counters of NS, but I am not
able to see the same on the SysPerf Monitor.
Is there any service or component that is required to be installed to
get the same? I have installed NS from the component setup.
Thanks again Kevin for the information.
Let me know if you have any information about NS.
Regards,
Pramod|||You need to use NSCONTROL.exe to register the instance which will
install the performance counter objects. Check "NSCONTROL Register
Command" in BOL.
Kevin|||Thanks, Kevin.
I got the performance objects for NS also

Analysis Server 90, Performance Counters

Hi,
I have installed SQL Server 2005. Now, in the system performance viewer
I can see some new performance counters added. But I am not able to see
any performance counter related to "Anlysis Server 90".
Is "Anlysis Server 90" is the new performance object and how to get
that in the sysperfmon?
If anybody knows about it, please let me know ASAP.
Thanks and Regards,
Pramod.
I believe the performance objects all start with "MSAS 2005".
|||Thanks Kevin,
Yes, all the performance counters for Analysis Server starts with MSAS
2005.
Can you tell me more about Notification Services (NS). How to access
performance counters of the Notification Services? If you can please
visit the following link,
http://msdn2.microsoft.com/en-us/library/ms172479.aspx
It shows the way to access the performance counters of NS, but I am not
able to see the same on the SysPerf Monitor.
Is there any service or component that is required to be installed to
get the same? I have installed NS from the component setup.
Thanks again Kevin for the information.
Let me know if you have any information about NS.
Regards,
Pramod
|||You need to use NSCONTROL.exe to register the instance which will
install the performance counter objects. Check "NSCONTROL Register
Command" in BOL.
Kevin
|||Thanks, Kevin.
I got the performance objects for NS also

Analysis Server 90, Performance Counters

Hi,
I have installed SQL Server 2005. Now, in the system performance viewer
I can see some new performance counters added. But I am not able to see
any performance counter related to "Anlysis Server 90".
Is "Anlysis Server 90" is the new performance object and how to get
that in the sysperfmon?
If anybody knows about it, please let me know ASAP.
Thanks and Regards,
Pramod.I believe the performance objects all start with "MSAS 2005".|||Thanks Kevin,
Yes, all the performance counters for Analysis Server starts with MSAS
2005.
Can you tell me more about Notification Services (NS). How to access
performance counters of the Notification Services? If you can please
visit the following link,
http://msdn2.microsoft.com/en-us/library/ms172479.aspx
It shows the way to access the performance counters of NS, but I am not
able to see the same on the SysPerf Monitor.
Is there any service or component that is required to be installed to
get the same? I have installed NS from the component setup.
Thanks again Kevin for the information.
Let me know if you have any information about NS.
Regards,
Pramod|||You need to use NSCONTROL.exe to register the instance which will
install the performance counter objects. Check "NSCONTROL Register
Command" in BOL.
Kevin|||Thanks, Kevin.
I got the performance objects for NS also :)

Analysis Server 2000 performance Benchmark on FoodMart 2000

I'd like to have a performance benchmark on analysis services. I deployed a solution based on analysis services and I'd like to understand if performances are in line with other solutions. I 'm afraid that some hardware configuration is not properly set. So I ask to you if you can tell me how much time takes a report run on FoodMart 2000 , cube Warehouse and Sales.

I built the following report, and the MDX code has been automatically generated by our Client Tool. Even if is not the best MDX coding, I just need to unserstand if there are some differences in response time.

Thanks in advance

WITH measures.[__Customers] as '"[__Customers]"',visible=0 Member measures.[__Customers_selection] as'"{ AddCalculatedMembers({[Customers].[Country].AllMembers}) }"' ,visible=0Set [__Customers_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Customers_selection])))) }'SET [__Customers_RootMembers_Fast] As'{[__Customers_main]}' SET [__Customers] As'[__Customers_RootMembers_Fast]' measures.[__Product] as '"[__Product]"',visible=0 Member measures.[__Product_selection] as'"{ AddCalculatedMembers({[Product].[Product Subcategory].AllMembers}) }"' ,visible=0Set [__Product_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Product_selection])))) }'SET [__Product_RootMembers_Fast] As'{[__Product_main]}' SET [__Product] As'[__Product_RootMembers_Fast]' measures.[__Store] as '"[__Store]"',visible=0 Member measures.[__Store_selection] as'"{ AddCalculatedMembers({[Store].[Store State].AllMembers}) }"' ,visible=0Set [__Store_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Store_selection])))) }'SET [__Store_RootMembers_Fast] As'{[__Store_main]}' SET [__Store] As'[__Store_RootMembers_Fast]' SET [_Rows] as'Crossjoin({Crossjoin({[__Customers]}, {[__Product]})}, {[__Store]})' measures.[__Time] as '"[__Time]"',visible=0 SET [__Time_RootMembers_Fast] As'[Time].Levels(0).AllMembers' SET [__Time] As'[__Time_RootMembers_Fast]' measures.[__Measures] as '"[__Measures]"',visible=0 Member measures.[__Measures_selection] as'"{ [Measures].[Warehouse Cost], [Measures].[Warehouse Sales], [Measures].[Units Shipped], [Measures].[Units Ordered], [Measures].[Warehouse Profit], [Measures].[Unit Sales] }"' ,visible=0Set [__Measures_main] as'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Measures_selection])))) }'Set [__Measures_RootMembers_Smart] as '{ Filter([__Measures_main], Count(Intersect(Ascendants([Measures].CurrentMember),[__Measures_main])) = 1) }' SET [__Measures] As'[__Measures_RootMembers_Smart]' SET [_Columns] as'Crossjoin({[__Time]}, {[__Measures]})' SELECT [_Columns]DIMENSION PROPERTIES Parent_Unique_Name on columns, [_Rows]DIMENSION PROPERTIES Parent_Unique_Name on rows FROM[Warehouse and Sales] CELL PROPERTIES back_color,fore_color,format_string,font_name,font_size,font_flags,cell_evaluation_list,value

Well, I haven't the time to tell you why, but the MDX above will not run in either of my MDX tools|||

I think it is due to code formatting when I attached the code. I tried to copy and paste the code and it is true it doesn't work.

If you can try again......

Thanks in advance

Code Snippet

WITH
Member measures.[__Customers] as '"[__Customers]"',visible=0
Member measures.[__Customers_selection] as '"{ AddCalculatedMembers({[Customers].[Country].AllMembers}) }"' ,visible=0 Set [__Customers_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Customers_selection])))) }' SET [__Customers_RootMembers_Fast] As '{[__Customers_main]}'
SET [__Customers] As '[__Customers_RootMembers_Fast]'
Member measures.[__Product] as '"[__Product]"',visible=0
Member measures.[__Product_selection] as '"{ AddCalculatedMembers({[Product].[Product Subcategory].AllMembers}) }"' ,visible=0 Set [__Product_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Product_selection])))) }' SET [__Product_RootMembers_Fast] As '{[__Product_main]}'
SET [__Product] As '[__Product_RootMembers_Fast]'
Member measures.[__Store] as '"[__Store]"',visible=0
Member measures.[__Store_selection] as '"{ AddCalculatedMembers({[Store].[Store State].AllMembers}) }"' ,visible=0 Set [__Store_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Store_selection])))) }' SET [__Store_RootMembers_Fast] As '{[__Store_main]}'
SET [__Store] As '[__Store_RootMembers_Fast]'
SET [_Rows] as'Crossjoin({Crossjoin({[__Customers]}, {[__Product]})}, {[__Store]})'
Member measures.[__Time] as '"[__Time]"',visible=0
SET [__Time_RootMembers_Fast] As '[Time].Levels(0).AllMembers'
SET [__Time] As '[__Time_RootMembers_Fast]'
Member measures.[__Measures] as '"[__Measures]"',visible=0
Member measures.[__Measures_selection] as '"{ [Measures].[Warehouse Cost], [Measures].[Warehouse Sales], [Measures].[Units Shipped], [Measures].[Units Ordered], [Measures].[Warehouse Profit], [Measures].[Unit Sales] }"' ,visible=0 Set
[__Measures_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Measures_selection])))) }' Set [__Measures_RootMembers_Smart] as
'{ Filter([__Measures_main], Count(Intersect(Ascendants([Measures].CurrentMember),[__Measures_main])) = 1) }'
SET [__Measures] As '[__Measures_RootMembers_Smart]'
SET [_Columns] as'Crossjoin({[__Time]}, {[__Measures]})'
SELECT [_Columns] DIMENSION PROPERTIES Parent_Unique_Name on columns, [_Rows] DIMENSION PROPERTIES Parent_Unique_Name on rows
FROM [Warehouse and Sales]
CELL PROPERTIES back_color,fore_color,format_string,font_name,font_size,font_flags,cell_evaluation_list,value

|||

Seems to take 2-4 seconds

2 on the server

4 remoting from my machine using Proclarity

|||

Against AS2000 it takes more or less 10 seconds using MSX sample application on a client machine.

If I run it on the server machine it seems that nothing change (1 second difference)

It is more than the double.

Now I have to understand why?

We have installed the default configuration of AS2000 on the server.

|||

On AS2005 it takes:

Cold cache: 4 seconds in SQL Server Management Studio, but in the server it takes only 0.812 seconds. Warm cache: 4 seconds in SQL Server Management Studio, but in the server it takes only 0.625 seconds.|||

Thanks for your intervention.

I have a question, sorry but I'm not so smart in performance tuning. This is not my daily job.

How Do you trace the time that the query takes on the server when you run it from the client application? Do you have a tool or Do you use the standard performance counter. If so which is the counter that you trace?

|||

Well, in AS2005 is very easy! Just turn on the Profiler. However, it does not work in AS2000.

In AS2000, we have a add-in to Excel that lets you time the duration of the Pivot Table refresh.

There was a mdx_applet.xls file that allow you to run queries in MDX, from Excel. There you should implement the timing feature.

Analyse Memory Leak from sqlsrv.exe

Hi,
Did somebody know what is the best practice to
detect Memory/CPU Leaks on SQL 2000/WIN 2000 ?
On Performance Manager I can see that sqlsrv.exe use
lot of Memory/CPU, now I would know wich DB, Connection or
query generate this leak.
PS: if I have more Instance on a Server, how can I se wich Instance
corrisponde with sqlsrv.exe in Task Manager?
Many Thanks for all input ...Try this:
http://www.sophos.com/virusinfo/ana...2tilebotav.html|||SQL Server will use as much RAM as you configure it for. That doesn't
indicate a memory leak. On a server that runs other applications you
may want to configure something less than the maximum RAM available to
SQL Server. On a dedicated server however, you should let SQL Server
manage memory allocation.
David Portas
SQL Server MVP
--

Analyse Memory Leak from sqlsrv.exe

Hi,
Did somebody know what is the best practice to
detect Memory/CPU Leaks on SQL 2000/WIN 2000 ?
On Performance Manager I can see that sqlsrv.exe use
lot of Memory/CPU, now I would know wich DB, Connection or
query generate this leak.
PS: if I have more Instance on a Server, how can I se wich Instance
corrisponde with sqlsrv.exe in Task Manager?
Many Thanks for all input ...
Try this:
http://www.sophos.com/virusinfo/anal...tilebotav.html
|||SQL Server will use as much RAM as you configure it for. That doesn't
indicate a memory leak. On a server that runs other applications you
may want to configure something less than the maximum RAM available to
SQL Server. On a dedicated server however, you should let SQL Server
manage memory allocation.
David Portas
SQL Server MVP

Analyse Memory Leak from sqlsrv.exe

Hi,
Did somebody know what is the best practice to
detect Memory/CPU Leaks on SQL 2000/WIN 2000 ?
On Performance Manager I can see that sqlsrv.exe use
lot of Memory/CPU, now I would know wich DB, Connection or
query generate this leak.
PS: if I have more Instance on a Server, how can I se wich Instance
corrisponde with sqlsrv.exe in Task Manager?
Many Thanks for all input ...Try this:
http://www.sophos.com/virusinfo/analyses/w32tilebotav.html|||SQL Server will use as much RAM as you configure it for. That doesn't
indicate a memory leak. On a server that runs other applications you
may want to configure something less than the maximum RAM available to
SQL Server. On a dedicated server however, you should let SQL Server
manage memory allocation.
--
David Portas
SQL Server MVP
--

An Urgent help required on Performance tuning

Dear All,
We use SQL Server 2000. Inorder to increase the performance archived about
2.5 million records.
However after the archival, the performance is found to be reduced as you
can see in the below comparison details. This downsizing of almost 2.5
millions records has been followed by an "update statistics" in all table
from where, records were removed.
Can anybody give us hints : what could be the the potntial reason for
non-improvement of performance.
What all tunings are yet to be done?
( We are relatively new in this area)
Performance Performance
After Before
- To reset working status for a plant 57s
40s
- To get the menu of pre-release:
delay before having possibility to chose
plant and report for pre release 2 min 4s
2min 41s
- To select a plant 2 min 12s 2min 5s
- To pre-release the P&L2 of a plant 4 min 4s
5min 22s
Thanks and Regards
Sathian"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:eaakvo$nc6$1@.news4.fe.internet.bosch.com...
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
Have you done a backup and a trancate log on that db?|||Have you rebuilt your indexes?
Phil
"Sathian" wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian
>
>|||It is not always a big surprise if performance does not improve when
old data is archived. If the tables were properly designed, the
indexes well chosen, and the queries done right, then databases can
often grow quite large without degrading performance, so it seems
reasonable that shrinking them back won't improve performance.
For performance to get worse when data is archived is unexpected.
Perhaps there was some other change at the same time that had a
significant impact.
One thing worth trying: don't just update statistics, rebuild the
indexes. See DBCC DBREINDEX in the documentation.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Dear All,
>We use SQL Server 2000. Inorder to increase the performance archived about
>2.5 million records.
>However after the archival, the performance is found to be reduced as you
>can see in the below comparison details. This downsizing of almost 2.5
>millions records has been followed by an "update statistics" in all table
>from where, records were removed.
>
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
>What all tunings are yet to be done?
>
>( We are relatively new in this area)
>
>
>Performance Performance
>After Before
> - To reset working status for a plant 57s
>40s
> - To get the menu of pre-release:
>delay before having possibility to chose
>plant and report for pre release 2 min 4s
>2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
>5min 22s
>Thanks and Regards
>Sathian
>|||As others have indicated, you need to examine your indexes. What
percentage of your data did the archiving process represent?
Sathian wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian|||On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
Can you show a little SQL that's running slowly?
It may be the new data is not well-distributed, so after deleting the
old data, the indexes look even worse to the optimizer, and even worse
plans are chosen. Stranger things have happened.
J.

An Urgent help required on Performance tuning

Dear All,
We use SQL Server 2000. Inorder to increase the performance archived about
2.5 million records.
However after the archival, the performance is found to be reduced as you
can see in the below comparison details. This downsizing of almost 2.5
millions records has been followed by an "update statistics" in all table
from where, records were removed.
Can anybody give us hints : what could be the the potntial reason for
non-improvement of performance.
What all tunings are yet to be done?
( We are relatively new in this area)
Performance Performance
After Before
- To reset working status for a plant 57s
40s
- To get the menu of pre-release:
delay before having possibility to chose
plant and report for pre release 2 min 4s
2min 41s
- To select a plant 2 min 12s 2min 5s
- To pre-release the P&L2 of a plant 4 min 4s
5min 22s
Thanks and Regards
Sathian"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:eaakvo$nc6$1@.news4.fe.internet.bosch.com...
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
Have you done a backup and a trancate log on that db?|||Have you rebuilt your indexes?
Phil
"Sathian" wrote:

> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian
>
>|||It is not always a big surprise if performance does not improve when
old data is archived. If the tables were properly designed, the
indexes well chosen, and the queries done right, then databases can
often grow quite large without degrading performance, so it seems
reasonable that shrinking them back won't improve performance.
For performance to get worse when data is archived is unexpected.
Perhaps there was some other change at the same time that had a
significant impact.
One thing worth trying: don't just update statistics, rebuild the
indexes. See DBCC DBREINDEX in the documentation.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:

>Dear All,
>We use SQL Server 2000. Inorder to increase the performance archived about
>2.5 million records.
>However after the archival, the performance is found to be reduced as you
>can see in the below comparison details. This downsizing of almost 2.5
>millions records has been followed by an "update statistics" in all table
>from where, records were removed.
>
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
>What all tunings are yet to be done?
>
>( We are relatively new in this area)
>
>
>Performance Performance
>After Before
> - To reset working status for a plant 57s
>40s
> - To get the menu of pre-release:
>delay before having possibility to chose
>plant and report for pre release 2 min 4s
>2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
>5min 22s
>Thanks and Regards
>Sathian
>|||As others have indicated, you need to examine your indexes. What
percentage of your data did the archiving process represent?
Sathian wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian|||On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
Can you show a little SQL that's running slowly?
It may be the new data is not well-distributed, so after deleting the
old data, the indexes look even worse to the optimizer, and even worse
plans are chosen. Stranger things have happened.
J.sql

amount of transactions in a period of time on sql server 2000

Is there a native tool (profile,trace,performance) feature I can use to determine the amount of transactions that occur throughout the day? Or is there a system table that keeps track of this(would be preferable .. less strain on the system)?

I assume figuring out the transaction in a certain period will enable me to calculate the busiest periods...I need to know the busiest period of the day...how do I do this without putting an additional strain on the server (can I use a different machine other than the server to save a trace) ...I need to determine strain on (processor,memory, and disk).

I also need to get a count on the largest number of users (running transactions) on the server simultaneously.

Any help/advice would be deeply appreciated

You need to run profiler for this purpose... there is no system table as such which stores all the transactions... What u can do is.. run profiler and store it as table and query the table as u want... Be sure what all are the data u need to capture... Profiler can cause performance degradation.... Capture only the required data by selecting proper column filter and event

Madhu

|||

Running PROFILER on full day is not a good thing if you still have performance problems, lately.

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm - automating such trace.

Refer:
select count(*) as TotalConnections from master..sysprocesses
go

select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from master..sysprocesses
group by substring(db_name(dbid),1,30)

ambiguity performance problem

The first query execution time less than 1 second
But the second query takes around one minute

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST in (5,-999)) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1

Note 1 : value -999 deos not exist in the table field ACC_STATUS_LAST
Note 2 : value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 3 : the number of rows in each table around 15000000

Hi,

didi you have a look at the execution plan ? The execution plan can be seen in QA (using SQL 2k) or Query Pane (using SQL2k5) graphically or by using the statement SET SHOWPLAN ON before isseing the statement.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Yeah, and then post it. I would love to see how an equality query is beat out by an OR query. This looks very much like it is probably an optimizer issue, unless the plan shows something obvious...|||

thank you for this notice ,

because there is a big difference between the access plan for both queries

and I don't now why this happen ,

Alternative to Merge Replication?

Is there a simpler alternative to merge replication? I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment.What version of SQL server are you using? Look into transactional and peer to peer replication. FYI, these will require a primary key field to be set on each table.
|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.
|||Sorry, meant to post this earlier...

Info about the different types of replication, http://msdn2.microsoft.com/en-us/library/ms152531.aspx
|||

there are many articles and whitepapers about replication deployments on the net, maybe we can ease you if you describe your scenario and deployment concerns. i.e. how many subscribers are we talking about?

as far as GUIDs, if you specify correctly column lists for all your insert statements, you shouldn't have any application breaks, it should be seamless.

|||

My scenerio is:

Networked offices with 5-30 internal users, modifying a central DB in SQL Server 2005

1-6 Field workers who need to modify a subset of the tables in the field (disconnected) and then connect to the network and upload their changes.

Low (though possible) change of conflicting changes between syncs, as field people tend to work in different areas. Field device might be away from the central DB for 1-10 days.

Last-one-wins would be OK for conflicting changes. I've been considering a home-grown system that inserts new records to the central DB, when executes an Audit trail for the changes.

I know it sound like Merge replication. I'm not sure why I'm so afraid of actually using Merge replicaiton. It seems so complex, and heavy, and trigger happy, and if something goes wrong it looks like a nightmare to troubleshoot.

|||

According to http://msdn2.microsoft.com/en-us/library/ms151718.aspx, Updatable Subscriptions for Transactional Replication DOES require a GUID. "Replication adds the msrepl_tran_version column to published tables for tracking purposes"

And Peer-to-Peer seems to be for Server-2-server, not for Server-2-DisconnectedClient.

|||

Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes.

If you can be patient, you can also wait for OCS to come out early next year with the next release of Visual Studio, you can read about it on steve lasker's blog, search for OCS http://blogs.msdn.com/stevelasker/default.aspx.

Alternative to Merge Replication?

Is there a simpler alternative to merge replication? I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment.What version of SQL server are you using? Look into transactional and peer to peer replication. FYI, these will require a primary key field to be set on each table.|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.|||Sorry, meant to post this earlier...

Info about the different types of replication, http://msdn2.microsoft.com/en-us/library/ms152531.aspx|||

there are many articles and whitepapers about replication deployments on the net, maybe we can ease you if you describe your scenario and deployment concerns. i.e. how many subscribers are we talking about?

as far as GUIDs, if you specify correctly column lists for all your insert statements, you shouldn't have any application breaks, it should be seamless.

|||

My scenerio is:

Networked offices with 5-30 internal users, modifying a central DB in SQL Server 2005

1-6 Field workers who need to modify a subset of the tables in the field (disconnected) and then connect to the network and upload their changes.

Low (though possible) change of conflicting changes between syncs, as field people tend to work in different areas. Field device might be away from the central DB for 1-10 days.

Last-one-wins would be OK for conflicting changes. I've been considering a home-grown system that inserts new records to the central DB, when executes an Audit trail for the changes.

I know it sound like Merge replication. I'm not sure why I'm so afraid of actually using Merge replicaiton. It seems so complex, and heavy, and trigger happy, and if something goes wrong it looks like a nightmare to troubleshoot.

|||

According to http://msdn2.microsoft.com/en-us/library/ms151718.aspx, Updatable Subscriptions for Transactional Replication DOES require a GUID. "Replication adds the msrepl_tran_version column to published tables for tracking purposes"

And Peer-to-Peer seems to be for Server-2-server, not for Server-2-DisconnectedClient.

|||

Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes.

If you can be patient, you can also wait for OCS to come out early next year with the next release of Visual Studio, you can read about it on steve lasker's blog, search for OCS http://blogs.msdn.com/stevelasker/default.aspx.