SQL Server Performance Base Line Script part3

/*
Looking for Physical IO read Pressure
Top 20 Executed SP ordered by physical reads.
*/

print N'<H3>SQL Server Top 10 SP Executed by Physical Read:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>SP Name</strong></th>'+
'<th><strong>Total Physical Reads</strong></th>'+
'<th><strong>Avg Physical Reads</strong></th>'+
'<th><strong>Execution Count</strong></th>'+
'<th><strong>Calls/Second</strong></th>'+
'<th><strong>AvgWorker Time(in Microsecond)</strong></th>'+
'<th><strong>TotalWorker Time(in Microsecond)</strong></th>'+
'<th><strong>Avg Elapsed Time(in Microsecond)</strong></th>'+
'<th><strong>Max Logical Reads</strong></th>'+
'<th><strong>Max Logical Writes</strong></th>'+
N'<th><strong>Age In Cache</strong></th></tr>'


declare cur_topspiopre_info cursor local for
SELECT TOP (20) qt.[text] AS [SP Name], qs.total_physical_reads,
qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads],
qs.execution_count AS [Execution Count],
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
--WHERE qt.[dbid] = db_id() -- Filter by current database
ORDER BY qs.total_physical_reads DESC;

open cur_topspiopre_info
fetch from cur_topspiopre_info into
@topsp_iopressure_spname,
@topsp_iopressure_physicalread,
@topsp_iopressure_spname_avgphysicalread,
@topsp_iopressure_spname_Executioncount,
@topsp_iopressure_spname_callsecond,
@topsp_iopressure_spname_Avgworkertime,
@topsp_iopressure_spname_Totalworkertime,
@topsp_iopressure_spname_Avgelapsedtime,
@topsp_iopressure_spname_maxlogicalreads,
@topsp_iopressure_spname_maxlogicalwrite,
@topsp_iopressure_spname_ageincache

while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@topsp_iopressure_spname as varchar(1000))+
 '</td><td>'+cast(@topsp_iopressure_physicalread as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_avgphysicalread as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_Executioncount as varchar(40))+
 '</td><td>'+ISNULL(cast(@topsp_iopressure_spname_callsecond as varchar(40)),0)+
 '</td><td>'+cast(@topsp_iopressure_spname_Avgworkertime as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_Totalworkertime as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_Avgelapsedtime as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_maxlogicalreads as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_maxlogicalwrite as varchar(40))+
 '</td><td>'+cast(@topsp_iopressure_spname_ageincache as varchar(40))+'</td>'+'</tr>'

fetch from cur_topspiopre_info into
@topsp_iopressure_spname,
@topsp_iopressure_physicalread,
@topsp_iopressure_spname_avgphysicalread,
@topsp_iopressure_spname_Executioncount,
@topsp_iopressure_spname_callsecond,
@topsp_iopressure_spname_Avgworkertime,
@topsp_iopressure_spname_Totalworkertime,
@topsp_iopressure_spname_Avgelapsedtime,
@topsp_iopressure_spname_maxlogicalreads,
@topsp_iopressure_spname_maxlogicalwrite,
@topsp_iopressure_spname_ageincache
end

close cur_topspiopre_info
deallocate cur_topspiopre_info
 print'</table><br/>'
 print'<br>
<table >
<tr>
<td><strong><span >SQL Server Top 10 SP Executed by
Physical Read(IO Pressure):-</span><br ></strong>--Above table shows the top 10 stored procedures sorted by total
physical reads(which equates to read I/O pressure). This will tell you
the most expensive stored procedures from a read I/O perspective.<br>--
If it is high Physical Read means SQL has to go to the disk in order to
write the data this inturns very expensive operation.</td>
</tr>
</table>
<br/>'

/*
-- Get Top 25 executed SP's ordered by logical reads (memory pressure)
*/



print N'<H3>SQL Server Top 10 SP Executed by Logical Read(Memory Pressure):-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>SP Name</strong></th>'+
'<th><strong>Total Logical Reads</strong></th>'+
'<th><strong>Execution Count</strong></th>'+
'<th><strong>Average Logical Reads</strong></th>'+
'<th><strong>Calls/Second</strong></th>'+
'<th><strong>AvgWorker Time(in Microsecond)</strong></th>'+
'<th><strong>TotalWorker Time(in Microsecond)</strong></th>'+
'<th><strong>Avg Elapsed Time(in Microsecond)</strong></th>'+
'<th><strong>Total Logical Writes</strong></th>'+
'<th><strong>Max Logical Reads</strong></th>'+
'<th><strong>Max Logical Writes</strong></th>'+
'<th><strong>Total Physical Reads</strong></th>'+
N'<th><strong>Age In Cache</strong></th></tr>'


declare cur_sp_top20logical cursor local for
SELECT TOP(10) qt.[text] AS 'SP Name', total_logical_reads,
qs.execution_count AS 'Execution Count',
total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
qs.execution_count/ISNULL(DATEDIFF(Second, qs.creation_time, GetDate()),1) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
--WHERE qt.[dbid] = db_id() -- Filter by current database
ORDER BY total_logical_reads DESC;

open cur_sp_top20logical
fetch from cur_sp_top20logical into
@topsp_mempressure_spname ,
@topsp_mempressure_totallogicalread ,
@topsp_mempressure_executioncount,
@topsp_mempressure_Avglogicalreads,
@topsp_mempressure_callspersecond ,
@topsp_mempressure_avgworkertime ,
@topsp_mempressure_totalworkertime ,
@topsp_mempressure_Avgelapsedtime ,
@topsp_mempressure_totallogicalwrite ,
@topsp_mempressure_maxlogicalread ,
@topsp_mempressure_maxlogicalwrite ,
@topsp_mempressure_totalphysicalread ,
@topsp_mempressure_ageincache


while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@topsp_mempressure_spname as varchar(1000))+
 '</td><td>'+cast(@topsp_mempressure_totallogicalread as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_executioncount as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_Avglogicalreads as varchar(40))+
 '</td><td>'+ISNULL(cast(@topsp_mempressure_callspersecond as varchar(40)),0)+
 '</td><td>'+cast(@topsp_mempressure_avgworkertime as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_totalworkertime as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_Avgelapsedtime as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_totallogicalwrite as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_maxlogicalread as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_maxlogicalwrite as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_totalphysicalread as varchar(40))+
 '</td><td>'+cast(@topsp_mempressure_ageincache as varchar(40))+'</td>'+'</tr>'

fetch from cur_sp_top20logical into
@topsp_mempressure_spname ,
@topsp_mempressure_totallogicalread ,
@topsp_mempressure_executioncount,
@topsp_mempressure_Avglogicalreads,
@topsp_mempressure_callspersecond ,
@topsp_mempressure_avgworkertime ,
@topsp_mempressure_totalworkertime ,
@topsp_mempressure_Avgelapsedtime ,
@topsp_mempressure_totallogicalwrite ,
@topsp_mempressure_maxlogicalread ,
@topsp_mempressure_maxlogicalwrite ,
@topsp_mempressure_totalphysicalread ,
@topsp_mempressure_ageincache
end


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

 print'<br>
<table >
<tr>
<td><strong>SQL Server Top 10 SP by Logical Reads(Memory Pressure):-</strong><br>
--Above table shows the top 10 stored procedures sorted by total logical
reads(which equates to memory pressure). This will tell you the most
expensive stored procedures from a memory perspective, and indirectly
from a read I/O perspective.</td>
</tr>
</table>
<br/>'
 /*
   Looking at Index Advantage to find missing indexes
-- Missing Indexes by Index Advantage (make sure to also look at last user seek time)
 */

 print N'<H3>SQL Server Missing Indexes by Index Advantage:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Index Advantage</strong></th>'+
'<th><strong>Last User Seek</strong></th>'+
'<th><strong>Datbase Schema Table</strong></th>'+
'<th><strong>Equality Columns</strong></th>'+
'<th><strong>Inequality Columns</strong></th>'+
'<th><strong>Included Columns</strong></th>'+
'<th><strong>Unique Compiles</strong></th>'+
'<th><strong>User Seeks</strong></th>'+
'<th><strong>Average Total user cost</strong></th>'+
N'<th><strong>Average User Impact</strong></th></tr>'



 declare cur_msng_idx_cost_cur cursor local for
 SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS
index_advantage, migs.last_user_seek,
mid.statement AS 'Database.Schema.Table',
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

open cur_msng_idx_cost_cur
fetch from cur_msng_idx_cost_cur into
@msngidx_idxadv,
@msngidx_lastuser_seek,
@msngidx_dbschematable,
@msngidx_equalitycols,
@msngidx_inequalitycols,
@msngidx_includedcols,
@msngidx_uniquecompiles,
@msngidx_userseeks,
@msngidx_avgtotalusercost,
@msngidx_avguserimpact


while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@msngidx_idxadv as varchar(10))+
 '</td><td>'+cast(@msngidx_lastuser_seek as varchar(40))+
 '</td><td>'+cast(@msngidx_dbschematable as varchar(1000))+
 '</td><td>'+cast(@msngidx_equalitycols as varchar(1000))+
 '</td><td>'+cast(@msngidx_inequalitycols as varchar(1000))+
 '</td><td>'+cast(@msngidx_includedcols as varchar(1000))+
 '</td><td>'+cast(@msngidx_uniquecompiles as varchar(40))+
 '</td><td>'+cast(@msngidx_userseeks as varchar(40))+
 '</td><td>'+cast(@msngidx_avgtotalusercost as varchar(40))+
 '</td><td>'+cast(@msngidx_avguserimpact as varchar(40))+'</td>'+'</tr>'

fetch from cur_msng_idx_cost_cur into
@msngidx_idxadv,
@msngidx_lastuser_seek,
@msngidx_dbschematable,
@msngidx_equalitycols,
@msngidx_inequalitycols,
@msngidx_includedcols,
@msngidx_uniquecompiles,
@msngidx_userseeks,
@msngidx_avgtotalusercost,
@msngidx_avguserimpact

end


close cur_msng_idx_cost_cur
deallocate cur_msng_idx_cost_cur
 print'</table><br/>'
 print'<br>
<table >
<tr>
<td><strong><span >SQL Server Missing Indexes by
Index Advantage:-</span><br ></strong>--Above table
will give you a list of indexes that the query optimizer would have
liked to have had, based on the workload.We can see if there are any
tables that jump out with multiple missing indexes.<br>--You may also
want to look at the last_user_seek column to see when was the last time
the optimizer wanted an index. If it is several hours or days ago, it
may have been from an ad-hoc query of maintenance job rather than your
normal workload.</td>
</tr>
</table>
<br/>'

 /*
 --Detecting blocking (a more accurate and complete version)
 */


 print N'<H3>SQL Server Detected Blocking on Instance:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Lock Type</strong></th>'+
'<th><strong>Database Name</strong></th>'+
'<th><strong>Blocked Object</strong></th>'+
'<th><strong>Lock Requested</strong></th>'+
'<th><strong>Waiter Spid</strong></th>'+
'<th><strong>Wait Time(in Microsecond)</strong></th>'+
'<th><strong>Waiter Batch</strong></th>'+
'<th><strong>Waiter Statement</strong></th>'+
'<th><strong>Blocker Sid</strong></th>'+
N'<th><strong>Blocker Statement</strong></th></tr>'



declare cur_sqlblcoking_detail_cur cursor local for
SELECT t1.resource_type AS 'lock type',db_name(resource_database_id) AS 'database',
t1.resource_associated_entity_id AS 'blk object',t1.request_mode AS 'lock req', --- lock requested
t1.request_session_id AS 'waiter sid', t2.wait_duration_ms AS 'wait time',
(SELECT [text] FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE r.session_id = t1.request_session_id) AS 'waiter_batch',
(SELECT substring(qt.text,r.statement_start_offset/2,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE r.statement_end_offset END - r.statement_start_offset)/2)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
WHERE r.session_id = t1.request_session_id) AS 'waiter_stmt',
t2.blocking_session_id AS 'blocker sid',
(SELECT [text] FROM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE p.spid = t2.blocking_session_id) AS 'blocker_stmt'
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address;


open cur_sqlblcoking_detail_cur
fetch from cur_sqlblcoking_detail_cur into
@blocking_lcktype ,
@blocking_dbname ,
@blocking_blockerobj ,
@blocking_lckreque ,
@blocking_waitersid ,
@blocking_waitime ,
@blocking_waitbatch ,
@blocking_waiterstmt ,
@blocking_blockersid ,
@blocking_blocker_stmt



while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@blocking_lcktype as varchar(100))+
 '</td><td>'+cast(@blocking_dbname as varchar(40))+
 '</td><td>'+cast(@blocking_blockerobj as varchar(100))+
 '</td><td>'+cast(@blocking_lckreque as varchar(100))+
 '</td><td>'+cast(@blocking_waitersid as varchar(10))+
 '</td><td>'+cast(@blocking_waitime as varchar(100))+
 '</td><td>'+cast(@blocking_waitbatch as varchar(200))+
 '</td><td>'+cast(@blocking_waiterstmt as varchar(1000))+
 '</td><td>'+cast(@blocking_blockersid as varchar(40))+
 '</td><td>'+cast(@blocking_blocker_stmt as varchar(1000))+'</td>'+'</tr>'
fetch from cur_sqlblcoking_detail_cur into
@blocking_lcktype ,
@blocking_dbname ,
@blocking_blockerobj ,
@blocking_lckreque ,
@blocking_waitersid ,
@blocking_waitime ,
@blocking_waitbatch ,
@blocking_waiterstmt ,
@blocking_blockersid ,
@blocking_blocker_stmt
end

close cur_sqlblcoking_detail_cur
deallocate cur_sqlblcoking_detail_cur

print'</table><br/>'



/*
Analyse the database size growth using backup history.
*/



 print N'<H3>SQL Server Database Growth in Last Six Month:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Database Name</strong></th>'+
'<th><strong>Year-Month</strong></th>'+
'<th><strong>MinSize in MB</strong></th>'+
'<th><strong>MaxSize in MB</strong></th>'+
'<th><strong>Average Size in MB</strong></th>'+
N'<th><strong>Growth in MB</strong></th></tr>'


set nocount on


SET @endDate = GetDate();  -- Include in the statistic all backups from today
SET @months = 6;           -- back to the last 6 months.
WITH HIST AS
   (SELECT BS.database_name AS DatabaseName
          ,YEAR(BS.backup_start_date) * 100
           + MONTH(BS.backup_start_date) AS YearMonth
          ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
          ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
          ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
    FROM msdb.dbo.backupset as BS
         INNER JOIN
         msdb.dbo.backupfile AS BF
             ON BS.backup_set_id = BF.backup_set_id
    WHERE NOT BS.database_name IN
              ('master', 'msdb', 'model', 'tempdb')
          AND BF.file_type = 'D'
          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    GROUP BY BS.database_name
            ,YEAR(BS.backup_start_date)
            ,MONTH(BS.backup_start_date))
SELECT MAIN.DatabaseName
      ,MAIN.YearMonth
      ,MAIN.MinSizeMB
      ,MAIN.MaxSizeMB
      ,MAIN.AvgSizeMB
      ,MAIN.AvgSizeMB
       - (SELECT TOP 1 SUB.AvgSizeMB
          FROM HIST AS SUB
          WHERE SUB.DatabaseName = MAIN.DatabaseName
                AND SUB.YearMonth < MAIN.YearMonth
          ORDER BY SUB.YearMonth DESC) AS GrowthMB into #DBgrwothdata
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
        ,MAIN.YearMonth

--select * from #DBgrwothdata

declare cur_dbgrowth_info cursor local for
select
DatabaseName,
YearMonth,
MinSizeMB,
MaxSizeMB,
AvgSizeMB,
GrowthMB from #DBgrwothdata

open cur_dbgrowth_info

fetch from cur_dbgrowth_info into
@DBG_Dbname ,
@DBG_YearMon ,
@DBG_MinSizeMB ,
@DBG_MaxSizeMB ,
@DBG_AVGSizeMB ,
@DBG_GrowthMB

while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@DBG_Dbname as varchar(100))+
 '</td><td>'+cast(@DBG_YearMon as varchar(40))+
 '</td><td>'+cast(@DBG_MinSizeMB as varchar(100))+
 '</td><td>'+cast(@DBG_MaxSizeMB as varchar(100))+
 '</td><td>'+cast(@DBG_AVGSizeMB as varchar(10))+
 '</td><td>'+IsNull(cast(@DBG_GrowthMB as varchar(100)),'')+'</td>'+'</tr>'
fetch from cur_dbgrowth_info into
@DBG_Dbname ,
@DBG_YearMon ,
@DBG_MinSizeMB ,
@DBG_MaxSizeMB ,
@DBG_AVGSizeMB ,
@DBG_GrowthMB
end
close cur_dbgrowth_info
deallocate cur_dbgrowth_info
set nocount on
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#DBgrwothdata') AND type in (N'U'))
DROP TABLE #DBgrwothdata
print'</table><br/>'
print'
<br>
<table >
<tr>
<td><span ><strong>SQL Server Database Growth
Matrix:-</strong></span><br >--Above table shows you
your user database growth based on hte backup of the database.<br>--This information is very handy when you planing for
capacity management.</td>
</tr>
</table>

<br/>'



/*
Memory Configuration
*/
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'

/*
--Physical Memory Details on Server along with VAS.

*/


 print N'<H3>SQL Server Instance Memory Configuration:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Physical Mem in MB</strong></th>'+
'<th><strong>Physical Mem in GB</strong></th>'+
N'<th><strong>Virtual Mem MB</strong></th></tr>'

declare cur_phyvasmem_det cursor local for
SELECT physical_memory_in_bytes/1048576.0 as [Physical Memory_MB], physical_memory_in_bytes/1073741824.0 as [Physical Memory_GB], virtual_memory_in_bytes/1048576.0 as [Virtual Memory MB] FROM sys.dm_os_sys_info
open cur_phyvasmem_det
fetch from cur_phyvasmem_det into
@phymem_onsrvinmb ,
@phymem_onsrvingb ,
@phymem_onsrvVAS


while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@phymem_onsrvinmb as varchar(100))+
 '</td><td>'+cast(@phymem_onsrvingb as varchar(40))+
 '</td><td>'+cast(@phymem_onsrvVAS as varchar(100))+'</td>'+'</tr>'
fetch from cur_phyvasmem_det into
@phymem_onsrvinmb ,
@phymem_onsrvingb ,
@phymem_onsrvVAS
end
close cur_phyvasmem_det
deallocate cur_phyvasmem_det
print'</table><br/>'

print'<br>
<table >
<tr>
<td><span ><strong>SQL Server Instance Memory
Configuration:-</strong></span><br>--Above table will show you available
physical memory in MB on the server and virtual memory available on the
server.<br>--It is always good to have overview
of how much physical RAM your server have and virtual memory will be
always depend upon the 32-bit and 64-bit system.<br>-- For 32-bit system
Virtual address space (Virtual Memory) is limited to 2 GB (User Mode
Address space and 2 GB( Kernel Mode Address Space).<br>-- While with
64-bit system this limitation has been removed. you have almost 8TB
virtual address space in 64bit system.</td>
</tr>
</table>
<br/>'
/*
----Buffer Pool Usage at the Moment

*/

print N'<H3>SQL Server Instance Buffer Pool Usage:-</H3>'
print N'<table cellspacing="1" cellpadding="1" border="1">'+
N'<tr><th><strong>Buffer Pool Commited in MB</strong></th>'+
'<th><strong>Buffer Pool Target Commited GB</strong></th>'+
N'<th><strong>Buffer Pool Visible Memory MB</strong></th></tr>'

declare cur_bpoolmeminfo cursor local for
SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB  FROM sys.dm_os_sys_info

open cur_bpoolmeminfo
fetch from cur_bpoolmeminfo into
@bpoolusg_commitedinmb,
@bpoolusg_commitedintargetmb ,
@bpoolusg_visibleinMB


while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@bpoolusg_commitedinmb as varchar(100))+
 '</td><td>'+cast(@bpoolusg_commitedintargetmb as varchar(40))+
 '</td><td>'+cast(@bpoolusg_visibleinMB as varchar(100))+'</td>'+'</tr>'

fetch from cur_bpoolmeminfo into
@bpoolusg_commitedinmb,
@bpoolusg_commitedintargetmb ,
@bpoolusg_visibleinMB
end
close cur_bpoolmeminfo
deallocate cur_bpoolmeminfo
print'</table><br/>'
print'<br>
<table >
<tr>
<td><span ><strong>SQL Server Instace Buffer Pool
Usage:-</strong></span><br><strong>1.Bpool Commited Memory:-</strong>Number
of 8-KB buffers in the buffer pool. This amount represents committed
physical memory in the buffer pool. Does not include reserved memory in
the buffer pool<br><strong>2.Bpool Target Commited:-</strong>Number of
8-KB buffers needed by the buffer pool. The target amount is calculated
using a variety of inputs such as the current state of the system,
including its load, the memory requested by current processes, the
amount of memory installed on the computer, and configuration
parameters. If the bpool_commit_target is larger than the
bpool_committed value, the buffer pool will try to obtain additional
memory. If the bpool_commit_target is smaller than the bpool_committed
value, the buffer pool will shrink.<br><strong>3.Bpool Visible Memory:-</strong>Number
of 8-KB buffers in the buffer pool that are directly accessible in the
process virtual address space. When not using the Address Windowing
Extensions (AWE), when the buffer pool has obtained its memory target
(bpool_committed = bpool_commit_target), the value of bpool_visible
equals the value of bpool_committed.<br><br>When using AWE on a 32-bit
version of SQL Server, bpool_visible represents the size of the AWE
mapping window used to access physical memory allocated by the buffer
pool. The size of this mapping window is bound by the process address
space and, therefore, the visible amount will be smaller than the
committed amount, and can be further reduced by internal components
consuming memory for purposes other than database pages. If the value of
bpool_visible is too low, you might receive out of memory errors.</td>
</tr>
</table>
<br/>'
/*
Total Memory Consumption by SQL Server from perfmon
*/
print N'<H3>SQL Server Total Memory Consumption:-</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>'

declare cur_sqlmeminfoperf 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 = 'Total Server Memory (KB)'
open cur_sqlmeminfoperf
fetch from cur_sqlmeminfoperf into
@totalmemsql_usageinkb,
@totalmemsql_usageinMB,
@totalmemsql_usageinGB

while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@totalmemsql_usageinkb as varchar(100))+
 '</td><td>'+cast(@totalmemsql_usageinMB as varchar(40))+
 '</td><td>'+cast(@totalmemsql_usageinGB as varchar(100))+'</td>'+'</tr>'
fetch from cur_sqlmeminfoperf into
@totalmemsql_usageinkb,
@totalmemsql_usageinMB,
@totalmemsql_usageinGB
end
close cur_sqlmeminfoperf
deallocate cur_sqlmeminfoperf
print'</table><br/>'
print '<br>
<table >
<tr>
<td><strong><span >SQL Server Total Memory
Consumption:-</span><br ></strong>Specifies the
amount of memory the server has committed using the memory manage how
much memory the cache (buffer cache) is using, which is what you control
when you specify max server memory.<br>Note:- This value is only mention
for the Buffer cache component of SQL Server memory no other components
has been mentioned here so far since this you can control it through Max
Server memory setting</td>
</tr>
</table>
<br/>'
/*
Memory Needed for current workload for SQL Server instance
*/
print N'<H3>Memory Needed by SQL Server Instance:-</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>'

declare cur_memneed_sql 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 = 'Target Server Memory (KB)'

open cur_memneed_sql
fetch from cur_memneed_sql into
@memneed_curwl_meminkb,
@memneed_curwl_meminmb,
@memneed_curwl_meminGB
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@memneed_curwl_meminkb as varchar(100))+
 '</td><td>'+cast(@memneed_curwl_meminmb as varchar(40))+
 '</td><td>'+cast(@memneed_curwl_meminGB as varchar(100))+'</td>'+'</tr>'
fetch from cur_memneed_sql into
@memneed_curwl_meminkb,
@memneed_curwl_meminmb,
@memneed_curwl_meminGB
end
close cur_memneed_sql
deallocate cur_memneed_sql
print'</table><br/>'
print '<br>
<table >
<tr>
<td>Memory Needed By SQL Server Instance:-<br>--Above table shows value
for Max Server memory Setting we have put for SQL Server and in use.</td>
</tr>
</table>
<br/>'

/*
Dynamic Memory usage by SQL Server Connections
*/
print N'<H3>Dynamic Memory Usage for SQL Server Connections:-</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_sqlconn_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 = 'Connection Memory (KB)'
open cur_sqlconn_memusg
fetch from cur_sqlconn_memusg into
@memcon_usageinkb ,
@memcon_usageinmb ,
@memcon_usageingb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@memcon_usageinkb as varchar(100))+
 '</td><td>'+cast(@memcon_usageinmb as varchar(100))+
 '</td><td>'+cast(@memcon_usageingb as varchar(100))+'</td>'+'</tr>'
fetch from cur_sqlconn_memusg into
@memcon_usageinkb ,
@memcon_usageinmb ,
@memcon_usageingb
end
close cur_sqlconn_memusg
deallocate cur_sqlconn_memusg
print'</table><br/>'
print '<br>
<table >
<tr>
<td><strong><span >Dynamic Memory Usage for SQL
Server Connection:-</span><br ></strong>--Specifies
the total amount of dynamic memory the server is using for maintaining
connections.</td>
</tr>
</table>'
/*
Total Amount of Memory Usage for SQL Server Locks
*/
print N'<H3>Dynamic Memory Usage for SQL Server Locks:-</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_locksmem_usg 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 = 'Lock Memory (KB)'

open cur_locksmem_usg
fetch from cur_locksmem_usg into
@memlock_useinkb ,
@memlock_useinMb ,
@memlock_useinGb

while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@memlock_useinkb as varchar(100))+
 '</td><td>'+cast(@memlock_useinMb as varchar(40))+
 '</td><td>'+cast(@memlock_useinGb as varchar(100))+'</td>'+'</tr>'
fetch from cur_locksmem_usg into
@memlock_useinkb ,
@memlock_useinMb ,
@memlock_useinGb
 end
 close cur_locksmem_usg
 deallocate cur_locksmem_usg
 print'</table><br/>'
 print '<br>
<table >
<tr>
<td><span ><strong>Dynamic Memory Usage for SQL
Server Locks:-</strong></span><br>--Specifies the total amount of
dynamic memory the server is using for locks.</td>
</tr>
</table>
<br/>'


/*
Total Amount of Memory Usage for Dynamic SQL Server Cache
*/
print N'<H3>Dynamic Memory Usage for SQL Server Cache:-</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_sqlmemcache_info 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 = 'SQL Cache Memory (KB)'

open cur_sqlmemcache_info
fetch from cur_sqlmemcache_info into
@dynsqlcache_useinkb ,
@dynsqlcache_useinMb ,
@dynsqlcache_useinGb

while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@dynsqlcache_useinkb as varchar(100))+
 '</td><td>'+cast(@dynsqlcache_useinMb as varchar(40))+
 '</td><td>'+cast(@dynsqlcache_useinGb as varchar(100))+'</td>'+'</tr>'
fetch from cur_sqlmemcache_info into
@dynsqlcache_useinkb ,
@dynsqlcache_useinMb ,
@dynsqlcache_useinGb
end
close cur_sqlmemcache_info
deallocate cur_sqlmemcache_info
print'</table><br/>'
print '<br>
<table >
<tr>
<td>Dynamic Memory SQL Server Cache:-<br>--Specifies the total amount of
dynamic memory the server is using for the dynamic SQL cache.</td>
</tr>
</table>
<br/>'

/*
Dynamic Memory Utilization by Query Optimization
*/
print N'<H3>Dynamic Memory Usage for SQL Server Query Optimization:-</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_quryopti_info 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 = 'Optimizer Memory (KB) '
open cur_quryopti_info
fetch from cur_quryopti_info into
@qryopt_useinkb,
@qryopt_useinMb ,
@qryopt_useinGb
while @@FETCH_STATUS>=0
 begin
print '<tr><td>'+cast(@qryopt_useinkb as varchar(100))+
 '</td><td>'+cast(@qryopt_useinMb as varchar(40))+
 '</td><td>'+cast(@qryopt_useinGb as varchar(100))+'</td>'+'</tr>'
fetch from cur_quryopti_info into
@qryopt_useinkb,
@qryopt_useinMb ,
@qryopt_useinGb
end
close cur_quryopti_info
deallocate cur_quryopti_info
print'</table><br/>'
print '<br>
<table >
<tr>
<td><strong>Memory Usage for SQL Server Query Optimization:-<br>--Specifies
the total amount of dynamic memory the server is using for query
optimization.</td>
</tr>
</table>
<br/>'