MSDE server performance question
-
I am experimenting with MSDE and I've found that one long running query on one table causes other (unrelated, different tables, different machines querying) queries to hang waiting for the first query to finish. Is this a "feature" of msde or is there some settings that control how time slices are divvied up for more balance? (Note: there is *nothing* in common between the two queries except the MSDE itself, they are different databases queried from different machines)
-
I am experimenting with MSDE and I've found that one long running query on one table causes other (unrelated, different tables, different machines querying) queries to hang waiting for the first query to finish. Is this a "feature" of msde or is there some settings that control how time slices are divvied up for more balance? (Note: there is *nothing* in common between the two queries except the MSDE itself, they are different databases queried from different machines)
It is not clear from you post if the queries execute against different databases. If they don't you may like to consider the following (if, indeed you haven't already). Running two consecutive queries will cause some slowdown, due to the server having to perform additional work. I would make sure that your long running query is not obtaining any locks that may prevent the other query from running. If your queries perform any joins you may find that the joins cause locks. I also remember reading somewhere that SQL Server/MSDE will escalate locks. Perhaps you are getting page locks that are causing the second query to block, as it has data on the same page. Have you tried moving the database(s) to a SQL Server (not MSDE) to see if you still get the same problem? With regards to the performance of MSDE, it will slow down if you have more than '5 concurrent batch workloads'. have a look at the performance section in this page for further information. http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp I hope this helps. Regards Mark Smithson
-
It is not clear from you post if the queries execute against different databases. If they don't you may like to consider the following (if, indeed you haven't already). Running two consecutive queries will cause some slowdown, due to the server having to perform additional work. I would make sure that your long running query is not obtaining any locks that may prevent the other query from running. If your queries perform any joins you may find that the joins cause locks. I also remember reading somewhere that SQL Server/MSDE will escalate locks. Perhaps you are getting page locks that are causing the second query to block, as it has data on the same page. Have you tried moving the database(s) to a SQL Server (not MSDE) to see if you still get the same problem? With regards to the performance of MSDE, it will slow down if you have more than '5 concurrent batch workloads'. have a look at the performance section in this page for further information. http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp I hope this helps. Regards Mark Smithson
Yup, different databases entirely. That's what I found wierd. It's a simple thing, two queries on two different databases from two different computers. I'll have a look at that info, thanks. "Things are more like they are now than they ever were before." -- Dwight Eisenhower
-
Yup, different databases entirely. That's what I found wierd. It's a simple thing, two queries on two different databases from two different computers. I'll have a look at that info, thanks. "Things are more like they are now than they ever were before." -- Dwight Eisenhower
What do your queries look like? If you are using "Select * Into #TempTable" then I seem to remember that the system catelog tables in the Master database get locked for the duration of the query (unfortunately all of my tuning books are at work). Also, what isolation level are you running the queries at (Commited Read, Repeatable Read, etc.)? Regards Andy
-
What do your queries look like? If you are using "Select * Into #TempTable" then I seem to remember that the system catelog tables in the Master database get locked for the duration of the query (unfortunately all of my tuning books are at work). Also, what isolation level are you running the queries at (Commited Read, Repeatable Read, etc.)? Regards Andy
Hello, thanks for the tips, but discovered it was an index problem (as in missing a critical index). MSDE / SQL server sure is dependant on those indexes. The equivalent problem with the exact same sized table and same query in Access would still not have degraded that far. Of course SQL server is much faster than Access once the index is there, but it's interesting how dependant it is on them for performance and how badly performance degrades if a critical one is missing. "Things are more like they are now than they ever were before." -- Dwight Eisenhower