|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem tuning procedures with temporary tablesTuning Wizard or Display Estimated Execution Plan with stored procedures using temporary tables? For instance, running the statements below give the following error: "Server: Msg 208, Level 16, State 1, Procedure test, Line 1 Invalid object name '#test'." The statements: CREATE PROCEDURE test AS SELECT TOP 1 * INTO #test FROM sysobjects DELETE #test EXEC test Also, is this possible in SQL Server 2005? Cheers, Johan Sjöström MSc, MCAD Johan Sjöström wrote:
Show quote > I'm using SQL Server 2000. Is it somehow possible to use the Index No, you'll have to either use Table variables, or show the Actual > Tuning Wizard or Display Estimated Execution Plan with stored > procedures using temporary tables? > > For instance, running the statements below give the following error: > "Server: Msg 208, Level 16, State 1, Procedure test, Line 1 > Invalid object name '#test'." > > The statements: > > CREATE PROCEDURE test AS > SELECT TOP 1 * INTO #test FROM sysobjects > DELETE #test > > EXEC test > > > > Also, is this possible in SQL Server 2005? > > Cheers, > Johan Sjöström > MSc, MCAD > Execution Plan. OK. This makes the Index Tuning Advisor pretty useless when it comes to
using a Profiler Trace as source then. I was hoping that the 2005 DTA would support this.. Thanks anyway! /Johan Tracy McKibben skrev: Show quote > Johan Sjöström wrote: > > I'm using SQL Server 2000. Is it somehow possible to use the Index > > Tuning Wizard or Display Estimated Execution Plan with stored > > procedures using temporary tables? > > > > For instance, running the statements below give the following error: > > "Server: Msg 208, Level 16, State 1, Procedure test, Line 1 > > Invalid object name '#test'." > > > > The statements: > > > > CREATE PROCEDURE test AS > > SELECT TOP 1 * INTO #test FROM sysobjects > > DELETE #test > > > > EXEC test > > > > > > > > Also, is this possible in SQL Server 2005? > > > > Cheers, > > Johan Sjöström > > MSc, MCAD > > > > No, you'll have to either use Table variables, or show the Actual > Execution Plan. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Johan Sjöström wrote:
> OK. This makes the Index Tuning Advisor pretty useless when it comes to In 10+ years, I've not once used the Index Tuning > using a Profiler Trace as source then. I was hoping that the 2005 DTA > would support this.. > Wizard/Advisor/whatever it's called today. You can achieve the same thing by analyzing the trace logs, identifying the heavy queries, and then tuning the worst one by looking at the execution plan. Right, Index Tuning Wizard in 2000, Database Tuning Advisor in 2005.
Well, I agree, but when reading Microsoft Press books, they always celebrate the Tuning Wizards/Advisors. Surely, they sound cool, but my practical experience is that they don't really measure up in real-life. Using a profiler trace doesn't work because of temp tables in procedures, and I get the impression that the wizard in 2000 always recommend the indexes that already exists, never new ones. I'm going to install 2005 and evaluate the DTA. I'm currently preparing for the 70-431 exam (MCTS) so I hope the DTA delivers on some of the hype! /Johan Tracy McKibben skrev: Show quote > Johan Sjöström wrote: > > OK. This makes the Index Tuning Advisor pretty useless when it comes to > > using a Profiler Trace as source then. I was hoping that the 2005 DTA > > would support this.. > > > > In 10+ years, I've not once used the Index Tuning > Wizard/Advisor/whatever it's called today. You can achieve the same > thing by analyzing the trace logs, identifying the heavy queries, and > then tuning the worst one by looking at the execution plan. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Johan Sjöström wrote:
Show quote > Right, Index Tuning Wizard in 2000, Database Tuning Advisor in 2005. Microsoft wants people using those wizards, because they can trumpet > > Well, I agree, but when reading Microsoft Press books, they always > celebrate the Tuning Wizards/Advisors. Surely, they sound cool, but my > practical experience is that they don't really measure up in real-life. > Using a profiler trace doesn't work because of temp tables in > procedures, and I get the impression that the wizard in 2000 always > recommend the indexes that already exists, never new ones. > > I'm going to install 2005 and evaluate the DTA. I'm currently preparing > for the 70-431 exam (MCTS) so I hope the DTA delivers on some of the > hype! > > /Johan > about "ease of use", trying to make SQL Server work like Excel. There is almost ALWAYS a better, faster, more flexible way to do anything they try to do with a wizard. Unfortunately, you have to know the "Microsoft" answers to those exam questions, not necessarily the "right" answers.  |
|||||||||||||||||||||||
Other interesting topics