Обработчик запросов обнаружил непредвиденную ошибку

This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.

asked Nov 19, 2012 at 18:29

0

Look for queries with very long IN lists, a large number of UNIONs, or a large number of nested subqueries. These are the most common causes of this particular error message in my experience.

Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.

Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:

Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199

answered May 28, 2013 at 4:45

Paul White♦Paul White

76.5k27 gold badges386 silver badges604 bronze badges

I have solved a similar issue by using a server-side startup ‘tuning’ trace that runs in the background capturing statements running for over 1 second (you could perhaps set it to 10secs if you have an extremely busy server).

When the Query Processor fails to produce a plan it takes over 10 seconds to do so (in my experience)

The Errorlog entry does show the SPID involved along with the exact time, so going to the ‘tuning’ trace it is easy to identify the offending statement.

Surpisingly it may have a ‘success’ errorcode.

answered May 27, 2013 at 17:40

John AlanJohn Alan

1,0617 silver badges13 bronze badges

I received this error because of another reason which I didn’t see online, so I’m posting the problem and solution here.

This can happen when trying to modify an xml column by inserting a very large text.
For example…

update MyTable
set XmlColumn.modify('
insert
    Very large text here...
after (/RootNode/Node)[1]')
where Id = 1

To fix it, you can use a sql variable to hold the large text

declare @MyXml xml
set @MyXml = 'Very large text here...'
update MyTable
set XmlColumn.modify('
insert
    sql:variable("@MyXml")
after (/RootNode/Node)[1]')
where Id = 1

The method seems faster anyway so should probably be used whenever possible

answered Jun 24, 2015 at 18:55

Possible it is not one query causing the issue. If you are using a ton of ad-hoc queries it would be prudent to enable ‘optimize for ad-hoc workloads’. This way SQL Server will only create plans the second time a query is executed.

You can check using below SQL (Reference here):

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go

Kin Shah

61.6k5 gold badges115 silver badges235 bronze badges

answered Apr 25, 2013 at 21:11

0

This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.

asked Nov 19, 2012 at 18:29

0

Look for queries with very long IN lists, a large number of UNIONs, or a large number of nested subqueries. These are the most common causes of this particular error message in my experience.

Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.

Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:

Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199

answered May 28, 2013 at 4:45

Paul White♦Paul White

76.5k27 gold badges386 silver badges604 bronze badges

I have solved a similar issue by using a server-side startup ‘tuning’ trace that runs in the background capturing statements running for over 1 second (you could perhaps set it to 10secs if you have an extremely busy server).

When the Query Processor fails to produce a plan it takes over 10 seconds to do so (in my experience)

The Errorlog entry does show the SPID involved along with the exact time, so going to the ‘tuning’ trace it is easy to identify the offending statement.

Surpisingly it may have a ‘success’ errorcode.

answered May 27, 2013 at 17:40

John AlanJohn Alan

1,0617 silver badges13 bronze badges

I received this error because of another reason which I didn’t see online, so I’m posting the problem and solution here.

This can happen when trying to modify an xml column by inserting a very large text.
For example…

update MyTable
set XmlColumn.modify('
insert
    Very large text here...
after (/RootNode/Node)[1]')
where Id = 1

To fix it, you can use a sql variable to hold the large text

declare @MyXml xml
set @MyXml = 'Very large text here...'
update MyTable
set XmlColumn.modify('
insert
    sql:variable("@MyXml")
after (/RootNode/Node)[1]')
where Id = 1

The method seems faster anyway so should probably be used whenever possible

answered Jun 24, 2015 at 18:55

Possible it is not one query causing the issue. If you are using a ton of ad-hoc queries it would be prudent to enable ‘optimize for ad-hoc workloads’. This way SQL Server will only create plans the second time a query is executed.

You can check using below SQL (Reference here):

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go

Kin Shah

61.6k5 gold badges115 silver badges235 bronze badges

answered Apr 25, 2013 at 21:11

0

Ошибка СУБД: Обработчик запросов исчерпал внутренние ресурсы…

Я

Ёхан Палыч

18.01.12 — 06:16

Периодически, не всегда, выдает на формирование ОСВ за год вот такую ошибку:

Ошибка СУБД:

Microsoft OLE DB Provider for QSL Server: Обработчик запросов исчерпал внутренние ресурсы, и ему не удалось предоставить план запроса.

Это редкое событие, которое может происходить только при очень сложных запросах или запросах, которые обращаются к очень большому числу таблиц или секций.

Упростите запрос…

Сервер на базе i5, ОЗУ=8Гб. 64-битный 1С Сервер. SQL 2008.

Как можно лечить? Поможет ли увеличение оперативки?

Rie

1 — 18.01.12 — 06:21

(0) Упрости запрос.

Ёхан Палыч

2 — 18.01.12 — 06:27

(1) бухам нужен ОСВ за год по всем счетам, упрощать не собираются, к тому же он иногда отрабатывает. след. можно как-то лечить

Rie

3 — 18.01.12 — 06:29

(2) Можно разбить запрос на несколько, использовать временные таблицы и т.д.

Ёхан Палыч

4 — 18.01.12 — 06:30

(3) все не то, не полезу я им новый ОСВ писать, мне кажется дело в настройках СКЛ, только что там настроть можно — ума не приложу

Rie

5 — 18.01.12 — 06:35

(4) Попробуй посмотреть, что говорит sp_configure.

Ёхан Палыч

6 — 18.01.12 — 06:36

(5) а что это? я не силен  в скл

Rie

7 — 18.01.12 — 06:39

Ёхан Палыч

8 — 18.01.12 — 06:40

(7) ок, посмтрю

Rie

9 — 18.01.12 — 06:41

+(7) Только, IMHO, всё же лучше попробовать упростить запрос. Не переписывая всё, только попробовать слегка оптимизировать. Например, IN (SELECT если есть — заменить на что-нибудь полегче. И вообще подзапросы повыносить во временные таблицы.

Ёхан Палыч

10 — 18.01.12 — 06:47

(9) но иногда же отрабатывает, это наводит на мысль…

Rie

11 — 18.01.12 — 06:51

(10) Оно, конечно, так…

Но если жрёт ресурсы безбожно — то где гарантия, что подкинешь ты ему ресурсов, а оно их снова не сожрёт? Тем более что дальше база будет расти и расти…

Я не настаиваю, посмотри настройки sp_configure — там могут стоять ограничения. Но, IMHO, соптимизировать запрос — обычно полезнее бывает.

упс

12 — 18.01.12 — 07:35

(0) а покажите результат «SELECT @@version»

Ёхан Палыч

13 — 18.01.12 — 08:04

(12) как его показать, я скл не знаю, поставил и работает

пипец

14 — 18.01.12 — 08:10

релиз?

упс

15 — 18.01.12 — 08:13

(13) Подключиться к SQL Server с помощью SQL Server Management Studio, нажать кнопку «New query», ввести SELECT @@version и нажать F5 (либо кнопку Execute)

Ёхан Палыч

16 — 18.01.12 — 08:17

Microsoft SQL Server 2008 R2 (RTM) — 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

упс

17 — 18.01.12 — 08:43

(16) Попробуйте, как вариант, поставить сервиспак — http://support.microsoft.com/kb/2528583

Ёхан Палыч

18 — 18.01.12 — 08:52

(17) ок, попробую

ILM

19 — 18.01.12 — 09:12

(2) Главному или всем? Если всем посылай их подальше. Пусть анализ счета и обороты смотрят по нему. Или список выдают…

ILM

20 — 18.01.12 — 09:14

Из тех счетов что смотреть…

Представляю себе  ОСВ, ну очень крупного холдинга ))))

Ёхан Палыч

21 — 18.01.12 — 09:20

(20) главному; нет не холдинг, так себе конторка — это и бесит, какой то ОСВ за год

Evgenich71

22 — 03.02.12 — 13:02

Может быть у пользователя установлены права с ограничением прав на уровне записи?

  

LanLion

23 — 24.02.12 — 16:14

Полный бред граждане это ошибка платформы 1с, скуля или релиза опять 1сники что-то улучшили, вылезла такая же гадость на днях да последних релизов такого небыло, работало все нормально и разворачивала и разворачивает намного более сложные отчеты. 1с позиционирует себя как ерп и т.д сажает себя на оракл и вытворяет такую ..ерню. Все тут советуют упростить запрос, а тем у кого конфа на поддержке да и вообще какого фига мы должны за них делать их работу это типовой отчет.

• Remove From My Forums

• Question

• SQL Server Version: Microsoft SQL Server 2008 (RTM) — 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

  Database Compatibility: 100

  We’re receiving this error on one of our sp when created through sqlcmd, but the error goes away when we re-create the sp on SSMS.
  Codes on this application is deployed using sqlcmd, is there any settings that we are missing, that causes the error when created on sqlcmd?

  sp is a a simple sp that does a select/insert/update that utilizes a table variable to hold a query result from a simple inner join query, then uses the values on the table to update different tables.

• Remove From My Forums

• Question

• SQL Server Version: Microsoft SQL Server 2008 (RTM) — 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

  Database Compatibility: 100

  We’re receiving this error on one of our sp when created through sqlcmd, but the error goes away when we re-create the sp on SSMS.
  Codes on this application is deployed using sqlcmd, is there any settings that we are missing, that causes the error when created on sqlcmd?

  sp is a a simple sp that does a select/insert/update that utilizes a table variable to hold a query result from a simple inner join query, then uses the values on the table to update different tables.

Я бегу следом:

DECLARE @g geography;
declare @point nvarchar(50)  =''
declare @i int =0,
        @lat decimal(8,6) =0.0,
        @long decimal(8,6) =0.0,
        @start datetime = getdate()
set @lat =(select (0.9 -Rand()*1.8)*100)
set @long =(select (0.9 -Rand()*1.8)*100)
set @point = (select 'POINT('+CONVERT(varchar(10), @lat)+ '  ' 
             +CONVERT(varchar(10), @long)+')')
SET @g = geography::STGeomFromText(@point, 4326);
SELECT TOP 1000
    @lat,
    @long,
        @g.STDistance(st.[coord]) AS [DistanceFromPoint (in meters)] 
    ,   st.[coord]
    ,   st.id
FROM    Temp st with(index([SpatialIndex_1]))

этот запрос выполняется плохо, потому что он не использует пространственный индекс, поэтому я добавил with(index([SpatialIndex_1])) чтобы заставить его.

индекс географии выглядит следующим образом:

CREATE SPATIAL INDEX [SpatialIndex_1] ON [dbo].Temp
(
    [coord]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = LOW,LEVEL_2 = MEDIUM,LEVEL_3 = LOW,LEVEL_4 = LOW), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) 
ON [PRIMARY]

теперь это дает мне сообщение об ошибке

Msg 8622, Уровень 16, состояние 1, строка 15 процессор запросов не может
предоставить план запроса из-за подсказок, определенных в запросе.
Отправьте запрос без указания каких-либо подсказок и без использования НАБОР
FORCEPLAN.

Я могу читать и понимать, что он говорит мне удалить подсказку, вопрос в том, почему он преуспевает в компиляции, но терпит неудачу во время выполнения? Что-то не так с моим индексом?

что мне нужно изменить для SQL, чтобы начать использовать пространственный индекс?

для генерации некоторых данных вы можете использовать следующий скрипт.

CREATE TABLE dbo.Temp
    (
    Id int NOT NULL IDENTITY (1, 1),
    Coord geography NOT NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Temp ADD CONSTRAINT
    PK_Temp PRIMARY KEY CLUSTERED 
    (
    Id
    ) 
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO


declare @i int =0
declare @lat decimal(8,6) =0.0
declare @long decimal(8,6) =0.0
while (@i < 47000)
begin
   set @lat =(select (0.9 -Rand()*1.8)*100)
   set @long =(select (0.9 -Rand()*1.8)*100)
   insert into Temp
   select geography::Point(@lat, @long,4326)
   set @i =@i+1
end
go

CREATE SPATIAL INDEX [SpatialIndex_1] ON [dbo].Temp
(
    [coord]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = LOW,LEVEL_2 = MEDIUM,LEVEL_3 = LOW,LEVEL_4 = LOW), 
   CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
   SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
   ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) 
ON [PRIMARY]
GO

1 ответов

---

С здесь:

для использования пространственного индекса в запросе ближайшего соседа должны быть выполнены следующие требования:

1. пространственный индекс должен присутствовать на одном из пространственных столбцов и
   метод STDistance() должен использовать этот столбец в WHERE и ORDER
   По пунктам.
2. предложение TOP не может содержать оператор PERCENT.
3. предложение WHERE должно содержать метод STDistance ().
4. если их несколько предикаты в предложении WHERE затем
   предикат, содержащий метод STDistance (), должен быть соединен с помощью AND
   вместе с другими предикатами. Метод STDistance () не может
   быть в необязательной части предложения WHERE.
5. первое выражение в предложении ORDER BY должно использовать
   STDistance() метод.
6. порядок сортировки для первого выражения STDistance() в порядке по
   предложение должно быть ASC.
7. все строки, для которых STDistance возвращает NULL, должны быть отфильтрованы из.

Так, это должно работать:

DECLARE @g geography;
declare @point nvarchar(50)  =''
declare @i int =0,
        @lat decimal(8,6) =0.0,
        @long decimal(8,6) =0.0,
        @start datetime = getdate()
set @lat =(select (0.9 -Rand()*1.8)*100)
set @long =(select (0.9 -Rand()*1.8)*100)
set @point = (select 'POINT('+CONVERT(varchar(10), @lat)+ '  ' 
             +CONVERT(varchar(10), @long)+')')
SET @g = geography::STGeomFromText(@point, 4326);

SELECT TOP 1000
    @lat,
    @long,
        @g.STDistance(st.[coord]) AS [DistanceFromPoint (in meters)] 
    ,   st.[coord]
    ,   st.id
FROM    Temp st with(index([SpatialIndex_1]))
WHERE @g.STDistance(st.[coord])  IS NOT NULL
ORDER BY @g.STDistance(st.[coord]) asc

вы можете проверить, что он использует пространственный индекс даже WITH INDEX подсказка убирается.

---

В коде генерируется огромный запрос с множеством методов Where, на что вылетает исключение с текстом:

Обработчику запросов не удалось предоставить план запроса, так как для
этого требуется рабочая таблица, а минимальный размер ее строки
превышает допустимый максимум в 8060 байт. Типичная причина, по
которой требуется рабочая таблица, — наличие в запросе предложений
GROUP BY или ORDER BY. Если в запросе присутствует предложение GROUP
BY или ORDER BY, рассмотрите возможность уменьшения количества или
размера полей в этих предложениях. Рассмотрите возможность
использования префикса (LEFT()) или хэширования (CHECKSUM()) полей для
группирования или префикса для упорядочивания. Однако следует принять
во внимание, что это приведет к изменению поведения запроса.

Подскажите, о чем тут речь и есть ли возможность обойти такое поведение?

Ответы (1 шт):

Если Where используется в linq, попробуй фильтровать информация при подтягивании из БД с помощью регулярных выражений(пример с MongoDriver — не знаю есть ли в Entity Framework):

        bsonArray.Add(new BsonDocument("productCode", new BsonDocument("$eq", ProductCode)));
        var filter = new BsonDocument("$and", bsonArray);
        return _orders.Find(filter).ToList()

Вытягивает информацию по определенному коду товара.

→ Ссылка

   progaoff

18.09.18 — 10:05

SQL Server: Fix Msg 8630, Level 16, State 1, Line 1 – Internal Query Processor Error: The query processor encountered an unexpected error during execution (HRESULT = 0x80040e19).

This is going to be a quick reference for people encountering this error and to do the fix more than a blog post but I do show a small trick on how to do an UPDATE in chunks 😉

I ran into this error with

• (Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) – 11.0.7462.6 (X64) Jan 5 2018 22:11:56 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

When you run a query such as below:

It produced an obscure error

The problem – Memory pressure?

Initial Google search indicated that it was due to memory pressure and a fix is needed via a Service Pack (for the older versions of SQL Server).

Thinking it was trying to do too much at once, I broke the UPDATE to do it in chunks of 10000 rows thinking it would help with the memory pressure part :-). Indeed, it might help you if that was the case for you, but it did not help me! Basically, the idea is to keep updating rows that qualify in chunks of 10000 inside a loop until no more rows qualify for the UPDATE and we exit the loop. A supporting index that makes this query efficient would be helpful.

..or corruption?

However, this link listed many possible problems that could cause this error and I decided to try DBCC CHECKTABLE on the table in question.

Doing a quick check, the table indeed had a corrupted index

The Solution:

To fix the index corruption, I tried doing an online index rebuild and it wasn’t supported in the Standard edition I was running. So, I just dropped and recreated the index.

Then, I ran the UPDATE and it worked like a charm!

Источник

Internal query processor error

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have an ETL job which runs daily on SQL server. It failed last week while trying to read data from a database which is present in the same server where ETL job is also running with the following error :

«Internal Query Processor Error: The query processor encountered an unexpected error during execution.»

The job runs on SQL Server 2005 with Standard Edition (Service Pack2) on a 32 bit machine.

RAM Size : 15.9 GB

The ETL job was running fine all these days. This error had occurred recently and after that for 1 week the job was running fine. But this error occurred again for the second time after 1 week.

Can someone please help me out in this?

Answers

It may be also caused by job agent which control by msdb database. You could try to turn msdb to single user model and run the below query:

DBCC CHECKDB (‘msdb’, Repair_ALL)

Best Regards,
Peja

Please remember to click «Mark as Answer» on the post that helps you, and to click «Unmark as Answer» if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Some time ago, I was also faced the same issue, and I ran the update stats and later executed the query and its successful.

Please try that, still if you are facing the issue please try to open a case with MS.

The reason why i executed the upstats is, query optimiser will builds the plan based on stats, if the stats are updated we can ran the situtation like «Internal Query Processor Error». ( in my point of view)

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue 🙂

I am not sure if there is corruption in any of the database and that can cause such issues. Once you have verified that databases are clean from corruption, I would suggest you to upgrade to latest build of SQL Server as there have been few fixes about the error which you are seeing.

What is the information in ERRORLOG?

8630 is a generic error with many possible causes. If none of above help then my request would be to open support ticket with Microsoft SQL Support team as this would need additional troubleshooting and gather more details. Again, please apply latest patch of SQL Server 2005 before contacting us.

Balmukund Lakhani | Please mark solved if I’ve answered your question, vote for it as helpful to help other user’s find a solution quicker
———————————————————————————
This posting is provided «AS IS» with no warranties, and confers no rights.
———————————————————————————
My Blog | Team Blog | @Twitter

I would recommend to start by installing the SP4 for SQL Server 2005.

Otherwise, tell me your query.

Also can you check is there any recent modification done at the SP level, if so you can tune and check it out.

also I would suggest you to check Optimization(reindex and update statistics)optmization job well , also is there any other performance issue faced during that time i,e is there any adhoc or additional load was ran against the DB level .

if vendors OK with the latest patch then upgrade to latest ->SP+Hotfix for SQL server 2005.

Rama Udaya.K ramaudaya.blogspot.com —————————————- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

I use the below query

select
A.productno,A.customerno,A.accountno,A.Orgno
from (
(
SELECT S.productno,S.customerno,S.accountno,S.Orgno
From StagingTable S
INNER JOIN (SELECT productno from tabeA
UNION
SELECT productno from tableB
UNION
SELECT productno from tableC
UNION
SELECT productno from tableD
UNION
SELECT productno from tableE
UNION
SELECT productno from tableF
UNION
SELECT productno from tableG
UNION
SELECT productno from tableH
UNION
SELECT productno from tableI
UNION
SELECT productno from tableJ
) M
ON S.productno=M.productno
)
UNION
(
SELECT S.productno,S.customerno,S.accountno,S.Orgno
From StagingTable S INNER JOIN FactTable F
on S.productno=F.productno
)

(SELECT CTK.productno,M.customerno,C.accountno,MK.Orgno
From FactTable CTK
INNER JOIN
(SELECT cutsomerno,customerkey FROM Dim_Cust ) M
ON CTK.customerkey=M.customerkey
INNER JOIN
(SELECT accountno,accountkey from Dim_Account) C
ON CTK.Accountkey=C.Accountkey
INNER JOIN
(SELECT Orgno,Orgkey from Dim_Org)MK
ON CTK.Orgkey=MK.Orgkey
)A

Hi Rama Udaya,
There wasn’t any additional load which was happening that time.

I also wanted to know why this error occurs ?

Источник

Internal query processor error

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have an ETL job which runs daily on SQL server. It failed last week while trying to read data from a database which is present in the same server where ETL job is also running with the following error :

«Internal Query Processor Error: The query processor encountered an unexpected error during execution.»

The job runs on SQL Server 2005 with Standard Edition (Service Pack2) on a 32 bit machine.

RAM Size : 15.9 GB

The ETL job was running fine all these days. This error had occurred recently and after that for 1 week the job was running fine. But this error occurred again for the second time after 1 week.

Can someone please help me out in this?

Answers

It may be also caused by job agent which control by msdb database. You could try to turn msdb to single user model and run the below query:

DBCC CHECKDB (‘msdb’, Repair_ALL)

Best Regards,
Peja

Please remember to click «Mark as Answer» on the post that helps you, and to click «Unmark as Answer» if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Some time ago, I was also faced the same issue, and I ran the update stats and later executed the query and its successful.

Please try that, still if you are facing the issue please try to open a case with MS.

The reason why i executed the upstats is, query optimiser will builds the plan based on stats, if the stats are updated we can ran the situtation like «Internal Query Processor Error». ( in my point of view)

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue 🙂

I am not sure if there is corruption in any of the database and that can cause such issues. Once you have verified that databases are clean from corruption, I would suggest you to upgrade to latest build of SQL Server as there have been few fixes about the error which you are seeing.

What is the information in ERRORLOG?

8630 is a generic error with many possible causes. If none of above help then my request would be to open support ticket with Microsoft SQL Support team as this would need additional troubleshooting and gather more details. Again, please apply latest patch of SQL Server 2005 before contacting us.

Balmukund Lakhani | Please mark solved if I’ve answered your question, vote for it as helpful to help other user’s find a solution quicker
———————————————————————————
This posting is provided «AS IS» with no warranties, and confers no rights.
———————————————————————————
My Blog | Team Blog | @Twitter

I would recommend to start by installing the SP4 for SQL Server 2005.

Otherwise, tell me your query.

Also can you check is there any recent modification done at the SP level, if so you can tune and check it out.

also I would suggest you to check Optimization(reindex and update statistics)optmization job well , also is there any other performance issue faced during that time i,e is there any adhoc or additional load was ran against the DB level .

if vendors OK with the latest patch then upgrade to latest ->SP+Hotfix for SQL server 2005.

Rama Udaya.K ramaudaya.blogspot.com —————————————- Please remember to mark the replies as answers if they help and unmark them if they provide no help.

I use the below query

select
A.productno,A.customerno,A.accountno,A.Orgno
from (
(
SELECT S.productno,S.customerno,S.accountno,S.Orgno
From StagingTable S
INNER JOIN (SELECT productno from tabeA
UNION
SELECT productno from tableB
UNION
SELECT productno from tableC
UNION
SELECT productno from tableD
UNION
SELECT productno from tableE
UNION
SELECT productno from tableF
UNION
SELECT productno from tableG
UNION
SELECT productno from tableH
UNION
SELECT productno from tableI
UNION
SELECT productno from tableJ
) M
ON S.productno=M.productno
)
UNION
(
SELECT S.productno,S.customerno,S.accountno,S.Orgno
From StagingTable S INNER JOIN FactTable F
on S.productno=F.productno
)

(SELECT CTK.productno,M.customerno,C.accountno,MK.Orgno
From FactTable CTK
INNER JOIN
(SELECT cutsomerno,customerkey FROM Dim_Cust ) M
ON CTK.customerkey=M.customerkey
INNER JOIN
(SELECT accountno,accountkey from Dim_Account) C
ON CTK.Accountkey=C.Accountkey
INNER JOIN
(SELECT Orgno,Orgkey from Dim_Org)MK
ON CTK.Orgkey=MK.Orgkey
)A

Hi Rama Udaya,
There wasn’t any additional load which was happening that time.

I also wanted to know why this error occurs ?

Источник