Site sponsored by:
Try Idera’s new SQL admin toolset
<script language='javascript1.1' src="http://jventures.com/advertising/a2.aspx?ZoneID=4&Task=Get&Browser=NETSCAPE4&NoCache=True&PageID=34077&SiteID=1"></script> <noscript><a href="http://jventures.com/advertising/a.aspx?ZoneID=4&Task=Click&Mode=HTML&SiteID=1&PageID=34077" target="_blank"> <img src="http://jventures.com/advertising/a2.aspx?ZoneID=4&Task=Get&Mode=HTML&SiteID=1&PageID=34077" width="468" height="60" border="0" alt=""></a> </noscript>
Home
Articles
Forums
Tips
Quiz
FAQ's
Blogs
Software
Books
About Us
RSS Feeds
Sign in
|
Join
Article Topics
All Articles
Performance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
Developer
General DBA
ASP.NET / ADO.NET
Write for Us
Share you SQL Server knowledge with others and raise your profile in the community
More...
Latest FAQ's
ALTER TABLE SWITCH statement failed because the object '%.*ls' is not ...
ALTER TABLE SWITCH statement failed because column '%.*ls' at ordinal %d ...
ALTER TABLE SWITCH statement failed because table '%.*ls' has %d columns ...
SQL Server Reporting Server (SSRS) service is failing to start ...
More
SQL Server Performance Tuning Articles
Compare Dates
The following article shows how to compare dates in an iterative manner for a particular set of records in a single query
SQL Server 2008 MERGE Statement
This article introduces how the SQL Server 2008 MERGE statement can be used
Speeding up the Performance of Table Counts in SQL Server 2005
This article describes how the Object Catalog Views can be used to determine the number of rows in a table
Characterizing I/O Workload
This article is excerpted from the upcoming title SQL Server 2008 Administration by Rod Colledge and published by Manning Publications. This article describes how to assess the I/O load that applications can place upon a system and how to determine the resources you’ll need to handle it.
Resource Governor in SQL Server 2008
This article describes how Database Administrators can manage SQL Server workload and critical system resource consumption using Resource Governor in SQL Server 2008
System Data Collection Reports
In this article Greg Larsen details the rich reporting capabilities in the SQL Server 2008 Management Data Warehouse
Breaking Down Complex Execution Plans
In this article Grant Fritchey demonstrates how to break down a complex execution plan to identify performance bottlenecks
How SQL Cluster Resource DLLs work with Cluster Components to Improve the Failover Process
In this article Nirmal Sharma explains the SQL Server internal clustering process
SQL Server 2008’s Management Data Warehouse
This article looks at the new Management Data Warehouse (MDW) that is incorporated into SQL Server 2008. This warehouse is performance analysis and capacity planning tool for DBAs. This article will discuss the basic architecture of the MDW and how to set it up using a wizard.
Data Comparison with Data Dude
In this article Dinesh Priyankara shows how Visual Studio Team Edition for Database Professionals can be used to perfrom data comparison.
New Features in ASP.NET 3.5
In this article Joydip Kanjilal explains the new features that are available in ASP.NET 3.5.
How to Use System Monitor to Identify SQL Server Hardware Bottlenecks
In this article Brad McGehee describes how System Monitor can be used to identify potential hardware bottlenecks.
Monitoring Stored Procedure Performance with sys.dm_exec_cached_plans
This article will show you how to use the sys.dm_exec_cached_plans DMV to monitor the performance of stored procedures
Schema Comparison with Visual Studio Team Edition for Database Professionals
In this article Dinesh Priyankara describes how schema comparison can be performed using Visual Studio Team Edition for Database Professionals.
SSIS Usage of Checkpoint File
In this article Dinesh Priyankara explains how Checkpoint files can be used in a SSIS package so that the package can be restarted from the point of failure.
Query Execution Statistics
In this article Greg Larsen discusses how the Dynamic Management Views and Dynamic Management Functions in SQL Server 2005 can be used to gather performance information.
Measure TSQL Statement Performance
Every developer needs to ensure that each TSQL statement is optimized. This article will give you a few different ideas on how to identify slow running queries and provide you with some tips on monitor your query performance while you make iterative changes to each query to try and improve performance.
Pivot and UnPivot with SSIS
Pivot and UnPivot are key data transformation functions for SQL Server Integration Service (SSIS).
The “sys.dm_os_performance_counters” Dynamic Management View
SQL Server performance can be tracked and monitored by using performance counters. For SQL Server 2005 performance counters can be displayed by using the “sys.os_exec_performance_counters” Dynamic Management View (DMV).
Index related DMVs and DMFs - sys.dm_db_index_usage_stats
Examining statistics of indexes is useful for optimizing the performance of queries. Statistics help us determine the usage and worth of indexes - one simple method is using the index-related dynamic management view; sys.dm_db_index_usage_stats
Index related DMVs and DMFs
This article deals with some important index-related output columns that are returned by the function, dm_db_index_physical_stats which returns information on the size and fragmentation of tables and indexes.
Reduce Aggravating Aggregation: Improve the Performance of History or Status Tables
Optimize the performance of a database which includes history or status tables.
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
The differences between temporary tables and table variables and how they compare in terms of performance on SQL Server 2000 and 2005.
Online Indexing in SQL Server 2005
In SQL Server 2005, DBAs can create, rebuild, or drop indexes online. The index operations on the underlying table can be performed concurrently with update or query operations. This was not possible in previous versions of SQL Server.
Parallel Statistics Update
Create jobs that update statistics on the system in parallel
Making the Most Out of the SQL Server 2005 Performance Dashboard
The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005. It is a custom report (custom reports are a new feature of Service Pack 2) for Management Studio that gathers data from the many Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) available in SQL Server 2005. By Brad M. McGehee.
Benchmarking SQL Server 2005 Covering Indexes
The focus of this article is to find out what will happen when running a query, without any indexes, with non-clustered indexes, and with covering indexes. By Dinesh Asanka.
How to Correlate a SQL Server 2005 Profiler Trace With System Monitor Performance Counter Data
In this article, we take a look at how to collect both System Monitor and Profiler data, then correlate the two so you can see exactly how SQL Server activity is directly affecting server performance. By Brad M. McGehee.
An Introduction to Dynamic Management Views and Functions in SQL Server 2005
The purpose of this article is to introduce you to Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) at a high level; in later articles, I will drill down into how specific DMVs and DMFs can be used to help you performance tune your servers and databases. By Brad M. McGehee.
New Isolation Levels Available in SQL Server 2005
One of the more mysterious features of SQL Server is isolation levels. Whenever a statement is executed, or a data modification is made, it runs under the influence of an isolation level. Traditionally, SQL Server has supported four isolation levels. In SQL Server 2005, two new isolation levels are introduced. By Brad M. McGehee.
Using Plan Guides in SQL Server 2005
In SQL Server 2005, there is a new feature called Plan Guides that can help out in some cases where you discover poorly performing queries that you don't have direct control over. Essentially, a Plan Guide allows you to add or modify query hints to queries on the fly, just before they are executed. By Brad M. McGehee.
Beware: New Query Hints Added to SQL Server 2005
The focus of this article is that SQL Server 2005 has added four new query hints. So let's take a quick look at what they are and what problems they are supposed to correct. By Brad M. McGehee.
Adding Indexes to Persisted Computed Columns in SQL Server 2005
Previously, SQL Server supported the concept of computed columns. SQL Server 2005 introduces persisted computed columns, and the ability to index the computed column for those cases where this feature makes sense. By Brad M. McGehee.
Using Asynchronous Statistics Updating in SQL Server 2005
Most DBAs are familiar with the AUTO_UPDATE_STATISTICS database option. Under most conditions, this feature provides the Query Optimizer with up-to-date index and column statistics so that optimum query plans can be created to execute queries. But what many DBAs don't know is how this option affects the performance of queries. By Brad M. McGehee.
Forced Parameterization in SQL Server 2005
For applications that use mostly simple queries, the default simple parameterization of SQL Server 2005 may be more than adequate to meet your performance expectations. For applications that use mostly complex queries, however, there is a new feature in SQL Server 2005 called forced parameterization that can tell SQL Server to force the parameterization of virtually all SELECT, INSERT, UPDATE and DELETE statements. By Brad M. McGehee.
An Instance of SQL Server 2000 Performance Tuning
This article is about altering a table and adding NOT NULL columns into it … I tried a new approach for this by which I created a new table using the SELECT INTO command and within this command included new columns as well by joining with the old table. This took less than 1/10th of the time compared to the standard approach. By Sunil Madan.
SQL Server Processor Performance, 2006
With the new multi-core processors launching this year and in 2007, finally unconstrained by thermal limitations, look for performance gains in multi-threaded applications to exceed the pace of Moore's Law. The traditional doubling of the logic complexity of a processor was only expected to generate a 40% performance gain. An unconstrained dual-core can yield a 80% performance gain over the corresponding single core.
Processing event logs using DumpEvt and SQL Server
How to use and filter event logs in SQL Server
How to Detect Table Fragmentation in SQL Server 2000 and 2005
Table fragmentation can occur when modifying data with INSERT, UPDATE, or DELETE statements, which over time cause gaps in each page. If a query search is based on a table scan or partial table scan, then it will create overhead for the SQL Server process with additional page reads, leading to high CPU activity and unresponsiveness.
Techniques for Indexing Low-Selectivity Columns in SQL Server
Most of us have probably faced this situation at one time or another: there's a deceptively simple query in your system that's performing poorly … So, what to do? There are a few possible actions here, but some will help and some will not. What follows is an analysis of some techniques, with their performance impact, using SQL Server 2005.
Ranking Functions and Performance in SQL Server 2005
Ranking functions, introduced in SQL Server 2005, are a great enhancement to Transact-SQL. Many tasks, like creating arrays, generating sequential numbers, finding ranks, and so on, which in pre-2005 versions requires many lines of code, now can be implemented much easier and faster.
Practical Solution to SQL Server Performance Monitoring
The following article includes code and hands-on instruction on how to automate such tedious tasks as reusing the preset monitor parameters and analyzing the results. Given the ever-increasing size of databases and the importance of database performance, this article can also help you monitor and tune the performance of your system, as well as improve your performance as a DBA.
System and Storage Configuration for SQL Server
A proper, technically correct system and storage sizing assessment of a SQL Server system and storage configuration needs to consider a broad range of CPU, memory and IO usage analysis.
SQL Server Performance: Query Tuning vs. Process Tuning
In the different projects where I perform a performance audit or am involved in performance tuning, I often find that while queries and stored procedures function correctly, they are not efficient in processing larger data sets.
Boost Performance and Reduce Code Use With SQL Server Aggregate Functions
This article will acquaint you with aggregate functions such as MIN, MAX, COUNT, and AVG, which easily let you perform tasks that you may have thought needed extensive programming codes to accomplish.
Clustered Indexes in SQL Server: Things You Need to Know
This article covers a few, more advanced topics about the usage of clustered indexes in SQL Server. Not only will I try to convince you of the absolute necessity of using clustered indexes, I'll also give you some tips on how to use them in a not so obvious context.
How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field
The only way you can minimize performance issues (because you'll never succeed in avoiding them completely) is to test the new release in a "production alike" environment. That's why we need to organize stress tests. Part three of a four-part articles series.
Using Master..Sysprocesses to Isolate Performance Issues - Part 1
The sysprocesses table holds valuable information about each unique process, or SPID, running on the SQL Server.
Is Your Java Middleware Impacting Application Performance?
By default, most, if not all, Java middleware drivers send string parameters to SQL Server as Unicode, which can have horrible consequences for application performance.
Using TRY/CATCH to Resolve a Deadlock in SQL Server 2005
The TRY/CATCH method is powerful enough to handle the exceptions encountered in your code irrespective of how deeply nested the application is in a stored procedure.
High Call Volume SQL Server Applications on NUMA Systems
How is SQL Server performance affected by high call volume on NUMA-based servers?
How Using GUIDs in SQL Server Affect Index Performance
Are GUIDs a good or bad thing, from a performance perspective?
Finding Duplicate Indexes in Large SQL Server Databases
Learn how to identify duplicate indexes in a database.
Are UDFs Harmful to SQL Server Performance?
If used incorrectly, UDFs can cause SQL Server performance problems.
SQL Server Database Backup Performance with Quest (Imceda) LiteSpeed for SQL Server; 3 Terabytes in Under 1 Hour
Take a look at one consultant's testing of LiteSpeed.
SQL Server XML Statistics and Execution Plans
XML execution plans may not provide the results you expect.
SQL Server Monitoring in 8 Steps: Lessons From the Field
Learn how to monitor your SQL Server's performance. Part two of a four-part articles series.
10 Baselining Tips for SQL Server: Lessons From the Field
Learn how to baseline your SQL Servers. Part one of a four-part articles series.
How to Optimize the Use of the "OR" Clause When Used with Parameters
While the "OR" clause is a very short clause, it can cause a lot of performance problems if you are not careful.
What the IT Decision-Maker Needs to Know About SQL Server Performance Tuning
A high-level look at SQL Server performance tuning for the IT executive or manager.
Comparing the SQL Server 2000 Index Tuning Wizard and the SQL Server 2005 Database Tuning Adviser
How does the SQL Server 2005 DTA stand up against the SQL Server 2000 ITW?
A Practical Path Towards the Ultimate Microsoft SQL Server Scalability and Availability
What practical options do DBAs have for SQL Server scalability and availability?
Comparcion Entre Index Tuning Wizard 2000 y Database Tuning Advisor 2005 Beta
In Spanish.
How to Best Implement a SQL Server Performance Audit
In this ninth and final article on how to perform a SQL Server Audit, we take a look at some best practices on how to best implement a SQL Server Performance Audit.
How SQL Server Determines an Execution Plan Using Available Indexes and Statistics
Learn how statistics are used to help determine execution plans.
Automating Reindexing In SQL Server 2000
Learn how to reindex your database's indexes with the least performance overhead, automatically!
Analyzing SQL Server 2000 Data Caching
Ever wonder what is inside your SQL Server's data cache, and how it can affect your server's performance. Learn more here.
Using Profiler to Identify Poorly Performing Queries
This is part eight of the series on how to perform a SQL Server performance audit. Learn how to identify and prioritize poorly performing queries.
SQL Server Backup Performance with Imceda LiteSpeed
Learn how Imceda's LiteSpeed compares to SQL Server's native backup ability.
How to Identify Non-Active SQL Server Indexes
Learn how to identify unused SQL Server indexes.
Eliminación de Índices no Usados
Quite los índices innecesarios.
Transferencia de las Estadísticas de SQL Server de una Base de Datos a otra
¿Deseó siempre tener una pequeña base de datos, que pueda imitar el comportamiento de una verdadera en producción?. Aprenda cómo hacer esto en el siguiente innovador artículo.
MANTENIMIENTO: Reorganización de Índices – Actualización de Estadísticas
Aprenda alrededor reorganización de indices.
Large Data Operations in SQL Server
Large data operations in SQL Server may not work exactly like you would expect. Find out why in this original article.
Transferring SQL Server Statistics From One Database to Another
Have you ever wanted to have a small, test database mimic the index statistics of a much larger, production database? Learn how to do this in this innovative article.
SQL Server Parallel Execution Plans
Learn how parallelism affects SQL Server execution plans.
Processor Performance, Update 2004
Take an up-to-date look at processor performance.
A First Look at Execution Plan Costs in Yukon Beta 1
As you might expect Yukon creates execution plans a little differently than does previous versions of SQL Server.
Advanced SQL Server Locking
Advance your knowledge of SQL Server locking, and how it affects SQL Server's performance.
An Introduction to Clustered and Non-Clustered Index Data Structures
Learn how SQL Server stored data for clustered and non-clustered indexes.
How to Perform SQL Server Row-by-Row Operations Without Cursors
Boost SQL Server performance by avoiding cursors using this row-by-row processing technique.
SQL Server Connection Pooling Myths
SQL Server connection pooling, if used correctly, can greatly speed performance. Learn if you using connection pooling correctly.
SQL Server Hardware Tuning and Performance Monitoring
Learn the basics of how to use Performance Monitor to optimize your SQL Server hardware.
SQL Server Index Fragmentation and Its Resolution
Learn how to optimize the use of your table's indexes.
An Introduction to SQL Server Query Tuning
Learn the basics of how to turn queries and stored procedures.
SQL Server Application and Transact-SQL Performance Checklist
This is part seven of the series on how to perform a SQL Server performance audit. In this installment, you will learn how to perform a performance audit on Transact-SQL and application code that interacts with SQL Server.
Strategies to Reduce SQL Server Blocking
Learn how to identify and reduce SQL Server blocking.
How Using the Windows 2000 Encrypted File System to Secure SQL Server Databases and Backups Affects SQL Server's Performance
While Windows 2000's encrypted file system option provides needed security, how does it affect SQL Server's performance?
SQL Server Hardware Resource Planning
Planning on getting new SQL Server hardware, this article might provide some help.
How to Select Indexes for Your SQL Server Tables
Learn the basics of when to add indexes to SQL Server tables. Includes an introduction to how to read graphical execution plans.
An Interview with the Authors of SQL Performance Tuning
In this interview, the authors tell us a little about their book and offer some advice for DBAs and SQL developers.
SQL Server Quantitative Performance Analysis
This highly informative and technical article series on SQL Server performance has been updated (some articles revised) and new articles added. (Updated and Expanded)
Processor Performance, 2002
Learn how a server's CPU can affects its performance.
SQL Server Database Index Performance Checklist
This sixth part of the SQL Server Performance Audit series covers indexes, and how to audit them for best performance.
Gigabit Ethernet Direct Connect Networking
If you need to direct connect two gigabit cards, as you might want to do in a SQL Server cluster, here's how to do it.
SQL Server Database Settings Performance Checklist
This fifth part of the SQL Server Performance Audit series covers SQL Server database options and configuration settings.
SQL Server Configuration Performance Checklist
This fourth part of the SQL Server Performance Audit series covers the SQL Server configuration settings.
Operating System Performance Checklist
This third part of the SQL Server Performance Audit series covers the operating system.
How to Interact with SQL Server's Data and Procedure Cache
Learn how SQL Server caches data and how you can manipulate it.
Server System Architecture, 2002
Learn about the various chipsets, and their pros and cons, available for your SQL Server.
Troubleshooting SQL Server Full-Text Search Problems
Learn how to troubleshoot common full-text search problems.
SQL Server 7.0 and 2000 Trace Flags
Learn about documented and undocumented SQL Server 7.0 and 2000 trace flags.
SQL Server Hardware Performance Checklist
This second part of the SQL Server Performance Audit series covers the SQL Server Hardware Performance Checklist.
Some Useful Undocumented SQL Server 2000 Trace Flags
You can never know too much about SQL Server trace flags. In this article, find out about those that are undocumented in SQL Server 2000.
How to Perform a SQL Server Performance Audit
This is a first in a series of new articles on how to complete a SQL Server performance audit.
An Exclusive Interview with David Gugick, SQL Server Performance Expert and Software Developer
David Gugick provides over 100 tips on how to boost SQL Server performance.
Interview with Carl Speshock, Author of the Microsoft SQL Server 2000 Database Administrator's Guidebook
Find out more about the author of the Microsoft SQL Server 2000 Database Administrator's Guidebook, and what he thinks about SQL Server.
SQL Server 2000 Table Hints
Learn how to use table hints to boost the performance of SQL Server 2000.
Some Useful Undocumented SQL Server 7.0 Trace Flags
Learn about these undocumented SQL Server 7.0 trace flags.
Are SQL Server Temp Tables Really Necessary?
Learn the the pros and cons of using SQL Server temp tables. Also find out how to optimize temp tables, assuming you can't find a way to avoid using them.
An Exclusive Interview with Edward Whalen Co-Author of Microsoft SQL Server 2000 Performance Tuning
Learn what a SQL Server performance tuning expert has to say about performance tuning SQL Server.
SQL Server 2000 I/O Configuration in a SAN/NAS Environment
Learn how disk I/O can affect SQL Server's performance.
An Exclusive Interview with Author Fernando Guerrero On How to Get the Most of ADO.NET and SQL Server
Learn from an expert on how to get the best performance out of ADO.NET and SQL Server. Fernando is the author of Microsoft SQL Server 2000 Programming by Example.
Optimizing SQL Server Stored Procedures to Avoid Recompiles
Learn how to tune your stored procedures so that they don't suffer from unnecessary recompiles.
Data Page Structures in SQL Server 6.5
Learn about the underlying architecture of SQL Server 6.5 data storage.
SQL Server Replication from 6.5 to 2000
Find out how to replicate data from a SQL Server 6.5 database to a SQL Server 2000 database. Includes some related performance tips.
SQL Server Join Nastiness Tamed: Finding Joy In The INNER LOOP
Learn how to performance optimize SQL Server joins using the INNER LOOP hint.
How Updates are Performed in SQL Server 6.5
If you are still using SQL Server 6.5, this article on how SQL Server internally handles updates might make for some interesting reading.
Best SQL Server Performance Tuning Tips
Are you having trouble determining which performance tips produce the greatest benefits? If so, then find out the SQL Server performance tuning tips that produce the greatest benefit for the least effort expended.
Undocumented SQL Server 2000 System Tables
Learn the inner workings of these undocumented SQL Server 2000 system tables.
Interview with Robin Dewson, Author of Beginning SQL Server 2000 Programming
In this exclusive interview, Robin shares some of his knowledge and experience on how to get the best performance from SQL Server.
Use the Correct ASP (ADO) Syntax to Enable Connection Pooling for Best SQL Server Performance
Learn how to take advantage of connection pooling for the fastest access to SQL Server.
Using AWE Memory in SQL Server 2000
Learn how to take advantage of AWE memory to boost the performance of SQL Server 2000.
Microsoft SQL Server & Solid State Accelerators
Using Solid State Accelerators (memory) can be an effective way to boost SQL Server I/O performance.
The Ultimate SQL Server
What do you consider to be the ultimate SQL Server? Do you agree with this author's view of the ultimate SQL Server?
Using Index Intersection to Boost SQL Server Performance
By taking advantage of Index Intersection, you can speed up many of your queries.
Optimizing SQL Server Performance Using Files and Filegroups
Learn how to boost the performance of SQL Server by making proper use of filegroups.
Speeding UPDATEs Using the CASE Statement
Learn how to boost the performance of your UPDATEs using the CASE statement.
Save Space To Boost SQL Server Performance
A few simple space saving techniques can speed up your SQL Server databases.
<
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset