SQL: Performance..

3 posts / 0 new
Last post
benzzon
benzzon's picture
SQL: Performance..
*** Show the recently most executed stored procedures..
 

use master

go

 

SELECT DB_NAME(st.dbid) DBName

      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName

      ,OBJECT_NAME(st.objectid,dbid) as [stp]

      ,max(cp.usecounts) cp_usecounts

 FROM master.sys.dm_exec_cached_plans cp

 CROSS APPLY master.sys.dm_exec_sql_text(cp.plan_handle) st

 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

   group by cp.plan_handle, DB_NAME(st.dbid),

            OBJECT_SCHEMA_NAME(objectid,st.dbid),

   OBJECT_NAME(objectid,st.dbid)

 order by max(cp.usecounts) DESC

go

 

 
*** Measure TSQL Statement Performance..
 

-- *** Reset: DBCC FREEPROCCACHE

 

SELECT  creation_time 

        ,last_execution_time

        , replace(convert(varchar,total_elapsed_time *0.000001),'.',',') as tot_elaps_secs

        , total_elapsed_time / execution_count avg_elapsed_time

        ,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

        ,total_physical_reads

        ,total_logical_reads 

        ,total_logical_writes

        , execution_count

        , total_worker_time

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_elapsed_time / execution_count DESC

 

benzzon
benzzon's picture
.
*** SQL 2005,2008, set max memory to take from system:
 

-- *** http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

-- Turn on advanced options

EXEC  sp_configure'Show Advanced Options',1;

GO

RECONFIGURE;

GO

 

-- Set max server memory = 3200MB for the server (for a 4Gb RAM-system..)

EXEC sp_configure'max server memory (MB)',3200;

GO

RECONFIGURE;

GO

 

sp_configure

GO

 

 

benzzon
benzzon's picture
.
*** What SQL Statements are currently executing?
 

CREATE PROC [dbo].[dba_WhatSQLIsExecuting]

AS

/*--------------------------------------------------------------------

Purpose: Shows what individual SQL statements are currently executing.

----------------------------------------------------------------------

Parameters: None.

Revision History:

            24/07/2008  Ian_Stirk@yahoo.com Initial version

Example Usage:

            1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting              

---------------------------------------------------------------------*/

BEGIN

    -- Do not lock anything, and do not get held up by any locks.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

    -- What SQL Statements Are Currently Running?

    SELECT [Spid] = session_Id

            , ecid

            , [Database] = DB_NAME(sp.dbid)

            , [User] = nt_username

            , [Status] = er.status

            , [Wait] = wait_type

            , [Individual Query] = SUBSTRING (qt.text,

             er.statement_start_offset/2,

            (CASE WHEN er.statement_end_offset = -1

                   THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

                         ELSE er.statement_end_offset END -

                                er.statement_start_offset)/2)

            ,[Parent Query] = qt.text

            , Program = program_name

            , Hostname

            , nt_domain

            , start_time

    FROM sys.dm_exec_requests er

    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt

    WHERE session_Id > 50              -- Ignore system spids.

    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.

    ORDER BY 1, 2

END