14 Replies Latest reply on Aug 16, 2016 12:58 PM by gregor

    Error Event Collection Exceeded The Maximum allowed 

    QasimGulzar

      I am getting error while query PIOLEDB. Event Collection Exceeded The Maximum allowed . snapshot is attached please guide me how to fix this issue.

       

      error-1.jpg

        • Re: Error Event Collection Exceeded The Maximum allowed 
          lorjales

          Hi Qasim,

          See below KB article:

          KB00347 - Error [-11091] when running a PI OLEDB non pass-through query using a linked server

          Normally, you would resolve error "[-11091] Event collection exceeded the  maximum allowed" by increasing the ArcMaxCollect parameter on the PI Server.  However, the error presented with the linked server is not necessarily the  result of a low ArcMaxCollect parameter setting. Instead, it can be the  result of an incorrect query plan. The possibility of an incorrect query plan should be investigated before increasing the ArcMaxCollect parameter.

          Thanks

          Luis

          • Re: Error Event Collection Exceeded The Maximum allowed 
            pthivierge

            Hello Quasim,

             

            That would really help to see your query, is it possible to post it in this discussion?

              • Re: Error Event Collection Exceeded The Maximum allowed 
                QasimGulzar

                SELECT time,value FROM piarchive..picomp2 WHERE tag = '<TAG_NAME>'

                  • Re: Error Event Collection Exceeded The Maximum allowed 
                    gregor

                    Hello Qasim,

                     

                    When querying for time series data, please always consider adding a time restriction to the WHERE-Clause. Without any time constrained when asking for archived data but as well when asking for calculated or sampled data, archive queries can become very expensive because the PI point may have millions, billions or more events archived. Please see below for some examples on how to restrict the query period.

                     

                    For the past 1 hour   

                    SELECT * FROM piarchive..picomp2 WHERE tag = 'sinusoid' AND time BETWEEN '*-1h' AND '*'

                     

                    Between yesterday midnight and today midnight   

                    SELECT * FROM piarchive..picomp2 WHERE tag = 'sinusoid' AND time BETWEEN 'y' AND 't'

                     

                    Since a specific date and time (until now)  

                    SELECT * FROM piarchive..picomp2 WHERE tag = 'sinusoid' AND time > '10-Apr-2016 10:00 AM' 
                      • Re: Error Event Collection Exceeded The Maximum allowed 
                        QasimGulzar

                        but what if i need all those archived events in one go is it possible ?? i

                          • Re: Error Event Collection Exceeded The Maximum allowed 
                            pthivierge

                            Hello Qasim,

                             

                            This is possible to avoid this error by increasing the parameter ArcMaxCollect. I would however not recommend it.

                             

                            I would typically avoid to perform unconstrained queries because the data density can be different from one tag to another and this can cause your application to behave in an unpredictable manner:

                            So, that would be preferable that you chunk the query in several smaller calls ( e.g. get data for 3 months at the time, but you will need to find the sweet spot between performing too many calls and querying too much data).  For each call, you should have your program processing the data.  Then you repeat for each remaining time period.  This technique is also known as paging,  this should help your application to be a good citizen in the IT environment because this will limit the amount of RAM necessary to contain the time series data received.

                             

                            Let me know if you have questions,

                              • Re: Error Event Collection Exceeded The Maximum allowed 
                                ygalipeau

                                Hi Patrice,

                                 

                                I have a similar problem with a SQL linked server. this query with a linked server works:

                                 

                                select * from openquery (TR_PI,'select *   FROM  picomp2     

                                WHERE tag = ''PTM_LAB_CONSISTANCE_RAFF_P1''     

                                AND time = ''2016-08-10 07:00:00''')

                                 

                                and it returns No data (i don't have any yet)

                                 

                                the same one done directly  instead of (openquery )like this:

                                select *   FROM  TR_PI.piarchive..picomp2
                                WHERE tag = 'PTM_LAB_CONSISTANCE_RAFF_P1'

                                AND time = '2016-08-10 07:00:00'

                                 

                                gives me the error:

                                OLE DB provider "PIOLEDB" for linked server "TR_PI" returned message "[PI SDK] [261.296875] Failed to retrieve events from server.  [-11091] Event collection exceeded the maximum allowed".

                                Msg 7320, Level 16, State 2, Line 1

                                Cannot execute the query "SELECT "Tbl1002"."tag" "Col1005","Tbl1002"."time" "Col1006","Tbl1002"."_index" "Col1007","Tbl1002"."value" "Col1008","Tbl1002"."status" "Col1009","Tbl1002"."questionable" "Col1010","Tbl1002"."substituted" "Col1011","Tbl1002"."annotated" "Col1012","Tbl1002"."annotations" "Col1003" FROM "piarchive"."picomp2" "Tbl1002"" against OLE DB provider "PIOLEDB" for linked server "TR_PI".

                                 

                                Any idea why?

                                thanks

                                 

                                Yannick

                                  • Re: Error Event Collection Exceeded The Maximum allowed 
                                    gregor

                                    Hello Yannick,

                                     

                                    With OPENQUERY you execute your query as path-through which means it will be send as is. Your second query is not executed path-through. SQL Server tries to to optimize your query but it doesn't know anything about PI and ends up executing multiple queries where it should be a single one. I believe SQL Server Profiler is the name of the SQL Tool that can be used to look at the execution plan created by SQL Server.

                                     

                                    In your case SQL Server appears to remove the time restriction causing the query becomes executed without time restriction which than causes error  [-11091] Event collection exceeded the maximum allowed because the amount of events without time restriction exceeds the maximum of events that can be retrieved with a single call. This amount is set through ArcMaxCollect timeout parameter and a mechanism to protect against expensive queries.

                                     

                                    When querying for time series data or summaries, I suggest to always restrict the time period instead of providing an exact timestamp e.g.

                                     

                                    WHERE time BETWEEN "10-Aug-2016 07:00:00 AM" AND "10-Aug-2016 07:00:05 AM"
                                    

                                     

                                    Restricting by a single timestamp is possible but you need to be sure there's an event at that exact time.

                                      • Re: Error Event Collection Exceeded The Maximum allowed 
                                        ygalipeau

                                        Hi Gregor,

                                        In my case, it is related to manual entries, so that is why the time constraint is specify as a single timestamp. what is strange is that we are doing this mechanism at multiple locations and this is the first time it occurs. could it be related to an OLEDB version or a PI server version?

                                          • Re: Error Event Collection Exceeded The Maximum allowed 
                                            gregor

                                            Hello Yannick,

                                             

                                            I consider a dependency on the PI OLEDB (Classic) Provider, PI SDK or PI Data Archive version very unlikely but am experienced enough to not state it's not the case.

                                            When you observer different behavior between 2 installations, I suggest to look at the PI OLEDB Logs for both these installations first and compare the queries that become recorded there. If they are different, what I assume will be the case, this would indicate SQL Server handling things differently. If the queries look similar but the calls PI OLEDB sends to the PI Data Archive are different, it could be because of different PI OLEDB Versions.

                                            You could enable debugging by specifying Log File and Log Level within the connection string in your Linked Server connection. For the details, please refer to the PI OLEDB (Classic) Provider manual.

                                            To continue with speculations / theories, I believe it's more likely that different versions or editions of SQL Server create different execution plans and I suggest looking at those first.

                                          • Re: Error Event Collection Exceeded The Maximum allowed 
                                            pthivierge

                                            Gregor Beck, this is a pass-through query, not a path-through!

                                             

                                            yannick galipeau, Glad you found your way, this was indeed a good find. I typically use a stored procedure now to create my linked servers so they are always the same.  Maybe Patrick Bergeron forgot to update a stored procedure ? 

                                            Once the provider is configured as needed (make sure to look at the documentation), I used the script feature of SQL Server management studio to generate the code and I created variables instead of the hard-coded script-enerated strings.

                                             

                                            Here is an example.

                                            -- ----------------------------------------------------------------
                                            -- Type de procedure: CONFIGURATION
                                            -- Description      : configures a LINKED server for PI OLEDB Provider Classic
                                            -- ----------------------------------------------------------------
                                            -- Usage Examples: 
                                            
                                            -- Debug - configues log level 1
                                            -- [dbo].[sp_configure_PIOLEDBLinkedServer] 'PIClassic','PIServer01',0,'C:\Temp\OLEDBClassicLogs.txt', 1
                                            
                                            -- PROD - no logging - use trust authentication
                                            -- [dbo].[sp_configure_PIOLEDBLinkedServer] 'PIClassic','PIServer01',0
                                            
                                            -- OLEDB Provider test query
                                            /*
                                            
                                            SELECT *
                                            FROM [PI].[piarchive]..[pisnapshot]
                                            WHERE tag = 'sinusoid'
                                            
                                            */
                                            CREATE PROCEDURE [dbo].[sp_configure_PIOLEDBLinkedServer]
                                            @LinkedServerName nvarchar(255)        ,   -- name of linked server to be created
                                            @PIServerName nvarchar(255),            -- Name of PI Server
                                            @UseSSPIAuthentication int =1,      -- tells if using SSPI
                                            @LogFilePath nvarchar(4000)=null,    -- log file full path,
                                            @LogLevel nvarchar(1),                                -- log level, 0 = basic, 1=detailed
                                            @User nvarchar(255)=null,           -- user, leave null for a trusted connection
                                            @Password nvarchar(255)=null        -- user password
                                            
                                            
                                            AS
                                            
                                            declare @useselfConfig nvarchar(10)
                                            declare @provider_string NVARCHAR(MAX)
                                            
                                            SET @provider_string='Session ID=-1;'
                                            SET @useselfConfig='False'
                                            
                                            IF NULLIF(@LogFilePath, '') IS NOT NULL
                                            BEGIN
                                            SET @provider_string=@provider_string + 'Log File =' + @LogFilePath + ';Log Level = ' + @LogLevel + ';'
                                            END
                                            
                                            IF (@UseSSPIAuthentication=1)
                                            BEGIN
                                             SET @provider_string=@provider_string+'Integrated Security=SSPI;'
                                             SET @useselfConfig='True'
                                            END
                                            -- Deletes the linked server if it exists
                                            IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @LinkedServerName)
                                            BEGIN
                                            print 'dropping existing linked server ' +  @LinkedServerName + '...'
                                            EXEC master.dbo.sp_dropserver @server=@linkedServerName, @droplogins='droplogins'
                                            END
                                            
                                            -- OLEDB Classic linked server creation
                                            print 'creating PI OLEDB Provider classic linked server ' + @LinkedServerName + '...'
                                            EXEC master.dbo.sp_addlinkedserver 
                                            @server = @linkedServerName, 
                                            @srvproduct=N'PIOLEDB', 
                                            @provider=N'PIOLEDB', 
                                            @datasrc=@PIServerName, 
                                            @provstr=@provider_string
                                            
                                            EXEC master.dbo.sp_addlinkedsrvlogin 
                                            @rmtsrvname=@LinkedServerName,
                                            @useself=@useselfConfig,
                                            @locallogin=NULL,
                                            @rmtuser=@User,
                                            @rmtpassword=@Password
                                            
                                            
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'collation compatible', @optvalue=N'true'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'data access', @optvalue=N'true'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'dist', @optvalue=N'false'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'pub', @optvalue=N'false'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'rpc', @optvalue=N'true'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'rpc out', @optvalue=N'true'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'sub', @optvalue=N'false'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'connect timeout', @optvalue=N'90'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'query timeout', @optvalue=N'600'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'collation name', @optvalue=null
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'lazy schema validation', @optvalue=N'false'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'use remote collation', @optvalue=N'true'
                                            EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'remote proc transaction promotion', @optvalue=N'true'
                                            
                                            print('Configures futher SQL Server options over the PIOLEDB Provider: AllowInProcess,DynamicParameters,NestedQueries,SqlServerLIKE')
                                            EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', 'AllowInProcess', 1
                                            EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', 'DynamicParameters', 1
                                            EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', 'NestedQueries', 1
                                            EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', N'SqlServerLIKE', 1
                                            
                                            print('task completed')