SQL Optimizer for SQL Server Forum - Toad for SQL Server - Toad World
Danny Torres
Follow / 1 Nov 2016 at 1:59pm / Toad for SQL Server

We are now on Twitter!!! Contact a Technical Support expert today...

Expand content
Do you have a quick support question and short of time? Contact us Via Twitter @ QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

Do you have a quick support question and short of time? Contact us Via Twitter  @QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

432 0 / Create an account to join the discussion.
Mark Freeman
Follow / 19 Nov 2015 at 10:43pm / Toad for SQL Server
Latest post by on 23 Nov 2015 at 4:01pm

How can I get SQL Optimizer to use snapshot isolation

Expand content
Because of dramatic differences between my test and production environments (amount of RAM and number of cores), I want to optimize a SELECT statement by connecting to my production server. But to avoid...

Because of dramatic differences between my test and production environments (amount of RAM and number of cores), I want to optimize a SELECT statement by connecting to my production server. But to avoid causing blocking, I want SQL Optimizer to do all its work in snapshot isolation. I have the database set to allow it, but that is not the default.

SQL Optimizer won't let me precede the SELECT statement with "SET TRANSACTION ISOLATION LEVEL SNAPSHOT;".  How can I configure SQL Optimizer to use snapshot isolation when executing my very long running SELECT the many dozens of times that it will need to test the optimization permutations?

3427 2 / Create an account to join the discussion.
nbarnard
Follow / 6 Oct 2015 at 3:26pm / Toad for SQL Server
Latest post by on 7 Oct 2015 at 10:32pm

Database, Date Collected, Time collected are grayed out on Optimize...

Expand content
Hello, I am trying to optimize indexes on an instance. All of the options for the selected server are grayed out. This is version 9.5. I am trying to pull from the SpotlightStatisticsRepository. I...

Hello,

I am trying to optimize indexes on an instance. All of the options for the selected server are grayed out. This is version 9.5. I am trying to pull from the SpotlightStatisticsRepository. I am an admin on both servers. The instance is actually a virtual instance for a cluster. However, I change to other servers that are not and the say results occur.  An error shows that says "There isn't date collected in current select criteria." All the other options are grayed out though.  Any thoughts?

Unanswered 3507 2 / Create an account to join the discussion.
bpcatt1
Follow / 8 Jun 2015 at 8:48pm / Toad for SQL Server
Latest post by on 15 Jun 2015 at 1:10am

SQL Optimizer cannot connect to any server??

Expand content
Hello all, I have tried to connect to four different servers now with no joy from SQL Optimizer for SQL Server 10.0.1 Trial Edition (I am attempting to evaluate the software, but it isn't looking...

Hello all, 

I have tried to connect to four different servers now with no joy from SQL Optimizer for SQL Server 10.0.1 Trial Edition (I am attempting to evaluate the software, but it isn't looking good at this point).  I can connect to the servers via SSMS and Toad.  SQL Optimizer display an error window with no text whenever I try to log in using my Windows credentials and Windows authentication as well as both sets of administrator credentials via SQL authentication.  

Has anyone ever experienced this issue before and have  solution?  I searched the forums here and on Dell's website to no avail.

6970 4 / Create an account to join the discussion.
Mark Freeman
Follow / 2 Apr 2015 at 6:00pm / Toad for SQL Server
Latest post by on 3 Apr 2015 at 4:58am

Cursor and Parameter Settings tab in the Optimize SQL window (SQL...

Expand content
I have copied a SELECT statement from a stored procedure into the Optimize SQL window. I now want to set the values for the parameters (bind variables in Oracle lingo). I can't find the tab that is...

I have copied a SELECT statement from a stored procedure into the Optimize SQL window. I now want to set the values for the parameters (bind variables in Oracle lingo). I can't find the tab that is described in the Help. Of course, no screen image is included in the help. Another area of the help says to just click on the auto optimize icon, implying that it will then prompt for the values, but that doesn't happen. The status goes to "Checking bind variables" and after an hour, it is still in the same status.

How can I enter values for the parameters needed by my SELECT statement?

10929 1 / Create an account to join the discussion.
EDavies
Follow / 20 Mar 2015 at 11:55am / Toad for SQL Server
Latest post by on 20 Mar 2015 at 5:32pm

Loop around a temp table and insert into main table

Expand content
I have a temp table with 4 columns, serial number,date , number of defects,inspector. From that row I would like to insert into the main table x row where X is the number of defects. e.g Temp Table...

I have a temp table with 4 columns, serial number,date , number of defects,inspector. From that row I would like to insert into the main table x row where X is the number of defects.

 

e.g Temp Table

Serial Number, Date, Number of defects, Inspector

AAA, 20/03/2015,4, ED

AAB, 20/03/2015,3,JOE

e.g MainTable

Serial Number, Date, Inspector

AAA, 20/03/2015, ED

AAA, 20/03/2015, ED

AAA, 20/03/2015, ED

AAA, 20/03/2015, ED

AAB, 20/03/2015,3,JOE

AAB, 20/03/2015,3,JOE

AAB, 20/03/2015,3,JOE

Any ideas??

Unanswered 11503 1 / Create an account to join the discussion.
tbrown10
Follow / 17 Jun 2014 at 9:29pm / Toad for SQL Server
Latest post by on 18 Jun 2014 at 10:19am

Optimze SQL link always greyed out in Spotlight For SQL Server

Expand content
When Using spotlight for SQL Server I go to the SQL activity page and no matter which session I click on the Optimize SQL Link/Button is always greyed out so I can not click on it. Any ideas?

When Using spotlight for SQL Server I go to the SQL activity page and no matter which session I click on the Optimize SQL Link/Button is always greyed out so I can not click on it.  Any ideas?

Unanswered 12747 1 / Create an account to join the discussion.
ian.rankin
Follow / 14 May 2014 at 9:58am / Toad for SQL Server
Latest post by on 15 May 2014 at 8:58am

Orphaned Virtual Indexes

Expand content
Hi, Has any one heard of and know a fix for SQL Optimizer leaving behind a phantom index definition in the database. I have been using the tool to tune for a few months now (against copy production databases...

Hi, Has any one heard of and know a fix for SQL Optimizer leaving behind a phantom index definition in the database. I have been using the tool to tune for a few months now (against copy production databases). We have found that on occasion when the tuning activity has suggested that a new index be created (as far as I understand a 'virtual' index is created) on leaving the tool there is still a 'ghost' of that index in the database. As far as we can determine it consists of a record in the dba_objects view, but cannot be seen in the dba_views view ! I have not yet been able to get this to occure on our development databases. Any ideas though much appreciated.

13104 3 / Create an account to join the discussion.
Kelvin Lai
Follow / 2 Nov 2012 at 9:46am / Toad for SQL Server
Latest post by on 10 Dec 2013 at 10:54am

Quest SQL Optimzier for SQL Server Version 8.5.0 has been released...

Expand content
Hi Everyone, SQL Optimizer for SQL Server version 8.5.0 is now available. In this release, we add a brand new module, Optimize Indexes. It analyzes a SQL workload or any group of SQL statements and determines...

Hi Everyone,


SQL Optimizer for SQL Server version 8.5.0 is now available.


In this release, we add a brand new module, Optimize Indexes. It analyzes a SQL workload or any group of SQL statements and determines the best index set for the workload or group of statements.  Some of the user interface also enhanced and has a better support for SQL Server 2012.


- Previous versions have supported optimizing only one SQL statement at a time. You can now input multiple SQL statements for optimization.
- Optimizer now able to automatically detects bind variable data type and it's value as well as undefined tempoaray tables referenced in a SQL statement. 
- When generating indexes for a SQL, the engine now considers all the tables referenced inside a view.

We are looking forward to hearing your feedback. Please feel free to download our trial version and have a try.

Thanks,
Kelvin

13229 1 / Create an account to join the discussion.
Kelvin Lai
Follow / 17 Jun 2013 at 3:46am / Toad for SQL Server
Latest post by on 10 Dec 2013 at 8:16am

Quest SQL Optimzier for SQL Server Version 9.0.0 Released

Expand content
Hi Everyone, SQL Optimizer for SQL Server version 9.0.0 is now available. In this release, we add a brand new module, Find SQL. It helps you locate the most resource-intensive SQL in your server by...

Hi Everyone,

SQL Optimizer for SQL Server version 9.0.0 is now available.

In this release, we add a brand new module, Find SQL. It helps you locate the most resource-intensive SQL in your server by extracting from SQL Server Plan Cache. It evaluates existing SQL performance by different statistics (such as CPU Time and Elapsed Time) from different areas (such as Top N SQL statements, Top N SQL Batches and Top N Database Objects). Different queries extraction sources will be added in the future releases. So please stay tuned Smile

Microsoft has introduced the Contained Database (CDB) feature in SQL Server 2012. To support this new feature, we have enhanced our connection manager to support the Contained Database login. The Optimization engine has also been upgraded so that SQL Optimizer recognizes and handles the new CATALOG_DEFAULT collation.

We are looking forward to hearing your feedback. Please feel free to download our trial version and let us know you think about this new release.

Thanks,

Kelvin

14791 1 / Create an account to join the discussion.
budi.yulianto
Follow / 12 Jul 2013 at 7:10pm / Toad for SQL Server
Latest post by on 22 Jul 2013 at 10:31am

Quest SQL Optimzier for SQL Server Ver.9.0 issue

Expand content
After I installed Quest SQL Optimzier for SQL Server Ver.9.0 issue, I get this message whenever I open SSMS - "TypeLoadException: Could not load type 'Microsoft.SqlServer.Management.SqlStudio...

After I installed Quest SQL Optimzier for SQL Server Ver.9.0 issue, I get this message whenever I open SSMS  - "TypeLoadException: Could not load type 'Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService'...".

Has anyone had the same issue like this?

Could you share with me how to fix it?

Thanks!

Answered 15978 8 / Create an account to join the discussion.
dave.blohm
Follow / 12 Jun 2013 at 4:59pm / Toad for SQL Server
Latest post by on 12 Jun 2013 at 5:18pm

Probably the most basic question of all...

Expand content
Since a conversation with a DBA friend of mine, I've been beating myself up trying to find where I get my hands on Query Optimizer for SQL Server. Where does one obtain the software? Thanks in advance...

Since a conversation with a DBA friend of mine, I've been beating myself up trying to find where I get my hands on Query Optimizer for SQL Server. Where does one obtain the software?

Thanks in advance.

Answered 13245 1 / Create an account to join the discussion.
Levin Peng
Follow / 24 May 2012 at 10:47am / Toad for SQL Server
Latest post by on 16 Nov 2012 at 9:45am

New Rules for Quest® SQL Optimizer for SQL Server 8.0

Expand content
http://toadworld.com/Blogs/tabid/67/EntryId/900/New-Rules-for-Quest%c2%ae-SQL-Optimizer-for-SQL-Server-8-0.aspx
15482 2 / Create an account to join the discussion.
john_093
Follow / 19 Dec 2011 at 4:45pm / Toad for SQL Server
Latest post by on 14 Sep 2012 at 4:43am

Default Schema not available

Expand content
Whenever I try to optimize a statement, I get the error: Default Schema not available. What causes this and how do I resolve it?
Whenever I try to optimize a statement, I get the error: Default Schema not available. 

What causes this and how do I resolve it?
23924 3 / Create an account to join the discussion.
kdooley_984
Follow / 31 Aug 2012 at 9:10pm / Toad for SQL Server
Latest post by on 3 Sep 2012 at 6:06am

Using Temp Table Variables

Expand content
Hi I am trying to optimize some procs that use temp table variables. It is very easy I see to use regular temp tables but I cannot get it to work with temp table variables.

Hi I am trying to optimize some procs that use temp table variables.  It is very easy I see to use regular temp tables but I cannot get it to work with temp table variables.

13157 1 / Create an account to join the discussion.
clee_671
Follow / 23 Sep 2011 at 3:38pm / Toad for SQL Server
Latest post by on 30 Sep 2011 at 5:19am

Tune SP with Apply statement

Expand content
I'm trying to tune a stored procedure that has outer apply and cross apply in it and I am getting the invalid SQL statement message. When I take the apply statements out it works fine. Can the optimizer...

I'm trying to tune a stored procedure that has outer apply and cross apply in it and I am getting the invalid SQL statement message.  When I take the apply statements out it works fine.  Can the optimizer do apply statements?

15014 1 / Create an account to join the discussion.
Kelvin Lai
Follow / 17 May 2011 at 2:34pm / Toad for SQL Server

Quest SQL Optimzier for SQL Server Version 7.1.0 has been release...

Expand content
Hi Everyone, SQL Optimizer for SQL Server version 7.1.0 is now available. In this release, we implemented two major featues as well as some of the some of the user interface enhancements and SQL Server...

Hi Everyone,

SQL Optimizer for SQL Server version 7.1.0 is now available.

In this release, we implemented two major featues as well as some of the some of the user interface enhancements and SQL Server 2008 R2 support.

- You can now create virtual indexes for user defined alternatives in Optimize SQL. The Virtual Indexes and Index Script tabs are also available for system generated index alternatives
- User defined alternatives now support functional-based indexes.
- Execution plan outdated reminder, for example, when you modified your SQL text again after the execution plan retrieved. SQL Optimizer will show a message at the top of the execution plan pane to alter you.

Also feel free to talk to us at "Talk to Our Developers".

Kelvin

12274 0 / Create an account to join the discussion.
norman.wroblewski_125
Follow / 18 Feb 2011 at 6:05pm / Toad for SQL Server
Latest post by on 28 Feb 2011 at 6:37am

Timings when Using Virtual Indexes vs Timings when creating Actual...

Expand content
Hello All, I've been optimizing MS SQL Code and getting some fabulous timing increases. - where does the Toad Optimizer store the virtual indexes. - When I rerun the optimizer same session creating...

Hello All,

I've been optimizing MS SQL Code and getting some fabulous timing increases.

- where does the Toad Optimizer store the virtual indexes.

- When I rerun the optimizer same session creating actual indexes the timings drop dramatically to the original timing.

- Where does the Toad optimizer store these indexes or does it actually change my database as I would expect.

- Also, running in MS SQL Server 2005 and have settings set for Ansi-92 join syntax.

- Microsoft does not support the 'OPTIONS' clause in views. Anyone have a solution?

Would appreciate any assistance. Getting beat up because no one is seeing timing increases.

Thx

Norm

12066 1 / Create an account to join the discussion.
peter.skoglund_171
Follow / 21 Feb 2011 at 5:26pm / Toad for SQL Server
Latest post by on 28 Feb 2011 at 6:30am

Tune SP with a Use-Defined Table types

Expand content
I'm trying to tune a SP with a Use-Defined Table type but I can't get it to work. What am I dowing wrong?, I get the below, hoe do I define the Use-Defined Table? Invalid SQL statement . Table...
I'm trying to tune a SP with a Use-Defined Table type but I can't get it to work.
What am I dowing wrong?, I get the below, hoe do I define the Use-Defined Table?

Invalid SQL statement.

Table does not exist.

Invalid object name '@ItemTable b'.

Possible causes of this error are:

- You are not pointing to the user and the database where the objects referenced by the SQL are located.

Therefore, the objects cannot be found.

- You are using T-SQL code or more than one SQL statement. Only a single SELECT/INSERT/DELETE/UPDATE can be processed.

For multiple SQL statements or T-SQL code, first use the SQL Scanner module to extract the SQL statements.

- Your SQL statement has object references within quotes. You need

to enable the "set quoted identified on" option in the Preferences - > Database Settings. - The SQL statement uses a temporary table. You must create the temporary table using the User-Defined Temp Table function.



Here is the sp.

CREATE PROCEDURE [dbo].[GetPriceByItems]
@MarketUnitCode CHAR (2), @ItemTable [dbo].[ItemTable] READONLY
AS
 
SELECT
 , DepDate
 , Dur
 , PaxPrice
 , Price
 , Disc_oth
 , RoomTp
FROM DBO.VW_TPRILO a
JOIN @ItemTable b ON (a.Wb.ItemID)  = b.ItemID)
WHERE a.Mucd = @MarketUnitCode
ORDER BY price


END

The Use-Defined Table types

CREATE TYPE [dbo].[ItemTable] AS TABLE(
 [ItemID] [int] NOT NULL
)

13201 2 / Create an account to join the discussion.
matthew.lang_640
Follow / 13 Oct 2010 at 10:19am / Toad for SQL Server
Latest post by on 15 Oct 2010 at 8:43am

Keep getting incorrect syntax error when trying to scan sql

Expand content
Hi, maybe I'm being really daft here. I have triued repeatedly to scan the stored procedures stored in my HR database system and each time I do I get an error on the scan results that reads This job...

Hi, maybe I'm being really daft here. I have triued repeatedly to scan the stored procedures stored in my HR database system and each time I do I get an error on the scan results that reads

This job had an error during the process of scanning SQL.

After reviewing the error below, you may try to restart the process by clicking the Scan button.

Incorrect syntax near 'Live'.

Lets say for example my Database name is [TestCompany Live]

What am I doing wrong here do you think?
Must I rewrite the procesures to enable SQL Scanner to read them.

13152 3 / Create an account to join the discussion.