Performance of SQL query for reading from trace file

#51

The hardware on the client will also play a BIG role in how fast it can read
and display the data. One thing you can do is run this locally and the
difference will be the network and client. But you may want to try looking
at the waitstats. If this is the only thing running on the SQL Server when
you do your test you can take a snapshot of the waitstats just before and
after you read the trace file from the client and look at the difference.
Specifically the networkio waits. This is a combination of the network and
client delays. Basically when sql server is trying to send the data as fast
as it can and the client or network can't process it as fast it will report
networkio waits.

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
[max_wait_time_ms], [signal_wait_time_ms], GETDATE()
FROM sys.dm_os_wait_stats

But what kind of report are you trying to show anyway? What good is looking
at a million rows of trace data?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Munish Narula" <munish.narula (AT) wipro (DOT) com> wrote

Quote:

I am calling this query from a java application using jdbc type 4 drivers
(from a remote machine) and displaying it in a report. Well you are
right,
the time taken depends on the network as well. But i was just wondering as
to
how can i get the time taken by the network and the query seperately.

"Andrew J. Kelly" wrote:

I run this command almost daily against much larger files than 200MB with
no
problems. You didn't say it was a remote query. It really helps to have
all
the details. Why are you trying to run this remotely? The network and
client
will obviously have an impact on performance and will depend on what
those
are. What is the client you are trying to read all of this data into and
what is it doing with the data it reads? I wrote a C# app that reads the
trace files into a data table and processes them one row at a time with a
parser. I can easy process over a million rows in just a few seconds with
the file on the same machine as the app. It is not the query or SQL
Server
that is the holdup it must be your client or network.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Munish Narula" <munish.narula (AT) wipro (DOT) com> wrote in message
news:AE2E653B-3B84-4B85-87EF-FF6E6749942C (AT) microsoft (DOT) com...
Hi,
The CPU and the memory configuration is very high. That is not a
problem..

You must be executing the query on the same machine on which SQL is
installed. Try executing it from some SQL client or say from some java
program.

Also check the trace file size. It should be atleast 200 MB.

It would be really great if anyone could help me optimize this query.

Regards,

"Andrew J. Kelly" wrote:

That pretty slow as I can read that on my laptop in just a few
seconds.
I
suspect your hardware is way underpowered. I would expect that adding
memory
and increasing the CPU speed will help the most. Also make sure you
don't
have an Antivirus program that is scanning the file as you read it.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Munish Narula" <munish.narula (AT) wipro (DOT) com> wrote in message
news:7B2A1536-7C84-4F4D-9795-AC4017682146 (AT) microsoft (DOT) com...
I am using fn_trace_gettable for fetching records from trace file
which
is
around 256 MB. the query is something like

select * from fn_trace_gettable('c:\traces\trace.trc', 1);

This query takes around 133 sec to fetch records and the time taken
to
run
the query increases as the size of the file increases.

Please let me know how can i tune the query so that the query
becomes
faster.

Note: I cannot use a where clause for fetching data, I have to
fetch
all
the records at once.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Performance of SQL query for reading from trace file

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Re: Performance of SQL query for reading from trace file - 04-01-2008 , 01:27 PM