August 10, 2015 at 4:26 am
Hello,
I think I have an issue with database locks. We have application with MS Access client side and SQL DB server side. Business logic mainly implemented with use of stored procedures. The interaction between client side and the SQL is via ODBC connection.
From time to time I get "query timed out" message on the client side and I suspect it is related to locks on database objects. I tried to address the issue with implementing WITH (NOLOCK) on SELECT queries, but probably the problem is deeper than I thought.
Please advice.
Thanks in advance!
August 10, 2015 at 6:52 am
Igor_IM (8/10/2015)
Hello,I think I have an issue with database locks. We have application with MS Access client side and SQL DB server side. Business logic mainly implemented with use of stored procedures. The interaction between client side and the SQL is via ODBC connection.
From time to time I get "query timed out" message on the client side and I suspect it is related to locks on database objects. I tried to address the issue with implementing WITH (NOLOCK) on SELECT queries, but probably the problem is deeper than I thought.
Please advice.
Thanks in advance!
Hi and welcome to the forums. There really isn't any information in your post here. You need to strike NOLOCK from your brain as solution to improve performance. It is NOT a magic go faster button. It brings a lot more baggage to the table than just dirty reads. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Back to the problem at hand. What makes you "think" you have blocking? What you done that suggests the problem is locks/blocking?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2015 at 7:12 am
Hello. Thanks for the response. I got the point concerning NOLOCK... Anyway I came to the conclusion there is an issue with db lock when I run SELECT * FROM sys.dm_tran_locks. Then I found there locked "request_session_id" and killed it and it solved the problem (for this time). I know this sounds like "try & pray" solution and I definitely do not have enough knowledge and will be happy to learn from real professionals
Thanks in advance
August 10, 2015 at 8:42 am
Igor_IM (8/10/2015)
Hello. Thanks for the response. I got the point concerning NOLOCK... Anyway I came to the conclusion there is an issue with db lock when I run SELECT * FROM sys.dm_tran_locks. Then I found there locked "request_session_id" and killed it and it solved the problem (for this time). I know this sounds like "try & pray" solution and I definitely do not have enough knowledge and will be happy to learn from real professionalsThanks in advance
You also need to be careful with the KILL as if that session is in the middle of doing work (insert/update/delete) then that work is lost. The place to start is to find poorly performing queries and find ways to improve the queries. That can be re-writing the queries and evaluating the indexes. Since you posted in a 2005 forum I'm going to recommend using Profiler/SQL trace to help identify long-runnign queries. You can get a start by reading this Simple Talk article[/url]. You can also learn about how to set up a server-side trace on this Stairway Series[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2015 at 8:47 am
Jack Corbett (8/10/2015)
Igor_IM (8/10/2015)
Hello. Thanks for the response. I got the point concerning NOLOCK... Anyway I came to the conclusion there is an issue with db lock when I run SELECT * FROM sys.dm_tran_locks. Then I found there locked "request_session_id" and killed it and it solved the problem (for this time). I know this sounds like "try & pray" solution and I definitely do not have enough knowledge and will be happy to learn from real professionalsThanks in advance
You also need to be careful with the KILL as if that session is in the middle of doing work (insert/update/delete) then that work is lost. The place to start is to find poorly performing queries and find ways to improve the queries. That can be re-writing the queries and evaluating the indexes. Since you posted in a 2005 forum I'm going to recommend using Profiler/SQL trace to help identify long-runnign queries. You can get a start by reading this Simple Talk article[/url]. You can also learn about how to set up a server-side trace on this Stairway Series[/url]
You can do some work with indexes using Jason Strate's index analysis stored procedure[/url]. This uses DMV's like sys.dm_db_index_usage_stats and the missing index DMV's to give you some recommendations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2015 at 9:05 am
Igor_IM (8/10/2015)
I suspect it is related to locks on database objects.
Don't 'suspect'. Investigate, identify the problem and then fix it. Otherwise you're going to waste a lot of time doing things which are not in the slightest bit useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2015 at 9:22 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply