April 30, 2011 at 4:36 am
I am currently Confronting too much waits, which are hampering my DB performance. Most prominent waits are CXPACKET, LCK_X and LATCH_EX.
Further information is given below:
OS:Window Server 2003 Enterprize R 2 64 bit
RAM: 8GB
SQL Server: MS SQL Server 2008 R2 64-bit Enterprize
We are using dedicated SQL server.
For Further detail Image files from Activity Monitor are attached, Hopefully these will explain things well.
Thanks in advance.
Azhar
April 30, 2011 at 5:06 am
did you check these resources :
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 30, 2011 at 11:13 am
CXPACKET Waits are due to parallelism happening on the server.
What are your parallelism settings on the server ? Are you using Server level Parallelism or MAXDOP query hints ?
Parallelism causes excessive blocking and may even lead to deadlocks.
Thank You,
Best Regards,
SQLBuddy
May 1, 2011 at 2:04 am
your database files structure,how many file groups and files places on different physical drives or simple on different partition,tempdb database place,how many files in tempdb database
How much filfactor have you given at the time of indexes rebuild
Cost of threshold value ?
Max degree of parallelism ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 1, 2011 at 2:40 am
azhar.iqbal499 (4/30/2011)
I am currently Confronting too much waits, which are hampering my DB performance. Most prominent waits are CXPACKET, LCK_X and LATCH_EX.
Interesting collection...
CXPacket is a parallel skew wait. It comes from queries running in parallel and some threads finishing before others and having to wait for the others to catch up. That wait is a CXPacket.
Parallelism is NOT a bad thing in general.
Most likely cause here is inefficient queries. Maybe written badly, maybe missing indexes, but they can't run as well as they should and are paralleling inappropriately.
Two things that you should consider here.
1) Increase the Cost Threshold for Parallelism. 5 (the default) is insanely low. Maybe 15-20, but it really depends on what kind of queries that your system is running.
2) Find the queries that are subject to this wait the most and see if you can optimise them.
LCK_X is an exclusive lock wait. Again, could be from inefficient queries holding locks longer than they should, again, maybe consider optimising the queries involved (nolock won't help here, it's an exclusive lock)
Latch_Ex is an interesting one. It's a wait for a memory object. To even begin to help here, I think you're going to need to investigate further, at least find out what kind of latch.
Query sys.dm_os_latch_stats and see what kind of latch class has the most waits there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 2:41 am
Syed Jahanzaib Bin hassan (5/1/2011)
your database files structure,how many file groups and files places on different physical drives or simple on different partition,tempdb database place,how many files in tempdb database
None of those waits have anything to do with files, IO or tempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 2:38 pm
azhar.iqbal499 (4/30/2011)
OS:Window Server 2003 Enterprize R 2 64 bitRAM: 8GB
SQL Server: MS SQL Server 2008 R2 64-bit Enterprize
Azhar, I'm surprised noone brought this up before. Latches are primarily memory objects. Think of them like putting your thumb on a page for a moment so noone else can turn the page while you finish reading something real fast. They're supposed to be lightweight. You *can* generate a lot of these but you need a previously existing baseline to know if something's wrong because of them.
However, your system is 8 GB. Can you confirm what your server settings for SQL Server's memory usage are, and if there are other things running on the system? I won't say in your case this is a memory pressure issue, but I have seen that be an indicator at the beginning of such a problem, and I'd like to simply rule that out up front. Gail covered the CXPacket and LCK_X that I'd merely be repeating her if I tried to bring more to the table on that. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 1, 2011 at 3:20 pm
Craig Farrell (5/1/2011)
Azhar, I'm surprised noone brought this up before. Latches are primarily memory objects.
Err, I did... 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 4:04 pm
GilaMonster (5/1/2011)
Craig Farrell (5/1/2011)
Azhar, I'm surprised noone brought this up before. Latches are primarily memory objects.Err, I did... 😉
"Excuse me, waitress? I'll take my foot with ketchup please... and grilled onions."
Badly, badly phrased. I meant drilled into it further regarding memory swaps and the like. I have a feeling he's ending up in the swapfile some.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 1, 2011 at 4:16 pm
Craig Farrell (5/1/2011)
Badly, badly phrased. I meant drilled into it further regarding memory swaps and the like. I have a feeling he's ending up in the swapfile some.
Maybe, but I'd rather see the class of latch that the waits are for before going further.
It's not a pagelatch wait, so it's not related to the buffer pool, so not IO. High latch waits (as opposed to pagelatch or pageIOlatch) should be fairly rare.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 11:18 pm
GilaMonster (5/1/2011)
Craig Farrell (5/1/2011)
Badly, badly phrased. I meant drilled into it further regarding memory swaps and the like. I have a feeling he's ending up in the swapfile some.Maybe, but I'd rather see the class of latch that the waits are for before going further.
It's not a pagelatch wait, so it's not related to the buffer pool, so not IO. High latch waits (as opposed to pagelatch or pageIOlatch) should be fairly rare.
Agreed, thus my significant curiousity on it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 2, 2011 at 5:48 am
Thanks Gail for your valuable time,
We are using mix type of transaction both short and long procedures for reports. We don't have separate Server for Reporting right now. Should We use replicated DB(for reporting) of our current DB and set different Max DOP property of both DBs
Currently, we are using default parameters in advance section of Server level properties. AS per your suggestion we are also digging out procedures and inline queries in Application Code.
Please suggest values for MaxDOP and Cost Threshold for Parallaism,lock and query wait time. Any further Suggestion would be definitely helpful for us. Also image file of Advance section is attached, Pl do let me know any further information you need.
Thanks in advance.
Azhar
May 2, 2011 at 5:56 am
Thanks for your valuable time.
We are currently using all default values in "Advance TAB" and no query hint for paralalism is being used. We have currently have both short and long queries for reporting purpose.That is reason of hesitation to not change the Max DOP properties.Pl do let me know any further detail u need.
Thanks in advance.
Regards.
Azhar Iqbal
DBA
Lahore,Pakistan
May 2, 2011 at 6:07 am
Thanks Sir for your valuable time. We are using default values that you have asked. The attached image file will explain more. We are using two mdf, ldf files for both temp and live DB. MDF and LDF files of live DBs are located at D and F drive but both temp DBS files are located at C drive. Pl do let me know any further detail.
Thanks in advance.
Azhar Iqbal
DBA
Lahore
May 2, 2011 at 6:08 am
We use no fill factor hint when we build indexes.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply