Welcome to Sign in | Join | Help

noiembrie 2007 - Posts

banner

Learn how you can make Data Mining work for you.

Most of us have heard of the magic of Data Mining. No doubt you know that it is part of the Business Intelligence platform offered by Microsoft. In this one-day seminar, Rafal Lukawiecki aims to demystify this technology in four easy-to-understand sessions packed with practical information. Learn what Data Mining and Business Intelligence can do for you, how to deploy and manage it, how to use it, and how to make it available to other parts of your IT environment. Microsoft has taken Data Mining technology to a new level, making it accessible to all IT Professionals and, with your help, to all of your users.

After attending this seminar Data Mining will no longer seem like black magic to you.


Agenda

09:20 Opening and Introductions (20 min)
After introductions, we begin the day with an overview of the agenda highlighting the key topics to be covered and the logistics of the event.
10:40 Introduction to Data Mining (80 min)
To commence, we will discuss the concepts and the terminology used in the discipline of Data Mining. To make this session as practical as possible, we will then review the common scenarios and applications for the use of Data Mining. We will also look at the "bigger picture" of the discipline of Business Intelligence and see how Data Mining is a part of it. Also in this session we will introduce the fundamental process for data mining, looking at the concepts of data assets and their preparedness. This session will end with a look at the technology product roadmap showing you relationships between Data Mining and technologies of Microsoft SQL Server 2008 & 2005, Microsoft Office 2007, and other systems. At the end of this session you should have a good understanding of applications of Data Mining.
11:00 Working with Data Mining (75 min)
You are ready to mine data-what are the steps and what is the recommended order? This session covers the already introduced Data Mining Process in detail. We will study its main steps: model preparation, model training, testing and evaluation of the built model, deployment, and ongoing model maintenance. We will spend time looking at possible exceptions and problems that you may be faced with in this process, such as missing or inconsistent data, or even data that seems fine but produces strange results. In the end, we want to make sure that the intelligence you are gathering is of quality that you expected. At the end of this session you will know how to use data mining well.
13:15 Using Data Mining in Your IT Systems (Part 1 & 2 ) (75 min)
The two afternoon sessions will apply your knowledge of Data Mining to practical situations and cases that you are likely to encounter in your professional life. We will look at a number of canonical applications of data mining from the perspective of a practical scenario in order to show you how to correctly select the best features of Data Mining technologies. For example, when we look at the scenario of customer segmentation in a consumer-oriented company, we will help you chose the best of the data mining algorithms available and configure its parameters correctly. We will pay attention to common issues that may arise, such as the seasonality of data over a sales year, and we will help you decide when it is correct to rely on sampled data and when you may want to use the entire database instead. By following this pattern a few times we hope to cover all the necessary technicalities of data mining toolkit so that you are ready to use this powerful technology straight away. Before we close this session we will present you with uses of data mining that benefit your own, day-to-day, tasks as an IT Professional, System Administrator, or, perhaps, a Security Officer. For instance, we will show you how you could use data mining to better understand your infrastructure performance characteristics, to build new, higher-level data sources, or, perhaps, to discover insecure chains of infrastructure events that could lead to fraud. We hope that at the end of these two sessions we will have enabled you to work with the intelligence that previously was hidden, inaccessible, or just unknown across your IT systems.
16:00 Q&A Session (30 min)
For those attendees wishing to ask more questions or to discuss additional issues that were not covered during the day we plan a short session. Please prepare any questions in advance, if possible.

Topics covered

  • Concepts and terminology.
  • Common scenarios and applications.
  • Data Mining as part of Business Intelligence.
  • Fundamental process for data mining: model preparation, model training, testing andevaluation, model deployment, and ongoing model maintenance.
  • Correct choice and usage of data mining techniques and algorithms for a wide varietyof scenarios and cases.
  • Possible exceptions and common problems affecting data quality and recommended solutions.
  • In-depth look at the data mining toolkit and its configuration and parameters.
  • Uses of data mining that benefit day-to-day tasks of an IT Professional, a System Administrator, or a Security Officer.
  • Technology product roadmap covering Data Mining and Microsoft SQL Server 2008 & 2005,Microsoft Office 2007, and other systems.

Objectives

This roadshow aims to educate the IT Professional about Microsoft DataMining. It will show, in a practical way, how data mining can be used in their IT infrastructure to support real business scenarios demystifying the perception that Data Mining is complex, untested or designed for its specialists only.

Abstract of the day

Most of us have heard of the magic of Data Mining. No doubt you know that it is part of Business Intelligence platform, offered by Microsoft SQL Server 2008 and 2005, and Microsoft Office System 2007 amongst others. We all know of companies that made fortunes by having carefully extracted intelligence from mountains of data - with Data Mining. Let us demystify this technology in our seminar. In four easy-to-understand yet packed with practical information sessions you will learn about what Data Mining and Business Intelligence can do for you, how to deploy and manage it, how to use it, and how to make it available to other parts of your IT environment. While in the past it may have taken a university degree in Statistics to make use of Data Mining, Microsoft has taken the technology to a new level, making it accessible to all IT Professionals and, with your help, to all of your users. We promise that after attending this seminar Data Mining will no longer seem like black magic to you. Perhaps, we may even help you embark on a new path in your career towards becoming someone akin to a Keeper of Enterprise Intelligence. Let us share our enthusiasm with you.


Don't Miss Out. Register now !

 

Taxa de participare
pentru acest seminar
este de

80 USD

sau

200 RON

- TVA inclus -

 

Seminarul va fi sustinut in limba engleza

 

4 decembrie 2007
Bucuresti
Hotel Marriott
Sala Constanta

 

Don't Miss Out. Register Now !

 

 

Rafal Lukawiecki

As Strategic Consultant at Project Botticelli Ltd, Rafal is responsible for analyzing and forecasting trends in the field of Information Technology. He has helped to build and restructure software development houses and IT consultancies to promote efficiency and productivity while stimulating team spirit across organizations. Rafal specializes in IT architecture models, security and cryptography, management of solution delivery, and more recently statistics and data mining. He is a frequent and popular speaker at Microsoft events such as Tech Ed Developers and Tech Ed IT Forum.

 

 

Expected audience

IT Professional in an EMEA-sized enterprise. Seminar will also appeal to smaller organisations in which IT Pro may have occasional system development or solution deployment responsibilities.


 

Are you a member of ROSQL? Then ask me for a free voucher - I have 5 of them (at this point).

[edit]

There are no vouchers left

[/edit]

because you can watch the best sessions on TechNet Spotlight (the former IT's Showtime):


The Next Wave: Bringing Dynamic IT to Life - Bob Kelly - 11/18/2007
Power up with the next wave of technologies coming from Microsoft and watch the benefits of Dynamic IT come to life at your organization. Join Corporate Vice President Bob Kelly as he discusses Microsoft's vision for "Dynamic IT" and demonstrates the new technologies coming from Microsoft, such as Windows Server 2008, Microsoft's virtualization technologies, System Center Virtual Machine Manager, System Center Configuration Manager, System Center Data Protection Manager, SQL Server 2008, and Windows Home Server.
Tech-Ed 2007 Keynote

Tech-Ed 2007 Keynote

Bob Muglia

★ ★ ★ ★
The Fortified Data Center in Your Future

The Fortified Data Center in Your Future

Steve Riley

★ ★ ★ ★ ★
Windows Server 2008 Deployment Overview

Windows Server 2008 Deployment Overview

Michael Niehaus, Tim Mintner

★ ★ ★ ★
New Cryptography: Algorithms, APIs, and Architecture

New Cryptography: Algorithms, APIs, and Architecture

Rafal Lukawiecki

★ ★ ★ ★ ★
Managing Windows Server 2008 with Server Manager

Managing Windows Server 2008 with Server Manager

Dan Harman

★ ★ ★ ★
Microsoft Office SharePoint Server 2007 Overview

Microsoft Office SharePoint Server 2007 Overview

Thomas Rizzo

★ ★ ★ ★ ★
Microsoft SoftGrid Application Virtualization

Microsoft SoftGrid Application Virtualization

Chad Jones

★ ★ ★ ★
Predictive Analytics with Microsoft SQL Server 2005

Predictive Analytics with Microsoft SQL Server 2005

Donald Farmer

★ ★ ★ ★
Windows Mobile 6 Security In-Depth

Windows Mobile 6 Security In-Depth

Steve Riley

★ ★ ★ ★ ★
Making the Tradeoff: Be Secure or Get Work Done

Making the Tradeoff: Be Secure or Get Work Done

Steve Riley

★ ★ ★ ★ ★
Using Group Policy with Windows and Windows Server 2008

Using Group Policy with Windows and Windows Server 2008

Mazhar Mohammed, Derek Melber

★ ★ ★ ★ ★
Windows Internet Explorer 7 Security In-Depth

Windows Internet Explorer 7 Security In-Depth

Markellos Diorinos

★ ★ ★ ★ ★
Using Zero Touch Provisioning

Using Zero Touch Provisioning

David Clark

★ ★ ★ ★ ★
Developing More Intelligent Applications with Data Mining

Developing More Intelligent Applications with Data Mining

Rafal Lukawiecki

★ ★ ★ ★ ★

 

The meeting was just fine, we didn't keep our promise that it will be a 2 hours and a half meeting but no one complained (yet) about the extra hour Big Smile

For those interested in the pictures from the event take a look here.

The resources I mentioned today are:

So, tomorrow I have an user group meeting. I tried to present the agenda using Reporting Services. I used a simple table:

 

USE tempdb;
GO
CREATE TABLE [dbo].[Agenda](
 [ID] [int] NOT NULL PRIMARY KEY,
 [SessionName] [varchar](128) NULL,
 [Speaker] [varchar](128) NULL,
 [Duration] [int] NULL);
GO
INSERT Agenda(ID, SessionName, Speaker, Duration)
VALUES (1, N'Intro', N'Cristian Lefter', 10),
(2, N'Object Dependencies', N'Cristian Lefter', 20),
(3, N'HierarchyID data type', N'Razvan Socol', 30),
(4, N'Pauza', N'N/A', 10),
(5, N'Intro to LINQ', N'Petru Jucovschi', 90);

Then I created a new DataSource and a DataSet in Report Designer based on the query SELECT * FROM Agenda;

The next step was to insert a chart control in the report and place the field ID in the Category Fields area, the field SessionName in the Series Fields area and the field Duration in the Data Fields area:

 

First problem: why in the Legends area a string Duration is attached to the session name?

If I run the report I get:

Following the same steps in Business Intelligence Development Studio I obtain what I expected in the first place:

I searched through every property and I didn't manage to remove the Duration label from the Report Designer. I can remove it from RDL but it doesn't seem a solution.

What am I doing wrong?

Dan Jones has a complete list with what's new: DMF: What's New In The November CTP.

Then you can help Microsoft making SSIS better by answering the questions of the next survey: SQL Server ETL Survey: Win a Zune!

[quote]

This survey will help SQL Server Integration Services (SSIS) team understand your current Data Extract, Transform & Load (ETL) practice.

When answering the question, respond with your first reaction on typical scenarios. Your feedback is entirely voluntary. If you do not wish to provide Microsoft® with any feedback, please disregard this survey. By filling out and returning this survey, you agree that Microsoft can use, disclose, reproduce, license, or otherwise distribute your feedback.

You will have a chance to win one of the 5 Zunes. To win a Zune, you must fill out the survey by Dec. 15, 2007. Please feel free to forward the survey to DBAs, Developers, IT Ops, or other SQL Server users who perform ETL/DTS/SSIS tasks in your organization. 

[/quote]

 

Orcas is finally RTM. Some things are not RTM yet such as  Expression Blend, Silverlight tooling for Visual Studio, ADO.NET Entity Framework and ADO.NET Entity Framework Tooling.

However getting back to Visual Studio MSDN Professional and Premium Subscribers can download Visual Studio 2008 from MSDN Subscriber Downloads.

Don't have an MSDN subscription? Then try Visual Studio 2008 Trial Downloads or you can download the free Visual Studio 2008 Express Editions and Microsoft .NET Framework 3.5.

Need to learn: Then use the Visual Studio 2008 and .NET Framework 3.5 Training Kit.Created originally for the ISVs, the training KIT includes presentations, hands-on labs, and demos. This content is designed to help you learn how to utilize the Visual Studio 2008 features and a variety of framework technologies including: LINQ, C# 3.0, Visual Basic 9, WCF, WF, WPF, ASP.NET AJAX, VSTO, CardSpace, SilverLight, Mobile and Application Lifecycle Management.

Add to that the Break Through Your Software Development Challenges with Microsoft Visual Studio 2008 White Paper the .NET Framework 3.5 Common Namespaces and Types Poster and the WCF, WF and .NET 3.5 Whitepapers by David Chappell.

Complete .NET Framework 3.5 Poster:

The Data, XML and LINQ section at the original resolution:

 

Why would anyone would want to do that? Unbelievable but some people still don't like the current state of Intellisense (it's still in evolution) Big Smile

Ok, ok - I admit it still has some limitations and for some can be anoying so to turn it off go to Tools menu and click Options. I assume that you do this in SSMS Smile. Then expand the Text Editor node and from the General tab of All Languages, Transact-SQL, or XML uncheck the options that you want to be turned off.

What if you start again the SSMS? Surprise, surprise your settings will be reset to on (the default). To really turn Intellisese OFF you have to use the registry by modifying the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Shell\Languages\Language Services\SQL

To turn IntelliSense off by default, set the value of the CodeSense field to 0. To enable IntelliSense by default, set CodeSense to 1.

It may not be a bad idea to backup important data before editing registry.

In a previous post I speculated about Surface Area Configuration Tool removal from SQL Server. Now with the CTP5 out it's official. See the readme file:

[quote] 

Surface Area Configuration Tool Removed from SQL Server 2008

The Surface Area Configuration tool for SQL Server has been removed from SQL Server 2008. Replacement functionality in this release is as follows:

Settings and component features How to configure

Protocols, connection, and startup options

Use SQL Server Configuration Manager.

Database Engine features

Use Declarative Management Framework, the property settings in SQL Server Management Studio, or sp_configure.

SSAS features

Use the property settings in SQL Server Management Studio.

SSRS - Enable Integrated Security property

Use the property settings in SQL Server Management Studio.

SSRS - "Schedule events and report delivery" and "Web service and HTTP access"

Edit the RSReportServer.config configuration file.

Command line options

No support in this release.

 

[/quote]

As a consequence we can assume that Declarative Management Framework policies will be available in all editions of SQL Server 2008.

 

Download the November CTP here

Overview:

SQL Server 2008 Improvement Pillars 

Express Edition Available: download here

[quote]Note: You can also try a CTP version of SQL Server 2008 Express Edition. This free, fully functional edition of SQL Server 2008 is ideal for learning and running desktop and small server database applications. SQL Server 2008 Express supports 1 CPU, 1 GB of RAM and databases that are up to 4GB in size. This software also automatically expires after 180 days.[/quote]

Visual Studio 2005 Support for SQL Server 2008, Community Technology Preview

What's new:

Backup Compression
Configuration Servers
Change Tracking
FILESTREAM Storage
Hot Add CPU
Plan Guide Enhancements
Query Processing on Partitioned Objects
Partition Switching on Partitioned Tables and Indexes
Resource Governor
Extensible Key Management
Transparent Data Encryption
Server Administration - Enhancements of DMF
Spatial Data Storage, Methods, and Indexing
SQL Server Management Studio Enhancements
- Transact-SQL Query Editor IntelliSense
- Transact-SQL Error List Window
- query multiple servers at the same time by opening query windows from registered server groups and the option to combine query results combined into a single results pane
- configure the number of rows that are returned when you are opening tables.
-prevent the table designer from re-creating tables when you are implementing design changes.
Lock Escalation Option for the ALTER TABLE statement

Aggregation Design Improvements
Cube Design Improvements

Creation of Holdout Test Sets
Filtering on Model Cases
Cross-Validation of Multiple Mining Models
Drillthrough to Structure Cases and Structure Columns
Aliasing Mining Model Columns

Enhanced Performance and Caching for the Lookup Transformation
Enhanced Data Type Handling in the SQL Server Import and Export Wizard
New ADO.NET Components
New Data Profiling Task and Data Profile Viewer (Data Profiling)

New Conversation Priorities
New Diagnostic Utility - the ssbdiagnose utility
New System Monitor Object - the Broker Transmission Object performance object
New Service Broker Tutorials

 


 

Browsing Silverlight Showcase page I found this SQL Server 2008 Overview Demo built using Silverlight:

 [quote]Microsoft SQL Server 2008 provides a trusted, productive, and intelligent data platform that enables you to run your most demanding mission critical applications, reduce time and cost of development and management of applications, and deliver actionable insight to your entire organization. This 7-minute video walks you through the feature enhancements available with SQL Server 2008. [/quote]

Do not expect a very technical content. Enjoy!

A new channel for IT professionals was launched recently by Microsoft. The Technet Edge.

Quoting from its about page: "TechNet Edge is a place where IT professionals go to get the inside scoop on the latest and greatest technologies. You can do stuff like watch screen casts, check out interviews with people who develop the products, connect with your peers and speak what’s on your mind. It is part of the Evangelism Network and follows its doctrine, so you know we keep it real."

Why would you wanna visit this page? For example to see an interview with Steve Riley: Steve Riley talks security

 


Steve Riley talks security

The new channel completes the other Microsoft communities such as:

Channel 8 - "THE place to be for STUDENTS who want to code, connect and create technology."

Channel 9 - "a new level of communication between Microsoft and developers."

Channel 9 has also a new face

 

Channel 10 - "Channel 10 is a place for enthusiasts with a passion for technology. Through a world-wide network of contributors, Channel 10 covers the latest news in music, mobility, photography, videography, gaming, and new PC hardware and software."

 

ASP.NET - The official Microsoft ASP .NET site.

IIS.NET - The Microsoft Internet Information Services Site.

Silverlight - "Microsoft® Silverlight™ is a cross-browser, cross-platform plug-in for delivering the next generation of .NET based media experiences and rich interactive applications for the Web."

WindowsClient.NET - "Windows Forms - Windows® Forms is a set of classes in the .NET Framework that enables rapid development of rich Windows client applications, with powerful, extensible libraries for user-interface controls and graphics. Windows Presentation Foundation
WPF, a component of Microsoft .NET Framework 3.0, empowers you to build the next-generation of Windows user experiences."

Mix Online - "MIX is an ongoing conversation between web designers, developers, and business decision makers. We showcase topics and solutions that bridge Microsoft and non-Microsoft perspectives, and emphasize the inclusive and participatory nature of the next web."

 

Port 25 - There is an enormous pool of resources and communities throughout the Internet and within the blogosphere dedicated to discussing technology. A ton. Sites dedicated to discussing Linux, Windows, UNIX, Mac – to all technology in all places – and how that does & will affect our lives.  However, healthy, productive discussion only occurs when there are two parties listening & responding to each other – the principle element of all communication. This is the foundation that Port 25 is built on.

So it wasn't 13 November the CTP5 day. But the CTP5 will be available soon. I can feel it Smile

 But meanwhile you have some new resources:

 

Add to that the resources from this post Free SQL Server 2008 and SQL Server 2005 training and these labs:TechNet Virtual Lab: Working with the New Date Data Type in SQL Server 2008, What's New in SQL Server 2008 for Database AdministratorsTechNet Virtual Lab: Administering Servers by Using Declarative Management Framework (DMF) Policies, TechNet Virtual Lab: Implementing Change Data Capture (CDC) in SQL Server 2008

So you have what to do till CTP5 is out Big Smile

Junior DBA talking to Senior DBA about Extended Events:

Is it today the CTP5 day? According to eWeek (Microsoft Releases Another SQL Server 2008 CTP) and Bink.nu(Microsoft SQL Server 2008 November CTP) the answer is yes!

We shall see!

Junior DBA talking to Senior DBA about Bitmap Filters and Bitmap Indexes:

Resources:

Intro to Query Execution Bitmap Filters

http://en.wikipedia.org/wiki/Bitmap_index

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1933280&SiteID=1

Junior DBA talking to Senior DBA about Notification Services and Event Notifications:

Maybe you've noticed on my blog in the news section a link named Chat With Me! The idea came initially from Tudor Galos and then I found a tutorial for how to setup this to work for your site here: Windows Live Messenger IM Control & Presence API - conversations from Web to Client & querying presence.

The HOL went just fine. I've been able to go through all subjects except the new DATE/TIME data types (I don't know exactly how we missed them).

Some pictures from the HOL:

This is a picture taken at the end of the HOL:

 

And some resources for the questions asked:

Administering Servers by Using Declarative Management Framework

Using hierarchyid Data Types (Database Engine)

 

What do you think will happen after running the next code:

EXEC sp_configure 'clr_enabled',0
RECONFIGURE
GO

Answer: Disabling SQLCLR.

Really?

Not quite. If you read Books Online it says:

"Use the clr enabled option to specify whether user assemblies can be run by Microsoft SQL Server."  and also "Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling. Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Declarative Management Framework."

So SQLCLR cannot be disabled because system data types (such as HierarchyID, Geometry, Geography), Declarative Management Framework and Change Data Capture need CLR. 

Why?

For start - HierarchyID, Geometry and Geography data types are implemented as CLR User-Defined types.

Then, if you watch the replay for the June CTP webcast about Declarative Management Framework you would have a chance to see its architecture and why it needs CLR:

To conclude, you can enable/disable CLR user-written code but not SQLCLR itself.

Bob Beauchemin has a series of posts on this topic if you want to read more:

SQLCLR and system functionality in SQL Server 2008 - part 3
SQLCLR and system functionality in SQL Server 2008 - part 2
SQLCLR and system functionality in SQL Server 2008 - part 1

If you don't have TechNet or MSDN subscribtions you can download evaluation versions from the following links:

Windows Media Video archive: Download

Format: wmv
Duration: 00:02:46

Don't have hardware? Wanna play with SQL Server 2008 ? Then here's the answer:

TechNet Virtual Lab: Working with the new DATE datatype in SQL Server 2008

Besides that, you may come to our SQL Server 2008 Hands-On Lab on 12 November.

Do you like Powershell? If the answer is yes, you'll be happy to know that SQL Server 2008 will benefit from Powershell by introducing a provider for the relational engine and policy management.You will be able to use Powershell for executing T-SQL scripts and policy checks.

Trying to use Surface Area Configuration on a SQL Server 2008 CTP I found out a bug. Searching for a resolution I found out more about Powershell on the Connect Site here: MSIT - SAP: Build 1075.06 Pre CTP5;Unable to Configure SQL SAC

[quote]

The Surface Area Configuration tool is being removed in SQL2K8. The existing functionality will be replaced by the SQL Server Configuration Manager (for service and remote connection configuration), SQL Server Management Studio (using DMF for feature level configuration) and PowerShell for scripting support. This functionality will show up in future CTPs. In the meantime, the SQL Configuration Manager can be used in current CTPs to configure servers and remote connectivity for the DB engine. Management Studio can be used for configure features of the DB engine (via sp_configure) and Analaysis Services (via the GUI for server properties). Reporting Services can be configured using the RS configuration file.
[/quote]

More about Powershell and SQL Server 2008 here: Powershell and SQL Server's 2008 SMO - the reprise

New to Windows Powershell? Then you can use this offer: Free Windows Server 2008 e-book and Microsoft Press newsletter

[quote]

This e-book includes excerpts from two recent publications from Microsoft Press:

Introducing Windows Server 2008 by Mitch Tulloch with the Microsoft Windows Server Team (ISBN: 9780735624214)
Usage scenarios, Windows Server virtualization, Managing Windows Server 2008, Managing server roles, Windows Server core, Additional resources

Microsoft Windows PowerShell Step by Step by Ed Wilson (ISBN: 9780735623958)
Overview of Windows PowerShell, Using Windows PowerShell cmdlets, Leveraging PowerShell providers.

Additional chapters from Introducing Windows Server 2008 will be added to the site periodically, so be sure to come back and read more from expert Mitch Tulloch and the Microsoft Windows Server Team.
[/quote]

From Microsoft Elearning:

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform - In this 1 hour on-line clinic, IT Professionals will learn about the new features in SQL Server 2008 that will help them to secure the enterprise data platform. More...

Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence - In this 1 hour on-line clinic, database developers will learn about the new features in SQL Server 2008 that will help them to improve the Business Intelligence infrastructure. More...

Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development - In this 1 hour on-line clinic, database developers will learn about the new features in SQL Server 2008 that will help them to develop database applications.More...

Clinic 7045: What's New in Microsoft SQL Server 2008 -

In this clinic, you will learn about the new and enhanced features included in SQL Server 2008. You will explore the new data types and the data management features. Additionally, you will learn about the enhanced Integration Services, Analysis Services, and Reporting Services included in SQL Server 2008. This online clinic is composed of a rich multimedia experience. To get the most out of this clinic, it is recommended that you have: Knowledge of general SQL database concepts that are larg More...

Clinic 6448: Understanding the Differences Between Microsoft SQL Server 2005 Standard and Enterprise Editions  - This online clinic explains to Database Administrators and Database Developers how SQL Server 2005 Enterprise Edition provides features and functionality to enable Server Consolidation, mission critical OLTP applications, Data Warehousing, Complex Analytics, Data Mining and Reporting.More...

 

SQL Server 2008 introduces a new hint, the FORCESEEK hint that allows to "kindly" suggest Query Optimizer to use a seek operation instead of a scan. You can use this either as a table hint or in a query plan as a query hint.

So far so good, it was late last night when I started to test it. And fatique causes not just car accidents but also big SQL mistakes. The Books Online samples worked just fine:

USE AdventureWorks;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO 

But no, I wanted an example that it's simpler.

1. MISTAKE #01: 

So I tried the next query:

USE tempdb;
GO
CREATE TABLE T(I INT PRIMARY KEY NOT NULL);
GO
INSERT INTO T VALUES (1),(2),(3);
GO
SELECT *
FROM T WITH (FORCESEEK);

The result was normal:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Dummy, FORCESEEK (f-o-r-c-e  s-e-e-k) , it needs a search condition (s-e-e-k). So if I run

SELECT *
FROM T WITH (FORCESEEK)
WHERE I=1;

it will work. But in this case the Query Optimizer uses an index seek anyway so the hint is useless.

2. MISTAKE #02:

Question: How can I generate the simplest query that the QO would execute as a scan and I can use the FORCESEEK operator to change its behavior?

WRONG QUESTION!

The right question is: How to generate a seek that QO decides to implement as scan, and FORCESEEK to help you eliminate a possible deadlock or other problems caused by the QO decision? Usually the answer is not a simple query. (Thanks Remus for sorting this out).

So I learned the lesson that you should test features considering their purpose, not yours Cool

 

Known as Mr Service Broker, Remus Rusanu or rremus as we know it on our forum has moved his blog at this address: http://rusanu.com/blog/.

Who is Remus? If you attended our meeting last year you probably know him (on the left in this picture):

If not, he worked on Service Broker and very important you'll have a chance to meet him in December when we will organize another meeting at Microsoft local office (at GTSC).

Just a click can get you an XBOX 360.