Tuesday, November 23, 2010

Singapore SQLUG on 2nd Dec 2010:: Special guest from SQL Server Team


We have SQL Server User Group Meeting on 2nd December 2010 at Aventus Training (Tanjong Pagar)


The Future of SQL Server

I believe that there are many of you are busy migrating. Moving and upgrading from previous version to SQL Server 2008, and some of you already using SQL Server 2008 R2. There are tons of R2 features we can count on, especially on BI part. We already have countless meeting in the past talking about those BI enhancements, its benefit, and how does it improve and impact our development. Now it’s time for reflection. What we have done in the past. What we have achieved, and what are our expectation for the future. Are there any features you want to include in the next release? Do you have any idea on how to make this platform better in the future?

Come and join us with 2 executives from SQL Server product team in our next meeting. This is the right time to hear about strategic direction of SQL Server directly from people who made the product. You may have some concerns, wish list and feedback. Nobody knows better other than us on how SQL Server performs in daily production. Our feedback will be very valuable for the product team to make necessary improvements.

Bring your boss too; he/she may have strategic idea that will be valuable for our meeting.

We will discuss the following topics:

  • SQL Server achievements and its market position
  • Who and how “big” customer uses SQL Server
  • Latest benchmark on SQL Server, focus on high availability and performance
  • Long-term SQL direction
  • An overview of Denali, the future of SQL Server
  • Get feedback from users on SQL needs

Speakers profile:

Sameet Agarwal
General Manager
SQL Server RDBMS Development
Microsoft Corporation

Sameet Agarwal is the General Manager of the SQL Server RDBMS Team. His team is responsible for delivering all the core database technologies like T-SQL Language, Security, Query Processing and Data Warehousing, High Availability, Replication, Indexing, Transactions and Recovery, Storage management, Connectivity, etc. His team is also responsible for innovation in technologies to exploit commodity hardware for scale, large memory and multi-core, solid state drives, etc. Sameet has been associated with the SQL Server team in a variety of roles for the past 15 years with key contributions in the design of the logging and recovery subsystem, transactions, access methods, etc. In his most recent role, Sameet was Product Unit Manager of the Relational Engine for the SQL Server 2008 release with features for parallelism and optimization for large scale data warehousing, resource governor, spatial and fulltext support.

Amit Shukla
Principal Group Program Manager
SQL Server RDBMS Development
Microsoft Corporation

Amit Shukla has worked on database technologies for the past 15 years, the last 8 of them on SQL Server, and the last four of these years have been spent working on the core database product itself. He is currently a Group Program Manager in the SQL RDBMS team where he is working on a variety of features and technologies. Before Microsoft he worked at three startups one after the other, all of which have since gone under, though the experiences from the startups continues to endure.

Meeting details:

Date: December 2nd, 2010
Time: 7:00PM – 8:30 PM
Registration starts at 6:30 PM
Venue: Avantus Training
79 Robinson Road #15-04
Singapore 068897

Direction: take MRT and alight at Tanjong Pagar, CPF building is at the opposite of Tanjong Pagar MRT.

Please confirm your attendance by sending email to SGSQL@LIVE.COM

Get in touch with DENALI: the next version of SQL Server!

C u there on 2nd December at 6:30 PM. If there is any doubt, let me know.


Saturday, November 20, 2010

SQL Server Team based Development


Red-Gate has another excellent book on SQL Server Team based development by Phil Factor, Grant Fritchey, Alex Kuznetsov, and Mladen Prajdić

This book shows how to use of a mixture of home-grown scripts, native SQL Server tools, and tools from the Red Gate SQL Toolbelt, to successfully develop database applications in a team environment, and make database development as similar as possible to "normal" development.

Following are the topics covered in this book:
Chapter 1: Writing Readable SQL
Chapter 2: Documenting your Database
Chapter 3: Change Management and Source Control
Chapter 4: Managing Deployments
Chapter 5: Testing Databases
Chapter 6: Reusing T-SQL Code
Chapter 7: Maintaining a Code Library
Chapter 8: Exploring your Database Schema
Chapter 9: Searching DDL and Build Scripts
Chapter 10: Automating CRUD
Chapter 11: SQL Refactoring



Friday, November 19, 2010



SQL SERVER next version DENALI's CTP is out. Here are the details:

Microsoft SQL Server code-named “Denali” empowers organizations to be more agile in today’s competitive market. Customers can efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and new user experiences expand the reach of BI to enable meaningful insights. With SQL Server code-named “Denali” customers will benefit from the following added investments:

  • Enhanced Mission-Critical Platform: an enhanced highly available and scalable platform.
  • Developer and IT Productivity: new innovative productivity tools and features.
  • Pervasive Insight: expanding the reach of BI to business users and end-to-end data integration and management.

SQL Server code-named “Denali’ continues to magnify the each of  BI through highly interactive web-based visualization and storyboarding providing breakthrough performance, common models and IT manageability. With end-to-end data integration and management tools, IT can help deliver consistent, credible data to the right users at the right time. The following highlights a few key enhancements for pervasive insight.
Expand the use of BI to business users
  • Business users will gain greater agility and empowerment with a new highly interactive, web-based data exploration and visualization solution. Introducing new and engaging ways to discover insights through a drag-and-drop interface, smart and powerful data querying and interactive storyboarding to allow users to quickly create and share visual presentations of large datasets.
Experience breakthrough performance with in-memory analytics, and drive alignment across the organization
  • Provide breakthrough analytics performance from the desktop to data center through the Vertipaq in-memory column store technology. Also benefit from next-generation semantic model for both reporting and analytics that span your business from personal BI solutions to the most demanding organizational BI needs.
Enterprise data integration management
  • SQL Server code-named “Denali” will introduce a holistic data integration and management solution that will help organizations ensure the right data is delivered to the right users at the right time. New to “Denali” are two key components to data management, Data Quality Services for knowledge-driven data cleansing and Impact Analysis and Lineage.
    New tools and capabilities will help developers build innovative applications with reduced time-to-market while IT professionals benefit from greater operational control and ease of use. The following highlights a few key enhancements for increased IT and Developer productivity.
    IT administration enhancements
    • Security enhancements will help improve compliance by using auditing for all editions of SQL Server and more easily and effectively manage separation of duties using fine-grained server roles. Meanwhile supportability improvements will help simplify application testing and minimize errors with application changes and upgrades using Distributed Replay. In addition, Extended events dramatically streamline performance troubleshooting with new profiling information and a new user interface.
    Beyond relational enhancements
    • Build more flexible, innovative applications with rich capabilities and data patterns with faster performance for FileStream and Full-Text Search, 2D spatial support, and FileTable.
    Unified developer experience, SQL Server Tools code-named “Juneau”
    • Accelerate time-to-market for developers with a single environment for developing database, BI and web solutions. New capabilities include a unified environment for SQL Server development, Declarative Database Definition, Dynamically Edit Existing Databases, Round-Trip Code Refactoring, and Targeted Database Deployment.
      SQL Server code-named “Denali” provides a highly available and scalable platform with greater flexibility, ease of use, lower TCO, and the performance required by the most mission-critical applications. The following highlights a few key enhancements for a mission-critical platform.
      SQL Server AlwaysOn
      • The new SQL Server AlwaysOn will provide a set of capabilities to help businesses maximize uptime of their mission critical applications, simplify high availability deployments and provide better returns on hardware investments. AlwaysOn supports multi-site clustering and the new availability group option will help improve availability of databases by supporting multiple secondaries, increase utilization using active secondary and simplify HA management. In addition, Windows Server Core support will help reduce unplanned downtime related to OS patching with ~50-60% few OS reboots.
      Column-based query accelerator
      • Column-Based Query Accelerator will help dramatically increase query performance ~10x and reduce performance tuning through interactive experiences with data for near instant response times and streamlined setup which removes the need to build summary aggregates.

        Click here to Download the CTP.


Friday, October 29, 2010

Monitoring and Tuning Parallel Query Execution + Performance Monitoring and Tuning from www.SQLWorkShops.Com


I am sure we all are aware of www.SQLWORKSHOPS.com and Mr. R Meyyappan.

SQL Bits VI recorded sessions are available at: http://www.sqlbits.com/information/event6/PublicSessions.aspx and we especially recommend you to watch 'Performance Monitoring & Tuning - Documented Tips & Tricks', the content is not part of our previous webcasts.

Monitoring and Tuning Parallel Query Execution: http://videos.sqlbits.com/event6/Monitoring%20and%20Tuning%20Parallel%20Query%20Execution_w.wmv

Performance Monitoring and Tuning - Documented Tips and Tricks: http://videos.sqlbits.com/event6/Performance%20Monitoring%20and%20Tuning_w.wmv

All my past SQL Bits sessions are available at: http://www.sqlbits.com/Speakers/R_Meyyappan/Default.aspx

These are very helpful sessions, please spare some time and watch the same.

Happy SQL Coding.


Friday, October 22, 2010

Southeast Asia SharePoint Conference 2010

Event Name: Southeast Asia SharePoint Conference 2010

Location: Waterfront Conference Centre at Grand Copthorne 

392 Havelock Road, Singapore - 169663
+65 6733 0880

Event Date: 26th & 27th Oct 2010

Keynote Announced! - Join Mike Fitzmaurice to hear about "The Evolution of SharePoint from Two Pet Projects to a Multi-Company Ecosystem"

Organiser: The Southeast Asia SharePoint Conference is organised by a group of dedicated SharePoint MVPs and Community Leaders from the Asia Pacific Region who participate and support the community through organising and sharing their knowledge at user group events.

How did SharePoint start and how did it become the centre of the universe in under a decade? Mike Fitzmaurice was there at the beginning, has worked on every product with the word “SharePoint” in it, and continues to work on SharePoint technology today, in a former position for Microsoft and now for a SharePoint partner ISV. He will share with you the motivations behind SharePoint’s creation and evolution, the realities behind how the products ship, why SharePoint caught on so completely, and how SharePoint has grown into something far beyond a single product from a single company.

Fees: Regular Registration fee is $SGD400.00.

This is is the conference to learn about SharePoint 2010 with expert local and international speakers presenting on topics that will help you understand and succeed with your SharePoint implementations and add real value to your organisation and businesses.

In addition to the session content you'll have ample opportunities to network with your peers in the community and technical and industry experts including local and international Microsoft Most Valued Professionals and Southeast Asia's top Certified Partners.

The wider Southeast Asian SharePoint Community (MVPs, User Group Leaders, Partners and Customers) are also valued contributors to the event.

The SharePoint Conference is intended for a wide audience and has content to cover roles such as:

  • Business Roles like End Users, Business Champions, Power Users and Business Managers

  • Information Architects, Record and Information Managers IT Managers, CIO and Decision Makers

  • Technical Roles including Developers, IT Pro, Systems Administration and DBA's Microsoft

  • Technologies covered during the conference include:

  • SharePoint Foundation 2010 SharePoint Server 2010

  • SharePoint Designer 2010

  • Integration with Microsoft Office, InfoPath, Excel, Access and Visio

  • Integration with 3rd Party products enhancing SharePoint functionality

  • Hope you can attend this.



    7 Free .NET Development and Architecture E-books »


    Here are some links to download Free .Net Development and Architecure E-books.

    Foundations Of Programming: The Foundation Of Programming Series Free e-book By Karl Seguin is one of the best book. It is simple, short and sweet. It gives a better thought process – that’ll definitely enable to code better and think better. This book covers the ALT.NET Philosophy, Domain Driven Development concepts, DI, TDD etc in a nice way. Download it

    Microsoft Application Architecture Guide, 2nd Edition: An essential read for any Microsoft.NET developer or Architect to understand the underlying architecture and design principles and patterns for developing successful solutions on the Microsoft platform and the .NET Framework. This guide neatly covers popular architecture patterns, best practices and common challenges you need to understand for developing .NET applications. Get a good grip on developing enterprise applications in .NET. Download it

    Rob Miles C# Yellow Book 2010: A nice action packed book that takes you through C# and .NET concepts. This book explains C# language and .NET library in general – with a clear focus on implementation thought process and best practices.
    Rob Miles CSharp Yellow Book 2008.pdf
    Rob Miles CSharp Yellow Book 2009.pdf
    Rob Miles CSharp Yellow Book 2010.pdf

    Threading in C#: A short, neatly written book from Joe Albahari about Threading in C#. This is a must read for any .NET programmer to understand more about threading in general, thread pooling, synchronization, Non blocking synchronization, etc. In this book, Joe even covers the Parallel Framework Extensions and Parallel programming in .NET.

    Improving .NET Application Performance and Scalability: Microsoft guide, which focuses on designing your applications with Performance and scalability in mind. It has sections relevant to architects, developers, testers, and administrators. Following the checklists and guidance in this book will ensure that there won’t be any unpleasant surprises in the end. Read this guide if you develop Enterprise applications in .NET.
    Download it

    Applying Design Patterns: A quick introduction towards the thought process of applying design patterns. The objective of the book is to introduce design patterns in a simple way, so that developers can understand some common patterns and how to apply them.
    RefCardz from DZone: DZone has a number of awesome Quick reference sheets on various technologies. Registration is required to get these reference sheets.
    Getting started with WCF 4.0
    Getting Started With Silverlight + Expression Blend
    Essential F#

    Enjoy & Happy Coding


    Thursday, September 2, 2010

    Register for Level 300-400 Microsoft SQL Server 2008 Performance Monitoring and Tuning Seminar

    Hello Friends,

    SQLWORKSHOP is organizing a free Level 300-400 Microsoft SQL Server 2008 Performance Monitoring and Tuning Seminar on Sept 9th.

    Click here to register for the Seminar.

    Event Overview

    Registration as of 08:30h, Start of Seminar 09:00h, End 16:15h.
    Goal of the Seminar: To provide the database developers and administrators with knowledge on how to monitor and tune performance of Microsoft SQL Server 2008 and SQL Server 2005. The seminar addresses CPU, Memory, Disk, Query and Lock monitoring and tuning techniques.

    Target Group

    Developers, Database Administrators and Database Architects. This seminar is equally designed for application developers and database administrators.


    Some experience administering or developing applications with SQL Server.


    Ramesh Meyyappan (www.sqlworkshops.com) with more than 15 years of SQL Server expertise including working in the past as a Program Manager in the SQL Server Development Team at Microsoft Corporation. He is specialized in SQL Server Performance Monitoring, Tuning & Troubleshooting and SQL Server Administration and Tuning for SAP installations.


    08.30 – 09.00 Registration
    09.00 – 10.15 Monitoring and Tuning Queries and Configuration to better utilize CPU, Memory and Disk and to reduce Total Cost of Ownership
    10.15 – 10.45 Break
    10.45 – 12.00 Monitoring and Tuning Queries and Configuration to better utilize CPU, Memory and Disk and to reduce Total Cost of Ownership
    12.00 – 13.00 Lunch
    13.00 – 14.15 Monitoring and Tuning Query Execution and Locking issues
    14.15 - 14.45 Break
    14.45 – 16.00 Monitoring and Tuning Query Execution and Locking issues
    16.00 – 16:15 Q and A

    Happy Coding


    Level 400 Performance Monitoring and Tuning Webcast


    Recently SQLWORKSHOPS organized Level 400 Performance Monitoring and Tuning Webcast. Here is the link to download the webcast.

    The agenda for the Webcast is:

    Webcast 1: Explains Memory allocation issues with sort. Demonstrates ways to identify sort spills to tempdb. Provides query rewrite procedure to avoid sort spills to tempdb. Demonstrates cases where 1 tempdb date file per core might not be ideal for all implementations.

    Webcast 2: Explains high CXPACKET waits are NOT a direct result of delays associated with inefficiencies of parallel processing. Provides example to scale queries over many CPU cores without reducing MAXDOP settings.

    Webcast 3: Recommends not using stored procedure or other plan caching mechanism like using sp_executesql and Prepared Statement using ADO.NET or OLEDB based executions for memory allocating queries. Common memory allocating queries are that perform Sort and do Hash Match operations like Hash Join or Hash Aggregation or Hash Union.

    With examples provides ways to identify queries performing Hash match operations that spill to tempdb. Using SQL Profiler: Hash Warnings (Hash Recursion and Hash Bailout). Using sys.dm_exec_query_memory_grants: Granted Memory, Used Memory and Maximum Used memory. Explains how SLEEP_TASK wait type is associated with Hash Warnings.

    Explains with examples how sp_recompile can block and bring an application to a standstill and recommends using DBCC FREEPROCCACHE (plan_handle) instead.

    Explains with examples how stored procedure or other plan caching mechanism affects queries that sort (perform order by). Explains how IO_COMPLETION wait type is associated with Sort Warnings.

    Demonstrates why rollbacks and database restore could wait on IO_COMPLETION wait type and if needed how this wait can be reduced.

    Explains how parallel query performance is significantly affected by a CPU intensive query executing on one of the CPU cores. Explains the reason for the performance issue and how to identify the issue with SLEEP_TASK wait type. With example explains the reason for the observed query execution time when the child thread executes on the CPU core where the CPU intensive query executes and also when the coordinator executes on the CPU core where the CPU intensive query executes. Recommends ways to avoid this parallel sort query performance issue and also demonstrates that only certain queries will be affected by this and not all parallel queries.

    Explains sys.dm_os_waits_stats.signal_wait_time_ms does not indicate system wide CPU pressure, just at a CPU core level.

    Explains the prefetch mechanism and how it can affect query performance. Provides ways to force prefecth. Demonstrates cases where Avg. Disk Sec / read, the disk latency, PAGEIOLATCH_SH, Avg. disk queue length is very high and the query executes fast.

    Explains why Plan Guides (Plan Freezing!) cannot help with prefetch or in few other cases.

    The Goal of these webcasts is to help reduce Total Cost of Ownership (TCO), by efficiently utilizing existing investment.

    Do let me know if you face any problem downloading the files.

    Happy Coding


    Thursday, August 12, 2010

    24 Hours of PASS: Summit Preview

    Hi Guys,

    I am sure you would have attended 24 hours of PASS last time. They are coming up with another 24 hour event in September (15 & 16).

    Here are the excerpts from PASS.

    Join an exceptional lineup of SQL Server and BI experts from around the world as they present a sneak peek of PASS Summit 2010 in 24 free live webcasts September 15-16.

    This installment of 24 Hours of PASS delivers the hottest SQL Server and BI content directly to your computer – with a twist. Instead of running continuously for 24 hours, the September event divides the webcasts across 2 days, with each day beginning at 12:00 GMT (UTC). Check out what time it starts in your neck of the woods.

    Dig into Data Warehousing, MDX, KPIs, and DTS-to-SSIS migration. Learn best practices for sizing database hardware and performance tuning. Get an in-depth look at PowerShell, storage for the DBA, and the latest T-SQL enhancements. And see how to manage teams, make your presentations rock, prepare for your next job, and more.

    Click here to register

    Here are the details about all sessions:

    • Session 01: Peter Ward - I AM A DBA – WHY SHOULD I CARE ABOUT SQL SERVER 2008 R2? SQL Server 2008 R2 isn't a minor release for the Database Engine. We’ll look many new R2 features for the DBA, including improvements in locking and merging partitions and Unicode compression.
    • Session 02: Allen White - GATHER SQL SERVER PERFORMANCE DATA WITH POWERSHELL. Keeping a performance metrics baseline lets you know when you really have a problem. We'll build a PowerShell script to gather metrics from SQL Server and a report to easily monitor your systems.
    • Session 03: Craig Utley - WHY DATA WAREHOUSING PROJECTS FAIL (AND WHAT YOU CAN DO ABOUT IT). Data warehousing projects, like many large IT projects, have high failure rates. Learn the main reasons behind these failures and how to succeed in overcoming the obstacles.
    • Session 04: Glenn Berry - HARDWARE 201: SELECTING AND SIZING DATABASE HARDWARE FOR OLTP PERFORMANCE. This session covers current and upcoming hardware from Intel and AMD and gives you the resources to make better hardware-selection decisions to support SQL Server OLTP workloads.
    • Session 05: Andy Leonard - MANAGING TEAMS. Are you a member of a team of database pros? Are you part of a developer team? Do you manage or lead a team? Andy Leonard shares experiences, war stories, and lessons learned from years of managing and leading teams.
    • Session 06: Ken Simmons - ENFORCING COMPLIANCE WITH POLICY-BASED MANAGEMENT. Learn specific ways you can use Policy-Based Management to help enforce proper server configuration, security, auditing, and encryption as well as creating Alerts to notify you when violations occur.
    • Session 07: Stacia Misner - INTRO TO MDX. Learn how to perform many calculations easier and faster with MDX than T-SQL, how to think about multidimensional space, how to create basic queries, which functions to learn first, and how to construct useful calculations.
    • Session 08: Kalen Delaney - ISOLATION VS CONCURRENCY: WHAT ARE THE CHOICES? In this presentation, we’ll look at the relationship between isolation level and concurrency models to see how the various isolation levels work in either an optimistic or pessimistic manner.
    • Session 09: Grant Fritchey - IDENTIFYING COSTLY QUERIES. Before you dive into query execution plans, you need to identify the queries and procedures that are causing your system the most pain. In this session, learn several useful ways to gather metrics on query performance.
    • Session 10: Douglas McDowell - HOW TO ROCK YOUR PRESENTATIONS. Just because you’re a technical rock star doesn’t mean your technical presentation is going to rock. Learn best practices for structuring, building and delivering a compelling presentation every time.
    • Session 11: Paul Nielsen - EXTREAME DATABASE DESIGN. Push the data modeling envelope with advanced EAV, temporal attributes, class inheritance, domain classes, decorator classes, and transparent data overlays using patterns that are easy to query with normal SQL.
    • Session 12: Peter Myers - DELIVERING KPIs WITH ANALYSIS SERVICES. See how Key Performance Indicators are defined in SSAS cubes and made available for reporting, where the KPI sources its target data, and how to use the new Excel 2010 What-If Analysis feature to collect data.
    • Session 13: Brian Knight - UPGRADING DTS PACKAGES TO SSIS. In this level-200 session, see why you should upgrade from DTS to SSIS, options for converting, how to add value to packages during the conversion process, and how to assess the scope of the project (time, costs).
    • Session 14: Kevin Goode - STATISTICS: HOW TO PROVE EVERYTHING BUT THE TRUTH. Statistics are a key component of tuning SQL Server. This session examines the internals of statistics and explains what ones are available, how they’re used, and what causes them to be recomputed.
    • Session 15: Marco Russo - SQLBI METHODOLOGY. SQLBI Methodology is a design and implementation guide to model and build a BI solution using SQL Server, Integration Services and Analysis Services, providing greater flexibility in terms of design and maintenance.
    • Session 16: Chris Shaw - PREPARING FOR YOUR NEXT JOB. Review interviews I’ve conducted and the mistakes that inspired me to continue looking for the right candidate. The goal of this session is to learn from others’ mistakes so that we can put our best foot forward.
    • Session 17: Denny Cherry - STORAGE FOR THE DBA. Storage is one of the biggest pain points in SQL Server performance. After this session, you will understand how to talk to your storage admin to get the best performance from your storage environment.
    • Session 18: Klaus Aschenbrenner - SQL Server 2008 R2 STREAMINSIGHT. Learn how to use Microsoft’s new complex event-processing runtime to analyze large data streams in real time and how to develop with StreamInsight and extend StreamInsight-based apps with your own adapters.
    • Session 19: Jen McCown - T-SQL BRUSH-UP: THE BEST THINGS YOU FORGOT YOU KNEW. You're a good SQL Server pro, but sometimes a piece of T-SQL falls out of memory. Revisit old favorites and brush up on new and improved T-SQL features like MERGE, OVER, and PARTITION BY.
    • Session 20: Brad McGehee - AUTOMATE YOUR DATABASE MAINTENANCE USING OLA HALLENGREN’S FREE SCRIPT. See how to implement Ola Hallengren’s free database maintenance script, which makes it easy for all DBAs—novice or experienced—to perform optimal database maintenance.
    • Session 21: Kevin Kline - RELATIONAL DATABASE DESIGN FOR UTTER NEWBIES. Investing a little time and effort into your design early will yield big benefits by providing a database that is self-documenting, easy to maintain and alter, and provides high-quality data.
    • Session 22: Adam Jorgensen - ZERO TO CUBE: FAST TRACK TO ANALYSIS SERVICES DEVELOPMENT. Come get hands-on with Analysis Services as we build a fully functional OLAP cube in 1 hour with all the bells and whistles. Even experienced SSAS developers will learn something!
    • Session 23: Louis Davidson - TOP 10 DESIGN MISTAKES. People do a lot of really stupid things in the name of "getting it done" (I’m not immune myself). The list we’ll cover in this session is simply the 10 most heinous database design mistakes that come up often in the real world.
    • Session 24: Rob Farley - UNDERSTANDING SARGABILITY TO MAKE YOUR QUERIES RUN FASTER. SARGability, the ability to search through an index for a value, is often misunderstood—especially in regard to joins—leading to queries that don’t run as well as they should.
    Register Now.


    Monday, June 7, 2010

    Execute UPDATE STATISTICS for all SQL Server Databases


    Today we will write a simple script which will generate UPDATE STATISTICS for all the database on current SQL Server. Statistics in SQL Server refers specifically to information that the server collects about the distribution of data in columns and indexes. This data is used by the query optimizer to determine the plan of attack for returning results when you run a query. While in the majority of cases SQL Server takes care of this statistical recordkeeping for you automatically, it's useful to have some understanding of what's going on, and to know when you might need to intervene.

    Automatic Statistics
    By default, SQL Server databases automatically create and update statistics. Although you can disable this (by setting a property of the database), you should almost certainly leave it on. SQL Server needs these statistics to do a good job with query processing, and it's likely to be smarter than you about what and when to update. The information that gets stored includes:

    • The number of rows and pages occupied by a table's data
    • The time that statistics were last updated
    • The average length of keys in a column
    • Histograms showing the distribution of data in a column
    • String summaries that are used when performing LIKE queries on character data
    SQL Server maintains statistics on index and key columns of all of your tables - in other words, the columns that the query processor is able to use in various ways in queries depending on the data that they contain.

    SQL Server's engine will update the statistic when:

    • When data is initially added to an empty table
    • The table had > 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records since that collection date
    • The table had < 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records + 20% of the row count from the previous statistics collection date
    Below is a simple code that should work on all SQL Versions

    Set nocount on
    DECLARE @minId int, @maxId int
    DECLARE @DB Nvarchar(100)
    Create table #tmp
    ID int Identity,
    Name varchar(100)
    INSERT INTO #tmp
    SELECT [name] 
    FROM master..sysdatabases 
    WHERE [name] NOT IN ('model', 'tempdb') 
    ORDER BY [name] 
    Select @minId = 1, @maxId = count(1) from #tmp
    While @minId <= @maxId
    SELECT 'Use ' + name + CHAR(13) + 'Exec sp_UpdateStats ' from #tmp Where Id = @minId
    Set @minId+=1
    drop table #tmp
    This code yields following results:

    Use AdventureWorks
    Exec sp_UpdateStats

    Use AdventureWorksDW
    Exec sp_UpdateStats

    Use LearningCT
    Exec sp_UpdateStats

    Use master
    Exec sp_UpdateStats

    Use msdb
    Exec sp_UpdateStats

    Use Northwind
    Exec sp_UpdateStats

    Use ReportServer$MSSQL2008
    Exec sp_UpdateStats

    Use ReportServer$MSSQL2008TempDB
    Exec sp_UpdateStats

    Use SensitiveDB
    Exec sp_UpdateStats

    Use testdb
    Exec sp_UpdateStats

    Now copy this text & paste it into a query window in SQL Server Management Studio, then execute it.

    When you execute the code, you will see the output like this:

    This shows which all indexes are updated.

    Happy SQL Coding

    Thursday, May 27, 2010

    Download Defensive Database Programming e-book


    Recently, Alex Kuznetsov has written a book "Defensive Database Programming". You can download it free from Red Gate

    Here are the contents of the Book:

    Chapter 1: Basic Defensive Database Programming Techniques
    Chapter 2: Code Vulnerabilities due to SQL Server Misconceptions
    Chapter 3: Surviving Changes to Database Objects
    Chapter 4: When Upgrading Breaks Code
    Chapter 5: Reusing T-SQL Code
    Chapter 6: Common Problems with Data Integrity
    Chapter 7: Advanced Use of Constraints
    Chapter 8: Defensive Error Handling

    The paid versions of this book contain two additional chapters: Chapter 9, Surviving Concurrent Queries and Chapter 10, Surviving Concurrent Modifications. See the Introduction for further details.

    Its a very interesting book. Download it now & start reading. If you face any problem with download, let me know.

    Happy SQL Coding

    Friday, May 7, 2010

    Invitation to The Future of Productivity Launch at Marina Bay Sands, 26 May

    On May 26, Microsoft Launches The Future Of Productivity

    Your connected enterprise depends on you. However, the continuous onslaught of support incidents, project mplementation, and system updates can stretch you and your team to the limit. Microsoft productivity tools can help you deal with any situation, so that your IT infrastructure runs at optimum conditions. That means being able to access and analyze data easily, coordinate team efforts seamlessly, and automate more administrative tasks, so you can concentrate on the tasks that really matter.

    On May 26, Microsoft breaks new ground with the launch of innovative software that is ready for the work of today and tomorrow.  Come to the Future of Productivity Launch at the much anticipated Marina Bay Sands, and discover how to connect and empower your people to attain competitive advantage:
    With Singapore’s focus on productivity as a key economic strategy, the time is right to explore new and innovative solutions that will impact employee motivation, boost work productivity, and accelerate business growth.

    Come to the Future of Productivity Launch!

    In today’s 24x7 world, information workers require the right productivity tools to support them in achieving great results. Register for this event and discover how to perform smarter and increase your effectiveness!
    Happening at the new Marina Bay Sands Integrated Resort

    To support Singapore’s drive for greater efficiency, Microsoft launches a suite of productivity tools for today’s information workers. Join us for a one day event as we showcase the latest from Microsoft. Learn how to adopt time-saving measures, remove barriers to communication, and achieve organisational productivity gains.


    • Learn through live demos on how Microsoft solutions work together to optimize the Business Productivity Infrastructure
    • Hear from peers and partners who are already seeing benefits to their business by leveraging the Future of Productivity solutions.
    • Experience the latest products and offerings at the Solutions Showcase brought to you by Microsoft’s ecosystem of partners
    • The event will commence with a keynote morning session, followed by breakout track sessions in the afternoon:


    Click here to view the full agenda and register for the event now!

    Hurry up & Register Now!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Entrepreneurial dreams with Microsoft Cloud Services contest

    Hi Guys,

    Microsoft is running a contest on AZURE. Here are the details of the content.

    Click here to register:

    Microsoft Web Camp in Singapore on June 4 and 5

    Hello Everyone,

    Are you interested in learning how new innovations in Microsoft's Web Platform and developer tools like ASP.NET 4 and Visual Studio 2010 can make you a more productive web developer?

    If you're currently working with PHP, Ruby, ASP or older versions of ASP.NET and want to hear how you can create amazing websites more easily, then register for a Web Camp near you today!

    Microsoft's Web Camps are free, two-day events that allow you to learn and build on the Microsoft Web Platform. At camp, you will hear from Microsoft experts on the latest components of the platform, including ASP.NET Web Forms, ASP.NET MVC, jQuery, Entity Framework, IIS, Visual Studio 2010 and much more.

    Web Camps also provide the opportunity to get hands on with labs and get creative by building in teams. All this with Microsoft experts on hand to guide you through.

    The goal for Web Camps is to give you a jumpstart training on building rich web applications using the latest innovations from the Microsoft Web Platform.

    Location: Microsoft Auditorium, Level 21 NTUC Centre, One Marina Boulevard.
    Microsoft Singapore

    Here’s the planned AGENDA for Day 1 of the event:

    08:30-09:00am - Arrival and Beverages

    09:00-09:15am - Welcome and agenda for the event

    09:15-10:30am - A Lap around the Microsoft Web Platform and ASP.NET Overview

    The Web Camp begins with a lap around the Microsoft Web Platform and tools, exploring its components and how they work together allowing you to quickly create powerful standards-based Websites that offer rich end-user experiences. We'll also learn how the Web Platform Installer makes installing and platform and exciting applications super simple.

    We'll also take a look at ASP.NET and how it offers developers a choice when building rich, standards-based web applications with ASP.NET Web Forms and/or ASP.NET MVC.

    10.30-10.45am - Break

    10.45-12:00pm - Building a rich web application

    Part 1: Getting started with MVC & Entity Framework

    This is the first of three sessions in which we will walk through building a simple application. We'll learn how easy and powerful the Microsoft Web Platform and how it is greater than the sum of its parts.

    In Part 1, we'll get started building our application in Visual Studio 2010, ASP.NET MVC and the Entity Framework. To start we will learn some of the fundamentals of ASP.NET MVC including the Model-View-Controller approach and how it works. Next, we will learn how the Entity Framework makes creating data-driven web applications really easy by providing a powerful ORM that we can leverage throughout our application.

    12:00-01:00pm - Lunch

    01:00-02:15pm - Building a rich web application

    Part 2: Deeper into MVC and new features

    With our application’s data layer in place, in Part 2 we'll learn how our data models are used throughout ASP.NET MVC and make it easy to wire up data to business logic and UI to quickly get it up and running. We'll explore how new features in ASP.NET MVC 2 simplify data validation and where HTML Helpers make coding against our model easier with templated and strongly-typed helpers. In addition, as our application grows in size and complexity we want to ensure the code is well organized and also reusable – we can use new features like Areas and RenderPartial in ASP.NET MVC 2 for this. We’ll also touch on a few new features in Visual Studio 2010 that help us be more productive when finding and using our code.

    02:15-03:30pm - Building a rich web application

    Part 3: jQuery and simplified web application deployment

    In Part 3, we will explore techniques to add “sizzle” to your web application on the client-side with jQuery and its powerful selection engine, network stack and animations. All of this is made possible by ASP.NET MVC on the server side and we’ll also take a look at how the two work together with JSONResult and how we can use jQuery for validation too. With the application taking shape, we’ll learn how to take the pain away from deployment with Visual Studio 2010 and Web Deploy including new features like publishing profiles, multiple configuration settings and options for database migration that make life simple.

    03:30-03:45pm - Break

    03:45-04:30pm - Getting the most out of IIS with IIS Extensions and wrap up for the day.

    To wrap up the day, we'll explore how to get the most of IIS with IIS Extensions like the SEO Toolkit, URL Rewriter and Database Manager and also give an overview of programs like WebsiteSpark that provide tools and servers for no upfront cost.

    04:30-05:00pm - Ask the expert Questions

    05:00-06:00pm - InstallFest and Day 2 Agenda

    Day 2 is available to get our hands dirty with labs (to be installed on your own laptop) and also to work building apps in teams. The best apps at the end of the day will win prizes!

    To view complete agenda, click here

    If you want to attend, then click here to Register now:

    Monday, May 3, 2010

    Community Technology Update 2010 (CTU 2010: Unvealing SQL Server 2008 R2 and Visual Studio 2010)


    Microsoft Singapore is coming up with CTU 2010 on May 22nd, Sat from 9 AM to 5 PM.

    Details are:

    The Singapore user group communities, Professional .NET User Group, SQL Server User Group, and Windows IT Pros User Group, once again, have come together to bring to you a day of in-depth sessions and demonstrations of the new Microsoft SQL Server 2008 R2 and Visual Studio 2010.

    Microsoft SQL Server 2008 R2 is packed with a wealth of new features and capabilities that can help organizations to scale with confidence on a trusted platform, improve IT and developer efficiency, and enable self-service business intelligence. The benefits of the enhanced capabilities extend to application and multi-server management, complex event processing, master data services and powerful BI tools.

    Microsoft Visual Studio 2010 is an integrated environment that simplifies the whole development lifecycle, from design to development. Developers can use existing skills to model, code, debug and deploy a growing number of applications types.

    With SQL Server 2008 R2 and Visual Studio 2010, users will be able to create compelling applications that deliver business value soonest while maintaining the quality and integrity of your IT assets.

    Why should you attend the event:

    To learn how significant new capabilities of SQL Server 2008 R2 to run your most demanding mission-critical applications, increase IT and developer efficiency, and deliver business insight where and when your users need it.

    To learn how Visual Studio 2010 can help you to unleash your creativity to deliver impactful solutions

    Who should attend the event:
    • Database Administrators,
    • IT Professionals, &
    • Developers


    Not yet a member? Join now for free.

    Click here for detailed agenda and register for the event

    For enquiries, please contact Vivian at

    mailto: vivian@rhapsodyconcept.com.sg

    Tel: +65 6296 7769

    Thursday, April 22, 2010

    SQL User Meeting on 29th April 2010

    Singapore SQL Server User Group

    April User Group Meeting

    Topic: Analysis Services 2008: Real time cube and Proactive caching


    SSAS is able to answer to real time BI scenario thanks to pro-active caching. During this presentation, we will see how ESSILOR uses this technology in order automate BI for plants, analysis and financial BI, keeping a detailed view and aggregated view at the same time (Asymmetric Pattern).

    Speaker: Renaud Harduin
    MVP – SQL Server

    Renaud Harduin was Business Intelligence Domain Manager for ESSILOR (www.essilor.com) where he is in charge of reporting projects for Finance, Distribution and Production. Today, he's working on new functional aspects as forecast and operational Scorecard. He is now Technical Manager of Order Management Information System for ESSILOR, and kept a BI expertise for European projects.

    Blog : http://www.dotnetguru2.org/renaudharduin/

    Date: April 29th, 2010
    Time: 7:00PM – 8:30 PM
    Registration starts at 6:30 PM
    Venue: Auditorium 21st Floor, Microsoft Office One Marina Boulevard

    Please confirm your attendance by sending email to sqlugsingapore@mvps.org

    Community Technology Update (CTU)

    The first edition for CTU 2010 will be held on May 2010. Please mark your calendar and make a date with us! We will send you the detail soon. The theme of coming CTU is SQL Server 2008 R2 community launch. We will have special guest coming down to share the latest release of SQL Server.

    Join for the Virtual Launch Event – May 12th

    Office 2010 and SharePoint 2010 will be official launched to business customers on May 12 in New York City with a keynote from Stephen Elop, President of Microsoft’s Business Division. People around the globe can participate in a virtual launch by going to http://www.the2010event.com/. The virtual launch will include product demos, customer and partner testimonials, and interviews with product managers and executives.

    Hope to see you all there.

    Monday, April 19, 2010

    SQL Server 2008 R2 ebook


    As we all are aware that SQL Server 2008 R2 will be released in May. recently a book is released on SQL Server 2008 R2 Microsoft SQL Server 2008 R2, by Ross Mistry and Stacia Misner

    The book is divided into 10 chapters and has total 216 pages:

    PART I Database Administration

    CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements
    CHAPTER 2 Multi-Server Administration
    CHAPTER 3 Data-Tier Applications
    CHAPTER 4 High Availability and Virtualization Enhancements
    CHAPTER 5 Consolidation and Monitoring

    PART II Business Intelligence Development

    CHAPTER 6 Scalable Data Warehousing
    CHAPTER 7 Master Data Services
    CHAPTER 8 Complex Event Processing with StreamInsight
    CHAPTER 9 Reporting Services Enhancements
    CHAPTER 10 Self-Service Analysis with PowerPivot

    How Is This Book Organized?

    SQL Server 2008 R2 is a collection of components that can be implemented either separately or as a group to form a scalable data platform. This book is divided into two parts to focus on the new capabilities for each of these areas.

    Part I, “Database Administration,” is for the DBA's and introduces numerous innovations in SQL Server 2008 R2.

    Chapter 1, “SQL Server 2008 R2 Editions and Enhancements,” discusses the key enhancements, what’s new in the different editions of SQL Server 2008 R2, and the benefits of running SQL Server 2008 R2 on Windows Server 2008 R2.

    Chapter 2, “Multi-Server Administration,” readers learn how centralized management capabilities are improved with the introduction of the SQL Server Utility Control Point. Step-by-step instructions show DBAs how to quickly designate a SQL Server instance as a Utility Control Point and enroll instances for centralized multi-server management.

    Chapter 3, “Data-Tier Applications,” focuses on how to streamline deployment and manage and upgrade database applications with the new data-tier application feature.

    Chapter 4, “High Availability and Virtualization Enhancements,” covers high availability enhancements and includes step-by-step implementations
    for ensuring business continuity with SQL Server 2008 R2, Windows Server 2008 R2, and Hyper-V Live Migration.

    Chapter 5, “Consolidation and Monitoring,” a discussion on consolidation strategies teaches readers how to improve resource optimization. This chapter also explains how to use the new dashboard and viewpoints to gain insight into application and database utilization, and it also covers how to use capacity policy violations to help identify consolidation opportunities, maximize investments, and ultimately maintain healthier systems.

    PART II - “Business Intelligence Development,” readers discover components
    new to the SQL Server data platform, as well as significant enhancements to the reporting component.

    Chapter 6, “Scalable Data Warehousing,” introduces the data warehouse appliance known as SQL Server 2008 R2 Parallel Data Warehouse by explaining its architecture, reviewing data layout strategies for optimal query performance, and describing the integration points with SQL Server BI components.

    Chapter 7, “Master Data Services,” readers learn about master data
    management concepts and the new Master Data Services component.

    Chapter 8, “Complex Event Processing with StreamInsight,” describes scenarios that benefit from complex event analysis, and it illustrates how to develop applications that use the SQL Server StreamInsight engine for complex event processing.

    Chapter 9, “Reporting Services Enhancements,” reviews all the new features available in SQL Server 2008 R2 Reporting Services that support self-service reporting and address common report design problems.

    Chapter 10, “Self-Service Analysis with PowerPivot,” continues the theme of self-service by explaining how users can integrate disparate data for analysis by using SQL Server PowerPivot for Excel, and how to centralize and share the results of this analysis by using SQL Server Power-Pivot for SharePoint.

    Please download the book here:

    XPS format

    PDF format

    Happy SQL Coding.....

    Tuesday, March 30, 2010

    Tom Casey on Microsoft’s BI Strategy


    Let's look at the past, present, and future of BI through a discussion conducted by SQL Server Magazine. They talked with TOM CASEY, Microsoft general manager for SQL Server BI.

    Click here to read the complete discussion.

    Tom Casey on Microsoft’s BI Strategy


    Happy SQL Coding

    Monday, March 29, 2010

    Authentication Modes in SQL Server


    Today we will discuss various authentication modes available in SQL Server. A script that helps to check current Authentication Mode and how we can change the Authentication mode from Registry.

    There are 3 authentication modes in SQL Server:
    • SQL Server
    • Windows Authentication
    • Mixed Mode

    Let's discuss various modes in detail:

    SQL Server Authentication allows a user to connect with a specified login name and password. when a request is received SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

    The authentication diagram as per BOL is:

    Windows Authentication mode allows a user to connect through a Microsoft Windows user account. When a user connects through a Windows user account, SQL Server validates the account name and password by calling back to Windows for the information. SQL Server achieves login security integration with Windows by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated by a Windows domain controller. When a network user tries to connect, SQL Server uses Windows-based facilities to determine the validated network user name. name and password. Login security integration operates over any supported network protocol in SQL Server.

    If a user attempts to connect to an instance of SQL Server providing a blank login name, SQL Server uses Windows Authentication. If a user attempts to connect to an instance of SQL Server configured for Windows Authentication Mode by using a specific login, the login is ignored and Windows Authentication is used.

    Windows Authentication has certain benefits over SQL Server Authentication, primarily due to its integration with the Windows security system. Windows security provides more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.

    Because Windows users and groups are maintained only by Windows, SQL Server reads information about a user's membership in groups when the user connects. If changes are made to the accessibility rights of a connected user, the changes become effective the next time the user connects to an instance of SQL Server or logs on to Windows (depending on the type of change). Note that Windows Authentication Mode is not available when an instance of SQL Server is running on Windows.

    Mixed Mode (Windows Authentication and SQL Server Authentication)
    Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.

    Windows vs SQLServer Authentication
    Windows authentication is generally preferred because it provides an optimal level of integration with Windows 2000. User and group accounts from Windows are granted or denied access to SQL Server. Windows 2000 authenticates the user when the user logs on to the network. Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user.

    Windows Authentication provides following advantages over SQL Server Authentication.
    • Windows Authentication can grant group accounts access SQL Server, thus minimizing the over head of login administration
    • Users are authenticated by Windows, resulting in a secure authentication over a network
    • Users could be able to use the same user credentials for network and database access
    • Audited events can be tracked to a network user.

    Change Authentication mode through Registry:

    We can change the authentication mode from Windows Registry of the machine where SQL Server is installed using following steps:

    Step 1: Let's check what is the current Authentication mode of our SQL Server:

    Step 2: Click Start=>Run and type REGEDIT to open Registry Editor

    Step 3: Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer. There you should be able to see LOGINMODE property.

    Step 4: Double Click on LoginMode property, it will be either 1 or 2, change it.

    Step 5: let's check what's the new/updated Authentication mode. Its already updated to SQL Server & Windows Authentication but it will not work till we restart the SQL Server Service.

    Step 6: Restart SQL Server Service

    Script to check Authentication Mode:

    --SQL 2005 Version

    set nocount on
    DECLARE @SqlPath Nvarchar(255)
    DECLARE @instance_name Nvarchar(30)
    DECLARE @reg_key Nvarchar(500) 
    Declare @value_name Nvarchar(20)
    Declare @LoginMode_Value int
    Declare @LoginMode Nvarchar(15)
    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
    select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
    if @instance_name is NULL
    set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp' 
    set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
    @key=@reg_key, @value_name=@instance_name,
    @value=@SqlPath output
    if @instance_name is NULL
    set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\' 
    set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
    @key=@reg_key, @value_name='LoginMode',
    @value=@LoginMode_Value output
    if @LoginMode_Value = 1
    set @LoginMode = 'Windows'
    if @LoginMode_Value = 2
    set @LoginMode = 'Mixed' 
    select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
    if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
    select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
    if @instance_name is NULL
    set @reg_key = 'Software\Microsoft\MSSQLServer\MSSQLServer' 
    set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer'
    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
    @key=@reg_key, @value_name=LoginMode,
    @value=@LoginMode_Value output 
    if @LoginMode_Value = 1
    set @LoginMode = 'Windows'
    if @LoginMode_Value = 2
    set @LoginMode = 'Mixed' 
    select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode

    Do let me know if you have any comments.