Click here to Skip to main content
14,331,980 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi folks,

I have a very slow stored procedure and the queryplan show me something strange. It is using a Indexed cluster on a table in the schema sys -> sys.query_notification_1626853258.

<Object Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Index="[cidx]" IndexKind="Clustered" Storage="RowStore" />
                           <RelOp AvgRowSize="2463" EstimateCPU="80.1192" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="41" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="466.54">
                               <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Column="id" />
                               <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Column="dialog" />
                               <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Column="sid" />
                               <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Column="ssb_service" />
                               <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Column="ssb_instance" />
                               <ColumnReference Database="[TDMS-TEST]" Schema="[sys]" Table="[query_notification_1626853258]" Column="message" />

But there is no such table, not in sys, not in any other database. What is biting and how to solve this?

What I have tried:

All indexes are rebuild, all stored procedure are recompiled. and now?
Updated 8-Apr-19 1:47am
0x01AA 8-Apr-19 7:11am
Look like you have some subscribers for query notifications?
See e.g. here: Quote:Query Notification using SqlDependency and SqlCacheDependency[^]
digimanus 8-Apr-19 7:14am
Yes I do use notification subscription. They are not used in any stored procedure, but the query plan of test_insert says it is being used where the nr of estimated rows increases from 1 to 17 million and more. (That seems 4 seconds work)

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 2

sys.query_notification_1626853258 is a generic name for an instance of Query_Notification.

It looks like there have been problems with this in the past, I would recommend reviewing the content of these posts and patches to see what applies to you so that you can figure out what you can do to eliminate the deadlocks.[^]

SQL 2012 SP1 producing deadlocks on sys.query_notification[^]
Maciej Los 8-Apr-19 14:51pm
digimanus 9-Apr-19 3:47am
I'm using SQL Server 2014......
MadMyche 9-Apr-19 7:03am
Still good stuff to read up on and things to look tat, maybe disable query notification and see what happens. Maybe a re-analyzing the indexes would be helpful. Don't know without digging into it
digimanus 9-Apr-19 7:07am
The strange situation is that the sp was no problem until last week!
If i backup that database and restore it to my own sql server the problem is gone too. DBCC Checkdb doesn't show up any problems.
digimanus 9-Apr-19 7:08am
disabling en reenabling the notificationservice didnit do anything. Restart the SQl Server perhaps
MadMyche 9-Apr-19 7:39am
Perhaps; at this time it looks to be some sort of configuration or problem with the SQL Server.
digimanus 9-Apr-19 8:15am
Maybe some background process is blocking this. I do not see anything blocking with sp_who (2). We do get some deadlocks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100