"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

August 30, 2009

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

August 09, 2009

Useful DMV Queries and Perfmon Guidance

Useful DMV queries

Getting the Most Out of SQL Server Profiler Using Templates

Monitoring for Blocked Processes On SQL 2005 - Extended Version

The Debut of The SQL DMV All-Stars Dream Team!

How to Tune a Database Using the Database Tuning Advisor (DTA)

SQL Server 2005 Performance Statistics Script

Understanding Perfmon Counters while troubleshooting SQL Server Performance Issues

Monitoring SQL Server health

Detecting SQL Server 2005 Blocking

sys.dm_exec_requests

Troubleshooting Performance Problems in SQL Server 2005

A great write-up on performance tuning

I/O Bottlenecks

Not more excuses of missing indexes with Activity Monitor in SQL Server 2008

SQL Server - Performance Counter Guidance

Trending and monitoring for performance

DBA Headquarters: SQL Server DBA Automation Tools

Top Tips for Effective Database Maintenance

Updated SQL Server 2008 Diagnostic Information Queries

CDC - Change Data Capture

CDC is SQL Server 2008 Feature. To fetch changes made to table.

This below articles provides a clear step by step approach consuming CDC changes


Happy Reading!!

August 07, 2009

Linked Server and FAQ

Today I got a question on using linked server, Below links I found useful

How to Achieve High Performance with Linked Servers
Test linked server connection settings
Performance Problem while reading from Linked server
Net Net it is preferred to have the data locally and execute query than using linked server as it might be a performance killer. There are some hints than can be specified, We would discuss it in next post.

August 01, 2009

Using Hints and Plan Guides

In this post we would see examples for using hints and plan guides

DBCC FREEPROCCACHE - Removes all plans in cache

--STEP 1
DROP TABLE TestTable
DROP TABLE TestTable2

--STEP 2
CREATE Table TestTable (
a int NOT NULL PRIMARY KEY,
name varchar(50))

CREATE Table TestTable2 (
a int NOT NULL PRIMARY KEY,
name varchar(50))

--STEP 3
Declare @i int
set @i = 1
While 1 =1
begin
insert into TestTable(a, name)
select @i, convert(varchar(2),@i)+ 'name'
insert into TestTable2(a, name)
select @i+5, convert(varchar(2),@i)+ 'name'
set @i = @i+1
if @i > 100
break;
end

sp_help TestTable
--PK__TestTable__627A25C7 clustered, unique, primary key located on PRIMARY

--STEP 4
CREATE INDEX IX_Name ON TestTable(name)

--Index Hint
We can specify the query to use a specific index as mentioned below.

SELECT a,name FROM TestTable WITH (INDEX(IX_Name)) WHERE a = 10
SELECT a,name FROM TestTable WITH (INDEX(PK__TestTable__627A25C7)) WHERE a = 10

--JOINT HINT
If I want my query to use a specific join you can provide it like mentioned below.

SELECT TT1.a,TT.name FROM TestTable TT JOIN TestTable2 TT1
ON TT.a = TT1.a
Option(MERGE JOIN)

--STEP 5
I need my query to stick to a plan, you can create a plan guide for your query as provided in below example.

--CREATE A PLAN GUIDE For the Same
sp_create_plan_guide @name = N'PlanGuidetest',
@stmt = N'SELECT TT1.a,TT.name FROM TestTable TT JOIN TestTable2 TT1
ON TT.a = TT1.a',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (Loop JOIN)'
GO

SELECT * FROM sys.plan_guides
GO

SELECT TT1.a,TT.name FROM TestTable TT JOIN TestTable2 TT1
ON TT.a = TT1.a

--Drop the plan guide.
EXEC sp_control_plan_guide N'DROP', N'PlanGuidetest'

References –
http://blogs.technet.com/andrew/archive/2008/11/14/sql-server-2008-plan-guides.aspx

Special thanks to balmukund for correcting me plan guide is case-sensitive, Even a little space adjusted in the query, it would not match with plan guide.