http://www.sqlservercentral.com/Content tagged T-SQL, SQL Server 7, 2000, Performance Tuning and Scaling 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/New Author! Deadlocks aren't the most common problem but they happen they can really degrade performance. Shiv works through how to nail down what is causing the deadlock. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/2007/04/06http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/New author Jambu Krishnamurthy brings us a look at table variables and how they differ from temporary tables in SQL Server 2000. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2691/2006/11/21http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2691/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/Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/2006/03/31http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/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/Performance tuning is an ongoing battle in SQL Server, but having a little knowledge up front when designing an application can greatly reduce the efforts. Do you know which performs better: stored procedures or triggers? There aren't many places where the two are interchangeable, but knowing the impacts of each might change the way you build an application. Read about this analysis by Vijaya Kumar. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/2006/03/31http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/storedprocedurevstriggers/1449/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/New Author! Deadlocks aren't the most common problem but they happen they can really degrade performance. Shiv works through how to nail down what is causing the deadlock. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/2007/04/06http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/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/As Chris points out, in most applications these days you end up having to go with optimistic locking, which presents a few challenges. Chris works through the list of options. If you're building web/disconnected apps and need anything besides last update wins, this one is for you. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/dealingwithchangingdata/1233/2003/12/19http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/dealingwithchangingdata/1233/Kumar discusses the differences between scale up and scale out, then does a very good walk through of how to build a scale out solution. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/scalingout/1209/2003/12/03http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/scalingout/1209/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/New author! Mike Pearson discusses how auto close was at the root of a performance problem he was troubleshooting, how you can check your servers easily, and discusses why both auto close and auto shrink are bad ideas in a production environment. http://www.sqlservercentral.com/articles/Administering/autocloseandautoshrinkjustdont/984/2003/05/05http://www.sqlservercentral.com/articles/Administering/autocloseandautoshrinkjustdont/984/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/