October 31, 2014 at 12:31 pm
On my production box CXPACKET is High around 65% and server has been up for almost 3 months. My question is instead of running Profiler while query is (are) running from Application. What's the best way to find history of queries which ran parallel. If there any, should I run on Production during on hours?
One last thing, should I reboot Prod server once 3 months? I read different recommendations.
Thank you
October 31, 2014 at 12:50 pm
Jonathan Kehayias has a query in this post, http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/, that shows parallel queries in the cache. I think you can run it against production, but I'm cautious and would run it in off-hours, not when the server is at its busiest.
I don't know why you'd need to reboot the server every 3 months. The only reason I'd be rebooting is for patching (windows, sql, application) that requires a reboot. I think if you have to reboot for any other reason you have other problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2014 at 1:06 pm
Thanks Mr. Jack
October 31, 2014 at 2:46 pm
As a first try, look at that instance, "Reports", "Standard Reports", "Total CPU Time" and "Average CPU Time".
On rebooting, it depends. If you have a lot of non-SQL stuff running on the box, reboots can help, since RAM may get fragmented or lost (memory leak). Also, SQL itself at times must allocate contiguous RAM, and too much RAM fragmentation interferes with/can delay that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply