[SQL Server] Collecting and Storing Poor Performing SQLServer


    GETDATE() AS "Collection Date",

    qs.execution_count AS "Execution Count",

    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,

                 (CASE WHEN qs.statement_end_offset = -1

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

                       ELSE qs.statement_end_offset END -



             ) AS "Query Text",

     DB_NAME(qt.dbid) AS "DB Name",

     qs.total_worker_time AS "Total CPU Time",

     qs.total_worker_time/qs.execution_count AS "Avg CPU Time (ms)",    

     qs.total_physical_reads AS "Total Physical Reads",

     qs.total_physical_reads/qs.execution_count AS "Avg Physical Reads",

     qs.total_logical_reads AS "Total Logical Reads",

     qs.total_logical_reads/qs.execution_count AS "Avg Logical Reads",

     qs.total_logical_writes AS "Total Logical Writes",

     qs.total_logical_writes/qs.execution_count AS "Avg Logical Writes",

     qs.total_elapsed_time AS "Total Duration",

     qs.total_elapsed_time/qs.execution_count AS "Avg Duration (ms)",

     qp.query_plan AS "Plan"

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp


     qs.execution_count > 50 OR

     qs.total_worker_time/qs.execution_count > 100 OR

     qs.total_physical_reads/qs.execution_count > 1000 OR

     qs.total_logical_reads/qs.execution_count > 1000 OR

     qs.total_logical_writes/qs.execution_count > 1000 OR

     qs.total_elapsed_time/qs.execution_count > 1000


     qs.execution_count DESC,

     qs.total_elapsed_time/qs.execution_count DESC,

     qs.total_worker_time/qs.execution_count DESC,

     qs.total_physical_reads/qs.execution_count DESC,

     qs.total_logical_reads/qs.execution_count DESC,

     qs.total_logical_writes/qs.execution_count DESC



CREATE TABLE [DBA].[dbo].[My_Poor_Query_Cache](

 [Collection Date] [datetime] NOT NULL,

 [Execution Count] [bigint] NULL,

 [Query Text] [nvarchar](max) NULL,

 [DB Name] [sysname] NULL,

 [Total CPU Time] [bigint],

 [Avg CPU Time (ms)] [bigint] NULL,

 [Total Physical Reads] [bigint] NULL,

 [Avg Physical Reads] [bigint] NULL,

 [Total Logical Reads] [bigint] NULL,

 [Avg Logical Reads] [bigint] NULL,

 [Total Logical Writes] [bigint] NULL,

 [Avg Logical Writes] [bigint] NULL,

 [Total Duration] [bigint] NULL,

 [Avg Duration (ms)] [bigint] NULL,

 [Plan] [xml] NULL





0 #1 Patti 20-11-2017 11:45
I have checked your site and i've found some duplicate content, that's
why you don't rank high in google, but there is a tool that can help you to create 100% unique content, search for: Boorfe's tips unlimited content

Also visit my blog post ClaudiaSmall: https://Glinda21.jimdo.com

Ajouter un Commentaire

Code de sécurité