SQL Server Performance Base Line Script part1

USE [master]
GO
if exists(select 1 from sys.sysobjects where name=N'InstanceAnalysis_PerformanceBaseLine' and 'P')
begin
Drop procedure [dbo].[InstanceAnalysis_PerformanceBaseLine]
end
if exists(select 1 from master.sys.sysobjects where name=N'SP_InstanceBaselinePerfReport' and 'P')
begin
Drop procedure [dbo].[SP_InstanceBaselinePerfReport]
end


/****** Object:  StoredProcedure [dbo].[InstanceAnalysis_PerformanceBaseLine]    
Script Date: 1/17/2013 10:28:04 PM 
Subject:This script will collect the performancebase line data from the diffrent DMV and performacen counter of the SQL Server.


******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[InstanceAnalysis_PerformanceBaseLine]
as
/****** Object:  StoredProcedure [dbo].[InstanceAnalysis_PerformanceBaseLine]    
Script Date: 1/17/2013 10:28:04 PM 
Subject:This script will collect the performancebase line data from the diffrent DMV and performacen counter of the SQL Server.
Please let me know your feedback about the script any suggestion comment are most welcome 


******/

declare @ts_now bigint,
@start_time varchar(20),
@Server_Name varchar(100),
@Server_ver varchar(500),
@SQLSer_OSInfo varchar(500),
@SQL_inst_date varchar(100),
@MachineName varchar(100),
@ServerName varchar(100),
@SrvName_prop varchar(100),
@Srv_Machine varchar(100),
@InstName varchar(100),
@IsCluster varchar(10),
@CompNetbios varchar(200),
@SqlEdition varchar(100),
@SqlProductLevel varchar(10),
@SqlProdVer varchar(10),
@SqlProid varchar(10),
@Sql_Ins_collation varchar(100),
@IsfullText varchar(10),
@IsInterSec varchar(10),
@LogicalCPUCount varchar(10),
@HTRatio varchar(10),
@PhyCPUCount varchar(10),
@PhyRAM_MB varchar(10),
@Proc_Value varchar(200),
@Proc_date Varchar(500),
@Sp_config_Name varchar(500),
@Sp_config_value varchar(10),
@Sp_config_inusevalue varchar(10),
@Sp_config_des varchar(1000),
@db_det_name varchar(400),
@db_det_fileid varchar(200),
@db_det_filename varchar(200),
@db_det_phyfilename varchar(4000),
@db_det_filedesc varchar(100),
@db_det_statedesc varchar(200),
@db_det_filesizeMB varchar(20),
@db_log_info_dbname varchar(500),
@db_log_info_rmodle varchar(500),
@db_log_info_logreusewait varchar(500),
@db_log_info_logsizekb varchar(200),
@db_log_info_logusedkb varchar(200),
@db_log_info_logusedper varchar(200),
@db_log_info_dbcmptlevel varchar(200),
@db_log_info_pageverify varchar(200),
@db_log_info_autstats varchar(10),
@db_log_info_autoupdstats varchar(10),
@db_log_info_autstatsasyncon varchar(10),
@db_log_info_parameterrizatio varchar(10),
@db_log_info_snapshotisolation varchar(50),
@db_log_info_readcommitedsnapshot varchar(50),
@db_log_info_autoclose varchar(10),
@db_log_info_autoshrink varchar(10),
@IO_DBName varchar(100),
@IO_PhyName varchar(5000),
@io_stall_read_ms real,
@io_num_of_reads bigint,
@io_avg_read_stall_ms real,
@io_stall_write_ms real,
@io_num_of_writes bigint,
@io_avg_write_stall_ms real,
@io_stalls bigint,
@io_total bigint,
@avg_io_stall_ms real,
@row_cnt int,
@Db_name varchar(500),
@Db_cpu_time_ms bigint,
@db_cpu_per real,
@dbcache_Dbname varchar(500),
@dbcache_dbcachesizeMB real,
@waitType_WaitTypeName varchar(500),
@WaitType_waittime_s real,
@WaitType_resource_s real,
@WaitType_Signal_s real,
@WaitType_counts bigint,
@WaitType_WaitingPct real,
@WaitType_RunningPct real,
@cpuwait_signal_cpu_waits real,
@cpuwait_resource_wait real,
@logindet_LoginName varchar(500),
@logindet_session_count bigint,
@avg_task_count varchar(200),
@avg_runnable_task_count varchar(200),
@avg_diskpendingio_count varchar(200),
@sqlproc_cpu_Sql_proc int,
@sqlproc_cpu_sysidle int,
@sqlproc_cpu_otheros_proc int,
@sqlproc_cpu_event_time datetime,
@sqlmem_svr_name varchar(200),
@sqlmem_obj_name varchar(200),
@sqlmem_ins_name int,
@sqlmem_Page_life_expe int,
@sqlmem_svrm_name varchar(200),
@sqlmem_sql_obj_name varchar(200),
@sqlmem_sql_mem_grant_pend int,
@sqlmemclerk_obj_name varchar(500),
@sqlmemclerk_mem_kb bigint,
@adhocQue_QueryText varchar(4000),
@adhocQue_Qplan_size_byte bigint,
@tokempermcachesizekb varchar(200),
@clocktokenname varchar(200),
@clocktyoe varchar(200),
@clockhand varchar(200),
@clock_status varchar(200),
@clockroundcounts varchar(200),
@clockremovedallroundcount varchar(200),
@clockremovedlastroundcount varchar(200),
@clockupdatedlastroundcount varchar(200),
@clocklastroundstarttime varchar(200),
@flagname varchar(20),
@flagstatus varchar(20),
@flagglobal varchar(20),
@flagsesion varchar(20),
@topspbycpu_spname varchar(4000),
@topspbycpu_totalworkertimeinmicros varchar(200),
@topspbycpu_Avgworkertimeinmicros varchar(200),
@topspbycpu_Executioncount varchar(100),
@topspbycpu_callsecond varchar(200),
@topspbycpu_averageelapsedtimeinmicros varchar(200),
@topspbycpu_maxlogicalread varchar(200),
@topspbycpu_maxlogicalwrites varchar(200),
@topspbycpu_ageincache varchar(200),
@sqlschedule_parenenodeid varchar(10),
@sqlschedule_schdulerid varchar(10),
@sqlschedule_cpuid varchar(10),
@sqlschedule_status varchar(30),
@sqlschedule_isonline varchar(10),
@sqlschedule_isidle varchar(10),
@sqlschedule_preemptiveswtichescounts varchar(50),
@sqlschedule_contextswtichescounts varchar(50),
@sqlschedule_idleswtichescounts varchar(50),
@sqlschedule_currenttaskcounts varchar(50),
@sqlschedule_runnabletaskcounts varchar(50),
@sqlschedule_currentworkercounts varchar(50),
@sqlschedule_activeworkercounts varchar(50),
@sqlschedule_pendingiocounts varchar(20),
@sqlschedule_failedtocreate varchar(20),
-- Listing 10 Locating physical read I/O pressure
-- Get Top 20 executed SP's ordered by physical reads (read I/O pressure)
@topsp_iopressure_spname varchar(1000),
@topsp_iopressure_physicalread varchar(40),
@topsp_iopressure_spname_avgphysicalread varchar(40),
@topsp_iopressure_spname_Executioncount varchar(40),
@topsp_iopressure_spname_callsecond varchar(40),
@topsp_iopressure_spname_Avgworkertime varchar(40),
@topsp_iopressure_spname_Totalworkertime varchar(40),
@topsp_iopressure_spname_Avgelapsedtime varchar(40),
@topsp_iopressure_spname_maxlogicalreads varchar(40),
@topsp_iopressure_spname_maxlogicalwrite varchar(40),
@topsp_iopressure_spname_ageincache varchar(40),

-- Listing 14 Finding indexes and tables that use the most buffer space
-- Breaks down buffers by object (table, index) in the buffer cache
@object_spaceinmem_objname varchar(1000),
@object_spaceinmem_objid varchar(10),
@object_spaceinmem_indexid varchar(10),
@object_spaceinmem_buffersizeinmb varchar(10),
@object_spaceinmem_Buffcount varchar(100),
-- Listing 16 Finding your 25 most expensive queries for memory
-- Get Top 25 executed SP's ordered by logical reads (memory pressure)
@topsp_mempressure_spname varchar(1000),
@topsp_mempressure_totallogicalread varchar(30),
@topsp_mempressure_executioncount varchar(30),
@topsp_mempressure_Avglogicalreads varchar(30),
@topsp_mempressure_callspersecond varchar(30),
@topsp_mempressure_avgworkertime varchar(30),
@topsp_mempressure_totalworkertime varchar(30),
@topsp_mempressure_Avgelapsedtime varchar(30),
@topsp_mempressure_totallogicalwrite varchar(30),
@topsp_mempressure_maxlogicalread varchar(30),
@topsp_mempressure_maxlogicalwrite varchar(30),
@topsp_mempressure_totalphysicalread varchar(30),
@topsp_mempressure_ageincache varchar(30),
-- Missing Indexes by Index Advantage
@msngidx_idxadv varchar(10),
@msngidx_lastuser_seek varchar(10),
@msngidx_dbschematable varchar(1000),
@msngidx_equalitycols varchar(1000),
@msngidx_inequalitycols varchar(1000),
@msngidx_includedcols varchar(1000),
@msngidx_uniquecompiles varchar(100),
@msngidx_userseeks varchar(100),
@msngidx_avgtotalusercost varchar(100),
@msngidx_avguserimpact varchar(100),

-- Listing 26 Detecting blocking (a more accurate and complete version)
@blocking_lcktype varchar(200),
@blocking_dbname varchar(500),
@blocking_blockerobj varchar(500),
@blocking_lckreque varchar(200),
@blocking_waitersid varchar(10),
@blocking_waitime varchar(10),
@blocking_waitbatch varchar(20),
@blocking_waiterstmt varchar(1000),
@blocking_blockersid varchar(200),
@blocking_blocker_stmt varchar(1000),

-- Listing 27 Looking at locks that are causing problems
@lockquery_restype varchar(100),
@lockquery_resdbid varchar(10),
@lockquery_resentryid varchar(100),
@lockquery_reqmode varchar(100),
@lockquery_reqsessid varchar(10),
@lockquery_blocksid varchar(10),

-- Database Growth Query
@endDate datetime,
@months smallint,
@DBG_Dbname varchar(200),
@DBG_YearMon varchar(50),
@DBG_MinSizeMB varchar(200),
@DBG_MaxSizeMB varchar(200),
@DBG_AVGSizeMB varchar(200),
@DBG_GrowthMB varchar(200),

--- Memory Configuration 
@pg_size int,
@Instancename varchar(50),
--Physical Memory Details on Server along with VAS.
@phymem_onsrvinmb varchar(200),
@phymem_onsrvingb varchar(200),
@phymem_onsrvVAS varchar(200),
--Buffer Pool Usage at the Moment
@bpoolusg_commitedinmb varchar(20),
@bpoolusg_commitedintargetmb varchar(20),
@bpoolusg_visibleinMB varchar(20),
--Total Memory used by SQL Server instance from Perf Mon
@totalmemsql_usageinkb varchar(20),
@totalmemsql_usageinMB varchar(20),
@totalmemsql_usageinGB varchar(20),
--Memory needed as per current Workload for SQL Server instance
@memneed_curwl_meminkb varchar(20),
@memneed_curwl_meminmb varchar(20),
@memneed_curwl_meminGB varchar(20),
--Total amount of dynamic memory the server is using for maintaining connections
@memcon_usageinkb varchar(50),
@memcon_usageinmb varchar(50),
@memcon_usageingb varchar(50),
--'Total amount of dynamic memory the server is using for locks
@memlock_useinkb varchar(50),
@memlock_useinMb varchar(50),
@memlock_useinGb varchar(50),
--Total amount of dynamic memory the server is using for the dynamic SQL cache
@dynsqlcache_useinkb varchar(50),
@dynsqlcache_useinMb varchar(50),
@dynsqlcache_useinGb varchar(50),
--Total amount of dynamic memory the server is using for query optimization
@qryopt_useinkb varchar(50),
@qryopt_useinMb varchar(50),
@qryopt_useinGb varchar(50),
--Total amount of dynamic memory used for hash, sort and create index operations.
@idexsort_userinkb varchar(50),
@idexsort_userinMb varchar(50),
@idexsort_userinGb varchar(50),
--Total Amount of memory consumed by cursors.
@curmem_useinkb varchar(50),
@curmem_useinMb varchar(50),
@curmem_useinGb varchar(50),
--Number of pages in the buffer pool (includes database, free, and stolen)
@bpool_page_8kbno varchar(50),
@bpool_pages_inkb varchar(50),
@bpool_pages_inmb varchar(50),

--Number of Data pages in the buffer pool
@dbpagebpool_page_8kbno varchar(50),
@dbpagebpool_page_inkb varchar(50),
@dbpagebpool_page_inmb varchar(50),

--Number of Free pages in the buffer pool
@freepagebpool_page_8kbno varchar(50),
@freepagebpool_page_inkb varchar(50),
@freepagebpool_page_inmb varchar(50),

--Number of Reserved pages in the buffer pool
@respagebpool_page_8kbno varchar(50),
@respagebpool_page_inkb varchar(50),
@respagebpool_page_inmb varchar(50),

--Number of Stolen pages in the buffer pool
@stolenpbpool_page_8kbno varchar(50),
@stolenpbpool_page_inkb varchar(50),
@stolenpbpool_page_inmb varchar(50),

--Number of Plan Cache pages in the buffer pool
@plancachebpool_page_8kbno varchar(50),
@plancachebpool_page_inkb varchar(50),
@plancachebpool_page_inmb varchar(50),
--SQL Server Binary Module Information 
@DllFilePath varchar(2000),
@FileVer varchar(500),
@Productver varchar(200),
@Bin_Descrip varchar(5000),
@Modulesize_inkb varchar(200),

-- Version Stored Application
@verstorepage_used varchar(20),
@verstorepage_spaceinMB Varchar(20),

--Script to total tempdb usage by type across all files
@tempdb_user_obj_pages_inMB varchar(20),
@tempdb_internal_obj_pages_inMB varchar(20),
@tempdb_versionstore_obj_pages_inMB varchar(20),
@tempdb_total_pages_use_inMB varchar(20),
@tempdb_total_pages_free_inMB varchar(20),

--Script to find the top five sessions running tasks that use tempdb
@tempdbsession_sid varchar(20),
@tempdbsession_requ_sid varchar(20),
@tempdbsession_execontext_sid varchar(20),
@tempdbsession_dbid varchar(20),
@tempdbsession_usrobjallocpage_count varchar(20),
@tempdbsession_usrobjdeallocpage_count varchar(20),
@tempdbsession_internalallocpage_count varchar(20),
@tempdbsession_internaldeallocpage_count varchar(20),
--Script to find the top five sessions running tasks that use tempdb
@sessionact_sid varchar(10),
@sessionact_logintime varchar(100),
@sessionact_hostname varchar(100),
@sessionact_programname varchar(520),
@sessionact_cputime varchar(10),
@sessionact_memusginkb varchar(10),
@sessionact_totalschetime varchar(10),
@sessionact_totalelsapsedtime varchar(10),
@sessionact_lastrequestendtime varchar(50),
@sessionact_reads varchar(10),
@sessionact_write varchar(10),
@sessionact_conncount varchar(10),
--script for IO Result for file in min
@fileio_dbname varchar(200),
@fileio_filename varchar(4000),
@fileio_filetype varchar(200),
@fileio_filesizegb varchar(200),
@fileio_mbread varchar(200),
@fileio_mbwrite varchar(200),
@fileio_noofread varchar(200),
@fileio_noofwrite varchar(200),
@fileio_miniowritestall varchar(200),
@fileio_minioreadstall varchar(200),
--script to look for open transaction actual activity
@otran_spid varchar(10),
@otran_lasworkertime varchar(200),
@otran_lastphysicalread varchar(200),
@otran_totalphysicalread varchar(200),
@otran_totallogicalwrites varchar(200),
@otran_lastlogicalreads varchar(200),
@otran_currentwait varchar(200),
@otran_lastwaittype varchar(1000),
@otran_watiresource varchar(1000),
@otran_waittime varchar(100),
@otran_opentrancount varchar(100),
@otran_rowcount varchar(10),
@otran_granterqmem varchar(20),
@otran_sqltect varchar(4000)



print'<HTML><head><Title>SQL Server Instance Detail Report.</Title>'+
'<style >'+
'table {
border-collapse:collapse;
background:#EFF4FB url(http://www.roscripts.com/images/teaser.gif) repeat-x;
border-left:1px solid #686868;
border-right:1px solid #686868;
font:0.8em/145% Trebuchet MS,helvetica,arial,verdana;
color: #333;
}'+

'td, th {
padding:5px;
}'+

'caption {
padding: 0 0 .5em 0;
text-align: left;
font-size: 1.4em;
font-weight: bold;
text-transform: uppercase;
color: #333;
background: transparent;
}'+

'table a {
color:#950000;
text-decoration:none;
}'+

'table a:link {}'+

'table a:visited {
font-weight:normal;
color:#666;
text-decoration: line-through;
}'+

'table a:hover {
border-bottom: 1px dashed #bbb;
}'+


'thead th, tfoot th, tfoot td {
background:#333 url(http://www.roscripts.com/images/llsh.gif) repeat-x;
color:#fff
}'+

'tfoot td {
text-align:right
}'+

'tbody th, tbody td {
border-bottom: dotted 1px #333;
}'+

'tbody th {
white-space: nowrap;
}'+

'tbody th a {
color:#333;
}'+

'.odd {}'+

'tbody tr:hover {
background:#fafafa
}'+


'</style></head>'

/*
SQL Server Startup Time
*/


print N'<h1>SQL Server Up Time</h1>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+

N'<tr><th><strong>Time</strong></th>'+'</tr>'

declare cur_uptime_sql cursor local for
select CONVERT(VARCHAR(20), create_date, 100) 
  from sys.databases where database_id=2
open cur_uptime_sql
fetch from cur_uptime_sql into 
@start_time
while @@fetch_status>=0
begin 
print '<tr><td>'+@start_time+'</td>'+'</tr>'
fetch from cur_uptime_sql into 
@start_time
end
close cur_uptime_sql
deallocate cur_uptime_sql
print'</table><br/>'
/*
Instance Detail Information fetching Query
*/

print N'<h1>SQL Server Instance Detail</h1>'
print N'<H3>SQL Server Name and Version Detail</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+

N'<tr><th><strong>Server Name</strong></th>'+
N'<th><strong>Instance Version</strong></th></tr>'



declare cur_sql_info  cursor local for SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info]
open cur_sql_info
fetch next from cur_sql_info into @Server_Name,@Server_ver
while @@fetch_status>=0
begin 
print '<tr><td>'+@Server_Name+'</td><td>'+@Server_ver+'</td>'+'</tr>'
fetch next from cur_sql_info into @Server_Name,@Server_ver
end
close cur_sql_info
deallocate cur_sql_info
print'</table><br/>'

print '<table >
<tr>
<td><span ><strong>RECOMMENDATION:</strong></span><br>
SQL Server 2005 fell out of Mainsteam Support on April 12, 2011 -- This 
means no more Service Packs or Cumulative Updates.<br>-- The SQL Server 
2005 builds that were released after SQL Server 2005 Service Pack 2 was 
released<br>
<a href="http://support.microsoft.com/kb/937137" target="_blank">
http://support.microsoft.com/kb/937137</a><br>-- The SQL Server 2005 
builds that were released after SQL Server 2005 Service Pack 3 was 
released<br>
<a href="http://support.microsoft.com/kb/960598" target="_blank">
http://support.microsoft.com/kb/960598</a><br>-- The SQL Server 2005 
builds that were released after SQL Server 2005 Service Pack 4 was 
released <br>
<a href="http://support.microsoft.com/kb/2485757" target="_blank">
http://support.microsoft.com/kb/2485757</a></td>
</tr>
</table>
'

/*
When was SQL Server last Installed date
*/

print N'<H3>SQL Server Name and Installation Detail</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+

N'<tr><th><strong>Server Name</strong></th>'+
N'<th><strong>SQL Installation Date</strong></th></tr>'


declare cur_sql_sqlinstall cursor local for SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date] 
FROM sys.syslogins 
WHERE [sid] = 0x010100000000000512000000;
open cur_sql_sqlinstall 
fetch next from cur_sql_sqlinstall into @SQLSer_OSInfo,@SQL_inst_date
while @@fetch_status>=0
begin 
print '<tr><td>'+@SQLSer_OSInfo+'</td><td>'+@SQL_inst_date+'</td>'+'</tr>'
fetch next from cur_sql_sqlinstall into @SQLSer_OSInfo,@SQL_inst_date
end
close cur_sql_sqlinstall
deallocate cur_sql_sqlinstall
print'</table><br/>'

/*
Get selected server properties (SQL Server 2005)
-- This gives you a lot of useful information about your instance of SQL Server

*/

print N'<H3>SQL Server Server properties</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+


N'<tr><th><strong>Machine Name</strong></th>'+
N'<th><strong>Server Name</strong></th>'+
N'<th><strong>Instance Name</strong></th>'+
N'<th><strong>Is Clustered</strong></th>'+
N'<th><strong>Computer Netbios Name</strong></th>'+
N'<th><strong>SQL Edition</strong></th>'+
N'<th><strong>SQL Product Patch Level</strong></th>'+
N'<th><strong>SQL Product Product Version</strong></th>'+
N'<th><strong>SQL Process ID</strong></th>'+
N'<th><strong>SQL Instance Collation</strong></th>'+
N'<th><strong>SQL FullText Installed</strong></th>'+
N'<th><strong>SQL IsIntegratedSecurityOnly</strong></th></tr>'

declare cur_sql_sqlpropties cursor local for 
SELECT 
cast(SERVERPROPERTY('MachineName') as varchar(200)) AS [MachineName], 
cast(SERVERPROPERTY('ServerName') as varchar(200)) AS [ServerName],  
cast(SERVERPROPERTY('InstanceName') as varchar(200)) AS [Instance],
cast(SERVERPROPERTY('IsClustered') as varchar(200)) AS [IsClustered], 
CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(200)) AS [ComputerNamePhysicalNetBIOS], 
cast(SERVERPROPERTY('Edition') as varchar(200)) AS [Edition],
cast(SERVERPROPERTY('ProductLevel') as varchar(200)) AS [ProductLevel], 
cast(SERVERPROPERTY('ProductVersion') as varchar(200)) AS [ProductVersion],
cast(SERVERPROPERTY('ProcessID') as varchar(200)) AS [ProcessID],
cast(SERVERPROPERTY('Collation') as varchar(200)) AS [Collation],
cast(SERVERPROPERTY('IsFullTextInstalled') as varchar(200)) AS [IsFullTextInstalled], 
cast(SERVERPROPERTY('IsIntegratedSecurityOnly') as varchar(200)) AS [IsIntegratedSecurityOnly]

open cur_sql_sqlpropties
fetch next from cur_sql_sqlpropties into 
@Srv_Machine,
@SrvName_prop,
@InstName,
@IsCluster,
@CompNetbios,
@SqlEdition,
@SqlProductLevel,
@SqlProdVer,
@SqlProid,
@Sql_Ins_collation,
@IsfullText,
@IsInterSec
while @@fetch_status>=0
begin 

if(@InstName IS NULL)
begin
set @InstName = 'Default'
end
print '<tr><td>'+@Srv_Machine+'</td><td>'+@SrvName_prop+'</td><td>'+@InstName+'</td><td>'+@IsCluster+'</td><td>'+@CompNetbios+'</td><td>'+@SqlEdition+'</td><td>'+@SqlProductLevel+'</td><td>'+@SqlProdVer+'</td><td>'+@SqlProid+'</td><td>'+@Sql_Ins_collation+'</td><td>'+@IsfullText+'</td><td>'+@IsInterSec+'</td>'+'</tr>'
--print 'I am in the cursor'
fetch next from cur_sql_sqlpropties into 
@Srv_Machine,
@SrvName_prop,
@InstName,
@IsCluster,
@CompNetbios,
@SqlEdition,
@SqlProductLevel,
@SqlProdVer,
@SqlProid,
@Sql_Ins_collation,
@IsfullText,
@IsInterSec
end
close cur_sql_sqlpropties
deallocate cur_sql_sqlpropties
print'</table><br/>'
print '<table >
<tr>
<td>--In the configuration detail where 0 is disable and 1 is enable.</td>
</tr>
</table>
<br/>'
/*

CPU Hardware Information for SQL Server 2005 

 */
print N'<H3>SQL Server Server CPU Information</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Logical CPU Count</strong></th>'+
N'<th><strong>Hyperthreading Ratio</strong></th>'+
N'<th><strong>Physical CPU Count</strong></th>'+
N'<th><strong>Physical RAM</strong></th></tr>'

declare sql_cpu_prop cursor local for
SELECT cast(cpu_count as varchar(10)) AS [Logical CPU Count], cast(hyperthread_ratio as varchar(10)) AS [Hyperthread Ratio],
cast(cpu_count/hyperthread_ratio as varchar(10)) AS [Physical CPU Count], 
cast(physical_memory_in_bytes/1048576 as varchar(10)) AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info 


open sql_cpu_prop

fetch from sql_cpu_prop into 
@LogicalCPUCount,
@HTRatio,
@PhyCPUCount,
@PhyRAM_MB
while @@fetch_status>=0
begin 
print '<tr><td>'+@LogicalCPUCount+'</td><td>'+@HTRatio+'</td><td>'+@PhyCPUCount+'</td><td>'+@PhyRAM_MB+'</td>'+'</tr>'
fetch from sql_cpu_prop into 
@LogicalCPUCount,
@HTRatio,
@PhyCPUCount,
@PhyRAM_MB
end
close sql_cpu_prop
deallocate sql_cpu_prop
print'</table><br/>'
print '<br>
<table >
<tr>
<td>-- In this above Table we have mention table Server CPU 
configuration along with  total physical RAM available on the 
server.<br>-- It is good to to check Hyperthreading Ratio for CPU some 
time CPU pressure can be contribute by it.<br>-- This does not 
distinguish between multicore and hyperthreading.</td>
</tr>
</table>'

/*
Server Model and Manufacturer and processor model
*/
set nocount on 
print N'<H3>Server Processor Information</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Processor Value</strong></th>'+
N'<th><strong>Processor Name</strong></th></tr>'
--declare @ProcName Table
--( Value varchar(200),
--  Name varchar(400)
-- )
set nocount on
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#ProcName') AND type in (N'U'))
DROP TABLE #ProcName 
create table #ProcName( Value varchar(200),Name varchar(400))
 insert into #ProcName exec xp_instance_regread 
'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';

--select * from @ProcName

declare cur_proc_name cursor local for select value,Name from #ProcName 

open cur_proc_name

fetch from cur_proc_name into 
@Proc_Value,
@Proc_date

while @@fetch_status>=0
begin 
print '<tr><td>'+@Proc_Value+'</td><td>'+@Proc_date+'</td>'+'</tr>'

fetch from cur_proc_name into 
@Proc_Value,
@Proc_date
end

close cur_proc_name
deallocate cur_proc_name
set nocount off
print'</table><br/>'
print '<br>
<table >
<tr>
<td>--Above Table will give you information about the CPU make and moel 
and clock speed information.</td>
</tr>
</table>'



/*
SQL Server configuration setting Information.
*/

print N'<H3>SQL Server SP_CONFIGURE Information For Instance</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Parameter Name</strong></th>'+
'<th><strong>Parameter Value</strong></th>'+
'<th><strong>Parameter Running Vlaue</strong></th>'+
N'<th><strong>Parameter Description</strong></th></tr>'


declare cur_sql_spconfig cursor local for SELECT name, cast(value as varchar(10)) as value,CAST(value_in_use as varchar(10)) as valueinuse, [description] 
FROM sys.configurations 
ORDER BY name;

open cur_sql_spconfig

fetch from cur_sql_spconfig into
@Sp_config_Name,
@Sp_config_value,
@Sp_config_inusevalue,
@Sp_config_des


while @@fetch_status>=0
begin 
print '<tr><td>'+@Sp_config_Name+'</td><td>'+@Sp_config_value+'</td><td>'+@Sp_config_inusevalue+'<td>'+@Sp_config_des+'</td>'+'</tr>'

fetch from cur_sql_spconfig into
@Sp_config_Name,
@Sp_config_value,
@Sp_config_inusevalue,
@Sp_config_des
end

close cur_sql_spconfig
deallocate cur_sql_spconfig
print'</table><br/>'

print'<table >
<tr>
<td>--Above table will show you SQL Server Instance Level configuration 
settings. Whic is very important to know and set it to proper according 
value in the first will save you from lot of performance related issues 
in the future.<br><strong><span >-- Focus on the 
following parameter.</span><br >1.Max Degree of 
Parallelism:-<br>--</strong>Set this option based on the your instance 
database configuration whether you have OLTP databases or DSS(Reporting) 
databases.For OLTP databases we dont need much processing power since 
ammount of transaction would very small.<br>--While in DSS or Reporting 
system we definetly need more CPU since many of queries doing select 
with conditional logic and that would be always fast if it would get 
benifited from parallel processing.<br>--Set this value  to 0 
indicate SQL can use all available CPU on the server for processing 
while setting to 1 indicate SQL can only use single CPU for processing.<br>
--You can set this value based on the number of processsor you have and 
type of your workload(OLTP,DSS).<br><strong>2.Max Server Memory:-<br>--</strong>This 
option is also very important for setting working set size for the SQL 
Server instance and also used to limit memory utilization on the server 
by instance.<br>-- This option has to be set for your instnace in order 
to avoid memory throtlling and memory bottleneck problem on the system. 
This option set memory dynamic so no need to restart SQL Server in order 
to take in to effect.<br>-- Hypothetical example of memory distribution 
System with having 32 GB RAM with 64 bit OS Single Production SQL Server 
instnace running on it then we can divide memory for OS to 6 GB rest 26 
GB to SQL and if you have any other application on the same box other 
than SQL then you have to further reduce SQL Server Max Server Memory.<br>
-- For Better tunning of Max Server Memory use Performance Monitor to 
examine the SQLServer:Buffer Manager performance object while under a 
load, and note the current values of the Stolen pages and Reserved pages 
counters. These counters report memory as the number of 8K pages. max 
server memory should be set above the sum of these two values to avoid 
out-of-memory errors.<br><strong>3. CLR Enabled:-</strong><br>--This 
should be set to 0 if you don't use any .Net related commond language 
run time.If you need it then enable it.<br><strong>4.lightweight 
pooling:-<br>--</strong>Setting lightweight pooling to 1 causes SQL 
Server to switch to fiber mode scheduling. The default value for this 
option is 0.<br>--Use the lightweight pooling option to provide a means 
of reducing the system overhead associated with the excessive context 
switching sometimes seen in symmetric multiprocessing (SMP) 
environments. When excessive context switching is present, lightweight 
pooling can provide better throughput by performing the context 
switching inline, thus helping to reduce user/kernel ring transitions.<br>
<em>--We do not recommend that you use fiber mode scheduling for routine 
operation. This is because it can decrease performance by inhibiting the 
regular benefits of context switching, and because some components of 
SQL Server that use Thread Local Storage (TLS) or thread-owned objects, 
such as mutexes (a type of Win32 kernel object), cannot function 
correctly in fiber mode.<br></em>5.Priority Boost:-<br>--By setting this 
option to 1 allows SQL Server to run on Windows Server with highest 
priority on Windows Scheduler. <br>-- If this option is enable then SQL 
Server will run on Windows Scheduler with priority base of 13 and in 
normal mode it will be running with priority base of 7<br>--We have seen 
failover issues in the past on Failover Cluster system when you ran SQL 
Server with High Priority boost.<br>-- So try to avoid configuring SQL 
Server for this option.<br><strong>5.optimize for ad hoc workloads:-</strong><br>
--The optimize for ad hoc workloads option is used to improve the 
efficiency of the plan cache for workloads that contain many single use 
ad hoc batches.<br>--When this option is set to 1, the Database Engine 
stores a small compiled plan stub in the plan cache when a batch is 
compiled for the first time, instead of the full compiled plan. <br>
--This helps to relieve memory pressure by not allowing the plan cache 
to become filled with compiled plans that are not reused.<br>--The 
compiled plan stub allows the Database Engine to recognize that this ad 
hoc batch has been compiled before but has only stored a compiled plan 
stub, so when this batch is invoked (compiled or executed) again, the 
Database Engine compiles the batch, removes the compiled plan stub from 
the plan cache, and adds the full compiled plan to the plan cache.<br>
--Setting the optimize for ad hoc workloads to 1 affects only new plans; 
plans that are already in the plan cache are unaffected.</td>
</tr>
</table>
<br/>'

/*
Database Data FIles Detail
*/



print N'<H3>SQL Server Databases Datafiles location size and status</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Database Name</strong></th>'+
'<th><strong>DB File Id</strong></th>'+
'<th><strong>DB File Name</strong></th>'+
'<th><strong>DB Physical File Name</strong></th>'+
'<th><strong>DB file Type</strong></th>'+
'<th><strong>DB File Status</strong></th>'+
N'<th><strong>DB File Size in (MB)</strong></th></tr>'


declare cur_db_datafiles cursor local for 
SELECT cast(DB_NAME([database_id]) as varchar(400))AS [Database Name], 
       cast([file_id] as varchar(10)) as File_id,
       name, 
       physical_name,
       type_desc, 
       state_desc, 
       cast(CONVERT( bigint, size/128.0) as varchar(200)) AS [Total Size in MB]
FROM sys.master_files 
WHERE [database_id] > 4 
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]);

open cur_db_datafiles 

fetch from cur_db_datafiles into 
@db_det_name,
@db_det_fileid,
@db_det_filename,
@db_det_phyfilename,
@db_det_filedesc,
@db_det_statedesc,
@db_det_filesizeMB

while @@fetch_status>=0
begin
print '<tr><td>'+@db_det_name+'</td><td>'+@db_det_fileid+'</td><td>'+@db_det_filename+'</td><td>'+@db_det_phyfilename+'</td><td>'+@db_det_filedesc+'</td><td>'+@db_det_statedesc+'</td><td>'+@db_det_filesizeMB+'</td>'+'</tr>'
fetch from cur_db_datafiles into 
@db_det_name,
@db_det_fileid,
@db_det_filename,
@db_det_phyfilename,
@db_det_filedesc,
@db_det_statedesc,
@db_det_filesizeMB
end

close cur_db_datafiles
deallocate cur_db_datafiles

print'</table><br/>'

print'<table >
<tr>
<td><span ><strong>SQL Server Databases Datafiles 
location size and status</strong></span><br>--The above table provides 
you inforation about your databases Files and their respective location 
with status of the file and along with FileSize.<br>--Things to look at 
also Files for all Databases are on the same drive.<br>-- Files like 
data file and log file are on diffrent drive.<br>-- How many files we 
have for tempdb and are they at same size.<br>-- Is tempdb is on 
dedicated drive.<br>-- Idle condition log file should be put on the very 
fast drive so we will not have IO latency bottelneck while performing 
transactions.</td>
</tr>
</table>
<br/>'


/*
Database Congiuration Properties QUery.
*/

print '<H3>SQL Server Databases Configuration Properties</H3>'
print '<table cellspacing="1" cellpadding="1" border="1">'+
'<tr><th><strong>Database Name</strong></th>'+
'<th><strong>DB Recovery Model</strong></th>'+
'<th><strong>DB Log Reuse Wait Description</strong></th>'+
'<th><strong>DB Log File Size(KB)</strong></th>'+
'<th><strong>DB Log File Used Size(KB)</strong></th>'+
'<th><strong>DB Log File Used(%)</strong></th>'+
'<th><strong>DB Compatibility Level</strong></th>'+
'<th><strong>DB Page Verify Option</strong></th>'+
'<th><strong>DB is_auto_create_stats_on</strong></th>'+
'<th><strong>DB is_auto_update_stats_on</strong></th>'+
'<th><strong>DB is_auto_update_stats_async_on</strong></th>'+
'<th><strong>DB Force Parameterization</strong></th>'+
'<th><strong>DB Snapshot Isolation State</strong></th>'+
'<th><strong>DB Read Commited Snapshot On</strong></th>'+
'<th><strong>DB AutoClose On</strong></th>'+
'<th><strong>DB AutoShrink On</strong></th></tr>'


declare cur_db_log_info cursor local for 
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced, 
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on
FROM sys.databases AS db 
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0;

open cur_db_log_info
fetch from cur_db_log_info into 
@db_log_info_dbname, 
@db_log_info_rmodle, 
@db_log_info_logreusewait,
@db_log_info_logsizekb, 
@db_log_info_logusedkb, 
@db_log_info_logusedper, 
@db_log_info_dbcmptlevel,
@db_log_info_pageverify, 
@db_log_info_autstats, 
@db_log_info_autoupdstats, 
@db_log_info_autstatsasyncon, 
@db_log_info_parameterrizatio, 
@db_log_info_snapshotisolation, 
@db_log_info_readcommitedsnapshot, 
@db_log_info_autoclose,
@db_log_info_autoshrink

while @@fetch_status>=0
begin
print '<tr><td>'+cast(@db_log_info_dbname as varchar(500))+'</td><td>'+cast(@db_log_info_rmodle as varchar(500))+'</td><td>'+cast(@db_log_info_logreusewait as varchar(500))+'</td><td>'+cast(@db_log_info_logsizekb as varchar(500))+'</td><td>'+cast(@db_log_info_logusedkb as varchar(500))+'</td><td>'+cast(@db_log_info_logusedper as varchar(500))+'</td><td>'+cast(@db_log_info_dbcmptlevel as varchar(500))+'</td><td>'+cast(@db_log_info_pageverify as varchar(500))+'</td><td>'+cast(@db_log_info_autstats as varchar(500))+'</td><td>'+cast(@db_log_info_autoupdstats as varchar(500))+'</td><td>'+cast(@db_log_info_autstatsasyncon as varchar(500))+'</td><td>'+cast(@db_log_info_parameterrizatio as varchar(500))+'</td><td>'+cast(@db_log_info_snapshotisolation as varchar(500))+'</td><td>'+cast(@db_log_info_readcommitedsnapshot as varchar(500))+'</td><td>'+cast(@db_log_info_autoclose as varchar(500))+'</td><td>'+cast(@db_log_info_autoshrink as varchar(500))+'</td>'+'</tr>'
fetch from cur_db_log_info into 
@db_log_info_dbname, 
@db_log_info_rmodle, 
@db_log_info_logreusewait,
@db_log_info_logsizekb, 
@db_log_info_logusedkb, 
@db_log_info_logusedper, 
@db_log_info_dbcmptlevel,
@db_log_info_pageverify, 
@db_log_info_autstats, 
@db_log_info_autoupdstats, 
@db_log_info_autstatsasyncon, 
@db_log_info_parameterrizatio, 
@db_log_info_snapshotisolation, 
@db_log_info_readcommitedsnapshot, 
@db_log_info_autoclose,
@db_log_info_autoshrink
end
close cur_db_log_info
deallocate cur_db_log_info

print'</table><br/>'
print'<table >
<tr>
<td><span ><strong>SQL Server Databases Configuration 
Properties:-</strong></span><br>--In the above table will show you each 
database properties configuration information like.<br>1.Recovery Model<br>
2.Transaction Log Reuse Wait Description.<br>3. DB log file size in KB<br>
4. DB log file used size in KB<br>5. DB log file percentage usage.<br>-- 
Another parameter is also very important is DB Compatibility level this 
parameter shows values like (80,90,100,110). Where if you have restored 
any of SQL Server database from older version to new version you will 
have to change this option.<br>--Consequnces of this option not set 
could be SQL databases which having SQL 2000(80) compatibility version 
can use old query optimization techniques on advance version of SQL 
Server which intern may degraded performance of the SQL Server.<br>
--Database Parameterization option is set to simple SQL Server query 
optimizer may choose to parameterize the queries. This means that any 
literal values that are contained in a query are substituted with 
parameters.<br>--When SIMPLE parameterization is in effect, you cannot 
control which queries are parameterized and which queries are not. 
However, you can specify that all queries in a database be parameterized 
by setting the PARAMETERIZATION database option to FORCED. This process 
is referred to as forced parameterization.<br>--you can specify that 
forced parameterization is attempted on a certain class of queries. You 
do this by creating a TEMPLATE plan guide on the parameterized form of 
the query, and specifying the PARAMETERIZATION FORCED query hint in the 
sp_create_plan_guide stored procedure. You can consider this kind of 
plan guide as a way to enable forced parameterization only on a certain 
class of queries, instead of all queries.<br>--When the PARAMETERIZATION 
database option is set to FORCED, you can specify that for a certain 
class of queries, only simple parameterization is attempted, not forced 
parameterization. You do this by creating a TEMPLATE plan guide on the 
force-parameterized form of the query, and specifying the 
PARAMETERIZATION SIMPLE query hint in <b>sp_create_plan_guide</b>.</td>
</tr>
</table>
<br><br/>'