<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
SQL Server Performance Problems
21 posts, Page 1 of 3
1
2
3
»
»»
SQL Server Performance Problems
Rate Topic
Display Mode
Topic Options
Author
Message
kiffab
kiffab
Posted Tuesday, December 18, 2012 10:34 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:18 AM
Points: 8,
Visits: 46
Hi Guys
Occasionally at peak times of the day I get I/O errors on my SQL server (v 2005, 16 core 2.4 ghz, 58 GB ram).
These errors read "SQL server has encountered x occurences of i/o requests taking longer than 15 seconds to complete on ....". The drive is always the data drive but multiple database are affected.
Various indexes and tweaks were put in place over the last year to stabilise things but it's starting to show signs of performance problems again. I've made all the usual changes like splitting data/logs, temp db on own drive, etc. The biggest DB is around 1 TB.
I have installed SQL server 2005 performance dashboard.
What's the best way to use it?
For expensive queries, what am I most interested in from CPU, duration, logical reads, physical reads, logical writes, CLR time? I can feed this back to suppliers and have them tweak their SQL if necessary. I also see various indexes that it suggests I should add.
What else can I check?
Thanks.
Post #1397920
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, December 18, 2012 10:56 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 12:07 PM
Points: 31,431,
Visits: 13,741
This is really an I/O subsystem issue, and less a query tuning issue. The load from your server exceeds what the I/O system can handle.
http://mssqlwiki.com/2012/08/27/io-requests-taking-longer-than-15-seconds-to-complete-on-file/
Tuning queries to use less reads can help, and indexing better might help, but this is a bit of an art. Really you need more/faster disks in the short term.
In looking at queries, I'd start with a workload trace, then feed that to the DTA and get an idea of what indexes it might recommend.
You can also take your trace and look for those queries with lots of reads in them (or writes) and see if there is something you can do to reduce the reads. Indexing helps here, but potentially rewriting SQL to work on smaller sets of data can help.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1397933
kiffab
kiffab
Posted Tuesday, December 18, 2012 11:12 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:18 AM
Points: 8,
Visits: 46
Hi Steve
Thanks for the prompt response. I will read through the article attached.
Given that I see nothing but expansion in the future, it sounds like I can tweak queries and index as much as I like but eventually this problem will surface again. In fact I'd say it is now as this problem had disappeared for a period of time as I changed the block write size to 64k from 8 or whatever the default was.
The cluster nodes appear to have plenty of free CPU and mem but I guess that doesn't rule out the SAN.
I need more evidence in order to request better kit so I will follow the steps in the link and may come back with a question or 2!
Thanks.
Post #1397943
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, December 18, 2012 11:29 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 12:07 PM
Points: 31,431,
Visits: 13,741
One thing you might examine is your waits for the various files. If you see waits stacking up, then you have IO issues.
A few more references for you:
http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/
http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/
http://www.sqlskills.com/blogs/paul/post/capturing-wait-stats-for-a-single-operation.aspx
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1397957
kiffab
kiffab
Posted Wednesday, December 19, 2012 4:37 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:18 AM
Points: 8,
Visits: 46
Regarding the waits, SQL server performance dashboard shows below (taken at 11:20 today)
Wait Category..................Number of Waits..........Wait Time (sec)..........% Wait Time
Parallelism...........................724065872.................6319246.421............... 28.06%
Other ................................1232197629................5075559.493...............22.54%
Sleep.................................425224734..................4091093.732...............18.17%
Latch................................1642991523.................3378139.014...............15.00%
Buffer IO............................148173427...................2212876.593..............9.83%
I am not sure whether that seems good bad or normal! Apologies for the dots....
Post #1398302
kiffab
kiffab
Posted Wednesday, December 19, 2012 9:59 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:18 AM
Points: 8,
Visits: 46
The top wait on live always seems to be this:
wait_type wait_time_ms
MSQL_XP 26126343
I believe this is due to SQL waiting on extended stored procs to complete yet I can't find a process relating to this. I use RedGate for SQL backup and the general feedback online is that an update from v5 is required.
Post #1398523
Jon.Morisi
Jon.Morisi
Posted Wednesday, December 19, 2012 11:39 AM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275,
Visits: 679
It looks like you have got a lot of things going on performance wise. CXPACKET waits (parallelism) should be addressed if > 5%. The IO waiting more that 15 sec indicates an IO subsystem bottleneck and the IO related waits seem to support this. However, that doesn't necessarily mean you need a faster IO subsystem, it could be a number of things including bad indexing, bad maintenance, or a big ad hoc query that needs tuning. Have you run any Performance Monitor Counter logs?
If I were in your shoes I'd start with addressing the CXPACKET waits by modifying Cost threshold for parallelism and Max Degree of Parallelism because that's a relatively easy one. Next I'd check index fragmentation and the last time statistics were updated. If there's no routine maintenance occurring, put it in place.
Next I'd run a PAL analysis, and go from there:
http://pal.codeplex.com/
HTH,
Jon
Post #1398588
kiffab
kiffab
Posted Wednesday, December 19, 2012 12:40 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 31, 2013 2:18 AM
Points: 8,
Visits: 46
Thanks Jon. I'll read up on this.
FYI -
max degree of parallelism = 0
Locks = 0
Cost = 5
Query waits = -1
I'm guessing this is the out the box setup as I've never changed it. Does this ring alarm bells or seem perfectly reasonable?
I have 16 processors @ 2.4 GHZ. 58 GB memory with 48 GB allocated to SQL.
Cheers
Post #1398623
GilaMonster
GilaMonster
Posted Wednesday, December 19, 2012 12:51 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 37,729,
Visits: 29,991
Jon.Morisi (12/19/2012)
If I were in your shoes I'd start with addressing the CXPACKET waits by modifying Cost threshold for parallelism and Max Degree of Parallelism because that's a relatively easy one.
Fixing excessive CXPacket waits isn't as simple as that (sure, maxdop 1 will make them go away entirely, but...). Requires investigating whether the CX packet waits are a problem (any time a query runs in parallel, you will get CXPacket waits), what, if anything, are the threads that aren't waiting on CXPacket waiting for, whether there's data skew, etc
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1398636
Adam Machanic
Adam Machanic
Posted Wednesday, December 19, 2012 1:01 PM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:20 PM
Points: 1,137,
Visits: 667
Jon.Morisi (12/19/2012)
CXPACKET waits (parallelism) should be addressed if > 5%.
That's a rather specific pronouncement, especially when you don't know anything about the workload in question. What's your logic there?
--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #1398640
« Prev Topic
|
Next Topic »
21 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
