none
What are the to increase performance of SQL database?

Answers

  • Most important tips are:
    • Stop Waiting Around. When SQL not running, the reason for the wait is recorded by SQL Server, examining these waits can improve performance. These waits can be decoded by using Tom Davidson’s paper
    • Locate I/O Bottlenecks. I/O is often the primary reason for slow performance. You can examine IO via wait stats, DMF sys.dm_io_virtual_file_stats(), and disk related Perfmon counters. Causes include slow disks, disk placement, badly written SQL, and lack of good indexes
    • Root Out Problem Queries. The DMV sys.dm_exec_query_stats aggregates query metrics (duration, time on CPU, waiting, reads, writes, executions etc) at individual SQL statement level – great for identifying longest running SQL. Can then optimise these
    • Plan To Reuse. Change dynamic SQL to use stored procedures, for plan reuse, else as transactions increase you can get bad performance
    • Monitor Index Usage. The DMF sys.dm_db_index_operational_stats(), details what has been used, how used, scans, singletons, inserts, deletes, updates, latches and locking
    • Separate Data and Log Files. Important for both DAS but also for SAN. Separate random access (data) from sequential access (log)
    • Use Separate Staging Databases. Can then use a simple recovery model, which speeds imports, and uses less CPUs, IO, and memory
    • Pay Attention to Log Files. Log growth can be expensive, better to ensure you have the required free space. Also ensure you don’t have too many virtual log files (VLFs)
    • Minimize tempdb Contention. tempdb can be used by all the other databases on the server, and it is often the most used database
    • Change the MAX Memory Limit. Remember to leave at least 1 or 2GB of memory for other processes. Also consider the impact of multiple instances

    More :

    http://www.mssqltips.com/sql-server-tip-category/9/performance-tuning/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    • Edited by Ahsan KabirMVP Saturday, September 07, 2013 3:25 PM
    • Marked as answer by Caladiya Saturday, September 07, 2013 3:27 PM
    Saturday, September 07, 2013 3:20 PM

All replies

  • Most important tips are:
    • Stop Waiting Around. When SQL not running, the reason for the wait is recorded by SQL Server, examining these waits can improve performance. These waits can be decoded by using Tom Davidson’s paper
    • Locate I/O Bottlenecks. I/O is often the primary reason for slow performance. You can examine IO via wait stats, DMF sys.dm_io_virtual_file_stats(), and disk related Perfmon counters. Causes include slow disks, disk placement, badly written SQL, and lack of good indexes
    • Root Out Problem Queries. The DMV sys.dm_exec_query_stats aggregates query metrics (duration, time on CPU, waiting, reads, writes, executions etc) at individual SQL statement level – great for identifying longest running SQL. Can then optimise these
    • Plan To Reuse. Change dynamic SQL to use stored procedures, for plan reuse, else as transactions increase you can get bad performance
    • Monitor Index Usage. The DMF sys.dm_db_index_operational_stats(), details what has been used, how used, scans, singletons, inserts, deletes, updates, latches and locking
    • Separate Data and Log Files. Important for both DAS but also for SAN. Separate random access (data) from sequential access (log)
    • Use Separate Staging Databases. Can then use a simple recovery model, which speeds imports, and uses less CPUs, IO, and memory
    • Pay Attention to Log Files. Log growth can be expensive, better to ensure you have the required free space. Also ensure you don’t have too many virtual log files (VLFs)
    • Minimize tempdb Contention. tempdb can be used by all the other databases on the server, and it is often the most used database
    • Change the MAX Memory Limit. Remember to leave at least 1 or 2GB of memory for other processes. Also consider the impact of multiple instances

    More :

    http://www.mssqltips.com/sql-server-tip-category/9/performance-tuning/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    • Edited by Ahsan KabirMVP Saturday, September 07, 2013 3:25 PM
    • Marked as answer by Caladiya Saturday, September 07, 2013 3:27 PM
    Saturday, September 07, 2013 3:20 PM
  • Optimization is related to many things such as design, indexing, set based programming and so on. Please see this thread:

    Optimization aides for legacy databases

    Also this link:

    Best Practices , Design and Development guidelines for Microsoft SQL Server


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Saturday, September 07, 2013 3:21 PM