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
Best Answer
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.