SQL Server Performance Base Line Script part4

/*
Memory Usage by Hash Sort Index Creation Operation
*/
print N'<H3>Dynamic Memory Usage for Hash sort Index Creation:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Memory in KB</strong></th>'+
'<th><strong>Memory in MB</strong></th>'+
N'<th><strong>Memory in GB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_idexsort_memusg cursor local for
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Granted Workspace Memory (KB) '

open cur_idexsort_memusg
fetch from cur_idexsort_memusg into
@idexsort_userinkb,
@idexsort_userinMb,
@idexsort_userinGb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@idexsort_userinkb as varchar(100))+
 '</td><td>'+cast(@idexsort_userinMb as varchar(40))+
 '</td><td>'+cast(@idexsort_userinGb as varchar(100))+'</td>'+'</tr>'
fetch from cur_idexsort_memusg into
@idexsort_userinkb,
@idexsort_userinMb,
@idexsort_userinGb
end
close cur_idexsort_memusg
deallocate cur_idexsort_memusg
print'</table><br/>'
print'<br>
<table >
<tr>
<td><span ><strong>SQL Server memory usage for Hash
Sort and Index Creation:-</strong></span><br>--Specifies the total
amount of memory currently granted to executing processes, such as hash,
sort, bulk copy, and index creation operations.ons.</td>
</tr>
</table>
<br/>'
/*
Dynamic memory consumed by Cursor
*/
print N'<H3>Dynamic Memory Usage by SQL Cursors:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Memory in KB</strong></th>'+
'<th><strong>Memory in MB</strong></th>'+
N'<th><strong>Memory in GB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_curmemusginfo cursor local for
SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'

open cur_curmemusginfo
fetch from cur_curmemusginfo into
@curmem_useinkb ,
@curmem_useinMb ,
@curmem_useinGb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@curmem_useinkb as varchar(100))+
 '</td><td>'+cast(@curmem_useinMb as varchar(40))+
 '</td><td>'+cast(@curmem_useinGb as varchar(100))+'</td>'+'</tr>'
fetch from cur_curmemusginfo into
@curmem_useinkb ,
@curmem_useinMb ,
@curmem_useinGb
end
close cur_curmemusginfo
deallocate cur_curmemusginfo
print'</table><br/>'
print '<br>
<table >
<tr>
<td>SQL Server Memory Usage by SQL Cursors:-<br>--Memory utilize by SQL
Server cursor.</td>
</tr>
</table>
<br/>'
/*
Number of Pages Consumed in buffer pool includes(free,database,stolen)
*/
print N'<H3>Bufferpool Pages(Includes Free,Datapage,Stolen):-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>No of 8KB Pages</strong></th>'+
'<th><strong>Pages in KB</strong></th>'+
N'<th><strong>Pages in MB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_buffpoolpage_info cursor local for
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages'

open cur_buffpoolpage_info
fetch from cur_buffpoolpage_info into
@bpool_page_8kbno,
@bpool_pages_inkb,
@bpool_pages_inmb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@bpool_page_8kbno as varchar(100))+
 '</td><td>'+cast(@bpool_pages_inkb as varchar(40))+
 '</td><td>'+cast(@bpool_pages_inmb as varchar(100))+'</td>'+'</tr>'
fetch from cur_buffpoolpage_info into
@bpool_page_8kbno,
@bpool_pages_inkb,
@bpool_pages_inmb
end
close cur_buffpoolpage_info
deallocate cur_buffpoolpage_info
print'</table><br/>'
print '<br>
<table >
<tr>
<td><strong><span >Buffer Pool Usage:-</span><br >
</strong>--Total Number of pages that are included in the buffer pool it
includes Data pages Free pages and Stolen pages.</td>
</tr>
</table>
<br/>'
/*
Total Number of Data Pages in Buffer Pool
*/
print N'<H3>Bufferpool Pages Total Number of DataPages:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>No of 8KB Pages</strong></th>'+
'<th><strong>Pages in KB</strong></th>'+
N'<th><strong>Pages in MB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_bpooldbpage_info cursor local for
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages'

open cur_bpooldbpage_info
fetch from cur_bpooldbpage_info into
@dbpagebpool_page_8kbno,
@dbpagebpool_page_inkb ,
@dbpagebpool_page_inmb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@dbpagebpool_page_8kbno as varchar(100))+
 '</td><td>'+cast(@dbpagebpool_page_inkb as varchar(40))+
 '</td><td>'+cast(@dbpagebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
fetch from cur_bpooldbpage_info into
@dbpagebpool_page_8kbno,
@dbpagebpool_page_inkb ,
@dbpagebpool_page_inmb
end
close cur_bpooldbpage_info
deallocate cur_bpooldbpage_info
print'</table><br/>'
print'<br>
<table >
<tr>
<td><strong>Bpool Number of Data Pages:-<br></strong>--Number of pages
in the buffer pool with database content.</td>
</tr>
</table>
<br/>'

/*
Total Number of Free Pages in Buffer Pool
*/
print N'<H3>Bufferpool Pages Total Number of FreePages:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>No of 8KB Pages</strong></th>'+
'<th><strong>Pages in KB</strong></th>'+
N'<th><strong>Pages in MB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_bpoolfreepage_info cursor local for
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages'

open cur_bpoolfreepage_info
fetch from cur_bpoolfreepage_info into
@freepagebpool_page_8kbno,
@freepagebpool_page_inkb,
@freepagebpool_page_inmb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@freepagebpool_page_8kbno as varchar(100))+
 '</td><td>'+cast(@freepagebpool_page_inkb as varchar(40))+
 '</td><td>'+cast(@freepagebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
fetch from cur_bpoolfreepage_info into
@freepagebpool_page_8kbno,
@freepagebpool_page_inkb,
@freepagebpool_page_inmb
end
close cur_bpoolfreepage_info
deallocate cur_bpoolfreepage_info
print'</table><br/>'
print '<br>
<table >
<tr>
<td><span ><strong>Bpool Total Number of Free Pages:-</strong></span><br>
--Number of requests per second that had to wait for a free page.Total
number of pages on all free lists.</td>
</tr>
</table>
<br/>'
/*
--Number of reserved pages in the buffer pool
*/
print N'<H3>Bufferpool Pages Total Number of Reserved Pages:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>No of 8KB Pages</strong></th>'+
'<th><strong>Pages in KB</strong></th>'+
N'<th><strong>Pages in MB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_bpoolresvpage_info cursor local for
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages'
open cur_bpoolresvpage_info
fetch from cur_bpoolresvpage_info into
@respagebpool_page_8kbno ,
@respagebpool_page_inkb ,
@respagebpool_page_inmb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@respagebpool_page_8kbno as varchar(100))+
 '</td><td>'+cast(@respagebpool_page_inkb as varchar(40))+
 '</td><td>'+cast(@respagebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
fetch from cur_bpoolresvpage_info into
@respagebpool_page_8kbno ,
@respagebpool_page_inkb ,
@respagebpool_page_inmb
end
close cur_bpoolresvpage_info
deallocate cur_bpoolresvpage_info
print'</table><br/>'
print '<br>
<table >
<tr>
<td>Bpool Total Number of Reserved Pages:-<br>--Number of buffer pool
reserved pages.</td>
</tr>
</table>
<br/>
'
/*
Number of stolen pages in Bpool
*/
print N'<H3>Bufferpool Pages Total Number of Stolen Pages:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>No of 8KB Pages</strong></th>'+
'<th><strong>Pages in KB</strong></th>'+
N'<th><strong>Pages in MB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_bpoolstolenpage_info cursor local for
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages'

open cur_bpoolstolenpage_info
fetch  from cur_bpoolstolenpage_info into
@stolenpbpool_page_8kbno ,
@stolenpbpool_page_inkb ,
@stolenpbpool_page_inmb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@stolenpbpool_page_8kbno as varchar(100))+
 '</td><td>'+cast(@stolenpbpool_page_inkb as varchar(40))+
 '</td><td>'+cast(@stolenpbpool_page_inmb as varchar(100))+'</td>'+'</tr>'
fetch  from cur_bpoolstolenpage_info into
@stolenpbpool_page_8kbno ,
@stolenpbpool_page_inkb ,
@stolenpbpool_page_inmb

end
close cur_bpoolstolenpage_info
deallocate cur_bpoolstolenpage_info
print'</table><br/>'
print'<br>
<table >
<tr>
<td><strong><span >Bpool Total number of Stolen
Pages:-</span><br ></strong>The size of SQL Server
database page is 8KB. Buffer Pool is a cache of data pages. Consequently
Buffer Pool operates on pages of 8KB in size. It commits and decommits
memory blocks of 8KB granularity only. If external components decide to
borrow memory out of Buffer Pool they can only get blocks of 8KB in
size. These blocks are not continues in memeory. Interesting, right? It
means that Buffer Pool can be used as underneath memory manager forSQL
Server components as long as they allocate buffers of 8KB. (Sometimes
pages allocated from BP are referred as stolen)<br></td>
</tr>
</table>
<br/>'
/*
Number plan cache pages in Buffer pool
*/
print N'<H3>Bufferpool Pages Total Number of Plan Cache Pages:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>No of 8KB Pages</strong></th>'+
'<th><strong>Pages in KB</strong></th>'+
N'<th><strong>Pages in MB</strong></th></tr>'

SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'

declare cur_bpoolplancache_info cursor local for
SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages'  and instance_name = '_Total'

open cur_bpoolplancache_info
fetch from cur_bpoolplancache_info into
@plancachebpool_page_8kbno ,
@plancachebpool_page_inkb ,
@plancachebpool_page_inmb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@plancachebpool_page_8kbno as varchar(100))+
 '</td><td>'+cast(@plancachebpool_page_inkb as varchar(40))+
 '</td><td>'+cast(@plancachebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
fetch from cur_bpoolplancache_info into
@plancachebpool_page_8kbno ,
@plancachebpool_page_inkb ,
@plancachebpool_page_inmb
end
close cur_bpoolplancache_info
deallocate cur_bpoolplancache_info
print'</table><br/>'
print'<br>
<table >
<tr>
<td><span ><strong>Bpool plan cache pages:-</strong></span><br>
--This metric counts the number of 8-kilobyte (KB) pages used by plan
cache objects, which indicates the plan cache size of an instance. This
counter is very similar to the SQL Server: memory manager: SQL cache
memory, but instead of providing the number of 8-kilobyte pages that
make up the plan cache, it provides the total amount of memory, in
kilobytes, used by the plan cache.</td>
</tr>
</table>
<br/>'
/*
--SQL Server Binary Module Information

*/

print N'<H3>SQL Server Binary Module Informatio:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Name and Path of File</strong></th>'+
'<th><strong>File Version</strong></th>'+
'<th><strong>Product Version</strong></th>'+
'<th><strong>Description of Module</strong></th>'+
N'<th><strong>Module Size KB</strong></th></tr>'

declare cur_sqlbinmodule_info cursor local for
SELECT olm.[name], olm.[file_version], olm.[product_version], olm.[description], SUM(ova.[region_size_in_bytes])/1024 [Module Size in KB]
FROM sys.dm_os_virtual_address_dump ova
INNER JOIN sys.dm_os_loaded_modules olm ON olm.base_address = ova.region_allocation_base_address
GROUP BY olm.[name],olm.[file_version], olm.[product_version], olm.[description],olm.[base_address]
ORDER BY [Module Size in KB] DESC

open cur_sqlbinmodule_info
fetch from cur_sqlbinmodule_info into
@DllFilePath,
@FileVer,
@Productver,
@Bin_Descrip,
@Modulesize_inkb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@DllFilePath as varchar(2000))+
 '</td><td>'+cast(@FileVer as varchar(400))+
 '</td><td>'+cast(@Productver as varchar(400))+
 '</td><td>'+cast(@Bin_Descrip as varchar(5000))+
 '</td><td>'+cast(@Modulesize_inkb as varchar(100))+'</td>'+'</tr>'
fetch from cur_sqlbinmodule_info into
@DllFilePath,
@FileVer,
@Productver,
@Bin_Descrip,
@Modulesize_inkb
end
close cur_sqlbinmodule_info
deallocate cur_sqlbinmodule_info
print'</table><br/>'
print'<br>
<table >
<tr>
<td><strong><span >SQL Server Binary Information:-</span><br >
--</strong>The above table contains information about SQL Server binary
information loaded inside in SQL Server OS.</td>
</tr>
</table>
<br/>'

/*
Version Store Information
*/

print N'<H3>SQL Server Version Store Informatio:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Version Store Pages Used</strong></th>'+
N'<th><strong>Version stored space in MB</strong></th></tr>'

declare cur_versionstoreinfo cursor local for
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]FROM sys.dm_db_file_space_usage

open  cur_versionstoreinfo
fetch from cur_versionstoreinfo into
@verstorepage_used,
@verstorepage_spaceinMB
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@verstorepage_used as varchar(200))+
 '</td><td>'+cast(@verstorepage_spaceinMB as varchar(100))+'</td>'+'</tr>'
fetch from cur_versionstoreinfo into
@verstorepage_used,
@verstorepage_spaceinMB
end
close cur_versionstoreinfo
deallocate cur_versionstoreinfo
print'</table><br/>'
print'<br>
<table >
<tr>
<td>SQL Server Version Store Information:-<br>-- Version store is
feature available in SQL Server with Snap shot isolation level.But it
has contention on the TEMPDB.<br>-- We have to check if any database is
having snapshot isolation level on.</td>
</tr>
</table>
<br/>'
/*
TempDB pages information for the storaage
*/

print N'<H3>SQL Server Version Store Informatio:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>User Object Pages MB</strong></th>'+
N'<th><strong>Internal Object Pages MB</strong></th>'+
N'<th><strong>Version Store Pages MB</strong></th>'+
N'<th><strong>Total in Use Pages MB</strong></th>'+
N'<th><strong>Total Free Pages MB</strong></th></tr>'

Declare cur_tempdbfileusg_info cursor local for
SELECT (SUM(user_object_reserved_page_count)*8)/1024 AS user_object_pages_mb,
(SUM(internal_object_reserved_page_count)*8)/1024 AS internal_object_pages_mb,
(SUM(version_store_reserved_page_count)*8)/1024 AS version_store_pages_mb,
total_in_use_pages_mb = (SUM(user_object_reserved_page_count)+ SUM(internal_object_reserved_page_count)+ SUM(version_store_reserved_page_count)*8)/1024,
(SUM(unallocated_extent_page_count)*8)/1024 AS total_free_pages_mb
FROM sys.dm_db_file_space_usage ;

open cur_tempdbfileusg_info
fetch from cur_tempdbfileusg_info into
@tempdb_user_obj_pages_inMB,
@tempdb_internal_obj_pages_inMB,
@tempdb_versionstore_obj_pages_inMB,
@tempdb_total_pages_use_inMB ,
@tempdb_total_pages_free_inMB
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@tempdb_user_obj_pages_inMB as varchar(20))+
 '</td><td>'+cast(@tempdb_internal_obj_pages_inMB as varchar(20))+
 '</td><td>'+cast(@tempdb_versionstore_obj_pages_inMB as varchar(50))+
'</td><td>'+cast(@tempdb_total_pages_use_inMB as varchar(50))+
 '</td><td>'+cast(@tempdb_total_pages_free_inMB as varchar(50))+'</td>'+'</tr>'
fetch from cur_tempdbfileusg_info into
@tempdb_user_obj_pages_inMB,
@tempdb_internal_obj_pages_inMB,
@tempdb_versionstore_obj_pages_inMB,
@tempdb_total_pages_use_inMB ,
@tempdb_total_pages_free_inMB
end

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


/*
TempDb usage by session
*/
print N'<H3>SQL Server Tempdb Usaage by Session:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Session ID</strong></th>'+
N'<th><strong>Request ID</strong></th>'+
N'<th><strong>Execution Context ID</strong></th>'+
N'<th><strong>Database ID</strong></th>'+
N'<th><strong>User Object Page Allocation Count</strong></th>'+
N'<th><strong>User Object Page Deallocation Count</strong></th>'+
N'<th><strong>Internal Object Page Allocation Count</strong></th>'+
N'<th><strong>Internal Object Page Deallocation Count</strong></th></tr>'

declare cur_tempdbsessinfo_usg cursor local for
SELECT TOP 10
*
FROM sys.dm_db_task_space_usage
WHERE session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

open cur_tempdbsessinfo_usg
fetch from cur_tempdbsessinfo_usg into
@tempdbsession_sid,
@tempdbsession_requ_sid,
@tempdbsession_execontext_sid,
@tempdbsession_dbid ,
@tempdbsession_usrobjallocpage_count,
@tempdbsession_usrobjdeallocpage_count,
@tempdbsession_internalallocpage_count,
@tempdbsession_internaldeallocpage_count

while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@tempdbsession_sid as varchar(20))+
 '</td><td>'+cast(@tempdbsession_requ_sid as varchar(20))+
 '</td><td>'+cast(@tempdbsession_execontext_sid as varchar(20))+
'</td><td>'+cast(@tempdbsession_dbid as varchar(20))+
'</td><td>'+cast(@tempdbsession_usrobjallocpage_count as varchar(20))+
'</td><td>'+cast(@tempdbsession_usrobjdeallocpage_count as varchar(50))+
'</td><td>'+cast(@tempdbsession_internalallocpage_count as varchar(50))+
 '</td><td>'+cast(@tempdbsession_internaldeallocpage_count as varchar(50))+'</td>'+'</tr>'
fetch from cur_tempdbsessinfo_usg into
@tempdbsession_sid,
@tempdbsession_requ_sid,
@tempdbsession_execontext_sid,
@tempdbsession_dbid ,
@tempdbsession_usrobjallocpage_count,
@tempdbsession_usrobjdeallocpage_count,
@tempdbsession_internalallocpage_count,
@tempdbsession_internaldeallocpage_count
end

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

/*
Top 10 Session in SQL by what they are doing

*/

print N'<H3>SQL Server Top Sessions:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Session ID</strong></th>'+
N'<th><strong>Login Time</strong></th>'+
N'<th><strong>Host Name</strong></th>'+
N'<th><strong>Program Name</strong></th>'+
N'<th><strong>CPU Time</strong></th>'+
N'<th><strong>Memory Usage in KB</strong></th>'+
N'<th><strong>Total Scheduled Time in MS</strong></th>'+
N'<th><strong>Total Elapsed Time in MS</strong></th>'+
N'<th><strong>Last Request End Time</strong></th>'+
N'<th><strong>Number of Reads</strong></th>'+
N'<th><strong>Number of Writes</strong></th>'+
N'<th><strong>Number of Connection Count</strong></th></tr>'

declare cur_topsess_activity cursor local for

select top 10 s.session_id
, s.login_time
, s.host_name
, s.program_name
, s.cpu_time as cpu_time
, s.memory_usage * 8 as memory_usage_in_KB
, s.total_scheduled_time as total_scheduled_time
, s.total_elapsed_time as total_elapsed_time
, s.last_request_end_time
, s.reads
, s.writes
, count(c.connection_id) as conn_count
from sys.dm_exec_sessions s
left outer join sys.dm_exec_connections c on ( s.session_id = c.session_id )
left outer join sys.dm_exec_requests r on ( r.session_id = c.session_id )
where (s.is_user_process= 1)
group by s.session_id, s.login_time, s.host_name, s.cpu_time, s.memory_usage,
s.total_scheduled_time, s.total_elapsed_time, s.last_request_end_time, s.reads,
s.writes, s.program_name
order by s.memory_usage desc

open cur_topsess_activity
fetch from cur_topsess_activity into
@sessionact_sid ,
@sessionact_logintime ,
@sessionact_hostname,
@sessionact_programname,
@sessionact_cputime ,
@sessionact_memusginkb ,
@sessionact_totalschetime ,
@sessionact_totalelsapsedtime ,
@sessionact_lastrequestendtime ,
@sessionact_reads,
@sessionact_write ,
@sessionact_conncount

while @@FETCH_STATUS>=0
 begin
print    '<tr><td>'+cast(@sessionact_sid as varchar(20))+
   '</td><td>'+cast(@sessionact_logintime as varchar(1000))+
   '</td><td>'+cast(@sessionact_hostname as varchar(50))+
'</td><td>'+cast(@sessionact_programname as varchar(520))+
'</td><td>'+cast(@sessionact_cputime as varchar(20))+
'</td><td>'+cast(@sessionact_memusginkb as varchar(50))+
'</td><td>'+cast(@sessionact_totalschetime as varchar(50))+
  '</td><td>'+cast(@sessionact_totalelsapsedtime as varchar(50))+
'</td><td>'+cast(@sessionact_lastrequestendtime as varchar(50))+
'</td><td>'+cast(@sessionact_reads as varchar(50))+
'</td><td>'+cast(@sessionact_write as varchar(50))+
'</td><td>'+cast(@sessionact_conncount as varchar(50))+'</td>'+'</tr>'


fetch from cur_topsess_activity into
@sessionact_sid ,
@sessionact_logintime ,
@sessionact_hostname,
@sessionact_programname,
@sessionact_cputime ,
@sessionact_memusginkb ,
@sessionact_totalschetime ,
@sessionact_totalelsapsedtime ,
@sessionact_lastrequestendtime ,
@sessionact_reads,
@sessionact_write ,
@sessionact_conncount
end

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

print N'<H3>SQL Server Top Activity:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Session ID</strong></th>'+
N'<th><strong>Last Worker Time</strong></th>'+
N'<th><strong>Last Physical Read</strong></th>'+
N'<th><strong>Total Physical Read</strong></th>'+
N'<th><strong>Total Logical Read</strong></th>'+
N'<th><strong>Last Logical Read</strong></th>'+
N'<th><strong>Current Wait Type</strong></th>'+
N'<th><strong>Last Wait Type</strong></th>'+
N'<th><strong>Wait Resource Type</strong></th>'+
N'<th><strong>Wait Time</strong></th>'+
N'<th><strong>Open Transaction Count</strong></th>'+
N'<th><strong>Row Count</strong></th>'+
N'<th><strong>Grant Memory in kB</strong></th>'+
N'<th><strong>SQL Text</strong></th>'+'</tr>'

declare cur_sqlact_info cursor
for
SELECT
Rqst.session_id as SPID,
Qstat.last_worker_time,
Qstat.last_physical_reads,
Qstat.total_physical_reads,
Qstat.total_logical_writes,
Qstat.last_logical_reads,
Rqst.wait_type as CurrentWait,
Rqst.last_wait_type,
Rqst.wait_resource,
Rqst.wait_time,
Rqst.open_transaction_count,
Rqst.row_count,
Rqst.granted_query_memory,
tSQLCall.text as SqlText
FROM sys.dm_exec_query_stats Qstat
JOIN sys.dm_exec_requests Rqst ON
Qstat.plan_handle = Rqst.plan_handle AND Qstat.sql_handle = Rqst.sql_handle
CROSS APPLY sys.dm_exec_sql_text (Rqst.sql_handle) tSQLCall

open cur_sqlact_info
fetch from cur_sqlact_info into
@otran_spid,
@otran_lasworkertime ,
@otran_lastphysicalread ,
@otran_totalphysicalread ,
@otran_totallogicalwrites,
@otran_lastlogicalreads ,
@otran_currentwait ,
@otran_lastwaittype,
@otran_watiresource,
@otran_waittime ,
@otran_opentrancount ,
@otran_rowcount ,
@otran_granterqmem ,
@otran_sqltect

while @@FETCH_STATUS>=0
 begin
print    '<tr><td>'+cast(@otran_spid as varchar(20))+
   '</td><td>'+cast(@otran_lasworkertime as varchar(1000))+
   '</td><td>'+cast(@otran_lastphysicalread as varchar(50))+
'</td><td>'+cast(@otran_totalphysicalread as varchar(520))+
'</td><td>'+cast(@otran_totallogicalwrites as varchar(50))+
'</td><td>'+cast(@otran_lastlogicalreads as varchar(50))+
'</td><td>'+cast(@otran_currentwait as varchar(500))+
  '</td><td>'+cast(@otran_lastwaittype as varchar(500))+
'</td><td>'+cast(@otran_watiresource as varchar(500))+
'</td><td>'+cast(@otran_waittime as varchar(50))+
'</td><td>'+cast(@otran_opentrancount as varchar(50))+
'</td><td>'+cast(@otran_waittime as varchar(50))+
'</td><td>'+cast(@otran_rowcount as varchar(50))+
'</td><td>'+cast(@otran_sqltect as varchar(4000))+'</td>'+'</tr>'


fetch from cur_sqlact_info into
@otran_spid,
@otran_lasworkertime ,
@otran_lastphysicalread ,
@otran_totalphysicalread ,
@otran_totallogicalwrites,
@otran_lastlogicalreads ,
@otran_currentwait ,
@otran_lastwaittype,
@otran_watiresource,
@otran_waittime ,
@otran_opentrancount ,
@otran_rowcount ,
@otran_granterqmem ,
@otran_sqltect

end

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


/*
Message From himalaya dua
*/
print'<table >
<tr>
<td><strong>Thanks for using this reporting solution for SQL Server Base
Line Performance Report.<br>This tool is developed by Himalaya dua<br></strong>
<a href="Download%20Scripts%20for%20SQL%20Server%20Performance%20BaseLine%20Report">
<strong>
<a href="mailto:himalaya.dua@gmail.com?subject=Feed%20Back%20for%20Performance%20Base%20Line%20Tool">Email
to himalaya dua</a><strong><br>
</strong><a href="http://dbahimalaya.weebly.com/meblog.html"><strong>dbahimalaya.com</strong></a><br>
</td>
</tr>
</table>'
print '</HTML>'




/****** Object:  StoredProcedure [dbo].[SP_InstanceBaselinePerfReport]
Script Date: 1/17/2013 10:28:51 PM

Subject:This script will create folder under the master database location as PerformanceBaseline and then it will also create HTML file in this folder
using the server name and created datetime stamp.



******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[SP_InstanceBaselinePerfReport]
/****** Object:  StoredProcedure [dbo].[SP_InstanceBaselinePerfReport]
Script Date: 1/17/2013 10:28:51 PM

Subject:This script will create folder under the master database location as PerformanceBaseline and then it will also create HTML file in this folder
using the server name and created datetime stamp.



******/

as
declare @sql varchar(8000)
declare @sql2 varchar(8000)
declare @path varchar(4000)
declare @foldername varchar(200)
declare @command varchar(4000)
declare @datefile varchar(200)
declare @srvname varchar(200)
declare @ftype varchar(10)
declare @finalfile varchar(2000)
declare @fret int
declare @repret int
declare @value int
DECLARE @fileEx int

declare cur_spvalue cursor for
SELECT cast(value_in_use as int)
FROM sys.configurations  where name='xp_cmdshell'
ORDER BY name ;

open cur_spvalue
fetch from cur_spvalue into
@value
while @@fetch_status>=0
begin
if @value<>1
begin
exec sp_configure 'xp_cmdshell',1
reconfigure with override
end
fetch from cur_spvalue into
@value
end

close cur_spvalue
deallocate cur_spvalue

set @foldername ='PerformanceBaseLine'
set @path=(SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'Data\master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1)
set @path=@path+@foldername
--select @path

create table #tempdir
(
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)
insert into #tempdir(File_Exists,File_is_a_Directory,Parent_Directory_Exists)
EXEC master..xp_fileexist @path
select @fileEx = (select File_is_a_Directory from #tempdir)


Drop table #tempdir

if @fileEx = 0
begin
set @command='mkdir "'+@path+'"'
--select @command
set nocount on

exec @fret= master.dbo.xp_cmdshell @command,no_output
print @fret
if @fret <> 0
begin
print'#############################################################################################################################'
print @path
print'#############################################################################################################################'
print'#############################################################################################################################'
print 'Folder is not able to create on the ' +@path+ ' please validate security settings for this folder for SQL Server service account or folder is already exisit.'
print'#############################################################################################################################'
return 1
end
else
begin
print'#############################################################################################################################'
print 'Folder is created now generating report'
print'#############################################################################################################################'
end
end

set @datefile = GETDATE()
set @datefile=(select convert(datetime,@datefile,126))
set @datefile=Replace(@datefile, ' ', '')
set @datefile=REPLACE(@datefile,':','')
--print @datefile

set @srvname=(select @@SERVERNAME)
set @srvname=REPLACE(@srvname,'\','')
set @path=@path+'\'
set @f'.html'
set @finalfile=(@path+@srvname+@datefile+@ftype)
--print @finalfile

select @sql='sqlcmd -E -Q "exec master.[dbo].[InstanceAnalysis_PerformanceBaseLine]" -o "'+@path+@srvname+@datefile+@ftype+'" -S'+ @@SERVERNAME
--print @sql
exec @repret=master..xp_cmdshell @sql,no_output
if @repret <>0
begin
print'#############################################################################################################################'
print 'Report creating has failed there is something wrong with report.'
print'#############################################################################################################################'
return 1
end
else
begin
print'#############################################################################################################################'
print 'Report is Created fine please check report at this location ' +@finalfile+ '  please validate it'
print'#############################################################################################################################'
   return 0
end

GO


USE [msdb]
GO

if exists(select 1 from sysjobs where name='DBA_PerfBaseline_Report_Job')
begin
declare @jid uniqueidentifier
select @jid=(select job_id from sysjobs where name='DBA_PerfBaseline_Report_Job')
EXEC msdb.dbo.sp_delete_job @job_id=@jid,@delete_unused_schedule=1
end

/****** Object:  Job [DBA_PerfBaseline_Report_Job]    Script Date: 1/17/2013 10:38:09 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 1/17/2013 10:38:09 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @'JOB', @'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_PerfBaseline_Report_Job',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job is creating performance baseline report.For this instance report is locating under the location where master data files are reside.--This job is owned by Physical DBA.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Execute SP For Creating HTML Report]    Script Date: 1/17/2013 10:38:09 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute SP For Creating HTML Report',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
declare @retval int

exec @retval=SP_InstanceBaselinePerfReport

select  @retval
if @retval <> 0
begin
RAISERROR (50005, -- Message id.
           10, -- Severity,
           1, -- State,
           N''PerformanceBaseline Job is failing Please check folder  and FIles or anything wrong with script'')
end',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO