Noticeable Delay In Prompting For Specific tables
EdCarden
Posts: 138 Silver 2
Forst off I need to point out that the DB that I am working with when this happens is itself around 250GB. That said the table that I am having probelms with when using SQL Prompt is not that big nor is it top heavy ( it does not have a large number of columns in its schema).
I am experiencing repeat and excessive lag/delays in SSMS 2008 R2(SQL Server Mgt Studio) with SQL Prompt when I get to the part of the FROM clause where I join 1 table with another and the lag is specific to a single table.
EXAMPLE:
SELECT A.*
FROM TABLEA A Join TABLEB B ON
Once I type the keyword ON my SSMS instance freezes and it and of course SQL Prompt both become unrepsonsive. AFter anywhere from a few seconds to as long as 10 seconds control returns and I am able to continue typing my query.
The lag is specific to TABLEA and only occurs when I have SQL Prompt enabled. If I disabled SQL Promt there is no delay nor any freeze up of SSMS. Granted there is also no auto complete.
If I back space over the ON keyword and try to type it again the same freeze up occurs.
This table that I have named TABLEA here is not that large consisting of a few thousand rows if that and has only 83 columns. Now that may be a lot of columns for a table in general but within this database its not. Many of the much larger tables have over 100 colulmns and consist of millions of rows of data and yet there is no delay/lag or Freeze up of SSMS when using them in a table join.
NOTE: I am not using the intellisnse feature within SSMS 2008 R2.
Any ideas on this one?
I am experiencing repeat and excessive lag/delays in SSMS 2008 R2(SQL Server Mgt Studio) with SQL Prompt when I get to the part of the FROM clause where I join 1 table with another and the lag is specific to a single table.
EXAMPLE:
SELECT A.*
FROM TABLEA A Join TABLEB B ON
Once I type the keyword ON my SSMS instance freezes and it and of course SQL Prompt both become unrepsonsive. AFter anywhere from a few seconds to as long as 10 seconds control returns and I am able to continue typing my query.
The lag is specific to TABLEA and only occurs when I have SQL Prompt enabled. If I disabled SQL Promt there is no delay nor any freeze up of SSMS. Granted there is also no auto complete.
If I back space over the ON keyword and try to type it again the same freeze up occurs.
This table that I have named TABLEA here is not that large consisting of a few thousand rows if that and has only 83 columns. Now that may be a lot of columns for a table in general but within this database its not. Many of the much larger tables have over 100 colulmns and consist of millions of rows of data and yet there is no delay/lag or Freeze up of SSMS when using them in a table join.
NOTE: I am not using the intellisnse feature within SSMS 2008 R2.
Any ideas on this one?
Comments
The number of columns in a table can be a cause of performance issues, but since you have other tables with more columns then it might not be the problem.
Would you be able to send me the table structure of TableA and TableB and I'll try and reproduce the issue here?
I did some more testing with this and I'm finding the problem is NOT limited to TABLEA but is occurring on every tabl;e join just as soon as I type in the keyword ON.
In the past it was most noticeable with teh first table join and %90 of my queries always woudl start with TABLEA and so it appeared to be spoecific to that table.
Hopefully that helps some with this.
I will tell you that many of the tables in our DB are top-heavy or wide (have many, many columns). For example the largest and most READ to table in the DB has 130 columns. The table with the largest number of columns has 244 columns and over 1 million rows of data.
The number of rows in any particular table shouldn't matter, but the cumulative effect of having lots of tables with a large number of columns is an issue we've seen before.
We are actually working on a version of SQL Prompt that will handle schemas with a large number of objects better, which is (optimistically) going to be available on Friday 9th March.
We are also going to reinstate the feature to ignore certain databases at some point, but I don't think it's going to appear in the release on Friday.
I'll let you know on Friday if you can try the new version on not.
If you guys need any testing against a large DB Schema I am happy to d oit since resolving this quirk in SQL Prompt would be most beneficial to me.
Some Metrics on our DB Schema:
Totals Number of:
TABLES: 2,134
COLUMNS (Across all tables): 33,553
VIEWS: 741
Stored Procedures: 716
UDFs (all types): 285
SQL DML Triggers: 29
Default/Default Constraint: 1,623
We have a new build available, but I don't think it's going to help you.
What they've added is a way to limit the number of objects that SQL Prompt caches in order to prevent SSMS crashing if SQL Prompt runs out of memory.
I don't think this will help with performance. What would be really useful is if you could send me a blank copy of your database schema and I can try and reproduce the performance issue here.
Is there any chance you could send it over?
Chris
Apolagies for not getting back to you sooner but I haven't had the time to dela with this again till now.
I really wish I could send you the schema but that’s the one thing I can’t do because of an NDA (non-disclosure agreement) we have with the Vendor who’s accounting software system uses this database. The DB is not of our own creation but is party of the accounting software package we use. We have the ability to tweak certain things such as adding custom tables to allow for custom reporting but sharing the core DB schema is one of those big no-nos.
I can tell you how many of what we have but I can’t provide the actual schema of the thing. Sorry.
Does this mean that this problem is going to fall to the wayside, go unaddressed? I’m at the point where I’m having to disable SQL Prompt on a regular basis because of the lockups and slowdowns caused by the thing. Its not just when a table is added to the query nor when a query is first loaded. It’s like as if SQL Prompt tries to re-fresh or re-query the schema with every single keystroke. I’m sure I am exaggerating and that it’s not really doing this but it is most certainly locking up all of SQL Server Mgt Studio when it does this.
For example I can type in a table join and be done but still have to wait anywhere from 10 seconds (on the low side) to 30 secs for the actual text I typed to appear and its because of SQL Prompt. How do I know its SQL Prompt? All the slowdowns and lock ups stop when I disable Code Suggestion’s in SQL Prompt.
While I realize that our DB schema is large its not that ,much larger now then 2 years ago and yet the previous versions of SQL Prompt were nowhere this slow. I would have the occasional slow down or lock up but nothing like I am seeing now. Its very, very frustrating especially since Dependency Tracker 2 is also dog slow; the other RedGate product I have for use with SQL Server. I also have SQL Monitor and while its not slow it has its own drawbacks like no way to report on the data it collects; very frustrating.