Create and Drop Constraints (Primary Key and Foreign Key Constraints)

Objective: Create and Drop Constraints (Primary Key and Foreign Key Constraints)
sp_pkeys — To see primary key
GO
sp_fkeys — To see foriegn key
USE [DatabaseName]
GO
ALTER TABLE [dbo].[TableName] WITH NOCHECK ADD CONSTRAINT [ForiegnKeyConstraintName] FOREIGN KEY([AffiliateID])
REFERENCES [dbo].[ReferencedTableName] ([ColumnName])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [ForiegnKeyConstraintName]
GO
USE [DatabaseName]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ForiegnKeyConstraintName]’) AND parent_object_id = OBJECT_ID(N'[dbo].[TableName]’))
ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [ForiegnKeyConstraintName]

IO Reads and Writes in SQL Server

Objective: To find IO reads and writes from SQL Server 2005
select *
from sys.dm_io_virtual_file_stats(NULL, NULL)
/*–what files are taking up the most IO*/
select db_name(mf.database_id)as database_name,mf.physical_name,left(mf.physical_name, 1) as drive_letter,
vfs.num_of_writes,vfs.num_of_bytes_written,vfs.io_stall_write_ms,mf.type_desc, vfs.num_of_reads,vfs.num_of_bytes_read,
vfs.io_stall_read_ms,vfs.io_stall,vfs.size_on_disk_bytes
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL,NULL)vfs
on mf.database_id = vfs.database_id and mf.file_id=vfs.file_id
order by vfs.num_of_bytes_written desc

Public DNS or Open DNS

http://code.google.com/speed/public-dns/
What is Google Public DNS?
Google Public DNS is a free, global Domain Name System (DNS) resolution service, that you can use as an alternative to your current DNS provider.
To try it out:
Configure your network settings to use the IP addresses 8.8.8.8 and 8.8.4.4 as your DNS servers or Read our configuration instructions.If you decide to try Google Public DNS, your client programs will perform all DNS lookups using Google Public DNS.

Why does DNS matter?
The DNS protocol is an important part of the web’s infrastructure, serving as the Internet’s phone book: every time you visit a website, your computer performs a DNS lookup. Complex pages often require multiple DNS lookups before they start loading, so your computer may be performing hundreds of lookups a day.

Why should you try Google Public DNS? By using Google Public DNS you can:
Speed up your browsing experience. Improve your security. Get the results you expect with absolutely no redirection.
Or
http://www.opendns.com/ provides the below IP’s
208.67.222.222
208.67.202.202

Database Size Report

Database Size Reporting:
Option 1:
Exec sp_databases
Option 2:
Exec sp_helpdb
Option 3:
This below procedure also gives information about each databases:
create procedure spReportDatabaseSizes as /* Purpose: Stored procedure to give at a glance information of database size,free space, file locations and file sizes */ DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT, PollDate datetime) DECLARE @command VARCHAR(5000) SELECT @command = ‘Use [‘ + ‘?’ + ‘] SELECT @@servername as ServerName, ‘ + ”” + ‘?’ + ”” + ‘ AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSize, sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(”?”,”Status”)) AS Status, CONVERT(sysname,DatabasePropertyEx(”?”,”Updateability”)) AS Updateability, CONVERT(sysname,DatabasePropertyEx(”?”,”Recovery”)) AS RecoveryMode, CAST(sysfiles.size/128.0 – CAST(FILEPROPERTY(sysfiles.name, ‘ + ”” + ‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ‘ + ”” + ‘SpaceUsed’ + ”” + ‘ ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ‘ + ”” + ‘%’ + ”” + ‘ AS FreeSpacePct, GETDATE() as PollDate FROM dbo.sysfiles’ INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate) EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName, LogicalFileName, PhysicalFileName, FileSizeMB, FreeSpaceMB, FreeSpacePct, Status, Updateability, RecoveryMode, PollDate as DateInfoGenerated FROM @DBInfo ORDER BY ServerName, DatabaseName

How to Find Worst SQL Queries by Dynamic Management View

Worst SQL Queries:
Eg: exec usp_Worst_TSQL @orderby=’TCPU’
CREATE PROC [dbo].[usp_Worst_TSQL]
/*
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,
I/O and Elapsed_Time as identified using DMV information. This can be display the worst
performing queries from an instance, or database perspective. The number of records shown,
the database, and the sort order are identified by passing pararmeters.
Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If
when calling this SP this parameter is set to a specific database name then only statements
that are associated with that database will be displayed. If the @DBNAME parameter is not set
then this SP will return rows associated with any database. The @COUNT parameter allows you
to control the number of rows returned by this SP. If this parameter is used then only the
TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
The @ORDERBY parameter identifies the sort order of the rows returned in descending order.
This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR
where “ACPU” represents Average CPU Usage
“TCPU” represents Total CPU usage
“AE” represents Average Elapsed Time
“TE” represents Total Elapsed Time
“EC” represents Execution Count
“AIO” represents Average IOs
“TIO” represents Total IOs
“ALR” represents Average Logical Reads
“TLR” represents Total Logical Reads
“ALW” represents Average Logical Writes
“TLW” represents Total Logical Writes
“APR” represents Average Physical Reads
“TPR” represents Total Physical Read
Typical execution calls
Top 6 statements in the AdventureWorks database base on Average CPU Usage:
EXEC usp_Worst_TSQL @DBNAME=’AdventureWorks’,@COUNT=6,@ORDERBY=’ACPU’;
Top 100 statements order by Average IO
EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY=’ALR’;
Show top all statements by Average IO
EXEC usp_Worst_TSQL;
*/
(@DBNAME VARCHAR(128) = ”
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = ‘AIO’)
AS
— Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN
@ORDERBY in (‘ACPU’,’TCPU’,’AE’,’TE’,’EC’,’AIO’,’TIO’,’ALR’,’TLR’,’ALW’,’TLW’,’APR’,’TPR’)
THEN 1 ELSE 0 END) = 0)
BEGIN
— abort if invalid @ORDERBY parameter entered
RAISERROR(‘@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR’,11,1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+’*’,
‘Resource’) AS [Database Name]
— find the offset of the actual statement being executed
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END –
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name]
,OBJECT_NAME(st.objectid,dbid) [Object Name]
,objtype [Cached Plan objtype]
,execution_count [Execution Count]
,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs]
,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]
,total_logical_reads/execution_count [Avg Logical Reads]
,total_logical_reads [Total Logical Reads]
,total_logical_writes/execution_count [Avg Logical Writes]
,total_logical_writes [Total Logical Writes]
,total_physical_reads/execution_count [Avg Physical Reads]
,total_physical_reads [Total Physical Reads]
,total_worker_time / execution_count [Avg CPU]
,total_worker_time [Total CPU]
,total_elapsed_time / execution_count [Avg Elapsed Time]
,total_elapsed_time [Total Elasped Time]
,last_execution_time [Last Execution Time]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = ‘dbid’ AND
CASE when @DBNAME = ” THEN ”
ELSE COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT)) + ‘*’,
‘Resource’) END
IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + ‘*’)
ORDER BY CASE
WHEN @ORDERBY = ‘ACPU’ THEN total_worker_time / execution_count
WHEN @ORDERBY = ‘TCPU’ THEN total_worker_time
WHEN @ORDERBY = ‘AE’ THEN total_elapsed_time / execution_count
WHEN @ORDERBY = ‘TE’ THEN total_elapsed_time
WHEN @ORDERBY = ‘EC’ THEN execution_count
WHEN @ORDERBY = ‘AIO’ THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = ‘TIO’ THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = ‘ALR’ THEN total_logical_reads / execution_count
WHEN @ORDERBY = ‘TLR’ THEN total_logical_reads
WHEN @ORDERBY = ‘ALW’ THEN total_logical_writes / execution_count
WHEN @ORDERBY = ‘TLW’ THEN total_logical_writes
WHEN @ORDERBY = ‘APR’ THEN total_physical_reads / execution_count
WHEN @ORDERBY = ‘TPR’ THEN total_physical_reads
END DESC

Top 100 Queries from particular instance of server

Top 100 Queries from particular instance of server:
create procedure usp_top100_queries as select top 100 a.total_worker_time,b.text from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.plan_handle) as b order by 1 desc

Replication Troubleshooting Information

Replication Troubleshoot Information Table:

http://vyaskn.tripod.com/repl_ans.htm

use distribution
go
select * from MSlogreader_history order by agent_id desc
select * from MSsnapshot_history order by agent_id desc
select * from MSdistribution_history order by agent_id desc
select * from MSmerge_history

Table Size Information

Objecive: To find table size information for particular database
USE DBNAME
go
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
DESCRIPTION
Returns TABLE Size Information
SORTING USAGE
@Sort bit VALUES
0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 1 /* Edit this value FOR sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
— =–Create temporary table
CREATE TABLE #TempTable
( [Table_Name] varchar(150),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
— =–Create Stored Procedure String
SELECT @cmdstr = ‘sp_msforeachtable ”sp_spaceused “?””’
–Populate Tempoary table
INSERT INTO #TempTable EXEC(@cmdstr)
–Determine sorting method
IF @Sort = 0
BEGIN
–Retrieve Table Data and Sort Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT *, cast(replace(table_size, ‘kb’, ”) as int) ts FROM #TempTable ORDER BY ts DESC
END
DROP TABLE #TempTable

SQL Server Service Pack Version Finder

Objective: To find out the SQL server Service Pack Version
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
go
select @@version

Way to find Blocking Information – SQL Server

Objective: Below script is to find the blocking information in sql server
Declare @wTable Table (spid int, blocked int, loginame varchar(100), dbName varchar(100), last_batch dateTime)
Insert into @wTable
select spid, blocked, loginame, db_name(dbid), last_batch from sys.sysprocesses (nolock)–where blocked 0
select spid,blocked,loginame,dbName,last_batch from @wtable where blocked 0
Union all
select spid,blocked,loginame,dbName,last_batch from @wtable where spId in(select blocked from @wtable where blocked 0)
order by blocked