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

Hello,
What would be the best place to run profiler, on the production Server or on
another client Machine?
Thanks,QA
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"OA" <omrana@.verizon.net> wrote in message
news:%23GQ7oPukIHA.1052@.TK2MSFTNGP05.phx.gbl...
> Hello,
> What would be the best place to run profiler, on the production Server or
> on another client Machine?
>
> Thanks,
>sql

Professional edition vs. SQL Server

Hi
i am using the professional edition (since i'm running it on a windows 2000 pro machine), but i wanted to know what performance gains there are between the pro. edition vs. the real sql server that would be run on a windows 2000 advanced server machine
currently i'm trying to write/query an enormous amount of data. the bottleneck is now the database, but before i go through the trouble of rebuilding a maching with 2000 server, i wanted to know what the "gains" were and if it would even be worth the time to do this
Thanks
mikeOOPS... i meant PERSONAL and not professional edition.|||Personal Edition has the same performance delimiter as MSDE, i.e. 25
concurrent users is really pushing it. It has no limit on the database size
though, but you need a full SQL Server license to actually be allowed to use
Personal Edition on a separate machine. You can't use personal Edition
separately. I.e. what you are doing probably violates the licensing
agreement.
--
Jacco Schalkwijk
SQL Server MVP
"mike" <anonymous@.discussions.microsoft.com> wrote in message
news:23137F31-435A-4FF0-9AAB-9ACC5190E48B@.microsoft.com...
> OOPS... i meant PERSONAL and not professional edition.

Proclarity

I log into a terminal services machine using domain\userid. When I try to connect to Proclarity it prompts me to log in again. I re-enter domain\userid and Proclarity connects to the AS cubes.

Is there an authentication problem with terminal services and the Proclarity client?

If you are using the professional, windows, client you should be able to only enter the server name, when promted at logon. If you use webaccess with a browser or connect with the windows client by using http, you can be promted for login information. Probably you have a webserver that uses basic authentication.

I use TS with ProClarity Professional(installed on the server) daily without any login requirements.

Regards

Thomas Ivarsson

|||It think it can also depend if you have proclarity and AS on two machines, I'm required to logon to both proclarity and the AS cubes when using the professional product, although this is the web prefessional product.|||

Everyone is on the right path here. ProClarity has three different "client" tools:

ProClarity Standard - Thin client requiring no client install accessed through the ProClarity Analytics Server (PAS) URL

ProClarity Web Professional - COM components required on client. User must access PAS to retrieve a Briefing Book. After a Briefing Book is retrieved from PAS this client operates exactly like ProClarity Desktop Professional.

ProClarity Desktop Professional - Standard "Setup.exe" install on client. Can open Desktop Professional and access a cube directly withouth having a PAS server involved.

If you are using ProClarity Standard, then you do not connect directly to an AS server. The connection to the AS server is made by the PAS server. When you execute an MDX statement, the PAS server sends the MDX to the server, renders the resulting view and then passes back DHTML to the client. In this case, as was correctly pointed out, if the PAS server and AS server are on different servers then you will have to use either Basic Authentication or implement Kerberos to allow the PAS server to delegate user credentials to the AS server.

If you are using either Web Professional or Desktop Professional, then you are connecting from the client directly to the AS server and there should be no need to do anything special.

HTH,

Steve

Processor affinity?

Hi there. This might be a dumb question, so please excuse me if it is :-) I recently purchased an AMD Athlon x2 machine. I'm running SQL Server 2005 (this is a development machine).

I'm rapidly learning that the second processor in this machine is pretty much completely unused. Is there a way in setup or in a config file or something for SQL Server 2005 to tell it to use the second processor?

Thanks in advance...

-Eric HarmonThis is for SQL 2000, but anyway: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_config_5lfd.asp

Make sure you're not running SQL 2005 Express, it only supports one processor...

process analysis server objects from web via ASP.NET + C#

Hi!

We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine.

Another machine is our web server (IIS, ASPX) which displays cube

content and allows drill down to several levels. Everything works fine

and we are looking forward to optimizing the whole data management

process. At the moment we try to develop DTS packeges (SQL 2005).

Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page?

TIA and kind regards from Vienna

JohnnyR

You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies"). If IIS impersonates the current user, then AMO code from the administration aspx page would also connect to AS2005 as that user. The user needs read permission for the Database and read+process permissions for the Cube.

Sample code:

using Microsoft.AnalysisServices;

...

Server server = new Server();
server.Connect("localhost");
try
{
server.Databases["the ID of the database"].Cubes["the ID of the cube"].Process();
}
finally
{
server.Disconnect();
}

You can also get processing notifications with AMO, to display progress on the administration page; more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1

Adrian Dumitrascu

|||

Hi,

I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.

Any help would be highly appreciated.

Thanks in advance.

|||

The control used in the browser is the Office Web Components 11.0 PivotTable control.

You can use this control in your asp.net app by referencing this control in the COM tab.

As to how to use it.. documentation is sparse, so I'd just do a search on the web.

Alternatively, you could host thepivot table in an Excel spread sheet and publish that as a web page... use Excel 2007 for the best experience... again, do a search on Excel services.

Otherwise, there are true .NEt controls, such as those produced by RadarSoft

process analysis server objects from web via ASP.NET + C#

Hi!

We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine.

Another machine is our web server (IIS, ASPX) which displays cube

content and allows drill down to several levels. Everything works fine

and we are looking forward to optimizing the whole data management

process. At the moment we try to develop DTS packeges (SQL 2005).

Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page?

TIA and kind regards from Vienna

JohnnyR

You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies"). If IIS impersonates the current user, then AMO code from the administration aspx page would also connect to AS2005 as that user. The user needs read permission for the Database and read+process permissions for the Cube.

Sample code:

using Microsoft.AnalysisServices;

...

Server server = new Server();
server.Connect("localhost");
try
{
server.Databases["the ID of the database"].Cubes["the ID of the cube"].Process();
}
finally
{
server.Disconnect();
}

You can also get processing notifications with AMO, to display progress on the administration page; more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1

Adrian Dumitrascu

|||

Hi,

I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.

Any help would be highly appreciated.

Thanks in advance.

|||

The control used in the browser is the Office Web Components 11.0 PivotTable control.

You can use this control in your asp.net app by referencing this control in the COM tab.

As to how to use it.. documentation is sparse, so I'd just do a search on the web.

Alternatively, you could host thepivot table in an Excel spread sheet and publish that as a web page... use Excel 2007 for the best experience... again, do a search on Excel services.

Otherwise, there are true .NEt controls, such as those produced by RadarSoft

Procedure takes more I/O on machine than on other

Hi,
I have a procedure for which I get different phsical_io reading from
sysprocesses on two different servers.
And this difference is not twice or thrice, but it is 10 times more I/O on
other machine.
What could be the possible cause?
I have verified both have exactly same tables design and server settings.
Thanks
Pushkarwhat about data volume?
"Pushkar" wrote:

> Hi,
> I have a procedure for which I get different phsical_io reading from
> sysprocesses on two different servers.
> And this difference is not twice or thrice, but it is 10 times more I/O on
> other machine.
> What could be the possible cause?
> I have verified both have exactly same tables design and server settings.
> Thanks
> Pushkar
>
>|||do a DBCC SHOWCONTIG on the tables (with the index that is used in the query
)
you select in the query on both servers
and check the logical and extent fragmentation.
And see if they are nearly equal.|||"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OljdnhBeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a procedure for which I get different phsical_io reading from
> sysprocesses on two different servers.
> And this difference is not twice or thrice, but it is 10 times more I/O on
> other machine.
> What could be the possible cause?
> I have verified both have exactly same tables design and server settings.
>
Is the execution plan the same on both servers?
Is the logical IO the same?
Physical IO is affected by a whole lot of factors, but logical io is a
function of the data volume and the query plan.
David|||Thanks David
I have checked that logical I/O is same both machines.
How should I verify whether physical I/O is same or not.
Problem is simple on one machine I am using 70-80% cpu, since less I/O is
done on this machine and sp completed very fast, but on another machine I am
using 10-12% cpu, because I/O is more, so sp takes very long time to
complete.
I don't think there is any differnce SQL Server settings and query plan on
both machines, stiill different behavior.
Thanks
Pushkar
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OsYz4CeGHA.3632@.TK2MSFTNGP02.phx.gbl...
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:OljdnhBeGHA.2188@.TK2MSFTNGP04.phx.gbl...
>
> Is the execution plan the same on both servers?
> Is the logical IO the same?
> Physical IO is affected by a whole lot of factors, but logical io is a
> function of the data volume and the query plan.
> David
>|||Check your overall disk usage on both machines. Does one of the machines
have something going on that uses a lot of the CPU, so that SQL server is
competing with it?
You think the query plans are the same, but have you confirmed this? Please
post the query plans from each server, to be certain if they are the same or
not.
Have you regenerated statisitics on both servers to confirm they are up to
date?
Do you have the same data on both servers?
Do you have virus scan, or some performance monitors running on either
server. If so, are they configured identically?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:uZlc1CEeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> Thanks David
> I have checked that logical I/O is same both machines.
> How should I verify whether physical I/O is same or not.
> Problem is simple on one machine I am using 70-80% cpu, since less I/O is
> done on this machine and sp completed very fast, but on another machine I
am
> using 10-12% cpu, because I/O is more, so sp takes very long time to
> complete.
> I don't think there is any differnce SQL Server settings and query plan on
> both machines, stiill different behavior.
> Thanks
> Pushkar
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OsYz4CeGHA.3632@.TK2MSFTNGP02.phx.gbl...
settings.
>|||underprocessable|||Sorry, by disk utilization I did not mean free disk space, but disk IO.
Poor choice of words on my part.
Both machines have the same processor and disk hardware, with the same
amount of memory?
Something must be different on each machine, but there are so many things to
look at, I'm not really sure where to start.
Hopefully someone else knows better what to look at.
Please let us know what the issue turns out to be.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OPLenwEeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> Hi,
machines
is
> I have checked disk usage on both machines, lot of free disk space is
there
> on all drives.
> There is no other process running on both server, so no issue of
competing.
>
same
> Query plans are exactly same, I have attached the same.
>
to
> Statistics are uptodate
>
> Exactly same data on both servers.
>
> I am using windows performance monitor and is configured properly.
> One thing I want to your notice that I have installed a new fresh instance
> on both machine and created same database and procedure on both machine.
> I have not changed any server/database settings. then also I am getting
same
> result.
> I don't still this is SQL Server issue or some other hardware/software
> issue.
> Please help me out.
> Thanks,
> Pushkar
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eDIg5JEeGHA.4900@.TK2MSFTNGP02.phx.gbl...
machines
is
same
to
is
I
a
>
>|||How shall I figure out about DISK I/O?
CPU and Memory are exactly the same on both machines. But do u agree that
this is some hardware issue?
Thanks
Pushkar
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%230$OL2EeGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Sorry, by disk utilization I did not mean free disk space, but disk IO.
> Poor choice of words on my part.
> Both machines have the same processor and disk hardware, with the same
> amount of memory?
> Something must be different on each machine, but there are so many things
> to
> look at, I'm not really sure where to start.
> Hopefully someone else knows better what to look at.
> Please let us know what the issue turns out to be.
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:OPLenwEeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> machines
> is
> there
> competing.
> same
> to
> same
> machines
> is
> same
> to
> is
> I
> a
>|||Honestly, I don't know what the issue is. There are so many different
configuration parameters for the OS, DB, and hardware that it is impossible
to say without checking them one by one. And I myself only know a handful
of them, so I can't provide any real guidance. You can use performance
monitor to check your IO, but even there you will find many different things
to watch. At the same time, either memory or processor could make a
difference. If one is running low, then it will affect everything else.
I would run perfmon and compare memory, CPU, and IO on both boxes for a few
hours and see if you can find a discrepency there. Try getting the above
stats with and without the database running. If you get significantly
different results (between the boxes) while the database is down, then you
know something else is going on, and you can exclude the database and focus
on the hardware/OS. If both boxes are the same with the database down, but
different with the database running, then I would revisit your DB
configuration and look for discrepencies.
Please confirm two things first...
1. Is your disk using the same hardware for both databases?
2. Do both databases have the same amount of data?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OkSKHIFeGHA.380@.TK2MSFTNGP04.phx.gbl...
> How shall I figure out about DISK I/O?
> CPU and Memory are exactly the same on both machines. But do u agree that
> this is some hardware issue?
> Thanks
> Pushkar
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%230$OL2EeGHA.1320@.TK2MSFTNGP04.phx.gbl...
things
server
up
either
machine.
server
up
either
I/O
in
more
is
>