Monday, February 20, 2012

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
>

No comments:

Post a Comment