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
. You put the sensor inside the freezer, plug the freezer into the controller, then plug the controller into an outlet. Quick and easy. You can get these from your local brewer supply shop, or online, about $80.
).
