SQL Server Magazine

Native Partitioning
Partitioning isn't a new concept in SQL Server 2005. Scaling and maintaining huge tables can be a nightmare for DBAs, requiring rebuilding indexes to deal with fragmentation, purging and archiving historic data, and other time-consuming and tedious tasks. Dataloading becomes slower as a table grows larger for some insert patterns to the different indexes. DBAs use partitioning to physically split a single huge table into multiple, smaller, more manageable units, resulting in an environment that is much more scalable.

Partitioning in SQL Server 2000 is a hybrid of several elements that weren't originally designed for partitioning: namely, tables, check constraints, views, and some added functionality. In SQL Server 2000 partitioning is achieved by:

The fact that partitioning isn't native in SQL Server 2000 has several implications, both in terms of managing partitioned data and performance. DBAs need to maintain multiple physical objects instead of one. Each partition is a separate table, and there might be variations among tables in terms of schema and indexing. The optimizer needs to separately consider each relevant table for a query when generating an execution plan, resulting in long optimization time and large plans. Also, there are many requirements and limitations to support the updatability of the data through the partitioned view, which Figure 2, illustrates.

SQL Server 2005 introduces native partitioning of tables and indexes, addressing many of the limitations of partitioning in SQL Server 2000. You create a single object (table or index) that is internally partitioned, as Figure 3 shows. You need only work with a single entity, resulting in significantly reduced management and maintenance overhead. Because partitioning is native in SQL Server 2005, you use a new vocabulary with native commands for activities such as adding, removing, and switching partitions. A significantly reduced set of requirements for partitioning fully supports both retrieval and modification of data. In addition, all of a table's partitions must be identical in structure (e.g., schema, indexes); therefore, the optimizer needs to spend much less time to optimize queries, and execution plans are typically smaller. There's much more to say about partitioning in SQL Server 2005. For more information, see "Native Partitioned Tables and Indexes" (January 2005 Web Exclusive, InstantDoc ID 45153), "Partitioned Indexes and Querying Metadata" (March 2005 Web Exclusive, InstantDoc ID 45533), "Maintaining Partitions" (March 2005 Web Exclusive, InstantDoc ID 45877), and "Querying Partitions" (April 2005 Web Exclusive, InstantDoc ID 46207).

Monitoring with DMVs and DMFs
SQL Server 2005 introduces 70 new Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). The new DMVs and DMFs provide a lot of useful information about the current state of SQL Server and its health, allowing you to diagnose problems and tune the performance of your instance and databases.

Diagnostic information is now easily accessible in a fully documented, convenient table format. Some of this information was not exposed at all in SQL Server 2000, some was documented but not available in a convenient table format (e.g., fragmentation information through DBCC SHOWCONTIG), and some was available only through undocumented tools (e.g., wait information through DBCC SQLPERF(WAITSTATS)).

The new DMVs and DMFs cover two types of scope: database scope and server scope. The new dynamic management objects cover the following categories of diagnostics: Common Language Runtime, Database Mirroring, Databases, Execution, Full-Text Search, Indexing, I/O, Query Notifications, Replication, Service Broker, SQL Operating System, and Transactions. You can find a lot of information about these new objects in SQL Server 2005 Books Online under the subject Dynamic Management Objects. Researching these new objects is time well spent because you can now find much useful information about them.

Security
SQL Server 2005 introduces several important security-related enhancements. All permissions in SQL Server 2005 are now grantable with the GRANT statement. In SQL Server 2000, some permissions were directly grantable and others were available only through other means (e.g., by being a member of a role). The management of security in SQL Server 2005 is hierarchical and requires you to get used to new vocabulary. For example, securables are entities that you can secure with permissions (e.g., a login or database at the server scope, an assembly or a service at the database scope, or lower-level entities such as a type at the schema scope or a table at the object scope). Actions on securables are granted to principals (e.g., Windows groups/logins, SQL Server roles/logins, database roles/users).

SQL Server 2005 also has an accurate notion of a schema (as defined by the ANSI committee) and completely separates database users from schemas. In SQL Server 2000, the schema to which an object belongs and the database user who creates the object are the same and can't be separated. If user1 creates Table1, the table becomes user1.Table1. If you want to drop the database user user1 because the user leaves the company, you first must change the owner of all objects that user1 owns to a different database user. In SQL Server 2005, you create objects in schemas, and a schema doesn't represent a database user—it's only a schema. You can grant, deny, and revoke permissions from principals on a schema, which in SQL Server 2005 is a securable. You don't have any problems when you need to drop database users because they don't "own" objects. You also have much more flexibility in regulating security against collections of objects (i.e., schemas).

Another enhancement that DBAs have been waiting for is the ability to use built-in tools to encrypt data within the database. SQL Server 2005 introduces a new tool that lets you control the security context of a command or a routine by using the EXECUTE AS statement. EXECUTE AS replaces the older SETUSER statement, providing more flexible means for security context switching. For more information on security enhancements in SQL Server 2005, refer to BOL and to the Microsoft SQL Server 2005 article "Introduction to SQL Server 2005 Relational Engine Security Features," available at http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlsysec.mspx. Also see Kalen Delaney, "10 Things to Love About SQL Server 2005" (May 2005, InstantDoc ID 45930).

T-SQL Enhancements
If you're in charge of writing, reviewing, and maintaining T-SQL code, you'll find a wealth of new features and enhancements in SQL Server 2005. I've covered the SQL Server 2005 T-SQL enhancements extensively in the SQL Server Magazine Web-exclusive column "T-SQL 2005." You can access these articles at http://www.windowsitpro.com/departments/DepartmentID/946/946.html. Let me highlight a couple of Data Definition Language (DDL) and Data Manipulation Language (DML) enhancements that I find particularly useful and interesting.

SQL Server 2005 introduces DDL Triggers, which let you reject or react to DDL events. A trigger can react to server-level events (e.g., ALTER LOGIN) or database-level events (e.g., CREATE TABLE). The potential in this new feature is enormous—you can use it to enforce company policies with object naming conventions, auditing, schema version changes, and more.

SQL Server 2005 also introduces the new XML datatype. This is a native XML datatype that lets you store XML data within your database and also use it for local variables and input/output arguments of routines. You can index the new XML datatype with specialized XML indexes, you can constrain it with schemas, and you can query and modify it by using the XQuery language.

SQL Server 2005 enhances the variable length datatypes (VARCHAR, NVARCHAR, and VARBINARY) by introducing the MAX specifier instead of an actual size. With the MAX specifier, you let SQL Server determine when to internally manage the value as a regular value or as a large object. The nice thing about working with the enhanced datatypes is that the programming model for regular types and large objects is unified. You can use them as local variables and as input/output arguments for routines, and you can also use most of the functions that apply to regular datatypes with the enhanced datatypes.

The two querying-related enhancements that I find most exciting are Analytical Ranking Functions and Recursive Queries. The former lets you provide row numbers and other ranking values to rows of a result set of a query. I've been able use those functions to tune many existing solutions. The latter lets you to write recursive queries, which are very handy in manipulating hierarchical data.

What's New in SQL Server 2005 for the DBA?
By now, you probably realize that it's easier to answer the question "What's not new or changed in SQL Server 2005?" Almost every area of the new product has changed. I hope that this overview gave you a taste of some of the exciting features that will affect your life as a DBA for the better and allow your organization to get more from SQL Server.

End of Article

Prev. page     1 2 [3]     next page -->