Detecting Overlapping Indexes in SQL Server 2005

When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer performs an index SEEK operation as opposed to an index (or table) scan to retrieve the required rows; this is desirable.  Based on this, one may be led to believe that having an index for every possible query…

4

New SQL Best Practice Articles now available

Please have a look at four new Best Practices Articles SQL Server 2005 Predeployment I/O best practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx                                by SQLISVPM team member Mike Ruthruff SQL Server 2005 Deployment Guidance for Web Hosting Environments http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspxby Alex DeNeui Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarioshttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspxby Carl Rabeler Implementing Application Failover with Database…


Will 64-bit increase the performance of my SQL Server application?

With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on native 64-bit Windows there is a common misperception that running on 64-bit will always provide an increase in performance over 32-bit.  Many customers are surprised to find that some workloads actually run faster on 32-bit SQL Server than on the native…

3

Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting.

Upgrading to SQL Server 2005 has many benefits . As we look back at the past year and the deployment of SQL Server 2005 with our ISV partners, one important but under-advertised feature in SQL Server 2005 became increasingly visible; the ability to control the dynamic name resolution behavior. This capability applies to workloads where object…

2

why cannot i GRANT EXECUTE permission on assemblies anymore ?

Just a quick note that this is by design and no you don’t need to use it.  What are we talking about ? Books online talks about granting assembly permissions. You used to do GRANT execute on ASSEMBLY :: <assembly_name> to <database_principal > with SQL Server 2005 Your database holds your assemblies and as such you could grant /…

4

Best Practices for SQL Server 2005

Did you know there is a great place to get real-world best practices for SQL Server 2005:  http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx This Best Practices site contians information such as Technical Whitepapers, Best Practices Toolbox which contains scripts and other tools for performance tuning and troubleshooting, Top 10 lists, and best practice information compiled from the SQL Server Books Online…

1

4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

    The following queries can be used to understand plan cache behavior. It should be noted that for systems with large plan caches, selecting all rows and joining with sys.dm_exec_sql_text is not recommended. To find the size of the plan cache use:   select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8  / (1024.0 * 1024.0)…

4

2.0 Diagnosing Plan Cache Related Performance Problems and Suggested Solutions

  Plan cache related performance problems usually manifest in decrease in throughput (or increase in query response time), and some times out of memory errors, connection time outs. None of these symptoms however point to plan cache related performance problems specifically. In order to determine and further isolate the cause of the slowdown the following…

2

Trouble Shooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1

  If after upgrading from SQL 2000 to SQL 2005 RTM and/or SP1, you notice decreased OLTP database application throughput, bloated plan cache or out of memory errors, you will need to gather machine configuration information and system performance data in order to perform a detailed analysis. Below, we will outline the steps to take…

1