Sql server query plan

Question

0.00/5 (No votes)

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.

XML

<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 7-Apr-19 23:48pm

Herman<T>.Instance

Updated 8-Apr-19 1:47am

Add a Solution

Comments

[no name] 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[^]

Herman<T>.Instance 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

Add a Solution

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

MadMyche · Accepted Answer · 2019-04-08T01:48:00

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[^]

Posted 8-Apr-19 1:48am

MadMyche

Comments

Maciej Los 8-Apr-19 14:51pm

5ed!

Herman<T>.Instance 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

Herman<T>.Instance 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.

Herman<T>.Instance 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.

Herman<T>.Instance 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.

Sql server query plan

1 solution

Solution 2

Add your solution here

Preview 0

Sql server query plan

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us