November 12, 2012 at 10:39 am
Hello,
I am trying to look into a query thats taking 5 mins to execute on our production. Its an LinQ generated 6000 Line SQL query that the dev team is trying to put into production without any testing. They want me to add indexes to the database( brand new) so that the query will run fast. The execution plan really looks weird. So Just as a starter I tried to tune it using the DTA and I am getting the below error. I am using a .sql query file as the input.
The minimum storage space required for the selected physical design structures exceeds the default storage space selected by Database Engine Tuning Advisor. Either keep fewer physical design structures, or increase the default storage space to be larger than at least 441 MB.Use one of the following methods to increase storage space: (1) If you are using the graphical user interface, enter the required value for Define max. space for recommendations (MB) in the Advanced Options of the Tuning Options tabbed page; (2) If you are using dta.exe, specify the maximum space value for the -B argument; (3) If you are using an XML input file, specify the maximum space value for the <StorageBoundInMB> element under <TuningOptions>
My question is what value is the maximum value that I should consider adding per recommendation (2) in the above error.
Thanks for your input
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
July 23, 2015 at 12:22 pm
Before click 'Start Analysis', Do
1. Click 'Advanced Options...' button in 'Tuning Options' TAB
2. Check 'Define max. space for recommendations (MB):' and Put '10000' into the textBox // Suppose the machine you are running DTA has 10GB memory, so 10000 MB is about 10 GB. So, Put '20000' if you machine has 20GB memory
3. Click 'OK'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply