Post reply

Transaction Efficiency

  • July 18, 2011 at 1:19 pm

    #254583

    We have been having random slowdown issues and i've been tasked to determine if any of it is related to our SQL database. We are using SQL 2008 and we are an ASP and PHP shop so we do alot of SQL calls to the database.

    I've tried looking into things like SP_WHO or SP_WHO2 and even looked into the sysprocess table. I'm just not sure what to do with the information or what it means. What I would really love is to see if there was a transaction taking to long to be able to track down that SQL statement.

    We do not have a DBA on staff so we are kind of learning as we go. Where would be a good place to start? I've tried looking at msdn but don't really understand what some of it means.

    Any help would be appreciated.

    Thanks

  • July 18, 2011 at 1:23 pm

    #1354455

    is the slow done periodic , such as during times when backups etc are being performed.

    Have you tried running Perfmon to monitor the system and sql resource utlization

    Any bulk operation happening in the DB. Index rebuilds etc ?

    Check for blocking issues under sys.sysprocesses

    Jayanth Kurup[/url]

  • July 18, 2011 at 1:28 pm

    #1354458

    We did change when backups were happening so i'm pretty confident thats not it. The slow downs seem to be random.

    We did run Perfmon and didn't see anything irregular.

    Not sure how to check for "

    Any bulk operation happening in the DB. Index rebuilds etc "

    or

    "Check for blocking issues under sys.sysprocesses"

  • July 18, 2011 at 2:00 pm

    #1354480

    SQL Script to check blocking and blocked processes.

    http://sqlserver-training.com/sql-script-to-check-blocking-and-blocked-processes/-

    To check for Index Rebuilds it would normally be a job. You could also check the processes and the statements that are being executed. The Account usually narrows it down.

    Edit:Typo on the tag.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 18, 2011 at 2:18 pm

    #1354492

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2011 at 2:33 pm

    #1354503

    I would also recommend that you setup a server side trace to capture all database calls that take more than xx seconds. You can start with 5 seconds and see how many you get and adjust from there.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 18, 2011 at 3:04 pm

    #1354520

    how do you do a server side trace? and how will that effect production?

  • July 18, 2011 at 3:08 pm

    #1354522

    See the first article I referenced

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2011 at 12:40 am

    #1354621

    Be sure to post the results of the above steps , it should help identify what issues your facing .

    Poor DB design could also be a factor by the way.

    Jayanth Kurup[/url]

  • July 19, 2011 at 7:37 am

    #1354787

    I will thanks for the help, i'm reading the article and will need to figure out a good time to run the trace without effect production to much.

  • July 19, 2011 at 7:43 am

    #1354799

    Run it when the system is slow. Taking a trace that's intended to catch slow-running queries and running it at a time when the server is idle is counter-productive. You need to run it when the performance problems that you want to resolve are occurring.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply