Top 5 wait_stats question
I have a client that just gave me access to a new polyserve SQL environment. They have placed one production db on the server for testing after they completed setup. We gave them our requirements except for the polyserve but that was because we are not familar with the app so cannot vote for or against.
The are very secuirty aware so right now will not give access to the actual hardware so I cannot run PERFMON but was able to run a query and gather the top 5 wait stats since server startup. (4 weeks)
The SQL instance has seperate drives for data, log and tempdb 16 3ghz proc and 64GB of RAM. The OS and SQL are x64 with SQL being enterprise. With only one db on this instance the two LATCH waits concern me. What dio you guys think. I am attaching the stats and config results.
config.rpt
The are very secuirty aware so right now will not give access to the actual hardware so I cannot run PERFMON but was able to run a query and gather the top 5 wait stats since server startup. (4 weeks)
The SQL instance has seperate drives for data, log and tempdb 16 3ghz proc and 64GB of RAM. The OS and SQL are x64 with SQL being enterprise. With only one db on this instance the two LATCH waits concern me. What dio you guys think. I am attaching the stats and config results.
config.rpt
ASKER
I have reviewed those articles (beleieve me I have read Penal Dave complete FEB. project on wait stats! )and am aware of a few queries we have that I am going to have to add the MAXDOP hint. I am testing the new settings now and am seeing improvement in the CXPACKET wait. Right now I am more concerned about the LATCH_EX and PAGIOLATCH_SH. This instance basically has no load with only one db in use. Should I be concerned with these? If we have disk subsystem issues I definitily do not want to start rolling production dbs into the new polyserve environment.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I agree it is not top two but is in top 5 which is not good. I am accepting this as the solution but any additional comments are welcome. I do want to say the article by Penal Dave on sqlauthority.com is a great source of info but we need all the info we can get since it is the same setup but different results depending on so mamy variables with SQL performance.
Thanks,
Robert
Thanks,
Robert
Daniel PL is correct
You will always have waits in a SQL server. You cannot hava a empty top 5 table!
Latches will Always be set, this is a quite normal top 4, given that you take care of the CXPackets.
Regards Martenrune
You will always have waits in a SQL server. You cannot hava a empty top 5 table!
Latches will Always be set, this is a quite normal top 4, given that you take care of the CXPackets.
Regards Martenrune
Read following articles:
Pinal Dave - SQL SERVER – CXPACKET – Parallelism – Usual Solution
Pinal Dave - SQL SERVER – CXPACKET – Parallelism – Advanced Solution
Pinal Dave - SQL SERVER – Wait Stats – Wait Types – Wait Queues
Microsoft white paper - Wait Types, Perfmon Counters, and Correlations
Wait Stats Introductory References