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.

  • You can find a decent list of wait type descriptions here. – user507 Aug 22 '14 at 17:27
  • Swap file should never be used by SQL Server. You need to lower the maximum memory setting (or establish one if it's set to the default, which is unlimited). Fix this (reboot the box after changing it), and if that doesn't solve the problem, let us know. – Jon Seigel Aug 22 '14 at 17:39
  • Hi ! Maximum Memory in server is 4608 MB – tarte-au-sucre Aug 25 '14 at 13:52
  • It's probably set too high; try lowering it. See my blog post here for more info: voluntarydba.com/post/2013/03/19/… – Jon Seigel Aug 28 '14 at 17:26
  • 1
    What protocol do you connect? Sounds like is trying namedpipes first, timing out and then trying TCP. Try forcing tcp via connection string: tcp:<servername>[\<instancename>],<port>. – Remus Rusanu Dec 29 '14 at 9:08
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

|improve this answer|||||
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?

|improve this answer|||||
  • Hi Jonathan, Thanks for your answer. Server Memory is now up to 12GB. sys.server_triggers returns empty results. – tarte-au-sucre Sep 17 '14 at 9:56
  • Well, to sum up : 20sec for authentication, during 3seconds once a week. No link with high traffic, high activity. No problem with IO bottleneck. – tarte-au-sucre Sep 17 '14 at 10:07
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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.