ok, I am rather a novice when it comes to SQL queries, so please bear with
me.
I will only be running this table on one table.
I have about 10 columns in teh table
just to make things easy to understand, I will try to make this cleaar and
straight forward:
I have table A and I run the following query on it:
Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
clmB is formated in SQL as DateTime
anyway, so I have several rows returned to me.
For example, it will read:
Shipped, 7/14/2007, 82465,8202007
Shipped,7/16/2007,123456,8202007
on-hand,7/14/2007,82465,
on-hand,7/13/2007,12345,
now, if you noticed, the first and forth items are the same, except the 3rd
item says on-hand instead of shipped.
What I am looking for is a query that would NOT include the items that say
ON-HAND, if the other items are teh same.
Meaning that in my example, it would have only listed items 1,2,4
also, when a row is set as on-hand, there are a couple of cells that will
not be populated.
oy, I hope I made it easy enough to understand.
thanks in advanceThis may give you some ideas to get you started.
SELECT colA, colB, colC,
CASE WHEN colA <> 'on-hand'
THEN colD
ELSE NULL
END
FROM TheTable as A
WHERE clmA <> 'on-hand'
OR NOT EXISTS
(SELECT * FROM TheTable as B
WHERE A.clmB = B.clmB
AND A.clmC = B.clmC
AND B.clmA <> 'on-hand')
Roy Harvey
Beacon Falls, CT
On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>ok, I am rather a novice when it comes to SQL queries, so please bear with
>me.
>I will only be running this table on one table.
>I have about 10 columns in teh table
>just to make things easy to understand, I will try to make this cleaar and
>straight forward:
>I have table A and I run the following query on it:
>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>clmB is formated in SQL as DateTime
>anyway, so I have several rows returned to me.
>For example, it will read:
>Shipped, 7/14/2007, 82465,8202007
>Shipped,7/16/2007,123456,8202007
>on-hand,7/14/2007,82465,
>on-hand,7/13/2007,12345,
>
>now, if you noticed, the first and forth items are the same, except the 3rd
>item says on-hand instead of shipped.
>What I am looking for is a query that would NOT include the items that say
>ON-HAND, if the other items are teh same.
>Meaning that in my example, it would have only listed items 1,2,4
>also, when a row is set as on-hand, there are a couple of cells that will
>not be populated.
>oy, I hope I made it easy enough to understand.
>thanks in advance
>|||WOW!!
That was fast, I will give it a shot. Thank you!!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||hmmmm
oy, this is kicking my rump here.
haven't gotten it to work, but to try to make it a little more clear...
lets say I have 2 rows, they have almost teh exact same info. The only
difference is that when colA says ON-HAND colD, colE and colF will be null
if colA says SHIPPED all teh other cols will be the same Excpet colD, colE
and colF will not be null
This table gets it's data by reading a txt file once a day with shipment
status info. Normally, I would have it update an exsisting record as the
STATUS (colA) changed from on-hand top shipped. BUT I can't do that becsue
the powers that be, want to be able to see teh records of when it was
on-hand and then when it shipped.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||OK, I think I got it to work, but now I can't get my DATE clause in there to
work.
clmB is a date, and I want everything from 8/8/2007 and newer
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||On Tue, 21 Aug 2007 14:03:34 -0700, "Johnfli" <john@.ivhs.us> wrote:
>OK, I think I got it to work, but now I can't get my DATE clause in there to
>work.
>clmB is a date, and I want everything from 8/8/2007 and newer
Hopefully clmB is of the datetime or smalldatetime datatype, in which
case I would expect this to work:
WHERE clmB >= '20070808'
If clmB is a string life is not so simple.
WHERE CONVERT(datetime,clmB) >= '20070808'
The really unfortunate part of that approach is that it can not take
advantage of an index on clmB, as it might if clmB were a datetime
column. Also, if any rows have data in clmB that can NOT be converted
to datetime it will fail. The workaround for THAT might be something
like:
WHERE CASE WHEN ISDATE(clmB) = 1
THEN CONVERT(datetime,clmB)
ELSE NULL
END >= '20070808'
Roy Harvey
Beacon Falls, CT|||as luck would have it, clmB is formated as DateTime.
The query I am running is:
SELECT Status, RecDate, Container, [PO Number], Shipment, Style,
Cartons, Qty, OSDNote, OutTrailer, ShipDate, SentEDI,
CASE WHEN status <> 'ON-hand' THEN sentedi ELSE NULL
END AS Expr1
FROM jcrew A
WHERE (Status <> 'ON-hand') AND (RecDate >= '8/10/2007') OR
(NOT EXISTS
(SELECT *
FROM jcrew AS B
WHERE a.[po number] = B.[po number] AND
a.shipment = B.shipment AND a.style = B.style AND a.cartons = B.cartons AND
a.qty = B.qty AND
b.status <> 'on-hand'))
****Please note that in the CASE statement, SQL adds the AS Expr1 on it's
own. *******
RecDate, is the clomun name that I substituted as clmB.
But when I run the query, it returns all dates, not just the ones 8/10/2007
and newer.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:c3qmc3pb1nckesfjlhg5coaqofckvfc7og@.4ax.com...
> On Tue, 21 Aug 2007 14:03:34 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>OK, I think I got it to work, but now I can't get my DATE clause in there
>>to
>>work.
>>clmB is a date, and I want everything from 8/8/2007 and newer
> Hopefully clmB is of the datetime or smalldatetime datatype, in which
> case I would expect this to work:
> WHERE clmB >= '20070808'
> If clmB is a string life is not so simple.
> WHERE CONVERT(datetime,clmB) >= '20070808'
> The really unfortunate part of that approach is that it can not take
> advantage of an index on clmB, as it might if clmB were a datetime
> column. Also, if any rows have data in clmB that can NOT be converted
> to datetime it will fail. The workaround for THAT might be something
> like:
> WHERE CASE WHEN ISDATE(clmB) = 1
> THEN CONVERT(datetime,clmB)
> ELSE NULL
> END >= '20070808'
> Roy Harvey
> Beacon Falls, CT|||On Wed, 22 Aug 2007 09:11:06 -0700, "Johnfli" <john@.ivhs.us> wrote:
>as luck would have it, clmB is formated as DateTime.
DATETIME is not a format, it is a data type.
>The query I am running is:
>SELECT Status, RecDate, Container, [PO Number], Shipment, Style,
>Cartons, Qty, OSDNote, OutTrailer, ShipDate, SentEDI,
> CASE WHEN status <> 'ON-hand' THEN sentedi ELSE NULL
>END AS Expr1
>FROM jcrew A
>WHERE (Status <> 'ON-hand') AND (RecDate >= '8/10/2007') OR
> (NOT EXISTS
> (SELECT *
> FROM jcrew AS B
> WHERE a.[po number] = B.[po number] AND
>a.shipment = B.shipment AND a.style = B.style AND a.cartons = B.cartons AND
>a.qty = B.qty AND
> b.status <> 'on-hand'))
>****Please note that in the CASE statement, SQL adds the AS Expr1 on it's
>own. *******
Some front-end tool adds the AS Expr1, not SQL Server.
And now I see the problem with filter on date: the OR test. I don't
know what was intended, but I think the problem is that the OR test
for the NOT EXISTS is an alternative to both the Status test and the
RecDate test. If the NOT EXISTS test passes, the row passes. When
you have AND mixed with OR, always use (parentheses) to make clear
what works with what.
Roy Harvey
Beacon Falls, CT
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
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]
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]
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 governor for users?
How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>sql
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>sql
query governor for users?
How to make only some users not to run long running blocking queries ?
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>
TIAHi
You could SET DEADLOCK_PRIORITY to low and a QUERY_GOVERNOR_COST_LIMIT,
setting ROWCOUNT and a LOCK_TIMEOUT may also be options.
John
"DallasBlue" wrote:
> How to make only some users not to run long running blocking queries ?
> TIA
>
Friday, March 23, 2012
Query formatting in 2005 converted reports
Yikes! I have upgraded to SQL Server 2005 Developer Edition, including the
RS upgrade. The ReportServer and reports all came over and are running
fabulously well. However, strange things are happening with my queries in
Intelligence Studio (and VS Studio 2005, I've tried both) after being
converted to the new report definition format. My extensive queries have
been reformatted predominantly as straight-text (very unreadable) and my
queries no longer pass SQL verification.
I tried retrieving the well-formatted queries from local text file backups,
but the new designer continues to reformat it as straight-text.
What can I do to get the following format in my queries:
Select A,
B,
C (as string),
D
From TableA
Where [clause]
I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
Thanks.
bhcHave you tried using the generic query designer (2 panes instead of 4). I
should leave it a alone. It is one of the buttons to the right of the ... to
switch to it).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
> the
> RS upgrade. The ReportServer and reports all came over and are running
> fabulously well. However, strange things are happening with my queries in
> Intelligence Studio (and VS Studio 2005, I've tried both) after being
> converted to the new report definition format. My extensive queries have
> been reformatted predominantly as straight-text (very unreadable) and my
> queries no longer pass SQL verification.
> I tried retrieving the well-formatted queries from local text file
> backups,
> but the new designer continues to reformat it as straight-text.
> What can I do to get the following format in my queries:
> Select A,
> B,
> C (as string),
> D
> From TableA
> Where [clause]
> I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> Thanks.
> bhc|||I get the following after converting to the new report definition format when
I access the Data tab:
Error in SELECT clause: alias not recognized.
Error in list of function arguments: 'DataCenter' not recognized.
Error in list of function arguments: ',' not recognized.
Error in list of function arguments: '<' not recognized.
Error in list of function arguments: 'out_month' not recognized.
Unable to parse query text.
If I let things load in the multi-pane view and use the Verify SQL button, a
Microsoft Visual Database Tools window pops up with "The command is not
supported by this provider".
Having reviewed a few of my report queries facing this issue, I notice
problems only with queries using native MySQL functions, such as date/time
conversions. All functions have been encapsulated by single quotes, so
instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
simply get inserted when I go to verify the SQL or move out of the Query text
pane. Though I get the above errors on other MySQL queries, I am still able
to run ones without functions in them.
Is there any way to configure RS to stop doing this? This is just nasty.
Thanks.
bhc
"Bruce L-C [MVP]" wrote:
> Have you tried using the generic query designer (2 panes instead of 4). I
> should leave it a alone. It is one of the buttons to the right of the ... to
> switch to it).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> > Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
> > the
> > RS upgrade. The ReportServer and reports all came over and are running
> > fabulously well. However, strange things are happening with my queries in
> > Intelligence Studio (and VS Studio 2005, I've tried both) after being
> > converted to the new report definition format. My extensive queries have
> > been reformatted predominantly as straight-text (very unreadable) and my
> > queries no longer pass SQL verification.
> >
> > I tried retrieving the well-formatted queries from local text file
> > backups,
> > but the new designer continues to reformat it as straight-text.
> >
> > What can I do to get the following format in my queries:
> > Select A,
> > B,
> > C (as string),
> > D
> > From TableA
> > Where [clause]
> >
> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> >
> > Thanks.
> > bhc
>
>|||Have you tried going to the generic query designer? I have no doubt you are
seeing these problems in the 4 pane graphical query designer. What happens
if you switch over to the generic query designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
>I get the following after converting to the new report definition format
>when
> I access the Data tab:
> Error in SELECT clause: alias not recognized.
> Error in list of function arguments: 'DataCenter' not recognized.
> Error in list of function arguments: ',' not recognized.
> Error in list of function arguments: '<' not recognized.
> Error in list of function arguments: 'out_month' not recognized.
> Unable to parse query text.
> If I let things load in the multi-pane view and use the Verify SQL button,
> a
> Microsoft Visual Database Tools window pops up with "The command is not
> supported by this provider".
> Having reviewed a few of my report queries facing this issue, I notice
> problems only with queries using native MySQL functions, such as date/time
> conversions. All functions have been encapsulated by single quotes, so
> instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
> 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
> simply get inserted when I go to verify the SQL or move out of the Query
> text
> pane. Though I get the above errors on other MySQL queries, I am still
> able
> to run ones without functions in them.
> Is there any way to configure RS to stop doing this? This is just nasty.
> Thanks.
> bhc
> "Bruce L-C [MVP]" wrote:
>> Have you tried using the generic query designer (2 panes instead of 4). I
>> should leave it a alone. It is one of the buttons to the right of the ...
>> to
>> switch to it).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
>> > Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
>> > the
>> > RS upgrade. The ReportServer and reports all came over and are running
>> > fabulously well. However, strange things are happening with my queries
>> > in
>> > Intelligence Studio (and VS Studio 2005, I've tried both) after being
>> > converted to the new report definition format. My extensive queries
>> > have
>> > been reformatted predominantly as straight-text (very unreadable) and
>> > my
>> > queries no longer pass SQL verification.
>> >
>> > I tried retrieving the well-formatted queries from local text file
>> > backups,
>> > but the new designer continues to reformat it as straight-text.
>> >
>> > What can I do to get the following format in my queries:
>> > Select A,
>> > B,
>> > C (as string),
>> > D
>> > From TableA
>> > Where [clause]
>> >
>> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
>> >
>> > Thanks.
>> > bhc
>>|||Yes, I have tried using just the generic query designer (that's what I'm used
from RS 2000). It is the generic query designer where I'm having all the
problems. It's very unreadable and does not parse when MySQL functions are
used in the query even if the generic query designer is the only pane
displayed. The properties on Query Designer are as follows:
Distinct Values = No
Output All Columns= No
Query Parameter Identification = [nothing here]
Query Parameter List = [greyed out, no parameters specified]
Thoughts?
bhc
"Bruce L-C [MVP]" wrote:
> Have you tried going to the generic query designer? I have no doubt you are
> seeing these problems in the 4 pane graphical query designer. What happens
> if you switch over to the generic query designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
> >I get the following after converting to the new report definition format
> >when
> > I access the Data tab:
> > Error in SELECT clause: alias not recognized.
> > Error in list of function arguments: 'DataCenter' not recognized.
> > Error in list of function arguments: ',' not recognized.
> > Error in list of function arguments: '<' not recognized.
> > Error in list of function arguments: 'out_month' not recognized.
> > Unable to parse query text.
> >
> > If I let things load in the multi-pane view and use the Verify SQL button,
> > a
> > Microsoft Visual Database Tools window pops up with "The command is not
> > supported by this provider".
> >
> > Having reviewed a few of my report queries facing this issue, I notice
> > problems only with queries using native MySQL functions, such as date/time
> > conversions. All functions have been encapsulated by single quotes, so
> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
> > simply get inserted when I go to verify the SQL or move out of the Query
> > text
> > pane. Though I get the above errors on other MySQL queries, I am still
> > able
> > to run ones without functions in them.
> >
> > Is there any way to configure RS to stop doing this? This is just nasty.
> > Thanks.
> > bhc
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Have you tried using the generic query designer (2 panes instead of 4). I
> >> should leave it a alone. It is one of the buttons to the right of the ...
> >> to
> >> switch to it).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
> >> > the
> >> > RS upgrade. The ReportServer and reports all came over and are running
> >> > fabulously well. However, strange things are happening with my queries
> >> > in
> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after being
> >> > converted to the new report definition format. My extensive queries
> >> > have
> >> > been reformatted predominantly as straight-text (very unreadable) and
> >> > my
> >> > queries no longer pass SQL verification.
> >> >
> >> > I tried retrieving the well-formatted queries from local text file
> >> > backups,
> >> > but the new designer continues to reformat it as straight-text.
> >> >
> >> > What can I do to get the following format in my queries:
> >> > Select A,
> >> > B,
> >> > C (as string),
> >> > D
> >> > From TableA
> >> > Where [clause]
> >> >
> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> >> >
> >> > Thanks.
> >> > bhc
> >>
> >>
> >>
>
>|||I am in the process of upgrading and testing all my reports. I will look at
some of the more complex ones that go against Sybase and see how it works.
I'll let you know.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:46E4343F-8FB0-4F09-A582-D7A3F5E9AF0A@.microsoft.com...
> Yes, I have tried using just the generic query designer (that's what I'm
> used
> from RS 2000). It is the generic query designer where I'm having all the
> problems. It's very unreadable and does not parse when MySQL functions
> are
> used in the query even if the generic query designer is the only pane
> displayed. The properties on Query Designer are as follows:
> Distinct Values = No
> Output All Columns= No
> Query Parameter Identification = [nothing here]
> Query Parameter List = [greyed out, no parameters specified]
> Thoughts?
> bhc
> "Bruce L-C [MVP]" wrote:
>> Have you tried going to the generic query designer? I have no doubt you
>> are
>> seeing these problems in the 4 pane graphical query designer. What
>> happens
>> if you switch over to the generic query designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
>> >I get the following after converting to the new report definition format
>> >when
>> > I access the Data tab:
>> > Error in SELECT clause: alias not recognized.
>> > Error in list of function arguments: 'DataCenter' not recognized.
>> > Error in list of function arguments: ',' not recognized.
>> > Error in list of function arguments: '<' not recognized.
>> > Error in list of function arguments: 'out_month' not recognized.
>> > Unable to parse query text.
>> >
>> > If I let things load in the multi-pane view and use the Verify SQL
>> > button,
>> > a
>> > Microsoft Visual Database Tools window pops up with "The command is not
>> > supported by this provider".
>> >
>> > Having reviewed a few of my report queries facing this issue, I notice
>> > problems only with queries using native MySQL functions, such as
>> > date/time
>> > conversions. All functions have been encapsulated by single quotes, so
>> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
>> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
>> > simply get inserted when I go to verify the SQL or move out of the
>> > Query
>> > text
>> > pane. Though I get the above errors on other MySQL queries, I am still
>> > able
>> > to run ones without functions in them.
>> >
>> > Is there any way to configure RS to stop doing this? This is just
>> > nasty.
>> > Thanks.
>> > bhc
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Have you tried using the generic query designer (2 panes instead of
>> >> 4). I
>> >> should leave it a alone. It is one of the buttons to the right of the
>> >> ...
>> >> to
>> >> switch to it).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
>> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition,
>> >> > including
>> >> > the
>> >> > RS upgrade. The ReportServer and reports all came over and are
>> >> > running
>> >> > fabulously well. However, strange things are happening with my
>> >> > queries
>> >> > in
>> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after
>> >> > being
>> >> > converted to the new report definition format. My extensive queries
>> >> > have
>> >> > been reformatted predominantly as straight-text (very unreadable)
>> >> > and
>> >> > my
>> >> > queries no longer pass SQL verification.
>> >> >
>> >> > I tried retrieving the well-formatted queries from local text file
>> >> > backups,
>> >> > but the new designer continues to reformat it as straight-text.
>> >> >
>> >> > What can I do to get the following format in my queries:
>> >> > Select A,
>> >> > B,
>> >> > C (as string),
>> >> > D
>> >> > From TableA
>> >> > Where [clause]
>> >> >
>> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
>> >> >
>> >> > Thanks.
>> >> > bhc
>> >>
>> >>
>> >>
>>|||The way the generic query designer works for me is it leaves everything
exactly the way I type it in. If it has been in the graphical designer it
can get munged up. If you clean it up it should work for you.
The properties you mentioned, I don't see anyplace to see these properties?
Where are you seeing them?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%238%23y%23UW5FHA.724@.TK2MSFTNGP14.phx.gbl...
>I am in the process of upgrading and testing all my reports. I will look at
>some of the more complex ones that go against Sybase and see how it works.
>I'll let you know.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:46E4343F-8FB0-4F09-A582-D7A3F5E9AF0A@.microsoft.com...
>> Yes, I have tried using just the generic query designer (that's what I'm
>> used
>> from RS 2000). It is the generic query designer where I'm having all the
>> problems. It's very unreadable and does not parse when MySQL functions
>> are
>> used in the query even if the generic query designer is the only pane
>> displayed. The properties on Query Designer are as follows:
>> Distinct Values = No
>> Output All Columns= No
>> Query Parameter Identification = [nothing here]
>> Query Parameter List = [greyed out, no parameters specified]
>> Thoughts?
>> bhc
>> "Bruce L-C [MVP]" wrote:
>> Have you tried going to the generic query designer? I have no doubt you
>> are
>> seeing these problems in the 4 pane graphical query designer. What
>> happens
>> if you switch over to the generic query designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
>> >I get the following after converting to the new report definition
>> >format
>> >when
>> > I access the Data tab:
>> > Error in SELECT clause: alias not recognized.
>> > Error in list of function arguments: 'DataCenter' not recognized.
>> > Error in list of function arguments: ',' not recognized.
>> > Error in list of function arguments: '<' not recognized.
>> > Error in list of function arguments: 'out_month' not recognized.
>> > Unable to parse query text.
>> >
>> > If I let things load in the multi-pane view and use the Verify SQL
>> > button,
>> > a
>> > Microsoft Visual Database Tools window pops up with "The command is
>> > not
>> > supported by this provider".
>> >
>> > Having reviewed a few of my report queries facing this issue, I notice
>> > problems only with queries using native MySQL functions, such as
>> > date/time
>> > conversions. All functions have been encapsulated by single quotes,
>> > so
>> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
>> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile;
>> > they
>> > simply get inserted when I go to verify the SQL or move out of the
>> > Query
>> > text
>> > pane. Though I get the above errors on other MySQL queries, I am
>> > still
>> > able
>> > to run ones without functions in them.
>> >
>> > Is there any way to configure RS to stop doing this? This is just
>> > nasty.
>> > Thanks.
>> > bhc
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Have you tried using the generic query designer (2 panes instead of
>> >> 4). I
>> >> should leave it a alone. It is one of the buttons to the right of the
>> >> ...
>> >> to
>> >> switch to it).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
>> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition,
>> >> > including
>> >> > the
>> >> > RS upgrade. The ReportServer and reports all came over and are
>> >> > running
>> >> > fabulously well. However, strange things are happening with my
>> >> > queries
>> >> > in
>> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after
>> >> > being
>> >> > converted to the new report definition format. My extensive
>> >> > queries
>> >> > have
>> >> > been reformatted predominantly as straight-text (very unreadable)
>> >> > and
>> >> > my
>> >> > queries no longer pass SQL verification.
>> >> >
>> >> > I tried retrieving the well-formatted queries from local text file
>> >> > backups,
>> >> > but the new designer continues to reformat it as straight-text.
>> >> >
>> >> > What can I do to get the following format in my queries:
>> >> > Select A,
>> >> > B,
>> >> > C (as string),
>> >> > D
>> >> > From TableA
>> >> > Where [clause]
>> >> >
>> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
>> >> >
>> >> > Thanks.
>> >> > bhc
>> >>
>> >>
>> >>
>>
>|||If you right-click the generic query designer, there's an option for
Properties. Those are the properties I referenced in my previous email.
I have since been poring through the Tools | Options in Report Designer to
see if there's a way to turn off the automatic quotes and perhaps the
straight-text formatting. Any idea what these options might be? Maybe I can
customize Report Designer to behave the way I want/expect.
Thoughts?
"Bruce L-C [MVP]" wrote:
> The way the generic query designer works for me is it leaves everything
> exactly the way I type it in. If it has been in the graphical designer it
> can get munged up. If you clean it up it should work for you.
> The properties you mentioned, I don't see anyplace to see these properties?
> Where are you seeing them?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%238%23y%23UW5FHA.724@.TK2MSFTNGP14.phx.gbl...
> >I am in the process of upgrading and testing all my reports. I will look at
> >some of the more complex ones that go against Sybase and see how it works.
> >I'll let you know.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> > news:46E4343F-8FB0-4F09-A582-D7A3F5E9AF0A@.microsoft.com...
> >> Yes, I have tried using just the generic query designer (that's what I'm
> >> used
> >> from RS 2000). It is the generic query designer where I'm having all the
> >> problems. It's very unreadable and does not parse when MySQL functions
> >> are
> >> used in the query even if the generic query designer is the only pane
> >> displayed. The properties on Query Designer are as follows:
> >> Distinct Values = No
> >> Output All Columns= No
> >> Query Parameter Identification = [nothing here]
> >> Query Parameter List = [greyed out, no parameters specified]
> >>
> >> Thoughts?
> >> bhc
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> Have you tried going to the generic query designer? I have no doubt you
> >> are
> >> seeing these problems in the 4 pane graphical query designer. What
> >> happens
> >> if you switch over to the generic query designer.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
> >> >I get the following after converting to the new report definition
> >> >format
> >> >when
> >> > I access the Data tab:
> >> > Error in SELECT clause: alias not recognized.
> >> > Error in list of function arguments: 'DataCenter' not recognized.
> >> > Error in list of function arguments: ',' not recognized.
> >> > Error in list of function arguments: '<' not recognized.
> >> > Error in list of function arguments: 'out_month' not recognized.
> >> > Unable to parse query text.
> >> >
> >> > If I let things load in the multi-pane view and use the Verify SQL
> >> > button,
> >> > a
> >> > Microsoft Visual Database Tools window pops up with "The command is
> >> > not
> >> > supported by this provider".
> >> >
> >> > Having reviewed a few of my report queries facing this issue, I notice
> >> > problems only with queries using native MySQL functions, such as
> >> > date/time
> >> > conversions. All functions have been encapsulated by single quotes,
> >> > so
> >> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
> >> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile;
> >> > they
> >> > simply get inserted when I go to verify the SQL or move out of the
> >> > Query
> >> > text
> >> > pane. Though I get the above errors on other MySQL queries, I am
> >> > still
> >> > able
> >> > to run ones without functions in them.
> >> >
> >> > Is there any way to configure RS to stop doing this? This is just
> >> > nasty.
> >> > Thanks.
> >> > bhc
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Have you tried using the generic query designer (2 panes instead of
> >> >> 4). I
> >> >> should leave it a alone. It is one of the buttons to the right of the
> >> >> ...
> >> >> to
> >> >> switch to it).
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> >> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition,
> >> >> > including
> >> >> > the
> >> >> > RS upgrade. The ReportServer and reports all came over and are
> >> >> > running
> >> >> > fabulously well. However, strange things are happening with my
> >> >> > queries
> >> >> > in
> >> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after
> >> >> > being
> >> >> > converted to the new report definition format. My extensive
> >> >> > queries
> >> >> > have
> >> >> > been reformatted predominantly as straight-text (very unreadable)
> >> >> > and
> >> >> > my
> >> >> > queries no longer pass SQL verification.
> >> >> >
> >> >> > I tried retrieving the well-formatted queries from local text file
> >> >> > backups,
> >> >> > but the new designer continues to reformat it as straight-text.
> >> >> >
> >> >> > What can I do to get the following format in my queries:
> >> >> > Select A,
> >> >> > B,
> >> >> > C (as string),
> >> >> > D
> >> >> > From TableA
> >> >> > Where [clause]
> >> >> >
> >> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> >> >> >
> >> >> > Thanks.
> >> >> > bhc
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
> >
>
>sql
RS upgrade. The ReportServer and reports all came over and are running
fabulously well. However, strange things are happening with my queries in
Intelligence Studio (and VS Studio 2005, I've tried both) after being
converted to the new report definition format. My extensive queries have
been reformatted predominantly as straight-text (very unreadable) and my
queries no longer pass SQL verification.
I tried retrieving the well-formatted queries from local text file backups,
but the new designer continues to reformat it as straight-text.
What can I do to get the following format in my queries:
Select A,
B,
C (as string),
D
From TableA
Where [clause]
I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
Thanks.
bhcHave you tried using the generic query designer (2 panes instead of 4). I
should leave it a alone. It is one of the buttons to the right of the ... to
switch to it).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
> the
> RS upgrade. The ReportServer and reports all came over and are running
> fabulously well. However, strange things are happening with my queries in
> Intelligence Studio (and VS Studio 2005, I've tried both) after being
> converted to the new report definition format. My extensive queries have
> been reformatted predominantly as straight-text (very unreadable) and my
> queries no longer pass SQL verification.
> I tried retrieving the well-formatted queries from local text file
> backups,
> but the new designer continues to reformat it as straight-text.
> What can I do to get the following format in my queries:
> Select A,
> B,
> C (as string),
> D
> From TableA
> Where [clause]
> I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> Thanks.
> bhc|||I get the following after converting to the new report definition format when
I access the Data tab:
Error in SELECT clause: alias not recognized.
Error in list of function arguments: 'DataCenter' not recognized.
Error in list of function arguments: ',' not recognized.
Error in list of function arguments: '<' not recognized.
Error in list of function arguments: 'out_month' not recognized.
Unable to parse query text.
If I let things load in the multi-pane view and use the Verify SQL button, a
Microsoft Visual Database Tools window pops up with "The command is not
supported by this provider".
Having reviewed a few of my report queries facing this issue, I notice
problems only with queries using native MySQL functions, such as date/time
conversions. All functions have been encapsulated by single quotes, so
instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
simply get inserted when I go to verify the SQL or move out of the Query text
pane. Though I get the above errors on other MySQL queries, I am still able
to run ones without functions in them.
Is there any way to configure RS to stop doing this? This is just nasty.
Thanks.
bhc
"Bruce L-C [MVP]" wrote:
> Have you tried using the generic query designer (2 panes instead of 4). I
> should leave it a alone. It is one of the buttons to the right of the ... to
> switch to it).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> > Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
> > the
> > RS upgrade. The ReportServer and reports all came over and are running
> > fabulously well. However, strange things are happening with my queries in
> > Intelligence Studio (and VS Studio 2005, I've tried both) after being
> > converted to the new report definition format. My extensive queries have
> > been reformatted predominantly as straight-text (very unreadable) and my
> > queries no longer pass SQL verification.
> >
> > I tried retrieving the well-formatted queries from local text file
> > backups,
> > but the new designer continues to reformat it as straight-text.
> >
> > What can I do to get the following format in my queries:
> > Select A,
> > B,
> > C (as string),
> > D
> > From TableA
> > Where [clause]
> >
> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> >
> > Thanks.
> > bhc
>
>|||Have you tried going to the generic query designer? I have no doubt you are
seeing these problems in the 4 pane graphical query designer. What happens
if you switch over to the generic query designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
>I get the following after converting to the new report definition format
>when
> I access the Data tab:
> Error in SELECT clause: alias not recognized.
> Error in list of function arguments: 'DataCenter' not recognized.
> Error in list of function arguments: ',' not recognized.
> Error in list of function arguments: '<' not recognized.
> Error in list of function arguments: 'out_month' not recognized.
> Unable to parse query text.
> If I let things load in the multi-pane view and use the Verify SQL button,
> a
> Microsoft Visual Database Tools window pops up with "The command is not
> supported by this provider".
> Having reviewed a few of my report queries facing this issue, I notice
> problems only with queries using native MySQL functions, such as date/time
> conversions. All functions have been encapsulated by single quotes, so
> instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
> 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
> simply get inserted when I go to verify the SQL or move out of the Query
> text
> pane. Though I get the above errors on other MySQL queries, I am still
> able
> to run ones without functions in them.
> Is there any way to configure RS to stop doing this? This is just nasty.
> Thanks.
> bhc
> "Bruce L-C [MVP]" wrote:
>> Have you tried using the generic query designer (2 panes instead of 4). I
>> should leave it a alone. It is one of the buttons to the right of the ...
>> to
>> switch to it).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
>> > Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
>> > the
>> > RS upgrade. The ReportServer and reports all came over and are running
>> > fabulously well. However, strange things are happening with my queries
>> > in
>> > Intelligence Studio (and VS Studio 2005, I've tried both) after being
>> > converted to the new report definition format. My extensive queries
>> > have
>> > been reformatted predominantly as straight-text (very unreadable) and
>> > my
>> > queries no longer pass SQL verification.
>> >
>> > I tried retrieving the well-formatted queries from local text file
>> > backups,
>> > but the new designer continues to reformat it as straight-text.
>> >
>> > What can I do to get the following format in my queries:
>> > Select A,
>> > B,
>> > C (as string),
>> > D
>> > From TableA
>> > Where [clause]
>> >
>> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
>> >
>> > Thanks.
>> > bhc
>>|||Yes, I have tried using just the generic query designer (that's what I'm used
from RS 2000). It is the generic query designer where I'm having all the
problems. It's very unreadable and does not parse when MySQL functions are
used in the query even if the generic query designer is the only pane
displayed. The properties on Query Designer are as follows:
Distinct Values = No
Output All Columns= No
Query Parameter Identification = [nothing here]
Query Parameter List = [greyed out, no parameters specified]
Thoughts?
bhc
"Bruce L-C [MVP]" wrote:
> Have you tried going to the generic query designer? I have no doubt you are
> seeing these problems in the 4 pane graphical query designer. What happens
> if you switch over to the generic query designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
> >I get the following after converting to the new report definition format
> >when
> > I access the Data tab:
> > Error in SELECT clause: alias not recognized.
> > Error in list of function arguments: 'DataCenter' not recognized.
> > Error in list of function arguments: ',' not recognized.
> > Error in list of function arguments: '<' not recognized.
> > Error in list of function arguments: 'out_month' not recognized.
> > Unable to parse query text.
> >
> > If I let things load in the multi-pane view and use the Verify SQL button,
> > a
> > Microsoft Visual Database Tools window pops up with "The command is not
> > supported by this provider".
> >
> > Having reviewed a few of my report queries facing this issue, I notice
> > problems only with queries using native MySQL functions, such as date/time
> > conversions. All functions have been encapsulated by single quotes, so
> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
> > simply get inserted when I go to verify the SQL or move out of the Query
> > text
> > pane. Though I get the above errors on other MySQL queries, I am still
> > able
> > to run ones without functions in them.
> >
> > Is there any way to configure RS to stop doing this? This is just nasty.
> > Thanks.
> > bhc
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Have you tried using the generic query designer (2 panes instead of 4). I
> >> should leave it a alone. It is one of the buttons to the right of the ...
> >> to
> >> switch to it).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition, including
> >> > the
> >> > RS upgrade. The ReportServer and reports all came over and are running
> >> > fabulously well. However, strange things are happening with my queries
> >> > in
> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after being
> >> > converted to the new report definition format. My extensive queries
> >> > have
> >> > been reformatted predominantly as straight-text (very unreadable) and
> >> > my
> >> > queries no longer pass SQL verification.
> >> >
> >> > I tried retrieving the well-formatted queries from local text file
> >> > backups,
> >> > but the new designer continues to reformat it as straight-text.
> >> >
> >> > What can I do to get the following format in my queries:
> >> > Select A,
> >> > B,
> >> > C (as string),
> >> > D
> >> > From TableA
> >> > Where [clause]
> >> >
> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> >> >
> >> > Thanks.
> >> > bhc
> >>
> >>
> >>
>
>|||I am in the process of upgrading and testing all my reports. I will look at
some of the more complex ones that go against Sybase and see how it works.
I'll let you know.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:46E4343F-8FB0-4F09-A582-D7A3F5E9AF0A@.microsoft.com...
> Yes, I have tried using just the generic query designer (that's what I'm
> used
> from RS 2000). It is the generic query designer where I'm having all the
> problems. It's very unreadable and does not parse when MySQL functions
> are
> used in the query even if the generic query designer is the only pane
> displayed. The properties on Query Designer are as follows:
> Distinct Values = No
> Output All Columns= No
> Query Parameter Identification = [nothing here]
> Query Parameter List = [greyed out, no parameters specified]
> Thoughts?
> bhc
> "Bruce L-C [MVP]" wrote:
>> Have you tried going to the generic query designer? I have no doubt you
>> are
>> seeing these problems in the 4 pane graphical query designer. What
>> happens
>> if you switch over to the generic query designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
>> >I get the following after converting to the new report definition format
>> >when
>> > I access the Data tab:
>> > Error in SELECT clause: alias not recognized.
>> > Error in list of function arguments: 'DataCenter' not recognized.
>> > Error in list of function arguments: ',' not recognized.
>> > Error in list of function arguments: '<' not recognized.
>> > Error in list of function arguments: 'out_month' not recognized.
>> > Unable to parse query text.
>> >
>> > If I let things load in the multi-pane view and use the Verify SQL
>> > button,
>> > a
>> > Microsoft Visual Database Tools window pops up with "The command is not
>> > supported by this provider".
>> >
>> > Having reviewed a few of my report queries facing this issue, I notice
>> > problems only with queries using native MySQL functions, such as
>> > date/time
>> > conversions. All functions have been encapsulated by single quotes, so
>> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
>> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile; they
>> > simply get inserted when I go to verify the SQL or move out of the
>> > Query
>> > text
>> > pane. Though I get the above errors on other MySQL queries, I am still
>> > able
>> > to run ones without functions in them.
>> >
>> > Is there any way to configure RS to stop doing this? This is just
>> > nasty.
>> > Thanks.
>> > bhc
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Have you tried using the generic query designer (2 panes instead of
>> >> 4). I
>> >> should leave it a alone. It is one of the buttons to the right of the
>> >> ...
>> >> to
>> >> switch to it).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
>> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition,
>> >> > including
>> >> > the
>> >> > RS upgrade. The ReportServer and reports all came over and are
>> >> > running
>> >> > fabulously well. However, strange things are happening with my
>> >> > queries
>> >> > in
>> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after
>> >> > being
>> >> > converted to the new report definition format. My extensive queries
>> >> > have
>> >> > been reformatted predominantly as straight-text (very unreadable)
>> >> > and
>> >> > my
>> >> > queries no longer pass SQL verification.
>> >> >
>> >> > I tried retrieving the well-formatted queries from local text file
>> >> > backups,
>> >> > but the new designer continues to reformat it as straight-text.
>> >> >
>> >> > What can I do to get the following format in my queries:
>> >> > Select A,
>> >> > B,
>> >> > C (as string),
>> >> > D
>> >> > From TableA
>> >> > Where [clause]
>> >> >
>> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
>> >> >
>> >> > Thanks.
>> >> > bhc
>> >>
>> >>
>> >>
>>|||The way the generic query designer works for me is it leaves everything
exactly the way I type it in. If it has been in the graphical designer it
can get munged up. If you clean it up it should work for you.
The properties you mentioned, I don't see anyplace to see these properties?
Where are you seeing them?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%238%23y%23UW5FHA.724@.TK2MSFTNGP14.phx.gbl...
>I am in the process of upgrading and testing all my reports. I will look at
>some of the more complex ones that go against Sybase and see how it works.
>I'll let you know.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:46E4343F-8FB0-4F09-A582-D7A3F5E9AF0A@.microsoft.com...
>> Yes, I have tried using just the generic query designer (that's what I'm
>> used
>> from RS 2000). It is the generic query designer where I'm having all the
>> problems. It's very unreadable and does not parse when MySQL functions
>> are
>> used in the query even if the generic query designer is the only pane
>> displayed. The properties on Query Designer are as follows:
>> Distinct Values = No
>> Output All Columns= No
>> Query Parameter Identification = [nothing here]
>> Query Parameter List = [greyed out, no parameters specified]
>> Thoughts?
>> bhc
>> "Bruce L-C [MVP]" wrote:
>> Have you tried going to the generic query designer? I have no doubt you
>> are
>> seeing these problems in the 4 pane graphical query designer. What
>> happens
>> if you switch over to the generic query designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
>> >I get the following after converting to the new report definition
>> >format
>> >when
>> > I access the Data tab:
>> > Error in SELECT clause: alias not recognized.
>> > Error in list of function arguments: 'DataCenter' not recognized.
>> > Error in list of function arguments: ',' not recognized.
>> > Error in list of function arguments: '<' not recognized.
>> > Error in list of function arguments: 'out_month' not recognized.
>> > Unable to parse query text.
>> >
>> > If I let things load in the multi-pane view and use the Verify SQL
>> > button,
>> > a
>> > Microsoft Visual Database Tools window pops up with "The command is
>> > not
>> > supported by this provider".
>> >
>> > Having reviewed a few of my report queries facing this issue, I notice
>> > problems only with queries using native MySQL functions, such as
>> > date/time
>> > conversions. All functions have been encapsulated by single quotes,
>> > so
>> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
>> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile;
>> > they
>> > simply get inserted when I go to verify the SQL or move out of the
>> > Query
>> > text
>> > pane. Though I get the above errors on other MySQL queries, I am
>> > still
>> > able
>> > to run ones without functions in them.
>> >
>> > Is there any way to configure RS to stop doing this? This is just
>> > nasty.
>> > Thanks.
>> > bhc
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Have you tried using the generic query designer (2 panes instead of
>> >> 4). I
>> >> should leave it a alone. It is one of the buttons to the right of the
>> >> ...
>> >> to
>> >> switch to it).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
>> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition,
>> >> > including
>> >> > the
>> >> > RS upgrade. The ReportServer and reports all came over and are
>> >> > running
>> >> > fabulously well. However, strange things are happening with my
>> >> > queries
>> >> > in
>> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after
>> >> > being
>> >> > converted to the new report definition format. My extensive
>> >> > queries
>> >> > have
>> >> > been reformatted predominantly as straight-text (very unreadable)
>> >> > and
>> >> > my
>> >> > queries no longer pass SQL verification.
>> >> >
>> >> > I tried retrieving the well-formatted queries from local text file
>> >> > backups,
>> >> > but the new designer continues to reformat it as straight-text.
>> >> >
>> >> > What can I do to get the following format in my queries:
>> >> > Select A,
>> >> > B,
>> >> > C (as string),
>> >> > D
>> >> > From TableA
>> >> > Where [clause]
>> >> >
>> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
>> >> >
>> >> > Thanks.
>> >> > bhc
>> >>
>> >>
>> >>
>>
>|||If you right-click the generic query designer, there's an option for
Properties. Those are the properties I referenced in my previous email.
I have since been poring through the Tools | Options in Report Designer to
see if there's a way to turn off the automatic quotes and perhaps the
straight-text formatting. Any idea what these options might be? Maybe I can
customize Report Designer to behave the way I want/expect.
Thoughts?
"Bruce L-C [MVP]" wrote:
> The way the generic query designer works for me is it leaves everything
> exactly the way I type it in. If it has been in the graphical designer it
> can get munged up. If you clean it up it should work for you.
> The properties you mentioned, I don't see anyplace to see these properties?
> Where are you seeing them?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%238%23y%23UW5FHA.724@.TK2MSFTNGP14.phx.gbl...
> >I am in the process of upgrading and testing all my reports. I will look at
> >some of the more complex ones that go against Sybase and see how it works.
> >I'll let you know.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> > news:46E4343F-8FB0-4F09-A582-D7A3F5E9AF0A@.microsoft.com...
> >> Yes, I have tried using just the generic query designer (that's what I'm
> >> used
> >> from RS 2000). It is the generic query designer where I'm having all the
> >> problems. It's very unreadable and does not parse when MySQL functions
> >> are
> >> used in the query even if the generic query designer is the only pane
> >> displayed. The properties on Query Designer are as follows:
> >> Distinct Values = No
> >> Output All Columns= No
> >> Query Parameter Identification = [nothing here]
> >> Query Parameter List = [greyed out, no parameters specified]
> >>
> >> Thoughts?
> >> bhc
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> Have you tried going to the generic query designer? I have no doubt you
> >> are
> >> seeing these problems in the 4 pane graphical query designer. What
> >> happens
> >> if you switch over to the generic query designer.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> news:ABF940A7-4625-4F04-8A88-D26B05E8B78B@.microsoft.com...
> >> >I get the following after converting to the new report definition
> >> >format
> >> >when
> >> > I access the Data tab:
> >> > Error in SELECT clause: alias not recognized.
> >> > Error in list of function arguments: 'DataCenter' not recognized.
> >> > Error in list of function arguments: ',' not recognized.
> >> > Error in list of function arguments: '<' not recognized.
> >> > Error in list of function arguments: 'out_month' not recognized.
> >> > Unable to parse query text.
> >> >
> >> > If I let things load in the multi-pane view and use the Verify SQL
> >> > button,
> >> > a
> >> > Microsoft Visual Database Tools window pops up with "The command is
> >> > not
> >> > supported by this provider".
> >> >
> >> > Having reviewed a few of my report queries facing this issue, I notice
> >> > problems only with queries using native MySQL functions, such as
> >> > date/time
> >> > conversions. All functions have been encapsulated by single quotes,
> >> > so
> >> > instead of 60-SECOND(A.TIMESTAMP), I now have to contend with
> >> > 60-'SECOND'(A.TIMESTAMP). Any attempts to remove them are futile;
> >> > they
> >> > simply get inserted when I go to verify the SQL or move out of the
> >> > Query
> >> > text
> >> > pane. Though I get the above errors on other MySQL queries, I am
> >> > still
> >> > able
> >> > to run ones without functions in them.
> >> >
> >> > Is there any way to configure RS to stop doing this? This is just
> >> > nasty.
> >> > Thanks.
> >> > bhc
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Have you tried using the generic query designer (2 panes instead of
> >> >> 4). I
> >> >> should leave it a alone. It is one of the buttons to the right of the
> >> >> ...
> >> >> to
> >> >> switch to it).
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> >> news:40DB9C60-A240-4B6E-9020-E79DEBEA613A@.microsoft.com...
> >> >> > Yikes! I have upgraded to SQL Server 2005 Developer Edition,
> >> >> > including
> >> >> > the
> >> >> > RS upgrade. The ReportServer and reports all came over and are
> >> >> > running
> >> >> > fabulously well. However, strange things are happening with my
> >> >> > queries
> >> >> > in
> >> >> > Intelligence Studio (and VS Studio 2005, I've tried both) after
> >> >> > being
> >> >> > converted to the new report definition format. My extensive
> >> >> > queries
> >> >> > have
> >> >> > been reformatted predominantly as straight-text (very unreadable)
> >> >> > and
> >> >> > my
> >> >> > queries no longer pass SQL verification.
> >> >> >
> >> >> > I tried retrieving the well-formatted queries from local text file
> >> >> > backups,
> >> >> > but the new designer continues to reformat it as straight-text.
> >> >> >
> >> >> > What can I do to get the following format in my queries:
> >> >> > Select A,
> >> >> > B,
> >> >> > C (as string),
> >> >> > D
> >> >> > From TableA
> >> >> > Where [clause]
> >> >> >
> >> >> > I'm querying against a MySQL 4.0 database with MyODBC 3.5.1 driver.
> >> >> >
> >> >> > Thanks.
> >> >> > bhc
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
> >
>
>sql
Wednesday, March 21, 2012
Query fails.
I have a two development environments running both RS and Database.
Everything works fine. I then set up a test environment with RS on a seperate
server and using a the database server for both reportserver database as well
as datasource for the reports. (medium deployment scenario). I use OLAP cubes
as data source for may reports. When requesting the database from the Test
webserver(with RS) I get OK reports a couple of times. BUT...when I access
the report a third, fourth or fifth time ... I get timeouts and errors in the
ReportServer log stating that the MDX failed. Same MDX works fine in two
different dev environments as well as when using the Test database from one
of the dev servers as datasource... I get confused by this behaviour... The
Test RS server is a w2k server with all necessary SPs... Could this have
something to do with buffersizes used by aspnet_wp ?
The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.When the query fails the aspnet_wp locks and no new requests are processed
until this worker process is restarted...
"GunnarL" wrote:
> I have a two development environments running both RS and Database.
> Everything works fine. I then set up a test environment with RS on a seperate
> server and using a the database server for both reportserver database as well
> as datasource for the reports. (medium deployment scenario). I use OLAP cubes
> as data source for may reports. When requesting the database from the Test
> webserver(with RS) I get OK reports a couple of times. BUT...when I access
> the report a third, fourth or fifth time ... I get timeouts and errors in the
> ReportServer log stating that the MDX failed. Same MDX works fine in two
> different dev environments as well as when using the Test database from one
> of the dev servers as datasource... I get confused by this behaviour... The
> Test RS server is a w2k server with all necessary SPs... Could this have
> something to do with buffersizes used by aspnet_wp ?
> The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
> and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.
Everything works fine. I then set up a test environment with RS on a seperate
server and using a the database server for both reportserver database as well
as datasource for the reports. (medium deployment scenario). I use OLAP cubes
as data source for may reports. When requesting the database from the Test
webserver(with RS) I get OK reports a couple of times. BUT...when I access
the report a third, fourth or fifth time ... I get timeouts and errors in the
ReportServer log stating that the MDX failed. Same MDX works fine in two
different dev environments as well as when using the Test database from one
of the dev servers as datasource... I get confused by this behaviour... The
Test RS server is a w2k server with all necessary SPs... Could this have
something to do with buffersizes used by aspnet_wp ?
The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.When the query fails the aspnet_wp locks and no new requests are processed
until this worker process is restarted...
"GunnarL" wrote:
> I have a two development environments running both RS and Database.
> Everything works fine. I then set up a test environment with RS on a seperate
> server and using a the database server for both reportserver database as well
> as datasource for the reports. (medium deployment scenario). I use OLAP cubes
> as data source for may reports. When requesting the database from the Test
> webserver(with RS) I get OK reports a couple of times. BUT...when I access
> the report a third, fourth or fifth time ... I get timeouts and errors in the
> ReportServer log stating that the MDX failed. Same MDX works fine in two
> different dev environments as well as when using the Test database from one
> of the dev servers as datasource... I get confused by this behaviour... The
> Test RS server is a w2k server with all necessary SPs... Could this have
> something to do with buffersizes used by aspnet_wp ?
> The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
> and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.
Query Execution Speed
Hi there - i'm hoping someone can help me!
I'm having a problem with a live database that i'm running on MSDE - It
seems to have slowed down quite considerably from the test environment
(even when all the data is the same). The is notably different on one
particular query that takes 1 sec on the test machine and almost 1 min
on the live machine
The total number of user connections on the live machine is normally 4
or so (found out through the Performance monitor). So I can't see that
it's MSDE's performance throttler...
Has anybody got any ideas on things i can check for?
Many thanks
James
I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!
|||I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!
Help gratefully accepted!!
|||Do the execution plans look different on the two servers? We had
similar problems and it turned out that the one plan used parallelism
and the other didn't.
|||It seems that the execution plans are different!!! I'm a bit new at
this kind of configuration with SQL Server... what do I need to change
to make the execution plans the same on both machines?
|||This is not unlike how two people perform the same task and get the
same result, but the process of performing it differs. In this case, I
assume the servers are not exactly the same, the physical distribution
of data on disk could be different, the load on the server could be
different, etc.
In general terms, I would try the following:
1. Update statistics on both servers, then compare execution plans
again.
2. Add query hints on the slower plan to get the desired result if the
above does not change things.
How are the plans different?
|||Sorry, I didnt' explain myself properly and I guess used the wrong
terminology somewhere along the line!!
Every evening, a copy of the database is made to another database on
the same instance of SQL Server. When I execute the SP on this
'snapshot' database it only takes a few seconds, but when I execute the
original it takes up to a minute or so.
So - the database is on the same SQL Server and has exactly (albeit to
a few hours) the same data as the live database, yet the execution
plans are different and the speed is dramatically different!!
|||Here are the two execution plans (I hope this is the right format that
you can understand...)
Query running on live data (slow):
|--Sort(ORDER BY
[Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE[Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Filter(WHEREIf ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If
([Expr1013]=0) then 0 else (
|--Nested Loops(Left Outer Join, OUTER
REFERENCES[StockLevel].[StockLine_ID]))
|--Bookmark Lookup(BOOKMARK[Bmk1011]),
OBJECT[foodcontrolSQL].[dbo].[Supplier]))
| |--Nested Loops(Left Outer Join, OUTER
REFERENCES[Product].[Supplier_ID]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES[StockLine].[StockLine_ID]) WITH PREFETCH)
| |
|--Filter(WHERE[StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL) then If
(Convert([@.ExcludeFutureDelist])=1) then 0 el
| | | |--Bookmark
Lookup(BOOKMARK[Bmk1007]),
OBJECT[foodcontrolSQL].[dbo].[StockLine]))
| | | |--Nested
Loops(Inner Join, OUTER REFERENCES[Product].[Product_ID]) WITH
PREFETCH)
| | | |--Table
Scan(OBJECT[foodcontrolSQL].[dbo].[Product]),
WHERE[Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
t
| | | |--Index
Seek(OBJECT[foodcontrolSQL].[dbo].[StockLine].[Product_ID]),
SEEK[StockLine].[Product_ID]=[Product].[Product_ID]) ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT[foodcontrolSQL].[dbo].[StockLevel].[PK__StockLevelBackup__5E74FADA]),
SEEK[StockLevel].[StockLocation_ID]=1 AND
[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]), WHERE[S
| |--Index
Seek(OBJECT[foodcontrolSQL].[dbo].[Supplier].[Supplier_ID]),
SEEK[Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)
|--Compute
Scalar(DEFINE[Expr1013]=Convert([Expr1004])/Convert([@.NumDays])))
|--Compute Scalar(DEFINE[Expr1004]=If
([Expr1032]=0) then NULL else [Expr1033]))
|--Stream
Aggregate(DEFINE[Expr1032]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1033]=SUM([StockTransaction].[StockAdjustmentQuantity])))
|--Filter(WHERE(([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]
AND [StockTransaction].[ExcludeROS]=If ([@.ExcludeROS]<>NULL) then
[@.ExcludeROS] else [StockTransaction].[ExcludeROS]) AND [StockTrans
|--Bookmark
Lookup(BOOKMARK[Bmk1000]),
OBJECT[foodcontrolSQL].[dbo].[StockTransaction]))
|--Index
Seek(OBJECT[foodcontrolSQL].[dbo].[StockTransaction].[StockTransaction11]),
SEEK[StockTransaction].[TransactionDate] >= [@.StartDate] AND
[StockTransaction].[TransactionDate] <= [@.EndDate]) OR
Query running on snapshot data (fast):
|--Sort(ORDER BY[Expr1016] ASC, [Expr1017] ASC))
|--Compute
Scalar(DEFINE[Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],
[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Bookmark Lookup(BOOKMARK[Bmk1011]),
OBJECT[foodcontrolSnapshot].[dbo].[Supplier]))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES[Product].[Supplier_ID]))
|--Hash Match(Inner Join,
HASH[StockLine].[Product_ID])=([Product].[Product_ID]),
RESIDUAL[Product].[Product_ID]=[StockLine].[Product_ID]))
| |--Merge Join(Inner Join,
MERGE[StockLine].[StockLine_ID])=([StockLevel].[StockLine_ID]),
RESIDUAL[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]))
| | |--Sort(ORDER
BY[StockLine].[StockLine_ID] ASC))
| | | |--Table
Scan(OBJECT[foodcontrolSnapshot].[dbo].[StockLine]),
WHERE[StockLine].[StockLineStatus_ID]=1 AND
Convert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL)
| | |--Filter(WHEREIf ([Expr1013]=0)
then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If ([Expr10
| | |--Merge Join(Left Outer
Join,
MERGE[StockLevel].[StockLine_ID])=([StockTransaction].[StockLine_ID]),
RESIDUAL[StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]))
| | |--Clustered Index
Seek(OBJECT[foodcontrolSnapshot].[dbo].[StockLevel].[PK__StockLevel__6DA725A5]),
SEEK[StockLevel].[StockLocation_ID]=1),
WHERE[StockLevel].[CurrentStockQuantity]-[StockLevel].[Committ
| | |--Compute
Scalar(DEFINE[Expr1013]=Convert([Expr1004])/Convert([@.NumDays])))
| | |--Compute
Scalar(DEFINE[Expr1004]=If ([Expr1035]=0) then NULL else [Expr1036]))
| | |--Stream
Aggregate(GROUP BY[StockTransaction].[StockLine_ID])
DEFINE[Expr1035]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),
[Expr1036]=SUM([StockTransaction].[StockAdjustmentQuantity
| |
|--Sort(ORDER BY[StockTransaction].[StockLine_ID] ASC))
| |
|--Clustered Index
Scan(OBJECT[foodcontrolSnapshot].[dbo].[StockTransaction].[PK_StockTransaction]),
WHERE((([StockTransaction].[StockLocation_ID]=1 AND
[StockTransaction].[StockTransac
| |--Table
Scan(OBJECT[foodcontrolSnapshot].[dbo].[Product]),
WHERE[Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If
(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
then If (Convert([@.Su
|--Index
Seek(OBJECT[foodcontrolSnapshot].[dbo].[Supplier].[aaaaaSupplier_PK]),
SEEK[Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDERED
FORWARD)
|||Just in case anybody is reading this topic and wants to know how i
solved the problem, I've run the command
UPDATE STATISTICS tablename
for each table that was dependent on the query and it's made the
queries run nice and fast again!!
I'm having a problem with a live database that i'm running on MSDE - It
seems to have slowed down quite considerably from the test environment
(even when all the data is the same). The is notably different on one
particular query that takes 1 sec on the test machine and almost 1 min
on the live machine
The total number of user connections on the live machine is normally 4
or so (found out through the Performance monitor). So I can't see that
it's MSDE's performance throttler...
Has anybody got any ideas on things i can check for?
Many thanks
James
I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!
|||I've done some more investigation ... I create a snapshot of the
database every evening so this is effectively the same data as at
midnight the previous day. If I run the stored procedure on the same
instance of SQL server but the snapshot database it executes in a
couple of seconds. Is there anything that could be slowing down this
one database? I've turned off the autoclose and autoshrink on it - but
the other copies have this set anyway!!
Help gratefully accepted!!
|||Do the execution plans look different on the two servers? We had
similar problems and it turned out that the one plan used parallelism
and the other didn't.
|||It seems that the execution plans are different!!! I'm a bit new at
this kind of configuration with SQL Server... what do I need to change
to make the execution plans the same on both machines?
|||This is not unlike how two people perform the same task and get the
same result, but the process of performing it differs. In this case, I
assume the servers are not exactly the same, the physical distribution
of data on disk could be different, the load on the server could be
different, etc.
In general terms, I would try the following:
1. Update statistics on both servers, then compare execution plans
again.
2. Add query hints on the slower plan to get the desired result if the
above does not change things.
How are the plans different?
|||Sorry, I didnt' explain myself properly and I guess used the wrong
terminology somewhere along the line!!
Every evening, a copy of the database is made to another database on
the same instance of SQL Server. When I execute the SP on this
'snapshot' database it only takes a few seconds, but when I execute the
original it takes up to a minute or so.
So - the database is on the same SQL Server and has exactly (albeit to
a few hours) the same data as the live database, yet the execution
plans are different and the speed is dramatically different!!
|||Here are the two execution plans (I hope this is the right format that
you can understand...)
Query running on live data (slow):
|--Sort(ORDER BY
[Expr1016] ASC, [Expr1017] ASC))|--Compute
Scalar(DEFINE
[Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Filter(WHERE
If ([Expr1013]=0) then 0 else(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If
([Expr1013]=0) then 0 else (
|--Nested Loops(Left Outer Join, OUTER
REFERENCES
[StockLevel].[StockLine_ID]))|--Bookmark Lookup(BOOKMARK
[Bmk1011]),OBJECT
[foodcontrolSQL].[dbo].[Supplier]))| |--Nested Loops(Left Outer Join, OUTER
REFERENCES
[Product].[Supplier_ID]))| |--Nested Loops(Inner Join, OUTER
REFERENCES
[StockLine].[StockLine_ID]) WITH PREFETCH)| |
|--Filter(WHERE
[StockLine].[StockLineStatus_ID]=1 ANDConvert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL) then If
(Convert([@.ExcludeFutureDelist])=1) then 0 el
| | | |--Bookmark
Lookup(BOOKMARK
[Bmk1007]),OBJECT
[foodcontrolSQL].[dbo].[StockLine]))| | | |--Nested
Loops(Inner Join, OUTER REFERENCES
[Product].[Product_ID]) WITHPREFETCH)
| | | |--Table
Scan(OBJECT
[foodcontrolSQL].[dbo].[Product]),WHERE
[Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
t
| | | |--Index
Seek(OBJECT
[foodcontrolSQL].[dbo].[StockLine].[Product_ID]),SEEK
[StockLine].[Product_ID]=[Product].[Product_ID]) ORDERED FORWARD)| | |--Clustered Index
Seek(OBJECT
[foodcontrolSQL].[dbo].[StockLevel].[PK__StockLevelBackup__5E74FADA]),SEEK
[StockLevel].[StockLocation_ID]=1 AND[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]), WHERE
[S| |--Index
Seek(OBJECT
[foodcontrolSQL].[dbo].[Supplier].[Supplier_ID]),SEEK
[Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDEREDFORWARD)
|--Compute
Scalar(DEFINE
[Expr1013]=Convert([Expr1004])/Convert([@.NumDays])))|--Compute Scalar(DEFINE
[Expr1004]=If([Expr1032]=0) then NULL else [Expr1033]))
|--Stream
Aggregate(DEFINE
[Expr1032]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),[Expr1033]=SUM([StockTransaction].[StockAdjustmentQuantity])))
|--Filter(WHERE
(([StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]AND [StockTransaction].[ExcludeROS]=If ([@.ExcludeROS]<>NULL) then
[@.ExcludeROS] else [StockTransaction].[ExcludeROS]) AND [StockTrans
|--Bookmark
Lookup(BOOKMARK
[Bmk1000]),OBJECT
[foodcontrolSQL].[dbo].[StockTransaction]))|--Index
Seek(OBJECT
[foodcontrolSQL].[dbo].[StockTransaction].[StockTransaction11]),SEEK
[StockTransaction].[TransactionDate] >= [@.StartDate] AND[StockTransaction].[TransactionDate] <= [@.EndDate]) OR
Query running on snapshot data (fast):
|--Sort(ORDER BY
[Expr1016] ASC, [Expr1017] ASC))|--Compute
Scalar(DEFINE
[Expr1014]=[StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity],[Expr1015]=If ([Expr1013]=0) then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr
|--Bookmark Lookup(BOOKMARK
[Bmk1011]),OBJECT
[foodcontrolSnapshot].[dbo].[Supplier]))|--Nested Loops(Left Outer Join, OUTER
REFERENCES
[Product].[Supplier_ID]))|--Hash Match(Inner Join,
HASH
[StockLine].[Product_ID])=([Product].[Product_ID]),RESIDUAL
[Product].[Product_ID]=[StockLine].[Product_ID]))| |--Merge Join(Inner Join,
MERGE
[StockLine].[StockLine_ID])=([StockLevel].[StockLine_ID]),RESIDUAL
[StockLevel].[StockLine_ID]=[StockLine].[StockLine_ID]))| | |--Sort(ORDER
BY
[StockLine].[StockLine_ID] ASC))| | | |--Table
Scan(OBJECT
[foodcontrolSnapshot].[dbo].[StockLine]),WHERE
[StockLine].[StockLineStatus_ID]=1 ANDConvert([StockLine].[IsFutureDelist])=If (If
(Convert([@.ExcludeFutureDelist])=1) then 0 else NULL<>NULL)
| | |--Filter(WHERE
If ([Expr1013]=0)then 0 else
(Convert(([StockLevel].[CurrentStockQuantity]-[StockLevel].[CommittedStockQuantity]))/[Expr1013])<=If
([@.MinDaysCover]<>NULL) then Convert([@.MinDaysCover]) else If ([Expr10
| | |--Merge Join(Left Outer
Join,
MERGE
[StockLevel].[StockLine_ID])=([StockTransaction].[StockLine_ID]),RESIDUAL
[StockTransaction].[StockLine_ID]=[StockLevel].[StockLine_ID]))| | |--Clustered Index
Seek(OBJECT
[foodcontrolSnapshot].[dbo].[StockLevel].[PK__StockLevel__6DA725A5]),SEEK
[StockLevel].[StockLocation_ID]=1),WHERE
[StockLevel].[CurrentStockQuantity]-[StockLevel].[Committ| | |--Compute
Scalar(DEFINE
[Expr1013]=Convert([Expr1004])/Convert([@.NumDays])))| | |--Compute
Scalar(DEFINE
[Expr1004]=If ([Expr1035]=0) then NULL else [Expr1036]))| | |--Stream
Aggregate(GROUP BY
[StockTransaction].[StockLine_ID])DEFINE
[Expr1035]=COUNT_BIG([StockTransaction].[StockAdjustmentQuantity]),[Expr1036]=SUM([StockTransaction].[StockAdjustmentQuantity
| |
|--Sort(ORDER BY
[StockTransaction].[StockLine_ID] ASC))| |
|--Clustered Index
Scan(OBJECT
[foodcontrolSnapshot].[dbo].[StockTransaction].[PK_StockTransaction]),WHERE
((([StockTransaction].[StockLocation_ID]=1 AND[StockTransaction].[StockTransac
| |--Table
Scan(OBJECT
[foodcontrolSnapshot].[dbo].[Product]),WHERE
[Product].[ProductStatus_ID]=2 AND [Product].[Supplier_ID]=If(If (Convert([@.SupplierFilter])=1) then [@.Supplier_ID] else NULL<>NULL)
then If (Convert([@.Su
|--Index
Seek(OBJECT
[foodcontrolSnapshot].[dbo].[Supplier].[aaaaaSupplier_PK]),SEEK
[Supplier].[Supplier_ID]=[Product].[Supplier_ID]) ORDEREDFORWARD)
|||Just in case anybody is reading this topic and wants to know how i
solved the problem, I've run the command
UPDATE STATISTICS tablename
for each table that was dependent on the query and it's made the
queries run nice and fast again!!
Subscribe to:
Posts (Atom)