SQL Server Performance

SQL performance issue

Discussion in 'ALL SQL SERVER QUESTIONS' started by SQL DBA, Jan 22, 2012.

  1. SQL DBA New Member

    A SQL Server have a very bad perfomance which is in cluster environment. Need to do a immediate analysis. But not familiar with the applications or the databases, but was asked to find the problem as soon as possible. I would need plan of action, and process on what need to be checked and how to continue if not found any issue at first level. How to find things at high level along with assumptions and
    recommendations.

    Thanks in advance
  2. davidfarr Member

    It is also equally true that members of this forum are not familiar with your applications and databases. Performance troubleshooting is a somewhat broad area of expertise and usually requires a skilled DBA to be done effectively. You can Google "SQL Server performance troubleshooting" and the number of results you see will give you an idea of the broad scope of what you are asking.
    To start, you should perhaps determine if performance has always been poor, or has it become poor after a recent change ? If so, identify and examine that change.
    Many performance troubleshooting articles like to focus on hardware issues first, although I prefer to look at the software side first. You should perhaps start by identifying 'expensive queries' using Activity Monitor / Profiler / Performance Monitor. Identify the database objects that are frequently accessed, and especially note the indexing on these objects and how often the indexing is maintained (rebuild/reindex). Check for process locks and blocking on regularly used queries, some queries may perform better in a transaction isolation level. Some performance issues can be caused by poor referential design, poorly written queries or the use of numerous ad-hoc queries in place of compiled stored procedures. Once all of these factors, and more, are addressed, then you can look at hardware. Monitor for high usage of CPU, disk i/o and paging. Hardware troubleshooting for performance, in my opinion, should be a last resort, because it is an area that can cost you time, money and downtime if you decide to replace hardware, and is very often not necessary.
  3. Luis Martin Moderator

    "It is also equally true that members of this forum are not familiar with your applications and databases"
    Indeed.!:)

Share This Page