Profiler / sql trace

There are several ways to capture/view sql activities. Can
you tell me what the best way is ?
Run Profiler from a client machine or
Use sql trace procs from a client machine using QA or
Run sql trace procs at the server (which I've seen MS PSS
has always used)
thanksFor a quick look at a server I'll use Profiler from my PC and if I want to
keep it I'll save to a file (which gives me the ability to save to a table
later if I want to do more analysis) For longer traces I'll run a server
side trace on the server and generally import the resulting files into a
table for analysis. I tend to use a rollover file size of 50 MB to keep them
manageable and generally define a stop time or set up a job to stop it at a
future date.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dba" <Dba@.Dba1.Com> wrote in message
news:021001c3d61e$e5e79bf0$a401280a@.phx.gbl...
> There are several ways to capture/view sql activities. Can
> you tell me what the best way is ?
> Run Profiler from a client machine or
> Use sql trace procs from a client machine using QA or
> Run sql trace procs at the server (which I've seen MS PSS
> has always used)
> thanks|||Hello,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Based on my experience, this kind of problem does not have an easy and
clear answer. It is hard for me to tell you which the best way is to
capture/view sql activities; what a good method is for you may not be a
good method for other people. The way in which we choose to monitor SQL
Server depends on specific and particular concerns. I can help you find
the most appropriate way for you to monitor SQL server based on your needs,
but, please understand that there is no "best way" to do this.
When people use stored procedures to monitor SQL Server, the information we
are able to get is limited. For example:
Sp_who stored procedure provides information about current Microsoft SQL
Server users and processes.
sp_lock stored procedure reports information about locks.
SQL Server Profiler (SQL Trace) is a graphical tool that allows system
administrators and database developers to monitor engine events on
computers running Microsoft SQL Server. Using SQL Server Profiler on the
client machine to monitor SQL Server will increase network traffic.
Monitoring on the server will increase the workload of the server machine.
For more information regarding this issue, please refer to the following
article.
325263 Support WebCast: SQL Server 2000 Profiler: What's New and How to
http://support.microsoft.com/?id=325263
I hope the above explanation is clear. Please let us know if you need
further assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
I'm very familiar about sp_lock/sp_who. My main confusion
is that MS PSS strongly is against running Profiler at the
client side everytime I've talked/worked with them. If the
Proiler is so bad, why MS ship it at all ! As I've said
they have always run sql trace procs at our sql server
box. Any comments ?
thanks
long
>--Original Message--
>Hello,
>My name is Michael and I would like to thank you for
using Microsoft
>newsgroup.
>Based on my experience, this kind of problem does not
have an easy and
>clear answer. It is hard for me to tell you which the
best way is to
>capture/view sql activities; what a good method is for
you may not be a
>good method for other people. The way in which we choose
to monitor SQL
>Server depends on specific and particular concerns. I
can help you find
>the most appropriate way for you to monitor SQL server
based on your needs,
>but, please understand that there is no "best way" to do
this.
>When people use stored procedures to monitor SQL Server,
the information we
>are able to get is limited. For example:
>Sp_who stored procedure provides information about
current Microsoft SQL
>Server users and processes.
>sp_lock stored procedure reports information about locks.
>SQL Server Profiler (SQL Trace) is a graphical tool that
allows system
>administrators and database developers to monitor engine
events on
>computers running Microsoft SQL Server. Using SQL Server
Profiler on the
>client machine to monitor SQL Server will increase
network traffic.
>Monitoring on the server will increase the workload of
the server machine.
>For more information regarding this issue, please refer
to the following
>article.
>325263 Support WebCast: SQL Server 2000 Profiler: What's
New and How to
>http://support.microsoft.com/?id=325263
>I hope the above explanation is clear. Please let us know
if you need
>further assistance on this issue.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Long,
Thanks for your feedback. Generally, the way we choose to monitor SQL
Server depends on the specific objective. Based on the particular
situation, our engineers will choose the most appropriate method. As for
this issue, can you please give me a sample to help describe the specific
objective? That way, we can help you analyze and determine which the most
appropriate way is to monitor SQL Server.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Long,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup. My name is
Billy and it's my pleasure to assist you with this issue.
To start with, as we known, Microsoft sometimes recommends our customers to perform the
best practices for their benefits. However, we never prohibit customers from applying their
easier way to do anything. It all depends on customer's specific issues and detailed
scenarios.
Based on my experience, the main reason why you are strongly recommended to run the
Profiler at the server side is that it guarantees you can trace all the information.
In the GUI trace setup, you can click the checkbox for: "server processes sql server trace
data"
The stored procedures to create/exec profiler traces will guarantee events will not be
dropped unless you use: sp_create_trace @.options = 1 which instead of a file creates a
rowset.
Server side trace files guarantee events not to be dropped, however, result set (or rowset
cannot guarantee this. On the client side, you cannot ensure the trace information is
complete as SQL Profiler gets the trace as a result set to the client.
Another consideration is the trace performance. On the client side, the trace information is
received as a result set, in which case if there is a large volume of trace information, the
performance will definitely be poorer than tracing on the server side. That's why we
recommend you run the Profiler on the server side.
However, if trace information is complete and the performance is also ok on the client side. I
fully understand that running the Profiler is convenient and also efficient for you in some
situations (such as the server is not on hand). Do you agree with me Long?
If there is anything more we can still do to assist you, please don't hesitate to let us know.
We are here to be of assistance!
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Profiler - just view Writes

Hello,
Is there any way to use profiler and just show the 'Writes' it does.
I do not want to display anything else for a specific DB.
Thanks,
TmuldSet the filter for Writes > 0
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1188592028.966774.60190@.z24g2000prh.googlegroups.com...
> Hello,
> Is there any way to use profiler and just show the 'Writes' it does.
> I do not want to display anything else for a specific DB.
> Thanks,
> Tmuld
>

ProClarity (6.2) Hanging when Adding Grand Totals

I have a view set up that has approx 40 rows and 15 columns. Prior to applying the view - i have filtered the grid to remove empty rows and columns. The data comes back very quickly. However - when I add grand totals to the columns and rows and apply - the application says "Running Query" but never comes back. Is there an option that I can set?

Sorry I don't have a quick answer for this.

I opened up Proclarity on my desktop and connected to AdventureWorks and followed steps similar to what you've desribed. The Grand Totals are calculated in the MDX and not by Proclarity itself. Here is the MDX Proclarity generated for this:

Code Snippet

WITH
MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS
'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Date].[Calendar].[All Periods].CHILDREN }, [Date].[Calendar] ) } }, { [Date].[Calendar].[All Periods].CHILDREN } ) )',
SOLVE_ORDER = 1000
MEMBER [Product].[Product Categories].[All Products].[ Grand Total] AS
'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Product].[Product Categories].[All Products].CHILDREN }, [Product].[Product Categories] ) } }, { [Product].[Product Categories].[All Products].CHILDREN } ) )',
SOLVE_ORDER = 1000
SELECT
NON EMPTY { { [Date].[Calendar].[All Periods].CHILDREN }, ( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON COLUMNS ,
NON EMPTY { { [Product].[Product Categories].[All Products].CHILDREN }, ( [Product].[Product Categories].[All Products].[ Grand Total] ) } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

So, I think you need to first determine if this is a Proclarity issue or if this is an SSAS issue. Open an MDX query window in SQL Server Management Studio and run this query. Does it behave just as poorly in the query window as it does in Proclarity? If it does, then you can focus on SSAS (kinda).

I suspect the problem is in how the MDX is attempting to assemble the Grand Totals. In the example above, you could get the same values back with a much simpler expression.

So, what options do you have? Well, always make sure you are on the latest service pack of SSAS to insure you get the best performance possible. If you are publishing these results as a "static" view in a Proclarity workbook, you could rewrite the query for better performance. Other than that, I'm not sure what else you can really do with this.

B.

|||

A colleague of mine contacted ProClarity and they said the Grand Totals display had known bugs.

I know its not the same issue, but presumably its related.

Its to do with how aggregations are handled.

processing time issue

Hi all,

I have a quick question regarding the processing time.

I created view in db for the count purposes with huge fact table. ( I'm using dimension table which

joins the 1 fact and 1 dimension to count the key for the fast data retrieval)

If I write the query for the count in management studio and it gives a results within 5 sec.

But, if I add this view in DSV and process the cubes than it takes around 50 minutes.

This causes due to the Fact table? or what else?

I don't know what to check and please give me some comments.

Thanks in advance.

Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||

Hi Darren,

Thanks for your good tips.

As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.

So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.

Please give me some comments.

Thanks.

|||

There would be a couple of possible approaches to improving the processing performance.

One would be to partition the fact table so that you only process recent records and not the full 153 million.

Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.

Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.

Processing AS 2005 dimension with Oracle data source

Hi everyone -

I have a problem with an AS 2005 Data Source View created with an Oracle 10g data source. The view creates fine, and the dimension also creates with no problems. I click "explore data" from the right-click menu on the dimension's source table and the table data is returned with no problem. However, when I process the dimension, I get an immediate error "Referenced account is locked out...". The Impersonation Information is the Oracle userid/password. I am not locked out of the Oracle database, so I can't figure this one out. Have found nothing about this error during my searches. Any help would be appreciated.

Thanks

In the Impersonation Information , pick 'Use the service account'.|||Thanks for the help. I always used "Use the service account" for SQL Server data sources, but figured it would need my Oracle login to connect to an Oracle source. How the service account knows my Oracle login is a mystery, but as long as it works, I won't complain....

Process Details/Last TSQL command batch for SPID

Is there a way to view the "Process Details/Last TSQL command batch" for a
given SPID from Query Analyser? In EM if you go to > Managment > Current
Activity > Process Info > and then double click on a SPID, you can view the
Process Details/Last TSQL command batch.
From QA I would like to be able see the same info by running a SPROC like
"sp_SPID_Details(51)". I know that SPROC doesn't exist, but is there
something similar?eHi
You could try DBCC INPUTBUFFER to get some information back. See Books
online for more.
John
"Dan" wrote:
> Is there a way to view the "Process Details/Last TSQL command batch" for a
> given SPID from Query Analyser? In EM if you go to > Managment > Current
> Activity > Process Info > and then double click on a SPID, you can view the
> Process Details/Last TSQL command batch.
> From QA I would like to be able see the same info by running a SPROC like
> "sp_SPID_Details(51)". I know that SPROC doesn't exist, but is there
> something similar?