SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy Claason / Is Your Database Application DeadLock and Timeout Resistent? / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 16:25:56 GMT20RE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxWHERE ius.database_id = DB_ID() AND DB_NAME(ius.database_id)=@YourDatabaseNameThe first condition is sufficient right? ius.database_id = DB_ID() should uniquely identify the correct database without the need for checking the db name as well.Or am I missing something?Thanks!Thu, 18 Mar 2010 08:16:46 GMTKarl KieningerRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxHi Tim,I am pretty sure that this statement is not accomplishing what you intended.exec('USE ' + @YourDatabaseName)When Exec is executed the database context is changed, but only for the statement(s) in the parenthsis. When Exex completes the database context returns to its previous setting.You can see that behavior with the following test script.[code="sql"]Use AdventureWorksDeclare @myDatabase varChar(255)Set @myDatabase = 'Northwind'Exec ('Use ' + @myDatabase + '; Select db_Name(); Select [name] from sys.objects where type = ''U'' ')Select db_Name()Select [name] from sys.objects where type = 'U'[/code]You will see that the database context returns to AdventureWorks after the conclusion of the Exec script.I am still looking for a reasonable methog to dynamically change the database context so if you find one please post it :)Tue, 16 Mar 2010 10:48:27 GMTRay HerringRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspx[quote][b]GDI Lord (3/10/2010)[/b][hr]Perhaps you should note in your article that the first script that you posted, the one that shows "TimesAccessed", only shows the data since the last restart of the SQL Server instance.[/quote]Indeed. Good point GDI Lord.Thu, 11 Mar 2010 07:13:13 GMTtimclaasonRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxPerhaps you should note in your article that the first script that you posted, the one that shows "TimesAccessed", only shows the data since the last restart of the SQL Server instance.Wed, 10 Mar 2010 23:36:54 GMTAndrewJacksonZARE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxThanks Tim. I will add this to my tool-set.Wed, 10 Mar 2010 12:49:05 GMTSQLRNNRRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspx[quote][b]timothyawiseman (3/10/2010)[/b][hr]Of course, I cannot overemphasize the fact that developers should be careful and thoughtful in using it since it risks dirty reads.[/quote]Agreed. It's easy for the scope of an article like this to expand exponentially. Each of the ways that I mentioned to deal with resource contention, etc, could have been their own article, and it would be easy to spend hours (or at least lots of minutes) talking about each one.Every one of the things I mentioned carries some level of risk (taking database interaction off the UI thread, playing with isolation levels, query hints, etc), but then again, we live in a pretty risky world :-)Wed, 10 Mar 2010 12:25:14 GMTtimclaasonRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxA very interesting and thought provoking article. Thank you!On actually making your application more resistant to locking, the [i]careful[/i] use of the "with (nolock)" query hint can improve performance on a server with a lot of locking tremendously.Of course, I cannot overemphasize the fact that developers should be careful and thoughtful in using it since it risks dirty reads. But there are times when a dirty read is acceptable, such as when you can known with great confidence that the records you are selecting are not being modified at that moment or when all you really care about is rough approximations of aggregate values. In those cases, it can be tremendously helpful.Wed, 10 Mar 2010 12:02:05 GMTtimothyawisemanRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxnice article can you also give us the solutions to make it resistentWed, 10 Mar 2010 03:13:56 GMTAbhijit MoreRE: Is Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxAny cheap and cheerful mechanisms for testing this issue in a non-production environment are most welcome. Brute force will reveal issues that really need sorting.Wed, 10 Mar 2010 02:38:05 GMTCraig@WorkIs Your Database Application DeadLock and Timeout Resistent?http://www.sqlservercentral.com/Forums/Topic879936-2631-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Database+load/69378/">Is Your Database Application DeadLock and Timeout Resistent?</A>[/B]Tue, 09 Mar 2010 22:45:06 GMTtimclaason