Tutorial: Performance tuning SQL Server via queries, indexes and more

 WHAT YOU'LL FIND IN THIS TUTORIAL:

  [IMAGE] Query design and tuning
  [IMAGE] Configuring SQL Server memory
  [IMAGE] Index design and tuning
  [IMAGE] Disk space and SANs
  [IMAGE] Solving common SQL errors

  [IMAGE] Tricks to increase SQL Server query performance

Increase query performance in SQL Server by having a few tricks up your sleeve, such as avoiding system table locking by knowing when to use the CREATE TABLE vs. SELECT INTO command. Also, discover a stored procedure method that improves query response time, as opposed to linked server queries that can unexpectedly slow down system performance, along with tips on how to use subqueries as join partners to decrease query execution time.

  [IMAGE] Improve query performance with SQL Server 2005 execution plans

SQL Server 2005 allows you to feed a full execution plan to your query. Find out how to outsmart query optimizer and improve your SQL Server query performance with ...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

faster execution plans. One new tool – the plan guide – allows you to inject query hints without changing the original stored procedure code.

  [IMAGE] Simplify queries with SQL Server 2005 common table expressions (CTEs)

Common table expressions (CTEs) are a handy alternative to using derived tables and views for retrieving data and improving performance in SQL Server. With them, there's no need to repeat complex code because CTEs separate code into unique units. Not only that, they're also self-referencing within your query. Learn how to use CTEs to simplify queries in SQL Server 2005.

  [IMAGE] SQL Server queries with DMVs for examining bottlenecks

When searching for bottlenecks that may be causing performance problems in SQL Server 2005, your first step should be to look for memory bottlenecks, then disk and finally CPU. To assist you, these queries that incorporate Dynamic Management Views (DMVs) will let you dig deeper into your server's performance statistics and shed some light on many common SQL Server bottlenecks.

  [IMAGE] FAQ: SQL Server query errors and explanations

Queries in SQL Server have many daunting components, and exploring every option can slow down your server considerably. This collection of frequently asked questions will help you find the answers to common SQL Server query errors, including how to fix code errors, speed up queries, show the query navigation path and more.

  [IMAGE] SQL Server out of memory: Troubleshoot and avoid SQL memory problems

Ever get a SQL Server out of memory error message? If so, you know that most often this indicates a problem with the system, not simply a memory shortage. Find out the best practices to tune SQL Server and troubleshoot memory problems, including changing clustered indexes to non-clustered indexes. You'll also learn preferred methods for allocating memory to the Windows OS vs. SQL Server, along with how to track SQL Server memory with PerfMon.

  [IMAGE] Tuning SQL Server performance via memory and CPU processing

If you are planning to tune the hardware that supports your SQL Server system, make sure you examine memory allocation and CPU processing and how their configurations impact SQL Server performance. Also, learn how clustered indexes can drain SQL Server memory, along with information on what causes CPU bottlenecks.

  [IMAGE] Configuring SQL Server memory settings

While SQL Server has only a few simple memory settings, applying them is crucial for improved memory performance. In this tip, you'll find the best configuration recommendations for the best SQL Server memory settings, including optimal RAM amounts, how to enable AWE, the suggested number of gigabytes for maximum server memory and the differences between 32- and 64-bit platforms.

  [IMAGE] SQL Server memory configurations for procedure cache and buffer cache

To determine how much memory your SQL Server database uses for buffer cache and procedure cache depends many factors, such as the SQL version you're using, whether it's x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance. Find out how the your version of SQL Server configures memory for the proper amount of buffer and procedure cache.

  [IMAGE] Tuning SQL Server performance via disk arrays and disk partitioning

Tuning SQL Server performance via disk arrays and disk partitioning requires a great deal of your DBA's focus. But have you spent any time tuning the hardware that supports your SQL Server system? Are you using the optimal disk array configuration? Are the disk partitions aligned? Insure that your hardware's performance is in top shape, whether the system is a new setup or already in operation.

  [IMAGE] Error when modifying DTS package on a remote SQL Server 2000 workstation

If you are receiving a "Memory cannot be written" error message when modifying a DTS package on a remote workstation running SQL Server 2000, you could have either a corrupt .DLL file or a bad memory module. Our SQL Server experts examine the problem and offer their solutions here.

  [IMAGE] Best SQL Server indexing strategies

Using the appropriate indexes can make a world of difference in performance, but SQL Server supports only two index types for most data types: clustered and non-clustered. Find out how and why each type could end up being the most performance-enhancing SQL Server indexing strategy for your system.

  [IMAGE] SQL Server clustered index design for performance

The clustered index design in SQL Server is important for both your system's performance and the future maintenance of your SQL Server database. Find out why clustered indexes should be narrow, static, increase over time and how they use many-to-many tables. Also, learn how the partitioned table concept in SQL Server 2005 affects clustered indexes.

  [IMAGE] How to maintain SQL Server indexes for query optimization

Maintaining SQL Server indexes is an uncommon practice. Discover the best techniques for page splits, table partitions, statistics updates and other processes that will keep indexing running smoothly and your performance at a high level. You'll also learn why query optimizer might choose to ignore your non-clustered index and instead opt for a table scan.

  [IMAGE] Stored procedure to find fragmented indexes in SQL Server

SQL Server indexes can become fragmented over time because of data modifications, and these fragmented indexes can slow down application performance, especially in larger operations. This stored procedure will assist you in checking for these fragmented indexes in SQL Server.

  [IMAGE] SQL Server 2000 indexing Q&A to create and tune indexes

Indexing a SQL Server 2000 database is both an art and a science. You must determine the correct indexes based on your design and coding, but when you test your index design, you may find it doesn't improve system performance as well as you had hoped. In this collection of Q&As, discover how to solve the common problems and situational slowdowns that occur when creating or tuning indexes in SQL Server 2000.

  [IMAGE] Determining SQL Server database storage requirements

Calculating storage requirements for a SQL Server database application is an ongoing process that should be repeated consistently throughout the life of the database application, but the first time should be done during the development phase. Here are two methods for determining SQL Server database storage requirements.

  [IMAGE] Check SQL Server database and log file size with this stored procedure

Knowing the size of a SQL Server database is one of the many DBA responsibilities that you can accomplish easily with the stored procedure sp_SDS. Not only will sp_SDS determine "SQL Database Space," but it can also be used to monitor database growth, alert a DBA on data or log file growth, execute a transaction log backup and even provide a detailed breakdown at the file level so a DBA can then shrink files with the most empty space.

  [IMAGE] SQL Server tempdb best practices increase performance

Take your SQL Server tempdb's performance to new heights by making a few adjustments to the standard database settings. Discover the best practices for tempdb, including physical file settings, storage array configurations, statistics update options and how to index temporary tables.

  [IMAGE] Optimize disk configuration in SQL Server

Proper disk configuration can result in a lifetime of high performance for your SQL Server databases. Go beyond storage capacity requirements and consider drive performance. This tip outlines best practices for designing your disk subsystem in SQL Server, including choosing the right disk array, RAID level and drive file layout.

  [IMAGE] Can you shrink your SQL Server database to death?

In an effort to free up disk space, SQL Server DBAs often turn to shrinking their SQL Server database as a part of their maintenance plan. When doing so, the database often needs to grow again to accommodate the new data inserted into the database. However, what are the performance impacts of this growth? Take a look at what really happens when a database file grows as the result of a transaction, including test results involving autogrowth, and see if the shrink database task is really necessary.

  [IMAGE] Monitoring server disk space in SQL Server

Monitoring your SQL Server database files is an essential operation of all DBAs. Failure to do so can cause significant problems with SQL Server jobs and their performance. Avoid downtime, data loss and other inconveniences with the three methods described here to monitor auto-growths of database files.

  [IMAGE] Tutorial: Migrating to SANs from local SQL Server disk storage

Making the leap to storage area networks (SANs) will ultimately improve performance in your SQL Server environment. Although SAN administrators share responsibility, SQL DBAs must be involved from SAN setup through maintenance. This tutorial will equip you with everything you need to know about migrating to SANs from SQL Server disk storage, from concept to implementation. You'll get expert advice for configuring SANs, along with specific advice such as setting up SANs on a cluster and how to use SAN snapshots.

  [IMAGE] SQL Server errors, failures and other problems fixed from the trenches

Sometimes SQL Server errors, failures and other issues can't be solved from a distance. A key SQL Server error, whether lengthy or otherwise, can cause serious and unnecessary slowdown to your SQL Server's performance. Whether your problem is a failed cluster installation, a long-running stored procedure that causes a 3 a.m. phone call or a database in suspect mode, this tip has got you covered with a handbook for DBAs who work on the SQL Server frontline.

  [IMAGE] Solve SQL Server errors and more from the DBA trenches -- part 2

There are some errors that DBAs just cannot avoid, most of which will negatively impact your SQL Server and its performance. Instead of spending countless hours searching out a solution, check out this expert tip designed for fixing SQL Server errors and other problems, including primary filegroup out of space, failed server logins and trouble bringing a database online.

  [IMAGE] Error importing Excel data to SSIS in SQL Server 2005

If you are importing data to SSIS from an Excel file, you should note that the process would be easier with a csv file, a tab delimited file, or a '|' delimited file. However, if you're stuck with the Excel file, lining up data types in Excel and SSIS will help avoid any error messages that might occur.

  [IMAGE] Maintain large SQL Server database and resolve website 'Timeout Error'

If you have exceptionally large SQL Server databases, and if the extend size is too large, your database may be extending itself too often and forcing your queries to timeout. To prevent this, refer to these best practices for proper SQL Server database maintenance and insure that you steer clear of a website 'Timeout Error.'

  [IMAGE] SSIS package failure message

If your SSIS package in SQL Server 2005 is failing, there could be several issues at hand. However, if it is also displaying an error message stating that the "Data conversion failed while converting column A to column B," you might solve the problem by using a uni-code data type from the source.

  [IMAGE] Error message in SQL Server Query Analyzer

When trying to change the user name in Query Analyzer on SQL Server 2005, you might be receiving this error message: "Incorrect syntax near 'login'." Find out why using LOGIN in this situation is not the correct method.