Perfmon was collecting stats... what happened next was...

whackadoodle!

I can't think of any other word to describe it.  Let me know if you've ever seen anything like this from perfmon on a physical windows server.

Perfmon was running locally on a 4 socket, six core per socket server with no Hyper-Threading, logging to tsv file in 15 second intervals.  The results were sent to me for review.

The 24 columns below are for "\Processor(x)\% Processor Time" for 0<=x<=23.

See the zany for yourself below. 


Duplicate times.  Missing per-cpu numbers.  Suspect per-cpu CPU busy reported. And suddenly, 10 minutes later, a return to normal.  I'm thinking major fault at CPU level or maybe a significant memory error.  So I'm hoping to dig details or at least a clue from the system or event log.  But, I'm open to any ideas... because other than that I don't have any.  And I usually have lots of ideas, even if they aren't very helpful :-)

This isn't what I was expecting to see at all.  I thought I was just going to see some of the disk IO and memory management issues I normally deal with on SQL Server configs.  Maybe some spinlocks.  Nothing that would completely throw me for a loop.  Maybe I just need more coffee.

**** Updated with Exhibit 1 for some context.  SQL Server is expected to be the only significant consumer of CPU, memory, or IO resources from this physical Windows server.  Note the way that the two extraordinary timeperiods are the two deviations from SQL Server CPU consumption strongly correlating with "total server CPU consumption." ****

	cpu0	cpu1	cpu2	cpu3	cpu4	cpu5	cpu6	cpu7	cpu8	cpu9	cpu10	cpu11	cpu12	cpu13	cpu14	cpu15	cpu16	cpu17	cpu18	cpu19	cpu20	cpu21	cpu22	cpu23
16:47:14	20	18	11	6	4	2	20	12	6	2	1	2	13	15	6	38	17	30	12	13	20	16	11	13
16:47:29	19	18	11	4	2	1	19	11	8	2	1	0	14	14	5	39	14	31	13	16	18	16	11	10
16:47:44	20	16	10	5	3	1	19	11	6	2	2	0	13	14	6	36	17	26	11	12	27	14	9	12
16:47:44	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18
16:47:59	21	17	9	4	2	1	22	12	5	3	1	0	13	17	5	37	15	31	17	14	23	12	6	6
16:47:59	100	46	46	46	46	46	100	46	46	46	46	46	100	100	46	46	46	46	46	46	46	46	46	46
16:48:14	22	20	11	4	2	1	7	16	8	34	21	13	12	15	6	39	19	32	13	14	26	12	8	9
16:48:14																	44	100		44
16:48:29	19	19	8	6	4	1	21	14	5	1	1	0	11	14	5	39	17	29	12	14	22	13	5	7
16:48:29	46	46	0	0	0	0	0	46	0	0	0	0	0	46	0	0	46	46	46	46	0	100	0	46
16:48:44	20	19	9	5	2	1	19	12	8	4	2	1	12	14	5	40	16	29	14	10	26	13	9	11
16:48:44							44													100
16:48:59	18	17	9	6	3	1	18	15	7	3	1	0	13	14	8	35	14	22	13	12	24	17	8	8
16:48:59														44				44			100			44
16:49:14	20	18	9	6	4	2	22	13	8	3	0	2	11	12	6	37	19	27	12	13	24	14	5	9
16:49:14	46	46	46	46	46	46	46	100	100	46	46	46	46	46	46	100	46	46	46	46	46	46	46	46
16:49:29	18	16	10	5	2	1	20	12	7	2	2	0	11	14	6	38	18	29	14	12	27	15	7	10
16:49:29
16:49:44	20	15	10	5	2	1	19	9	6	2	3	0	12	14	6	39	20	25	13	13	23	14	5	9
16:49:44	0	0	0	0	0	0	0	0	0	0	0	0	46	46	0	0	0	100	0	46	100	0	0	46
16:49:59	20	14	11	7	2	1	20	15	6	1	0	0	12	14	4	40	14	29	12	14	29	17	8	9
16:49:59	44	44				44	44		44				44		100		44						100	100
16:50:14	25	17	11	5	5	3	19	15	8	3	2	1	12	15	8	37	17	28	13	14	26	16	12	11
16:50:14	46	46	100	0	0	0	100	100	0	0	0	0	0	0	0	46	46	46	100	46	0	0	0	46
16:50:29	20	18	6	6	2	1	23	13	4	2	2	0	13	14	7	38	17	28	13	13	27	13	9	9
16:50:29	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44
16:50:44	21	15	10	3	2	2	20	13	7	2	1	0	13	13	3	38	17	36	11	12	25	16	7	12
16:50:44	0	46	0	0	0	0	0	0	0	0	0	0	0	46	0	100	0	46	46	0	46	46	0	0
16:50:59	14	20	12	10	5	1	18	17	9	4	2	1	6	12	1	52	19	45	8	13	32	25	7	10
16:50:59	44	44	100	44	44	44	44	100	44	44	44	44	44	100	44	44	44	100	44	44	100	44	44	44
16:51:14	15	22	16	12	4	2	18	16	12	6	2	2	7	14	2	52	23	46	3	11	25	32	21	17
16:51:14				44										44		100		44		44	100		44	44
16:51:29	12	24	15	10	4	1	19	17	11	3	2	0	7	14	1	52	22	47	4	11	29	26	12	13
16:51:29	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	100	46	0	0	46	46	0	0	46
16:51:44	16	21	15	13	4	2	20	17	10	3	1	0	7	14	1	52	20	47	5	13	28	30	17	14
16:51:44																44		44			100
16:51:59	16	20	14	7	5	1	20	16	10	4	2	0	6	15	2	52	22	46	5	14	27	28	13	11
16:51:59	0	0	46	0	0	0	0	0	0	0	0	0	46	0	0	0	46	46	0	0	46	46	0	46
16:52:14	15	22	13	10	4	2	19	18	11	3	1	0	7	15	2	51	23	49	3	9	31	31	16	13
16:52:14	44	44	44	44	44	44	44	44	44	44	44	44	44	44	44	100	44	44	44	44	44	44	44	100
16:52:29	14	22	14	10	4	2	19	16	11	5	3	0	6	14	2	51	23	48	3	11	24	28	17	13
16:52:29	0	0	46	0	0	0	46	0	0	0	0	0	0	0	0	100	0	0	0	0	0	0	0	0
16:52:44	15	23	13	14	8	2	21	18	10	4	2	0	5	12	1	52	20	50	5	15	27	30	15	15
16:52:59	13	21	14	10	6	2	20	16	9	4	4	2	7	12	2	49	25	50	6	13	20	29	19	12
16:52:59			100				100									100				100	100
16:53:14	14	20	16	13	7	4	18	18	11	4	1	1	5	14	1	49	22	50	4	11	24	32	24	20
16:53:14			100					100								100						100		100
16:53:29	13	22	17	11	7	2	20	17	13	6	3	2	5	13	2	51	20	50	3	11	26	30	18	13
16:53:29			100													100				100		100		100
16:53:44	14	25	14	15	9	5	20	14	7	4	1	0	5	15	2	53	24	48	5	13	25	27	18	20
16:53:44			100		100					100	100					100						100		100
16:53:59	12	23	13	13	8	4	20	18	14	6	2	0	4	14	2	51	22	50	5	12	23	31	17	13
16:53:59	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:54:14	19	23	14	9	6	3	20	16	10	6	2	0	7	13	1	50	21	48	6	17	28	28	16	18
16:54:14	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:54:29	14	20	16	10	5	2	19	19	11	5	1	0	6	13	1	53	23	44	4	10	25	32	15	14
16:54:29									100							100		100
16:54:44	15	22	13	9	4	1	19	17	13	7	1	1	8	15	2	51	23	48	7	12	29	30	15	15
16:54:44																		100		100
16:54:59	16	23	13	7	3	1	17	16	13	11	3	0	7	14	1	51	24	47	4	10	22	32	15	19
16:54:59																100								100
16:55:14	19	22	19	12	9	8	20	17	12	8	3	1	7	15	2	54	25	45	3	11	25	34	27	17
16:55:14										100						100								100
16:55:29	12	21	16	10	3	1	17	18	15	6	2	1	7	15	2	53	22	48	3	8	24	33	20	20
16:55:29	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:55:44	16	21	14	8	4	2	19	17	13	6	1	1	6	14	4	52	19	50	5	8	28	27	17	18
16:55:44	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:55:59	14	19	15	12	4	1	17	17	13	6	3	1	5	15	2	52	19	48	2	9	24	34	24	21
16:55:59	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:56:14	13	20	17	13	5	3	19	17	13	4	1	1	8	13	2	53	23	47	3	10	28	33	20	14
16:56:14								100										100					100
16:56:29	17	21	13	6	3	1	19	17	9	3	1	0	6	16	2	50	23	45	3	11	29	29	15	12
16:56:29		100												100				100					100	100
16:56:44	17	22	16	8	4	1	17	16	13	4	2	0	9	16	2	53	20	47	4	9	24	29	12	15
16:56:44																100								100
16:56:59	12	21	13	8	5	1	17	19	10	6	2	1	6	13	2	50	21	49	6	13	29	24	9	9
16:56:59	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:57:14	15	21	15	10	4	2	18	17	10	5	2	0	5	16	2	56	19	45	6	12	27	26	13	13
16:57:14	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:57:29	12	21	16	12	5	1	20	18	10	4	1	0	6	15	1	53	24	47	4	9	28	31	16	17
16:57:29	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
16:57:44	16	22	15	9	5	2	18	19	9	5	1	1	7	16	1	52	23	44	3	10	25	31	17	19
16:57:59	13	21	16	9	5	2	21	15	9	4	1	1	5	12	2	49	24	50	4	9	29	31	18	16
1:53:14	21	25	22	3	5	4	14	3	2	4	4	7	45	3	20	18	63	16	12	10	17	10	23	10
1:53:29	20	15	4	2	2	2	7	2	2	3	0	0	22	12	14	6	45	12	14	5	18	5	2	3
1:53:44	16	34	25	19	25	19	28	20	7	21	9	4	19	30	44	15	45	25	11	15	16	29	21	8
1:53:59	15	6	2	0	0	0	2	1	0	0	0	0	30	8	9	13	39	10	12	4	16	0	1	0
1:53:59	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	100	100	46	46	46	46	46	46	46
1:54:14	17	5	1	1	2	0	6	3	0	0	0	0	10	15	27	23	67	11	14	6	14	2	2	4
1:54:14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	100	0	0	0	100	0	0	100
1:54:29	14	6	2	1	0	1	4	1	0	0	0	0	38	15	12	5	28	8	19	4	10	2	1	1
1:54:29	44	44	44	44	44	44	44	44	44	44	44	44	44	100	44	44	44	44	44	44	44	44	44	44
1:54:44	10	6	1	8	1	0	2	1	0	1	0	0	28	5	10	6	33	3	9	4	16	1	0	0
1:54:44	0	0	0	0	0	0	0	0	0	0	0	0	46	0	0	0	0	0	0	0	0	0	0	0
1:54:59	24	3	5	2	7	0	3	0	0	0	0	0	27	17	16	14	28	2	14	9	14	7	3	0
1:54:59			44		100	100								100	44									44
1:55:14	17	3	12	3	22	0	1	0	0	0	0	0	28	24	18	2	18	1	11	3	16	2	2	4
1:55:14	0	0	0	0	0	0	46	0	0	0	0	0	100	0	0	0	0	0	0	0	0	0	0	0
1:55:29	11	4	2	3	4	1	5	1	4	4	2	0	37	15	16	6	29	4	10	12	19	9	11	4
1:55:29									44				100								100
1:55:44	9	2	1	0	0	0	4	1	1	5	0	0	31	9	11	1	34	0	9	2	12	7	1	1
1:55:44	100	46	46	46	46	46	46	46	46	46	46	46	46	46	100	46	46	46	100	46	46	46	46	46
1:55:59	15	7	6	5	6	1	17	3	5	8	0	0	12	30	15	7	61	16	6	16	22	46	10	12
1:55:59										100							100		100
1:56:14	20	12	21	8	15	13	18	6	30	23	4	2	19	32	28	22	52	27	21	18	26	20	22	9
1:56:14	46	0	0	0	0	0	46	0	0	0	0	0	0	46	0	0	100	0	0	46	100	0	0	46
1:56:29	24	16	19	17	15	8	16	15	10	12	2	2	10	34	23	18	61	25	9	19	33	10	15	7
1:56:29	0	0	46	0	0	0	0	100	0	0	0	0	0	0	0	0	100	100	0	46	0	100	0	0
1:56:44	26	5	8	5	6	0	15	29	5	5	1	0	17	18	31	6	58	7	13	11	16	6	0	3
1:56:44	100	2	2	2	2	2	2	2	2	2	2	2	2	2	100	2	100	2	2	2	100	2	2	2
1:56:59	22	12	21	5	6	9	7	0	0	4	0	0	7	13	52	10	62	18	15	13	14	4	3	4
1:56:59	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	46	100	46	46	100	46	46	46	46
1:57:14	26	5	7	1	0	0	9	1	0	4	0	0	10	14	17	14	62	10	10	11	16	5	3	8
1:57:14	100		44				100										100					44
1:57:29	38	16	13	3	10	2	14	7	6	11	1	0	8	18	16	8	65	38	16	15	16	3	1	2
1:57:29	46	46	46	100	46	46	46	46	46	46	46	46	46	46	46	46	100	46	46	100	100	46	46	46
1:57:44	30	10	9	2	9	0	9	4	0	8	0	0	31	13	6	7	42	2	9	14	16	1	1	2
1:57:44	44			44												100	100
1:57:59	23	26	9	10	10	12	28	2	9	4	3	2	32	7	18	6	46	20	13	20	17	12	7	4
1:57:59	0	0	0	0	0	0	0	0	0	0	0	0	0	0	48	0	100	0	0	0	100	0	100	48
1:58:14	27	29	16	22	20	24	33	12	20	11	7	4	26	21	26	26	60	29	32	20	32	9	23	10
1:58:14	44	44	100	44	44	44	44	100	44	100	44	44	44	100	44	44	44	44	44	44	44	44	44	44
1:58:29	27	8	10	1	3	1	29	3	1	7	1	1	2	24	17	29	55	26	17	5	15	3	7	4
1:58:29	0	0	0	0	0	0	100	0	0	0	0	0	0	0	46	0	46	46	46	0	0	0	0	0
1:58:44	25	31	19	13	17	19	23	26	15	25	15	11	27	25	16	44	51	27	21	15	35	8	14	13
1:58:44	38	38	38	38	100	100	38	38	100	38	38	38	38	38	38	38	100	38	38	38	100	38	38	38
1:58:59	16	13	16	1	5	1	42	4	5	6	0	3	24	22	18	12	52	6	45	7	16	2	4	9
1:58:59	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
1:59:14	21	9	5	1	1	0	44	6	2	15	2	0	13	12	9	12	55	13	16	6	18	4	5	5
1:59:14	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
1:59:29	16	4	7	1	1	0	20	3	4	5	0	6	19	7	6	16	65	1	15	5	16	6	2	2
1:59:29															100		100
1:59:44	22	7	4	1	0	0	27	4	2	4	4	1	23	10	8	3	61	5	14	5	16	2	2	4
1:59:44																			100					100
1:59:59	20	14	5	1	1	0	30	1	0	2	0	0	18	10	19	6	61	5	18	2	11	1	2	1
1:59:59	100												100											100
2:00:14	28	8	16	12	11	12	29	20	16	19	3	5	24	26	16	37	56	16	13	11	33	11	16	29
2:00:14	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:00:29	7	5	37	35	34	40	49	57	55	57	7	13	6	11	7	59	72	3	13	15	24	5	4	4
2:00:29	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:00:44	5	7	38	44	39	44	29	66	63	62	3	4	11	3	6	54	29	2	20	11	15	3	58	8
2:00:44	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:00:59	12	23	43	35	19	19	32	53	50	47	8	8	14	17	9	37	14	3	26	12	20	5	9	5
2:00:59	100	100	100	100					100						100						100
2:01:14	21	32	13	6	6	22	27	7	32	7	22	23	7	42	29	9	3	0	7	28	35	9	15	9
2:01:14																								100
2:01:29	23	7	5	1	1	0	24	1	10	0	0	0	5	1	0	0	0	0	8	3	15	8	1	2
2:01:44	28	12	2	0	2	0	3	1	0	0	0	0	5	1	0	2	0	0	8	3	9	2	0	1
2:01:44	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:01:59	21	6	3	0	2	0	4	0	0	0	0	0	6	1	0	0	0	0	15	4	22	8	13	2
2:01:59	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:02:14	10	2	1	1	0	0	1	0	0	0	0	0	2	1	0	0	0	0	10	2	14	2	3	3
2:02:14	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:02:29	6	16	18	7	14	19	12	1	19	13	0	12	3	14	17	5	12	14	7	16	24	19	51	14
2:02:29
2:02:44	7	4	0	1	0	1	2	0	0	0	0	0	4	3	1	0	0	0	16	12	16	13	24	6
2:02:44																								100
2:02:59	5	7	2	1	0	0	3	1	0	1	0	0	6	2	0	3	0	0	18	7	11	2	6	6
2:02:59																								100
2:03:14	6	0	0	0	0	0	1	0	0	0	0	0	4	2	0	0	0	0	22	15	16	2	13	2
2:03:14	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:03:29	5	1	0	0	0	0	3	1	0	0	0	0	4	1	0	0	0	0	22	13	18	2	8	3
2:03:29	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:03:44	16	11	3	1	6	0	6	1	6	0	0	6	15	2	3	5	0	0	14	27	33	3	24	14
2:03:44	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
2:03:59	19	3	1	0	0	0	12	0	0	0	0	0	14	3	0	0	0	0	1	50	39	4	45	3
2:04:14	17	7	11	2	1	2	20	7	4	1	2	2	19	5	10	6	1	1	5	38	30	8	80	17
2:04:29	2	1	43	2	3	0	2	19	0	9	15	13	10	7	8	0	0	0	6	1	28	2	8	5
2:04:44	9	18	11	5	2	2	12	2	7	9	2	6	5	11	22	11	11	7	8	12	29	5	7	13
2:04:59	34	14	5	19	11	3	10	28	16	55	24	28	31	30	31	8	13	11	19	8	23	27	25	6

Here's something crazy: sight-unseen Oracle storage and parallelism recommendations ;-)

****A small update to clarify... although I do some work with SSD drives and flash both in storage arrays and onboard at the database server, the model I describe below assumes fibre channel SAN storage with an HDD config****

****Another update with a performance consideration.  Below I recommend spreading the data in chunks across the available LUNs.  With ASM, I recommend the 4 mb au size.  For other LVMs, use a ppsize/extent size NO LARGER than 16mb, to prevent queue depth overruns by many reads from parallel queries in a dense contiguous data area.  16mb in a logical volume manager rotating among the appropriate number of LUNs is workable.  Smaller is better... 8mb or 4mb.  I don't recommend going below 4mb.  Remember that the ppsize/extent size may have consequences for maximum logical volume size and/or maximum volume group size.****


I work with a crazy workload, so don't take recommendations from me without some significant testing.  The workload I tune for is 4 queries (at least) per x86 core (8 queries per Power core) with parallel_max_servers at 16 per x86 core (32 per Power core).  I also tune for SMP, not RAC.  But tuning for RAC this way can also be done...  assuming complete data level isolation among the nodes it would be the same.  RAC nodes fetching data from each other lowers the total read liability that I eventually mention below.  So my formula isn't completely linear if applied to RAC because hopefully no RAC workload has nodes that are completely data independent of each other :)

On Linux, I recommend ASM only, at least until the 3.13 kernel and the benefits of the multi-queue block layer. 
http://bit.ly/18ivXci
http://bit.ly/OwiMxC

****Updated to include AIX and Sparc Solaris details.  Silly of me to assume that quad core in the twitter thread meant x86 :-)****

On AIX I recommend ASM because its a portable knowledge base, and I also believe its a better bet for long-term performance capacity as databases age and experience transitions in administrators.  But JFS2 filesystems and filesystemio_options=SETALL can achieve similar level of performance.  Same LUN count as below in a volume group, use a physical partition size (ppsize) 16mb or less maximum "inter-physical volume allocation policy".

On Sparc Solaris I recommend ASM most of all.  Veritas lvm and fs can give great performance and I love the fact that its a portable skill set (HP-UX and AIX).  UFS is a solid, reliable filesystem that can perform very, very well but the number of folks that can tune UFS for maximum performance grows smaller every year.  I lover host-side ZFS for many purposes, including lightweight proof-of-concept databases, training databases, and dev/test databases outside of scalability/performance context.  If your workload is like mine, however, ZFS can only outperform UFS if you load up with so much host-side onboard flash that honestly I'd rather put to other use.  And it might not even outperform UFS then.  Whether Veritas or UFS, use wide spreading in the logical volumes across LUNs/physical volumes.  16 mb at most.  Smaller is better, down to 4 mb when possible.

****



For my workloads, where bandwidth and throughput are king, I scale the number of ASM disks for Oracle tablespaces based on the core count.  Eight ASM disks for tablespaces for x86 core count mod 12, minimum of 8 ASM disks for tablespaces (mod 6 for Power7 cores).  12 x86 cores? 8 ASM disks for tablespaces.  16 x86 cores? Also 8 disks.  8 x86 cores?  Also 8 disks.  60 cores (one of these new 4 socket, 15 cores per socket servers)=40 ASM disks for tablespaces.

Two ASM disks for redo logs.  Although my workload can do wonders at full fibre channel bandwidth and up to 100 ms read latency (maybe even a smidgeon more as long as bytes/sec throughput is high), I still need low write latency to the redo logs and fast log backups that don't interfere with the redo log write latency.

Now... LUN queue depth.  I assume a LUN queue depth of 32, because its the "minimum of the allowed maximums" for the various storage types I work with.  EMC VNX virtual provisioning gives maximum LUN queue depth of 32 and Hitachi USP-V/VSP, etc have maximum LUN queue depth of 32.  Some types of storage allow cranking that way up to 256!  That's great - for me that is value-add though, not a planning specification.  If I move a given database from one storage technology to another I don't want to re-work the data layout because I originally assumed and depended on a really high queue depth.

Multi-pathing?  Yes, please :)  I assume 2 paths, round robin.  Maybe even tweaking down the IO interval for switching between paths from its default.

Fibre channel adapter queue depth tends to be good on Linux - I want at least 1024 allowed outstanding IOs per HBA port.

Maximum transfer size of at least 1 mb at the LUN and adapter level.  For Oracle 11gR2 and beyond, no specified dbfmbrc so that it defaults to 128*8=1mb.

OK.  An ASM allocation unit (AU) of 4mb or smaller.  Oracle just recently began documenting the general recommendation of 4 mb au.  The idea behind it is with a 4mb ASM au, the chances for a 1mb or near 1 mb multiblock read are greater than if the au is 1mb.

OK.  So where does that leave us?  We've got a framework for host-side configuration that allows a pretty high level of throughput whether checking IOPs, bytes/second, or snapshot count of in-flight IOs.

The twitter thread that prompted this post is based on at least 80 EMC disks available to the host.  I'll assume they are 15k fibre channel disks.  For my workloads, I can get 2.4gigabytes/sec or more with about 24,000 IOPs sustained from 80 disks on a VNX using virtual provisioning and RAID5 4+1s.  I'd be tempted to push it even higher by using traditional LUNs... but the flexibility of virtual provisioning is REALLY handy.

What's gonna drive that throughput on the host side?  The number of concurrent serial queries and their in-flight IO plus the parallel queries and their in-flight IO.  The maximum in-flight read liability of parallel queries is where parallel_max_servers comes in.  I don't even remember exactly WHY but I use as assumption of 4 maximum in-flight reads per parallel server.  And I want to accommodate at least half the in-flight read liability of the parallel queries in the aggregate LUN queue depth.

So, if I've got 16 ASM LUNs for the tablespaces with 32 queue depth per LUN, aggregate queue depth is 512.  At 4 reads per parallel server, that's enough for 256 parallel servers (assuming accommodating half the liability in the aggregate queue depth).   

Wait... what?  Planning query parallelism based on the storage rather than CPU?  Yup.  But the storage configuration is informed by the CPU count.  And the CPU count was planned carefully because the CPU count determine license fees, and licenses are doggone expensive. :-)

Here's the way I think of IO bound workloads: unless the CPUs are fed fast enough by the storage, they won't be able to stretch their legs.  Unused CPU (below the 70%/80% threshold anyway) is money that's not working for me :)

Take it with a grain of salt and a lot of testing.  This type of storage and system planning works for my throughput/bandwidth sensitive workloads.  YMMV.  But if your workload is truly IO bound - rather than simply not yet tuned ;-) thinking about it this way may be valuable.

I try to model stuff this way because although tuning parallelism isn't easy, its much faster and typically lower risk to make parallelism changes (with before and after metrics captured to evaluate) than making storage changes like converting from an OS native logical volume manager to ASM or increasing the LUN count and rebalancing data.

OK... enough rambling for one morning.  Hope its helpful for someone :-)

Windows Server 2008 and beyond: NtfsDisableLastAccessUpdate disablesNTFS LastAccessTime by default

For Oracle database and other database engines implemented on UNIX or Linux systems, disabling atime access time updates to filesystems can be an important performance optimization.  For Oracle 11gr2 on AIX 7.1, using the noatime mount option for a JFS2 root filesystem should even be considered.

So when I learned about the NtfsDisableLastAccessUpdate registry setting, my first thought was: wow... why did it take so long for me to hear about this?

Now I know :-) I only started paying attention to the Windows Server world after WS2008. Turns out that for WS2008 and beyond, LastAccessTime updates are off by default, as cited below.

http://msdn.microsoft.com/en-us/library/ff469400.aspx

"<16> Section 2.1.1.3: In Windows Vista/Windows Server 2008 and later, LastAccessTime updates are disabled by default in the ReFS and NTFS file systems. It is only updated when the file is closed. This behavior is controlled by the following registry key: HKLM\System\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate. A nonzero value means LastAccessTime updates are disabled. A value of zero means they are enabled."

****Updated 3/16/2014****

A registry query for filesystem options will indicate whether LastAccessTime is being maintained or not.  A value of 1 for NtfsDisableLastAccessUpdate means its not being maintained - this is default for Windows Server 2008 and beyond.  A value of 0 means this access time IS being maintained.

C:\Windows\system32>reg query HKLM\System\CurrentControlSet\Control\FileSystem\

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem
    DisableDeleteNotification    REG_DWORD    0x0
    SymlinkLocalToLocalEvaluation    REG_DWORD    0x1
    SymlinkLocalToRemoteEvaluation    REG_DWORD    0x1
    SymlinkRemoteToLocalEvaluation    REG_DWORD    0x0
    SymlinkRemoteToRemoteEvaluation    REG_DWORD    0x0
    Win31FileSystem    REG_DWORD    0x0
    Win95TruncatedExtensions    REG_DWORD    0x1
    NtfsAllowExtendedCharacter8dot3Rename    REG_DWORD    0x0
    NtfsBugcheckOnCorrupt    REG_DWORD    0x0
    NtfsDisable8dot3NameCreation    REG_DWORD    0x2
    NtfsDisableCompression    REG_DWORD    0x0
    NtfsDisableEncryption    REG_DWORD    0x0
    NtfsDisableLastAccessUpdate    REG_DWORD    0x1
    NtfsDisableVolsnapHints    REG_DWORD    0x2
    NtfsEncryptPagingFile    REG_DWORD    0x0
    NtfsMemoryUsage    REG_DWORD    0x0
    NtfsMftZoneReservation    REG_DWORD    0x0
    NtfsQuotaNotifyRate    REG_DWORD    0xe10
    UdfsCloseSessionOnEject    REG_DWORD    0x3
    UdfsSoftwareDefectManagement    REG_DWORD    0x0
 

 ****End Updat****

In Search of... #SQLServer stats maintenance model(s) Part I: Trace Flag 2388

Statistics!!?!  Argghhhh!

Had to get that out of my system.

No fancy graphs today, not even any code to share.  Gotta get these thoughts into bytes, though or this will become just another of the blog posts I should write but won't.

I believe performance & resource utilization gains should offset the cost of statistics & index maintenance.  And I love models! So I need a model for stats 'potential benefit' to decide when and how to update, and a model for 'benefit realized' so the choice can be evaluated afterward.  This post is part of my journey to those destinations.

Models don't need to be perfect... but they should be useful.

"Essentially, all models are wrong. But some are useful."
page 424
Empirical Model Building and Response Surfaces
Box, G.E.P. & Draper, N.R.

Auto stats updates in SQL Server 2008 R2 and beyond are triggered based on rules involving colmodctr, a column modification counter for the leading column of the stats key.  Great - secret knowledge! But colmodctr is only available (afaik) via DAC.  Bummer! I want to use DAC as sparingly in SQL Server as I use kdb in UNIX.  It's not a wise way to regularly model maintenance on a tool that is restricted to that degree, imo.

Enter a new dmv - Sys.dm_db_stats_properties! And it has a mysterious column 'modification_counter'.  Based on its description, it sounds like an equivalent of colmodctr - or at least a very closely correlated proxy.  Awesome!

sys.dm_db_stats_properties (Transact-SQL)

http://bit.ly/1nFu92u

But... it's introduced in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1.  That  leaves out a good number of the instances I care about, until upgrades that haven't been scheduled yet.  Bogus!!!

But wait... I remember something.  Kinda.  When I first read about mitigating the 'ascending key problem' with trace flags 2389 and 2390, it kinda seemed like the writers were implying that SQL Server always kept track of the 'brand' for a stats key - whether or not it treated keys differently based on brand.

I remember testing trace flags 2389 & 2390.  When I used trace flag 2388 at the session level and called dbcc show_statistics for the stats, the brand was revealed.  I seem to remember that stats update date was there, too.  And inserts and deletes since update!  Hmmm...

Ok... I just confirmed that memory.  No fancy cut-n-pastes today, sorry.  Blogging from my phone. Left as an exercise for the reader I guess :-)

But, what if trace flags 2389 and 2390 aren't in place?  Hot dog!!

Trace flag 2388 reveals the brand in dbcc 'show_statistics' even without 2389 a& 2390!  But even more exciting... last stats update, inserts, deletes are there, too!

So inserts and deletes since last stats update can be my colmodctr proxy.... even if the instance is SQL Server 2008. R2 before SP2, or SQL Server 2012 SP1 and thus missing 'modification_counter' from Sys.dm_db_stats_properties.

I kinda like getting inserts and deletes separately anyway... since the resulting changes to underlying data may profile differently for deletes compared to inserts.

An astute reader may think: why bother when lots of interesting similar information can be grabbed from 

sys.dm_db_index_operational_stats?

Trouble is, most of the databases I care about have 20,000 or more indexes.  And they are busy databases.

That means the following passage disqualifies sys.dm_db_index_operational_stats as my colmodctr proxy, because I don't want holes in my model based on cache eviction:

"The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available."

sys.dm_db_index_operational_stats (Transact-SQL)

http://bit.ly/1qsFu8t

Nothing fancy today... just to let you know trace flag 699 is a bust

I'm involved in or at least near a lot of performance and scalability testing.  One interesting facet of that work is the large amount of work involved in setup. Sometimes multi-terabyte databases have to be converted between widely disparate systems before a performance or scalability benchmark can even be established.  Minimizing the setup time can buy back a few hours that can be well used for investigation, exploration or tinkering.

So, when I first sw rumors that trace flag 699 disabled transaction logging throughout the SQL Server instance, I was intrigued.

In a production instance, I'd stay far away from pushing that big red button.  I personally wouldn't plan to use such a trace flag in a dev/test or QA/validation environment. But in cases where we are simply throwing terabytes of data between database implementations, and the data itself has been securely backed up, the thought of speeding up the migration is very tempting.

So I tinkered a bit with trace flag 699.  I set it globally.  I set it as a startup parameter.  I messed around with a few other scenarios.

I didn't get too scientific... I was really just looking for a binary. Was there any transaction logging after activating trace flag 699?  

The answer is: yes, there was.  As I mentioned, I didn't do a thorough compare.  The steady stream of new log records displayed by fn_dblog was enough for me.

I haven't seen anyone write about trace flag 699 in years.  Hey - if it worked I probably wouldn't tell many folks... a small way to keep some folks from shooting themselves in the foot.

But the references I did see were so old that I began to think that maybe trace flag 699 hasn't done anything for years.

I think it was probably there in the Sybase code base.  And maybe in some Sybase documentation somewhere.  But most likely, trace flag 699 hasn't done anything for years.

If you're trying to 'speed load' a large database, rather than tinkering with trace flag 699, you'll be much better off learning how to make trace flag 610 work for you, by loading data into clustered indexes with all nonclustered indexes created later.