Perf Testing - Revisited

Couple of useful suggestions

Powershell - Your friend for Performance Testing projects

Couple of useful Tools posted in Codeplex. I need to check them

SQL Load Test

Fundamentals of Storage Systems – Capturing IO Patterns

VSTT 2008 Quick Reference Guide

WCF Load Test

How To Add File Upload To Team System Web Test

VS2010 – Load Test Network Emulation Profile

Load Agent and Load Controller Installation Guide

Load Agent and Load Controller Configuration Guide

VSTS 2010 Features explained by shivaji...Read it here

CodedUITest

SQL Server 2008 R2 November CTP - Released

SQL Server 2008 R2 November CTP: Installation Step by Step

Download R2 bits here

Summarizing SQL 2008 Features

• TDE (Transparent Data Encryption) - Encryption of data on the disk while it remains transparent to the application requesting the data.
• Auditing - Server and Database Audits
• Backup Compression
• Performance Data Collection (Disk Usage, Query Stats, Server Activity)
• Resource Governor - Limit Usage of CPU, Memory
• CDC - Change Data Tracking
• TVP - Ability to declare table types and pass as variables
• Data Compression - Row Compression, Page Compression
• FileStream - Ability to store objects in the file system but still be managed by SQL Server
• Sparse Columns - Sparse columns reduce the amount of storage for null values  at the sacrifice of more overhead to retrieve non-null values. Best practice is to use when column contains 20 – 40 percent null values.
• New Index Types - Filtered Index, Spatial Index, XML Index
• Merge Statement

Summarizing SQL 2008 R2 Features

SQL Query Execution

SQL Query Execution Phase Involves below Stages

1. Parsing
2. Normalization
3. Optimization
4. Caching
5. Wait for Memory to Execute the Plan
6. Execute
7. Return Results

Summary

• Parsing - Validate Syntax of Query, Split Query into Operators, Expressions, Keywords. Output is Parse Tree.
• Normalization - Validate Objects, Replaces views by definitions, Algebrized tree is output. This is input to Optimizer to generate execution Plan.
• Optimizer - Responsible for generating Query Plan. Cost Based Optimizer, based on Table Statistics, Indexes, JOIN selection. Trivial or Straight Forward Optimization and Full Optimization are two phases involved in identifying Optimal Plan.
• Compiled Plan is Output from Optimizer. The compiled plan for this query, though, would tell SQL Server exactly which physical query operators to use. Compiled plans are reentrant, which is to say that if multiple users are simultaneously executing the same stored procedure, they can all share a single compiled plan.
• Execution contexts - Information Specifix to Particular user, Execution. Cannot be Shared simultaneously. Every Execution Context is linked to a compiled Plan.
• SQL Server breaks queries down into a set of fundamental building blocks that we call operators.
• Operators can be either Physical or Logical. JOIN is a logical operation wheras nested loop join is a Physical Operator.
• Physical Operators implement operation defined by logical operators. Physical Operators answers three method calls INIT() - Initialize itself with required data structure. GETNEXT() - Call first, next subsequent rows, CLOSE() - Clear up operations.
• Few Tricky Physical Operators
• Lazy spool - . The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database.
• Spool - The Spool operator saves an intermediate query result to the tempdb database.
• Caching - Plans are Cached in Cached stores for SPs, Functions, Adhoc Query Plans, Auto Parameterized Plans

You can also check recorded session on Execution Plan Anaysis in SQLCommunity site link
Intepreting Execution Plans

Ref - Link


Why should I create an index?
I Smell a Parameter!
Microsoft SQL Server Execution Plans: From Compilation, to Caching, to Reuse
SQL Query Execution Notes

Happy Learning!!!!