vrijdag 27 mei 2011

SQL: Index Scan Vs. Index seek

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records (see http://mattfleming.com/node/192 for an idea on how this works) - time taken is only proportional to the number of matching records.

In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
Note however that in certain situations an index scan can actually be faster than an index seek - usually when the table is very small, or when a large percentage of the records match the predicate.

woensdag 18 mei 2011

Microsoft SQL Server 2008 R2 Service Pack 1

Service Pack 1 is available for SQL Server 2008 R2.

What’s New in SQL Server 2008 R2 Service Pack 1
  • Dynamic Management Views for increased supportability:
    sys.dm_exec_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.
  • ForceSeek for improved querying performance :
    Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.
  • Data-tier Application Component Framework (DAC Fx) for improved database upgrades:
    The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.
  • Disk space control for PowerPivot:
    This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

    Source:Download details: Microsoft® SQL Server® 2008 R2 Service Pack 1 Community Technology Preview
  • SQL Server 2008 Developer Training Kit

    Learning how to develop with the rich data types, programming models and new development paradigms in SQL Server 2008?
    Microsoft has a training kit available to do this with slides, demos and hands-on labs.

    DOWNLOAD: SQL Server 2008 Developer Training Kit

    Since Januari 2011 there is also an update available.

    New Content: Build Your First Microsoft BI Solution with SQL Server 2008 R2
    This update includes a rich new set of content designed for developers who want to learn how to build their first Microsoft Business Intelligence solution with SQL Server 2008 R2. Microsoft BI solutions leverage your existing technology investments in .NET, SQL Server and Office to develop rich integrated reporting and analytics experiences that empower users to gain access to accurate, up-to-date information for better, more relevant decision making. This content is a complete training course that contains eleven units covering all the key aspects of Microsoft BI solution development. You can find it by navigating to the Getting Started - BI Developers page. Here is a summary of what’s included:
    31 presentations (PowerPoint slides, videos and transcripts included)
    27 demos (installer scripts, videos and transcripts included)
    12 hands on labs (installer scripts included)

    More Info + Download: SQL Server 2008 R2 Update for Developers Training Kit

    dinsdag 10 mei 2011

    interesting vids

    I've been to the Microsoft Techdays a few weeks ago.It's been an amazing experience and learned some new things.

    Too bad though that some interesting sessions took place on the exact same moment. So I could only follow one of them.
    Luckily however, they announced that all sessions would be recorded and placed on the channel 9 web site to view later.

    Well, I'm still waiting for those sessions to appear on the website...
    I've found some other Microsoft technology event sessions of this year that were on channel 9 already. So for now, I won't have any evenings not knowing what to do :).

  • Devdays 2011 Netherlands
  • MIX 2011 Las Vegas
  • techdays 2011 Belgium (only developer keynote on line yet)
  • woensdag 4 mei 2011

    Promoted property vs distinguished field - tutorial

    My latest post has been quite some time ago now.
    This is because of a holiday, a course on SQL 2008 and off course attending the techdays 2011 Belgium.
    I recently found an interesting blog on the difference between promoted properties and distinguished fields in BizTalk. It explains the differences between them both and shows you how to add both of them to your schema.
    BLOG: Francois Malgreve - Bangkok: Promoted property vs distinguished field - tutorial