SQL server "View" times out

[DataMiner]

HI,
I am just starting to transfer stuff from Access 2002 to SQL server 2005. Initially I am just changing my backend (tables) to SQL server and leaving everything else in an Access .mdb frontend.

The following query simply uses two tables that now reside in SQL server. When I run the query from the Access front-end, it runs in the blink of an eye. However, another query that depends on this one ends up timing out.

So, It thought I'd try changing this query to a View in SQL server. But when I do, this one times out when I try to run it from SQL Server Management studio.

I also tried running this as a pass-through query, and it also times out.

SELECT ProductInfoMasterChanges.Noun, Min(ProductInfoMasterChanges.Updated) AS MinOfUpdated, ProductInfoMasterChanges.bom1rev
FROM ProductInfoMasterChanges INNER JOIN LatestRevMaster ON (ProductInfoMasterChanges.bom1rev = LatestRevMaster.BOM1Rev) AND (ProductInfoMasterChanges.Noun = LatestRevMaster.Noun)
GROUP BY ProductInfoMasterChanges.Noun, ProductInfoMasterChanges.bom1rev;

So, in summary: I try using the exact same SQL 3 different ways:
1. As a stored querydef in Access --- runs fine.
2. As a View in SQL server -- times out
3. As a pass-through query in Access -- times out.

What's going on? I thought that changing stuff to SQL server "View" would, if anything, make stuff run faster...

 

[SQL_Hell]

hey,

How many rows are we talking about?

If the view times out via query in management studio, then increase the query time out on the server, so we can see how long it actually takes. Can you increase the time out? you are the DBA?

Do these tables have primary keys?

The problem is probably that the tables and query aren't tuned for sql server, more on this later

 

[DataMiner]

Roughly 700 rows.

If I can increase the timeout, I don't know how. Can you clue me in?

Yes, all of my tables have primary keys.

And...sometimes they timeout, sometimes they don't.

 

[tehNellie]

I'm starting to suspect a [dead]lock. Are you linking to the View in Access?

Next time you find yourself waiting for view/query to open check the Management/CurrentActivity/Lock tab (sql server 2000, might be different in 2005) and see if you've a lock in place that is holding up other people trying to access the data.

If locking is the problem you might consider using the WITH NOLOCK hint within your views to enable the view to open without any danger of holding a lock on the table(s) in question:

CREATE VIEW dbo.vwtest
AS
SELECT col1,col2,col3
FROM dbo.Atable WITH NOLOCK
WHERE somecriteria

This is not totally without risk of viewing a record mid update, but on a 700 row table you shouldn't experience problems.

I've experience similar problems using Access linked to views in SQL server and there is a discussion here: http://discuss.joelonsoftware.com/default.asp?design.4.515190.14
That seems relevant.

 

[DataMiner]

I don't seem to have permission to view the Activity Monitor (SQLServer 2005). I've now asked IT to give me permissionl We'll see.

MEanwhile, I've not had a recurrence of this problem, so who knows??? Today I've implemented the SQL Server world for all my users, so if it's going to be a problem again it should be showing up soon....

 

[SQL_Hell]

Hi again,

Sorry for the late reply, been very busy lately

It does indeed sound like a deadlock was causing the issue, when they give you the right permissions you will be able change the query time-out by right clicking on the server name in management studio and the query timeout setting is under options.

I definitely would try to find out what activity there is on this server to see where the deadlock came from and whether it was something you did or something someone else did that was causing the issue.

I would also recommend re-indexing your database and updating statistics

 

[DataMiner]

My IT department is refusing to give me the permissions I need to view Activity Monitor on our "production server". Their reasoning is that there are other production db's running on it besides just mine. They say that they can only give me those permissions on our "test server". THis sounds to me like I will need to maintain two parallel universes, on on each server. Does their reasoning sound reasonable to you?

 

[SQL_Hell]

Yeah if there is other production databases on that server then it does sound reasonable. It's probably not that the fact that they dont trust you with those permissions, it more likely to be an issue for auditing.

However if you do see the view timing out again, I would go straight to the DBA and get him to find out where the deadlock is and what can be done to ensure it doesn't happen again.