http://www.sqlservercentral.com/Content tagged T-SQL, Performance Tuning and Scaling, Performance Tuning posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/2008/05/30http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/2008/05/30http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/Joe discusses why he prefers command line tools over GUI, then launches into a how-to about querying sysperfinfo to resolve performance issues. Couple nice sample queries included! http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/2006/04/21http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/One thing that often amazes me is that many SQL Server developers do not actually measure the performance of their queries. Many of them thought that you needed external tools, more or less complicated, to run against your server, and they did not have the time or inclination to learn and try these. This article will describe a couple of much easier methods of measuring performance of queries. http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/2005/12/23http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/How many of you use Execution Plans to tune your queries? Do you understand the impact of different indexes? Mr. Vijayakumar looks at his experiments with different types of indexes and their effects on the execution plan use. A great article for those of you that want to learn more about how you can tune your server for better performance. http://www.sqlservercentral.com/articles/Administering/executionplans/1345/2005/10/07http://www.sqlservercentral.com/articles/Administering/executionplans/1345/One of the biggest performance gains built into SQL Server is the stored procedure. In this article by Brian Kelley, he shows you how to fully utilize, debug and monitor the caching of such objects. http://www.sqlservercentral.com/articles/Administering/procedurecache/591/2004/11/19http://www.sqlservercentral.com/articles/Administering/procedurecache/591/SQL Server showplan is a great tool for analyzing queries and determining whether or not more performance tuning needs to be done. However working with showplan can be confusing and it takes some practice to learn the ins and outs of what is occurring. Joseph Sack looks at showplan and seven of the main things that he is aware of when looking at the results. If you've rarely or never used showplan, this is a great article to get started in learning how an experienced DBA views the output. http://www.sqlservercentral.com/articles/Performance+Tuning/sevenshowplanredflags/1425/2004/07/15http://www.sqlservercentral.com/articles/Performance+Tuning/sevenshowplanredflags/1425/How can you tell if your code if performing well? Do you know what you want to look for when starting to analyze code? Here's an article from Leo Peysakhovich that looks at some of the things that you can use at a high level to improve the performance of your code. http://www.sqlservercentral.com/articles/Performance+Tuning/overviewofperformance/1350/2004/05/11http://www.sqlservercentral.com/articles/Performance+Tuning/overviewofperformance/1350/How many of you use Execution Plans to tune your queries? Do you understand the impact of different indexes? Mr. Vijayakumar looks at his experiments with different types of indexes and their effects on the execution plan use. A great article for those of you that want to learn more about how you can tune your server for better performance. http://www.sqlservercentral.com/articles/Administering/executionplans/1345/2005/10/07http://www.sqlservercentral.com/articles/Administering/executionplans/1345/One thing that often amazes me is that many SQL Server developers do not actually measure the performance of their queries. Many of them thought that you needed external tools, more or less complicated, to run against your server, and they did not have the time or inclination to learn and try these. This article will describe a couple of much easier methods of measuring performance of queries. http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/2005/12/23http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/New Author! Jeff had some questions about fill factors - but no answers! He did some work and came up with a set of recommendations for how and when to set your fill factors to something other than the default. http://www.sqlservercentral.com/articles/Performance+Tuning/amethodologyfordeterminingfillfactors/1195/2003/11/20http://www.sqlservercentral.com/articles/Performance+Tuning/amethodologyfordeterminingfillfactors/1195/New Author! The first of two articles this week on fill factors, this article looks at the impact fill factor can have on performance and shows you the key tools and counters you use to assess the impact of changes to fill factor. http://www.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/2003/11/19http://www.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/Randy starts a multiple part series on tuning queries. We've asked Randy to really start from scratch and walk through the process. This week he takes a look at viewing query plans in Query Analyzer. http://www.sqlservercentral.com/articles/Performance+Tuning/displayingexecutionplans/1103/2003/08/19http://www.sqlservercentral.com/articles/Performance+Tuning/displayingexecutionplans/1103/Vicktor has a bunch of scripts he uses to monitor performance, he explains why he built them and how to use them. Good coverage of the different reasons for slow performance. http://www.sqlservercentral.com/articles/Performance+Tuning/monitoringperformance/1007/2003/06/09http://www.sqlservercentral.com/articles/Performance+Tuning/monitoringperformance/1007/Joe discusses why he prefers command line tools over GUI, then launches into a how-to about querying sysperfinfo to resolve performance issues. Couple nice sample queries included! http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/2006/04/21http://www.sqlservercentral.com/articles/Performance+Tuning/troubleshootingsqlserverwiththesysperfinfotable/987/A monster book at 980 pages, it's written in 'how-to' format and has a ton of good material. Andy gave it the once over for us and reports back - see what he thinks! http://www.sqlservercentral.com/articles/Installation/reviewofsql2000fastanswers/959/2003/04/18http://www.sqlservercentral.com/articles/Installation/reviewofsql2000fastanswers/959/It is always better to be proactive than reactive, when it comes to identifying and eliminating SQL Server performance issues. This article shows you how to analyze the data gathered by Profiler, to identify the performance issues. This article also provides four generic stored procedures for analyzing Profiler output. http://www.sqlservercentral.com/redirect/articles/858/2003/04/01http://www.sqlservercentral.com/redirect/articles/858/This white paper discusses the basics of indexes, such as density and selectivity, effects of many versus few indexes, long versus short keys, covering indexes, and how the SQL Server optimizer chooses indexeshttp://www.sqlservercentral.com/articles/Performance+Tuning/938/2003/03/14http://www.sqlservercentral.com/articles/Performance+Tuning/938/In this article, James Travis covers a common performance topic of prefixing stored procedures sp_. Does it really slow down performance? http://www.sqlservercentral.com/articles/Performance+Tuning/sp_performance/850/2002/11/14http://www.sqlservercentral.com/articles/Performance+Tuning/sp_performance/850/During the process of performance tuning queries and stored procedures there comes a time when you will notice that the execution plan selected by SQL Server is not the best plan. On occasion, everything you try doesnt cause SQL Server to choose the best way to execute your code. These are the times when hints can improve performance. http://www.sqlservercentral.com/articles/Performance+Tuning/performanceaddinghints/795/2002/09/10http://www.sqlservercentral.com/articles/Performance+Tuning/performanceaddinghints/795/Bitwise operators can be challenging to manage at first. However, with practice and patience, and under the right conditions, these operators can provide remarkable performance improvements in production environments. This article will compare two methods of accomplishing the same output, one with a normalized model and the other with bitwise operators. http://www.sqlservercentral.com/articles/Performance+Tuning/usingbitmaskoperators/790/2002/09/05http://www.sqlservercentral.com/articles/Performance+Tuning/usingbitmaskoperators/790/This article briefly discusses SQL Server's data and procedure cache and shows you the common Transact-SQL statements/command and system tables that you can use to interact with the cache through Transact-SQL. http://www.sqlservercentral.com/articles/Administering/cache/766/2002/08/15http://www.sqlservercentral.com/articles/Administering/cache/766/The purpose of this article is to give you a working knowledge of how to view and understand query execution plans for SQL Server. This is part 1 in a series of articles that will walk you through understanding execution plans to help you improve your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/understandingexecutionplans/739/2002/07/26http://www.sqlservercentral.com/articles/Performance+Tuning/understandingexecutionplans/739/As you advance in your skills as a Transact-SQL developer or SQL Server database administrator there will come a time when you need to override SQL Server's locking scheme and force a particular range of locks on a table. This article by Randy Dyess shows you how to optimize your queries using table hints. http://www.sqlservercentral.com/articles/Performance+Tuning/tablehints/667/2002/04/15http://www.sqlservercentral.com/articles/Performance+Tuning/tablehints/667/In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries. http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/2008/05/30http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/Architectural choices for data access affect performance, scalability, maintainability, and usability. This article focuses on the performance aspects of these choices by comparing relative performance of various data access techniques, including Microsoft® ADO.NET Command, DataReader, DataSet, and XML Reader in common application scenarios with a Microsoft SQL Server™ 2000 database.http://www.sqlservercentral.com/redirect/articles/620/2002/03/01http://www.sqlservercentral.com/redirect/articles/620/One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored procedures. A database getting thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database users. This article by Randy Dyess shows you some of the ways you can avoid stored procedure recompiles. http://www.sqlservercentral.com/articles/Performance+Tuning/ospr/666/2002/02/26http://www.sqlservercentral.com/articles/Performance+Tuning/ospr/666/Last week Andy started a discussion of the various ways you can reduce the number of round trips to the server. This week he continues by looking at a method he used recently to do client side caching of data to eliminate the round trip altogether. Gotta read it! http://www.sqlservercentral.com/articles/Performance+Tuning/reducingroundtripspart2/588/2002/02/06http://www.sqlservercentral.com/articles/Performance+Tuning/reducingroundtripspart2/588/One of the biggest performance gains built into SQL Server is the stored procedure. In this article by Brian Kelley, he shows you how to fully utilize, debug and monitor the caching of such objects. http://www.sqlservercentral.com/articles/Administering/procedurecache/591/2004/11/19http://www.sqlservercentral.com/articles/Administering/procedurecache/591/An alternative procedure for viewing locks based on sp_lock. http://www.sqlservercentral.com/articles/Performance+Tuning/lockview/297/2001/07/24http://www.sqlservercentral.com/articles/Performance+Tuning/lockview/297/