SQL Server Performance

SQL Server Running Processes goes more than 200

Discussion in 'Performance Tuning for Hardware Configurations' started by adnanahmed235, Sep 15, 2004.

  1. adnanahmed235 New Member

    Hi,
    We have application ruuning on two web servers built on ASP.NET technology. These two web servers using SQL Server 2000 Enterprise Edition installed on 3rd PC. The configuration of 3rd PC in which SQL Server installed is following:
    OS: Window Server 2003 Standard Edition
    Hardware: Intel Xeon CPU 2.80 GHz , RAM 4GB
    SQL Server: SQL Server Enterprise Edition

    The problem which i am facing is after 2, 3 hours, SQL Server running processes goes upto 200 for my DataBase and CPU utilization goes upto 100% and site is down. So i have to restart the SQL Server, then restart the IIS of two web servers as well. After 5 minutes, Site is okay and working very fast.

    When SQL Server running processes less than 30, site is very fast and its processes is not increasing gradually, instead it suddenly shout upto 200.

    I am using stored procedures to call all the queries. I check database connection , that is also.

    As this is the big issue and i have to restart the live site every 2, 3 hours, now-a-days.
    Please any body about the solution of this problem or have any idea,plz reply me.

    Syed Adnan Ahmed
    Software Engineer
    adnan.pk@gmail.com
  2. satya Moderator

    Do not post multiple(duplicate) threads for same problem, I've deleted your post and pasting replies for your query below:

    Posted by Chopeen - 09/15/2004 : 09:52:34

    quote:quote:
    --------------------------------------------------------------------------------
    Originally posted by adnanahmed235

    I am facing the problem on my production server that its running processes after 2, 3 hours goes upto more than 200 processes and CPU usage is around 100%.
    --------------------------------------------------------------------------------

    What processes do you mean?

    --

    Marek 'chopeen' Grzenkowicz

    'You're so cute when you're frustrated.' -- Interpol


    My reply:
    Run SP_WHO2 and see what are those processes.
    WHat is the memory setting on SQL server?
    Any other application sharing the server resources?
    How about schedule of DBCC checks?


    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.
  3. bradmcgehee New Member

    Based on the information provided, my guess is that you are having a problem with your application, not SQL Server. The jump in connections should not occur like you describe. I can't debug an application via a forum, but as a start, I would do a Profiler trace of the activity between your application and SQL Server to look for clues.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. adnanahmed235 New Member

    Actually we are handling database connections from one centralized place, so i double check it, but could find bug.

    if u run the following script:

    Select spid, memusage,uid=rtrim(loginame),
    dbname=db_name(dbid), dbid, status=rtrim(status),
    Program_name = rtrim(Program_Name)
    FROM master.dbo.sysprocesses
    WHERE loginame = 'sa' AND Program_Name = '.Net SqlClient Data Provider'
    Order BY memusage desc, Status asc


    it will give u all SQL Server processes running by user=sa and program name = '.Net SqlClient Data Provider'

    It normally show around 30 sleeping connections and 4 or 5 running connections. But after sometime, running processes exceed upto more than 200. At that time, if i by force kill those processes , it will create new processes. In order to over come this situation, i have last option, that is, resart the SQL server. Then after few minutes, all the processes kills automatically and application run smoothly!.


    One more thing, i am using SQL Sevre search service that is scheduled for every 30 minutes as well. As i have to search the data from the database.

    No other application is using the SQL Server.

    i am looking for u people's reply.
    Regards,
    Syed Adnan Ahmed
    adnan.pk@gmail.com
  5. satya Moderator

  6. adnanahmed235 New Member

    Hi Satya,
    I will do these tests, for the time being, do u have any idea, how to overcome this situation, like i kill the running processes as it goes upto a certain limit.
    Bye
  7. adnanahmed235 New Member

    Satya,<br />please have a look at my DAL layer which is calling Stored procedures.<br /><br />Imports System<br />Imports System.Configuration<br />Imports System.Data<br />Imports System.Data.SqlClient<br />Imports System.Reflection<br /><br />Namespace DataLayer<br /><br /> Public Class DBPersistanceManager<br /><br /> Shared sqlTransaction As sqlTransaction<br /> Shared sqlConnection As sqlConnection<br /> Shared connectionString As String = "Server=servername;Database=databasename;uid=sa<img src='/community/emoticons/emotion-4.gif' alt=';p' />wd=password<img src='/community/emoticons/emotion-4.gif' alt=';P' />ooling=True"<br /> Shared connectionTimeOut As Integer = 30<br /><br /> Private sqlConn As sqlConnection<br /><br /> Public Sub BeginTransaction()<br /> sqlConnection = New SqlConnection(connectionString)<br /> sqlConnection.Open()<br /> ' Start a local transaction<br /> sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted, "DataLayerTransaction")<br /> End Sub<br /> Public Sub CommitTransaction()<br /> sqlTransaction.Commit()<br /> sqlConnection.Close()<br /> sqlTransaction.Dispose()<br /> End Sub<br /> Public Sub RollBackTransaction()<br /> sqlTransaction.Rollback()<br /> sqlConnection.Close()<br /> sqlTransaction.Dispose()<br /> End Sub<br /> Public Overloads Function Execute(ByVal className As String, ByVal methodInfo As MemberInfo, ByVal paramObject As Object) As DataSet<br /><br /> Dim sqlCommand As SqlCommand<br /> Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter()<br /> Dim dataSet As DataSet = New DataSet()<br /> Try<br /><br /> 'Check to see if already transaction created<br /> If Not sqlTransaction Is Nothing Then<br /> sqlCommand = DBSqlCommandGenerator.GenerateCommand(sqlConnection, methodInfo, paramObject, , , className)<br /> sqlCommand.CommandTimeout = connectionTimeOut<br /> sqlCommand.Transaction = sqlTransaction<br /> sqlDataAdapter.SelectCommand = sqlCommand<br /> sqlDataAdapter.Fill(dataSet)<br /><br /> Else<br /><br /> sqlConn = New SqlConnection(connectionString)<br /><br /> ' Generate Command Object based on Method<br /> sqlCommand = DBSqlCommandGenerator.GenerateCommand(sqlConn, methodInfo, paramObject, , , className)<br /> sqlConn.Open()<br /> sqlDataAdapter.SelectCommand = sqlCommand<br /> sqlDataAdapter.Fill(dataSet)<br /><br /><br /> End If<br /><br /> ' Return the data set <br /> Return dataSet<br /> Catch ex As Exception<br /><br /> ' Throw the exception<br /> Throw New DataLayer.DBException("Error Occured:" & vbCrLf & "Class: " & Me.GetType.FullName & vbCrLf & "Method: " & CType(MethodBase.GetCurrentMethod(), MethodInfo).Name, ex)<br /><br /> Finally<br /><br /> If sqlTransaction Is Nothing Then<br /> sqlCommand.Dispose()<br /> ' // Close connection.<br /> sqlConn.Close()<br /> dataSet.Dispose()<br /> End If<br /> End Try<br /> End Function<br /> End Class<br /><br />End Namespace<br /><br /><br /><br />------------------------------------------------<br />all my business logic accessing the Data Access Layer in order to access the database.<br /><br />Do i need to optimize the ADo.NET connection pooling as well.<br /><br />Regards,<br />Syed Adnan Ahmed<br /><br /><br /><br /><br /><br /><br /><br /><br /><br />---------------------------------------------------------------------------------
  8. derrickleggett New Member

    You need to select the count of the PID from sysprocesses and find out how many of these processes are from each PID. That will allow you to track down what process in the application is creating the connections. From that point, you need to troubleshoot the app. You have an application that is not properly closing connections or is improperly opening connections for something.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. chopeen Member

    quote:Originally posted by adnanahmed235

    if i by force kill those processes , it will create new processes.

    What happens when you kill a process?
    Does it disconnect a site user?

    [My first reply to this topic wasn't very intelligent, I know. I must've been tired.]
  10. satya Moderator

    Adnan

    First of all answer to the questions posted by other members, that may give assessment to resolve the issue.

    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.
  11. Twan New Member

    Hi ya,

    a couple of comments:
    - generally starting/committing/rolling back of transactions is way more efficient if it is done server side (within the stored procs)
    - transactions for selects are quite an overhead, and generally not the way to go... not sure if the execute method is used for select procs as well as insert/update/delete?
    - in the code above you have
    ' Return the data set
    Return dataSet

    before the "finally" bit. I thought that the return will actually abort the try/catch and return to the caller, so the finally bit in your method would only be called if there was an exception?


    Cheers
    Twan
  12. adnanahmed235 New Member

    Hi All,
    Here are my answers of those questions that you people have asked.
    In my DAL, transaction facility is there, but i am not using transaction in my whole application any more.
    Regarding Try .. Catch .. Finally, i am returning data set before finally statement but in any case Finally statement will be execute either exception occur or not.

    Any way i remove this transaction thing from my DAL layer. NOW DAL looks like following:

    Imports System.Data.SqlClient
    Imports System.Reflection

    --------------------------------------------------------------------------------------
    Namespace DataLayer

    Public Class DBPersistanceManager
    Shared connectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
    Private sqlConn As sqlConnection
    Public Overloads Function Execute(ByVal className As String, ByVal methodInfo As MemberInfo, ByVal paramObject As Object) As DataSet

    Dim sqlCommand As SqlCommand
    Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter()
    Dim dataSet As DataSet = New DataSet()
    Try
    sqlConn = New SqlConnection(connectionString)
    sqlCommand = DBSqlCommandGenerator.GenerateCommand(sqlConn, methodInfo, paramObject, , , className)
    sqlConn.Open()
    sqlDataAdapter.SelectCommand = sqlCommand
    sqlDataAdapter.Fill(dataSet)
    Catch ex As Exception

    ' Throw the exception
    Throw New DataLayer.DBException("Error Occured:" & vbCrLf & "Class: " & Me.GetType.FullName & vbCrLf & "Method: " & CType(MethodBase.GetCurrentMethod(), MethodInfo).Name, ex)

    Finally
    sqlCommand.Dispose()
    sqlConn.Close()
    dataSet.Dispose()
    sqlDataAdapter.Dispose()
    End Try
    Return dataSet
    End Function



    ---------------------------------------------------------------------------------------


    The execute method will execute SELECT/UPDATE/INSERT/DELETE Statement.

    When i kill the Status="Sleeping" process, no harm with database but when i try to kill status ="runnable" processes it kills no problem, but increase no of runnable processes. So no use of killing processes.


    I need little bit explanation about this question:
    " You need to select the count of the PID from sysprocesses and find out how many of these processes are from each PID. That will allow you to track down what process in the application is creating the connections."

    I am waiting for you people's fruit full ideas.
    Regards,
    Syed Adnan Ahmed
  13. satya Moderator

  14. derrickleggett New Member

    The PID is the actual Client Process ID on the Host connecting to SQL Server. If you group the connections by Process ID and HostName, you can find out what process on the host is causing the majority of your connections. Many times, this will allow you to troubleshoot a specific application, which is likely not closing the connections properly or has other issues.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  15. adnanahmed235 New Member

    Hi Satya and MeanOldDBA,
    I am not using any SQL Server transaction on the live site and we have only one application that is calling DataBase Server.
    Syed Adnan Ahmed
    adnan.pk@gmail.com
  16. satya Moderator

    Have you found the processess that are blocking each other and locking which is the main issue here?

    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.
  17. adnanahmed235 New Member

    Hi Satya,
    Thank you for guiding me for this direction.
    I found the processid but dont understand how its blocking in irregular period of time, like SQL Server processes increases more than 200 at random time interval.
    Today, it happen with the site, site is almost stopped then the following error page shown on the web site then i restart the SQL Server and reset the IIS as well. After sometime, site is ok.

    The error was faoolowing:
    ================================================================
    Transaction(Process ID 259) was deadlocked on lock resources with another process and has been chosen as network deadlock victim. Rerun the transaction.
    =================================================================
    As soon as this error comes on the site, i run the sp_who2 system stored procedure to find out the problem.
    The i find out, this process id is taking too much time and its command = "select" was there.

    Its calling one of my function ArticlePublishing.SelectByCriteria(positionid, articleid) As articlepublishingifnormation


    Actually, Every user control calls this function to populate the data, and there is only one Stored procedure who is handling this routine. If there is problem in this stored procedure or function, how it is working in normal cirsumstances and sometime after 1 day, giving this error.

    Please help me in this regard. What could be the reason when this Sql exception throws this type of error.

    Waiting for ur fruitfull remarks.

    Regards,
    Syed Adnan Ahmed
  18. satya Moderator

    To reduce the lock contention that contributes the deadlock:

    - Avoid situations in which many processes are attempting to perform updates or inserts on the same data page.
    - This particular hotspot situation can be avoided by creating a clustered index on a value that will insure each user and/or process is inserting to a different page in the table.
    - Avoid transactions that include user interaction. Because locks are held for the duration of the transaction, a single user can degrade the entire systems performance.
    - Keep the transactions to short and in one batch, network issues may also increase the problem of blocking if the transaction is trying to process.
    - Reduce the fillfactor when creating an index to help diminish the chance of random updates requiring the same page.



    Also search under this website for other tips on tricks to avoid blocking & deadlocks.


    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. derrickleggett New Member

    Also, if this has been chosen as a deadlock victim, you need to find out who the deadlock source was. You can find out how to track the entire deadlock chain by looking it up in Books Online. Let us know if you need help on that.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  20. adnanahmed235 New Member

    Hi All,
    Thank you for u people guidance. I will ask any question, if feel any problem, finding out the deadlock cuplprit.
    Regards,
    Adnan
  21. adnanahmed235 New Member

    HI Satya and MeanoldDBA,

    I need help regarding how to overcome dead lock situaton, as i am very careful doing any change on the live server, as its not happening on the production server, so i cannot test it. In Microsoft Books online, they stated that either to set deadlock priority to "LOW" or set the timeout of deadlock in order to minimize the locking. They are also talking about the trace flag 1204 to trace more. I dont know how to get that trace flag 1204. By doing, any change on the live site, i need u people help.
    As in my whole application, i am not using SQL Server transaction, so there is no question of locking due to transaction. But one thing i would like to mention in my application,which is, my application entirely depend upon 2,3 tables very extensively. and rest of the tables are used not so often. There is dynamic select statement going on for every reqtest that is selecting data from 1 or 2 tables. In my thinking, these 2 or 3 tables are infact bone of contention. But i am using select statement, i dont how to overcome load on that query.

    I am looking for u peoples experience.

    Regards,
    Syed Adnan Ahmed
  22. Kamen New Member

    I would suggest to check permissions and the actual sql statement. Sometimes an error in an object and it's crash leaves open connections, especially if you are running windows services of some sort. Check your stored procedure thoroughly, this happened to me with a bugging object i have been creating and it used to leave open connections after unhandled exceptions.
  23. satya Moderator

Share This Page