I am asking you after days of research but I think I don't have required skills to understand what is happening.
I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the linked application. Sometimes up to 20s for a simple SQL auth (not windows or kerberos or AD)
6GB of RAM, W 2008 R2, virtualized on VMWare.
There is a separate drive for TempDB and for the Main DB, both on a fast SAN behind fiber channel.
Queries (once authentication is ok) are quite fast (few micro seconds up to 10 s, average around 15ms).
I have the following data :
MSDB on C:
86% of 32GB
io_stall_read_ms : 2340428
num_of_reads : 45062
avg_read_stall_ms : 51.9
io_stall_write_ms : 192217
num_of_writes : 34392
avg_write_stall_ms : 5.6
io_stalls : 2532645
total_io : 79454
avg_io_stall_ms : 31.9
Main DB : on E:
94 % of 250GB
io_stall_read_ms : 556848206
num_of_reads : 46555392
avg_read_stall_ms : 12.0
io_stall_write_ms : 2213187501
num_of_writes : 62730741
avg_write_stall_ms : 35.3
io_stalls : 2770035707
total_io : 109286133
avg_io_stall_ms : 25.3
Master on C:,
io_stall_read_ms : 11966
num_of_reads : 2229
avg_read_stall_ms : 5.4
io_stall_write_ms : 240
num_of_writes : 104
avg_write_stall_ms : 2.3
io_stalls : 12206
total_io : 2333
avg_io_stall_ms : 5.2
Well, I don't know which column to look at, but C looks like a slow drive isn't it ?
WAITS are the following :
Oledb 28 %
Pageiolatch Ex 18 %
Writelog 13 %
Pageiolatch Sh 10 %
Cxpacket 8 %
Sos Scheduler Yield 5 %
Sleep Bpool Flush 4 %
Backupbuffer 1 %
Io Completion 1 %
Preemptive Os Authenticationops 1 %
Preemptive Os Waitforsingleobject 0 %
Backupio 0 %
Async Io Completion 0 %
My understanding is that Pageiolatch and Writelog indicates a slowness around IO completion , is that right ?
IO over C drive is quite calm : 1% of data Read / Write over 24hours, no peaks.
1 or 2 Read / Write per second, of a few KB.
Only Main Db (E:) is heavily used : 100 Reads / sec, 130 writes / sec.
Memory usage : 100 % of 6GB, swap file of 6GB full . (from what I've read, it is perfectly normal).
Well, What do you think slowness over authentication can come from then ?
Thanks a lot for your help,
S.
Best Answer
Here is your answer:
If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds!
First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away
Check if the master database has some free space - Usually you dont need to tune it as very little is written to the master database, unless if someone has created objects in it
Then make space in the MSDB database is way to big which can cause a lot of troubles, please remove as much backup and job history from it database as you can.
You can do all these checks very simply by downloading and running sp_blitz