Compartir a través de

Performance tips...

  • Artículo

I recently moved to the Central Performance Team in the SQL Server product group. My group is responsible for box-wide performance, benchmark and scalability. Given my new role I will be posting more of performance tips and techniques. I will be covering the following topics:

 

  • Query performance tips
  • Engine features that improve performance
  • Troubleshooting performance problems using new SQL Server 2005 features
  • Differences between SQL Server 2000 and SQL Server 2005 with respect to certain features or behavior changes that might affect performance

Please comment to this post if you would like some specific topics to be covered.

 

Thanks

Umachandar

Comments

  • Anonymous
    February 17, 2006
    Would like to see tips for improving performance of snapshot isolation level transactions

  • Anonymous
    February 27, 2006
    If you guys can provide an TOOL (like FxCop) that can scan the Server, Code-base and then compare this with your "Suggested Configuration, Coding Practice" that will be another way to standardize the coding practices and "Server Configurations"

  • Anonymous
    March 01, 2006
    The comment has been removed

  • Anonymous
    March 01, 2006
    Snapshot isolation level transactions and read committed snapshot isolation relies of row versions stored in tempdb database to provide before image of the modified rows. The version store information as this is called is stored in the tempdb database. So such your tempdb database is a primary bottleneck and you need to carefully consider the placement of the tempdb database files, number of tempdb database files, RAID configuration of the volume containing the tempdb database files. This should be your first order of optimization.

    To learn more about the snapshot isolation, version store etc take a look at the Books Online topics below:

    Using Snapshot Isolation - http://msdn2.microsoft.com/en-us/library/tcbchxcb.aspx">http://msdn2.microsoft.com/en-us/library/tcbchxcb.aspx

    Using Row Versioning-based Isolation Levels - http://msdn2.microsoft.com/en-us/library(d=robot)/ms179599.aspx

    Understanding Row Versioning-Based Isolation Levels - http://msdn2.microsoft.com/en-us/library(d=robot)/ms189050.aspx

    sys.dm_tran_active_snapshot_database_transactions - http://msdn2.microsoft.com/en-us/library(d=robot)/ms180023.aspx

    Choosing Row Versioning-based Isolation Levels- http://msdn2.microsoft.com/en-us/library/ms188277.aspx

    Hope this helps.

    --
    Umachandar

  • Anonymous
    March 10, 2006
    This may be to open ended a question but one thing that concerns me is in SQL 2005 how am i going to identify performance issues related to CLR assemblies?

  • Anonymous
    March 17, 2006
    The comment has been removed

  • Anonymous
    March 22, 2006
    The comment has been removed

  • Anonymous
    March 22, 2006
    The comment has been removed

  • Anonymous
    May 14, 2006
    Need to know if there will be a degradation in performance when using cross join - convert columns to rows. I do not know the number of rows that will be created.

    Thanks, Geetha

  • Anonymous
    May 17, 2006
    Cross join performance is directly proportional to the number of rows that are produced. Since this is essentially a cartesian product of two sets of data, the larger sets will take longer to generate.

    Have you looked at the new PIVOT/UNPIVOT operators in SQL Server 2005 which will help do the column to row conversion and vice versa? These new operators have limitations so if you hit those you can use the GROUP BY/CASE approach for pivot and UNION ALL approach for unpivot (or cross join with numbers table for example).

    --
    Umachandar

  • Anonymous
    June 27, 2006
    I AM USING VIEWS WITH ORDER BY CLAUSE WHICH SEEMS TO BE NOT WORKING. WHAT I GOT WAS UNSORTED VIEW. IT IS SORTING BASED ON ID IT SEEMS

  • Anonymous
    June 28, 2006
    The only way to guarantee that rows from a query are returned in a particular order is to include an ORDER BY clause in the outer most SELECT statement. The use of TOP 100 PERCENT in view might have worked in SQL Server 2000 but there are cases where it fails there too and it was never guaranteed to behave that way.

    For more details on the ordering guarantees, see the post below:

    http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

    For more details on the change in SQL Server 2005 for ORDER BY in a view, see Books Online topic below:

    http://msdn2.microsoft.com/en-us/library/ms143179.aspx

    --
    Umachandar

  • Anonymous
    July 02, 2006
    From bol I see a major architecture change in indexes, root & node pages now store all index fields. Previously this was only on leaf nodes. Now for leaf nodes use INCLUDE. Sould we then expect in a migration an increase in size on these multicolumn indexes. Exactly how did (SQL 2000) clustered indexes (non unique) work when searching for primary index. (I wouldn't expect scans, have never figured out). How do the are the new (2005) indexes navigated when using multi-columns, dose the order and selectivenes matter ?

    I would, need and would very much like much more detail on this matter.

  • Anonymous
    February 02, 2007
    PingBack from http://chaespot.com/mssql/2007/02/02/to-the-central-performance-team-in-the/

  • Anonymous
    March 06, 2009
    Thank you for the article. It is very helpful and has lots of good tips!