How to: usage-based performance optimization in SQL Server 2008 Express

Question

How can I do usage-based perfromance optimization if I am running SQL Server 2008 Express and using SQL Server Management Studio Express?

Answer 1

Does the Dynamic management views exist in SQL Server Express? Try to use sys.dm_exec_query_stats to see if you get any result back. If thats the case, I have some stored procedures you can execute to optimize the performance. I give you an example of one of the procedures, and I can post some others if you want.

CREATE PROCEDURE [ADMIN].[spExecutionTimeStats]
          @DBName     AS SYSNAME
    AS
     -- +----------------------------------------------------------------------------------------------------------------
      -- ! O b j e c t         : ADMIN.spExecutionTimeStats
      -- ! R e t u r n s       : NONE
      -- ! P a r a m e t e r s : Name                    DataType       Description
      -- +                       ======================= ============== ==================================================
        -- !            @dbname         sysname 
      -- + ---------------------------------------------------------------------------------------------------------------
      -- ! O b j e c t i v e   : 
      -- + ---------------------------------------------------------------------------------------------------------------
      -- ! S A M P L E S       :
      -- !             EXEC ADMIN.spExecutionTimeStats 'DWH'
      -- !             EXEC ADMIN.spExecutionTimeStats 'DWH_TOOLKIT'
      -- !             EXEC ADMIN.spExecutionTimeStats 'DWH_HISTORY'
      -- + ---------------------------------------------------------------------------------------------------------------
      -- ! H i s t o r y       :
      -- + ---------------------------------------------------------------------------------------------------------------
      -- !                       Date       Who   What
      -- +                       ========== ===== ========================================================================
      -- !             2009-09-04 HAWI Initial version
      -- +----------------------------------------------------------------------------------------------------------------

    --DECLARE @DBName AS SYSNAME='DWH'
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL='SELECT TOP 100 percent 
             ''' +@DBName +'''AS DBName,
                OBJECT_SCHEMA_NAME(s.object_id, DB_ID(''' +@DBName +''')) AS Schema_name,
                S.name, 
                D.execution_count,
                D.total_physical_reads,
                D.total_logical_reads,
                d.total_logical_writes,
                d.last_execution_time,
                total_elapsed_time_s=convert(money,d.total_elapsed_time)/1000000,
                max_elapsed_time_s=convert(money,D.max_elapsed_time)/1000000,
                last_elapsed_time_s=convert(money,d.last_elapsed_time)/1000000,
                min_elapsed_time_s=convert(money,D.min_elapsed_time)/1000000,
                avg_time_s=(convert(money,d.total_elapsed_time)/d.execution_count)/1000000,
                d.sql_handle as proc_handle,
                stmnt.*
    FROM ' + @dbname + '.sys.procedures s
                INNER JOIN ' + @dbname + '.sys.dm_exec_procedure_stats d
                             ON s.object_id = d.object_id
                LEFT JOIN 
              (
               SELECT TOP 100 PERCENT
               QS.sql_handle,         
               ROW_NUMBER() OVER(PARTITION BY qs.sql_handle ORDER BY statement_start_offset) AS statement_no,    
               qs.execution_count,
               qs.total_physical_reads,
               qs.total_logical_reads,
               qs.total_logical_writes,
               qs.last_execution_time,
               sql_total_elapsed_time_s=convert(money,qs.total_elapsed_time)/1000000,
               sql_max_elapsed_time_s=convert(money,qs.max_elapsed_time)/1000000,
               sql_last_elapsed_time_s=convert(money,qs.last_elapsed_time)/1000000,
               sql_min_elapsed_time_s=convert(money,qs.min_elapsed_time)/1000000,
               sql_avg_time_s=(convert(money,qs.total_elapsed_time)/qs.execution_count)/1000000,            
               SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
                ((CASE statement_end_offset 
                 WHEN -1 THEN DATALENGTH(st.text)
                 ELSE QS.statement_end_offset END 
                 - QS.statement_start_offset)/2) + 1) AS statement_text
               FROM ' + @dbname + '.sys.dm_exec_query_stats AS QS
               CROSS APPLY ' + @dbname + '.sys.dm_exec_sql_text(QS.sql_handle) as ST 
              ) AS stmnt
             ON d.sql_handle=stmnt.sql_handle
    WHERE 
                convert(money,d.total_elapsed_time)/d.execution_count/1000000>10
                OR
                d.last_elapsed_time > (d.total_elapsed_time/d.execution_count)*1.15
    ORDER BY avg_time_s DESC;'

    PRINT @SQL;
    EXEC sp_executeSQL @SQL;
    GO