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

Guys,

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


AGENDA:

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.

Enjoy

SQL Server Team based Development

Guys,

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

Download book here

Enjoy

Sudhir

SQL SERVER - DENALI

GUYS,


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.
      

      
      

      Enjoy

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

Guys,

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.

Sudhir

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.

Enjoy

Sudhir

7 Free .NET Development and Architecture E-books »

Guys,

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.
http://www.albahari.com/threading/threading.pdf

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.
http://amazedsaint.net/patterns.pdf
 
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

Sudhir

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.

Prerequisite

Some experience administering or developing applications with SQL Server.

Instructor

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.

Agenda

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

Sudhir

Level 400 Performance Monitoring and Tuning Webcast

Hello,

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

Sudhir

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.

Enjoy

Execute UPDATE STATISTICS for all SQL Server Databases

Guys,

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
Begin
SELECT 'Use ' + name + CHAR(13) + 'Exec sp_UpdateStats ' from #tmp Where Id = @minId
Set @minId+=1
END

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

Download Defensive Database Programming e-book

Guys,



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

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.

Highlights:

• 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:

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

Hi,

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

 This is a not-to-be-missed launch FOR THE COMMUNITY, BY THE COMMUNITY. FREE FOR COMMUNITY MEMBERS, DETAILS ON REGISTRATION SITE.

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

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

Synopsis:

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.

SQL Server 2008 R2 ebook

Guys,

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.....