You will no longer receive e-mail notifications from this forum.
Log-In to post
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
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?
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?
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.
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?
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
Serial Number, Date, Inspector
AAA, 20/03/2015, ED
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?
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.
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
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
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.
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?
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.
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.
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?
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".
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.
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.
- You are using T-SQL code or more than one SQL statement. Only a single SELECT/INSERT/DELETE/UPDATE can be processed.
- Your SQL statement has object references within quotes. You need
Here is the sp.CREATE PROCEDURE [dbo].[GetPriceByItems] @MarketUnitCode CHAR (2), @ItemTable [dbo].[ItemTable] READONLYAS 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
ENDThe Use-Defined Table typesCREATE TYPE [dbo].[ItemTable] AS TABLE( [ItemID] [int] NOT NULL)
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 readsThis 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.