http://www.sqlservercentral.com/Articles tagged Administering, Performance Tuning and Scaling posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)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/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/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/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/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/Another new author! Gheorge shares some ideas about importing event logs and using OLAP to analyze the results. Not a bad idea at all. How many of use OLAP as often as we should? Read the article, see if it's something you want to try - and let Gheorge know what you think! http://www.sqlservercentral.com/articles/Administering/importingandanalyzingeventlogs/997/2003/05/28http://www.sqlservercentral.com/articles/Administering/importingandanalyzingeventlogs/997/Seagate has a white paper out that discusses how faster (15k rpm) drives can lead to lower cost of ownership. Interesting, and worth looking into. http://www.sqlservercentral.com/redirect/articles/983/2003/05/08http://www.sqlservercentral.com/redirect/articles/983/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/Joseph recently had to upgrade a server from 4g to 8g of memory. Read how he did it and learn about some interesting aspects of AWE. For example, if you have 6.5g configured for use by SQL, how will task manager show used by sqlserver.exe? http://www.sqlservercentral.com/articles/Administering/aweadventures/960/2003/04/16http://www.sqlservercentral.com/articles/Administering/aweadventures/960/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 article by new contributing member Bob Musser shows you how to reduce the amount of scans that SQL Server Agent does against databases. Not for the faint of heart. http://www.sqlservercentral.com/articles/Administering/squeezingwastedfullscansoutofsqlserveragent/914/2003/02/13http://www.sqlservercentral.com/articles/Administering/squeezingwastedfullscansoutofsqlserveragent/914/This article by new columnist Cathan Kirkwood shows you how to find wait locks and eliminate them. http://www.sqlservercentral.com/articles/Administering/wait_types/856/2002/11/21http://www.sqlservercentral.com/articles/Administering/wait_types/856/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/Get a sneak peak of some of the sections from Start to Finish Guide to SQL Server Performance Monitor. Find out what counters to watch and what their thresholds are. http://www.sqlservercentral.com/articles/Administering/perfmonexcerpt/757/2002/08/06http://www.sqlservercentral.com/articles/Administering/perfmonexcerpt/757/Middle tier applications often use a single database management system (DBMS) to store data, which can expose scaling limitations as the number of user requests increases. Caching, a technique used to increase application performance by copying data and then using the copied data in place of the original data, can dramatically increase the throughput (the number of application requests serviceable per unit time) and scalability of middle tier applications. http://www.sqlservercentral.com/redirect/articles/619/2002/03/08http://www.sqlservercentral.com/redirect/articles/619/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/This white paper outlines the following: -The steps to configure Log Shipping between two or more servers that are running SQL Server 2000 Enterprise Edition. -The steps to configure Log Shipping between Microsoft SQL Server 7.0 Service Pack 2 (SP2), or later, and Microsoft SQL Server 2000 Enterprise Edition. -A brief comparison between Log Shipping and the other high availability-solutions that SQL Server provides. http://www.sqlservercentral.com/redirect/articles/577/2002/01/25http://www.sqlservercentral.com/redirect/articles/577/This white paper is a joint effort by Microsoft and Dell to demonstrate the scalability of SQL Server 2000 and Dell hardware. http://www.sqlservercentral.com/redirect/articles/576/2002/01/10http://www.sqlservercentral.com/redirect/articles/576/This article covers some of the common issues and questions when you cluster SQL Server 2000 and Windows 2000. http://www.sqlservercentral.com/articles/Administering/commonclusterproblems/470/2001/10/11http://www.sqlservercentral.com/articles/Administering/commonclusterproblems/470/In an online transaction processing (OLTP) environment, the connection affinity mask option may provide performance enhancement in high-end, enterprise-level SQL Server environments that are running on computers with 16 or more CPUs. In particular, this option is useful when there are a significant number of network interactions (more than 10,000 per second) between the middle-tier application servers and the back-end SQL Server system. http://www.sqlservercentral.com/redirect/articles/382/2001/08/28http://www.sqlservercentral.com/redirect/articles/382/