Aug-2013 ~ Query Optimization – Specific Techniques to Make Queries Faster ~ Andy Novick

Posted by admin


Presenter: Andy Novick
Topic: Query Optimization – Specific Techniques to Make Queries Faster

Executing individual queries is the heart of what SQL Server does for us and many a hour is spent trying to optimize the performance of a single query.  This presentation show that by examining query plans,  specific techniques can be applied that transform slow queries into speedy ones.  Using a series of examples Andy will demonstrate these techniques and show how the query plan and other tools point the way to a better solution.

Some of the techniques are:

  • Optimize for specific conditions
  • Managing parameter sniffing, auto parameterization and when to use the hints RECOMPILE, OPTIMIZE FOR or switch to dynamic SQL.
  • Using a table of numbers
  • Creating parallelism with CROSS APPLY
  • Rewrite Functions for order of magnitude speedup
  • Introduce a #temp table to get statistics and a better plan
  • Adding redundant predicates
  • Suppressing unnecessary statistics creation

Biography:

Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server’s capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular “SQL Server Load-fest” event conduced in Waltham, MA.

FREE! Query Optimizer eBook and SQL Prompt trial

This free ebook from Red Gate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Download it now

Oct-2010 ~ DMV’s as a Shortcut to Procedure Tuning ~ Grant Fritchney

Posted by admin

 

Presenter: Grant Fritchey – SQL Server MVP
Topic: DMV’s as a Shortcut to Procedure Tuning

 

Dynamic Management Views (DMV) expose a wealth of information to the database administrator. However, they also expose information that is vital to the database developer. More often than not people gather performance metrics through server side traces. This session will show how to gather information from the DMVs for currently executing, and recently executed queries.

The session will demonstrate combining this information with other DMVs to get more intersting information such as the query plan and query text. I’ll show where you can get aggregate information for the queries in cache to determine which queries are being frequently accessed or using the most resources. I’ll show how to determine which indexes are being used in your system and which are not. All of this will be focused, not on the DBA, but on the query writer, the developer or database developer that needs information to tune and troubleshoot data access.

Biography:

Grant Fritchey has twenty years experience in IT. That time was spent in technical support, development and database administration. For the last seven years he has been working at a major insurance company as a DBA. He writes articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. He also published two books, ”Dissecting SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” He is one of the founding officers of the this Users Group and currently serving as the president. In April 2009 he was awarded as a Microsoft SQL Server MVP. You can also find him tweeting as @GFritchey and blogging at Home of the Scary DBA.