Previously committed rows might be missed if NOLOCK hint is used

I received a question from one of our customers about using the NOLOCK hint: can it cause missing rows in scans even if the rows were committed well before my SELECT with NOLOCK starts?   The NOLOCK hint is employed by many users to avoid contention on tables where updates are performed concurrently with selects…. Read more

Best SQL Server 2005 MDX Tips and Tricks – Part 1

Overview SQL Server’s 2005 Analysis Services has introduced several changes to the MDX queries syntax that can lead to better performance than the equivalent AS 2000 queries.   Here is a list of tips and tricks as well as best practices on how to get better performance from your MDX queries in AS2005. Details For… Read more

OLTP Blueprint – A Performance Profile of OLTP applications

Performance and Tuning Blue Prints We will look at different types of applications, how they use resources, and how one would approach performance tuning each.  The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application.  It is helpful to understand these differences and the objectives for high performance. OLTP blueprint… Read more

Load 1TB in less than 1 hour

OVERVIEW   This project was done using SQL Server 2005 Enterprise Edition (with SP1 beta) using the BULK INSERT command with 60 parallel input files, each of approximately 16.67 GB.  The best time I got was 50 minutes.  Hewlett Packard was kind enough to loan me some time one night on the machine they use… Read more

SQL Server Sequence Number

Simulating Sequence Objects in SQL Server   Many applications need sequentially incremental number as unique/primary key of records.  SQL Server 2005 today supports identity column as the primary mean to general sequence number, which generates the sequence number upon the execution of DML (insert) or bulk insert.  The value of the insertion is known (when using… Read more

Managing Schema Changes (Part 2)

SQLCAT Blog:  Managing Schema Changes (Part 2)   Question: What are best practices for managing schema changes in SQL Server 2005?  What are the fastest, least intrusive techniques for high availability?    Answer:  In Part 1, we outlined the behavior of SQL Server 2005 schema changes.  In Part 2, we will look at best practices… Read more

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