Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jasscat
Starting Member
9 Posts |
 Posted - 2009-11-05 : 14:55:12
|
| I am confused.Take a look at this link to Microsoft describing the Database Engine Tuning Advisor: [url]http://technet.microsoft.com/en-us/library/ms174215.aspx[/url]. Right under the heading "Improved Workload Parsing" it says: "Handles batches that reference transient tables such as temp tables." But a quick search on the web seems to indicate that lots of people have problems running DETA with temp tables. So what does this statement mean? Does it, or does it not, handle temporary tables?The problem, I think, is that by the time DETA does its thing, those temp tables are gone. Run a stored procedure containing a temp table and the temp table is deleted as soon as the stored procedure ends. Several posts say the way to get around this is to capture a trace in Sql Profiler using the tuning template, run your stored procedure, and then use the trace file as your workload in DETA. But I've never gotten that to work. The problem? The temp tables are long gone after I've saved the trace file. I've also saved a trace of database activity and run applications that use stored procedures with temp tables. Same problem. The trace contains the names of the temp tables, and the temp tables are gone.Put a stored procedure into a Sql Service Management Studio session, right click on the 'exec stored_procedure' statement, choose to analyze the statement in DETA, and it still doesn't work. DETA can't find the temp table. Take sql statements out of the stored procedure, put it in a SSMS session, click to analyze it in DETA, and it's still no go. I think this is because the temp table is only available to the SSMS session, but I could be wrong. About the only way I've gotten DETA to work is to take the sql statements from the stored procedure, paste it into a SSMS session, change the temp tables to permanent or do a "select into" a new table, then analyze the statement with DETA. This is a doable pain but it can be done. Of course, that doesn't help when you're analyzing a day's worth of activity in SQL Profiler because those temp table names are still in any stored procedure that contains them and runs that day. Oh, I've also tried using table variables. Table variables don't just exist in memory, they also exist on disk just like a temporary table. I believe their access scope is limited to the sql statement using them. So DETA doesn't recognize them.A lot of stored procedures at work use temporary tables. We're not going to replace temporary tables with permanent tables just so DETA can work. So - - what am I missing, and what are my other options? DETA works great when it works, but in my case, it often doesn't. |
|
|
jasscat
Starting Member
9 Posts |
Posted - 2009-11-06 : 11:19:13
|
| Ok, maybe I was wrong. It looks like DETA works with temporary tables. Only some of the temporary tables are being flagged with "Invalid Object Name." So now my question is: What is the best way to track down these "Invalid Object Name" errors? I can run the stored procedure I'm analyzing and I don't get any errors. When I try to analyze it in DETA, I get the "Invalid Object Name" error.I have noticed that sometimes the error isn't readily apparent. For instance, I may get "Invalid Object Name" for a temporary table, but the error could be something like using a reserved name for a column name (putting brackets around the name cleared up the error). Any ideas why I get these errors when I can parse the error and run the stored procedure in management studio? |
 |
|
|
jasscat
Starting Member
9 Posts |
Posted - 2009-11-10 : 10:25:30
|
| Finally - - I answered my own question after much searching. Sql Server's Database Engine Tuning Advisor can use temporary tables. What it can't do is use temporary tables created with a 'select into' statement. Take a look at this:[url]https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=349750[/url] |
|
|
|
|
|
|
|
|
Database Engine Tuning Advisor and temporary table