|
Home > Archive > microsoft.public.sqlserver.server > December 2002 > Trace Result in Query Analyser
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Trace Result in Query Analyser
|
|
| Beechams 2002-12-17, 7:23 am |
| Hi Everyone,
Our application bulkloads data in the following way:-
File is parsed through front-end into XML string
XML string is then passed to Stored Procedure
INSERT into table variable using OPENXML
Perform any data transforms on table variable
INSERT into table(s) from table vairable
W2K Advanced Server, SQL Server 2000 SP1
My problem is I have been asked by a client to provide some benchmark
performance data for comparison with theirs. I have been using
Profiler to retrieve Duration, CPU, Read and Write data for each SQL
statement within the test procedure. This is not a problem until I
thought the client could use Query Analyser to retrieve the same data
so I ran the tests again using both Profiler and Query Analyser, the
results were a little worrying:
Query Analyser (INSERT INTO @tablevariable FROM OPENXML):
Duration: 25
CPU: 234
Reads: 180
Writes: 0
Profiler (INSERT INTO @tablevariable FROM OPENXML):
Duration: 109
CPU: 110
Reads: 561
Writes: 0
I assumed the SQL Trace facility should produce the same results as
Profiler, however this doesn't appear to be the case. Can anyone shed
any light?
Thanks in advance
Cheers
Chris
| |
| SQL Server Development Team [MSFT] 2002-12-18, 1:24 pm |
| What do you mean by 'using QA'? Are you using extended stored procs to get
the information?
Did you run the query only once invoking both xp's and profile at the same
time?
It looks like the buffers are not flushed before running the queries, i.e.
you might still have some data in the caches. That would explain the
different number of reads as well as the different running time.
Try
dbcc freeproccache
dbcc dropcleanbuffers
before running the individual queries and see if the output still differs.
Thanks,
Corinna, Florian
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Beechams" <chris.webster@totalise.co.uk> wrote in message
news:c38bce76.0212170506.14be6729@posting.google.com...
> Hi Everyone,
>
> Our application bulkloads data in the following way:-
> File is parsed through front-end into XML string
> XML string is then passed to Stored Procedure
> INSERT into table variable using OPENXML
> Perform any data transforms on table variable
> INSERT into table(s) from table vairable
>
> W2K Advanced Server, SQL Server 2000 SP1
>
>
> My problem is I have been asked by a client to provide some benchmark
> performance data for comparison with theirs. I have been using
> Profiler to retrieve Duration, CPU, Read and Write data for each SQL
> statement within the test procedure. This is not a problem until I
> thought the client could use Query Analyser to retrieve the same data
> so I ran the tests again using both Profiler and Query Analyser, the
> results were a little worrying:
>
> Query Analyser (INSERT INTO @tablevariable FROM OPENXML):
> Duration: 25
> CPU: 234
> Reads: 180
> Writes: 0
>
> Profiler (INSERT INTO @tablevariable FROM OPENXML):
> Duration: 109
> CPU: 110
> Reads: 561
> Writes: 0
>
>
> I assumed the SQL Trace facility should produce the same results as
> Profiler, however this doesn't appear to be the case. Can anyone shed
> any light?
>
> Thanks in advance
>
> Cheers
> Chris
| |
|
| I am using QA to execute the test stored procedures, through QA you can
output a trace file, just like you can an Execution Plan, with the results.
At the same time I use profiler to trace the SPID i am using. The results
from profiler and QA differ wildly but I assumed they would use the same
extended stored procedures.
I have use the dbcc commands as part of the test, this makes no difference.
Thanks
Chris
"SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in message
news:OLT5qfspCHA.2480@TK2MSFTNGP12...
> What do you mean by 'using QA'? Are you using extended stored procs to get
> the information?
> Did you run the query only once invoking both xp's and profile at the same
> time?
>
> It looks like the buffers are not flushed before running the queries, i.e.
> you might still have some data in the caches. That would explain the
> different number of reads as well as the different running time.
>
> Try
>
> dbcc freeproccache
> dbcc dropcleanbuffers
>
> before running the individual queries and see if the output still differs.
>
>
> Thanks,
> Corinna, Florian
>
>
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
>
> "Beechams" <chris.webster@totalise.co.uk> wrote in message
> news:c38bce76.0212170506.14be6729@posting.google.com...
> > Hi Everyone,
> >
> > Our application bulkloads data in the following way:-
> > File is parsed through front-end into XML string
> > XML string is then passed to Stored Procedure
> > INSERT into table variable using OPENXML
> > Perform any data transforms on table variable
> > INSERT into table(s) from table vairable
> >
> > W2K Advanced Server, SQL Server 2000 SP1
> >
> >
> > My problem is I have been asked by a client to provide some benchmark
> > performance data for comparison with theirs. I have been using
> > Profiler to retrieve Duration, CPU, Read and Write data for each SQL
> > statement within the test procedure. This is not a problem until I
> > thought the client could use Query Analyser to retrieve the same data
> > so I ran the tests again using both Profiler and Query Analyser, the
> > results were a little worrying:
> >
> > Query Analyser (INSERT INTO @tablevariable FROM OPENXML):
> > Duration: 25
> > CPU: 234
> > Reads: 180
> > Writes: 0
> >
> > Profiler (INSERT INTO @tablevariable FROM OPENXML):
> > Duration: 109
> > CPU: 110
> > Reads: 561
> > Writes: 0
> >
> >
> > I assumed the SQL Trace facility should produce the same results as
> > Profiler, however this doesn't appear to be the case. Can anyone shed
> > any light?
> >
> > Thanks in advance
> >
> > Cheers
> > Chris
>
>
| |
| SQL Server Development Team [MSFT] 2002-12-19, 1:23 pm |
| Which EventClass in Profiler are you getting the results from (duration,
cpu, reads, writes)? If possible please post the entire batch that you are
sending to the server.
Thanks, Corinna
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Chris" <chris.webster@russellmelloncaps.com> wrote in message
news:ats2d4$fv4$1$8300dec7@new
s.demon.co.uk...
> I am using QA to execute the test stored procedures, through QA you can
> output a trace file, just like you can an Execution Plan, with the
results.
> At the same time I use profiler to trace the SPID i am using. The results
> from profiler and QA differ wildly but I assumed they would use the same
> extended stored procedures.
>
> I have use the dbcc commands as part of the test, this makes no
difference.
>
> Thanks
> Chris
>
>
> "SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in
message
> news:OLT5qfspCHA.2480@TK2MSFTNGP12...
> > What do you mean by 'using QA'? Are you using extended stored procs to
get
> > the information?
> > Did you run the query only once invoking both xp's and profile at the
same
> > time?
> >
> > It looks like the buffers are not flushed before running the queries,
i.e.
> > you might still have some data in the caches. That would explain the
> > different number of reads as well as the different running time.
> >
> > Try
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > before running the individual queries and see if the output still
differs.
> >
> >
> > Thanks,
> > Corinna, Florian
> >
> >
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> > "Beechams" <chris.webster@totalise.co.uk> wrote in message
> > news:c38bce76.0212170506.14be6729@posting.google.com...
> > > Hi Everyone,
> > >
> > > Our application bulkloads data in the following way:-
> > > File is parsed through front-end into XML string
> > > XML string is then passed to Stored Procedure
> > > INSERT into table variable using OPENXML
> > > Perform any data transforms on table variable
> > > INSERT into table(s) from table vairable
> > >
> > > W2K Advanced Server, SQL Server 2000 SP1
> > >
> > >
> > > My problem is I have been asked by a client to provide some benchmark
> > > performance data for comparison with theirs. I have been using
> > > Profiler to retrieve Duration, CPU, Read and Write data for each SQL
> > > statement within the test procedure. This is not a problem until I
> > > thought the client could use Query Analyser to retrieve the same data
> > > so I ran the tests again using both Profiler and Query Analyser, the
> > > results were a little worrying:
> > >
> > > Query Analyser (INSERT INTO @tablevariable FROM OPENXML):
> > > Duration: 25
> > > CPU: 234
> > > Reads: 180
> > > Writes: 0
> > >
> > > Profiler (INSERT INTO @tablevariable FROM OPENXML):
> > > Duration: 109
> > > CPU: 110
> > > Reads: 561
> > > Writes: 0
> > >
> > >
> > > I assumed the SQL Trace facility should produce the same results as
> > > Profiler, however this doesn't appear to be the case. Can anyone shed
> > > any light?
> > >
> > > Thanks in advance
> > >
> > > Cheers
> > > Chris
> >
> >
>
>
| |
|
| I am getting the results form the SP:StmtCompleted EventClass, exactly the
same as shown in the trace output shown in QA.
The Procedure is as below:
CREATE PROCEDURE XML_Load2
@ReturnVal int OUTPUT,
@XML text
AS
DECLARE @XMLID int
DECLARE @tXML table
(
SIC smallint,
IC varchar(16),
ID datetime,
CC smallint,
RSC smallint,
IV float,
PRIMARY KEY CLUSTERED
(
SIC,
IC,
ID,
CC,
RSC
)
)
EXEC sp_xml_preparedocument @XMLID OUTPUT, @XML
INSERT INTO @tXML
SELECT *
FROM OPENXML (@XMLID, '/ROOT/BATCH/Row',1)
WITH
(
SIC smallint,
IC varchar(16),
ID datetime,
CC smallint,
RSC smallint,
IV float
)
EXEC sp_xml_removedocument @XMLID
INSERT INTO IV
SELECT x.SIC,
x.IC,
x.ID,
x.CC,
x.RSC,
x.IV
FROM @tXML x LEFT LOOP JOIN IV i
ON x.SIC = i.SIC
AND x.IC = i.IC
AND x.ID = i.ID
AND x.CC = i.CC
AND x.RSC = i.RSC
WHERE i.SIC IS NULL
GO
I send data like the following in 1000, 2000, 4000, 8000 and 21000 row XML
strings as seperate calls to the above sp, to increase the speed of the
OPENXML statement each XML string is broken down into 500 row batches
'<ROOT>
<BATCH>
<Row SIC="4" IC="ABC123" ID="2002/10/31" CC="1" RSC="2" IV="101.23"/>
<Row SIC="4" IC="ABC123" ID="2002/10/31" CC="1" RSC="3" IV="104.82"/>
<Row SIC="4" IC="ABC123" ID="2002/10/31" CC="2" RSC="2" IV="90.12"/>
<Row SIC="4" IC="ABC123" ID="2002/10/31" CC="2" RSC="3" IV="123.12"/>
<Row SIC="4" IC="ABC123" ID="2002/10/31" CC="15" RSC="2" IV="321.0"/>
..
..
<\BATCH>
<BATCH>
..
..
<\BATCH>
<\ROOT>'
Cheers
Chris
"SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in message
news:eQRe1t4pCHA.1612@TK2MSFTNGP09...
> Which EventClass in Profiler are you getting the results from (duration,
> cpu, reads, writes)? If possible please post the entire batch that you
are
> sending to the server.
>
> Thanks, Corinna
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
> "Chris" <chris.webster@russellmelloncaps.com> wrote in message
> news:ats2d4$fv4$1$8300dec7@new
s.demon.co.uk...
> > I am using QA to execute the test stored procedures, through QA you can
> > output a trace file, just like you can an Execution Plan, with the
> results.
> > At the same time I use profiler to trace the SPID i am using. The
results
> > from profiler and QA differ wildly but I assumed they would use the same
> > extended stored procedures.
> >
> > I have use the dbcc commands as part of the test, this makes no
> difference.
> >
> > Thanks
> > Chris
> >
> >
> > "SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in
> message
> > news:OLT5qfspCHA.2480@TK2MSFTNGP12...
> > > What do you mean by 'using QA'? Are you using extended stored procs to
> get
> > > the information?
> > > Did you run the query only once invoking both xp's and profile at the
> same
> > > time?
> > >
> > > It looks like the buffers are not flushed before running the queries,
> i.e.
> > > you might still have some data in the caches. That would explain the
> > > different number of reads as well as the different running time.
> > >
> > > Try
> > >
> > > dbcc freeproccache
> > > dbcc dropcleanbuffers
> > >
> > > before running the individual queries and see if the output still
> differs.
> > >
> > >
> > > Thanks,
> > > Corinna, Florian
> > >
> > >
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > Use of included script samples are subject to the terms specified at
> > > http://www.microsoft.com/info/cpyright.htm.
> > >
> > >
> > > "Beechams" <chris.webster@totalise.co.uk> wrote in message
> > > news:c38bce76.0212170506.14be6729@posting.google.com...
> > > > Hi Everyone,
> > > >
> > > > Our application bulkloads data in the following way:-
> > > > File is parsed through front-end into XML string
> > > > XML string is then passed to Stored Procedure
> > > > INSERT into table variable using OPENXML
> > > > Perform any data transforms on table variable
> > > > INSERT into table(s) from table vairable
> > > >
> > > > W2K Advanced Server, SQL Server 2000 SP1
> > > >
> > > >
> > > > My problem is I have been asked by a client to provide some
benchmark
> > > > performance data for comparison with theirs. I have been using
> > > > Profiler to retrieve Duration, CPU, Read and Write data for each SQL
> > > > statement within the test procedure. This is not a problem until I
> > > > thought the client could use Query Analyser to retrieve the same
data[
color=darkred]
> > > > so I ran the tests again using both Profiler and Query Analyser, the
> > > > results were a little worrying:
> > > >
> > > > Query Analyser (INSERT INTO @tablevariable FROM OPENXML):
> > > > Duration: 25
> > > > CPU: 234
> > > > Reads: 180
> > > > Writes: 0
> > > >
> > > > Profiler (INSERT INTO @tablevariable FROM OPENXML):
> > > > Duration: 109
> > > > CPU: 110
> > > > Reads: 561
> > > > Writes: 0
> > > >
> > > >
> > > > I assumed the SQL Trace facility should produce the same results as
> > > > Profiler, however this doesn't appear to be the case. Can anyone[/color]
shed[
color=darkred]
> > > > any light?
> > > >
> > > > Thanks in advance
> > > >
> > > > Cheers
> > > > Chris
> > >
> > >
> >
> >
>
>[/color]
| |
| SQL Server Development Team [MSFT] 2002-12-20, 1:23 pm |
| Chris,
Can you try narrowing it down to where the difference originates from by
executing the individual statements one by one?
Thanks,
Corinna, Florian
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Chris" <chris.webster@russellmelloncaps.com> wrote in message
news:atuuk5$7v8$1$8300dec7@new
s.demon.co.uk...
> I am getting the results form the SP:StmtCompleted EventClass, exactly the
> same as shown in the trace output shown in QA.
>
> The Procedure is as below:
>
> CREATE PROCEDURE XML_Load2
> @ReturnVal int OUTPUT,
> @XML text
> AS
> DECLARE @XMLID int
> DECLARE @tXML table
> (
> SIC smallint,
> IC varchar(16),
> ID datetime,
> CC smallint,
> RSC smallint,
> IV float,
> PRIMARY KEY CLUSTERED
> (
> SIC,
> IC,
> ID,
> CC,
> RSC
> )
> )
>
> EXEC sp_xml_preparedocument @XMLID OUTPUT, @XML
>
> INSERT INTO @tXML
> SELECT *
> FROM OPENXML (@XMLID, '/ROOT/BATCH/Row',1)
> WITH
> (
> SIC smallint,
> IC varchar(16),
> ID datetime,
> CC smallint,
> RSC smallint,
> IV float
> )
>
> EXEC sp_xml_removedocument @XMLID
>
> INSERT INTO IV
> SELECT x.SIC,
> x.IC,
> x.ID,
> x.CC,
> x.RSC,
> x.IV
> FROM @tXML x LEFT LOOP JOIN IV i
> ON x.SIC = i.SIC
> AND x.IC = i.IC
> AND x.ID = i.ID
> AND x.CC = i.CC
> AND x.RSC = i.RSC
> WHERE i.SIC IS NULL
>
> GO
>
> I send data like the following in 1000, 2000, 4000, 8000 and 21000 row XML
> strings as seperate calls to the above sp, to increase the speed of the
> OPENXML statement each XML string is broken down into 500 row batches
>
> '<ROOT>
> <BATCH>
> <Row SIC="4" IC="ABC123" ID="2002/10/31" CC="1" RSC="2" IV="101.23"/>
> <Row SIC="4" IC="ABC123" ID="2002/10/31" CC="1" RSC="3" IV="104.82"/>
> <Row SIC="4" IC="ABC123" ID="2002/10/31" CC="2" RSC="2" IV="90.12"/>
> <Row SIC="4" IC="ABC123" ID="2002/10/31" CC="2" RSC="3" IV="123.12"/>
> <Row SIC="4" IC="ABC123" ID="2002/10/31" CC="15" RSC="2" IV="321.0"/>
> ..
> ..
> <\BATCH>
> <BATCH>
> ..
> ..
> <\BATCH>
> <\ROOT>'
>
> Cheers
> Chris
>
> "SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in
message
> news:eQRe1t4pCHA.1612@TK2MSFTNGP09...
> > Which EventClass in Profiler are you getting the results from (duration,
> > cpu, reads, writes)? If possible please post the entire batch that you
> are
> > sending to the server.
> >
> > Thanks, Corinna
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> > "Chris" <chris.webster@russellmelloncaps.com> wrote in message
> > news:ats2d4$fv4$1$8300dec7@new
s.demon.co.uk...
> > > I am using QA to execute the test stored procedures, through QA you
can[c
olor=darkred]
> > > output a trace file, just like you can an Execution Plan, with the
> > results.
> > > At the same time I use profiler to trace the SPID i am using. The
> results
> > > from profiler and QA differ wildly but I assumed they would use the[/color]
same[
color=darkred]
> > > extended stored procedures.
> > >
> > > I have use the dbcc commands as part of the test, this makes no
> > difference.
> > >
> > > Thanks
> > > Chris
> > >
> > >
> > > "SQL Server Development Team [MSFT]" <sqldev@microsoft.com> wrote in
> > message
> > > news:OLT5qfspCHA.2480@TK2MSFTNGP12...
> > > > What do you mean by 'using QA'? Are you using extended stored procs[/color]
to
> > get
> > > > the information?
> > > > Did you run the query only once invoking both xp's and profile at
the
> > same
> > > > time?
> > > >
> > > > It looks like the buffers are not flushed before running the
queries,
> > i.e.
> > > > you might still have some data in the caches. That would explain the
> > > > different number of reads as well as the different running time.
> > > >
> > > > Try
> > > >
> > > > dbcc freeproccache
> > > > dbcc dropcleanbuffers
> > > >
> > > > before running the individual queries and see if the output still
> > differs.
> > > >
> > > >
> > > > Thanks,
> > > > Corinna, Florian
> > > >
> > > >
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > Use of included script samples are subject to the terms specified at
> > > > http://www.microsoft.com/info/cpyright.htm.
> > > >
> > > >
> > > > "Beechams" <chris.webster@totalise.co.uk> wrote in message
> > > > news:c38bce76.0212170506.14be6729@posting.google.com...
> > > > > Hi Everyone,
> > > > >
> > > > > Our application bulkloads data in the following way:-
> > > > > File is parsed through front-end into XML string
> > > > > XML string is then passed to Stored Procedure
> > > > > INSERT into table variable using OPENXML
> > > > > Perform any data transforms on table variable
> > > > > INSERT into table(s) from table vairable
> > > > >
> > > > > W2K Advanced Server, SQL Server 2000 SP1
> > > > >
> > > > >
> > > > > My problem is I have been asked by a client to provide some
> benchmark
> > > > > performance data for comparison with theirs. I have been using
> > > > > Profiler to retrieve Duration, CPU, Read and Write data for each
SQL[c
olor=darkred]
> > > > > statement within the test procedure. This is not a problem until I
> > > > > thought the client could use Query Analyser to retrieve the same
> data
> > > > > so I ran the tests again using both Profiler and Query Analyser,[/color]
the[c
olor=darkred]
> > > > > results were a little worrying:
> > > > >
> > > > > Query Analyser (INSERT INTO @tablevariable FROM OPENXML):
> > > > > Duration: 25
> > > > > CPU: 234
> > > > > Reads: 180
> > > > > Writes: 0
> > > > >
> > > > > Profiler (INSERT INTO @tablevariable FROM OPENXML):
> > > > > Duration: 109
> > > > > CPU: 110
> > > > > Reads: 561
> > > > > Writes: 0
> > > > >
> > > > >
> > > > > I assumed the SQL Trace facility should produce the same results[/color]
as[co
lor=darkred]
> > > > > Profiler, however this doesn't appear to be the case. Can anyone
> shed
> > > > > any light?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > Cheers
> > > > > Chris
> > > >
> > > >
> > >
> > >
> >
> >
>
>[/color]
|
|
|
|
|