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

Aug-2011 ~ Double Feature ~ John Miner & Grant Fritchey

Posted by admin

 

Presenter: John Miner of Sensata Technologies
Topic: Working with bit patterns

 

In today’s manufacturing environment, production lines are automated with robotics and sensors. Many of these low end microprocessors and/or integrated circuits are designed for specific tasks such as temperature and pressure control.

This presentation will review how to store the memory buffer in a table and use a view to interpret the results.

The following topics will be presented with examples.

1. Using the VARBINARY data type to store the bit pattern.
2. Storing a version number just in-case the decode changes.
3. Breaking the pattern into registers and nibbles.
4. Left shifting of the bits.
5. Right shifting of the bits.
6. Combining bits from two registers.
7. Putting it all together with a view.

Managing your data well can lead to savings in disk space as much as 75%.

Biography:

John Miner (www.craftydba.com) has twenty years of data processing and proven project management experience, specializing in the banking, health care, and government areas. His architecture expertise encompasses all phases of the project life cycle, including design, development, implementation, and maintenance. His credentials include a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages. He has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).

 

Presenter: Grant Fritchey of Red Gate
Topic: Seven Different Solutions for Bad Parameter Sniffing

 

Parameter sniffing is a misunderstood issue on SQL Server. Most of the time parameter sniffing is helping performance on your servers. But sometimes, circumstances change and what was helping you is now hurting you, bad.

In this session we’ll gain an understanding of what exactly parameter sniffing is and why it’s usually so helpful. Then, we’ll explore how parameter sniffing can go wrong and I’ll show you seven different ways you can deal with it when it does.

You’ll bring back a wealth of knowledge so that you can identify and resolve bad parameter sniffing in your own environment. Everyone has heard about twitter, facebook and linkedin but how or why does one get started?

Biography:

Grant Fritchey has twenty+ years experience in IT. That time was spent in technical support, development and database administration. He works for Red Gate Software as a Product Evangelist and writes articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. He has published two books, “Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” He is one of the founding officers of the Southern New England SQL Server Users Group and its current president and works on part-time, short-term, off-site consulting contracts.