I just want to confirm from you that is this is the good practise if I can
take full backup which size is 4 GB and log backup into another system
instead of same system... right now what I am thinking is the size of the
backup is too larget so the network would be too jam if the full backup
would be take place ? need your idea.
Thanks
Hi,
If you have enough hard disk space, it is always recomended to perform full
database backup in to the local system.
This will complete the backup very fast and will not create network issues.
After the backup process you could
copy the .BAK file to remote machine.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:%23snt5iwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
>I just want to confirm from you that is this is the good practise if I can
> take full backup which size is 4 GB and log backup into another system
> instead of same system... right now what I am thinking is the size of the
> backup is too larget so the network would be too jam if the full backup
> would be take place ? need your idea.
> Thanks
>
|||if you can compare then how much would be difference ... 50% delay or 20%
like if you campare to both backup into same machine and backup into another
machine....just for idea.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eM55cuwWFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If you have enough hard disk space, it is always recomended to perform
full
> database backup in to the local system.
> This will complete the backup very fast and will not create network
issues.[vbcol=seagreen]
> After the backup process you could
> copy the .BAK file to remote machine.
> Thanks
> Hari
> SQL Server MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:%23snt5iwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
can[vbcol=seagreen]
the
>
|||Hi,
That depends up on your Network speed and disk controller in the remote
server. As a trial you could try once by backup the database in your local
server once and do a backup across the network once. This will give you the
exact picture on timing.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:uHY2bzwWFHA.2080@.TK2MSFTNGP15.phx.gbl...
> if you can compare then how much would be difference ... 50% delay or 20%
> like if you campare to both backup into same machine and backup into
> another
> machine....just for idea.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eM55cuwWFHA.2348@.TK2MSFTNGP14.phx.gbl...
> full
> issues.
> can
> the
>
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Monday, March 12, 2012
Wednesday, March 7, 2012
Expanding a database
Is there to increase the size a database datafile with using the same
datafile. I do not want to create a seperate datafile. I am using SQL2000
SP4.Yes, use ALTER DATABASE ... MODIFY FILE...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:%23EFCGAGJHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Is there to increase the size a database datafile with using the same
> datafile. I do not want to create a seperate datafile. I am using SQL2000
> SP4.
>
datafile. I do not want to create a seperate datafile. I am using SQL2000
SP4.Yes, use ALTER DATABASE ... MODIFY FILE...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:%23EFCGAGJHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Is there to increase the size a database datafile with using the same
> datafile. I do not want to create a seperate datafile. I am using SQL2000
> SP4.
>
Expanding a database
Is there to increase the size a database datafile with using the same
datafile. I do not want to create a seperate datafile. I am using SQL2000
SP4.Yes, use ALTER DATABASE ... MODIFY FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:%23EFCGAGJHHA.1816@.TK2MSFTNGP06.phx
.gbl...
> Is there to increase the size a database datafile with using the same
> datafile. I do not want to create a seperate datafile. I am using SQL2000
> SP4.
>
datafile. I do not want to create a seperate datafile. I am using SQL2000
SP4.Yes, use ALTER DATABASE ... MODIFY FILE...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:%23EFCGAGJHHA.1816@.TK2MSFTNGP06.phx
.gbl...
> Is there to increase the size a database datafile with using the same
> datafile. I do not want to create a seperate datafile. I am using SQL2000
> SP4.
>
Expand Row Height on Export to Exel?
I have a report where the row height is allowed to grow as needed. When I
export this to Excel, the row hight will not adjust to the proper size.
Help Please!
I have read this posted by others, but I did not read a solution.I created an Excel macro that looks for the max number of LFs per row
and adjusts the heights accordingly
not super, but it works...
"bugfish" wrote:
> I have a report where the row height is allowed to grow as needed. When I
> export this to Excel, the row hight will not adjust to the proper size.
> Help Please!
> I have read this posted by others, but I did not read a solution.
>|||Thanks Derk. Doesn't this doesn't really help me since I want to render to
Excel via email to my clients. I need a solution that works without
manipulating the Excel worksheet.
Where are the MS contributors on this one'''
"Derk Jan" wrote:
> I created an Excel macro that looks for the max number of LFs per row
> and adjusts the heights accordingly
> not super, but it works...
> "bugfish" wrote:
> > I have a report where the row height is allowed to grow as needed. When I
> > export this to Excel, the row hight will not adjust to the proper size.
> >
> > Help Please!
> >
> > I have read this posted by others, but I did not read a solution.
> >
> >
export this to Excel, the row hight will not adjust to the proper size.
Help Please!
I have read this posted by others, but I did not read a solution.I created an Excel macro that looks for the max number of LFs per row
and adjusts the heights accordingly
not super, but it works...
"bugfish" wrote:
> I have a report where the row height is allowed to grow as needed. When I
> export this to Excel, the row hight will not adjust to the proper size.
> Help Please!
> I have read this posted by others, but I did not read a solution.
>|||Thanks Derk. Doesn't this doesn't really help me since I want to render to
Excel via email to my clients. I need a solution that works without
manipulating the Excel worksheet.
Where are the MS contributors on this one'''
"Derk Jan" wrote:
> I created an Excel macro that looks for the max number of LFs per row
> and adjusts the heights accordingly
> not super, but it works...
> "bugfish" wrote:
> > I have a report where the row height is allowed to grow as needed. When I
> > export this to Excel, the row hight will not adjust to the proper size.
> >
> > Help Please!
> >
> > I have read this posted by others, but I did not read a solution.
> >
> >
Wednesday, February 15, 2012
Execution penalty depending on column size
Hello!
Yesterday we were reported a problem when querying a particular item in =
a =
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID =3D V.SampleID
INNER JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and =
returns the expected result. The set of data associated to each buoy is =
=
similar in size.
A strange thing is that when the execution plan for key 470000 is =
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with =
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan t=
o =
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the =
=
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID =3D V.SampleID
LEFT JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes =
=
fine with all the buoys except that particular one? And why does the =
execution plan changes when the size of the number increases from 470000=
=
to 2922594?
Thanks for your help!
Sounds like param sniffing issues. Have a look at this document for more
details:
http://www.microsoft.com/technet/pr...005/recomp.mspx
Andrew J. Kelly SQL MVP
"Gerardo Gonzlez" <ggonzalez@.odyssey.com.uy> wrote in message
news:op.ts85v9dflva8xe@.sapereaude...
Hello!
Yesterday we were reported a problem when querying a particular item in a
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
returns the expected result. The set of data associated to each buoy is
similar in size.
A strange thing is that when the execution plan for key 470000 is
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes
fine with all the buoys except that particular one? And why does the
execution plan changes when the size of the number increases from 470000
to 2922594?
Thanks for your help!
|||Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
that pointed to parameter sniffing as a cause for degraded performance in
a query.
I want to know if it's normal for a query to change its execution plan
based on the values of it's parameters...that's what's happening here, we
have the same query with two wildly differing execution plans depending
only on a single value (that's being matched to a Primary key).
Many thanks for your help,
Pablo
--
I'm glad my Mom was pro-life.
Pablo Montilla
www.odyssey.com.uy|||Not sure if batch queries can be 'forcibly recompiled' for each execution.
If you put the code into a sproc, you CAN do this, using the WITH RECOMPILE
option. This will ensure a fresh query plan is generated for every call and
is a normal action to respond to varying parameter value performance issues.
TheSQLGuru
President
Indicium Resources, Inc.
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||In addition to the other replies: make sure the index statistics are up
to date. When in doubt, update them WITH FULLSCAN.
Gert-Jan
Gerardo Gonzlez wrote:
> Hello!
> Yesterday we were reported a problem when querying a particular item in a
> table:
> SELECT * FROM [Parameter]
> WHERE Parameter.ParameterID IN (
> SELECT DISTINCT V.ParameterID
> FROM Sample AS S
> INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
> INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> )
> The preceding query times out and never returns a value.
> When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
> returns the expected result. The set of data associated to each buoy is
> similar in size.
> A strange thing is that when the execution plan for key 470000 is
> DIFFERENT from the execution plan for 2922594.
> Another curious behaviour is that when the query is executed with
> Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
> the 470000 case.
> When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
> database, the execution plan was equal to the pathological case.
> We replaced the preceding query for
> SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
> SELECT V.ParameterID
> FROM Sample AS S
> LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
> LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> GROUP BY V.ParameterID
> )
> which works in all the situations and with improved performance.
> Can you help us on what is happening with the first query that executes
> fine with all the buoys except that particular one? And why does the
> execution plan changes when the size of the number increases from 470000
> to 2922594?
> Thanks for your help!
|||The section on Parameter sniffing makes it clear that the plan is based on
the value you pass to the sp the first time it is run or the first time it
is marked for recompiles. So if one value works best as a scan because it
returns many rows the plan will be bad for one that returns only a few rows
and visa versa. There must be more to this than you say because the
optimizer would know there is only one row that matches a PK and would
produce the same query each time regardless of the value. Is there a PK
constraint on the PK column? Have statistics been disabled for that column
or table? I think we need to see the actual query along with the DDL for
the tables accessed.
Andrew J. Kelly SQL MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||I'll check those things with Gerardo and reply you.
Many thanks for your answer!
Regards,
Pablo
--
I've fallen... and can't BOOT up!
Pablo Montilla
www.odyssey.com.uy
Yesterday we were reported a problem when querying a particular item in =
a =
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID =3D V.SampleID
INNER JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and =
returns the expected result. The set of data associated to each buoy is =
=
similar in size.
A strange thing is that when the execution plan for key 470000 is =
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with =
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan t=
o =
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the =
=
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID =3D V.SampleID
LEFT JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes =
=
fine with all the buoys except that particular one? And why does the =
execution plan changes when the size of the number increases from 470000=
=
to 2922594?
Thanks for your help!
details:
http://www.microsoft.com/technet/pr...005/recomp.mspx
Andrew J. Kelly SQL MVP
"Gerardo Gonzlez" <ggonzalez@.odyssey.com.uy> wrote in message
news:op.ts85v9dflva8xe@.sapereaude...
Hello!
Yesterday we were reported a problem when querying a particular item in a
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
returns the expected result. The set of data associated to each buoy is
similar in size.
A strange thing is that when the execution plan for key 470000 is
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes
fine with all the buoys except that particular one? And why does the
execution plan changes when the size of the number increases from 470000
to 2922594?
Thanks for your help!
that pointed to parameter sniffing as a cause for degraded performance in
a query.
I want to know if it's normal for a query to change its execution plan
based on the values of it's parameters...that's what's happening here, we
have the same query with two wildly differing execution plans depending
only on a single value (that's being matched to a Primary key).
Many thanks for your help,
Pablo
--
I'm glad my Mom was pro-life.
Pablo Montilla
www.odyssey.com.uy|||Not sure if batch queries can be 'forcibly recompiled' for each execution.
If you put the code into a sproc, you CAN do this, using the WITH RECOMPILE
option. This will ensure a fresh query plan is generated for every call and
is a normal action to respond to varying parameter value performance issues.
TheSQLGuru
President
Indicium Resources, Inc.
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||In addition to the other replies: make sure the index statistics are up
to date. When in doubt, update them WITH FULLSCAN.
Gert-Jan
Gerardo Gonzlez wrote:
> Hello!
> Yesterday we were reported a problem when querying a particular item in a
> table:
> SELECT * FROM [Parameter]
> WHERE Parameter.ParameterID IN (
> SELECT DISTINCT V.ParameterID
> FROM Sample AS S
> INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
> INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> )
> The preceding query times out and never returns a value.
> When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
> returns the expected result. The set of data associated to each buoy is
> similar in size.
> A strange thing is that when the execution plan for key 470000 is
> DIFFERENT from the execution plan for 2922594.
> Another curious behaviour is that when the query is executed with
> Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
> the 470000 case.
> When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
> database, the execution plan was equal to the pathological case.
> We replaced the preceding query for
> SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
> SELECT V.ParameterID
> FROM Sample AS S
> LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
> LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> GROUP BY V.ParameterID
> )
> which works in all the situations and with improved performance.
> Can you help us on what is happening with the first query that executes
> fine with all the buoys except that particular one? And why does the
> execution plan changes when the size of the number increases from 470000
> to 2922594?
> Thanks for your help!
the value you pass to the sp the first time it is run or the first time it
is marked for recompiles. So if one value works best as a scan because it
returns many rows the plan will be bad for one that returns only a few rows
and visa versa. There must be more to this than you say because the
optimizer would know there is only one row that matches a PK and would
produce the same query each time regardless of the value. Is there a PK
constraint on the PK column? Have statistics been disabled for that column
or table? I think we need to see the actual query along with the DDL for
the tables accessed.
Andrew J. Kelly SQL MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||I'll check those things with Gerardo and reply you.
Many thanks for your answer!
Regards,
Pablo
--
I've fallen... and can't BOOT up!
Pablo Montilla
www.odyssey.com.uy
Labels:
91parameterwhere,
column,
database,
depending,
execution,
helloyesterday,
item,
microsoft,
mysql,
oracle,
parameter,
parameterid,
particular,
penalty,
querying,
reported,
server,
size,
sql,
tableselect
Execution penalty depending on column size
Hello!
Yesterday we were reported a problem when querying a particular item in = a =
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID =3D V.SampleID
INNER JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and =
returns the expected result. The set of data associated to each buoy is = =
similar in size.
A strange thing is that when the execution plan for key 470000 is =
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with =
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan t= o =
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the = =
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID =3D V.SampleID
LEFT JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes = =
fine with all the buoys except that particular one? And why does the =
execution plan changes when the size of the number increases from 470000= =
to 2922594?
Thanks for your help! :)Sounds like param sniffing issues. Have a look at this document for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
"Gerardo González" <ggonzalez@.odyssey.com.uy> wrote in message
news:op.ts85v9dflva8xe@.sapereaude...
Hello!
Yesterday we were reported a problem when querying a particular item in a
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
returns the expected result. The set of data associated to each buoy is
similar in size.
A strange thing is that when the execution plan for key 470000 is
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes
fine with all the buoys except that particular one? And why does the
execution plan changes when the size of the number increases from 470000
to 2922594?
Thanks for your help! :)|||Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
that pointed to parameter sniffing as a cause for degraded performance in
a query.
I want to know if it's normal for a query to change its execution plan
based on the values of it's parameters...that's what's happening here, we
have the same query with two wildly differing execution plans depending
only on a single value (that's being matched to a Primary key).
Many thanks for your help,
Pablo
--
I'm glad my Mom was pro-life.
Pablo Montilla
www.odyssey.com.uy|||Not sure if batch queries can be 'forcibly recompiled' for each execution.
If you put the code into a sproc, you CAN do this, using the WITH RECOMPILE
option. This will ensure a fresh query plan is generated for every call and
is a normal action to respond to varying parameter value performance issues.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||In addition to the other replies: make sure the index statistics are up
to date. When in doubt, update them WITH FULLSCAN.
Gert-Jan
Gerardo González wrote:
> Hello!
> Yesterday we were reported a problem when querying a particular item in a
> table:
> SELECT * FROM [Parameter]
> WHERE Parameter.ParameterID IN (
> SELECT DISTINCT V.ParameterID
> FROM Sample AS S
> INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
> INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> )
> The preceding query times out and never returns a value.
> When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
> returns the expected result. The set of data associated to each buoy is
> similar in size.
> A strange thing is that when the execution plan for key 470000 is
> DIFFERENT from the execution plan for 2922594.
> Another curious behaviour is that when the query is executed with
> Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
> the 470000 case.
> When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
> database, the execution plan was equal to the pathological case.
> We replaced the preceding query for
> SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
> SELECT V.ParameterID
> FROM Sample AS S
> LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
> LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> GROUP BY V.ParameterID
> )
> which works in all the situations and with improved performance.
> Can you help us on what is happening with the first query that executes
> fine with all the buoys except that particular one? And why does the
> execution plan changes when the size of the number increases from 470000
> to 2922594?
> Thanks for your help! :)|||The section on Parameter sniffing makes it clear that the plan is based on
the value you pass to the sp the first time it is run or the first time it
is marked for recompiles. So if one value works best as a scan because it
returns many rows the plan will be bad for one that returns only a few rows
and visa versa. There must be more to this than you say because the
optimizer would know there is only one row that matches a PK and would
produce the same query each time regardless of the value. Is there a PK
constraint on the PK column? Have statistics been disabled for that column
or table? I think we need to see the actual query along with the DDL for
the tables accessed.
--
Andrew J. Kelly SQL MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||I'll check those things with Gerardo and reply you.
Many thanks for your answer!
Regards,
Pablo
--
I've fallen... and can't BOOT up!
Pablo Montilla
www.odyssey.com.uy
Yesterday we were reported a problem when querying a particular item in = a =
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID =3D V.SampleID
INNER JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and =
returns the expected result. The set of data associated to each buoy is = =
similar in size.
A strange thing is that when the execution plan for key 470000 is =
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with =
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan t= o =
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the = =
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID =3D V.SampleID
LEFT JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes = =
fine with all the buoys except that particular one? And why does the =
execution plan changes when the size of the number increases from 470000= =
to 2922594?
Thanks for your help! :)Sounds like param sniffing issues. Have a look at this document for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
"Gerardo González" <ggonzalez@.odyssey.com.uy> wrote in message
news:op.ts85v9dflva8xe@.sapereaude...
Hello!
Yesterday we were reported a problem when querying a particular item in a
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
returns the expected result. The set of data associated to each buoy is
similar in size.
A strange thing is that when the execution plan for key 470000 is
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes
fine with all the buoys except that particular one? And why does the
execution plan changes when the size of the number increases from 470000
to 2922594?
Thanks for your help! :)|||Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
that pointed to parameter sniffing as a cause for degraded performance in
a query.
I want to know if it's normal for a query to change its execution plan
based on the values of it's parameters...that's what's happening here, we
have the same query with two wildly differing execution plans depending
only on a single value (that's being matched to a Primary key).
Many thanks for your help,
Pablo
--
I'm glad my Mom was pro-life.
Pablo Montilla
www.odyssey.com.uy|||Not sure if batch queries can be 'forcibly recompiled' for each execution.
If you put the code into a sproc, you CAN do this, using the WITH RECOMPILE
option. This will ensure a fresh query plan is generated for every call and
is a normal action to respond to varying parameter value performance issues.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||In addition to the other replies: make sure the index statistics are up
to date. When in doubt, update them WITH FULLSCAN.
Gert-Jan
Gerardo González wrote:
> Hello!
> Yesterday we were reported a problem when querying a particular item in a
> table:
> SELECT * FROM [Parameter]
> WHERE Parameter.ParameterID IN (
> SELECT DISTINCT V.ParameterID
> FROM Sample AS S
> INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
> INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> )
> The preceding query times out and never returns a value.
> When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
> returns the expected result. The set of data associated to each buoy is
> similar in size.
> A strange thing is that when the execution plan for key 470000 is
> DIFFERENT from the execution plan for 2922594.
> Another curious behaviour is that when the query is executed with
> Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
> the 470000 case.
> When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
> database, the execution plan was equal to the pathological case.
> We replaced the preceding query for
> SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
> SELECT V.ParameterID
> FROM Sample AS S
> LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
> LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> GROUP BY V.ParameterID
> )
> which works in all the situations and with improved performance.
> Can you help us on what is happening with the first query that executes
> fine with all the buoys except that particular one? And why does the
> execution plan changes when the size of the number increases from 470000
> to 2922594?
> Thanks for your help! :)|||The section on Parameter sniffing makes it clear that the plan is based on
the value you pass to the sp the first time it is run or the first time it
is marked for recompiles. So if one value works best as a scan because it
returns many rows the plan will be bad for one that returns only a few rows
and visa versa. There must be more to this than you say because the
optimizer would know there is only one row that matches a PK and would
produce the same query each time regardless of the value. Is there a PK
constraint on the PK column? Have statistics been disabled for that column
or table? I think we need to see the actual query along with the DDL for
the tables accessed.
--
Andrew J. Kelly SQL MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||I'll check those things with Gerardo and reply you.
Many thanks for your answer!
Regards,
Pablo
--
I've fallen... and can't BOOT up!
Pablo Montilla
www.odyssey.com.uy
Subscribe to:
Posts (Atom)