Performance issue with a single database

  • Hi,

    Im using the SQL 2008 R2. Iam required to perform two tests on one of our database as anything to do with it, seems very slow.

    I have no control on the applications/query as it is a packaged vendor; however i do have some ability to initiate certain transactions from the application side that could be a possible culprit. I only have admin access at the database side. I know the name of the database in question that iam suspecting.

    1) What tests can i perform on the sql side on this database so i can identify and confirm it.

    2) How do i test for fragmentation on this database and its indexes?

    3) What tools or scripts if available can help me to identify the cause?

    Please guide. Thanks.

  • Can you generate execution plans? It would be very helpful to see the actual execution plan from a query that is slow.

    For maintaining indexes you should check out this link. http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Yes, i was able to view the graph and cost from the studio, but i could not make head or tail out of it!

  • The issues you're facing are all covered in this book: Troubleshooting SQL Server: A Guide for the Accidental DBA[/url].

    Take a look and make it your faithful companion.

    EDIT: And be sure to check the other books in the Books section from this site. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • thanks

  • You can also look to see if processes are getting blocked.

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

    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
  • Hey,

    Im back at this again. I rebuilt my index and that fixed it!

    However , now after couple of days ( i should say ), the database is exhibiting the same problem. I know that if i rebuild the index again, that would fix it.

    Also, upon checking for fragmentation i notice that some of the tables in this Database has the value as 100 for the column - avg_fragmentation_in_percent.

    I rechecked after performing another rebuild but the value still shows 100 ? WHy is that so? I was hoping that it would show a more ideal value for the avg_fragmentation_in_percent.

    Thanks,

    Jai

  • Too small for rebuilding to matter?

    It's unlikely that rebuilding the index fixed the problem. It's far more likely that the statistics update which the rebuild performs fixed your problem. Consider adding some manual statistics updates on the tables you identified.

    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
  • yes, I did think so! and then i went on to verify the views that show me the statistics last update.

    I then realise that we also have a job that runs and updates the statistics almost every day. This did reflect in the STATS_DATE(A.OBJECT_ID,A.INDEX_ID) from the Indexes and Objects.

    So thats what confuses me..that even though we do have the stats getting updated everyday, the stats (detailed sampling for fragmentation ) arent getting any better. Weird!?

  • Im assuming and expecting a value of 0 to be most ideal

  • GilaMonster,

    You're right!

    Its the stats update. That fixed it. However Im trying to understand how this works. Would you be able to give some idea? Thanks.

  • Google SQL Server statistics and do some reading. Far more than I can summarise here.

    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 13 posts - 1 through 12 (of 12 total)

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