Dev IT Connections In-Depth Site review session demo

 

In-Depth Configuration Manager site review – presented in Las Vegas on Sept 16, 2014. This was a shared session with Kent Agerlund.

It was a phenomenal time presenting with Kent. Here are the demo files and relevant links for the SQL Server portion.

<Update> includes Kent’s link regarding WSUS and the SUSDB:

http://blog.coretech.dk/kea/house-of-cardsthe-configmgr-software-update-point-and-wsus/ 

Demo2 – SQL Scripts

-- Change to current CM database
USE CM_PS1
GO
/*
-- So, how many collections are set to incremental?

-- AGGRAGATE, counts only (Note: >250 incremental updates may cause issues)
-- GROUP BY
SELECT 
    CASE RefreshType
        WHEN 1 THEN 'No Scheduled Update'
        WHEN 2 THEN 'Full Scheduled Update'
        WHEN 4 THEN 'Incremental Update (only)'
        WHEN 6 THEN 'Incremental and Full Update Scheduled'
        ELSE 'Unknown'
    END AS RefreshType,
    COUNT(*) AS Total
FROM  dbo.v_Collection
GROUP BY RefreshType
*/

-- Top Cached SPs By Execution Count (SQL 2008) (SQL 2008 R2 SP1 only)
-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload

SELECT TOP(50) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],  
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);



-- Top Cached SPs By Avg Elapsed Time (SQL 2008)
-- This helps you find long-running cached stored procedures that
-- may be easy to optimize with standard query tuning techniques
  
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, 
GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);

/*

-- Table row counts
SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC
 
 -- Tables with Byte Length, useful for calculating space used
 SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
    ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
    SUM(sys.length) AS Byte_Length
 FROM sysobjects sob, syscolumns sys
 WHERE sob.xtype='u' AND sys.id=sob.id
 GROUP BY sob.name
 WITH CUBE
 
 -- http://blog.sqlauthority.com/2010/09/08/sql-server-find-row-count-in-table-find-largest-table-in-database-part-2/

 */

-- Calculate table name/space used 
-- Select query to end!
-- http://mlichtenberg.wordpress.com/2012/02/13/determine-the-space-used-by-each-table-in-a-sql-server-database/ 

SET NOCOUNT ON 

DECLARE @table_name VARCHAR(500) 

DECLARE @SpaceUsed TABLE (     
    tablename sysname, 
    row_count INT, 
    reserved VARCHAR(50), 
    data VARCHAR(50), 
    index_size VARCHAR(50), 
    unused VARCHAR(50) 
) 

DECLARE curTables CURSOR FOR  
SELECT s.name + '.' + t.name   
FROM sys.tables t  INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

OPEN curTables 
FETCH NEXT FROM curTables INTO @table_name 
WHILE @@FETCH_STATUS = 0  
BEGIN   
    SET @table_name = REPLACE(REPLACE(@table_name, '[',''), ']', '') 

    -- make sure the object exists before calling sp_spacedused 
    IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name)) 
    BEGIN 
        INSERT INTO @SpaceUsed EXEC sp_spaceused @table_name, false 
    END 
    FETCH NEXT FROM curTables INTO @table_name 
END 
CLOSE curTables 
DEALLOCATE curTables 

-- Compute the total size of each table and add the schemaname to the result set
-- Order by Table size (Total MB) 

SELECT  s.name as Schemaname, 
        su.tablename, 
        su.row_count, 
        su.data, 
        su.index_size, 
        su.unused, 
        CONVERT(decimal, CONVERT(bigint, REPLACE(su.data, ' KB', '')) + 
            CONVERT(bigint, REPLACE(su.index_size, ' KB', '')) + 
            CONVERT(bigint, REPLACE(su.unused, ' KB', ''))) / 1000 AS [total (MB)] 
FROM    @SpaceUsed su INNER JOIN sys.tables t 
            ON t.name = su.tablename 
        INNER JOIN sys.schemas s 
            ON t.schema_id = s.schema_id 
ORDER BY  
        [total (MB)] DESC, 
        schemaname, 
        tablename
        
   

Powershell scripts – place both in C:\TEMP, view comments in the SQL Audit script

PS-SQLAuditScriptv1

https://stevethompsonmvp.wordpress.com/?attachment_id=253

PS-CreateXLS from CSV files

https://stevethompsonmvp.wordpress.com/?attachment_id=254

Information on Optimizing SQL Server databases:

Optimizing CM dbs:

https://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s