sql server 2008 sp2- slowness

Question

sql server 2008 sp2- slowness

wannabe1

SSCommitted

Points: 1960
More actions
September 3, 2011 at 4:58 pm

#256614

Hi
we applied sql server 2008 sp2 in our production.
But after we applied it ,we see slowness. we see writelog and log buffer wait types. we checked the disk subsystem, and it looks good. but the ms/write goes up over 300 ms when lot of writes come in and they get clogged. but when there aren't lot of writes, then the ms/write is below 10 ms
we didn't see these writes getting clogged up when we were running the 2008 RTM version.there were some writelog waits, but not as many as now. the end users are facing slowness and freezing
have any one seen behavior like this?
any advice will be of great help
thanks for the help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

The question is, what is causing it? What are your queries doing and what is your structure such that you're getting excessive writes? Could something have changed between RTM & SP2? Yes. But that's not the place to focus. The place to focus is your code and structure.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SQLforU SSC Veteran Points: 266 More actions · Answer 2

Microsoft has updated only following stuffs in SP2

SQL Server Utility,

Data-Tier Application (DAC),

as well as integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies

Try applying CUs gfor SP2 in ur DEV environment and measure the performance. Available CUs for SP2 are

CU1

CU2

CU3

CU4

CU5

SQLforU
info@sqlforu.com
For online training on SQL Server and Sybase, please contact
contact@sqlforu.com
www.sqlforu.com

Perry Whittle SSC Guru Points: 233792 More actions · Answer 3

what version were these databases originally, SQL 2008 or a previous version?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

so is your storage san, das or internal?

I can't think of anything offhand which might cause this from a SQL service pack, other than there may ( probably will) be changes to the optimiser which would affect queries but that shouldn't affect io issues on the storage.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

wannabe1 SSCommitted Points: 1960 More actions · Answer 5

colin.Leversuch-Roberts (9/5/2011)
so is your storage san, das or internal?
I can't think of anything offhand which might cause this from a SQL service pack, other than there may ( probably will) be changes to the optimiser which would affect queries but that shouldn't affect io issues on the storage.

it is SAN..

we suspect the SAN and have opened a call with EMC.

we also removed the SP just to confirm that it is not an issue with the SP.

thank you all for the help.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

What does your disk couters say

Avg Disk secs \Read

Avg Disk secs \Write

Current disk queue length ...

Also did you run a perfmon trace to find the possible resource bottleneck ?

Thank You,

Best Regards,

SQLBuddy