2. Application Patterns and database performance: Academically correct object modeling.

  Another pattern that can cause too many round trips to the database is when each object is treated as a black box and is responsible for retrieving and saving its own data.  This by itself is not the problem; it’s when the object hierarchy reaches three or more levels.  The natural programming pattern becomes… Read more

Managing Schema Changes (Part 1)

Question: What are best practices for managing schema changes in SQL Server 2005?  What are the fastest, least intrusive techniques for high availability?   Answer: A common requirement as business requirements evolve over time is managing schema changes.  While SQL Server 2005 supports the changing of schema (e.g. adding columns and constraints, changing column data… Read more

Partition Elimination in SQL Server 2005

Lubor Kollar   Partition elimination is very important when SQL Server executes queries against partitioned tables or partitioned views. In general, SQL Server is doing an excellent job of not scanning the partitions that are excluded by some predicates. Recently we have discovered one scenario where the partition elimination does not work against partitioned tables… Read more

Application Patterns and database performance: Complex Screens

As promised way back in August, here is the first installment of a series of blogs on database performance from the application point of view. Complex screens can cause database performance problems when each control (grid, list box, etc) on the screen is treated as an individual object and is responsible for retrieving its own… Read more

How can SQL Server 2005 help me evaluate and manage indexes?

Question:  How can SQL Server 2005 help me evaluate and manage indexes? (1) How can I find out whether my indexes are useful?  How are they used? (2) Do I have any tables or indexes that are not used (or rarely) (3) What is the cost of index maintenance vs. its benefit?  (4) Do I… Read more

Achieving Selective Uniqueness in SQL Server Tables

SQL Server development group is repeatedly getting questions, suggestions and requests related to supporting unique indexes allowing multiple NULLs in SQL Server.   The SQL Standard requires that a column—or a set of columns—which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the DBMS implements an optional… Read more

How can SQL Server 2005 help me evaluate and manage indexes?

  SQLCAT Blog:   Question:  How can SQL Server 2005 help me evaluate and manage indexes? (1) How can I find out whether my indexes are useful?  How are they used? (2) Do I have any tables or indexes that are not used (or rarely) (3) What is the cost of index maintenance vs. its… Read more

Partitioned Tables, Parallelism & Performance considerations

Q:  When querying SS2005 Partitioned Tables, what kind of parallelism should I expect to see and how will that affect performance?   A: First, a brief background on partitioning.  SQL Server 2005 table partitioning provides many improvements in terms of manageability and availability.   The manageability improvements allow metadata only switch-in and switch-out of a partition… Read more

SQL Server 2005 Configuration Blog #2.doc

Prem Mehra and Mike Ruthruff   In an earlier blog, Deploying SQL Server 2005 with SAN #1, we addressed three topics: 1) the core SQL Server requirements with respect to IO subsystem, 2) the complexity introduced by virtualization of the IO subsystem and 3) the type of RAID level to choose.In this one we want… Read more

Deploying SQL Server 2005 with SAN #3

Prem Mehra and Mike Ruthruff   We recommend that prior to deploying SAN in a SQL Server production environment you conduct a bench-mark to establish a performance base-line. There are several benefits: 1) Establish a base-line so that you know ahead of time the maximum throughput and response time to expect 2) Identify any performance… Read more