http://www.sqlservercentral.com/Content tagged Performance Tuning and Scaling posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)Building high performance applications with SQL Server can be a challenge if you do not understand how the query processor works and how the server uses indexes. SQL Server expert Randy Dyess brings a look at how bookmark lookups are used when satisfying a query and how this can impact the performance of your query. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bookmarklookups/1899/2008/07/18http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bookmarklookups/1899/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/Or should I place my database files on SAN or directly-attached storage? This is a frequently asked question. It comes up repeatedly in public newsgroups, email discussion lists, and private meetings with customers who are concerned with database performance.http://www.sqlservercentral.com/redirect/articles/62055/2008/02/05http://www.sqlservercentral.com/redirect/articles/62055/Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/2007/12/17http://www.sqlservercentral.com/articles/Performance+Tuning/2764/Find pointers to optimize tempdb performance in SQL Server by striping and splitting across multiple files in SQL Server 2005. http://www.sqlservercentral.com/redirect/articles/61411/2007/11/07http://www.sqlservercentral.com/redirect/articles/61411/Building high performance applications with SQL Server can be a challenge if you do not understand how the query processor works and how the server uses indexes. SQL Server expert Randy Dyess brings a look at how bookmark lookups are used when satisfying a query and how this can impact the performance of your query. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bookmarklookups/1899/2008/07/18http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bookmarklookups/1899/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/Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/2007/12/17http://www.sqlservercentral.com/articles/Performance+Tuning/2764/A series of tips on optimizing XML in your SQL Server databases. http://www.sqlservercentral.com/redirect/articles/2869/2007/02/21http://www.sqlservercentral.com/redirect/articles/2869/Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system. http://www.sqlservercentral.com/articles/Performance+Tuning/2764/2007/12/17http://www.sqlservercentral.com/articles/Performance+Tuning/2764/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/Gathering performance data on your servers can be a cumbersome task without paying for an expensive tool. New author Jambu Krishnamurthy brings us a short tutorial on how you can gather this data from Perfmon and import it into SQL Server for reporting. http://www.sqlservercentral.com/articles/Performance+Tuning/2677/2006/10/30http://www.sqlservercentral.com/articles/Performance+Tuning/2677/How do your indexes perform under different fragmentation levels in SQL 2000? Joe Doherty brings us part 1 of his look at this topic with an examination of how your data is stored and a detailed analysis of DBCC SHOWCONTIG. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2648/2006/10/23http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2648/Working with memory in SQL Server and tuning your server for optimum performance gets more complex all the time, especially with the availability of 64-bit platforms. New author Paul Mu brings us a short guide to using the /3GB and PAE switches along with their implications for your server. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2644/2006/10/16http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2644/Dealing with text, image, and other large object data types can be confusing for many people, in all aspects of working with them. However one place that is important is performance. SQL Server MVP Simon Sabin brings us a few thoughts on improving performance with these columns and the Text in Row setting. http://www.sqlservercentral.com/articles/Performance+Tuning/2639/2006/10/04http://www.sqlservercentral.com/articles/Performance+Tuning/2639/Performance Tuning can be as much an art as a science when working with SQL Server, but there are many fine techniques that can help you get started. New author Michael Morin brings a great new technique helping you get started on determining where the heavy I/O users are in your database. http://www.sqlservercentral.com/articles/Performance+Tuning/2573/2006/08/30http://www.sqlservercentral.com/articles/Performance+Tuning/2573/Stored procedures are a great way to encapsulate code and reuse it in many places in your application. But when you have a performance issue, how do you know which part of the stored procedure is giving you problems? Steve Jones takes a look at a few techniques for diving into stored procedures and identifying problem areas. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/performancetuningstoredprocedures/2505/2006/07/20http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/performancetuningstoredprocedures/2505/The second in a series from Adindam Banerjee on server performance and capacity planning. This time Arindam takes a look at the memory requirements for SQL Server. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/analyzingmemoryrequirementsforsqlserver/2498/2006/07/17http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/analyzingmemoryrequirementsforsqlserver/2498/Performance tuning often seems like it can be more of an art than a science. However there are a number of fundamentals that can help you tune most of the queries that you will write or have issues with their performance. Don Peterson brings us a look at how he tuned a query in the real world to avoid a conversion that can cause a query to run slower. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bewareofsearchargumentsargdatatypes/2485/2006/07/13http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/bewareofsearchargumentsargdatatypes/2485/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/This process uses ITWIZ.EXE the command line version of the Index Tuning Wizard. SQL trace file information is stored in a table on production then transferred to a non-production server. ITWIZ is then run against the transferred trace data resulting in a UNICODE SQL script. This script has index creation and deletion information as well as script parameters in a remarks section.http://www.sqlservercentral.com/redirect/articles/2208/2005/12/29http://www.sqlservercentral.com/redirect/articles/2208/This article is the third in a series of four where I share my findings on baselining, monitoring, stress testing and performance tuning. It builds on the foundations of my previous articles on baselining and monitoring.http://www.sqlservercentral.com/redirect/articles/2209/2005/12/28http://www.sqlservercentral.com/redirect/articles/2209/Analyzing and detecting performance problems is a big part of a DBA's job and also one of the more difficult ones to learn. New author Terry Troisi brings us a few things that you can use the next time your server spikes to 100% CPU. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/detectingperformanceissueswithsysprocesses/2180/2005/12/26http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/detectingperformanceissueswithsysprocesses/2180/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/Fragmentation in SQL Server is a huge debate. Does it matter? How much is too much? What should you do? In any case, new author Nicholas Cain has put together a system that allows him to keep track of the levels of fragmentation as well as defragment those tables when he feels they are getting too spread apart. http://www.sqlservercentral.com/articles/Administering/checkingyourdatabasefragmentationlevel/1373/2005/11/18http://www.sqlservercentral.com/articles/Administering/checkingyourdatabasefragmentationlevel/1373/What counters should you monitor to baseline your servers? Which ones for checking performance? It's an interesting question and one that always leads Steve Jones to a wide range of sources in print and on the web. Steve compiled his own list and finally has put some words around the list to give some justification of why they are chosen. Read on and see how this compares to your list (you do have a list don't you?). http://www.sqlservercentral.com/articles/Administering/performancemonitoringbasiccounters/1348/2005/11/11http://www.sqlservercentral.com/articles/Administering/performancemonitoringbasiccounters/1348/Index Creation Guidelines for SQL Server can be pretty sparse. Usually there are a couple, clustered index for ranges, nonclustered, etc. Leo Peysakhovich has taken some time to write down his guidelines based on his experience for creating indexes and the rational for doing so. He's also taken a few minutes to look at which indexes NOT to create, something that might be worth knowing.. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/indexcreationguidelines/1389/2005/10/21http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/indexcreationguidelines/1389/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/