MS SQL Server Profiler Reads

Hello,
following questions. I have two server systems. The configuration for both systems are exact the same. I create an empty database on both server systems. Next i start the query analyzer and start the following query :
USE TESTX
DECLARE @.akt decimal
SET NOCOUNT ON
SET STATISTICS IO ON
create table dbo.test
(
zahl decimal
)
SET @.akt=0
WHILE NOT @.akt=1000
BEGIN
SET @.akt=@.akt+1
insert into dbo.test VALUES (@.akt)
END
SET NOCOUNT OFF
SET STATISTICS IO OFF
GO
On the first system that query runs 1 sec on the other system the query runs 4 sec
When i show the values of sysprocesses, all physical_IO, CPU .. are near the same. When i use the MS SQL Profiler, the query need on the first system 1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms. Why are the performance value
s so different ? What does reads exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and and and ... the reads increase to 25000 and takes 118000 in Duration.
How can i increase the performance with the same TSQL Script (see above) ?
On the temp db the query above has the same performance on both systems ? Why ?
Please see KB ID 314648 INF: Differences in STATISTICS IO, SQL Profiler and
Sysprocesses.
As for optimizing the queries we need to collect Executions Plans ,
STATISTICS PROFILE and performance monitor from both servers to have a
better understanding of the differences in the query execution and
bottlenecks.
Please also see
243589.INF: Troubleshooting Slow-running Queries
243588.INF: Troubleshooting Performance of Ad-Hoc Queries
243589 INF: Troubleshooting Slow-running Queries
HTH
Agnes Panosian
SQL Server Support
|||Can i sent it via eMail to you ? (I have Performance Counters Profler Informations and the STATISTICS from sysprocesses
|||The profiler reads counter will not show exactly the same value as what you
would get from statistics io for a query because it will also accumulate the
reads profiler itself did. The best way to find actual reads for a query, in
my opinion, is to rely on stats io output alone.
To answer the 1 sec to 4 sec difference, I can only assume that this is
because of the underlying h/w. It is best to turn off tracing(profiling),
then run the queries on both the systems and re-evaluate because profiler
has some overhead.
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
> systems are exact the same. I create an empty database on both server
> systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
> runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
> the same. When i use the MS SQL Profiler, the query need on the first
> system 1000 Reads and 1000 ms on the other system it takes 6000 reads and
> 4000 ms. Why are the performance values so different ? What does reads
> exactly mean in MS SQL Profiler ? When i perform a lot of IO Trafffic
> (with Backup and and and ... the reads increase to 25000 and takes
> 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
> Why ?
|||You might want to take a look with sysinternal's filemon, and see how many
reads (and how many blocks at the time) are done at the OS level, sometimes
the readsizes (clusters of 8k blocks) can differ, and thus I/O access times.
Also take a look at sqlperf(waitstats) and see where you loose time..(make
sure you reset the stats before you run your tests)
Or use my tools to monitor what is really happening, downloadable for build
760. Build 800 on request..
regards,
Mario
http://www.sqlinternals.com
"Dataworld" <anonymous@.discussions.microsoft.com> wrote in message
news:445C02E4-92AC-40C6-9F3D-6F2171D260E0@.microsoft.com...
> Hello,
> following questions. I have two server systems. The configuration for both
systems are exact the same. I create an empty database on both server
systems. Next i start the query analyzer and start the following query :
> USE TESTX
> DECLARE @.akt decimal
> SET NOCOUNT ON
> SET STATISTICS IO ON
> create table dbo.test
> (
> zahl decimal
> )
> SET @.akt=0
> WHILE NOT @.akt=1000
> BEGIN
> SET @.akt=@.akt+1
> insert into dbo.test VALUES (@.akt)
> END
> SET NOCOUNT OFF
> SET STATISTICS IO OFF
> GO
> On the first system that query runs 1 sec on the other system the query
runs 4 sec
> When i show the values of sysprocesses, all physical_IO, CPU .. are near
the same. When i use the MS SQL Profiler, the query need on the first system
1000 Reads and 1000 ms on the other system it takes 6000 reads and 4000 ms.
Why are the performance values so different ? What does reads exactly mean
in MS SQL Profiler ? When i perform a lot of IO Trafffic (with Backup and
and and ... the reads increase to 25000 and takes 118000 in Duration.
> How can i increase the performance with the same TSQL Script (see above) ?
> On the temp db the query above has the same performance on both systems ?
Why ?