Query governor problem

Hi all,
We're running MS SQL 2000, latest service pack. Windows 2000 server,
advanced server and Windows 2003 server. Assorted hardware.
The above configuration refers to various client sites, and the SQL
we're having problems with is generated by an object/relational
mapping layer so we cannot easily change this.
Our problem is that certain queries are occasionally take far, far
longer to execute than at other times. My suspicion is that this
occurs when the database server is heavily loaded, and this causes it
to pick an alternative, and awful, execution plan. This only makes
things worse for everyone else. The query is given at the end of this
post.
The query will normally execute in less than a second. I can copy it
directly out of the profiler, which shows it is taking 1280 seconds of
cpu time and about 4500 seconds duration, paste it into the query
analyzer of the correct database, and a short while later have it
execute in a flash.
Statistics have very recently been updated. The data will not have
changed significantly either.
Over the next few days and we will be turning off intra query
parallelism and hyperthreading because we know these have caused us
problems in the past and may very well be the cause of things here,
but in the mean time as a very short term measure I have considered
using the query governor.
Unfortunately I don't think this will be the solution. I suspect that
the cpu estimate used by the query governor will always be for the
sub-second execution times (because if the query plan realised it
would take 20 minutes of cpu time to execute the query then it would
certainly have chosen something better, which it clearly can under the
right circumstances. If the query planner fails this way, and the
query governor is presumably driven by the estimate of the query
planner, then the query Governor will not catch these excessive
execution times). I'm pretty sure that the query governor only works
by estimates and does not actually 'watch over' the query and count
the cpu ticks.
So it looks like this will not help. This is a critical issue for our
customers. I'm therefore looking for some way of observing other
queries from a special watchdog job and killing them if the cpu
exceeds a certain amount, say a minute.
I have to look for single queries, not connections as many users are
serialised down to a few connections. Using the enterprise manager's
'process info' page I can see per-connection info but this it is not
fine-grained enough for what I want. I've discovered the sysperfinfo
table but that doesn't seem to help.
Any suggestions?
I suppose it would be better than nothing to be able to watch over
connections and if I could somehow detect excessive cpu activity on
that then I could kill that connection, even if it made a small group
of users unhappy. Better that than the whole site irate. Can anyone
suggest reliable way of doing that? How would I distinguish a very
busy but correctly functioning connection from one which was tied up
too long with a single query?
For what it's worth, when these mad queries start to execute their
lock-up the database server so tightly that it's almost impossible to
get in to kill things manually.
The query:
SELECT A14, A15, A16, A17, A18, A19, A20, A21, A22, A23
FROM (
SELECT <field names removed for confidentiality
reasons>
FROM <ditto table name> T13
) D24
WHERE ((A18 IN (21799))
AND (A17 IN (13575))
)
OR ((A21 IN (2130740, 2413061, 2199038, 2107888,
2147294, 2390317, 2390947, 2262791,
2262540, 2175261, 2436256, 2163102,
2119434, 2272644, 2356412, 2240713,
2282838, 2318792, 2333253, 2230674,
2083129, 2402966, 2367886, 2187455,
2297886, 2251478, 2069408, 2309686,
2450636, 2424679, 2061268, 2219841
)
)
AND (A17 IN (18583))
)
OR ((A18 IN (2130740, 2413061, 2199038, 2107888,
2147294, 2390317, 2390947, 2262791,
2262540, 2175261, 2436256, 2163102,
2119434, 2272644, 2356412, 2240713,
2282838, 2318792, 2333253, 2230674,
2083129, 2402966, 2367886, 2187455,
2297886, 2251478, 2069408, 2309686,
2450636, 2424679, 2061268, 2219841
)
)
AND (A17 IN (14849, 15468))
)
OR ((A21 IN (2129425, 2412043, 2197272, 2106911,
2146319, 2389802, 2260770, 2173489,
2434610, 2161990, 2117737, 2271342,
2355571, 2239606, 2282111, 2316669,
2332031, 2229624, 2081664, 2401161,
2366604, 2186416, 2296450, 2250431,
2067383, 2308778, 2450673, 2423002,
2061282, 2218767
)
)
AND (A17 IN (18583))
)
OR ((A18 IN (2129425, 2412043, 2197272, 2106911,
2146319, 2389802, 2260770, 2173489,
2434610, 2161990, 2117737, 2271342,
2355571, 2239606, 2282111, 2316669,
2332031, 2229624, 2081664, 2401161,
2366604, 2186416, 2296450, 2250431,
2067383, 2308778, 2450673, 2423002,
2061282, 2218767
)
)
AND (A17 IN (14849, 15468))
)
OR ((A18 IN (75300, 84834, 77226, 74551, 75693,
83958, 79587, 76442, 85713, 76074,
74904, 79967, 82740, 78787, 80387,
81446, 81966, 78422, 73758, 84374,
83124, 76859, 80771, 79203, 81187,
86351, 85251, 73040, 78012
)
)
AND (A17 IN (15818, 15790))
)
OR ((A18 IN (6434, 6835, 6511, 6652, 7338, 7381,
7516, 7053, 8076, 6751, 7212, 8346,
5951
)
)
AND (A17 IN (15862))
)
OR ((A18 IN (88417, 102177, 91248, 87350, 88994,
101065, 94706, 90093, 103296, 86807,
89570, 87850, 95275, 99386, 93535,
95910, 92699, 97459, 98251, 92997,
86154, 101590, 99922, 90709, 96465,
94143, 85304, 97084, 99933, 87091,
104110, 102719, 84897, 100531, 92404
)
)
AND (A17 IN (38577, 37251, 12998))
)
OR ((A18 IN (6014, 6357, 6075, 6196, 6790, 6829,
6950, 6542, 7442, 6277, 6671, 7689,
5614
)
)
AND (A17 IN (15847))
)
OR ((A18 IN (21799))
AND (A17 IN (14519))
)
FYI I have seen queries like this cause problems in the past, and on a
single processor machines. It appears that large combinations of
'and's and 'or's can cause big problems with the query planner,
possibly made worse if the statistics read out of date (which they are
not here).
Any help appreciated
cheers
jan
Jan,
AFIK, you're correct, the query governor works with estimated cost, not
actual.
Can you tell for certain that the runaway queries are parallelized, i.e.
using sp_who2 and looking for multiple rows per spid?
If bad parallel plans are the issue, then lowering Max DOP to 4 or 1 might
help, as it looks like you'll be testing.
Can you capture the query plan in Profiler and compare a slow version's plan
with a fast query's plan in QA?
That would help verify that a bad plan is truly the cause.
The only caveat I've heard about hyperthreading is not to mix it with
affinitizing.
Some other things to try:
Look for issues around waits and queues, using DBCC SQLPERF(waitstats). Find
out what the slow queries are waiting on.
Inspect the procedure cache (syscacheobjects) to determine whether these
plans are being reused.
There could be pressure on the cache if they are dynamically generated and
can't be reused.
Also check on the cache plan size to see if it's excessively large. I've
seen very large plans hose up a system.
For more info about waits and queues, and syscacheobjects, see the articles
in SQL Magazine by Tom Davidson.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"jan" <anomalocarus@.hotmail.com> wrote in message
news:62da4c70.0409160107.3f7d305c@.posting.google.c om...
> Hi all,
> We're running MS SQL 2000, latest service pack. Windows 2000 server,
> advanced server and Windows 2003 server. Assorted hardware.
> The above configuration refers to various client sites, and the SQL
> we're having problems with is generated by an object/relational
> mapping layer so we cannot easily change this.
> Our problem is that certain queries are occasionally take far, far
> longer to execute than at other times. My suspicion is that this
> occurs when the database server is heavily loaded, and this causes it
> to pick an alternative, and awful, execution plan. This only makes
> things worse for everyone else. The query is given at the end of this
> post.
> The query will normally execute in less than a second. I can copy it
> directly out of the profiler, which shows it is taking 1280 seconds of
> cpu time and about 4500 seconds duration, paste it into the query
> analyzer of the correct database, and a short while later have it
> execute in a flash.
> Statistics have very recently been updated. The data will not have
> changed significantly either.
> Over the next few days and we will be turning off intra query
> parallelism and hyperthreading because we know these have caused us
> problems in the past and may very well be the cause of things here,
> but in the mean time as a very short term measure I have considered
> using the query governor.
> Unfortunately I don't think this will be the solution. I suspect that
> the cpu estimate used by the query governor will always be for the
> sub-second execution times (because if the query plan realised it
> would take 20 minutes of cpu time to execute the query then it would
> certainly have chosen something better, which it clearly can under the
> right circumstances. If the query planner fails this way, and the
> query governor is presumably driven by the estimate of the query
> planner, then the query Governor will not catch these excessive
> execution times). I'm pretty sure that the query governor only works
> by estimates and does not actually 'watch over' the query and count
> the cpu ticks.
> So it looks like this will not help. This is a critical issue for our
> customers. I'm therefore looking for some way of observing other
> queries from a special watchdog job and killing them if the cpu
> exceeds a certain amount, say a minute.
> I have to look for single queries, not connections as many users are
> serialised down to a few connections. Using the enterprise manager's
> 'process info' page I can see per-connection info but this it is not
> fine-grained enough for what I want. I've discovered the sysperfinfo
> table but that doesn't seem to help.
> Any suggestions?
> I suppose it would be better than nothing to be able to watch over
> connections and if I could somehow detect excessive cpu activity on
> that then I could kill that connection, even if it made a small group
> of users unhappy. Better that than the whole site irate. Can anyone
> suggest reliable way of doing that? How would I distinguish a very
> busy but correctly functioning connection from one which was tied up
> too long with a single query?
> For what it's worth, when these mad queries start to execute their
> lock-up the database server so tightly that it's almost impossible to
> get in to kill things manually.
> The query:
> SELECT A14, A15, A16, A17, A18, A19, A20, A21, A22, A23
> FROM (
> SELECT <field names removed for confidentiality
> reasons>
> FROM <ditto table name> T13
> ) D24
> WHERE ((A18 IN (21799))
> AND (A17 IN (13575))
> )
> OR ((A21 IN (2130740, 2413061, 2199038, 2107888,
> 2147294, 2390317, 2390947, 2262791,
> 2262540, 2175261, 2436256, 2163102,
> 2119434, 2272644, 2356412, 2240713,
> 2282838, 2318792, 2333253, 2230674,
> 2083129, 2402966, 2367886, 2187455,
> 2297886, 2251478, 2069408, 2309686,
> 2450636, 2424679, 2061268, 2219841
> )
> )
> AND (A17 IN (18583))
> )
> OR ((A18 IN (2130740, 2413061, 2199038, 2107888,
> 2147294, 2390317, 2390947, 2262791,
> 2262540, 2175261, 2436256, 2163102,
> 2119434, 2272644, 2356412, 2240713,
> 2282838, 2318792, 2333253, 2230674,
> 2083129, 2402966, 2367886, 2187455,
> 2297886, 2251478, 2069408, 2309686,
> 2450636, 2424679, 2061268, 2219841
> )
> )
> AND (A17 IN (14849, 15468))
> )
> OR ((A21 IN (2129425, 2412043, 2197272, 2106911,
> 2146319, 2389802, 2260770, 2173489,
> 2434610, 2161990, 2117737, 2271342,
> 2355571, 2239606, 2282111, 2316669,
> 2332031, 2229624, 2081664, 2401161,
> 2366604, 2186416, 2296450, 2250431,
> 2067383, 2308778, 2450673, 2423002,
> 2061282, 2218767
> )
> )
> AND (A17 IN (18583))
> )
> OR ((A18 IN (2129425, 2412043, 2197272, 2106911,
> 2146319, 2389802, 2260770, 2173489,
> 2434610, 2161990, 2117737, 2271342,
> 2355571, 2239606, 2282111, 2316669,
> 2332031, 2229624, 2081664, 2401161,
> 2366604, 2186416, 2296450, 2250431,
> 2067383, 2308778, 2450673, 2423002,
> 2061282, 2218767
> )
> )
> AND (A17 IN (14849, 15468))
> )
> OR ((A18 IN (75300, 84834, 77226, 74551, 75693,
> 83958, 79587, 76442, 85713, 76074,
> 74904, 79967, 82740, 78787, 80387,
> 81446, 81966, 78422, 73758, 84374,
> 83124, 76859, 80771, 79203, 81187,
> 86351, 85251, 73040, 78012
> )
> )
> AND (A17 IN (15818, 15790))
> )
> OR ((A18 IN (6434, 6835, 6511, 6652, 7338, 7381,
> 7516, 7053, 8076, 6751, 7212, 8346,
> 5951
> )
> )
> AND (A17 IN (15862))
> )
> OR ((A18 IN (88417, 102177, 91248, 87350, 88994,
> 101065, 94706, 90093, 103296, 86807,
> 89570, 87850, 95275, 99386, 93535,
> 95910, 92699, 97459, 98251, 92997,
> 86154, 101590, 99922, 90709, 96465,
> 94143, 85304, 97084, 99933, 87091,
> 104110, 102719, 84897, 100531, 92404
> )
> )
> AND (A17 IN (38577, 37251, 12998))
> )
> OR ((A18 IN (6014, 6357, 6075, 6196, 6790, 6829,
> 6950, 6542, 7442, 6277, 6671, 7689,
> 5614
> )
> )
> AND (A17 IN (15847))
> )
> OR ((A18 IN (21799))
> AND (A17 IN (14519))
> )
> FYI I have seen queries like this cause problems in the past, and on a
> single processor machines. It appears that large combinations of
> 'and's and 'or's can cause big problems with the query planner,
> possibly made worse if the statistics read out of date (which they are
> not here).
> Any help appreciated
> cheers
> jan
|||Hi Ron,
Thanks for your response, sorry for the delay.
Just disabling intra-query parallelism seemed to do the trick -- the
client has needed no reboots since, and my tracking of the cpu use
shows a much more reasonable (ie. far less 'spiky') behaviour.
While this proves nothing, it is certainly strong evidence that the
IQP was the problem. I was not able to get any different query plans
under different loads on a test server, and when the client was having
problems the database server locked up *tight* -- even logging in
locally (ie. by a person on site) could take 5 or 10 minutes. Even
starting a profiler was almost impossible. Checking query plans or
blocking etc. was not feasible. Not a good situation for debugging.
The warning about disabling hyperthreading/SMT was given to us
verbally by one of the technical support engineers (bless them,
they're wonderful) but he was quite clear.
The SQL is dynamically generated, and currently they are very
un-reusable. They can also be very large, and I have absolutely no
doubt that there is plenty of pressure being put on the procedure
cache. We'll deal with that in a release or two.
I will definitely follow up that article you recommended.
Thanks again
jan
"Ron Talmage" <rtalmage@.prospice.com> wrote in message news:<eysruLAnEHA.3876@.TK2MSFTNGP15.phx.gbl>...[vbcol=seagreen]
> Jan,
> AFIK, you're correct, the query governor works with estimated cost, not
> actual.
> Can you tell for certain that the runaway queries are parallelized, i.e.
> using sp_who2 and looking for multiple rows per spid?
> If bad parallel plans are the issue, then lowering Max DOP to 4 or 1 might
> help, as it looks like you'll be testing.
> Can you capture the query plan in Profiler and compare a slow version's plan
> with a fast query's plan in QA?
> That would help verify that a bad plan is truly the cause.
> The only caveat I've heard about hyperthreading is not to mix it with
> affinitizing.
> Some other things to try:
> Look for issues around waits and queues, using DBCC SQLPERF(waitstats). Find
> out what the slow queries are waiting on.
> Inspect the procedure cache (syscacheobjects) to determine whether these
> plans are being reused.
> There could be pressure on the cache if they are dynamically generated and
> can't be reused.
> Also check on the cache plan size to see if it's excessively large. I've
> seen very large plans hose up a system.
> For more info about waits and queues, and syscacheobjects, see the articles
> in SQL Magazine by Tom Davidson.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "jan" <anomalocarus@.hotmail.com> wrote in message
> news:62da4c70.0409160107.3f7d305c@.posting.google.c om...
[snip orig post]

Query governor problem

Hi all,
We're running MS SQL 2000, latest service pack. Windows 2000 server,
advanced server and Windows 2003 server. Assorted hardware.
The above configuration refers to various client sites, and the SQL
we're having problems with is generated by an object/relational
mapping layer so we cannot easily change this.
Our problem is that certain queries are occasionally take far, far
longer to execute than at other times. My suspicion is that this
occurs when the database server is heavily loaded, and this causes it
to pick an alternative, and awful, execution plan. This only makes
things worse for everyone else. The query is given at the end of this
post.
The query will normally execute in less than a second. I can copy it
directly out of the profiler, which shows it is taking 1280 seconds of
cpu time and about 4500 seconds duration, paste it into the query
analyzer of the correct database, and a short while later have it
execute in a flash.
Statistics have very recently been updated. The data will not have
changed significantly either.
Over the next few days and we will be turning off intra query
parallelism and hyperthreading because we know these have caused us
problems in the past and may very well be the cause of things here,
but in the mean time as a very short term measure I have considered
using the query governor.
Unfortunately I don't think this will be the solution. I suspect that
the cpu estimate used by the query governor will always be for the
sub-second execution times (because if the query plan realised it
would take 20 minutes of cpu time to execute the query then it would
certainly have chosen something better, which it clearly can under the
right circumstances. If the query planner fails this way, and the
query governor is presumably driven by the estimate of the query
planner, then the query Governor will not catch these excessive
execution times). I'm pretty sure that the query governor only works
by estimates and does not actually 'watch over' the query and count
the cpu ticks.
So it looks like this will not help. This is a critical issue for our
customers. I'm therefore looking for some way of observing other
queries from a special watchdog job and killing them if the cpu
exceeds a certain amount, say a minute.
I have to look for single queries, not connections as many users are
serialised down to a few connections. Using the enterprise manager's
'process info' page I can see per-connection info but this it is not
fine-grained enough for what I want. I've discovered the sysperfinfo
table but that doesn't seem to help.
Any suggestions?
I suppose it would be better than nothing to be able to watch over
connections and if I could somehow detect excessive cpu activity on
that then I could kill that connection, even if it made a small group
of users unhappy. Better that than the whole site irate. Can anyone
suggest reliable way of doing that? How would I distinguish a very
busy but correctly functioning connection from one which was tied up
too long with a single query?
For what it's worth, when these mad queries start to execute their
lock-up the database server so tightly that it's almost impossible to
get in to kill things manually.
The query:
SELECT A14, A15, A16, A17, A18, A19, A20, A21, A22, A23
FROM (
SELECT <field names removed for confidentiality
reasons>
FROM <ditto table name> T13
) D24
WHERE ((A18 IN (21799))
AND (A17 IN (13575))
)
OR ((A21 IN (2130740, 2413061, 2199038, 2107888,
2147294, 2390317, 2390947, 2262791,
2262540, 2175261, 2436256, 2163102,
2119434, 2272644, 2356412, 2240713,
2282838, 2318792, 2333253, 2230674,
2083129, 2402966, 2367886, 2187455,
2297886, 2251478, 2069408, 2309686,
2450636, 2424679, 2061268, 2219841
)
)
AND (A17 IN (18583))
)
OR ((A18 IN (2130740, 2413061, 2199038, 2107888,
2147294, 2390317, 2390947, 2262791,
2262540, 2175261, 2436256, 2163102,
2119434, 2272644, 2356412, 2240713,
2282838, 2318792, 2333253, 2230674,
2083129, 2402966, 2367886, 2187455,
2297886, 2251478, 2069408, 2309686,
2450636, 2424679, 2061268, 2219841
)
)
AND (A17 IN (14849, 15468))
)
OR ((A21 IN (2129425, 2412043, 2197272, 2106911,
2146319, 2389802, 2260770, 2173489,
2434610, 2161990, 2117737, 2271342,
2355571, 2239606, 2282111, 2316669,
2332031, 2229624, 2081664, 2401161,
2366604, 2186416, 2296450, 2250431,
2067383, 2308778, 2450673, 2423002,
2061282, 2218767
)
)
AND (A17 IN (18583))
)
OR ((A18 IN (2129425, 2412043, 2197272, 2106911,
2146319, 2389802, 2260770, 2173489,
2434610, 2161990, 2117737, 2271342,
2355571, 2239606, 2282111, 2316669,
2332031, 2229624, 2081664, 2401161,
2366604, 2186416, 2296450, 2250431,
2067383, 2308778, 2450673, 2423002,
2061282, 2218767
)
)
AND (A17 IN (14849, 15468))
)
OR ((A18 IN (75300, 84834, 77226, 74551, 75693,
83958, 79587, 76442, 85713, 76074,
74904, 79967, 82740, 78787, 80387,
81446, 81966, 78422, 73758, 84374,
83124, 76859, 80771, 79203, 81187,
86351, 85251, 73040, 78012
)
)
AND (A17 IN (15818, 15790))
)
OR ((A18 IN (6434, 6835, 6511, 6652, 7338, 7381,
7516, 7053, 8076, 6751, 7212, 8346,
5951
)
)
AND (A17 IN (15862))
)
OR ((A18 IN (88417, 102177, 91248, 87350, 88994,
101065, 94706, 90093, 103296, 86807,
89570, 87850, 95275, 99386, 93535,
95910, 92699, 97459, 98251, 92997,
86154, 101590, 99922, 90709, 96465,
94143, 85304, 97084, 99933, 87091,
104110, 102719, 84897, 100531, 92404
)
)
AND (A17 IN (38577, 37251, 12998))
)
OR ((A18 IN (6014, 6357, 6075, 6196, 6790, 6829,
6950, 6542, 7442, 6277, 6671, 7689,
5614
)
)
AND (A17 IN (15847))
)
OR ((A18 IN (21799))
AND (A17 IN (14519))
)
FYI I have seen queries like this cause problems in the past, and on a
single processor machines. It appears that large combinations of
'and's and 'or's can cause big problems with the query planner,
possibly made worse if the statistics read out of date (which they are
not here).
Any help appreciated
cheers
janJan,
AFIK, you're correct, the query governor works with estimated cost, not
actual.
Can you tell for certain that the runaway queries are parallelized, i.e.
using sp_who2 and looking for multiple rows per spid?
If bad parallel plans are the issue, then lowering Max DOP to 4 or 1 might
help, as it looks like you'll be testing.
Can you capture the query plan in Profiler and compare a slow version's plan
with a fast query's plan in QA?
That would help verify that a bad plan is truly the cause.
The only caveat I've heard about hyperthreading is not to mix it with
affinitizing.
Some other things to try:
Look for issues around waits and queues, using DBCC SQLPERF(waitstats). Find
out what the slow queries are waiting on.
Inspect the procedure cache (syscacheobjects) to determine whether these
plans are being reused.
There could be pressure on the cache if they are dynamically generated and
can't be reused.
Also check on the cache plan size to see if it's excessively large. I've
seen very large plans hose up a system.
For more info about waits and queues, and syscacheobjects, see the articles
in SQL Magazine by Tom Davidson.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"jan" <anomalocarus@.hotmail.com> wrote in message
news:62da4c70.0409160107.3f7d305c@.posting.google.com...
> Hi all,
> We're running MS SQL 2000, latest service pack. Windows 2000 server,
> advanced server and Windows 2003 server. Assorted hardware.
> The above configuration refers to various client sites, and the SQL
> we're having problems with is generated by an object/relational
> mapping layer so we cannot easily change this.
> Our problem is that certain queries are occasionally take far, far
> longer to execute than at other times. My suspicion is that this
> occurs when the database server is heavily loaded, and this causes it
> to pick an alternative, and awful, execution plan. This only makes
> things worse for everyone else. The query is given at the end of this
> post.
> The query will normally execute in less than a second. I can copy it
> directly out of the profiler, which shows it is taking 1280 seconds of
> cpu time and about 4500 seconds duration, paste it into the query
> analyzer of the correct database, and a short while later have it
> execute in a flash.
> Statistics have very recently been updated. The data will not have
> changed significantly either.
> Over the next few days and we will be turning off intra query
> parallelism and hyperthreading because we know these have caused us
> problems in the past and may very well be the cause of things here,
> but in the mean time as a very short term measure I have considered
> using the query governor.
> Unfortunately I don't think this will be the solution. I suspect that
> the cpu estimate used by the query governor will always be for the
> sub-second execution times (because if the query plan realised it
> would take 20 minutes of cpu time to execute the query then it would
> certainly have chosen something better, which it clearly can under the
> right circumstances. If the query planner fails this way, and the
> query governor is presumably driven by the estimate of the query
> planner, then the query Governor will not catch these excessive
> execution times). I'm pretty sure that the query governor only works
> by estimates and does not actually 'watch over' the query and count
> the cpu ticks.
> So it looks like this will not help. This is a critical issue for our
> customers. I'm therefore looking for some way of observing other
> queries from a special watchdog job and killing them if the cpu
> exceeds a certain amount, say a minute.
> I have to look for single queries, not connections as many users are
> serialised down to a few connections. Using the enterprise manager's
> 'process info' page I can see per-connection info but this it is not
> fine-grained enough for what I want. I've discovered the sysperfinfo
> table but that doesn't seem to help.
> Any suggestions?
> I suppose it would be better than nothing to be able to watch over
> connections and if I could somehow detect excessive cpu activity on
> that then I could kill that connection, even if it made a small group
> of users unhappy. Better that than the whole site irate. Can anyone
> suggest reliable way of doing that? How would I distinguish a very
> busy but correctly functioning connection from one which was tied up
> too long with a single query?
> For what it's worth, when these mad queries start to execute their
> lock-up the database server so tightly that it's almost impossible to
> get in to kill things manually.
> The query:
> SELECT A14, A15, A16, A17, A18, A19, A20, A21, A22, A23
> FROM (
> SELECT <field names removed for confidentiality
> reasons>
> FROM <ditto table name> T13
> ) D24
> WHERE ((A18 IN (21799))
> AND (A17 IN (13575))
> )
> OR ((A21 IN (2130740, 2413061, 2199038, 2107888,
> 2147294, 2390317, 2390947, 2262791,
> 2262540, 2175261, 2436256, 2163102,
> 2119434, 2272644, 2356412, 2240713,
> 2282838, 2318792, 2333253, 2230674,
> 2083129, 2402966, 2367886, 2187455,
> 2297886, 2251478, 2069408, 2309686,
> 2450636, 2424679, 2061268, 2219841
> )
> )
> AND (A17 IN (18583))
> )
> OR ((A18 IN (2130740, 2413061, 2199038, 2107888,
> 2147294, 2390317, 2390947, 2262791,
> 2262540, 2175261, 2436256, 2163102,
> 2119434, 2272644, 2356412, 2240713,
> 2282838, 2318792, 2333253, 2230674,
> 2083129, 2402966, 2367886, 2187455,
> 2297886, 2251478, 2069408, 2309686,
> 2450636, 2424679, 2061268, 2219841
> )
> )
> AND (A17 IN (14849, 15468))
> )
> OR ((A21 IN (2129425, 2412043, 2197272, 2106911,
> 2146319, 2389802, 2260770, 2173489,
> 2434610, 2161990, 2117737, 2271342,
> 2355571, 2239606, 2282111, 2316669,
> 2332031, 2229624, 2081664, 2401161,
> 2366604, 2186416, 2296450, 2250431,
> 2067383, 2308778, 2450673, 2423002,
> 2061282, 2218767
> )
> )
> AND (A17 IN (18583))
> )
> OR ((A18 IN (2129425, 2412043, 2197272, 2106911,
> 2146319, 2389802, 2260770, 2173489,
> 2434610, 2161990, 2117737, 2271342,
> 2355571, 2239606, 2282111, 2316669,
> 2332031, 2229624, 2081664, 2401161,
> 2366604, 2186416, 2296450, 2250431,
> 2067383, 2308778, 2450673, 2423002,
> 2061282, 2218767
> )
> )
> AND (A17 IN (14849, 15468))
> )
> OR ((A18 IN (75300, 84834, 77226, 74551, 75693,
> 83958, 79587, 76442, 85713, 76074,
> 74904, 79967, 82740, 78787, 80387,
> 81446, 81966, 78422, 73758, 84374,
> 83124, 76859, 80771, 79203, 81187,
> 86351, 85251, 73040, 78012
> )
> )
> AND (A17 IN (15818, 15790))
> )
> OR ((A18 IN (6434, 6835, 6511, 6652, 7338, 7381,
> 7516, 7053, 8076, 6751, 7212, 8346,
> 5951
> )
> )
> AND (A17 IN (15862))
> )
> OR ((A18 IN (88417, 102177, 91248, 87350, 88994,
> 101065, 94706, 90093, 103296, 86807,
> 89570, 87850, 95275, 99386, 93535,
> 95910, 92699, 97459, 98251, 92997,
> 86154, 101590, 99922, 90709, 96465,
> 94143, 85304, 97084, 99933, 87091,
> 104110, 102719, 84897, 100531, 92404
> )
> )
> AND (A17 IN (38577, 37251, 12998))
> )
> OR ((A18 IN (6014, 6357, 6075, 6196, 6790, 6829,
> 6950, 6542, 7442, 6277, 6671, 7689,
> 5614
> )
> )
> AND (A17 IN (15847))
> )
> OR ((A18 IN (21799))
> AND (A17 IN (14519))
> )
> FYI I have seen queries like this cause problems in the past, and on a
> single processor machines. It appears that large combinations of
> 'and's and 'or's can cause big problems with the query planner,
> possibly made worse if the statistics read out of date (which they are
> not here).
> Any help appreciated
> cheers
> jan|||Hi Ron,
Thanks for your response, sorry for the delay.
Just disabling intra-query parallelism seemed to do the trick -- the
client has needed no reboots since, and my tracking of the cpu use
shows a much more reasonable (ie. far less 'spiky') behaviour.
While this proves nothing, it is certainly strong evidence that the
IQP was the problem. I was not able to get any different query plans
under different loads on a test server, and when the client was having
problems the database server locked up *tight* -- even logging in
locally (ie. by a person on site) could take 5 or 10 minutes. Even
starting a profiler was almost impossible. Checking query plans or
blocking etc. was not feasible. Not a good situation for debugging.
The warning about disabling hyperthreading/SMT was given to us
verbally by one of the technical support engineers (bless them,
they're wonderful) but he was quite clear.
The SQL is dynamically generated, and currently they are very
un-reusable. They can also be very large, and I have absolutely no
doubt that there is plenty of pressure being put on the procedure
cache. We'll deal with that in a release or two.
I will definitely follow up that article you recommended.
Thanks again
jan
"Ron Talmage" <rtalmage@.prospice.com> wrote in message news:<eysruLAnEHA.3876@.TK2MSFTNGP15.phx.gbl>...
> Jan,
> AFIK, you're correct, the query governor works with estimated cost, not
> actual.
> Can you tell for certain that the runaway queries are parallelized, i.e.
> using sp_who2 and looking for multiple rows per spid?
> If bad parallel plans are the issue, then lowering Max DOP to 4 or 1 might
> help, as it looks like you'll be testing.
> Can you capture the query plan in Profiler and compare a slow version's plan
> with a fast query's plan in QA?
> That would help verify that a bad plan is truly the cause.
> The only caveat I've heard about hyperthreading is not to mix it with
> affinitizing.
> Some other things to try:
> Look for issues around waits and queues, using DBCC SQLPERF(waitstats). Find
> out what the slow queries are waiting on.
> Inspect the procedure cache (syscacheobjects) to determine whether these
> plans are being reused.
> There could be pressure on the cache if they are dynamically generated and
> can't be reused.
> Also check on the cache plan size to see if it's excessively large. I've
> seen very large plans hose up a system.
> For more info about waits and queues, and syscacheobjects, see the articles
> in SQL Magazine by Tom Davidson.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "jan" <anomalocarus@.hotmail.com> wrote in message
> news:62da4c70.0409160107.3f7d305c@.posting.google.com...
> > Hi all,
> > We're running MS SQL 2000, latest service pack. Windows 2000 server,
> > advanced server and Windows 2003 server. Assorted hardware.
[snip orig post]

Query for service pack version

How can I get the service pack version from SQL2000?
thanksTry:
select serverproperty ('ProductLevel')
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff Kendrick" <Jeff Kendrick@.discussions.microsoft.com> wrote in message
news:EBB3CE7F-8C53-4016-BF83-3E8066BA3755@.microsoft.com...
How can I get the service pack version from SQL2000?
thankssql

Query for service pack version

How can I get the service pack version from SQL2000?
thanksTry:
select serverproperty ('ProductLevel')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff Kendrick" <Jeff Kendrick@.discussions.microsoft.com> wrote in message
news:EBB3CE7F-8C53-4016-BF83-3E8066BA3755@.microsoft.com...
How can I get the service pack version from SQL2000?
thanks

query distribution using service broker

Hello ,

I am wokring on an application where in I am migrating the legacy application to SQL server. for this when ever I make any changes to the legacy application,I immediately move this changes to SQL, using insert/delete/update to SQL.

I also want all these data on 2 to 3 other SQL servers which are on different remote machines. for this i thought of using the service broker to send the query as messages.

So on the initiator(where the legacy application reside), i create 2 to 3 queues(based upon number of receivers) and each time a query is successful on the initiator, i move these messages(queries) to the initiator queues and send them to the various targets. on the target i just pop these messages and execute them so that the query run on the all the targets and all my databases are in synch.

I need some info on various operations on the Service broker queues like:

1.How do i insert/update/delete from the queue.
2.Can i set some size constraint on the queue
3.Can i get the info like when the queue is full.
4.Can i set some custom flags on the queue like, queue is invalid or valid(setting and getting these falgvalues).
5.Clearing the entire queue.

These are the operations, i need on the queue for various use cases(failure cases)

Can you point to some documentation/tutorials whihc sheds more light on these queue operations.
Thanks,

1. Unlike other messaging products like MSMQ, MQSeries, etc, Service Broker doesn't allow you to enqueue directly to queues. Instead, it provides reliable communication via dialogs between services. Queues are just backing stores used by services to store incoming messages until they are processed. But the targettable endpoints are Services and the primitive for communication is a dialog or conversation.

In your application, you could create one initiator service from where you will send the data, one intermediate service which would receive the data and in turn send copies to each of the target services. The target services would reside in each of your databases on the different SQL Server instances.

2. Service Broker Queues do not have size constraints and therefore there is no such thing as queue is full. Like database tables, they can grow logically up to infinity or physically up to the size of the physical media they reside on.

3. You can enable/disable the queue which causes the service to accept/reject inbound messages.

4. You cannot clear the queue in a single operation. But you could iterate over all the conversation endpoins and end them causing messages associated with those conversations to be deleted from the queue.

Books Online (the SQL Server documentation) and Roger Wolter's book are good resources to learn about Service Broker. Also look at my links on our homepage.

Query based parameter and web service call

has anyone seen this before?
I have a developer that has built a report that has a query based parameter.
Since they want to be able to run the report both interactively and through
a .net batch job. They are attempting to do a soap call to the report
passing a string value in as the query based parameters value. They are
getting a soap exception error .SoapException: Default value or value
provided for the report parameter 'PROD_DESC' is not a valid value. -->
Has anyone seen this before or can verify that you can pass string values
into query based parameters?
Thank YOu!!!We figured out the issue. The developer had a trailing space in his code so
the values in the string were not matching the values returned by the query.
"Mark" wrote:
> has anyone seen this before?
> I have a developer that has built a report that has a query based parameter.
> Since they want to be able to run the report both interactively and through
> a .net batch job. They are attempting to do a soap call to the report
> passing a string value in as the query based parameters value. They are
> getting a soap exception error .SoapException: Default value or value
> provided for the report parameter 'PROD_DESC' is not a valid value. -->
> Has anyone seen this before or can verify that you can pass string values
> into query based parameters?
> Thank YOu!!!

query at runtime

Hi all,
Is there way in reporting service programming to give the sql select query
for a report during runtime.
Thanks
rsuserYou can have your query be an expression. Be in the generic query designer
(two panes). The button to switch to this is to the right of the ...
Put in an expression.
="select somefield, someotherfield from mytable where startdate>= '" &
parameters!ParamName & "' order by " & parameters!AnotherParam
Note that you have to put your own single quotes where appropriate. What I
do is first have a report with just a textbox with the expression so I can
see it and see if I have created the query appropriately. Then I assign it
to a dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> Hi all,
> Is there way in reporting service programming to give the sql select query
> for a report during runtime.
> Thanks
> rsuser|||Just adding to Bruce's description:
Once you are using an expression-based command-text, you can no longer
execute the query in the report designer data view. Also you can no longer
refresh the fields list. That's why it is important to first finish the
report design based on a constant command text and use Bruce's suggested
approach of a textbox to look at the expression results - before you change
the command text to be expression-based.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> You can have your query be an expression. Be in the generic query designer
> (two panes). The button to switch to this is to the right of the ...
> Put in an expression.
> ="select somefield, someotherfield from mytable where startdate>= '" &
> parameters!ParamName & "' order by " & parameters!AnotherParam
> Note that you have to put your own single quotes where appropriate. What I
> do is first have a report with just a textbox with the expression so I can
> see it and see if I have created the query appropriately. Then I assign it
> to a dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
>> Hi all,
>> Is there way in reporting service programming to give the sql select
>> query
>> for a report during runtime.
>> Thanks
>> rsuser
>|||thanks!! I got it working. I have other issues. Please help me on these
1)Is there a way to supress the image when the report is
displayed in browser and then make them available for printing on paper
alone? is there a way to use any javascript or any other way to do this?
2)Say i have a dot net application which will display reports using
webservices and reports are using custom assembly which requires read
permission. Then while installing the dot net application in the
end users system, how the policy files will be configured. is there a way to
set up client's report server policy files through dot net code?
Thanks,
rsuser
"Robert Bruckner [MSFT]" wrote:
> Just adding to Bruce's description:
> Once you are using an expression-based command-text, you can no longer
> execute the query in the report designer data view. Also you can no longer
> refresh the fields list. That's why it is important to first finish the
> report design based on a constant command text and use Bruce's suggested
> approach of a textbox to look at the expression results - before you change
> the command text to be expression-based.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> > You can have your query be an expression. Be in the generic query designer
> > (two panes). The button to switch to this is to the right of the ...
> >
> > Put in an expression.
> >
> > ="select somefield, someotherfield from mytable where startdate>= '" &
> > parameters!ParamName & "' order by " & parameters!AnotherParam
> >
> > Note that you have to put your own single quotes where appropriate. What I
> > do is first have a report with just a textbox with the expression so I can
> > see it and see if I have created the query appropriately. Then I assign it
> > to a dataset.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> > news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> >> Hi all,
> >>
> >> Is there way in reporting service programming to give the sql select
> >> query
> >> for a report during runtime.
> >>
> >> Thanks
> >> rsuser
> >
> >
>
>