3

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.

Community
  • 1
tarte-au-sucre
  • 31
  • 3

3 Answers3

1

Here is your answer:

MSDB on C:
86% of 32GB
io_stall_write_ms : 192217

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

Spörri
  • 4,734
  • 15
  • 28
0

Check the latency between your PC and the domain controller after doing ipconfig /dnsflush.
Are you connecting to server by IP or hostname ? If IP, it could be doing a reverse-DNS, so add a hosts file entry.
Try switching Network Protocol between TCP or Named Pipes in SSMS Connect to Server dialog - Options - Connection Properties.

d-_-b
  • 1,184
  • 1
  • 12
  • 25
0

Have you looked into if there is a login trigger? You can query sys.server_triggers to see if there any server level triggers, but you will need to look inside each one to determine what it's doing.

Any other auditing going on with the server?

Jonathan Fite
  • 9,414
  • 1
  • 25
  • 30