Query process time

I have a weird issue that I just can't figure out. I have a query that runs on tables that are all located in db x. If I run the query under a different db (like use database y) then it runs in like 11 seconds. If I run the query under the db that all of the tables reside under (use database x) then it takes almost 4 minutes.

Why would it take so much longer to run under the db where all of the data/tables exists? Both dbs are on the same SQL 2005 server.

I just don't get it?

Any ideas?

S

We can't possibly give you a meaningful response without having more information. What is the query, what is the nature of the data, what is different between the two databases, etc.

Perhaps, using your description about, database X has data in it, and database Y does not?

|||If you think that execution plan will be same then why SQl statement take different time on different server. But have you checked the CPU Cost and I/O cost.

Query Notification not fire all subscriptions...

Hello all,

We have encountered a weird problem; we have an application that registers to several queries for notification.

For several weeks everything worked fine, any change to any query result in the database triggered the notification event, but since yesterday the mystery began… since yesterday not all changes triggers events , changing one table raise the notification event, changing the other doesn’t , all seems fine in the database , the application reports that all queries were registered successfully , in the subscription view in the database we can see that all the queries that the application subscribed to are present…

What can be the problem? Is it something in the database? Can it be a problem in our application, please keep in mind that everything worked until yesterday…

Thanks,

Eden

Check sys.transmission_queue in the database where the events are registered. If it contains undelivered notifications, check the transmission_status column, it should tell you the reason why the messages cannot be delivered.|||

Thanks for your answer Remus

Unfortunately our Dba’s deleted the transmission queue, and now it’s all working again,

The Dba’s said that the transmission queue is getting very large (even millions of records) with the Conversing status.

Can the queue overflow be the problem?

Thanks

|||

No, that's not the problem. Queues don't 'overflow'. The transmission queue cannot be deleted, so the dba must had eaither terminated the dialogs with cleanup or started a new broker in the database.

If millions of conversation in 'Conversing' state accumulate, it means you notification are not being delivered. Again, the transmission_status of column will indicate the cause.