I am running a trace on a set of queries. What I am noticing is that the
duration for all the individual SQL statment within the batch is minimal but
yet the total duration for the same batch completed is a lot more than if I
added the duration for individul SQL statements. So my question is where is
the remaining time coming from? How do I speed that up if I do not know what
is causing the batch to take longer time?
Thanks
Try checking out how the sql queries are being executed on the client side if possible. Usually, there are other things involved like recompilations and/or sql prepare operations and/or cursor fetch(es). ODBC calls usually do this and run sp_cursoropen, s
p_prepare, sp_cursorfetch, ..., etc... Try looking at RPC's and SQL's on the profiler... =)
Showing posts with label profiler. Show all posts
Showing posts with label profiler. Show all posts
Wednesday, March 21, 2012
question on SELECT 1 and Profiler
I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David
|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David
|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...[vbcol=seagreen]
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably
>
|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...
> driver
> "ping"
> probably
|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegro ups.com...[vbcol=seagreen]
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the
>
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David
|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David
|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...[vbcol=seagreen]
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably
>
|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...
> driver
> "ping"
> probably
|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegro ups.com...[vbcol=seagreen]
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the
>
question on SELECT 1 and Profiler
I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably[vbcol=seagreen]
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> driver
> "ping"
> probably|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the[vbcol=seagreen]
>
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably[vbcol=seagreen]
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> driver
> "ping"
> probably|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the[vbcol=seagreen]
>
question on SELECT 1 and Profiler
I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> >
> > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> > and I see a lot of SELECT 1 statements that are running longer in sql
> > 2005 than in sql 2000. what are these select 1 statements?
> >
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
> > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > >
> > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
driver
> > > and I see a lot of SELECT 1 statements that are running longer in sql
> > > 2005 than in sql 2000. what are these select 1 statements?
> > >
> >
> > SELECT 1
> >
> > Is a query that returns a single row and a single column containing the
> > value 1. It's extremely cheap, and probably being used as kind of
"ping"
> > from some application. It's such a cheap query that its cost is
probably
> > just background noise.
> >
> > David
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> > How much longer? 1,000 times longer? SQL Server 2005's profiler can
> > track time in Miliseconds or Microseconds. I believe the default is
> > microseconds. If something took 1 ms (would not have shown that level
> > of detail, but just for an example.. In 2000 I believe the lowest level
> > of detail visible was about 10 or 12 ms, one of the reasons the
> > microseconds are used, to provide much more detail over the life of a
> > query and it's trending time) in 2005 profiler under microseconds it
> > would be 1,000 (still 1 milisecond, however).
> >
> >
> > David Browne wrote:
> > > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > > >
> > > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
> driver
> > > > and I see a lot of SELECT 1 statements that are running longer in sql
> > > > 2005 than in sql 2000. what are these select 1 statements?
> > > >
> > >
> > > SELECT 1
> > >
> > > Is a query that returns a single row and a single column containing the
> > > value 1. It's extremely cheap, and probably being used as kind of
> "ping"
> > > from some application. It's such a cheap query that its cost is
> probably
> > > just background noise.
> > >
> > > David
> >|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
> > First of all, the SELECT 1 query is used by Connection Pools to maintain
> > those connections as "live" so the pool manager does not close them.
Only
> > the base connections should be executing them; otherwise, the pool would
> > never shrink.
> >
> > Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> > SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> > execute the same script on SS2K5. What are the average per execution
> > duration and the overall duration for each of these installations?
> >
> > Now, there can be environmental factors that you should be careful
about,
> > and I would prefer to run these against each instance, but on the same
> > hardware, but they should still be roughly the same. There's just not a
> > whole lot of improvements a system could make on such a simple query.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> > > How much longer? 1,000 times longer? SQL Server 2005's profiler can
> > > track time in Miliseconds or Microseconds. I believe the default is
> > > microseconds. If something took 1 ms (would not have shown that level
> > > of detail, but just for an example.. In 2000 I believe the lowest
level
> > > of detail visible was about 10 or 12 ms, one of the reasons the
> > > microseconds are used, to provide much more detail over the life of a
> > > query and it's trending time) in 2005 profiler under microseconds it
> > > would be 1,000 (still 1 milisecond, however).
> > >
> > >
> > > David Browne wrote:
> > > > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > > > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > > > >
> > > > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
> > driver
> > > > > and I see a lot of SELECT 1 statements that are running longer in
sql
> > > > > 2005 than in sql 2000. what are these select 1 statements?
> > > > >
> > > >
> > > > SELECT 1
> > > >
> > > > Is a query that returns a single row and a single column containing
the
> > > > value 1. It's extremely cheap, and probably being used as kind of
> > "ping"
> > > > from some application. It's such a cheap query that its cost is
> > probably
> > > > just background noise.
> > > >
> > > > David
> > >
>
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> >
> > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> > and I see a lot of SELECT 1 statements that are running longer in sql
> > 2005 than in sql 2000. what are these select 1 statements?
> >
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
> > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > >
> > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
driver
> > > and I see a lot of SELECT 1 statements that are running longer in sql
> > > 2005 than in sql 2000. what are these select 1 statements?
> > >
> >
> > SELECT 1
> >
> > Is a query that returns a single row and a single column containing the
> > value 1. It's extremely cheap, and probably being used as kind of
"ping"
> > from some application. It's such a cheap query that its cost is
probably
> > just background noise.
> >
> > David
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> > How much longer? 1,000 times longer? SQL Server 2005's profiler can
> > track time in Miliseconds or Microseconds. I believe the default is
> > microseconds. If something took 1 ms (would not have shown that level
> > of detail, but just for an example.. In 2000 I believe the lowest level
> > of detail visible was about 10 or 12 ms, one of the reasons the
> > microseconds are used, to provide much more detail over the life of a
> > query and it's trending time) in 2005 profiler under microseconds it
> > would be 1,000 (still 1 milisecond, however).
> >
> >
> > David Browne wrote:
> > > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > > >
> > > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
> driver
> > > > and I see a lot of SELECT 1 statements that are running longer in sql
> > > > 2005 than in sql 2000. what are these select 1 statements?
> > > >
> > >
> > > SELECT 1
> > >
> > > Is a query that returns a single row and a single column containing the
> > > value 1. It's extremely cheap, and probably being used as kind of
> "ping"
> > > from some application. It's such a cheap query that its cost is
> probably
> > > just background noise.
> > >
> > > David
> >|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
> > First of all, the SELECT 1 query is used by Connection Pools to maintain
> > those connections as "live" so the pool manager does not close them.
Only
> > the base connections should be executing them; otherwise, the pool would
> > never shrink.
> >
> > Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> > SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> > execute the same script on SS2K5. What are the average per execution
> > duration and the overall duration for each of these installations?
> >
> > Now, there can be environmental factors that you should be careful
about,
> > and I would prefer to run these against each instance, but on the same
> > hardware, but they should still be roughly the same. There's just not a
> > whole lot of improvements a system could make on such a simple query.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> > > How much longer? 1,000 times longer? SQL Server 2005's profiler can
> > > track time in Miliseconds or Microseconds. I believe the default is
> > > microseconds. If something took 1 ms (would not have shown that level
> > > of detail, but just for an example.. In 2000 I believe the lowest
level
> > > of detail visible was about 10 or 12 ms, one of the reasons the
> > > microseconds are used, to provide much more detail over the life of a
> > > query and it's trending time) in 2005 profiler under microseconds it
> > > would be 1,000 (still 1 milisecond, however).
> > >
> > >
> > > David Browne wrote:
> > > > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > > > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > > > >
> > > > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
> > driver
> > > > > and I see a lot of SELECT 1 statements that are running longer in
sql
> > > > > 2005 than in sql 2000. what are these select 1 statements?
> > > > >
> > > >
> > > > SELECT 1
> > > >
> > > > Is a query that returns a single row and a single column containing
the
> > > > value 1. It's extremely cheap, and probably being used as kind of
> > "ping"
> > > > from some application. It's such a cheap query that its cost is
> > probably
> > > > just background noise.
> > > >
> > > > David
> > >
>
Monday, March 12, 2012
Question on Plan Cache
I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss? Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!
Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss? Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>
|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss? Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!
Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss? Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>
|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
Question on Plan Cache
I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is gener
ated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan,
I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you cal
l the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you
are about to do it,
just let us know. I wouldn't be surprised if cache handling is different wit
h RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss l
ike crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in s
ys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used...
.why would I still be
> getting CacheMiss' Some of these procs are only called from one specific
webservice the same way
> every time. The usage counts on some of these are over 20k so the plan do
esn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET
> application instead of as a paramterized query? We are trying to convert
everything to
> parameterized as quickly as we can. But if this is the cause, why is ther
e a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is gener
ated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan,
I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you cal
l the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you
are about to do it,
just let us know. I wouldn't be surprised if cache handling is different wit
h RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss l
ike crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in s
ys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used...
.why would I still be
> getting CacheMiss' Some of these procs are only called from one specific
webservice the same way
> every time. The usage counts on some of these are over 20k so the plan do
esn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET
> application instead of as a paramterized query? We are trying to convert
everything to
> parameterized as quickly as we can. But if this is the cause, why is ther
e a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
Question on Plan Cache
I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss' Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> I've got something awry in one of our servers. I'm getting SP:CacheMiss
>> like crazy in Profiler. The same procs repeatedly. The strange thing is
>> that these procs are in sys.syscacheobjects and the usagecount is growing
>> regularly so the cached plans are being used....why would I still be
>> getting CacheMiss' Some of these procs are only called from one
>> specific webservice the same way every time. The usage counts on some of
>> these are over 20k so the plan doesn't seem to be clearing it just
>> doesn't seem to be getting used every time...
>> Is it because the are being executed as "exec procname parm1, parm2,..."
>> from the .NET application instead of as a paramterized query? We are
>> trying to convert everything to parameterized as quickly as we can. But
>> if this is the cause, why is there a plan that *IS* getting used?
>> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
>> I don't believe should be happening.
>> Thanks in advance!!
>
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is generated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan, I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you call the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you are about to do it,
just let us know. I wouldn't be surprised if cache handling is different with RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss like crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in sys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used....why would I still be
> getting CacheMiss' Some of these procs are only called from one specific webservice the same way
> every time. The usage counts on some of these are over 20k so the plan doesn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..." from the .NET
> application instead of as a paramterized query? We are trying to convert everything to
> parameterized as quickly as we can. But if this is the cause, why is there a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> I've got something awry in one of our servers. I'm getting SP:CacheMiss
>> like crazy in Profiler. The same procs repeatedly. The strange thing is
>> that these procs are in sys.syscacheobjects and the usagecount is growing
>> regularly so the cached plans are being used....why would I still be
>> getting CacheMiss' Some of these procs are only called from one
>> specific webservice the same way every time. The usage counts on some of
>> these are over 20k so the plan doesn't seem to be clearing it just
>> doesn't seem to be getting used every time...
>> Is it because the are being executed as "exec procname parm1, parm2,..."
>> from the .NET application instead of as a paramterized query? We are
>> trying to convert everything to parameterized as quickly as we can. But
>> if this is the cause, why is there a plan that *IS* getting used?
>> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
>> I don't believe should be happening.
>> Thanks in advance!!
>
Subscribe to:
Posts (Atom)