A common complaint of database administrators (DBAs) is that performance bottlenecks are not among those problems that one can fix "by just throwing hardware at it." Thus, database servers must provide tools and techniques to help administrators address this issue. On that aspect, SQL Server 2005 does not disappoint.

However, one missing element among the performance tuning services of the new SQL Server is the ability to analyze large amounts of code to detect possible inefficiencies without executing the code. We will discuss an add-on, Code Inspector for SQL, which is a static analysis tool for T-SQL being developed at Siemens Corporate Research. It is intended to help DBAs and developers optimize database performance.

SQL Server 2005 and Performance Tuning
Better Index Wizard
The old Index Selection Wizard tool has been replaced by a new and improved tool called Database Tuning Advisor that can make recommendations not only on indexes but also on partitions and multi-database tuning. Also, as one would expect, the tool can make recommendations on how to exploit the new indexing features (e.g., indexes with non-key columns, etc.). This means that if a DBA is unsure as to whether or not some of these new features might be helpful to the database, he or she could simply query the Advisor with some typical workloads to see which new features should be investigated further.

New Language Constructs
There are interesting fine-tuning possibilities for some T-SQL constructs that might impact performance. The TOP option can be used with expressions and, more importantly, it can be used with modifying statements (insert, update, delete). This should discourage the common but inefficient use of the SET ROWCOUNT option around modifying statements to avoid excessive logging and locking - we've counted over 70 such uses of SET ROWCOUNT in our reference code base.

Other improvements include the new BULK rowset provider used with the OPENROWSET function. It can be utilized to access files without loading their contents into a temporary table. The APPLY operator can be used in the FROM clause of a query to avoid the creation of temporary tables. Finally, the development of Common Table Expressions (CTE) is very interesting; however, its impact on performance remains to be determined.

SQL Server Profiler
The SQL Server Profiler has been improved to provide more information, enabling a better analysis of the behavior of the server. For example, it can now capture events raised by Analysis Services and correlate them with Performance Monitor Counters.

The tracing and Showplan mechanisms have also been enhanced. In particular, the results can be saved in XML format, enabling DBAs to analyze and compare traces and Showplans both over time and across different servers. Thus, as the load or configuration of the servers changes, DBAs can compare new traces and Showplans to a baseline to diagnose discrepancies.

XML Support
The list of XML features in SQL Server 2005 is quite long. We will briefly mention a couple that relate to the performance of the server. First, Native XML data support was added by introducing a type called XML. XML data types can be associated with an XML schema collection to automatically enforce schema constraints on XML instances. Another new feature is that XML instances can be retrieved by using SELECT statements. Note that if the XML values queried are large in size (XML data can store up to 2GB per field) the performance would suffer. Thus, a new mechanism for indexing XML columns ("create xml index ...") was added to optimize the querying of XML instances at runtime.

Online Index Operations
SQL Server 2005 substantially reduces the number of scenarios that require a restart of the database server. In many cases, database administrators are now able to change SQL Server System parameters online, without restarting the database. In particular, it is now possible to create, rebuild, or drop most indexes online. This results in the ability to make updates to the data in the database and to perform queries on the data while a clustered index is being rebuilt.

Scalability Features
The new Snapshot Isolation feature allows users to access the last committed row using a transitionally consistent view of the database, providing increased data availability for read-only access. For example, non-blocking read operations are allowed in an Online Transaction Processing (OLTP) environment even while the database is being updated. In addition, automatic mandatory conflict detection for write transactions is provided.

Another scalability feature is table partitioning. While partitioning data across tables is not new, SQL Server 2005 adds the capability to partitioning tables across filegroups in a database. This horizontal partitioning will significantly improve query performance in very large databases. Furthermore, by partitioning across column values, subsets of data can be managed and reassigned to other tables quickly and efficiently.

Integration in the .NET Development Process
One of the most exciting features in SQL Server 2005 is the ability to write SQL routines in a .NET language of your choice. This feature adds a significant amount of power to SQL programming and enables the developers to accomplish tasks like string handling, regular expressions, and complicated business logic more easily than was possible with Transact SQL. SQL programming finally becomes a first class citizen in the .NET environment.

Analyzing Large Code Bases and .NET Routines
A weak point of SQL Server 2005 for performance support is the inability to analyze large code bases without executing them. While any single query can be analyzed with the Showplan tool, it cannot be used in batch mode to analyze all queries in a large code base.

Integrating SQL code within .NET developments is an exciting feature. But using high-level, object-oriented languages in conjunction with SQL will introduce new error possibilities and complexities. This was one of the reasons behind the development of our static code analyzer for SQL, C#, and C++. By integrating our analyzer into the .NET environment, we allow developers to check their .NET code automatically for possible coding errors in SQL/C#/ C++/.NET without leaving the Visual Studio environment.

The Code Checking Tool
Our static code analysis tool works like a compiler in which the code generation is replaced by a rule checking engine (See Figure 1).

For SQL code, the rules check attempts to detect potential errors and performance degradation. For example, we have rules that check that all tables in a query are joined (avoiding cross-products). Another rule checks that the number of tables joined in a query is limited. This rule is configured by the administrator, who can use his or her knowledge of the database configuration to select the optimal limit. For example, in one installation using MS SQL 6, the administrator had found that performance would significantly degrade if more than four tables were joined together and would often become unacceptable if more than eight tables were joined together. So the rule was configured to issue a warning if a select statement required more than four joins and an error if it required more than eight joins.

Static Code Checking for SQL 2005 .NET Routines
Adapting our tool for SQL Server 2005 and .NET Routines entails the development of checks that are targeted to that platform. For example, consider the following problem: What are the criteria for selecting T-SQL instead of .NET Routines? The answer to this question is that T-SQL is most suitable where data access with little or no procedural logic is required while a .NET Routine should be preferred when there are significant computations and data manipulation needed. In the extreme case where only data access is executed, the SQL routines written in .NET (CLR routines) perform about 20% slower than Transact SQL procedures, whereas if the code checked implements only computations, then CLR routines are several times faster.

To address this problem, we developed a rule that looks at stored procedures and computes the ratio of data access to computation. By using this ratio, Code Inspector will recommend use of either stored procedures in CLR User Defined Stored Procedures or T-SQL procedures using a Scalar Valued Function to perform the computations. Figure 2 shows our prototype SQL Add-In for Visual Studio 2005.

Summary
SQL Server 2005 provides many new features that should help the DBAs obtain the best performance from their servers. The weak support for code base analysis led us to develop a powerful static code analysis tool for T-SQL and .NET SQL Routines, the Code Inspector. Combining Code Inspector with other optimization tools can help significantly increase the performance.

Acknowledgments
We would like to thank Simon Stemplinger, Rita Altucher, Michael Greenberg, and Gilberto Matos.