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">
                             <OutputList>
                               <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" />
                             </OutputList>


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?
Posted
Updated 8-Apr-19 1:47am
Comments
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.

https://support.microsoft.com/en-us/help/975090/fix-deadlocks-may-occur-when-multiple-concurrent-query-notification-su[^]

SQL 2012 SP1 producing deadlocks on sys.query_notification[^]
   
Comments
Maciej Los 8-Apr-19 14:51pm
   
5ed!
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