What's slow on my database server?

What's slow on my database server?

A video investigating the relative performance of network, disk IO, and CPU on SQL Server.

Here is the SQL that goes with the demo.

-- Database by Doug
-- 10/8/2015
-- What's Slow Demo?

-- for database performance
-- There are 3 basic items to worry about:
-- * IO
-- * Network
-- * CPU

-- I see many people focus on them in this order:
-- 1 - CPU
-- 2 - IO
-- 3 - network

-- this demo should show that you should focus on them in this order:
-- 1 - network
-- 2 - IO
-- 3 - CPU


-- part 1: network vs IO
use referencedb

SET STATISTICS IO ON

SELECT *
FROM   Person

-- about 53 seconds

-- while this runs, here's some basics about the setup:

-- SQL Instance running as a virtual machine, no idea about the storage, general purpose
-- synthetic data with reasonable data distributions
-- 1.3M records
-- average record size is about 456 bytes
-- zero fragmentation
-- total table size about 587MB
-- some bogus fields for experimentation
-- no non-clustered indexes

--CREATE TABLE [dbo].[Person](
-- [ID] [int] IDENTITY(1,1) NOT NULL,
-- [salutory] [nvarchar](15) NULL,
-- [firstName] [nvarchar](25) NULL,
-- [middleInitial] [nchar](1) NULL,
-- [lastName] [nvarchar](25) NOT NULL,
-- [nameSuffix] [nvarchar](8) NULL,
-- [email] [nvarchar](255) NULL,
-- [entryDate] [datetime] NOT NULL CONSTRAINT [DF_Person_entryDate]  DEFAULT (getdate()),
-- [lastUpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Person_lastUpdateDate]  DEFAULT (getdate()),
-- [weight] [float] NULL,
-- [gender] [char](1) NULL,
-- [dateOfBirth] [date] NULL,
-- [bogusGUID] [uniqueidentifier] ROWGUIDCOL  NULL CONSTRAINT [DF_Person_bogusGUID]  DEFAULT (newid()),
-- [bogusGUID2] [uniqueidentifier] NULL CONSTRAINT [DF_Person_bogusGUID2]  DEFAULT (newid()),
-- [bogusChar] [char](100) NOT NULL CONSTRAINT [DF_Person_bogusChar]  DEFAULT ('fred'),
-- [bogusNChar] [nchar](100) NOT NULL CONSTRAINT [DF_Person_bogusNChar]  DEFAULT (N'george'),
-- CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [ID] ASC)
--) ON [PRIMARY]


-- ok, so what about this?
SELECT TOP 1 ID
FROM   Person

-- one record, one value, 4 bytes
-- 0 seconds - the time for a round trip

-- so what is slow?

SELECT ID
FROM   Person
-- 1.3M records, 4 bytes each
-- about 4 seconds

SELECT ID, ID
FROM   Person
-- 1.3M records, 8 bytes each
-- about 5 seconds

SELECT bogusGuid
FROM   Person
-- 1.3M records, 16 bytes each
-- about 5 seconds


SELECT bogusGUID,
       bogusGUID2
FROM   Person
-- 1.3M records, 32 bytes each
-- about 6 seconds

SELECT 75804 * 8/1024

SELECT bogusGUID,
       bogusGUID2,
       '1234567890'
FROM   Person
-- 1.3M records, 42 bytes each
-- about  seconds

SELECT bogusGUID,
       bogusGUID2,
       '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
FROM   Person
-- 1.3M records, 132 bytes each
-- about 16 seconds

SELECT bogusGUID,
       bogusGUID2,
       '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890',
       '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
FROM   Person
-- 1.3M records, 232 bytes each
-- about 27 seconds


-- keep going, and you get a graph that looks like ...

-- but you are cheating - you have all your data in memory...

-- now starve the db server for memory
  /**
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 256;
GO
RECONFIGURE;
GO
**/
SELECT ID
FROM   Person
-- 1.3M records, 4 bytes each
-- about 4 seconds



-- network is slower than IO
-- even with  physical IO

-- **************************************************


 /**
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2147483647;
GO
RECONFIGURE;
GO
**/

-- part 2: IO vs CPU


SELECT AVG(weight)
FROM   Person
-- 1.3M records, but only 8 bytes back
-- about 0 seconds

-- can we make it worse?
-- can we make the CPU worse?

SELECT weight
FROM   Person
-- 1.3M records, 8 bytes back per record
-- 4 seconds

SELECT MAX(weight)
FROM   Person
-- about 0 seconds

SELECT AVG(LOG(weight/1.7))
FROM     Person
-- 1.3M records, 8 bytes back
-- about 0 seconds

SELECT AVG(SQRT(LOG(weight/1.7)))
FROM     Person
-- about 0 seconds

SELECT AVG(SIN(SQRT(LOG(weight/1.7))))
FROM     Person
-- about 0 seconds

SELECT AVG(SIN(SQRT(10+3.3*LOG(weight/1.7))))
FROM     Person
-- about 1 seconds

SELECT AVG(SIN(SQRT(10+3.3*LOG(weight/1.7)))*PI())
FROM   Person
-- about 1 seconds

-- taking just the average out
SELECT (SIN(SQRT(10+3.3*LOG(weight/1.7)))*PI())
FROM   Person
-- 4 seconds

-- you can do tons of calculations, and not impact speed

-- summary: focus on items in this order
-- 1 - network
-- 2 - IO
-- 3 - CPU

-- caveats:
-- your mileage may vary
-- use your own judgement
-- this is the order in which I do investigations on a new database

-- end of what's slow