Friday, December 9, 2016

Script to display drive details in sql server 

CREATE proc [dbo].[USP_Send_DiskSpace]  
( 
    @To  varchar(200) ,    
    @CRITICAL    int     = 10    -- if the freespace(%) is less than @alertvalue, it will send message 
) 
as 
Begin 
DECLARE     @HOSTNAME     VARCHAR(20),  
                @HEAD        VARCHAR(100), 
                @BGCOLOR    VARCHAR(50), 
                @REC        VARCHAR(50), 
                @PRIORITY    VARCHAR(10), 
                @FREE VARCHAR(20), 
                @TOTAL VARCHAR(20), 
                @FREE_PER VARCHAR(20), 
                @CHART VARCHAR(2000), 
                @HTML VARCHAR(MAX), 
                @HTMLTEMP VARCHAR(MAX), 
                @TITLE VARCHAR(100), 
                @DRIVE VARCHAR(100), 
                @SQL VARCHAR(MAX) 
 
CREATE TABLE #MOUNTVOL (COL1 VARCHAR(500)) 
 
INSERT INTO #MOUNTVOL 
EXEC XP_CMDSHELL 'MOUNTVOL' 
 
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%' 
DELETE #MOUNTVOL WHERE COL1 LIKE '%VOLUME%' 
DELETE #MOUNTVOL WHERE COL1 IS NULL 
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%' 
DELETE #MOUNTVOL WHERE COL1 LIKE '%MOUNTVOL%' 
DELETE #MOUNTVOL WHERE COL1 LIKE '%RECYCLE%' 
 
SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL 
 
CREATE TABLE #DRIVES 
    ( 
        DRIVE VARCHAR(500), 
        INFO VARCHAR(80) 
    ) 
 
DECLARE CUR CURSOR FOR SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL 
OPEN CUR 
FETCH NEXT FROM CUR INTO @DRIVE 
WHILE @@FETCH_STATUS=0  
BEGIN 
       SET    @SQL = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + @DRIVE +'''' 
         
        INSERT    #DRIVES 
            ( 
                INFO 
            ) 
        EXEC    (@SQL) 
 
        UPDATE    #DRIVES 
        SET    DRIVE = @DRIVE 
        WHERE    DRIVE IS NULL 
          
FETCH NEXT FROM CUR INTO @DRIVE 
END          
CLOSE CUR          
DEALLOCATE CUR        
 
-- SHOW THE EXPECTED OUTPUT 
SELECT        DRIVE, 
        SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES             : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE, 
        SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES        : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE 
INTO #DISKSPACE FROM        ( 
            SELECT    DRIVE, 
                INFO 
            FROM    #DRIVES 
            WHERE    INFO LIKE 'TOTAL # OF %' 
        ) AS D 
GROUP BY    DRIVE 
ORDER BY    DRIVE 
 
 
 
 
SET @TITLE = 'DISK SPACE REPROT : '+ @@SERVERNAME 
 
SET @HTML = '<HTML><TITLE>'+@TITLE+'</TITLE> 
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2> 
 <TR BGCOLOR=#0070C0 ALIGN=CENTER STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:WHITE''> 
  <TD WIDTH=40><B>DRIVE</B></TD> 
  <TD WIDTH=250><B>TOTAL</B></TD> 
  <TD WIDTH=150><B>FREE SPACE</B></TD> 
  <TD WIDTH=150><B>FREE PRECENTAGE</B></TD> 
</TR>' 
 
DECLARE    RECORDS CURSOR  
FOR SELECT CAST(DRIVE AS VARCHAR(100)) AS 'DRIVE', CAST(FREESPACE/1024/1024 AS VARCHAR(10)) AS 'FREE',CAST(TOTALSIZE/1024/1024 AS VARCHAR(10)) AS 'TOTAL',  
CONVERT(VARCHAR(2000),'<TABLE BORDER=0 ><TR><TD BORDER=0 BGCOLOR='+ CASE WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 < @CRITICAL   
    THEN 'RED' 
WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 > 70   
    THEN '66CC00' 
   ELSE   
    '0033FF' 
   END +'><IMG SRC=''/GIFS/S.GIF'' WIDTH='+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0*2 AS INT) AS CHAR(10) )+' HEIGHT=5></TD> 
     <TD><FONT SIZE=1>'+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 AS INT) AS CHAR(10) )+'%</FONT></TD></TR></TABLE>') AS 'CHART'  
    FROM #DISKSPACE ORDER BY ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 
 
OPEN RECORDS 
 
FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART  
         
WHILE @@FETCH_STATUS = 0 
 
BEGIN 
 
    SET @HTMLTEMP =  
        '<TR BORDER=0 BGCOLOR="#E8E8E8" STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:#0F243E''> 
        <TD ALIGN = CENTER>'+@DRIVE+'</TD> 
        <TD ALIGN=CENTER>'+@TOTAL+'</TD> 
        <TD ALIGN=CENTER>'+@FREE+'</TD> 
        <TD  VALIGN=MIDDLE>'+@CHART+'</TD> 
        </TR>' 
         
        SET @HTML = @HTML +    @HTMLTEMP 
         
    FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART  
 
END 
CLOSE RECORDS 
DEALLOCATE RECORDS 
 
 
SET @HTML = @HTML + '</TABLE><BR> 
<P CLASS=MSONORMAL><SPAN STYLE=''FONT-SIZE:10.0PT;''COLOR:#1F497D''><B>THANKS,</B></SPAN></P> 
<P CLASS=MSONORMAL><SPAN STYLE=''FONT-SIZE:10.0PT;''COLOR:#1F497D''><B>DBA TEAM</B></SPAN></P> 
</HTML>' 
 
--PRINT  
    PRINT @HTML 
 
--save data 
if(object_id('DBA.dbo.diskdrive_stats') is null) 
Begin 
    create table DBA.dbo.diskdrive_stats (   
        Drive varchar(100) ,    
        FreeSpace float null,   
        TotalSize float null, 
        Free_per float, 
        date_time datetime)   
     
insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time) 
    select Drive,convert(float,freespace),convert(float,totalsize), 
   convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE 
 
    --insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time) 
    --select *,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0,getdate() from #DISKSPACE 
End 
    Else 
Begin 
    insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time) 
    select Drive,convert(float,freespace),convert(float,totalsize), 
   convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE 
End     
 
 
--############################Send Mail############################# 
 
set @head = '<RED> Disk Space report from SQL Server : '+@@servername 
 
--SELECT * FROM #DISKSPACE 
 
IF EXISTS(SELECT * FROM #DISKSPACE WHERE CAST((FREESPACE/(TOTALSIZE*1.0))*100.0 AS INT) <= @CRITICAL) 
    BEGIN 
        SET @PRIORITY = 'HIGH' 
         
        print @head 
        exec msdb.dbo.sp_send_dbmail     
        @profile_name = 'SQLProfile',     
        @recipients = @To,    
        @subject = @head, 
        @importance =  @Priority,   
        @body = @HTML,     
        @body_format = 'HTML' 
 
    END     
    ELSE 
    BEGIN     
        print'' 
    END 
 
 
 
DROP TABLE #MOUNTVOL 
DROP TABLE #DRIVES 
DROP TABLE #DISKSPACE 
 
END
Posted by at 1 comment:

Tuesday, December 6, 2016

How is SQL Server using all that memory, anyway

Ref : http://colleenmorrow.com/2011/05/02/how-is-sql-server-using-all-that-memory-anyway/

I want to stick with the subject of memory because I think it’s probably the most misunderstood (and sometimes downright mysterious) component of SQL Server. I know I, for one, could benefit from a better understanding of its inner workings. So today I’d like to share that little bit of code I mentioned last week, that I find very handy for getting an overall picture of how memory is being used by a SQL instance. Personally I run it as a complete script, but I’ll break it down here and provide a little description of each section. I should say, too, that I didn’t write any of this from scratch. For most of it I started with code found here, here, and here, and modified it to suit.
So, without further ado, let’s get started. The first section provides an big-picture look at how SQL is currently allocating memory between the database page cache, procedure cache, and miscellaneous memory usage. It also provides the total memory usage, and I added in the maximum available workspace memory.


--HOW MUCH MEMORY IS MY SQL SERVER USING?
 
declare @plan_cache_size float, @obj_data_size float , @avail_workspace_size float
 
-- plan cache size
 
select @plan_cache_size = (cntr_value*8)/1024.0 FROM sys.dm_os_performance_counters
    WHERE object_name like '%Plan Cache%' and counter_name = 'Cache Pages' and instance_name = '_Total'
 
-- DATABASE PAGE CACHE (PAGES, INCLUDING INDEXES)
 
select @obj_data_size = (cntr_value*8)/1024.0  FROM sys.dm_os_performance_counters
    WHERE object_name like '%Buffer Manager%' and counter_name like 'Database pages%'
 
-- Maximum workspace available for sorts, hashes, etc
 
select @avail_workspace_size = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters
    WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)'
 
-- CACHE TOTALS
select   @obj_data_size [Database_Page_Cache_MB]
 , @plan_cache_size [Procedure_Cache_MB]
 , [Misc_Memory_Usage_MB]
 , [Misc_Memory_Usage_MB] + @obj_data_size + @plan_cache_size [Total_Memory_Usage_MB]
 , @avail_workspace_size [Maximum_Workspace_MB]
from
(
 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]
 from sys.dm_os_performance_counters
 where object_name like '%memory%'
 and (
   counter_name like '%Connection Memory (KB)%'
   or
   counter_name like '%Granted Workspace Memory (KB)%'
   or
   counter_name like '%Lock Memory (KB)%'
   or
   counter_name like '%Optimizer Memory (KB)%'
   or
   counter_name like '%SQL Cache Memory (KB)%'
 )
) x
GO
The output:

The next section uses sys.dm_os_memory_cache_counters to examine how the procedure cache is being broken down.
-- How the Procedure cache is being used
 
SELECT  TOP 6
    LEFT([name], 20) as [name],
    LEFT([type], 20) as [type],
    ([single_pages_kb] + [multi_pages_kb])/1024 AS cache_mb,
    [entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC
The output:

The third part of the script goes deeper into the procedure cache and displays the top 25 cached plans by size. Because it displays some application code, I’m not going to include the results here.
-- Top cached plans by size
 
select top 25
cacheobjType
, ObjType
, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]
, db_name(dbid) [database_name]
, object_name(objid, dbid) [object_name]
, 1
from master..syscacheobjects (nolock)
order by pagesUsed desc
GO
And, finally, the last section shows us how much of the buffer pool is being utilized by each database, including the dirty and clean page counts, sorted by total memory.

-- Buffer Pool Memory Per DB
 
SELECT
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
count(*)AS TotalPageCount,
cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc
GO
And the output:
Posted by at No comments:
Subscribe to: Posts (Atom)