question

Hi All, We have regularly been hit by writelog wait with Buffer i/o wait in the last week. While doing my investigations I found that one of the table which i believe has the highest number of hits for read , update and inserts, had a seriously messed up primary key clustered index which 92% fragmented. I also found that the log was stupidly big and had 217 vlfs some 64 and some 128. Anyways, I have fixed the log so that its now got 2 vlfs and its come down from 1.5gbs to 9mb. We take log backups every 10mins. The only problem is that this issue is very intermittent and makes me wonder whether its more our new network with enterprise SSDs (very hard to blame but not impossible:)) or is it something that is in our engine. To create a baseline I took several baseline figures including disk activity etc. While doing that I also capture log flushes/sec. This was just over 80000 while things were running good. It was around 88000 in the afternoon while things were still good without any delay. I am wondering whether there is anything else I should be looking at. Please see screenshot attached. ![alt text][1] now to me free pages are really low and stolen ones are really getting my goat now.:) I have also looked at who is stealing my pages. Please see output below. type name stolen_pages OBJECTSTORE_LOCK_MANAGER Lock Manager : Node 0 59227 CACHESTORE_SQLCP SQL Plans 27452 CACHESTORE_OBJCP Object Plans 11547 CACHESTORE_PHDR Bound Trees 10525 USERSTORE_SCHEMAMGR SchemaMgr Store 1337 CACHESTORE_SYSTEMROWSET SystemRowsetStore 1022 MEMORYCLERK_SOSNODE SOS_Node 711 MEMORYCLERK_SQLSTORENG Default 656 OBJECTSTORE_SNI_PACKET SNIPacket 624 USERSTORE_DBMETADATA mssqlsystemresource 474 MEMORYCLERK_SQLGENERAL Default 423 MEMORYCLERK_SQLCONNECTIONPOOL Default 288 USERSTORE_TOKENPERM TokenAndPermUserStore 198 USERSTORE_DBMETADATA msdb 184 so I am just a bit puzzled as to what on earth is going on. Any help would be greatly appreciated. Its full of real experts here. Thanks in advance, Waqar [1]: /storage/temp/4624-capture.jpg