none
Performance tuning

    Question

  • I am new Hire in an IT company,

    i am asked to improve the performance of one of the databses .

    i am not too sure where to start from.


    k

    Thursday, July 04, 2013 7:04 PM

Answers

  • Hallo Huuh,

    sorry if my post may disgust you - but...

    You've hired in an IT-company as a dba and ask for "basics".
    Are you kidding or do you want to know how much fun we have in this forum :)

    Get back serious and get started.

    First of all you need to get an overwiew of the status of SQL Server itself. Therefore it's interesting to know all about the hardware. This will be the first step INTO getting familiar with "the system".

    If you know the hardware parameters you have to have a look to the setup of the tempdb.
    The tempdb is the workbench of sql server. A very "rough" basic:

    If you have less than 8 cores check whether you have at least 8 files:
    If you have more than 8 cores leave it to 8 files and check the contention of the tempdb by using fn_virtualfilestats() and increase the number of files by 4 for each step... (very rough recommendation!!!!)

    Next step should be the analysis of the waiting stats of your system. If you google wait stats you'll get hundreds of thousands of articles concerning this issue. To get in details concerning each wait stat will explode my answer to you.

    ok. - now after checking all above the next step is your database itself.

    Sigh... - where to start...

    hmm - check the disk layout for your database.
    - is data and log separated?
    - is your database using multiple filegroups
    - are you using partitioning
    - how large is your database
    - how many people (sessions) are using the database
    - what is the workload for the databases.
    - is the blocksize of the underlying disks (if not SAN) >= 64 KB and is the storage dedicated to database activity?

    next step is IN the database

    - what is the fragmentation of existing indexes
    - what is the usage of indexes
    - what is the operational stats of indexes
    - what is ...

    I can ask hundreds of questions. Long story short... - nobody can give you a serious answer where to start if nobody nows WHAT is the bottleneck of your database.

    If I get back to the first sentence of your post I'm really afraid on the one hand - on the other hand I know why I don't have to have sorrows about my future income as a database professional :)

    Sorry for the last sentence - SCNR :)

    Best would be a basic seminar for getting started - this should be the basic platform of becoming a good dba.
    As you can see from my "question and suggestion" there is no "best practise" because we have hundreds of separate impacts which will affect your database(s)

    BTW: Uri has given you a pretty good start to investigations in your first thread. I would recommend NOT to start an new thread for the same issue!

    Best, Uwe


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Thursday, July 04, 2013 8:00 PM
  • Below are only pointers to get you started and the question can have a lot of different answers:-

    1. Check whether all databases are performing slow or just this one as for former, that seems to be more of a server issue than a single database performance issue.

    2. Check for the database size and also all table sizes. If any of the table is in xx GB (double digit) then you would want to setup partitioning for that table or if partitioning seems complex then atleast have two filegroup and have the data under separate files and the data that is only used for OLAP purposes, put them under READ-ONLY format.

    3. Check for Long Running Queries in your database and setup alerts for the same so that you are notified when the query is executing from let's say last 60min.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2f10b92f-6a13-4c1d-a08b-072ae8c35f0d/how-to-find-top-long-running-queries

    4. Check for Blocking and Deadlock in your database. Below is the link to help you get started, setting alerts.

    http://www.practicalsqldba.com/2012/07/sql-server-instant-deadlock-alert-using.html

    http://www.practicalsqldba.com/2012/07/sql-server-instant-blocking-alert-using.html

    This will make sure that you are notified on blocking and deadlock. If you notice that happening very often then you will need to work on the query and read the execution plan for the same to tune the query as well.

    Below link will give you the basics on execution plan if you are not aware of it.

    https://www.simple-talk.com/sql/performance/execution-plan-basics/

    5. Check for Indexes and find out if there are missing indexes or unused indexes. Below link would get you started on this.

    http://www.mssqltips.com/sqlservertip/1545/deeper-insight-into-unused-indexes-for-sql-server/  

    Thursday, July 04, 2013 10:44 PM

All replies

  • Hallo Huuh,

    sorry if my post may disgust you - but...

    You've hired in an IT-company as a dba and ask for "basics".
    Are you kidding or do you want to know how much fun we have in this forum :)

    Get back serious and get started.

    First of all you need to get an overwiew of the status of SQL Server itself. Therefore it's interesting to know all about the hardware. This will be the first step INTO getting familiar with "the system".

    If you know the hardware parameters you have to have a look to the setup of the tempdb.
    The tempdb is the workbench of sql server. A very "rough" basic:

    If you have less than 8 cores check whether you have at least 8 files:
    If you have more than 8 cores leave it to 8 files and check the contention of the tempdb by using fn_virtualfilestats() and increase the number of files by 4 for each step... (very rough recommendation!!!!)

    Next step should be the analysis of the waiting stats of your system. If you google wait stats you'll get hundreds of thousands of articles concerning this issue. To get in details concerning each wait stat will explode my answer to you.

    ok. - now after checking all above the next step is your database itself.

    Sigh... - where to start...

    hmm - check the disk layout for your database.
    - is data and log separated?
    - is your database using multiple filegroups
    - are you using partitioning
    - how large is your database
    - how many people (sessions) are using the database
    - what is the workload for the databases.
    - is the blocksize of the underlying disks (if not SAN) >= 64 KB and is the storage dedicated to database activity?

    next step is IN the database

    - what is the fragmentation of existing indexes
    - what is the usage of indexes
    - what is the operational stats of indexes
    - what is ...

    I can ask hundreds of questions. Long story short... - nobody can give you a serious answer where to start if nobody nows WHAT is the bottleneck of your database.

    If I get back to the first sentence of your post I'm really afraid on the one hand - on the other hand I know why I don't have to have sorrows about my future income as a database professional :)

    Sorry for the last sentence - SCNR :)

    Best would be a basic seminar for getting started - this should be the basic platform of becoming a good dba.
    As you can see from my "question and suggestion" there is no "best practise" because we have hundreds of separate impacts which will affect your database(s)

    BTW: Uri has given you a pretty good start to investigations in your first thread. I would recommend NOT to start an new thread for the same issue!

    Best, Uwe


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Thursday, July 04, 2013 8:00 PM
  • Below are only pointers to get you started and the question can have a lot of different answers:-

    1. Check whether all databases are performing slow or just this one as for former, that seems to be more of a server issue than a single database performance issue.

    2. Check for the database size and also all table sizes. If any of the table is in xx GB (double digit) then you would want to setup partitioning for that table or if partitioning seems complex then atleast have two filegroup and have the data under separate files and the data that is only used for OLAP purposes, put them under READ-ONLY format.

    3. Check for Long Running Queries in your database and setup alerts for the same so that you are notified when the query is executing from let's say last 60min.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2f10b92f-6a13-4c1d-a08b-072ae8c35f0d/how-to-find-top-long-running-queries

    4. Check for Blocking and Deadlock in your database. Below is the link to help you get started, setting alerts.

    http://www.practicalsqldba.com/2012/07/sql-server-instant-deadlock-alert-using.html

    http://www.practicalsqldba.com/2012/07/sql-server-instant-blocking-alert-using.html

    This will make sure that you are notified on blocking and deadlock. If you notice that happening very often then you will need to work on the query and read the execution plan for the same to tune the query as well.

    Below link will give you the basics on execution plan if you are not aware of it.

    https://www.simple-talk.com/sql/performance/execution-plan-basics/

    5. Check for Indexes and find out if there are missing indexes or unused indexes. Below link would get you started on this.

    http://www.mssqltips.com/sqlservertip/1545/deeper-insight-into-unused-indexes-for-sql-server/  

    Thursday, July 04, 2013 10:44 PM
  • Asking the right question is key to tuning . Is it a performance, efficiency or capacity problem?This influences your approach. Read more

    http://www.sqlserver-dba.com/2013/02/sql-performance-tuning-asking-the-right-question.html

    Here is a general approach - that will save you lots of time , and give you flexibility in identifying bottlenecks

    http://www.sqlserver-dba.com/2013/05/performance-tuning-save-hundreds-of-hours.html


    Jack Vamvas sqlserver-dba.com

    Friday, July 05, 2013 5:44 AM
  • I am new Hire in an IT company,

    i am asked to improve the performance of one of the databses .

    i am not too sure where to start from.


    k

    Ask the question what type of an issue that they are facing , ensure you have reindex/Update statistics scheculed for the databse (most of the query slowness improves but other thing also exists).

    based on the issue, the white papaer can help for troubleshooting-

    Troubleshooting Performance Problems in SQL Server
    http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

     


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Sunday, July 07, 2013 9:59 AM