-- Code and scripts from Chapter 1: Methodology


-- Though the SQL Server optimizer does rearrange Transact-SQL submitted for better optimization,
-- it cannot compensate for a poor design. One such example is using the cursor to
-- update multiple rows. Because the cursor operates on one row at time, it will be significantly
-- slower than the corresponding set operation. Here is one example to illustrate this point.

CREATE TABLE t1 
    (c1 int PRIMARY KEY, c2 int, c3 char(8000))
GO

-- Load 6000 rows into this table as follows

DECLARE @i int

SELECT @i = 0
WHILE (@i < 6000)
BEGIN
INSERT INTO t1 VALUES (@i, @i + 1000, 'hello')
SET @i = @i + 1
END

-- Method 1: Update all the rows in this table in a single statement
BEGIN TRAN
UPDATE t1 SET c2 = 1000 + c2
COMMIT TRAN

--  Method 2: Update all the rows in this table using cursor
DECLARE mycursor CURSOR FOR
SELECT c2 FROM t1
OPEN mycursor
GO
 
BEGIN TRAN
FETCH mycursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
  UPDATE t1 SET c2 = 1000 + c2 WHERE CURRENT OF mycursor
  FETCH mycursor
END
COMMIT TRAN

-- Now query the total worker time
SELECT TOP 10
    total_worker_time/execution_count AS avg_cpu_cost,
    execution_count,
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), text)) * 2
            ELSE statement_end_offset
       END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC;



-- Another way to detect CPU pressure is by counting the number of 
-- workers in the RUNNABLE state. You can get this information by 
-- executing the following DMV query:

SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state = 'RUNNABLE' AND
    t1.scheduler_address = t2.scheduler_address AND
    t2.scheduler_id < 255
GROUP BY t2.scheduler_id;


-- You can also use the time spent by workers in RUNNABLE state 
-- by executing the following query:

SELECT SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats;



-- Here is a DMV query that can be used to get the top ten queries
-- that are taking the most CPU per execution. It also lists 
-- the SQL statement, its query plan, and the number of times 
-- this plan was executed. If an expensive query is executed 
-- very infrequently, it may not be of that much concern.

SELECT TOP 10
    total_worker_time/execution_count AS avg_cpu_cost, 
    plan_handle, execution_count,
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
       (CASE WHEN statement_end_offset = -1
               THEN LEN(CONVERT(nvarchar(max), text)) * 2
               ELSE statement_end_offset
        END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC;



-- To find the most frequently executed queries in your workload, 
-- you can execute the following DMV query, a slight variant of 
-- the previous DMV query:

SELECT TOP 10 total_worker_time, plan_handle,execution_count,
      (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
                  THEN LEN(CONVERT(nvarchar(max),text)) * 2
                  ELSE statement_end_offset
           END - statement_start_offset)/2)
       FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY execution_count DESC;



-- It is also useful to know how much time SQL Server is spending 
-- in optimizing the query plans. You can use the following 
-- DMV query to get this information:

SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations' OR counter = 'elapsed time';



-- To identify queries/batches that are being recompiled frequently, 
-- you can, of course, use SQL Profiler to get this information. 
-- However, it is not a preferred option for reasons we explained
-- earlier. In SQL Server 2005, you can use DMVs to find the 
-- top-ten query plans that have been recompiled the most.

SELECT TOP 10 plan_generation_num, execution_count,
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max),text)) * 2
            ELSE statement_end_offset
       END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
WHERE plan_generation_num >1
ORDER BY plan_generation_num DESC;



-- For example, you can use the following DMV query to find the 
-- total amount of memory consumed (including AWE) by 
-- the buffer pool:

SELECT  SUM(multi_pages_kb + virtual_memory_committed_kb 
            + shared_memory_committed_kb  
            + awe_allocated_kb) AS [Used by BPool, Kb]
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL';



-- If you have determined (using DBCC MEMORYSTATUS) that 
-- there is an internal memory pressure because internal 
-- components have stolen most of the pages from buffer pool, 
-- you can identify internal components that are stealing 
-- the most pages from buffer pool using the following DMV query:

SELECT TOP 10 type, SUM(single_pages_kb) AS stolen_mem_kb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC;



-- You can identify the internal components that have 
-- allocated memory outside of buffer pool by using 
--  multipage allocator with the following query:

SELECT type, SUM(multi_pages_kb) AS memory_allocated_KB
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type;



-- Similarly, it will be useful if you have files from different 
-- file groups share the same physical disk and you want to know
-- if object(s) mapped to a particular file are the reason of I/O 
-- bottleneck. Using this information, you can choose to remap 
-- your objects to different physical disks to minimize I/O latency.

-- Here is the DMV query that can be used to identify such file(s). 
-- The two columns io_stall_read_ms and io_stall_write_ms 
-- represent the time SQL Server waited for Reads and Writes 
-- issued on the file since the start of SQL Server. 
-- To get meaningful data, you will need to snapshot these numbers
-- for small duration and then compare it to compare these numbers
-- with baseline numbers. If you see that there are significant 
-- differences, it will need to be analyzed.

SELECT database_id, file_id, io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL);



-- The following DMV query can be used to find 
-- I/O latch wait statistics:

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_type;




-- The following DMV query returns the top-ten queries/batches 
-- that are  generating the most I/Os.You can also use a 
-- variation of this query to find the top-ten queries that do 
-- the most I/Os per execution.

SELECT TOP 10
 (total_logical_reads/execution_count) AS avg_logical_reads,
 (total_logical_writes/execution_count) AS avg_logical_writes,
 (total_physical_reads/execution_count) AS avg_phys_reads,
  execution_count,plan_handle, 
  (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
    (CASE WHEN statement_end_offset = -1
          THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
          ELSE statement_end_offset
     END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC;



-- Let us consider an example to show how DMVs can be used 
-- to identify missing indexes. Assume we have a table t_sample 
-- that has five columns. We first load 1,000 rows into this 
-- table followed by creating two indexes; one clustererd index 
-- on column c1 and a nonclustered index on columnn c4. 
-- Here is the script:

CREATE TABLE t_sample (c1 int, c2 int, c3 int, c4 int, c5 char(5000))
GO
-- insert the data and create the indexes
DECLARE @i int;
SELECT @i = 0;
WHILE (@i < 1000)
BEGIN
  INSERT INTO t_sample
       VALUES (@i, @i + 1000, @i+2000, @i+3000, 'hello');
  SET @i = @i + 1;
END;

-- create the indexes
CREATE CLUSTERED INDEX t_sample_ci ON t_sample(c1);
CREATE NONCLUSTERED INDEX t_sample_nci_c4 ON t_sample(c4);

-- Now, we run multiple selects in a loop as follows:
DECLARE @i int;
SELECT @i = 0;
WHILE (@i < 100)
  BEGIN
    SELECT SUM(c1 + c2 + c3) FROM t_sample
      WHERE c1 BETWEEN @i AND @i+50;
    SELECT SUM(c2) FROM t_sample 
      WHERE c2 BETWEEN @i+1000 AND @i + 1100;
    SELECT SUM(c3) FROM t_sample
      WHERE c3 BETWEEN @i +2000 AND @i+2400;
    SET @i = @i + 1;
  END  ;



-- Here is a DMV query that you can execute to identify the 
-- missing indexes and their usefulness:

SELECT t1.object_id, t2.user_seeks, t2.user_scans,
       t1.equality_columns, t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1,
     sys.dm_db_missing_index_group_stats AS t2,
     sys.dm_db_missing_index_groups AS t3
WHERE database_id = DB_ID()
     AND object_id = OBJECT_ID('t_sample')
     AND t1.index_handle = t3.index_handle
     AND t2.group_handle = t3.index_group_handle;



-- This query finds out if one or more threads are waiting to 
-- acquire latch on pages in tempdb. Note, this DMV shows the 
-- current workers that are waiting. 
-- You will need to poll this DMV often to identify allocation 
-- bottleneck.

SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGE%LATCH_%' 
      AND resource_description like '2:%';



-- You can use the following DMV query to identify the currently 
-- executing query this is causing the most allocations and 
-- deallocations in tempdb.

SELECT TOP 10 t1.session_id, t1.request_id, t1.task_alloc,
              t1.task_dealloc, t2.plan_handle,
       (SELECT SUBSTRING (text, t2.statement_start_offset/2 + 1,
            (CASE WHEN statement_end_offset = -1
                  THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
                  ELSE statement_end_offset
             END - t2.statement_start_offset)/2)
        FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM (SELECT session_id, request_id, 
         SUM(internal_objects_alloc_page_count +
                 user_objects_alloc_page_count) AS task_alloc,
          SUM(internal_objects_dealloc_page_count +
                  user_objects_dealloc_page_count) AS task_dealloc
       FROM sys.dm_db_task_space_usage
       GROUP BY session_id, request_id) AS t1,
  sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
     (t1.request_id = t2.request_id) AND t1.session_id > 50
ORDER BY t1.task_alloc DESC;



-- Here is one example that shows how the above DMV query 
-- can be used to identify a query that is generating the 
-- most allocations and deallocations in the tempdb:

CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int, c3 char(8000));
GO
CREATE TABLE t2 (C4 int, c5 char(8000));
GO
-- load large number of rows into each of the tables
DECLARE @i int;
SELECT @i = 0;
WHILE (@i < 6000)
BEGIN
  INSERT INTO t1 VALUES (@i, @i + 1000, 'hello');
  INSERT INTO t2 VALUES (@i,'there');
  SET @i = @i + 1;
END;
-- Run a query with hash-join in a separate session
SELECT c1, c5
FROM t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4
ORDER BY c2;



-- You can use the following script to indentify if 
-- temporary objects are being cached or not in a stored 
-- procedure. It shows number of temporary objects created 
-- when you invoke a particular stored procedure ten times.

DECLARE @table_counter_before_test bigint;
SELECT @table_counter_before_test = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Temp Tables Creation Rate';

DECLARE @i int;
SELECT @i = 0;
WHILE (@i < 10)
BEGIN
  -- <execute your stored procedure>
   SELECT @i = @i+1;
END;

DECLARE @table_counter_after_test bigint;
SELECT @table_counter_after_test = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Temp Tables Creation Rate';

PRINT 'Temp tables created during the test: ' +
       CONVERT(varchar(100), @table_counter_after_test 
                             - @table_counter_before_test);



-- Consider the following example. The stored procedure 
-- test_temptable_caching creates a #table inside it. 
-- We want to check if this temporary table is cached across 
-- multiple invocations using the script described above.

CREATE PROCEDURE test_temptable_caching
AS
CREATE TABLE #t1 (c1 int, c2 int, c3 char(5000))
--  CREATE UNIQUE CLUSTERED INDEX ci_t1 ON #t1(c1);
DECLARE @i int;
SELECT @i = 0;
WHILE (@i < 10)
BEGIN
  INSERT INTO #t1 VALUES (@i, @i + 1000, 'hello');
  SELECT @i = @i+1;
END;
PRINT 'done with the stored proc';
GO



-- The following DMV query shows the top-ten waits encountered 
-- in your application:

SELECT TOP 10 wait_type, waiting_tasks_count AS tasks, 
              wait_time_ms, max_wait_time_ms AS max_wait,
              signal_wait_time_ms AS signal
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;



-- You can find out at any given times all the locks
-- that have been granted or waited upon by currently 
-- executing transactons using the following DMV query. 
-- This query provides a output similarly to the stored 
-- procedure sp_lock.

SELECT request_session_id AS spid, resource_type AS rt,
    resource_database_id AS rdb,
   (CASE resource_type
       WHEN 'OBJECT' 
          THEN object_name(resource_associated_entity_id)
       WHEN 'DATABASE' 
           THEN ' '
       ELSE (SELECT object_name(object_id)
             FROM sys.partitions
             WHERE hobt_id=resource_associated_entity_id)
    END) AS objname,
resource_description AS rd, request_mode AS rm, request_status AS rs
FROM sys.dm_tran_locks;



-- If you want more details, you can combine the sys.dm_tran_locks 
-- DMV with other DMVs to get more detailed information such as 
-- duration of the block and the Transact-SQL statement being 
-- executed by the blocked transaction as follows:

SELECT t1.resource_type,
       'database' = DB_NAME(resource_database_id),
       'blk object' = t1.resource_associated_entity_id,
       t1.request_mode, t1.request_session_id,
       t2.blocking_session_id,
       t2.wait_duration_ms,
      (SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
          (CASE WHEN t3.statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max),text)) * 2
            ELSE t3.statement_end_offset
           END - t3.statement_start_offset)/2)
       FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
t2.resource_description
FROM
   sys.dm_tran_locks AS t1,
   sys.dm_os_waiting_tasks AS t2,
   sys.dm_exec_requests AS t3
WHERE
   t1.lock_owner_address = t2.resource_address AND
   t1.request_request_id = t3.request_id AND
   t2.session_id = t3.session_id;



-- The following DMV query shows the operational statistics on 
-- all the indexes on a table called employee:

SELECT index_id, range_scan_count, 
       row_lock_count, page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID('<db-name>'),
OBJECT_ID('employee'), NULL, NULL);