New answers tagged performance-tuning
Folks, I was able to resolve the issue by updating the statistics . I believe it is related to outdated stats and so the query optimizer getting not so efficient execution plan. Thanks
You are getting soft faults and this is a non-issue. Soft faults are part of the normal page lifetime as the process working set gets continuously trimmed. If you also experience application problems then you need to investigate the cause. Read How to analyse SQL Server performance for a starting point.
If you have a lot of CXPACKET waits it is possible that reducing setting the MaxDOP is appropriate and will help. You should not change the cost threshold for parallelism setting assuming you have a NUMA machine (which you most likely do). MaxDOP: https://support.microsoft.com/en-us/kb/2806535 ...and I read somewhere that cost threshold needn't be changed ...
BlitzIndex will return some useful information on a non-production server as it is analyzing various things, some of which have nothing to do with what queries are running. It should be clear to you when viewing the output what is relevant and what is not. BlitzCache will not return useful information (or not much, at best) on a non-production server ...
Top 50 recent answers are included