MS SQL Tables Locking Part 2

[trent]

Thanks, I've checked that link before and it's not very informative. Unfortunality this is for Client A and they only have SQL 2005. Makes things harder to find.

Yesterday I upgraded Client A and Client C to have the new driver string: /MULTIPLEACTIVERESULTSETS=TRUE /BUSYHANDLING=2 /BUSYRETRIES=5 /BUSYMESSAGE=The connection is busy, please try again later. /LOGONSCREEN=FALSE /VERIFYVIASELECT=TRUE

Client C has not emailed today saying that their desktop app is freezing but Client A has every desktop app freezing at the moment. I have restarted the WebServer but the app is still freezing so now I'm thinking it's not limited to the WebServer anymore... Also the BUSYMESSAGE doesn't appear, has anyone gotten this switch to work?

Delving into the desktop app now...

Regards,
Trent

I am not sure if that query will work in 2005, some of those dynamic management views may be new in 2008.  
This link gives an explanation of 2005 activity monitor  http://technet.microsoft.com/en-us/library/ms175518(v=sql.90).aspx

Do you have access to a 2008 server?  They added quite a few tools to manager performance in 2008.

On a side not, whoever made this forum REALLY needs to fix the problem with losing topics after so many responses are added and having to post a second topic.

[debzidoodle]

This is totally from memory, it has been a long time since I was in 2005.  But I think that activity monitor is  in the Object Explorer (aka the slide out window usually on the left side where you see your server and databases).  I think its toward the bottom by the or inside of the SQL Server Agent menu.

Have you looked at turning off the Page Locking on your high transaction table indexes?

[trent]

Yes I found the Activity Monitor but there is no way to see the most expensive queries, which also means I can't find out which indexes have the most transactions. I think this is only in 2008 and above.

I can't turn off Page Locking until I know which indexes to turn it off on.

Does IsolationLevel and LogoutIsolationLevel have anything to do with table locking?

Regards,
Trent

[debzidoodle]

You do not need the most expensive queries to know which indexes to turn off Page Locking on.  And also adjust your fill factor.  Do it on your Primary key's on the tables that have the most data being entered.  The fill factor and page locking are different performance tuning options than what you get with missing indexes.

As for isolation level, its can; but you want it to be read committed.  Blocking is normally either transactions being started and left open, indexing issues, or improper queries.

Do you use sequential numbers or guids for your primary keys?

[trent]

We use sequential numbers.

I can't set the Page Locking or Fill Factor on the pk index in SQL 2005, all the options are disabled - see attached image.

[attachment deleted by admin]

[trent]

I've found scripts to rebuild the indexes with a Fill Factor of 80 and also set the Page Locking to disabled in SQL 2005. Doing this for Client A at 3:30pm today so hopefully I can get some results after that.

Regards,
Trent

[debzidoodle]

Hi Trent, fill factor will be less important on a PK that is sequentially numbered.  But I would still turn off the Page Lock to see if that helps.
The other thing you could do to find indexes that are needed is to run SQL Profiler, have it log all the transactions to a table.  Then you can find the "Database Engine Tuning Advisor" in the tools menu of Management Studio.  Run this tool over the table profiler created.  It may have some suggestions for your indexing as well.  These options are available on 2005.

[trent]

Hi Debra,

Client A are still experiencing freezing issues today which means changing the Fill Factor and disabled Page Locking did not fix the issue.

This has now gone over what I can handle. Is anybody available for paid work to help me sort this out?

Regards,
Trent

[Larry Sand]

Trent,

FWIW, Take a look at PTSS 40719, David shows that the rtl will grab the wrong connection in a transaction sometimes.  Also if your ntws runs as a service with MS SQL Server, I found that you have to get the Service Control Manager to open a file on thread one, otherwise transaction processing doesn't work correctly.  It does a SET IMPLICIT TRANSACTIONS ON and never turns it off.  You'll see this happen in profile on an MDI app too if you do not open a file on thread one.  I ended up sending a message via ControlService() to the SCM and it raises an event that calls a callback on thread one where I open a file. 

Larry Sand

[trent]

Thanks, Larry. That PTSS does look like the issues I'm having.

>> I found that you have to get the Service Control Manager to open a file on thread one...

How do you do this?

>> I ended up sending a message via ControlService() to the SCM...

What is ControlService() and where does it come from?

Regards,
Trent

[Larry Sand]

Hi Trent,

ControlService is a Windows API function: http://msdn.microsoft.com/en-us/library/windows/desktop/ms682108  I'm using Self Service and added properties to let me know on thread one if I should open or close the file, and a method that calls ControlService to send the interrogate message to SCM that in turn ends up causing a derived _ServiceHandler() to be called on thread one where you can evaluate the message sent to the service from the SCM to open or close the file.  I am checking for SS_SERVICE_CONTROL_INTERROGATE and SS_SERVICE_CONTROL_STOP.  If you read the SS source code it's apparent how to do this.  You need your own properties because the SCM will normally send the interrogate message to your service under other circumstances but all services must support this message.

Hope that helps,
Larry Sand

[trent]

Hi Larry,

Thanks for that, it's a bit over my head so I will need to look at this later on when I have more time.

Regards,
Trent

[djohnson]

Hi Trent,

If you are using C9 for development, you might checkout the current release of C9.1. We submitted a sample app to SV demonstrating a problem with the MS SQL driver and transactions, and it looks like they have the problem fixed from my testing today. Here is the note from the readme file for the release:

9.1 Alpha 10971
FIX: If a new connection was needed by the SQL drivers and an old connection was available and a transaction was active, the reused connection was not added to the transaction frame
FIX: The ODBC based drivers would sometimes reuse an old connection when an existing active connection could be used

[trent]

Hi djohnson,

We are still on C8 at the moment, waiting for 9.1 to be released before upgrading. But it's good that there is more work being done by SoftVelocity to fix these issues.

I have found that C8 wasn't closing around 13x tables in an update procedure and I suspect this could be causing some of the problems. Just waiting on results of this fix from the clients today.

Regards,
Trent

[trent]

Hi Everyone,

I was able to resolve the locking issues with your help. In the end it came down to a number of things:

1) Optimizing the MS SQL database - changing the Fill Factor, disabling Row Locks, rebuilding Indexes for the largest tables that get used the most.
2) Wrapping all 'access:' and 'prop:sql' in the problem DLL with 'SET LOCK_TIMEOUT' prop:sql statements.
3) Not using MARS on the tables that were locking. I now have two global driver options strings, one with MARS enabled and the other with only BUSYHANDLING=2.

It was not the Nettalk WebServer that was locking the tables. It was the desktop app!

Thank you everyone for your help.

Regards,
Trent