August 28, 2003 at 11:28 am
How much memory does the Index Tuning Wizard need? I keep getting the error message that there is insufficient memory to tune the database no matter what I do. I've tried it on my machine with 500 MB of RAM and a 3000 MB Pagefile. I've even restored to booting into Safe Mode so that there will be maximum memory available.
I have tried this on the server itself which has 6 GB of memory and got the same error message.
Am I doing something wrong? What am I missing?
Thanks for your help.
September 1, 2003 at 12:00 pm
This was removed by the editor as SPAM
September 3, 2003 at 10:49 am
No one has run into a problem like this?
September 4, 2003 at 4:40 am
What version of SQL are you running and what SP level? I seem to recall something like this with SQL 7 but was SPed with a fix. Have you tried getting another sample as well to test the Index Wizard?
September 4, 2003 at 2:26 pm
Thanks for the reply. I am running SQL 7, SP 3. I have searched everywhere and can't find any info on this. Maybe I am just searching on the wrong thing. And yes, I have tried it on a couple of different workloads. Thanks again for the input.
September 4, 2003 at 4:18 pm
Ok I suggest taking a look at the memory on the server thru Task Manager while running and if using the wizard via your local machine do the same there to make sure whwre it is hitting the threshold. I believ some work is done on the local machine when running from other than the server and it may be the issue there. ALso see at what point it gives the error and how fast the memory climb is.
September 4, 2003 at 5:00 pm
Thanks. What I have done so far is run it on my machine which has 512 MB of memory and received the error. I then terminal served into the server itself which has 6 GB of memory and tried with the same results. On both I did not see that the memory was being taxed at all. I also played with the number of records in the workload to see if perhaps that had anything to do with it but no luck.
September 4, 2003 at 5:58 pm
Ok, you could try SP 4 but sounds like you are trying to avoid. However, I have looked around and the problem is taht there are some similar things in concept but not sure if issue. Can you post the exact wording of the error and have you checked the Windows Event logs to see if anything written there?
September 8, 2003 at 12:00 pm
Wasn't specifically holding off on SP4 for any reason. That server is hosted so I'll take a look at having that patch installed. In the meantime the exact message is: There is insufficient memory to perform index analysis. I just tried this again on my machine. I have about 366 MB of free memory when I run the Index Tuning Wizard. I am running this against 75 tables although at other times I have pared it down to about 20 with no effect. I changed the Advanced parameters to tell it to try and make a concatenated index with a max of 4 columns versus the default 16. I am also not performing and exhaustive analysis. Errors in the Event Viewer related to wiztrace.exe say "insufficent_memory".
When I run the trace the CPU ramps up a bit as would be expected. The memory doesn't budge. There is plenty avaialable and it's almost as if it isn't being used.
Thanks for your help. I'll see about SP4 but in the meantime if you hear anything please let me know.
September 10, 2003 at 12:25 pm
No answers yet. Just more frustration. I have whittled the trace file (which is a SQL Server table) down to just over 2000 rows with only the EventClass and Text fields. I look at the table and see all of the queries that I am hoping would be analyzed.
I installed SP4 for SQL Server and this did not help. I have also tried running the wizard on several different machines, databases and servers. And also tried to run it against only 1 small table with the same results.
Grrrrr!!!!!!!
September 11, 2003 at 2:58 pm
Well, I never did get this to work under SQL 7 on any machine or server. Same results no matter what I tried.
So, I installed a trial version of SQL Server 2000 on my machine, restored a copy of the database I was tuning to it and ran the Index Tuning Wizard. Worked just fine without a hitch.
And after all that the wizard could provide me with 0% improvement. Sheesh. All that for nothing.
Thanks for the help.
September 17, 2003 at 8:17 am
It didn't show any room for improvement? Did you try going with thurough and setting the number of statements to check up? Also you may want to make sure that the db you ran it against when it failed all those times does not have any statistics droppings.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply