SQL Server Performance

Automatically kill a process

Discussion in 'General DBA Questions' started by CanadaDBA, May 10, 2004.

  1. CanadaDBA New Member

    Env.: SQL2000, Win2000
    App.: Access2000

    I had a situation some days ago in the morning that all clients' applications were waiting for a locked process. It seemed a client had turned off his computer without exiting his application last night and therefor the resource left locked.

    1. Is my conclusion correct?
    2. Do you recommend that I kill a process which is left for a specific time? If yes, is it possible to automate this?
  2. derrickleggett New Member

    I recommend you turn his linked table into a pass-through query so he doesn't lock your system up everytime he opens his application.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. gaurav_bindlish New Member

    The application needs to be revied for this. The application should never hold locks on the data and wait for user's input. There are many recommendations for application design on this site...

    Killing a process is not a good practice. It may happen some day that the maintainence job has been running for 10 hours and you kill the same. It'll take almost the same time to roll it back.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  4. CanadaDBA New Member

    Gaurav - I inherited the application and the client doesn't approve mager changes. I am wondering if I can do something in SQL side. Thank you for the comment about killing processes. I'll keep it in my mind.

    Derrick - I like your recommendation. Would you please give me some hints about how I can implement it. Thanks
  5. CanadaDBA New Member

    There is an automate process which imports data from text files into some tables. This happens at 3:00AM everyday. At that time there shouldn't anybody working with the DB. I want to detect users who had left their programs logged on and locked my DB. Then kill those process and let my import works.

    If I detect and find out who is doing the process, then how can I kill the process. I don't want to use EM or AQ. I want the application calls an SP and that SP finds the locking process and kills it.



    Farhad R
    CanadaDBA@Yahoo.ca
  6. Raulie New Member

    Here is a handy sp that kill ALL procesess on the the DB. Note use this SP with extreme caution, becuase like the other forum members mentioned if there are jobs running this kill the spids as well. Only use this sp if you are absolutely sure that you want to kill all spids. Here it is. Also like Derrick mentioned I would revise the app not to use link tables as they are resource intensive. Good luck!

    CREATE PROCEDURE sp_ku @dbname varchar(50)

    AS
    SET NOCOUNT ON

    DECLARE @strSQL varchar(255)
    PRINT 'Killing Users'
    PRINT '-----------------'

    CREATE table #tmpUsers(
    spid int,
    eid int,
    status varchar(30),
    loginname varchar(50),
    hostname varchar(50),
    blk int,
    dbname varchar(50),
    cmd varchar(30))

    INSERT INTO #tmpUsers EXEC SP_WHO


    DECLARE LoginCursor CURSOR
    READ_ONLY
    FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

    DECLARE @spid varchar(10)
    DECLARE @dbname2 varchar(40)
    OPEN LoginCursor

    FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT 'Killing ' + @spid
    SET @strSQL = 'KILL ' + @spid
    EXEC (@strSQL)
    END
    FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
    END

    CLOSE LoginCursor
    DEALLOCATE LoginCursor

    DROP table #tmpUsers
    go
  7. CanadaDBA New Member

    Thank you Lazy_DBA,

    I am going to read your sp. But as I asked Derrick (and he haven't answered yet), how can I use query instead of linked table? The app is Access and the development team is using it. Do you mean I use view for each table? I couldn't get Derrick's 'turn his linked table into a pass-through query'

    Farhad R
    CanadaDBA@Yahoo.ca
  8. Raulie New Member

    I suggest you look into linked tables and Pass-Through query in MS Access help files or go out and grab a book on Access with SQL Server I dont know the exact name but there is a book specific to Access as a front end to SQL Server also Access unleashed is pretty good. It's been ages since I programmed with Access, but I dont think this will be an over night mission for you, it will require change in your application code.
  9. derrickleggett New Member

    I was at a Microsoft Security Conference yesterday. Sorry about that. To create a pass-through query, you just go into the Query pane and design a new query. You will select Pass-through as your type on the top menu. You can then put in EXEC stored_procedure and it will execute the stored procedure. You then right-click on the title bar to set your connection string.

    Basically, this will just execute a stored procedure in SQL Server, which you can have pull the same recordset the linked table pulls. You can set that as your recordset in the form. If the form needs to be updateable, you will have more work as you need to create insert and updates statements.

    If you want to see examples of this, there are a lot of good books such as Access Bible.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  10. CanadaDBA New Member

    Thank you Derrick. I didn't know about pass-Through query. I thought it's a technique. "Lazy_DBA"'s reply taught me it is an Access feature and I read Access help. Your reply confirmed what I learnt yesterday. So, I have to do (or ask our developers do) some changes in the application, which I think the client would not accept.

    I am asking those who have already implemented both "Linked tables" and "Pass-Trough query" methods, do you think there should be too many changes to convert from the first one to the second one? For example, there is a link to tblAccount and there are some SELECT, DELETE and UPDATEs in the application. So, I have to create a query for each of these. If application has two SELECTs with different columns, I have to create two pass-trough query to support them and in the application call the appropriate sp. Am I right?

    Sorry, if it seems silly but I want to confirm my understanding then prepare a report estimating the size of changes in the application.

    Thank you and appreciate your patients




    Farhad R
    CanadaDBA@Yahoo.ca
  11. Raulie New Member

    Since I it has been a while since I have used ACCESS I got a little mixed up. I do remember that they gave a better performance boost than using direct connection with DAO. Linked tables do not pull all the SQL Server Data into the local Jet Engine (Access) the data remains in the SQL Server tables. Also Link tables store the database schema information locally in the linked tables reducing startup delays. Im sure you wont be using DAO since it is primitive now and has been replaced with ADO which is much faster. I recommend you do more research on pass though queries and SQL Server before you make any recomendations to your developers. If the locking were the only performance hits, you can circumvent the problem with other solutions. Let me look in my archives to see what I can dig up. Check out this site from http://www.candace-tripp.com/_pages/index_fl.asp it even looks like she gave her website a new look since I last visited. Good luck!
  12. x002548 New Member

    I think He is a she....

    Mensa member!

    What IQ does that have to be?





    Brett

    :cool:
  13. Raulie New Member

    quote:Originally posted by x002548

    I think He is a she....

    Brett

    :cool:

    Oops, so your saying that it's a He She, is she your mother or something?

    Menso member!

    What IQ does that have to be? 8-(

    By the way mistakes can be corrected.
  14. CanadaDBA New Member

    Seems the site has good information for me. I am going to check it. As far as I know, the application was built with DAO but when they converted it to use SQL-Server, they changed some part of the application to ADO and in addition to the luck problem, we are having experience of ODBC error, which I may write it under a new title. Thanks

    Farhad R
    CanadaDBA@Yahoo.ca
  15. Raulie New Member

    What type of odbc errors?
  16. CanadaDBA New Member

    Lazy_DBA,

    The error code is [Microsoft][ODBC SQL Server Driver]Timeout expired (#0) 3146

    This happens (only sometimes) when the following code is running:

    Private Sub UpdateMainAndComments()
    'updates table main and comments with ABC table information
    Dim strSQL As String
    Dim ABRS As Recordset

    strSQL = "Select BNumber from qryactivebranch order by BNumber"
    Set ABRS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    If ABRS.EOF And ABRS.BOF Then
    ABRS.Close
    Exit Sub
    End If

    ABRS.MoveFirst
    Debug.Print "Start: " & Now
    Do While Not ABRS.EOF
    strSQL = "UPDATE (ABC INNER JOIN tblMain ON (ABC.[Account #] = tblMain.Contract) " & _
    "AND (ABC.[4Dig] = tblMain.Emp) AND (ABC.FSO = tblMain.Branch)) " & _
    "INNER JOIN tblComments ON (tblMain.Contract = tblComments.Contract) " & _
    "AND (tblMain.Emp = tblComments.Emp) " & _
    "AND (tblMain.Branch = tblComments.Branch) " & _
    "SET tblMain.[ABC Account] = True, " & _
    "tblMain.[SPB Number] = [ABC]![SPB], " & _
    "tblMain.[Ret Reason] = [ABC]![Ret_Reason], " & _
    "tblMain.[Ret Type] = [ABC]![Ret_Type], " & _
    "tblMain.[Ret Date] = [ABC]![Ret_Date], " & _
    "tblMain.[Date Available for Sale] = [ABC]![Avail_Sale_Date], " & _
    "tblMain.[Ready For Sale Date] = [ABC]![Ready_Sale_Date], " & _
    "tblMain.[Ready For Sale Indicator] = IIf([ABC]![Ready_Sale_Date] Is Not Null,'X'), " & _
    "tblMain.[Sold Date] = IIf(tblMain![Sold Date] Is Null,[ABC]![sold_date],tblMain![sold date]), " & _
    "tblComments.[ITS Title Received Date] = IIf(tblComments![ITS Title Received Date] Is Null,[ABC]![Paper_Recvd_Date],tblComments![ITS Title Received Date]), " & _
    "tblMain.[ABC Extract Date] = [ABC]![Processing_Date],tblMain.[ABC Initials] = [ABC]![Initials], " & _
    "tblMain.[Ret Branch] = [ABC]![Ret_FSO],tblMain.[Ret Emp] = [ABC]![Ret_EMP4], " & _
    "tblMain.[Ret Location] = [ABC]![Ret_Location],tblMain.Date_XX_Not = [ABC]![Date_XX_Not], " & _
    "tblMain.Glad_R_Date = [ABC]![Glad_R_Date],tblMain.[Disposition Code] = [ABC]![Disposition Code], " & _
    "tblMain.[Emp account] = [ABC]![Emp account number], tblMain.[RPEmpNum] = [ABC]![RPEmpNum]," & _
    "tblMain.[RDAN] = [ABC]![RDAN], tblMain.[BusinessType] = [ABC]![BusinessType]," & _
    "tblComments.[HSH Date] = IIF(IsNull(tblComments.[HSH Date]), [ABC]![Current_Recvd_Date], tblComments.[HSH Dropoff Date]) " & _
    "WHERE (((tblMain.[Branch])= " & ABRS.Fields(0) & "))"

    DoCmd.RunSQL strSQL, False
    Debug.Print "End: " & Now
    ABRS.MoveNext

    Loop
    End Sub

    The code joins 3 tables and updates 2 of them (tblMain, tblComments)with the information of 3rd one (HSH). This is in a WHILE until all accounts are updated. Do you think something is wrong with the code? It runs very well and sometimes generates the error. Is it because of Access / VBA connection to SQL Server. I mean some exceptions or bugs for this type of connection.

    Thanks,

    Farhad R
    CanadaDBA@Yahoo.ca
  17. Raulie New Member

    How many concurrent connection are you making?
  18. satya Moderator

    Timeout issues can be caused by a number of problems. Typically they are caused by a deadlock condition where one process is holding a resource while attempting another operation. If the process blocking the operation is doing the same thing you're locked out and pooched. The system can detect this in some cases.

    Consider that the query plan is created the FIRST time a SP is executed and reused indefinitely until it's pushed out of cache by more important data.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  19. CanadaDBA New Member

    I noticed that there are at most 6 concurrent connections. Also, there are 115 users who run the application everyday.


    quote:Originally posted by Lazy_DBA

    How many concurrent connection are you making?

    Farhad R
    CanadaDBA@Yahoo.ca
  20. Raulie New Member

    When Access starts reaching 10 performance starts to suffer. If you have 115 users running the app on a daily bases you should start to look for another front end alternative. I believe msde versione is a lot more powerful it uses the same engine as SQL Server not the jet engine.
  21. Adriaan New Member

    Why aren't you feeding a proper UPDATE statement to SQL Server by way of a pass-through query or an ADO connection? The default time-out that Jet assumes is 60 seconds, IIRC, and Jet UPDATEs on SQL Server tables can be time-consuming.

    You'll have to check the correct syntax in T-SQL, because Jet-SQL is a bit different from that.

    Also, there is an incomplete IIf statement in your query:
    IIf([ABC]![Ready_Sale_Date] Is Not Null,'X')
    It's missing the False part.

    Finally, use tablename.fieldname instead of tablename!fieldname - the exclamation mark suggests it's a field on a form or report, and Jet first has to check what kind of object you're referring to (one of the nice things about Jet-SQL is that you can include calls to VBA functions and references to open Access objects, but there are a lot of performance pitfalls). Use the dot and Jet will immediately assume it's a table referenced in the FROM clause.

Share This Page