sqlCrunch

23 New Whitepapers, Articles and Blog Posts...

9 Sep 2009 13:00:00 GMT

In my recently released book, the summary section of each chapter has a link to a page on SQLCrunch.com which contains best practice articles, whitepapers and blog posts appropriate for that chapter. Since the book was released, I've noticed a large number of new subscribers to this RSS feed, so welcome if you're in that category :-)

My last site update was over 3 months ago, so apologies for the delay (I've been busy finishing the book among other things). Since then, a whole pile of tasty links have arrived, summarized here;

------

Understanding and Using Powershell Support in SQL Server 2008

In this article, Allen covers both an introduction to Powershell, and how we can use it to our advantage in administering SQL Server ... Source : Allen White - Technical Article - MSDN.com

Multi-Terabyte Database Backups over the Network

This document discusses the challenges of designing a robust backup and restore solutions for very large databases (VLDBs). Using a real-world example, it demonstrates how to make the best use of the backup and restore features of SQL Server 2008 to help create a fast and reliable backup and restore plan for VLDBs over the network ... Source : Thomas H. Grohser - Case Study - SQLCAT.com

Best Practices for Data Warehousing with SQL Server 2008

As the title suggests, this article presents "accumulated wisdom" from Data Warehousing projects and covers initial design, partitioning, data profiling, business sponsorship and a whole lot more ... Source : Mark Whitehorn - Technical Article - MSDN.com

Assigning surrogate keys to early arriving facts using SSIS

In this post, the SQLCAT team demonstrate an SSIS technique for handling early arriving facts ... Source : SQLCat.com - Blog Post

SQL Server 2008 Failover Clustering

A great companion whitepaper to the SQL Server Books Online failover clustering documentation ... Source : Whitepaper - SQLCAT.com

MemToLeave and 64bit

Bob Ward talks about one of the benefits of 64bit SQL Server - the absence of "MemToLeave" issues ... Source : Bob Ward - CSS SQL Server Engineers - Blog Post

Resource_semaphore_query_compile

It looks like Jason and I have shared the same nightmare. In this post, he explores the possible causes of the infamous Resource_Semaphore_Query_Compile error ... Source : Jason Massie - jasonmassie.com

Data Compression: Strategy, Capacity Planning and Best Practices

While the new data compression feature in SQL Server 2008 delivers obvious benefits, there's a number of considerations before implementation, most notably the CPU impact. This article covers both the benefits and impacts of compression by different workload types ... Source : Sanjay Mishra - Technical Article - MSDN.com

DBCC Checks and Terabyte-Scale Databases

This document covers some of the options available to reduce the impact of running DBCC checks on VLDBs. ... Source : Stuart Ozer - Technical Note - SQLCAT.com

MERGE and OUTPUT

Adam explores the various uses for the MERGE command, including the power of its OUTPUT clause ... Source : Adam Machanic - SQLBlog.com

Can NOLOCK block inserts?

Yes, and Linchi demonstrates how this can occur ... Source : Linchi Shea - SQLBlog.com

Calculating the third Wednesday of the month

Alexander provides a (fast) solution to problems such as this using inline UDFs ... Source : Alexander Kuznetsov - SQLBlog.com

Un-named Defaults

In this post, Andrew covers several reasons why it's important to give default constraints a name ... Source : Andrew Kelly - SQLBlog.com

Grouped String Concatenation

Adam announces the results of his "Grouped String Concatenation" challenge ... Source : Adam Machanic - SQLBlog.com

TRY, CATCH, FAIL

In some (rare) cases, the CATCH block of a TRY-CATCH statement may fail to fire. In this post, Alexander walks us through one such example ... Source : Alexander Kuznetsov - SQLBlog.com

Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

This papers covers the inner workings of statistics, and the vital role they play in achieving the best query performance in SQL Server. In addition to a broad overview, it covers how SQL Server automatically maintains statistics, and how these settings can be overridden for individual columns/tables/databases (and why you may want to consider doing so) ... Source : Eric N. Hanson and Yavor Angelov - Whitepaper - MSDN.com

Plan Caching in SQL Server 2008

In this paper, Greg explains the inner workings of plan caching in SQL Server 2008, including best practices for reducing recompilations and plan cache pollution ... Source : Greg Low - Technical Article - MSDN.com

Using the Resource Governor

This paper covers the new Resource Governor feature introduced in SQL Server 2008, including ways of using it for server consolidation, preventing runaway queries, meeting service level agreements and implementing a resource usage based charging system ... Source : Whitepaper - MSDN.com

High Availability and Disaster Recovery: A SQL Server 2008 Technical Case Study

A case study of how ServiceU Corporation achieved 99.99% uptime using SQL Server 2008 ... Source : David P. Smith - Whitepaper - SQLCAT.com

Free SQL Server tools

Mladen provides links to a number of cool (and free) SQL Server tools ... Source : SQLTeam.com

SQL Server 2008 Diagnostic Script

Glenn kindly provides access to a number of diagnostic scripts for SQL Server 2008 ... Source : Glen Berry - spaces.live.com

Disk Partition Alignment Best Practices for SQL Server

Covering both the historical background information surrounding partition alignment and the negative performance impact unaligned partitions can have, this paper is essential reading for anyone involved in storage configuration for SQL Server systems. It provides actual results that demonstrate the performance impact of unaligned partitions, and includes a discussion of how Windows Server 2008 deals with this situation ... Source : Jimmy May and Denny Lee - Best Practices Article - Microsoft.com

SAN Multipathing

Brent walks us through the benefits and considerations for using SAN Multipathing ... Source : brentozar.com - Blog Post

Cheers,

Rod.

SQLCrunch: May 2009 Edition

Tue, 5 May 2009 07:25:00 GMT

SQL Server Blocked Process Monitor

Jonathan provides a tool via codeplex to track and capture the details of blocked processes, saving us the hassle of manually creating traces ... Source : Jonathan Kehayias - codeplex.com

Data type conversion

A timely reminder from Tibor to watch out for implicit data type conversion ... Source : Tibor Karaszi - SQLBlog.com

Common SQL Server Security Issues and Solutions

In this article, Paul tackles a number of important security issues from physical and network security through to SQL Injection, auditing and service accounts ... Source : Paul Randal - Technet Magazine

What the?

What looks odd here? select * from person where 'thomas' in (Firstname,LastName,LEFT(emailaddress,7)) ... Source : Denis Gobo - SQLBlog.com

Partition switching gotchas

Important things to consider when switching-out partitions in SQL Server 2005 / 2008 ... Source : Microsoft SQL ISV Program Management Team - Blog Post

Index Defrag script

Aaron provides some personal tweaks to Michelle Ufford's Index Defrag script ... Source : Aaron Bertrand - SQLBlog.com

Limiting connections to a specific application

Aaron lets us in on a little known SQL Server feature; the ability to restrict connections to a particular application ... Source : Aaron Bertrand - SQLBlog.com

Troubleshooting Performance Problems in SQL Server 2008

From the whitepaper summary "This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, Performance Monitor, dynamic management views, and SQL Server Extended Events (Extended Events) and the data collector" ... Source : SQLCat.com - Whitepaper

Locked pages in memory

Sankar provides a quick and painless way to determine if a SQL Server instance is using Locked Pages in Memory ... Source : Sankar Reddy - SQLServerPedia.com

T-SQL debugger for SQL Server 2008

The first of Bill's three part series on using the T-SQL debugger in SQL Server 2008 ... Source : Bill Ramos - MSDN.com - Blog Post

Recursive Queries

Linchi answers one of life's great questions; How do recursive queries actually work? ... Source : Linchi Shea - SQLBlog.com

Standardized SQL Server 2008 Audit

Lara provides several scripts to help get us started on creating standardized SQL Server audits ... Source : Lara Rubbelke - SQLBlog.com

Roadmap of SQL Server Certifications

Pinal Dave provides us with a link to his SQL Server certification roadmap pdf ... Source : Pinal Dave - SQLServerPedia.com

Memory deep dive

The first of Ulrich's 9 part series on memory. Whilst not written for a SQL Server audience, it's a great read nonetheless. Lots to be learned here ... Source : Ulrich Drepper - LWN.net

Controlling Lock Granularity

In this post, Kalen talks about the new options in SQL Server 2008 for controlling lock granularity ... Source : Kalen Delaney - SQLBlog.com

Using @@ROWCOUNT in Triggers

Steve warns us of the dangers of using @@ROWCOUNT in Triggers ... Source : Steve Kass - SQLBlog.com

Cheers!
Rod Colledge

SQLCrunch: All you can eat

Thu, 2 Apr 2009 10:15:00 GMT

I've just spent the best part of today getting through the backlog of links (123 of them) for posting to SQLCrunch. Obviously too many to list individually, but here's a selection;

Installing SQL Server 2008 on a Windows Server 2008 Cluster

Installing a clustered instance of SQL Server 2008 on Windows Server 2008 has changed significantly from earlier versions. In this 4 part post, Edwin walks through the process including screen shots and coverage of the iSCSI Initiator ... Source : Edwin Sarmiento - mssqltips.com

Free E-Book; Understanding Microsoft Virtualization Solutions

A free 15MB pdf download covering Microsoft based virtualization solutions ... Source : Microsoft.com - Downloads

MySpace.com and SQL Server

MySpace.com runs one of the largest (if not "the" largest) SQL Server implementations in the world. Learn how they do it here ... Source : WatchTechVideos.com - Video Download

Creating Alerts using T-SQL Script

In this post, Paul walks us through the process of creating an alert for a particular error message and also discusses the types of alerts we should create by default for all new SQL Server installations ... Source : Paul Randal - SQLSkills.com - Blog Post

Database Snapshot Performance Considerations

This whitepaper covers snapshots from several angles including the impact on index creation, duration to create a snapshot and restoration of databases from a snapshot ... Source : Microsoft.com - Downloadable Word Document

Presentation Logic; Database or App?

Do you format dates in stored procedures or an application layer? This post from Jonathon Kehayias (and a follow up post from Alexander Kuznetsov) talks about the pros and cons of each technique ... Source : Jonathan Kehayias - SQLBlog.com

Perl vs. Powershell vs. SSIS vs. Batch Files

What's the best/easiest way to execute multiple .sql script files in a given directory? What starts out as a simple perl script example from Linchi turns into a contest of sorts using various other script languages and tools to achieve the same result ... Source : Linchi Shea - SQLBlog.com

Pre-Installation Checklist

Brent shares his pre-installation checklist containing best practices for preparing for a SQL Server installation. This post also links to a follow up blog entry on post installation tasks ... Source : BrentOzar.com - Blog Post

Fixing setup prior to running it

Bob walks us through the new slipstream setup process whereby service packs and cumulative updates can be included in the one installation ... Source : Bob Dorr - CSS SQL Server Engineers - Blog Post

Wait Stats (for the last second)

Using the sys.dm_os_wait_stats DMV to identify wait statistics is a crucial step in any performance tuning exercise, however, the results returned are accumulated since the last server restart. In this post, Jason demonstrates a technique for using this DMV to return wait stats for the last second, useful for troubleshooting performance problems in real time ... Source : Jason Massie - Blog Post

Adam Machanic's Who is Active Script

Adam has released the latest version of his excellent DMV based current activity script Who Is Active - version 8.4 ... Source : Adam Machanic - SQLBlog.com

Paging T-SQL Results

How do you return a particular page of results with minimal overhead? Robert walks through a number of options before finishing with the lowest overhead approach ... Source : Robert Cary - SQLServerCentral.com

HBA Queue Depth Resources

A great blog from Joe focussed on HBA Queue Depth. In it, he links to a number of other prominent blogs which describe the performance impact (good and bad) of testing with various different settings ... Source : Joe Sack - MSDN.com - Blog Post

SSD vs. SAS and SATA

A great article from AnandTech comparing SSD to SATA and SAS ... Source : AnandTech.com - Article

Cheers!
Rod Colledge

SQLCrunch Update: Clustering

Wed, 11 Mar 2009 11:45:00 GMT

Added to SQLCrunch.com/clustering this week ...

Overview of Failover Clustering with Windows Server 2008

In this 10 page summary, we learn the major new Clustering enhancements in Windows Server 2008 ... Source : Microsoft.com - Downloadable Word Document

Understanding Quorum Configurations

This short Technet article does a good job of explaining the importantance of Quorum in a cluster ... Source : Microsoft.com - Technet Article

Multi-Site Clustering in Windows Server 2008

A great whitepaper (16 pages) covering Multi-Site clusters, including the business need for geographical dispersion and considerations for Quorum, Network and Storage ... Source : Microsoft.com - Downloadable Word Document

Windows Server 2008 Failover Clustering

A 22 page document detailing the new features and capabilities of Failover Clustering in Windows Server 2008 ... Source : Microsoft.com - Downloadable Word Document

Auto Notification for Cluster Failover

A simple but effective tip from Tim regarding a low cost method for email notification on cluster node failure ... Source : Tim Ford - mssqltips.com

Cheers

Drinking from the SQL fire hose

Wed, 4 Mar 2009 13:25:00 GMT

One of the great things about working with SQL Server is the incredible support community that has grown alongside the product itself. From local user groups through to conferences and forum websites, the breadth and depth of knowledge available through these mediums is possible only because of many very talented people willing to share their own valuable time in helping others.

Back in 2007, I decided that the best way for me to further advance my knowledge (and therefore career prospects) was to become vigilant with reading and summarizing blogs, articles and whitepapers from Microsoft and SQL Server MVPs. There is a massive amount of freely available SQL Server knowledge out there (far more than I ever imagined) and I naively decided that the best way to manage and benefit from it was to categorize, summarize and link to what I believed to be the best available content. SQLCrunch was born as a result of that decision, and to this day I desperately try and keep up with the torrent of great information released on a daily basis.

Over the last few months, I've been completely consumed with my book, and as a result, the SQLCrunch updates have reduced in frequency somewhat. I've got 143 links queued up and ready to go, and will be adding these to the site over the next week or so. With so many links to add, rather than release them in chronological order (which I typically do), I've decided to release them grouped by category, starting this week with Business Intelligence links ....

Fast Track Data Warehouse Design

This whitepaper introduces and defines a repeatable architecture for building scalable SMP based data warehousing solutions in SQL Server 2008 ... Source : SQLCat.com - Whitepaper

Analysis Services Codeplex Projects

Codeplex is an absolute goldmine. Check out these Analysis Services projects including SSAS stress testing and load simulation, collecting SSAS performance data, powershell scripts and SSIS packages for querying/collecting SSAS DMVs, monitoring scripts for Management Data Warehouse and a whole lot more ... Source : Codeplex.com

Slowly Changing Dimensions

In this post, Boyan walks us through an example implementation of a Slowly Changing Dimension (SCD) ... Source : Boyan Penev - Blog Post

The Data Loading Performance Guide

This paper contains the tricks and techniques used by the SQLCat team to achieve the ETL World Record for loading over 1TB of data in under 30 minutes ... Source : MSDN.com - Technical Article

Copying and Moving DTS packages

This post describes the process of copying and moving DTS packages between servers, including to SQL 2005/2008 destinations in which they need to be retained in legacy mode i.e.; not upgraded to SSIS packages ... Source : MSDN.com - Blog Post

SSIS Expression Language

In the first post in this series, Andy introduces us to the SSIS expression language, and provides some basic examples. Be sure to follow his later posts on derived columns, conditional splits and dynamic property expressions ... Source : Andy Leonard - SQLBlog.com

Reporting Services Performance Optimizations

In the latest addition to their Building and Deploying Large Scale Reporting Services Environments series, the SQLCAT team describes methods for optimizing Reporting Services architecture for higher throughput and performance, for both 2005 and 2008 environments ... Source : SQLCAT.com - Technical Notes

SSRS Management Reports

Thanks to Greg Low for pointing this one out. This BOL article walks us through the process of installing the sample Server Management reports for reporting on Reporting Services itself ... Source : SQL Server Books Online

...

Cheers

source: www.rodcolledge.com

DBA Best Practices Book; Available Now...

Fri, 27 Feb 2009 13:40:00 GMT

I've just spoken with my publisher after submitting the final manuscript for my book. In a celebration of sorts, they've decided to offer my blog readers 40 % off the retail price. From as little as $16.50, you can download the book today, and receive the hard copy when it goes into print in about 3 months from now.

Download here and use discount code sqlcrunch40

I'm keen for opinions on the front cover. Where is this guy from, and more importantly, whats he smoking?

Now for a little information on the book itself. It's essentially a best practices book for SQL Server DBAs (or developers that find themselves administering the database when they'd rather be doing something else). Whilst the main focus is on the new administration features in SQL Server 2008, there's a lot of information and best practices which are version independent, therefore, over half of the book is suitable for SQL Server 2005, 2000 and earlier.

Given its best practices nature, the book does not spend a lot of time on step by step instructions. There are plenty of other resources for that, including Books Online. Its main goal is to cover as many best practices as possible, whilst providing background information on why they are so important. For example, in addition to recommending the use of SQLIO/SIM and track aligning partitions, it describes why these tasks are important, and includes an overview of how these tasks are performed.

Each chapter closes with a bullet point list of all best practice statements. The chapter list is available here.

Cheers!
Rod Colledge

SQLCrunch Weekly Update; Emergency mode repair, T-SQL Debugger, Physical Corruption, 2008 Cluster Quorum, Profiler, Memory config, Storage Sizing...

Mon, 16 Feb 2009 20:15:00 GMT

Recently added to SQLCrunch ...

Using Emergency Mode to access a suspect/recovery pending database
A great post from Paul that walks through the process of simulating a corrupt transaction log, and the available options for accessing the database ... Source : Paul Randal - SQLSkills.com - Blog Post

Using the Management Studio T-SQL Debugger
In this screencast, SQL Server MVP Paul Nielsen walks us through the new T-SQL Management Studio debugger ... Source : Paul Nielsen - screencast.com

What is (and what causes) corruption?
Depending on who you talk to, "Corruption" can mean anything from orphaned records due to an application bug through to a trashed hard drive. In this post, Paul categorizes corruptions, and dispels a few myths along the way ... Source : Paul Randal - SQLSkills.com - Blog Post

Windows Groups; Management Challenges for SQL Server Logins
The Windows Authentication mode allows logins to be defined for both Windows users and groups. As Laurentiu points out in this blog post, whilst adding Windows Groups can simplify management, they come with some additional considerations, particularly around object ownership and default databases/schemas ... Source : Laurentiu Cristofor - MSDN.com - Blog Post

Windows Server 2008 Failover Clustering
A great overview of the clustering enhancements in Windows Server 2008 including the new Management Interface, Quorum Models, Storage Interaction and Migration features ... Source : TechNet Magazine - Article

Mastering SQL Server Profiler
Written by Brad McGehee, this 283 page pdf covers just about everything there is to know about Profiler. Includes coverage of trace templates, deadlocks, blocks, slow running queries, statistics operations, auditing & perfmon integration. Thanks to Tom Gniadek for the heads up on the link ... Source : Brad McGehee - Red-Gate.com

Physical Database Storage Design
One of the classics. This downloadable whitepaper covers the all important process of designing storage for SQL Server. As well as an overview of the differences in OLTP/OLAP workloads, it includes details of sizing a system for performance and throughput, RAID levels and the different types of storage systems available today ... Source : Microsoft.com - Whitepaper

system_health Xevent
Using the new Xevent model, the system_health session can be used to look back on the detail of a variety of system problems including sev 20+ errors, non yielding problems, deadlocks and blocking issues ... Source : CSS SQL Server Engineers - Blog Post

A Walk Down Memory Lane
A fantastic powerpoint presentation from Christian that covers all of the important memory configuration tasks including the limits of 32 bit systems, the memtoleave area, considerations for 64 bit systems, AWE on 32/64 bit systems and setting mix/max memory values ... Source : Christian Bolton - Coeo.com - pdf presentation

Partition Alignment
There are many articles that list partition alignment as an important configuration step, but few that go to this level of detail explaining why this is important. In this post, Jimmy also links to a downloadable powerpoint deck that walks through the process and supporting information. Great stuff. ... Source : Jimmy May - msdn.com - Blog Post

Sizing Storage for Performance Requirements
A good article that describes a method to calculate the number of spindles/disks in a storage system required to meet a given workload ... Source : SQLJunkies.com - Article

Cheers!
Rod Colledge

Now blogging @ www.rodcolledge.com

Mon, 16 Feb 2009 13:00:00 GMT

With the bulk of the book now complete, I've been able to check off one of my long outstanding tasks; to start a blog. This rss feed will continue as is, but I'll also be posting sqlCrunch updates over at my new blog.

Cheers!
Rod Colledge

SQLCrunch Weekly Update; SQLOS, Stored Proc Best Practices and Error Handling, TCP Ports, Database Exposure, Automation, CDC and SQLIOSIM...

Mon, 26 Jan 2009 21:15:00 GMT

Recently added to SQLCrunch ...

SQLOS
In this post, Slava provides a great overview of the SQL Server Operating System (SQLOS), introduced in SQL Server 2005 to take advantage of emerging hardware trends such as NUMA and multicore cpus ... Source : Slava Oks - MSDN.com - Blog Post

Stored Procedure Best Practices
Everyone has their own Stored Proc layout and coding conventions (and occasionally stick to them!), but very few people go to the effort of documenting them to this level of detail. Whilst you may not agree with *all* of Aarons suggestions, he provides fantastic food for thought ... Source : Aaron Bertrand - SQLBlog.com

Stored Procedure Error Handling
As Alexander points out, mixing stored procedure error handling techniques sometimes leads to unexpected results ... Source : Alexander Kuznetsov - SQLBlog.com

sys.dm_db_persisted_sku_features
There are lots of Enterprise Only features (compression, TDE, CDC etc ..) in SQL Server 2008, and restoring a database containing one of these to a standard (or lower) version will result in an error *at the end* of the restore process. As Paul points out in this post, rather than wait to the end of the restore to find this out, the new sys.dm_db_persisted_sku_features can be used to determine this before the restore starts, saving lots of time, particularly for large databases ... Source : Paul Randal - SQLSkills.com - Blog Post

TCP Port Assignment
When choosing a static TCP port for a SQL Instance, care should be taken to avoid port numbers used by other applications. This resource from Iana.org lists all registered application port numbers and points out the safe range (49152 through 65535) for private port assignment ... Source : iana.org

The Database Exposure Survey 2007
A frightening read which highlights the number of internet exposed database servers ... Source : Next Generation Security Software - ngssoftware.com

The Importance of Automation
Kevin Kline joins a number of other experts in discussing the various benefits of Automating DBA tasks ... Source : TechEd Online - Video

Transaction Log VLFs
In this blog post, Kimberly discusses the potential adverse impact of having too few Virtual Log Files (VLFs), usually as a result of preallocating a very large transaction log file ... Source : Kimberly L Tripp - SQLSkills.com

Tuning Change Data Capture Performance
This whitepaper describes methods for maximizing the performance of Change Data Capture implementations ... Source : SQLCat.com - Whitepaper

Understanding SQLIOSIM Output
Kevin shares a conversation with Jimmy May on understanding SQLIOSIM output ... Source : Kevin Kline - SQLBlog.com

Cheers!
Rod Colledge

SQLCrunch Weekly Update; Analyzing Session Level Waits, SQL Rover, Best Practices for SQL + Hyper-V, SSIS Performance ...

Wed, 14 Jan 2009 10:15:00 GMT

Recently added to SQLCrunch ...

Session Level Wait Stats
Whilst sys.dm_os_wait_stats is undoubtedly powerful, its information is collected and exposed at a server level. In this post, we're shown how session level wait information can be exposed using the new extended events feature ... Source : IvanPe - SQLOS Team Blog - Blog Post

Signal Waits
In addition to the classic CPU usage % performance counter, signal wait % is an important consideration when measuring SQL Server CPU pressure ... Source : Tom Davidson - MSDN.com - Blog Post

SQL Rover
SQL Rover will recursively search through a directory, generally your IIS log directory, and look inside of the logs for possible SQL Injections ... Source : Codeplex.com

SQL Server & Hyper-V: Best Practices and Recommendations
This whitepaper covers a number of Best Practices for running SQL Server in a Hyper-V environment ... Source : Microsoft.com - Whitepaper

SQL Server 2005 Integration Services: A Strategy for Performance
This white paper covers several interesting and unique methods for managing metadata using built-in features in SQL Server 2005 Integration Services, Analysis Services, and Reporting Services. The paper explains such concepts as data lineage, business and technical metadata, auditing, and impact analysis ... Source : Microsoft.com - TechNet Article

SQL Server 2008 Compliance Guide
This whitepaper provides an overview of the SQL Server 2008 features that can be utilized for compliance purposes. As well as covering Policy Based Management, it covers security techniques such as separation of duties, encryption and auditing ... Source : Microsoft.com - Whitepaper

SQL Server 2008 Desktop Background and Screen Saver
For the true SQL Server Geek! ... Source : Shashank Pawar - Technet.com - Blog Post

SQL Server Instance Inventory
In this article, Sakthivel lists a number of tools that can be used to generate an inventory of SQL Server instances. He then provides a step by step example of the usage of one of the listed tools (SQLping) ... Source : Sakthivel Chidambaram - SQLServerCentral.com

SQL Server Memory Usage & Task Manager
Relying on Task Manager to determine SQL Server's memory usage can be quite confusing unless you know how/what to look at ... Source : Kevin Kline - SQLBlog.com

SQLIOSIM; Under the covers
In this post, the PSS SQL Server Engineers provide more information on a number of aspects of SQLIOSIM including Running Average, Target Duration and Discarded Buffers ... Source : PSS SQL Server Engineers - Blog Post

Cheers!
Rod Colledge

SQLCrunch Weekly Update; Index Summary Script, ping.fm, Cache Bloat, Rebuilding System Databases, RML for SQL 2008 ...

Wed, 7 Jan 2009 11:15:00 GMT

Recently added to SQLCrunch ...

Physical Index Details
A great script (stored proc) from Tibor that retrieves index information that would otherwise require digging around in various places. Amongst others, it includes indexes for one or more tables, the index type, columns included (and included columns) in the index, uniqueness, number of rows, space usage and usage frequency. Great stuff ... Source : Tibor Karaszi - Karaszi.com

ping.fm = SQL Server + Twitter
An interesting suggestion from Jason regarding harnessing the power of social networking services such as Twitter as part of a SQL Server monitoring solution ... Source : Jason Massie - StatisticsIO.com

Procedure Cache Size
At what point does SQL Server decide that the Plan Cache is getting too large? As this post points out, it depends on the version of SQL Server ... Source : WesleyB - sqlug.be

Rebuilding System Databases
In SQL Server 2000, we used the Rebuildm.exe program to rebuild the master database. This was replaced in 2005 with the REBUILDDATABASE option in setup.exe. 2008 operates in the same manner, but as Bob Ward points out in this post, both the parameters and behind the scenes processes have changed ... Source : Bob Ward - MSDN.com - Blog Post

REPAIR_ALLOW_DATA_LOSS; What CHECKDB will delete
Before running the REPAIR_ALLOW_DATA_LOSS command, an attempt should be made to identify the extent of loss. In this post, Paul takes us through the use of the DBCC PAGE command to do exactly that ... Source : Paul Randal - SQLSkills.com - Blog Post

Resource Governor; Multiple Schedulers
Understanding how Resource Governor throttles CPU usage involves understanding process scheduling. Boris explains ... Source : Boris Baryshnikov - Technet.com - Blog Post

Restoring the Master Database
Restoring the master database requires SQL Server to be started in single user mode (-m startup switch) followed by the actual restore, however, as James points out, there are several important considerations before doing so, such as the currency of the backup (patch/service pack level) and login SIDs ... Source : James Luetkehoelter - SQLblog.com

RML for SQL Server 2008
Version 9.01 of the (very powerful) RML Utilities has been released, including support for SQL Server 2008 ... Source : PSS SQL Server Engineers - Blog Post

SAN Configuration
A humorous exchange between 2 respected SQL Server experts on the merits (or otherwise) of separating data and logs in a SAN ... Source : brentozar.com - Blog Post

Schedular Contention for concurrent BULK INSERTS
This example from the SQLCat team illustrates the inner workings of the SQLOS schedular mechanism, in turn explaining how CPU usage may be low despite significant processor contention ... Source : SQLCat.com - Technical Note

Cheers!
Rod Colledge

SQLCrunch Weekly Update; iFTS, Database Object Schemas, Inside Resource Governor, SSIS Performance Tuning, MemToLeave ...

Wed, 17 Dec 2008 11:00:00 GMT

Recently added to SQLCrunch ...

iFTS Best Practices
This blog offers a number of Best Practices surrounding the Integrated Full Text Search (iFTS) feature in SQL Server 2008 ... Source : SQLCAT Team - Blog Post

Implementing Database Object Schemas
A great whitepaper explaining the benefits of Database Object Schemas. As well as listing a number of best practices, it explains how schemas can be used to protect database objects from unintended access/modification as well as simplifying administration ... Source : Michael Redman - MSDN.com - Technical Article

Inside Resource Governor
In this post, Boris takes us through the setup and execution of a Resource Governor demo, and in doing so, exposes some of the nuances of how it works ... Source : Boris Baryshnikov - Technet.com - Blog Post

Integration Services: Performance Tuning Techniques
This white paper describes common performance-tuning techniques that you can apply to your SQL Server Integration Services (SSIS) data integration solutions ... Source : Microsoft.com - TechNet Article

Lock Escalation & Trace Flags
By default, SQL Server will choose the appropriate lock granularity & escalation points. This can be overridden with trace flags and query hints, however, as Sunil points out, there are various limitations with doing so ... Source : Sunil Agarwal - MSDN.com - Blog Post

Login Failures after a Mirroring Failover
One of the crucial aspects of a successful database mirroring installation is ensuring logins work at both ends (principal and mirror). As Chad points out, the SIDs for SQL Server logins need to match to ensure a smooth failover process ... Source : Chad Boyd - MSDN.com - Blog Post

MemToLeave and 32-bit SQL Backup failures
A common symptom of MemToLeave issues on 32 bit systems are backup failures with "reserve contiguous memory" related errors. In this post, Joe discusses adjusting the BufferCount and MaxTransferSize as an alternative to increasing the MemToLeave value (-g switch) ... Source : Joe Chang - SQLBlog.com - Blog Post

Microsoft Assessment and Planning (MAP) Toolkit
Version 3.2 of the MAP tool now includes a SQL Server Instance Discovery module, a fantastic tool for planning an Enterprise consolidation or upgrade project ... Source : Ward Pond - Technet.com - Blog Post

Minimal logging in SQL Server 2008
It was in, then it was out, and now it's back in again. Check out this post regarding the new Minimal Logging feature in SQL Server 2008 ... Source : SQL Server Storage Engine - Blog Post

One of the (many) benefits of Stored Procedures
In addition to reducing Adhoc SQL induced cache bloat, Stored Procedures increase security by avoiding the need to grant permissions to underlying tables. In this post, Tony Rogerson uses an example of a recent SQL Injection Robot to highlight this point ... Source : Tony Rogerson - SQLBlogcasts.com - Blog Post

Cheers!
Rod Colledge