SQL Server Performance Forum – Threads Archive
Query runs slower from another database
Here’s a trivial query: Select ROWID From HomeDB.dbo.CatalogItems where rowid = 11111(RowID is the primary key) When I am in database HomeDB, the response is immediate as you would expect.
When I execute from another database (but still same server), the response takes 2 secs. The issue seems to be due to some latency referencing the other database. We’re using SQL Server Express SP2 Comments anyone?
Did you run Update Statistics?
Did you see if the execution plan are different? BTW:Welcome!! Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
Thanks for the welcome, Luis! Execution plans are the same. Haven’t run stats yet, will try. I noticed that if I batch several similar queries it takes about the same time, that is, the delay seems to be on the first query executed against the other database whereas the remaining ones in the same batch run almost instantaneously (as you would expect, given the nature of the query)
I guess this could be a paging issue.
Not sure but worth checking about relevant indexes:http://sqlserver-qa.net/blogs/perft…that-a-query-could-benefit-from-an-index.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I am now sure that the problem is not data- or table-oriented. My latest test is this:
USE ProblemDatabase
SELECT ‘Hello’ The above takes 2 seconds, whereas
USE OtherDatabase
SELECT ‘Hello’
is more or less immediate. Even in Query Analyzer I can see a delay of 1-2 seconds when using the database name dropdown to switch database context. My user and schema context is ‘dbo’ We are now going to create a new database, populate it with the same contents as the problem one, and see what effect that has.
Problem now solved, but without knowing why: After recreating and repopulating the suspect database, the problem has gone away.
Unfortunately, we’ll never know what caused the problem! Thanks everyone for your comments.
Good news to you, bad news to us I would like to know what fix the problem.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
You say suspect database, that means is that database not identical to the previous one? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
It’s different only in the sense that it contains different tables. But it’s supposedly the same otherwise: same server, same file location, same database options. Nevertheless there was something about it that we couldn’t spot.
If it has something to do on the difference between the tables then you wouldn’t be able to find what went wrong, if not having a review of error logs including event viewer may give any clue outside of the SQL process. That is why to troubleshoot the performance problem PERFMON & SQL Trace are useful. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Don’t Make a http://www.bsu.edu/web/mbaassoc/_disc1/00000e1f.htm Ivey is on http://www.bsu.edu/web/mbaassoc/_disc1/00000e3a.htm For example the http://www.earto.org/DESCA/DESCAFeedback/0000022a.htm Does it make http://www.bsu.edu/web/mbaassoc/_disc1/00000e80.htm If two or Hello
Delete that reply! —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
]]>