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

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

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

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

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

How can SQL Server 2005 OnLine Piecemeal Restore improve availability?

Question 1:  How can SQL Server 2005 OnLine Piecemeal Restore improve availability? Question 2:  Can I recover a single object such as a table or specific partition(s) from a partitioned table? Answer:   The question is how to get the greatest flexibility, manageability, availability and recoverability for large databases.  Anytime you need to restore a… Read more

How do you measure CPU pressure?

It is important to understand whether CPU pressure is affecting SQL Server performance.  This is true even in the case where SQL Server is the only application running on a particular box.  The System object Perfmon counter Processor Queue length is not necessarily an effective way of measuring CPU pressure in SQL Server.  To see… Read more