Performance tips…


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 (15)

  1. payyans says:

    Would like to see tips for improving performance of snapshot isolation level transactions

  2. PP says:

    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"

  3. sqletips says:

    We have a tool called Best Practices Analyzer for SQL Server that analyzes SQL code for invalid constructs, obsolete calls, incorrect configurations etc. You can download it at:

    http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

    Note that the SQLBPA component is used by the Upgrade Advisor in SQL Server 2005 to scan the code for compatibility issues, upgrade blockers etc. Currently, we only have a version that supports SQL Server 2000.



    Umachandar

  4. sqletips says:

    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

  5. ACALVETT says:

    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?

  6. furmangg says:

    Good blog… thanks.

    I’d like to know if there’s any way in SQL2005 to "raise the priority" of one query and/or "lower the priority" of another query. In particular, I’m thinking of a query I run on our production system… it’s a pretty expensive query, but it doesn’t need to finish quickly… I’d just like it to execute "in the background" so it doesn’t slow other stuff down. We’ve already set MAXDOP to 1 and that helped some. Any new features that might help there?

  7. sqletips says:

    SQL Server does not have any form of resource governor yet. There is a SET option called QUERY_GOVERNOR_COST_LIMIT but it is practically useless. And it prevents query from executing based on the cost rather than controlling resources. For now, the only way is to make sure you disable parallelism or set it to the optimum MAXDOP value and optimize your query so that the execution plans are simpler. Keeping statistics updated periodically and depending on the data modifications will help also. We are looking at the resource governor feature for a future version of SQL Server. Feel free to send feedback via MSDN Product Feedback Center.



    Umachandar

  8. sqletips says:

    There are few CLR related DMVs that you can use to monitor the server. You can find them at the Books Online topic below:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/57e79c45-ff47-4885-bdf5-aecf29891ab3.htm

    The sys.dm_clr_appdomains DMV for instance can be used to determine the cost/value of app domains created on the server. There are also other SQLOS DMVs which can provide information about SQLCLR.

    And there is a "CLR Execution" performance counter that you can monitor also.

    If you want more specific details you can ask in the SQLCLR Team blog at https://blogs.msdn.com/sqlclr/.



    Umachandar

  9. Geetha says:

    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

  10. sqletips says:

    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

  11. KANNAN says:

    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

  12. sqletips says:

    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

  13. jccondor says:

    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.

  14. Match says:

    Thank you for the article. It is very helpful and has lots of good tips!