One of the new management related features in SQL Server 2008 R2 is SQL Server Utility. SQL Server Utility gives you a way to manage and monitor multiple instances of SQL Server using a variation of Management Data Warehouse. You can use this to monitor things such as CPU utilization and disk space utilization. SQL Server Utility is an Enterprise Edition and above only feature, that requires SQL Server 2008 R2 for both the Utility Control Point (UCP) and all managed instances (at least in the November 2009 CTP of SQL Server 2008 R2). SQL Server 2008 R2 Enterprise Edition has a limit of 25 managed instances in each UCP.

If you want to use SQL Server Utility, the first thing you need to do is create a Utility Control Point. The UCP is where all of the monitoring data from your managed instances will be stored. You need to make sure that you have TCP/IP enabled on the instance where the UCP will live. You also need to make sure that SQL Agent is running and is set to start automatically.

Inside of SQL Server Management Studio (SSMS), you select Utility Explorer from the View menu. You will see the Utility Configuration Steps screen for a series of wizards that allow you to do most SQL Server Utility related tasks.

Choose “Create a Utility Control Point (UCP)”, and you will see the first screen in that wizard.

Click on Next, and you will see “Specify the Instance of SQL Server”. You need to specify the instance where the UCP will be located, and supply login credentials. You also need to give the UCP a relevant name.

Click Next, and you will see “Utility Collection Set Account”. You need to specify a Windows domain account that will be used as the SQL Server Agent proxy account for the utility collection set.

Click Next, and you will see  “SQL Server Instance Validation”, and the wizard will run a series of twelve validation tests to make sure the the instance will work as a UCP. The WMI validation test may take some time, so don’t be alarmed.

If every test passes (or you just have warnings and no errors), click Next, and you will see “Summary of UCP Creation”.

Click Next, and you will see “Utility Control Point Creation”, which will create the utility management data warehouse database, configure the UCP, and enroll the UCP instance as a managed instance.

Click Finish, and you will see a summary of the UCP instance’s health (with no data). Wait a few minutes, and you should start to see some more interesting data appear in the various reports.

If you look under databases in Object Explorer, you will see a new database called sysutility_mdw. If you go to SQL Agent, and look under Jobs, you will see seven new Agent jobs that are used to collect data from the managed instances into the sysutility_mdw.

Microsoft has recently announced that SharePoint 2010 and Office 2010 will RTM in April 2010, and will be officially launched on May 12, 2010. From historical experience, this means that the RTM bits will be available on MSDN Subscribers probably one to two weeks after it is declared RTM, and that SharePoint 2010 and Office 2010 will be available for purchase on May 12.  That is not very far away…

SharePoint 2010 requires SQL Server. Here is some information regarding the relationship between SharePoint 2010 and SQL Server.

SharePoint Server 2010 Hardware and Software Requirements

SharePoint 2010 is 64-bit only. It requires Windows Server 2008 SP2 or Windows Server 2008 R2. It will not run on a Server Core installation.

It requires one of these versions of x64 SQL Server:

                SQL Server 2005 SP3 CU3 (Build 4220) or greater (Note: SQL Server 2005 goes out of mainstream support in April 2011)

                SQL Server 2008 SP1 CU2 (Build 2714) or greater

                SQL Server 2008 R2

Microsoft’s official position is still that SQL Server 2008 R2 will be available in the first half of 2010, even though the SharePoint team has announced a May 12 launch date.  If I were planning on a brand new SharePoint 2010 deployment, I would want to be running on Windows Server 2008 R2, using SQL Server 2008 R2. This will give you the best performance and the most functionality. For example, PowerPivot in SharePoint 2010 requires SQL Server 2008 R2.

I have put together some sample code that shows how to determine what edition and version of SQL Server is installed on an instance. This makes it easier to take advantage of version specific features (such as data compression) and edition specific features (such as online index creation) where appropriate.

-- Method 1: Retrieve complete version info, parse results in C#
SELECT @@VERSION AS [SQLVersionInfo];


-- Method 2: Detecting the edition and version of SQL Server from T-SQL
IF SERVERPROPERTY('EngineEdition') = 3
    BEGIN
        PRINT 'Enterprise Edition'
        IF CONVERT(INT, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion')))) > 9
            BEGIN
                PRINT 'SQL Server 2008 or greater';
                -- Do something like use data compression on an index
                
            END
        ELSE
            BEGIN
                PRINT 'SQL Server 2005 or lower';
                -- Do something like create an index in online mode
                
            END
    END
ELSE
    BEGIN
        PRINT 'Standard Edition';
        -- Create an index in offline mode
    END

If you want to record the results of some useful, instance level DMV queries somewhere where you can easily query them later, you can use this technique.  This gives you a pretty decent overview of the health of your instance by capturing AvgTaskCount, AvgRunnableTaskCount, AvgPendingIOCount, SQlServerCPUUtilization, and PageLifeExpectancy on a periodic basis.

I like to create a utility database called ServerMonitor on each one of my SQL instances. Then, I can use that database to hold instance level DMV queries wrapped in stored procedures. This way, I can ensure that the database is always available regardless of the mirroring status of other user databases on the instance.

The script below creates a table called SQLServerInstanceMetricHistory in that database. Then, after checking for SQL Server 2008 or greater and for Enterprise Edition, it enables Page compression on the clustered index for that table. Next, it creates a stored procedure called DBAdminRecordSQLServerMetrics that inserts rows into the SQLServerInstanceMetricHistory table. The next step would be to create a SQL Server Agent job that runs once a minute. The SQL Agent job would simply call this command:

EXEC dbo.DBAdminRecordSQLServerMetrics;

You could then query the SQLServerInstanceMetricHistory table to get a better idea of the average workload over time for that instance of SQL Server.

-- Logging SQL Server instance metrics
-- Glenn Berry 
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Drop table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[SQLServerInstanceMetricHistory]') 
            AND type in (N'U'))
DROP TABLE [dbo].[SQLServerInstanceMetricHistory]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Create table to hold metrics
CREATE TABLE [dbo].[SQLServerInstanceMetricHistory]
(
    [SQLServerInstanceMetricHistoryID] [bigint] IDENTITY(1,1) NOT NULL,
    [MeasurementTime] [datetime] NOT NULL,
    [AvgTaskCount] [int] NOT NULL,
    [AvgRunnableTaskCount] [int] NOT NULL,
    [AvgPendingIOCount] [int] NOT NULL,
    [SQLServerCPUUtilization] [int] NOT NULL,
    [PageLifeExpectancy] [int] NOT NULL,
 CONSTRAINT [PK_SQLServerInstanceMetricHistory] PRIMARY KEY CLUSTERED 
(
    [SQLServerInstanceMetricHistoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO


-- Check for SQL Server 2008 or 2008 R2 and Enterprise Edition
IF LEFT(CONVERT(CHAR(2),SERVERPROPERTY('ProductVersion')), 2) = '10' 
   AND SERVERPROPERTY('EngineEdition') = 3
    BEGIN
        -- Use Page Compression on the clustered index 
        -- if we have SQL Server 2008 Enterprise Edition
        ALTER TABLE [dbo].[SQLServerInstanceMetricHistory] REBUILD PARTITION = ALL
        WITH (DATA_COMPRESSION = PAGE);
    END
GO


-- Drop stored procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[DBAdminRecordSQLServerMetrics]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DBAdminRecordSQLServerMetrics]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* DBAdminRecordSQLServerMetrics ===================================================
Description : Used to keep track of instance level SQL Server Metrics
                        
Author: Glenn Berry    
Date: 3/9/2010    
Input:                            
Output:    
Used By: Only used to maintain the database                

Last Modified          Developer        Description
-------------------------------------------------------------------------------------
3/9/2010            Glenn Berry        Added Modification Comment
===================================================================================*/
CREATE PROCEDURE [dbo].[DBAdminRecordSQLServerMetrics]
AS

    SET NOCOUNT ON;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_NULLS ON;

    
    DECLARE @PageLifeExpectancy int = 0;
    DECLARE @SQLProcessUtilization int = 0;
    

    -- Get PLE info
    SET @PageLifeExpectancy = (SELECT cntr_value AS [PageLifeExpectancy]
    FROM sys.dm_os_performance_counters
    WHERE object_name = 'SQLServer:Buffer Manager'
    AND counter_name = 'Page life expectancy'); 
    
    
    -- Get CPU Utilization History (SQL 2008 Only)
    SET @SQLProcessUtilization = (SELECT TOP(1) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]              
    FROM ( 
          SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                AS [SystemIdle], 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
                'int') 
                AS [SQLProcessUtilization], [timestamp] 
          FROM ( 
                SELECT [timestamp], CONVERT(xml, record) AS [record] 
                FROM sys.dm_os_ring_buffers 
                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                AND record LIKE '%<SystemHealth>%') AS x 
          ) AS y 
    ORDER BY record_id DESC);
    
    
    -- Add metrics info to SQLServerInstanceMetricHistory
    INSERT INTO dbo.SQLServerInstanceMetricHistory
    (MeasurementTime, AvgTaskCount, AvgRunnableTaskCount, AvgPendingIOCount, 
     SQLServerCPUUtilization, PageLifeExpectancy)
    (SELECT GETDATE() AS [MeasurementTime], AVG(current_tasks_count)AS [AvgTaskCount], 
            AVG(runnable_tasks_count) AS [AvgRunnableTaskCount], 
            AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount], 
            @SQLProcessUtilization, @PageLifeExpectancy
     FROM sys.dm_os_schedulers
     WHERE scheduler_id < 255);
     
    RETURN;
    
GO

It is pretty common for large, busy SQL Server instances to run into I/O bottlenecks. Even smaller, less busy systems often run into problems when they are not sized and configured correctly. As a database professional, you need to be able to detect when your SQL Server instances are experiencing poor performance due to I/O bottlenecks.

Quite often, you may need to convince someone from another part of the organization (such as a SAN engineer or server administrator) that your SQL Server instances are seeing decreased performance because of I/O issues.  It is also very possible that you have plenty of capacity, and that your I/O subsystem is configured correctly, but that you have some poorly written queries that are causing excessive disk I/O. Before you start talking to the rest of the organization, it is a good idea to know what the real issue is.

I have put together a small collection of queries (most of which are DMV queries) that should be very helpful in that effort. Even if you are not able to run PerfMon (because you don’t enough rights on the database server itself), you can still run these queries to get a pretty good idea what is going on from an I/O perspective.

This post is part of T-SQL Tuesday #004, which is hosted by Mike Walsh. You can see his invitation here.

-- Some I/O Specific DMV Queries
-- Glenn Berry
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Always look at Avg Disk Sec/Read and Avg Disk Sec/Write 
-- in PerfMon for each Physical Disk 

-- Isolate top waits for server instance since last restart or statistics clear
-- Look for I/O specific waits at the top of the list
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP'
,'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'
, 'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

-- *** I/O Related Waits ***
-- ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish
-- IO_COMPLETION        Occurs while waiting for I/O operations to complete. 
--                      This wait type generally represents non-data page I/Os. 
--                      Data page I/O completion waits appear 
--                      as PAGEIOLATCH_* waits
-- PAGEIOLATCH_SH       Occurs when a task is waiting on a latch for a buffer that 
--                      is in an I/O request. The latch request is in Shared mode. 
--                      Long waits may indicate problems with the disk subsystem.
-- PAGEIOLATCH_EX       Occurs when a task is waiting on a latch for a buffer that 
--                      is in an I/O request. The latch request is in Exclusive mode. 
--                      Long waits may indicate problems with the disk subsystem.
-- WRITELOG             Occurs while waiting for a log flush to complete. 
--                      Common operations that cause log flushes 
--                      are checkpoints and transaction commits.
-- PAGELATCH_EX         Occurs when a task is waiting on a latch for a buffer that 
--                      is not in an I/O request. The latch request is in Exclusive mode.
-- BACKUPIO             Occurs when a backup task is waiting for data, or is waiting for a 
--                      buffer in which to store data


-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count 
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'
ORDER BY cpu_id;

-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers 
WHERE [status] = 'VISIBLE ONLINE';

-- High Latch waits (SH and EX) indicates the I/O subsystem is too busy 
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms,
       wait_time_ms - signal_wait_time_ms AS [io_wait_time_ms]
FROM sys.dm_os_wait_stats
WHERE wait_type IN('PAGEIOLATCH_EX', 'PAGEIOLATCH_SH', 'PAGEIOLATCH_UP')
ORDER BY wait_type;


-- File Names and Paths for TempDB and all user databases in instance 
SELECT DB_NAME([database_id])AS [Database Name], [file_id], 
       name, physical_name, type_desc
FROM sys.master_files
WHERE [database_id] > 4 AND [database_id] <> 32767
OR [database_id] = 2;

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?


-- Which queries are causing the most IO operations (can take a few seconds)
SELECT TOP (20) total_logical_reads/execution_count AS [avg_logical_reads],
    total_logical_writes/execution_count AS [avg_logical_writes],
    total_worker_time/execution_count AS [avg_cpu_cost], execution_count,
    total_worker_time, total_logical_reads, total_logical_writes, 
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '') 
     FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
    last_logical_reads, min_logical_reads, max_logical_reads,
    last_logical_writes, min_logical_writes, max_logical_writes,
    total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
    (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;


-- Calculates average stalls per read, per write, 
-- and per total input/output for each database file. 
SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) 
AS [avg_read_stall_ms],io_stall_write_ms, 
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) 
AS [avg_write_stall_ms],io_stall_read_ms + io_stall_write_ms AS [io_stalls], 
num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + 
io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) 
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;

-- Helps determine which database files on the entire instance have the most I/O bottlenecks


-- Analyze Database I/O, ranked by IO Stall%
WITH DBIO AS
(SELECT DB_NAME(IVFS.database_id) AS db,
 CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
 SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
 SUM(IVFS.io_stall) AS io_stall
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
 INNER JOIN sys.master_files AS MF
 ON IVFS.database_id = MF.database_id
 AND IVFS.file_id = MF.file_id
 GROUP BY DB_NAME(IVFS.database_id), MF.[type])
SELECT db, file_type, 
  CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
  CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
  CAST(100. * io_stall / SUM(io_stall) OVER()
       AS DECIMAL(10, 2)) AS io_stall_pct,
  ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
FROM DBIO
ORDER BY io_stall DESC;


-- The queries below are database specific
USE yourdatabasename;
GO

-- Top Cached SPs By Total Physical Reads (SQL 2008). 
-- Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time 
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC;
       
-- Top Cached SPs By Total Logical Writes (SQL 2008). 
-- Logical writes relate to both memory and disk I/O pressure 
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], 
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC;


-- Lists the top statements by average input/output usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2, 
        (CASE 
            WHEN qs.statement_end_offset = -1 
         THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
            ELSE qs.statement_end_offset 
         END - qs.statement_start_offset)/2) AS [Query Text]    
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC;

-- Helps you find the most expensive statements for I/O by SP

I just wanted to remind people to participate in T-SQL Tuesday #004 on the subject of IO. You just need to post a blog post on something to do with IO between 00:00:00 UTC on Tuesday the 9th of March and 00:00:00 UTC on Wednesday the 10th. You also have to link back to Mike’s invitation post.

I’ll be trying my hand at a post for this one, which should be fun.

I have been playing around some more with SQL Azure in order to get ready for an upcoming presentation, so I thought I would share some of the T-SQL commands in the script below. I have a partial copy of the sample AdventureWorksLT2008R2 database installed on my SQL Azure logical “server” in the Southern US data center before this demo starts. You need at least the November CTP of SQL Server 2008 R2, in order to talk to SQL Azure with SSMS.

I have mainly just trying different commands to see what works and what does not work in SQL Azure, so I hope you find this interesting and useful.

-- Trying out SQL Azure
-- Glenn Berry
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry


-- Get version information
SELECT @@VERSION AS [SQL Version Info];


-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE AdventureWorksLT2008R2;
GO


-- ***  Connect to AdventureWorksLT2008R2 database ***


-- Create a table and populate it

--Drop table if it exists
IF OBJECT_ID('dbo.UserAccount', 'U') IS NOT NULL
  DROP TABLE dbo.UserAccount;
GO

-- Create a table
-- Notice no file group or other options allowed in CREATE TABLE for SQL Azure
CREATE TABLE [dbo].[UserAccount](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [ExpireDate] [datetime] NULL,
    [FName] [nvarchar](50) NOT NULL,
    [LName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_UserAccount] PRIMARY KEY CLUSTERED 
([UserID] ASC));


-- Drop index if it exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserAccount]') 
            AND name = N'IX_UserAccount_UserName')
DROP INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount];
GO

-- Create NC Index
-- Notice no options allowed in CREATE INDEX for SQL Azure
CREATE NONCLUSTERED INDEX [IX_UserAccount_UserName] ON [dbo].[UserAccount] 
(
    [UserName] ASC
);

-- Insert a few rows
INSERT INTO dbo.UserAccount(UserName, [Password], CreateDate, [ExpireDate], FName, LName)
VALUES('GlennBerry', 'testpassword', GETDATE(), '12/31/2010', 'Glenn', 'Berry'),
      ('JessicaAlba', 'testpassword', GETDATE(), '12/31/2010', 'Jessica', 'Alba'),
      ('MirandaKerr', 'testpassword', GETDATE(), '12/31/2010', 'Miranda', 'Kerr');


-- See what we have in the table (notice GETDATE() actually returned UTC Date)
SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
FROM dbo.UserAccount;

-- Get the UTC Date
SELECT GETUTCDATE() AS [UTC Date];


-- This works in SQL Azure
EXEC sp_HelpIndex [dbo.UserAccount];
EXEC sp_HelpIndex [SalesLT.Customer];
EXEC sp_HelpIndex [SalesLT.CustomerAddress];

-- This works in SQL Azure
EXEC sp_Help [SalesLT.Customer]


-- Stop the rowcount messsages
SET NOCOUNT ON;

-- Return IO statistics
SET STATISTICS IO ON;

-- Turn on graphical execution plan

-- Run a simple SELECT query
SELECT UserID, UserName, [Password], CreateDate, 
[ExpireDate], FName, LName
FROM dbo.UserAccount;

-- Run a simple SELECT query
SELECT CustomerID, NameStyle, Title, FirstName, MiddleName, 
LastName, Suffix, CompanyName, SalesPerson, EmailAddress, 
Phone, PasswordHash
FROM SALESLT.Customer;


-- Run a simple SELECT query with a WHERE clause
-- Notice SQL Server 2008 syntax works for DECLARE
DECLARE @CustomerID int = 1; 

SELECT CustomerID, NameStyle, Title, FirstName, MiddleName, 
LastName, Suffix, CompanyName, SalesPerson, EmailAddress, 
Phone, PasswordHash
FROM SALESLT.Customer
WHERE CustomerID = @CustomerID;


-- Create a stored procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserInfoByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserInfoByID]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserInfoByID]
(
    @UserID int
)
AS
    BEGIN
        
        SET NOCOUNT ON;

        SELECT UserID, UserName, [Password], CreateDate, [ExpireDate], FName, LName
        FROM dbo.UserAccount
        WHERE UserID = @UserID;
        
        RETURN;
        
    END
GO

-- Call the stored procedure
EXEC dbo.GetUserInfoByID 1;
EXEC dbo.GetUserInfoByID 2;





-- Must connect to master database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE [Master];
GO

-- *** Connect to master database ***


-- Get firewall rules
SELECT id, name, start_ip_address, end_ip_address, 
create_date, modify_date 
FROM sys.firewall_rules;


-- Switch to Business Edition ($99.99/month)
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 10 GB);

-- Refresh SQL Azure Portal web page to see change

-- Switch to Web Edition ($9.99/month)
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 1 GB);


-- Create an "Admin" login and user in the SQL Azure instance

-- List all logins on "instance" (must be connected to master)
SELECT *
FROM sys.sql_logins;

-- Cleanup if necessary
DROP LOGIN TestLogin;
GO
DROP USER TestLoginUser;
GO

-- Create a logon with a "strong" password that
-- can create databases and other logins
CREATE LOGIN TestLogin WITH password='1994Acura#';
GO
-- Create a database user that is linked to login
CREATE USER TestLoginUser FROM LOGIN TestLogin;
GO
EXEC sp_addrolemember 'loginmanager', 'TestLoginUser';
EXEC sp_addrolemember 'dbmanager', 'TestLoginUser';  -- like dbcreator


-- List all logins on "instance" 
SELECT *
FROM sys.sql_logins;

-- List all databases (must be connected to master)
SELECT name, database_id, create_date, [compatibility_level]
FROM sys.databases;

-- Drop database if necessary
DROP DATABASE TestDatabase;

-- Create new database on SQL Azure "instance"
CREATE DATABASE TestDatabase;


-- List all databases (must be connected to master)
SELECT name, database_id, create_date, [compatibility_level]
FROM sys.databases;

-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- This does not work in SQL Azure
USE TestDatabase;
GO

-- ***  Connect to TestDatabase ***

-- Create a table
CREATE TABLE [dbo].[TestTable](
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [TestName] [nvarchar](50) NOT NULL,    
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
([TestID] ASC));

-- Insert a few rows into table
INSERT INTO dbo.TestTable(TestName)
VALUES('Test1'),
      ('Test2'),
      ('Test3');
      
-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;


-- This does not work in SQL Azure
DBCC FREEPROCCACHE;

-- This does not work in SQL Azure
DBCC FREEPROCINDB(5);

-- This does not work in SQL Azure
DBCC DROPCLEANBUFFERS;


-- ***  Connect to AdventureWorksLT2008R2 database ***

-- Run a stored procedure
EXEC dbo.GetCustomerInfoByFirstName N'James';
EXEC dbo.GetUserInfoByID 2;


-- Run some DMV queries against the database

-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;


-- Monitor connections
SELECT s.session_id, s.login_name, e.connection_id,
      s.last_request_end_time, s.cpu_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS e
ON s.session_id = e.session_id;

-- Find top Avg CPU time queries
SELECT TOP (5) MIN(query_stats.statement_text) AS [Statement Text], 
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time],
query_stats.query_hash AS [Query Hash]

FROM (SELECT QS.*, 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 sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

-- Top Cached Plans By total worker time (CPU)
SELECT q.[text], hcpu.total_worker_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_worker_time DESC;


-- Top Cached Plans By total logical reads (Memory)
SELECT q.[text], hcpu.total_logical_reads, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_logical_reads DESC;


-- Top Cached Plans By total elapsed time
SELECT q.[text], hcpu.total_elapsed_time, 
       hcpu.execution_count, hcpu.plan_handle
FROM 
    (SELECT TOP (50) qs.* 
     FROM sys.dm_exec_query_stats AS qs 
     ORDER BY qs.total_worker_time DESC) AS hcpu 
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q 
ORDER BY hcpu.total_elapsed_time DESC;



-- DMVs that are available in SQL Azure Service Update 1
SELECT * FROM sys.dm_exec_connections; 

SELECT * FROM sys.dm_exec_requests; 

SELECT * FROM sys.dm_exec_sessions; 

SELECT * FROM sys.dm_tran_database_transactions;  

SELECT * FROM sys.dm_tran_active_transactions; 


-- Drop the Stored procedure
DROP PROCEDURE dbo.GetUserInfoByID;

-- Drop the table
DROP TABLE dbo.UserAccount;

-- Drop the database
DROP DATABASE TestDatabase;

-- Drop the login
DROP LOGIN TestLogin;

Microsoft has a new KB article outlining issues with using SCOPE_IDENTITY and @@IDENTITY to retrieve values inserted into an identity column in a table. This particular issue shows up when you have parallel execution plans, but I have also seen DML triggers cause problems when you use @@IDENTITY.  The article lists several workarounds, including setting MAXDOP=1 at the query or instance level. In my opinion, the best option is to use the output clause of the INSERT table to retrieve the identity value.

Here is an example of how to use the OUTPUT clause:

-- Example of using OUTPUT clause in an INSERT statement
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
USE AdventureWorks;
GO

-- Declare table variable to hold results
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);

-- Insert into table with IDENTITY column
-- Use OUTPUT clause to retrieve new value                           
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM @MyTableVar;

I attended and presented at the Rocky Mountain Tech Trifecta yesterday, where I had a very nice time. Julie Yack did a great job of organizing the free event, which had nearly 700 registrations. There were a lot of sponsors, so there was plenty of food (including breakfast, lunch and snacks) and lots of loot to give away during the sessions. After lunch, there were two stacks of empty pizza boxes that were each over six feet high. That was a lot of pizza!  It is very nice to see so many people working together to put on high quality, free events like this.

It was great to see Steve Jones, Eric Johnson, Paul Nielsen, and Louis Davidson (who had flown in from Tennessee for the event). Kevin Cox from SQLCAT was also there, where he stepped in at the last minute to do a presentation. Louis Davidson used an add-in for PowerPoint that let it send tweets to the Twitter API while he was giving his SQL Server keynote presentation.

My session was not until 4PM, but despite that, it was still well attended. I was concerned that a lot of people might have decided to leave earlier in the afternoon since it was a nice day in Denver. The North Classroom building on the Auraria campus in Denver has a very strong, free Wireless-N connection, which was very welcome. I actually used it to show SQL Azure for a couple of minutes before I started my presentation. After the event was over, I went to Cilantro Fusion for a nice meal and conversation with Carlos Bossy, Jason Horner , Marc Beacom, and Paul Paiva. Cilantro Fusion is owned by Carlos’ wife, Gabriela Bossy, and the food is excellent.

Since I like to measure and monitor things (maybe that is why I am a DBA), and I care about the environment, I installed a TED 5000 and connected it to Google PowerMeter about six weeks ago. Every week, Google sends out an email that summarizes your power usage by day for the previous week, and by week for the previous six weeks. My latest report is shown below:

My goal for February was to come in under 400kWh for the month, but it looks like I am going to end up at around 430kWh for the month, which is not too bad for late winter. As we move into spring and average outside temperatures increase, I have a better chance of breaking the 400kWh barrier.

The sawtooth pattern in blue in the picture below shows the effect of the gas furnace blower fan coming on periodically. I think it also is an indication that I need to improve the insulation in my attic. It also may be a sign that the furnace is too large for the house. It is an Amana 95% efficient gas furnace that has worked well, but I suspect that the HVAC subcontractor that sized and installed it didn’t know what they were doing.

I am very curious what other people’s typical monthly electrical usage is in kWh, so feel free to post a comment!

Here is the script that has the diagnostic queries that I will be using during my presentation at the Rocky Mountain Tech Trifecta on February 27.

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry 
-- February 26, 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Instance level queries *******************************

-- SQL and OS Version information for current instance
SELECT @@VERSION AS [SQL Version Info];

--   2008 RTM Builds                    2008 SP1 Builds
-- Build       Description        Build        Description
-- 1600        Gold RTM
-- 1763        RTM CU1
-- 1779        RTM CU2
-- 1787        RTM CU3    -->      2531        SP1 RTM
-- 1798        RTM CU4    -->      2710        SP1 CU1
-- 1806        RTM CU5    -->      2714        SP1 CU2 
-- 1812        RTM CU6    -->      2723        SP1 CU3
-- 1818        RTM CU7    -->      2734        SP1 CU4
-- 1823        RTM CU8    -->      2746        SP1 CU5
-- 1828        RTM CU9    -->      2757        SP1 CU6

-- SQL Azure Builds (most DMV queries don't work on SQL Azure)
-- Build            Description
-- 10.25.9200        RTM Service Update 1

-- 2008 R2 Builds
-- Build            Description
-- 10.50.1092        August CTP2
-- 10.50.1352        November CTP3



-- Hardware information from SQL Server 2008 
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;


-- Get sp_configure values for instance
EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

-- Focus on
-- backup compression default
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism (consider setting to 1 for OLTP workloads)
-- max server memory (MB)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)


-- File Names and Paths for TempDB and all user databases in instance 
SELECT DB_NAME([database_id])AS [Database Name], [file_id], 
       name, physical_name, type_desc
FROM sys.master_files
WHERE [database_id] > 4 AND [database_id] <> 32767
OR [database_id] = 2;

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?


-- Calculates average stalls per read, per write, and per total input/output for each database file. 
SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, 
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) 
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;

-- Helps determine which database files on the entire instance have the most I/O bottlenecks


-- Recovery model, log reuse wait description, log file size, log usage size 
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.log_reuse_wait_desc, 
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu 
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE  'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE 'Log File(s) Size (KB)%';

-- Things to look at
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs ?
-- What compatibility level are they on?


-- Clear Wait Stats 
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

-- Common Significant Wait types with BOL explanations

-- *** Network Related Waits ***
-- ASYNC_NETWORK_IO        Occurs on network writes when the task is blocked behind the network

-- *** Locking Waits ***
-- LCK_M_IX                Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
-- LCK_M_IU                Occurs when a task is waiting to acquire an Intent Update (IU) lock
-- LCK_M_S                Occurs when a task is waiting to acquire a Shared lock

-- *** I/O Related Waits ***
-- ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish
-- IO_COMPLETION        Occurs while waiting for I/O operations to complete. 
--                      This wait type generally represents non-data page I/Os. Data page I/O completion waits appear 
--                      as PAGEIOLATCH_* waits
-- PAGEIOLATCH_SH        Occurs when a task is waiting on a latch for a buffer that is in an I/O request. 
--                      The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
-- PAGEIOLATCH_EX        Occurs when a task is waiting on a latch for a buffer that is in an I/O request. 
--                      The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
-- WRITELOG             Occurs while waiting for a log flush to complete. 
--                      Common operations that cause log flushes are checkpoints and transaction commits.
-- PAGELATCH_EX            Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. 
--                      The latch request is in Exclusive mode.
-- BACKUPIO                Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

-- *** CPU Related Waits ***
-- SOS_SCHEDULER_YIELD  Occurs when a task voluntarily yields the scheduler for other tasks to execute. 
--                      During this wait the task is waiting for its quantum to be renewed.

-- THREADPOOL            Occurs when a task is waiting for a worker to run on. 
--                      This can indicate that the maximum worker setting is too low, or that batch executions are taking 
--                      unusually long, thus reducing the number of workers available to satisfy other batches.
-- CX_PACKET            Occurs when trying to synchronize the query processor exchange iterator 
--                        You may consider lowering the degree of parallelism if contention on this wait type becomes a problem


-- Signal Waits for instance
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) 
AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) 
AS [%resource waits]
FROM sys.dm_os_wait_stats;

-- Signal Waits above 10-15% is usually a sign of CPU pressure


-- Get CPU Utilization History for last 30 minutes (in one minute intervals)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization], 
               SystemIdle AS [System Idle Process], 
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
FROM ( 
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
            AS [SystemIdle], 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 
            'int') 
            AS [SQLProcessUtilization], [timestamp] 
      FROM ( 
            SELECT [timestamp], CONVERT(xml, record) AS [record] 
            FROM sys.dm_os_ring_buffers 
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
            AND record LIKE '%<SystemHealth>%') AS x 
      ) AS y 
ORDER BY record_id DESC;


-- Good basic information about memory amounts and state
SELECT total_physical_memory_kb, available_physical_memory_kb, 
       total_page_file_kb, available_page_file_kb, 
       system_memory_state_desc
FROM sys.dm_os_sys_memory;


-- SQL Server Process Address space info 
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb,large_page_allocations_kb, locked_page_allocations_kb,
       total_virtual_address_space_kb, virtual_address_space_reserved_kb, 
       virtual_address_space_committed_kb, virtual_address_space_available_kb,
       page_fault_count, memory_utilization_percentage, available_commit_limit_kb, 
       process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory;


-- Page Life Expectancy (PLE) value for default instance
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = 'Page life expectancy';

-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Below 300 is generally bad.
-- Watch the trend, not the absolute value.


-- Get Buffer cache hit ratio (higher is better)
SELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / 
CAST(B.cntr_value2 AS NUMERIC),3) AS [Buffer Cache Hit Ratio]
FROM ( SELECT cntr_value AS [cntr_value1]
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = 'Buffer cache hit ratio'
) AS A,
(SELECT cntr_value AS [cntr_value2]
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = 'Buffer cache hit ratio base'
) AS B;

-- Buffer cache hit ratio is another measure of memory pressure.
-- A higher value is better. Below 95% is generally bad.
-- Watch the trend, not the absolute value.


-- Buffer Pool Usage for instance
SELECT TOP(20) [type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC;

-- CACHESTORE_SQLCP  SQL Plans         - These are cached SQL statements or batches that aren't in 
--                                     stored procedures, functions and triggers
-- CACHESTORE_OBJCP  Object Plans      - These are compiled plans for stored procedures, 
--                                     functions and triggers
-- CACHESTORE_PHDR   Algebrizer Trees  - An algebrizer tree is the parsed SQL text that 
--                                     resolves the table and column names


-- Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_Exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cacheobjtype = 'Compiled Plan' 
AND cp.objtype = 'Adhoc' 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

-- Gives you the text and size of single-use ad-hoc queries that waste space in plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 only)
-- Enabling forced parameterization for the database can help


-- Database specific queries *****************************************************************

-- Switch to a user database
--USE YourDatabaseName;
--GO

-- Individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;

-- Look at how large and how full the files are and where they are located
-- Make sure the transaction log is not full!!


-- Top Cached SPs By Execution Count (SQL 2008)
SELECT TOP(25) 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
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC;

-- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC;

-- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

-- Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time 
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC;
       
-- Top Cached SPs By Total Logical Writes (SQL 2008). Logical writes relate to both memory and disk I/O pressure 
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], 
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC;


-- Lists the top statements by average input/output usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2, 
    (CASE 
        WHEN qs.statement_end_offset = -1 
     THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
        ELSE qs.statement_end_offset 
     END - qs.statement_start_offset)/2) AS [Query Text]    
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC;

-- Helps you find the most expensive statements for I/O by SP


-- Possible Bad Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;


-- Missing Indexes for entire instance by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

-- Look at last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware


-- Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id], 
p.index_id, COUNT(*)/128 AS [buffer size(MB)],  COUNT(*) AS [buffer_count] 
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

-- Tells you what tables and indexes are using the most memory in the buffer cache



-- Detect blocking (run multiple times)
SELECT t1.resource_type AS [lock type],DB_NAME(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], --- lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], -- spid of waiter  
(SELECT [text] FROM sys.dm_exec_requests AS r                              -- get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, 
    (CASE WHEN r.statement_end_offset = -1 
    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
    ELSE r.statement_end_offset END - r.statement_start_offset)/2) 
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],    -- statement blocked
t2.blocking_session_id AS [blocker sid],                         -- spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p                        -- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1 
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address;

I have a fairly busy schedule of upcoming speaking events (especially for someone with a full-time day job).

I will be giving my Dr. DMV: How to Use Dynamic Management Views to Monitor and Diagnose Performance Issues With High Volume OLTP Workloads presentation at the Rocky Mountain Tech Trifecta event on Saturday, February 27, 2010 at Metro State College in downtown Denver. This is a free all-day event, similar to a Code Camp or SQL Saturday. Other SQL Server speakers who will be there include Steve Jones, Paul Nielsen, Louis Davidson, Eric Johnson,  and Jeremy Lowell. There will also be a lot of good .NET and SharePoint content. You can register for the event here.

I am going to be filming three sessions for the SSWUG.ORG Spring 2010 Ultimate Virtual Conference on March 5 in Tucson. I have never done a filmed session in a studio with no live audience, so that should be fun. The Virtual Conference actually runs April 7-9, 2010, and all three of my sessions are on April 9, and I will be available during the live chat sessions during the event. They have rounded up a very good slate of speakers for this event. These include Denny Cherry, Donald Farmer, Kathi Kellenberger, Kevin Kline, Brian Knight, Devin Knight, Jason Strate, and Craig Utley.

Finally, I will presenting at the Colorado Springs SQL Server User’s Group (CSSQLUG) on Wednesday, May 19, 2010. I will be doing my Dr. DMV: How to Use Dynamic Management Views to Monitor and Diagnose Performance Issues With High Volume OLTP Workloads presentation, using SQL Server 2008 R2, which should be fun.

After seeing an interesting blog post on AnandTech IT last week that showed slower query response time in x64 SQL Server 2008 running on x64 Windows Server 2008 when using the default “Balanced” power plan compared to the “High performance” power plan (in Windows), I decided to do some investigation of my own.

I found this KB article from Microsoft: SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies, which seemed somewhat relevant even though it refers to SQL Server 2005 SP2.

Then I decided to run the GeekBench 2.1.4 benchmark on an ASUS P7P55D motherboard, with a 2.8GHz Intel Core i7 860 CPU and 8GB of DDR3 RAM to see if I would see any performance difference on this processor and memory intensive benchmark. It turns out that I saw a very significant difference with different BIOS settings and Windows Power Plans.

With Intel Enhanced Speed Step and Intel TurboBoost enabled in the BIOS, I got the following GeekBench scores:

“Balanced” Power Plan                   6450

“High performance” Power Plan     7860

The High performance power plan showed a 22% increase over the default Balanced power plan (in an average of five runs).

Next, I rebooted the machine and went into the BIOS setup, and disabled Intel Enhanced Speed Step (which also disables Intel TurboBoost on this motherboard. Running GeekBench five times gave me these average scores:

“Balanced” Power Plan                   7323

“High performance” Power Plan     7330

This shows the effect of TurboBoost also being disabled (which happens on this motherboard when you disable Enhanced Speed Step) on the Intel Core i7 860 CPU (which is the equivalent of the Xeon X3460). The  benchmark scores for the two Windows power plans were the same (within the margin of error) because Enhanced Speed Step was disabled in the BIOS, so Windows 2008 R2 was not able to throttle back the CPU frequency when the processor was lightly loaded.

You can get to the Power Options screen by typing powercfg.cpl in a Run prompt.

Of course, this is just a synthetic benchmark, run on one machine so far, but it does give me some food for thought (and further testing with additional machines and with SQL Server specific benchmarks).  As always, YMMV, and you should do your own testing with your workload in your own environment before you change this setting.

Data compression is an Enterprise Edition only feature that was added in SQL Server 2008. It allows you to use either Page or Row compression on the clustered index or any non-clustered indexes on a table. Data compression lets SQL Server trade off disk space and I/O pressure for some extra CPU pressure. It is best suited to relatively large tables that that are relatively static. Small tables that are highly volatile are not usually good candidates for compression (especially page compression).

I thought I would do a simple experiment to measure the effects of page compression on a table with 2.3 million rows of data. I created four identical tables with the schema shown below. The EventLog table has a non-compressed clustered index, the EventLogCompressed table has a compressed clustered index, the EventLogNew table has a non-compressed clustered index and a non-compressed non-clustered index, and the EventLogNewCompressed table has a compressed clustered index and a compressed non-clustered index. Each table has the exact same data. I wrote four identical stored procedures that hit each of the four tables.

This stored procedure causes a clustered index scan on the tables that don’t have a non-clustered index (EventLog and EventLogCompressed), and an index seek on the tables that have the non-clustered index (EventLogNew and EventLogNewCompressed). The tables with Compressed in their name have page compression on each index in the table. For this data, page compression compressed the clustered index by about 4.5 to 1.

Page compression reduced the logical reads from 23941 to 5545 for the clustered index scan and the query cost by 66%. It also reduced the memory consumed by the clustered index scan from 186MB to 43MB. In my mind, this means that having a compressed clustered index gives you some extra protection from the effects of an expensive clustered index scan. More details about these results are shown below:

-- Table with 2.3 million rows
CREATE TABLE [dbo].[EventLog](
    [EventID] [bigint] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [GroupID] [int] NULL,
    [PostID] [bigint] NULL,
    [FeedID] [int] NULL,
    [EventChange] [int] NOT NULL,
    [EventObject] [int] NOT NULL,
    [EventType] [varchar](50) NOT NULL,
    [EventSource] [int] NOT NULL,
    [DataField] [nvarchar](max) NULL,
    [ExtensionData] [xml] NULL,
    [CreateDate] [datetime] NOT NULL,
    [ParentEventID] [bigint] NULL,
    [Deleted] [bit] NOT NULL,
 CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED 
(
    [EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


-- Non-clustered "covering index"
CREATE NONCLUSTERED INDEX [IX_EventLogNew_Cover1] ON [dbo].[EventLogNew] 
(
    [UserID] ASC,
    [Deleted] ASC,
    [CreateDate] ASC
)
INCLUDE ( [EventID],
[GroupID],
[PostID],
[FeedID],
[EventChange],
[EventObject],
[EventType],
[EventSource]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


-- Return IO Statistics
SET STATISTICS IO ON;

-- Clear out the procedure cache for instance    
DBCC FREEPROCCACHE;
    
-- Don't do this on a production server!
DBCC DROPCLEANBUFFERS;

-- Run four identical stored procedures
-- against identical tables    (except for index changes)
PRINT 'Query 1'; -- Clustered Index Scan
EXEC dbo.GetEventLogByUserID 137926;
PRINT 'Query 2'; -- Clustered Index Scan (compressed index)
EXEC dbo.GetEventLogCompressedByUserID 137926;
PRINT 'Query 3'; -- Index seek
EXEC dbo.GetEventLogNewByUserID 137926;
PRINT 'Query 4'; -- Index seek (compressed index)
EXEC dbo.GetEventLogNewCompressedByUserID 137926;

-- Statistics IO output
--Query 1 Clustered index scan against EventLog table
--Table 'EventLog'. Scan count 3, logical reads 23941, physical reads 176, 
--read-ahead reads 23858, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Query 2 Clustered index scan against EventLogCompressed table
--Table 'EventLogCompressed'. Scan count 3, logical reads 5545, physical reads 45, 
--read-ahead reads 5509, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Query 3 Index seek against EventLogNew
--Table 'EventLogNew'. Scan count 1, logical reads 3, physical reads 3, 
--read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Query 4 Index seek against EventLogNewCompressed
--Table 'EventLogNewCompressed'. Scan count 1, logical reads 3, physical reads 2, 
--read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


-- Breaks down buffers used by current database 
-- by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], 
p.index_id, COUNT(*)/128 AS [buffer size(MB)],  
COUNT(*) AS [buffer_count] 
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

-- Buffer space results
--ObjectName                index_id    buffer size(MB)    buffer_count
--EventLog                    1            186                23855
--EventLogCompressed          1             43                 5508
--EventLogNew                 5              0                   24
--EventLogNewCompressed       2              0                   16

The Windows Server Division blog has a post up trumpeting a very impressive iSCSI benchmarking result achieved with Intel hardware and Microsoft Windows Server 2008 R2 software in January of 2010. Running on server hardware equipped with an Intel Xeon 5580 CPU and an Intel 82599 10GbE network interface card (NIC), Windows Server 2008 R2 achieved 715,000 IOPs. The Windows Virtualization Team blog has some more details here. This is even more interesting because it was done on Hyper-V.

Both Intel and Microsoft recommend enabling jumbo frames for iSCSI workloads. It is important to make sure the entire network path has it enabled.

It seems to be SQL Azure night for me tonight…  I thought I would try out some of the new features in SQL Azure Service Update 1. First I tried ALTER DATABASE to switch between the Web Edition and Business Edition size for a SQL Azure database. I discovered that you have to be connected to the master database in your SQL Azure “instance” (which makes sense). Remember, you cannot just run USE master, or switch the database context in the dropdown in the toolbar in SSMS. You have to be using the November CTP version of SQL Server 2008 R2 SSMS to work with SQL Azure in SSMS.

You have to change the connection to connect specifically to the database that you want (which is a little inconvenient). Then you can run your ALTER DATABASE command. I refreshed the Server Administration web page in the SQL Azure portal to confirm that the database edition had changed.

Then, I tried running the six DMV queries, and they all worked just fine. Finally, I waited more than five minutes (but less than 30 minutes) in SSMS, and verified that SQL Azure did not drop my connection. Everything worked just fine for these three new features.

-- Testing new features in SQL Azure Service Update 1
-- Glenn Berry
-- 2/18/2010

-- Must connect to master database first
-- Remember, there is no USE databasename allowed
-- Switch to Business Edition
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 10 GB);

-- Switch to Web Edition
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 1 GB);

-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- Test new DMV queries in SQL Azure

-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], *
FROM sys.dm_db_partition_stats; 

SELECT *
FROM sys.dm_exec_connections; 

SELECT *
FROM sys.dm_exec_requests; 

SELECT *
FROM sys.dm_exec_sessions; 

SELECT *
FROM sys.dm_tran_database_transactions;  

SELECT *
FROM sys.dm_tran_active_transactions;