locked
database design RRS feed

  • Question

  • Hello every once 

    I have migrated SQL server databases from SQL 2000 to Sql 2008

    so can you please suggest me some tips to improve the database performance 

    (what are the necessary steps i need to follow to improve performance )


    Kamal


    • Edited by Dasarikamal Wednesday, April 23, 2014 10:42 AM
    Friday, April 18, 2014 1:57 PM

Answers

  • Update statistics.. I would recommend you using Ola's great utility to maintain database,

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Identify bad executing queries and look at execution plan.. BTW, did you migrate to the better hardware? CPU, RAM, disks?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, April 18, 2014 2:45 PM
  • Change the compatibility level 100, Perform data purity_check ,rebuild all the indexes and update the stats 

    http://technet.microsoft.com/en-us/library/ms176064.aspx

    DATA_PURITY

    Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

    For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.

    If PHYSICAL_ONLY is specified, column-integrity checks are not performe

    --Prashanth


    Friday, April 18, 2014 2:48 PM

All replies

  • Update statistics.. I would recommend you using Ola's great utility to maintain database,

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Identify bad executing queries and look at execution plan.. BTW, did you migrate to the better hardware? CPU, RAM, disks?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, April 18, 2014 2:45 PM
  • Change the compatibility level 100, Perform data purity_check ,rebuild all the indexes and update the stats 

    http://technet.microsoft.com/en-us/library/ms176064.aspx

    DATA_PURITY

    Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

    For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.

    If PHYSICAL_ONLY is specified, column-integrity checks are not performe

    --Prashanth


    Friday, April 18, 2014 2:48 PM