Here they are, the 10 most popular SQL Server tips of 2007. From learning the differences between a clustered and non-clustered index and how to optimize SQL Server disk configuration, to routine tasks such as updating table statistics and troubleshooting performance bottlenecks, these are the tips that peaked your interest in 2007.
#1 -Examples of SQL Server stored procedures and parameters
How can SQL Server stored procedures be helpful if you don't know they exist? Database architect Denny Cherry shares 18 examples of handy stored procedures and their parameters that have gone undocumented by Microsoft.
#2 -Clustered and non-clustered indexes in SQL Server
Rules for using a clustered index vs. a non-clustered index are tough to decipher. In this tip, you'll see the fundamentals to consider when making your choice, along with some best practice techniquesfor SQL Server indexes. Get an overview of tradeoffs and proper index implementation, as well as other areas involved in your decision, including index partitioning and storage arrays.
#3 -Maintain DTS packages in SQL Server 2005
What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? In this tip, you'll see how to edit, maintain and even develop existing DTS packages using the SQL Server DTS Designer Tool. SQL Server expert Joe Toscano also discusses migration challenges and using the SQL Server 2005 Upgrade Adviser.
#4 -Optimize disk configuration in SQL Server
Proper disk configuration can result in a lifetime of high performance for SQL Server databases. Go beyond storage capacity requirements and consider drive performance. Database architect Denny Cherry outlines best practices for designing your disk subsystem, including choosing the right disk array.
#5 -Compatibility of SQL Server 2005 and 2000 coexisting
So, you've decided to upgrade your databases to SQL Server 2005.
Here are some issues that may arise when installing a SQL Server 2005 instance on your SQL Server 2000 machine. Also discussed are options for interaction between the two versions, such as: linked servers, multi-server administration and log shipping.
#6 -Update SQL Server table statistics for performance kick
Here's what you need to know about using auto and manual update commands and avoiding false statistics -- two of many techniques that will fine-tune SQL Server statistics and boost performance and accuracy.
#7 -Identify performance bottlenecks in SQL Server
Identifying and resolving bottlenecks is key to a high-performing SQL Server. Types of bottlenecks include memory, disk and CPU bottlenecks. SQL Server MVP Hilary Cotter explains how to locate these log jams using System Monitor, SQL Profiler and Dynamic Management Views (DMVs).
#8 -Optimize SAN setup for improved SQL Server performance
Maybe you're already using Storage Area Networks (SANs) in your SQL Server environment, but is it the optimal setup? Consider these pointers for a disk system design that yields a high performing and scalable SQL Server environment.
#9 -Managing permissions in SQL Server Reporting Services
SQL Server Reporting Services (SSRS) offers ease when creating and deploying SQL Server reports for your entire organization. But when it comes to SSRS security, there's some work to be done on your part. Learn how to lock down your SSRS environment with role and task permissions.
#10 -Managing identity columns with replication in SQL Server
Managing identity columns with replicated tables in a SQL Server 2005 database requires some TLC. In these scenarios, see how to replicate tables with identity columns and also get options to manage identity seeds for replicated tables. You'll see that you don't have to ditch identity columns to use replication, just handle them with care.
Check out the Top 10 SQL Server tips of 2006.
