2

I have a 18,6 GB SQL Server 2008 R2 profiler trace file. Trace file recorded at tuning profile. When I submit this trace file to database engine tuning advisor I get this error:

Error: Can not get minimal database information in allotted time

How can I solve this ?

  • Windows Server 2008 R2, SQL Server 2008 R2
  • 24 GB ram , 16 cores server
Improve this question
8
  • correct. how can i move ?
    – Furkan Gözükara
    Commented Oct 12, 2011 at 12:44
  • @MonsterMMORPG Wait for a moderator...
    – Richard
    Commented Oct 12, 2011 at 12:46
  • 1
    Or just copy and paste into a new question there then delete this one.
    – Martin Smith
    Commented Oct 12, 2011 at 12:51
  • @MonsterMMORPG: A file created with the tuning template should be good for the DTA. Can you open the file in Profiler or in Management Studio (using system function fn_trace_gettable) without any error?
    – Marian
    Commented Oct 12, 2011 at 15:01
  • 2
    That is a pretty large trace file... I imagine you'll have a hard time ingesting that even if you axed the time to analyze limit. I would consider rolling your trace files over more and trying a smaller time/workload file. When you say it is opening in Profiler, do you mean it is opening AND all read? When you first open in profiler, it is still reading. Can you scroll to the end? My guess here is you need smaller files.
    – Mike Walsh
    Commented Oct 22, 2011 at 14:25

1 Answer 1

Reset to default
2

From the comments I see you removed the default time limit like I mentioned but still the analysis didn't happen in enough time or ever complete as I feared.

18GB is definitely the largest trace I have ever heard of attempting to be analyzed by the Database Engine Tuning Advisor. I suggest you break your trc file up into smaller sizes. Either by looking at the data a bit more filtered or for a smaller window of time while still maintaining enough of a workload to capture both data modification and selects.

Alternatively -

I haven't been a big fan of the DETA and sometimes the results, even with a properly sized workload file, fly in the face of reality (most index suggestions are very wide covering index in a lot of cases, redundant indexes in overlapping column coverage at times, etc.) I would suggest a "Top 10" style tuning exercise may be a better approach. Find the worst performing queries by duration and/or reads and work on tuning them through query tuning and index tuning/design best practices.

Edited: Added a couple more tips based on comment from OP

I would also look to set an end time. DETA isn't supposed to be a "done in 5 minutes" deal. It is trying to be incredibly thorough and look at your database as deeply as it can and analyze the workload as long as it can to come up with the recommendations. Have you tried setting an end time for the analysis?

I'd check out this link on MSDN for some other tips - https://blogs.technet.com/themes/blogs/generic/post.aspx?WeblogApp=sql_server_isv&y=2011&m=04&d=08&WeblogPostName=fundamentals-running-database-engine-tuning-advisor-and-selecting-indexes&GroupKeys=

Bottom line though - This will be a slower operation. The larger the workload file and more complex the database, the slower it will be. If you are running on the same system that other work is going on then you are each competing for resources.

Improve this answer
6
  • hello. i could not find any way to split trace file. after i captured 2 gb trace file (which takes only several hours to capture at my website) and analyzed it. the analyze took more than 2 days to complete on my monster computer. DETA is very very slow.
    – Furkan Gözükara
    Commented Nov 3, 2011 at 14:33
  • Are you doing your analysis on the server and SSMS on it? You are competing for memory and resources with the server when doing that then. DETA is a bit slower and I don't always like its results so I rarely use it and can't offer more than the smaller trace file approach or use SSMS someplace other than the actual server and connect to a separate instance than your prod instance, if not already Competing for load otherwise.
    – Mike Walsh
    Commented Nov 3, 2011 at 14:47
  • I added another point to my answer based on your comment above. Try and set the end time and see what happens. Read that article. HTH
    – Mike Walsh
    Commented Nov 3, 2011 at 14:52
  • i did not analyze it on real server. i downloaded my database backup restored and used that on my pc. so the sql server was totally free. worst of all it uses only 1 core while i have 8 cores on my pc. this is totally lame :D
    – Furkan Gözükara
    Commented Nov 3, 2011 at 15:04
  • but i chose same db for both workload analysis and tune. the image at your link shows master as workload analysis . is it the proper way ?
    – Furkan Gözükara
    Commented Nov 3, 2011 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.