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

Example - Use of Cross Apply Operator

We will look at simple example using CROSS Apply operator. Using Cross Apply Operator to JOIN table returned by function. Example below lists an example step by step

use tempdb

DROP TABLE Employee
DROP TABLE ADDRESS

--STEP 1
CREATE TABLE Employee
(
     NAME VARCHAR(20),
     Id INT Primary Key
)

--STEP 2

CREATE TABLE ADDRESS
(
   Id INT Foreign Key References Employee(Id),
   Location VARCHAR(100),
   Isactive bit
)

--STEP 3

INSERT INTO Employee (NAME,Id)
VALUES ('Ram',1)
INSERT INTO Employee (NAME,Id)
VALUES ('Sri',2)

--STEP 4

INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (1,'Chennai',1)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (1,'Bangalore',0)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (2,'Bangalore',1)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (2,'Delhi',0)

--STEP 5

SELECT * FROM ADDRESS

--STEP 6
CREATE Function dbo.getaddress (@id int)
RETURNS
@ADDRESS TABLE
(
   Id int,
   location varchar(100)
)
AS
BEGIN
   IF ISNULL(@id,0) = 0
     BEGIN
       RETURN
     END
   INSERT INTO @ADDRESS(Id, location)
   SELECT ID, Location
   FROM ADDRESS
   WHERE Isactive = 1
   AND Id = @id
RETURN
END

--STEP 7

--Function and Cross Apply
SET STATISTICS IO ON
SELECT * FROM
dbo.Employee T1 CROSS APPLY
dbo.getaddress(T1.Id)

--Table '#1CF15040'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'Employee'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT * FROM

dbo.Employee T1 JOIN ADDRESS AD
ON T1.Id = AD.Id
WHERE AD.Isactive = 1
--Table 'Employee'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'ADDRESS'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SCAN Count is Zero for employee table. Reason Here. Both inputs to nested loop are "SEEK"s, meaning you should usually see zero scan count.

Reference - Interpreting IO Statistics

To Touch on Basics Again

• Scalar Functions - Returns Single Variable
• Inline Table Value Function - Returns Table
• MultiStatement TVF - Return Table as defined in Schema

Good link on Difference between Functions and Stored Procedures Pinal Article - link

Happy Reading!!

Learning's on perfomance troubleshooting

Enables DBCC Traceon (1222) to Capture Deadlocks.
Run below trace to capture deadlock process. Link here

I also learnt I need to do a good learning on locking concepts, I found below links useful
Range locks
Geek City: What do you intend with that lock?
SQL Server DBA Concurrency and Locking Interview Questions
Presentation Links: SQL Server Performance Tuning (Quest)


Happy Learning!!!

TSQL XML Parsing

Parsing XML using TSQL. Below listed are some approaches.

--Approach I
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number><Message>AAAA</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT ParamValues.ID.query('Number').value('.','integer') as 'Number', ParamValues.ID.query('Message').value('.','VARCHAR(20)') as 'Message'
FROM @x.nodes('/Response/Errors/Error') as ParamValues(ID)

--Approach II
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number><Message>AAAA</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT c.value('./Number[1]', 'Integer')
, c.value('./Message[1]', 'VARCHAR(100)')
FROM @x.nodes('Response/Errors/Error') T(c)

--Approach III
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number><Message>AAAA</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

DECLARE @max INT, @i INT
SELECT @max = @x.query('<e>{ count(/Response/Errors/Error) }</e>').value('e[1]','int')
print @max
SET @i = 1
DECLARE @ErrorId VARCHAR(10)
DECLARE @ErrorMessage VARCHAR(100)
WHILE @i <= @max
BEGIN
SELECT @ErrorId = x.value('Number[1]', 'VARCHAR(10)') FROM @x.nodes('/Response/Errors/Error[position()=sql:variable("@i")]') e(x)
SELECT @ErrorMessage = x.value('Message[1]', 'VARCHAR(100)') FROM @x.nodes('/Response/Errors/Error[position()=sql:variable("@i")]') e(x)
SELECT @ErrorId, @ErrorMessage
SET @i = @i + 1
END

--With SubNodes
DECLARE @x XML
SET @x = '<Response>
<Status><IsError>1</IsError></Status>
<Errors>
<Error><Number>1</Number>
<Message>AAAA</Message>
<SubError>
<SNumber>5</SNumber>
<SMessage>SAAA</SMessage>
</SubError>
</Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT c.value('./Number[1]', 'Integer')
, c.value('./Message[1]', 'VARCHAR(100)')
, c.value('./SubError[1]/SNumber[1]', 'Integer')
, c.value('./SubError[1]/SMessage[1]', 'VARCHAR(100)')
FROM @x.nodes('Response/Errors/Error') T(c)

--Master Status Error 0, Sub Nodes Error Collection

DECLARE @x XML
SET @x = '
<ReturnStatus>
<Status>0</Status>
</ReturnStatus>
<Response>
<Errors>
<Error><Number>1</Number>
<Message>AAAA</Message>
<SubError>
<SNumber>5</SNumber>
<SMessage>SAAA</SMessage>
</SubError>
</Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
<Error><Number>2</Number><Message>BBB</Message></Error>
</Errors>
</Response>'

SELECT
c.value('..[1]/..[1]/..[1]/ReturnStatus[1]/Status[1]', 'Integer')
, c.value('./Number[1]', 'Integer')
, c.value('./Message[1]', 'VARCHAR(100)')
, c.value('./SubError[1]/SNumber[1]', 'Integer')
, c.value('./SubError[1]/SMessage[1]', 'VARCHAR(100)')
FROM @x.nodes('Response/Errors/Error') T(c)

I thought of posting it from my past learning. We can use above approach to load data into a table from XML...

SQL Server 2005 XQuery Performance Tips
Bulk Inserts with XML
OpenXML and XQuery Optimisation Tips
Performance tips of using XML data in SQL Server


Happy Reading!!

Simple TSQL Exercise

Question #1
Given a Table, Lookup based on Priority and return the value of action based on it

--Priotitylist
1. Match for PriorityA, PriorityB
2. Match for PriorityA, *
3. Match for *, PriorityB
4. Match for *, *

--STEP 1
CREATE TABLE TestPriority
(
PriorityA CHAR(5) NOT NULL,
PriorityB CHAR(5) NOT NULL,
Action BIT NOT NULL
)

--STEP2
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('AA','BB',1)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('AA','*',0)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('*','BB',1)
INSERT INTO TestPriority (PriorityA, PriorityB, Action)
VALUES ('*','*',0)

--STEP 3
DECLARE @PriorityA CHAR(5)
DECLARE @PriorityB CHAR(5)
SET @PriorityA = '*'
SET @PriorityB = 'BB'

SELECT Top 1 Action
FROM
(
SELECT ACTION, Priority =
CASE WHEN PriorityA = @PriorityA AND PriorityB = @PriorityB THEN 1
WHEN PriorityA = @PriorityA AND PriorityB = '*' THEN 2
WHEN PriorityA = '*' AND PriorityB = @PriorityB THEN 3
WHEN PriorityA = '*' AND PriorityB = '*' THEN 4
END
FROM TestPriority
) AS TESTResult
WHERE Priority IS NOT NULL
Order by Priority ASC

Question #2
You Have a Customer And Interest Table. Write a Query to Calculate Interest based on below conditions

Condition
1. When a match is found use the interest value
2. When no match found use default value '*'

--STEP 1
CREATE TABLE INTEREST
(
Region VARCHAR(5),
Rate INT
)

--STEP 2
INSERT INTO INTEREST(Region,Rate)
VALUES('CA',5),('US',10),('FR',20)

INSERT INTO INTEREST(Region,Rate)
VALUES('*',25)

--STEP 3
CREATE TABLE Customer
(
Name VARCHAR(20),
Amount INT,
Region VARCHAR(5)
)

--STEP 4
INSERT INTO Customer(Name,Amount,Region)
VALUES('Raj',10000,'CA')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Raja',10200,'SA')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Raa',10200,'FR')

INSERT INTO Customer(Name,Amount,Region)
VALUES('Ram',10200,'IN')

SELECT * FROM Customer

--STEP 5
SELECT C.Name, C.Amount*I.Rate/100, C.Amount, C.Region FROM
INTEREST I JOIN Customer C
ON ((I.Region = C.Region) OR (I.Region = CASE WHEN C.Region NOT IN (SELECT Region FROM INTEREST) THEN '*' END))

Database Development Model

Back to basics


We have three phases involved in Database Application Development

• Conceptual Data Model- Identifying Entities-Attributes, Outcode of this Phase ER Diagram, Identify Relationships
• Logical Phase - Normalize, Identify Rules, Map ER Diagram to Tables
• Physical Data Model - Tables, Constraints, Triggers, Relationships

For Physical Database Design the following are in scope - High Availability, Paritioning Strategy, Archival and Audting

Database Design Methodologies for Microsoft SQL Server
The phases of database design
Jim Gray - A talk with THE SQL Guru and Architect
Jim Gray - Part II of talking about Database Design
Database Design Process
Data Design
Advanced Performance Tuning – 1 :: Importance of data-types
Advanced Performance Tuning – 2 :: Which side of the operator
Advanced Performance Tuning – 3 :: Designing for better performance
Advanced Performance Tuning – 4 :: Designing for better performance contd…



Happy Learning!!!

Wiki useful Channel9 Links

Performance tuning wiki

Patterns & practices Performance Wiki

Patterns & practices Security Wiki

Channel9 Wiki

Patterns & practices Performance Testing Guidance for Web Applications.

Performance Testing Guidance for Web Applications

SQL Injection Walkthrough

SQL Injection and how to avoid it

SQL Injection Attacks by Example

Working with Tempdb

Compiled list of posts for tempdb related learning resources

• T-SQL:Generate statements to add TEMPDB datafiles
• Operations that heavily stress out tempdb
• Compilation of SQL Server TempDB IO Best Practices
• Taming the Tempdb Tempest - WI SQL Server Virtual User Group, 22 Apr 2011
• TempDB Configuration
• TempDB Monitoring and Troubleshooting: Out of Space
• TempDB Monitoring and Troubleshooting: DDL Bottleneck
• TempDB Monitoring and Troubleshooting: IO Bottleneck
• TempDB matters for a healthy SQL Server (Best Practices)
• Troubleshoot SQL Server 2008 Features
• Working with tempdb in SQL Server 2005
• Storage Top 10 Best Practices
• Managing TempDB in SQL Server:TempDB Basics
• Tempdb Perf mon counters

Happy Learning!!!