MS SQL Data Warehousing - UBO - Wizard fails because of Group or Order By clause(s)

Author

UBO - Wizard fails because of Group or Order By clause(s)

stevefromoz

2006-10-25, 6:00 am

Hi Dev guys,

Have hit a snag trying to use Usage Based Optimization (64bit AS 2K5
install, olapquerylog on SQL2K 32bit). Going through the wizard I can
select the partition(s) I'm interested in applying the aggregation design to
but on the next screen (filter screen for the queries in the query log) as
soon as I click 'Next' I get the following error. Below the error I've
pasted the two queries that are being passed from AS2K5 to the SQL instance
that holds the log table. The first runs without issue, it's the second one
with the 'option (Robust Plan) ' option that is causing the problem. For
some reason it believes that the total length of the columns in the (I'm
assuming) GROUP BY is greater than 8000 bytes. Seems like an impossibility
seeing as the two fields are both 4000 bytes each (unless 4000 + 4000 =
number > 8000!?!?!). Removing the option (test running the querying in ol'
Query Analyzer) does return a resultset (also pasted below).

Any workarounds (other than writing up a quick winforms app to apply the UBO
aggregations myself)?

Cheers,

Steve.

====================
===============

This wizard will close because it encountered the following error:
(Microsoft SQL Server)

------------------------------
For help, click:
http://go.microsoft.com/fwlink? Pro...on&LinkId=20476

====================
===============

Warning: The query processor could not produce a query plan from the
optimizer because the total length of all the columns in the GROUP BY or
ORDER BY clause exceeds 8000 bytes. Resubmit your query without the ROBUST
PLAN hint. (Microsoft SQL Native Client)

------------------------------
Program Location:

at System.Data.OleDb.OleDbDataReader. ProcessResults(OleDb
HResult hr)
at System.Data.OleDb.OleDbDataReader.NextResult()
at System.Data.OleDb.OleDbCommand. ExecuteReaderInterna
l(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand. ExecuteReader(Comman
dBehavior behavior)
at System.Data.OleDb.OleDbCommand. ExecuteDbDataReader(
CommandBehavior
behavior)
at System.Data.Common.DbCommand. ExecuteReader(Comman
dBehavior behavior)
at Microsoft.DataWarehouse.Design. DataSourceConnection
.Fill(DataSet
dataSet, String sql, CommandBehavior commandBehavior)
at Microsoft.DataWarehouse.Design. DataSourceConnection
.Fill(DataSet
dataSet, String sql)
at Microsoft.AnalysisServices.Wizards.ReviewQueriesPage.LoadGrid()
at
Microsoft.AnalysisServices.Wizards.ReviewQueriesPage. OnEnterPage(EventArg
s
e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage
nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender,
EventArgs e)

********************
END OF ERROR BIT ********************
*

********************
Start of first traced query from AS2K5 to SQL2K
(querylog) ********************
*

Select Count( MSOLAP_ObjectPath ), Count( Distinct MSOLAP_User ),
Count( Distinct Dataset ), Avg(Duration ), Min(StartTime ),
Max(StartTime ) From [OlapQueryLog] Where ( & #91;MSOLAP_Database]
=
N'Lineitem' ) And ( & #91;MSOLAP_ObjectPat
h] =
N'GRMOLAP01.Lineitem.CUBE_Lineitem. vw_Fact_COLineitem_1
' )

********************
End of first traced query from AS2K5 to SQL2K
(querylog) ********************
*

********************
Start of second traced query from AS2K5 to SQL2K
(querylog) ********************
*
********************
********************
********************
********************
***********
********************
***************This is the offending query
********************
****************
********************
********************
********************
********************
***********

SELECT & #91;MSOLAP_ObjectPat
h],& #91;Dataset],Count(M
SOLAP_ObjectPath) AS
& #91;CountOfObjectPat
h],Avg(Duration) AS & #91;AverageOfDuratio
n]
FROM [dbo].[OlapQueryLog] AS & #91;UBO_QueryTableAl
ias]
WHERE ( & #91;MSOLAP_Database]
= N'Lineitem' ) And ( & #91;MSOLAP_ObjectPat
h] =
N'GRMOLAP01.Lineitem.CUBE_Lineitem. vw_Fact_COLineitem_1
' ) Group by
MSOLAP_ObjectPath, Dataset Order by CountOfObjectPath Desc Option (Robust
Plan)

********************
End of second traced query from AS2K5 to SQL2K
(querylog) ********************
*

********************
Start of resultset from querylog, modified the object
names to protect the innocent :P ********************
*
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,00000
0000000,00,000 171 0
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,00000
1000000,00,000 91 1
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,00000
1000000,01,000 73 10
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00001,000,0000,00000
0000000,00,000 22 17
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00001,000,0000,00000
1000000,00,000 20 259
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,00000
1000000,00,000 20 27663
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,00000
0000000,00,000 18 0
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,00000
1010000,00,000 12 0
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,00000
1010000,01,000 8 7281
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,00000
1000000,01,000 4 2847
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,00000
1010000,00,000 4 16199
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,001,0000,00000
1010001,00,000 4 16324
AServer.AnASDatabase.AnASCube.AnASMeasureGroup
00000,000,0000,00000
0000000,01,000 4 19
********************
End of resultset from querylog ********************
*

stevefromoz

2006-10-25, 6:00 am

On thinking about this and looking at the table structure again, am now
thinking this could be a bug. The field types for the MSOlap_ObjectPath and
DataSet fields are both nvarchar, which probably means that they're
double-byte in size, so both of them being a size of 4000 may really mean
that their both 8000 bytes, hence the total would be 16k bytes, well above
the 8000 byte SQL limit.

Your thoughts??

Steve.

"stevefromoz" < steven_h_allen@hotma
il.isnotmail.com> wrote in message
news:uQmlnoX6GHA.3620@TK2MSFTNGP04.phx.gbl...
> Hi Dev guys,
>
> Have hit a snag trying to use Usage Based Optimization (64bit AS 2K5
> install, olapquerylog on SQL2K 32bit). Going through the wizard I can
> select the partition(s) I'm interested in applying the aggregation design
> to but on the next screen (filter screen for the queries in the query log)
> as soon as I click 'Next' I get the following error. Below the error I've
> pasted the two queries that are being passed from AS2K5 to the SQL
> instance that holds the log table. The first runs without issue, it's the
> second one with the 'option (Robust Plan) ' option that is causing the
> problem. For some reason it believes that the total length of the columns
> in the (I'm assuming) GROUP BY is greater than 8000 bytes. Seems like an
> impossibility seeing as the two fields are both 4000 bytes each (unless
> 4000 + 4000 = number > 8000!?!?!). Removing the option (test running the
> querying in ol' Query Analyzer) does return a resultset (also pasted
> below).
>
> Any workarounds (other than writing up a quick winforms app to apply the
> UBO aggregations myself)?
>
> Cheers,
>
> Steve.
>
> ====================
===============
>
> This wizard will close because it encountered the following error:
> (Microsoft SQL Server)
>
> ------------------------------
> For help, click:
> http://go.microsoft.com/fwlink? Pro...on&LinkId=20476
>
> ====================
===============
>
> Warning: The query processor could not produce a query plan from the
> optimizer because the total length of all the columns in the GROUP BY or
> ORDER BY clause exceeds 8000 bytes. Resubmit your query without the
> ROBUST PLAN hint. (Microsoft SQL Native Client)
>
> ------------------------------
> Program Location:
>
> at System.Data.OleDb.OleDbDataReader. ProcessResults(OleDb
HResult hr)
> at System.Data.OleDb.OleDbDataReader.NextResult()
> at System.Data.OleDb.OleDbCommand. ExecuteReaderInterna
l(CommandBehavior
> behavior, String method)
> at System.Data.OleDb.OleDbCommand. ExecuteReader(Comman
dBehavior
> behavior)
> at System.Data.OleDb.OleDbCommand. ExecuteDbDataReader(
CommandBehavior
> behavior)
> at System.Data.Common.DbCommand. ExecuteReader(Comman
dBehavior behavior)
> at Microsoft.DataWarehouse.Design. DataSourceConnection
.Fill(DataSet
> dataSet, String sql, CommandBehavior commandBehavior)
> at Microsoft.DataWarehouse.Design. DataSourceConnection
.Fill(DataSet
> dataSet, String sql)
> at Microsoft.AnalysisServices.Wizards.ReviewQueriesPage.LoadGrid()
> at
> Microsoft.AnalysisServices.Wizards.ReviewQueriesPage. OnEnterPage(EventArg
s
> e)
> at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
> at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage
> nextPage)
> at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object
> sender, EventArgs e)
>
> ********************
END OF ERROR BIT ********************
*
>
>
> ********************
Start of first traced query from AS2K5 to SQL2K
> (querylog) ********************
*
>
> Select Count( MSOLAP_ObjectPath ), Count( Distinct MSOLAP_User ),
> Count( Distinct Dataset ), Avg(Duration ), Min(StartTime ),
> Max(StartTime ) From [OlapQueryLog] Where ( & #91;MSOLAP_Database]
=
> N'Lineitem' ) And ( & #91;MSOLAP_ObjectPat
h] =
> N'GRMOLAP01.Lineitem.CUBE_Lineitem. vw_Fact_COLineitem_1
' )
>
> ********************
End of first traced query from AS2K5 to SQL2K
> (querylog) ********************
*
>
> ********************
Start of second traced query from AS2K5 to SQL2K
> (querylog) ********************
*
> ********************
********************
********************
********************
***********
> ********************
***************This is the offending query
> ********************
****************
> ********************
********************
********************
********************
***********
>
> SELECT & #91;MSOLAP_ObjectPat
h],& #91;Dataset],Count(M
SOLAP_ObjectPath) AS
> & #91;CountOfObjectPat
h],Avg(Duration) AS & #91;AverageOfDuratio
n]
> FROM [dbo].[OlapQueryLog] AS & #91;UBO_QueryTableAl
ias]
> WHERE ( & #91;MSOLAP_Database]
= N'Lineitem' ) And ( & #91;MSOLAP_ObjectPat
h] =
> N'GRMOLAP01.Lineitem.CUBE_Lineitem. vw_Fact_COLineitem_1
' ) Group by
> MSOLAP_ObjectPath, Dataset Order by CountOfObjectPath Desc Option
> (Robust Plan)
>
> ********************
End of second traced query from AS2K5 to SQL2K
> (querylog) ********************
*
>
>
> ********************
Start of resultset from querylog, modified the
> object names to protect the innocent :P ********************
*
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,000,0000,00000
0000000,00,000 171 0
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,000,0000,00000
1000000,00,000 91 1
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,000,0000,00000
1000000,01,000 73 10
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00001,000,0000,00000
0000000,00,000 22 17
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00001,000,0000,00000
1000000,00,000 20 259
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,001,0000,00000
1000000,00,000 20 27663
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,001,0000,00000
0000000,00,000 18 0
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,000,0000,00000
1010000,00,000 12 0
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,000,0000,00000
1010000,01,000 8 7281
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,001,0000,00000
1000000,01,000 4 2847
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,001,0000,00000
1010000,00,000 4 16199
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,001,0000,00000
1010001,00,000 4 16324
> AServer.AnASDatabase.AnASCube.AnASMeasureGroup
> 00000,000,0000,00000
0000000,01,000 4 19
> ********************
End of resultset from querylog ********************
*
>
>

stevefromoz

2006-10-25, 6:00 am

Well, did the unthinkable and changed the datatype from nvarchar to varchar
on the OlapQueryLog table and low and behold, the wizard now works again
(well, probably more accurately the SQL being passed is now
validated/accepted by SQL server).

I'm still going with 'this is a bug'.

Cheers,

Steve.

"stevefromoz" < steven_h_allen@hotma
il.isnotmail.com> wrote in message
news:OJ$4slY6GHA.3760@TK2MSFTNGP02.phx.gbl...
> On thinking about this and looking at the table structure again, am now
> thinking this could be a bug. The field types for the MSOlap_ObjectPath
> and DataSet fields are both nvarchar, which probably means that they're
> double-byte in size, so both of them being a size of 4000 may really mean
> that their both 8000 bytes, hence the total would be 16k bytes, well above
> the 8000 byte SQL limit.
>
> Your thoughts??
>
> Steve.
>
>
> "stevefromoz" < steven_h_allen@hotma
il.isnotmail.com> wrote in message
> news:uQmlnoX6GHA.3620@TK2MSFTNGP04.phx.gbl...
>
>

Sponsored Links