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

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

Deploying SQL Server 2005 with SAN #1

Deploying SQL Server 2005 with SAN #1   Prem Mehra and Mike Ruthruff   An often asked question is how to design and deploy SAN with SQL Server 2005. The question is frequently raised by installations that are either deploying SQL Server for the first time or are upgrading to SAN from direct attach storage…. Read more

Obtaining Statement-Level Query Plans

In workloads that involve expensive long-running queries and reports, or complex batch operations, you frequently want to see the SQL statements and associated query plans that are active on the system or that consume the most resources.   SQL Server 2005 makes available the query plans and SQL text associated with running queries or cached… 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

Application Patterns causing poor performance

This series of blogs is a direct response from observing application patterns that can cause poor database performance.  Some of the patterns are may seem trivial or simple, but they still need to be covered because we see them many times.  Most of the patterns have a simple interpretation of what causes the bad performance… Read more