Performance different

I have 2 databases that are identical in schema and structure, one hold June
data and one hold July data. I ran the query that hits 4 tables. Tables on
both databases are identical including indexes and keys. It took 40 seconds
when I run under June database. When I run under July, it did not return
the result set and it been running more than 2 hours. I checked the number
of data on those 4 tables and they are basically the same. Is there a way
for me to know what is going on? I used Profiler but no help. The query is
single commit transaction. Please help. Thanks!Your most likely blocked. Run sp_who2 to see who is blocking you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> I have 2 databases that are identical in schema and structure, one hold
June
> data and one hold July data. I ran the query that hits 4 tables. Tables
on
> both databases are identical including indexes and keys. It took 40
seconds
> when I run under June database. When I run under July, it did not return
> the result set and it been running more than 2 hours. I checked the
number
> of data on those 4 tables and they are basically the same. Is there a way
> for me to know what is going on? I used Profiler but no help. The query
is
> single commit transaction. Please help. Thanks!
>|||I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
found was shared locks. This is for the data warehouse environment and we
only have select statments. Thanks for the recommendation.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> Your most likely blocked. Run sp_who2 to see who is blocking you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > I have 2 databases that are identical in schema and structure, one hold
> June
> > data and one hold July data. I ran the query that hits 4 tables.
Tables
> on
> > both databases are identical including indexes and keys. It took 40
> seconds
> > when I run under June database. When I run under July, it did not
return
> > the result set and it been running more than 2 hours. I checked the
> number
> > of data on those 4 tables and they are basically the same. Is there a
way
> > for me to know what is going on? I used Profiler but no help. The
query
> is
> > single commit transaction. Please help. Thanks!
> >
> >
>|||Is the estimated query plan the same for both? What is the status of the
spid while it is running? Is there activity going on (disk, cpu etc)?
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> found was shared locks. This is for the data warehouse environment and we
> only have select statments. Thanks for the recommendation.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > Your most likely blocked. Run sp_who2 to see who is blocking you.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > I have 2 databases that are identical in schema and structure, one
hold
> > June
> > > data and one hold July data. I ran the query that hits 4 tables.
> Tables
> > on
> > > both databases are identical including indexes and keys. It took 40
> > seconds
> > > when I run under June database. When I run under July, it did not
> return
> > > the result set and it been running more than 2 hours. I checked the
> > number
> > > of data on those 4 tables and they are basically the same. Is there a
> way
> > > for me to know what is going on? I used Profiler but no help. The
> query
> > is
> > > single commit transaction. Please help. Thanks!
> > >
> > >
> >
> >
>|||I was not able to see the execution plan for the slow one because it didn't
stop. But the query structure, tables, indexes are identical. The SPID for
both are SELECT and using parallelism. The CPU for both are spiking, disk
write/sec, page write/sec, data map hits, and lazy write pages/sec are at
constant flat lines.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Is the estimated query plan the same for both? What is the status of the
> spid while it is running? Is there activity going on (disk, cpu etc)?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> > found was shared locks. This is for the data warehouse environment and
we
> > only have select statments. Thanks for the recommendation.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > I have 2 databases that are identical in schema and structure, one
> hold
> > > June
> > > > data and one hold July data. I ran the query that hits 4 tables.
> > Tables
> > > on
> > > > both databases are identical including indexes and keys. It took 40
> > > seconds
> > > > when I run under June database. When I run under July, it did not
> > return
> > > > the result set and it been running more than 2 hours. I checked the
> > > number
> > > > of data on those 4 tables and they are basically the same. Is there
a
> > way
> > > > for me to know what is going on? I used Profiler but no help. The
> > query
> > > is
> > > > single commit transaction. Please help. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kevin,
You can do an Estimated query plan without actually running the query. In
Query Analyzer you can highlight the query and press Ctrl + L to see it. By
"all flat lines" do you mean maxed out? If so then your are most likely
doing a full table scan. The estimated query plan will tell you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> I was not able to see the execution plan for the slow one because it
didn't
> stop. But the query structure, tables, indexes are identical. The SPID
for
> both are SELECT and using parallelism. The CPU for both are spiking, disk
> write/sec, page write/sec, data map hits, and lazy write pages/sec are at
> constant flat lines.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Is the estimated query plan the same for both? What is the status of
the
> > spid while it is running? Is there activity going on (disk, cpu etc)?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks
I
> > > found was shared locks. This is for the data warehouse environment
and
> we
> > > only have select statments. Thanks for the recommendation.
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > I have 2 databases that are identical in schema and structure, one
> > hold
> > > > June
> > > > > data and one hold July data. I ran the query that hits 4 tables.
> > > Tables
> > > > on
> > > > > both databases are identical including indexes and keys. It took
40
> > > > seconds
> > > > > when I run under June database. When I run under July, it did not
> > > return
> > > > > the result set and it been running more than 2 hours. I checked
the
> > > > number
> > > > > of data on those 4 tables and they are basically the same. Is
there
> a
> > > way
> > > > > for me to know what is going on? I used Profiler but no help.
The
> > > query
> > > > is
> > > > > single commit transaction. Please help. Thanks!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you for your suggestion. After figuring what the Estimated query plan
does, it is caused by recursive loops. One month return 2,500 rows while
the other one return 15,000. These recursively 3 times and that make the
query run on 2nd database longer. I was able fix the query so it run
faster.
THANK YOU Andrew for your help !!!!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufQspyXrDHA.2964@.tk2msftngp13.phx.gbl...
> Kevin,
> You can do an Estimated query plan without actually running the query. In
> Query Analyzer you can highlight the query and press Ctrl + L to see it.
By
> "all flat lines" do you mean maxed out? If so then your are most likely
> doing a full table scan. The estimated query plan will tell you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I was not able to see the execution plan for the slow one because it
> didn't
> > stop. But the query structure, tables, indexes are identical. The SPID
> for
> > both are SELECT and using parallelism. The CPU for both are spiking,
disk
> > write/sec, page write/sec, data map hits, and lazy write pages/sec are
at
> > constant flat lines.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > > Is the estimated query plan the same for both? What is the status of
> the
> > > spid while it is running? Is there activity going on (disk, cpu etc)?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > > I ran sp_lock & sp_who2 and found no exclusive locks. The only
locks
> I
> > > > found was shared locks. This is for the data warehouse environment
> and
> > we
> > > > only have select statments. Thanks for the recommendation.
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > > I have 2 databases that are identical in schema and structure,
one
> > > hold
> > > > > June
> > > > > > data and one hold July data. I ran the query that hits 4
tables.
> > > > Tables
> > > > > on
> > > > > > both databases are identical including indexes and keys. It
took
> 40
> > > > > seconds
> > > > > > when I run under June database. When I run under July, it did
not
> > > > return
> > > > > > the result set and it been running more than 2 hours. I checked
> the
> > > > > number
> > > > > > of data on those 4 tables and they are basically the same. Is
> there
> > a
> > > > way
> > > > > > for me to know what is going on? I used Profiler but no help.
> The
> > > > query
> > > > > is
> > > > > > single commit transaction. Please help. Thanks!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

performance counters on a SAN

Hello,
Recently we moved our database servers onto a emc cx600 san. Since
the move I ran perfmon against the server for a week to try to get a
baseline of the server's performance. I am getting some curious
metrics from perfmon on the avg. disk queue lenth and %disk time for
the data drive for the sql server database. We have the same
dedicated spindle config as we did on our former system with the
difference being the greatly increased throughput of the new system.
Avg. disk queue lenth is reporting an avg. of over 30 for lengthy
periods of time(> 10 minutes) and %disk time is reporting an avg of
over 300 around the same time . The former system was not reporting
any performance bottlenecks. I wonder if the counters are skewed
somehow. Has anyone experienced any oddities with disk related
performance counter metrics on a san or am I suddenly looking at a
disk bottleneck? Is it possible that the increased throughput to the
san is now causing a disk bottleneck?
Let me know if more details are needed.
Thanks,
GregThanks for the reply. The logical drive is a 10 spindle raid 5, so
the avg. disk queue length would be 3. If I'm not mistaken an avg.
disk queue length of 2 or more for any extended period of time would
be considered a bottleneck. Also, a % disk time of 300 seems rather
high to me as well. I am just curious if these disk counters report
accurately on a san as there weren't any bottlenecks reported on the
former system which was much slower than this system all the way
around. To my knowledge there hasn't been a substantial change in
workload to suddenly cause this.
Greg
On Tue, 12 Aug 2003 05:26:31 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>Remember that disk queue lengths are for the logical drive... and to get
>*real* number you care about divide 30 by the number of physical drives
> which are likely to be a larger number in the SAN)...
>"Greg" <gjackson@.thq.com> wrote in message
>news:gjagjvgsp8ogelufmret27pdddi7vi4sh4@.4ax.com...
>> Hello,
>> Recently we moved our database servers onto a emc cx600 san. Since
>> the move I ran perfmon against the server for a week to try to get a
>> baseline of the server's performance. I am getting some curious
>> metrics from perfmon on the avg. disk queue lenth and %disk time for
>> the data drive for the sql server database. We have the same
>> dedicated spindle config as we did on our former system with the
>> difference being the greatly increased throughput of the new system.
>> Avg. disk queue lenth is reporting an avg. of over 30 for lengthy
>> periods of time(> 10 minutes) and %disk time is reporting an avg of
>> over 300 around the same time . The former system was not reporting
>> any performance bottlenecks. I wonder if the counters are skewed
>> somehow. Has anyone experienced any oddities with disk related
>> performance counter metrics on a san or am I suddenly looking at a
>> disk bottleneck? Is it possible that the increased throughput to the
>> san is now causing a disk bottleneck?
>> Let me know if more details are needed.
>> Thanks,
>> Greg
>sql