Intermittently Slow query - Left Join

Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:

Descriptions table:

ID | Title
Doc1 | Document 1
Doc2 | Document 2

Documents table:

ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189

View:

ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)

So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.

In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.

It's running on SQL Server 2005 with 2GB of RAM.

Any suggestions on tracking down the reason for the slowness would be great.

Thanks in advance!!!

-DanPost the query you are executing.|||SELECT
dbo.Table1.ItemType,
dbo.Table1.ItemLabel,
dbo.Table1.ItemParent,
dbo.Table1.ItemID,
dbo.Table1.ProjectID,
dbo.Table1.ItemDate,
dbo.Table1.Active,
dbo.Table1.ItemBaseLabel,
dbo.Table2.ItemDescription
FROM dbo.Table1 LEFT OUTER JOIN dbo.Table2
ON dbo.Table1.ProjectID = dbo.Table2.ProjectID
AND dbo.Table1.ItemBaseLabel = dbo.Table2.ItemBaseLabel

Here you go!

-Dan|||where are the indices placed? have you looked at the execution plan?|||That's weird... I just checked the indices on the base table and there are three. One is By ItemID. Another is by ItemLabel and another is by ItemLabel, ItemParent, Item ID, and another field. Could they be throwing off the way the data is being stored?

There are no indices on the view and I cannot add one. It comes up with an error saying that it can't add an index because it's not schema based. Please, bear with me - I'm new to 2005. This was originally stored on MSSQL 7.0 and it worked fine there.

Thanks!

-Dan|||typically you want indices on search conditions, primary keys (there is usually a clustered one there) and the foriegn keys but you should examine the execution plan first to make sure this will help. if they are already there, make sure they are not fragmented, the statistics are up to date, and then if this is a sp, recompile it. I am not working with 2K5 just yet.|||I checked the indices and everything seems to be in order. I also tried it through a stored procedure and it still takes way too long.

Any other ideas?

-Dan|||for the third time... have you looked at the execution plan?|||Sorry. The execution plan said that a table scan on the dbo.Table2 was 100% cost. So I looked at the fields it was referencing and added a column to the Table2 index and then re-ran the execution plan and it was more divided among the tasks, now with 2 index scans.

Went back to the query and now it's blazing fast.

Apparently my listening skills are about as good as my databasing skills...

Thanks Thrasymachus. :)

-Dan

Intermittent Slowdown Of SQL Server 2000

Hi,
I have been struggling to identify the cause of an application go slow
that happens perhaps once a month or a day or two after the server has
been rebooted. The application is a web based ASP app that uses SQL
server 2000, both on the same box. This has been occuring for at least
6 months, probably longer and the only way I have found to overcome
this problem without a server reboot is to restart to the SQL service.
>From my investigations I am assuming that the problem is SQL server
related but I am unable to identify the root cause of the problem and
was wondering if anyone can offer suggestions to help me fix this
issue.
Many ThanksThis sounds like this is a blocking problem. You can try to identify
the blocking SPID. If you run this SQL Statement, you'll get the heads
for the blocking chain:
select blocker.spid
from master..sysprocesses blocker inner join master..sysprocesses
blocked
on blocker.spid = blocked.blocked
where blocker.blocked = 0
After you get the blocking spid you can chek what the process is doing
and who is running it with sp_who2 and dbcc inputbuffer.
Adi
Ham wrote:
> Hi,
> I have been struggling to identify the cause of an application go slow
> that happens perhaps once a month or a day or two after the server has
> been rebooted. The application is a web based ASP app that uses SQL
> server 2000, both on the same box. This has been occuring for at least
> 6 months, probably longer and the only way I have found to overcome
> this problem without a server reboot is to restart to the SQL service.
> >From my investigations I am assuming that the problem is SQL server
> related but I am unable to identify the root cause of the problem and
> was wondering if anyone can offer suggestions to help me fix this
> issue.
> Many Thanks|||Hi,
Try using fixed memory area for SQL Server.
Also to start with basics
Use SQL Profiler to capture the events at that time.
Thanks
Ajay
Adi wrote:
> This sounds like this is a blocking problem. You can try to identify
> the blocking SPID. If you run this SQL Statement, you'll get the heads
> for the blocking chain:
> select blocker.spid
> from master..sysprocesses blocker inner join master..sysprocesses
> blocked
> on blocker.spid = blocked.blocked
> where blocker.blocked = 0
> After you get the blocking spid you can chek what the process is doing
> and who is running it with sp_who2 and dbcc inputbuffer.
> Adi
> Ham wrote:
> > Hi,
> >
> > I have been struggling to identify the cause of an application go slow
> > that happens perhaps once a month or a day or two after the server has
> > been rebooted. The application is a web based ASP app that uses SQL
> > server 2000, both on the same box. This has been occuring for at least
> > 6 months, probably longer and the only way I have found to overcome
> > this problem without a server reboot is to restart to the SQL service.
> >
> > >From my investigations I am assuming that the problem is SQL server
> > related but I am unable to identify the root cause of the problem and
> > was wondering if anyone can offer suggestions to help me fix this
> > issue.
> >
> > Many Thanks|||Thanks all, I will try these when the problem next occurs.
Thanks Again
On 23 Jan, 10:50, "Adi" <adic...@.hotmail.com> wrote:
> This sounds like this is a blocking problem. You can try to identify
> the blocking SPID. If you run thisSQLStatement, you'll get the heads
> for the blocking chain:
> select blocker.spid
> from master..sysprocesses blocker inner join master..sysprocesses
> blocked
> on blocker.spid = blocked.blocked
> where blocker.blocked = 0
> After you get the blocking spid you can chek what the process is doing
> and who is running it with sp_who2 and dbcc inputbuffer.
> Adi
>
> Ham wrote:
> > Hi,
> > I have been struggling to identify the cause of an application go slow
> > that happens perhaps once a month or a day or two after the server has
> > been rebooted. The application is a web based ASP app that usesSQL
> > server 2000, both on the same box. This has been occuring for at least
> > 6 months, probably longer and the only way I have found to overcome
> > this problem without a server reboot is to restart to theSQLservice.
> > >From my investigations I am assuming that the problem isSQLserver
> > related but I am unable to identify the root cause of the problem and
> > was wondering if anyone can offer suggestions to help me fix this
> > issue.
> > Many Thanks- Hide quoted text -- Show quoted text -

Intermittent slow SQL2005 database

I'm querying a small SQL2005 database and finding that the query can sometimes complete in under a second and then 5 minutes later the same query can take 15 minutes to complete.

The query I'm running is very simple as follows:
select TOP 26 * from vSearchListOpportunityItem WHERE OpIt_OpportunityId=2495 ORDER BY Prod_Name, OpIt_OpportunityItemId

The view it is pulling data from only contains only 1890 lines, which in turn pulls data from 3 tables with 821, 2560, and 1957 lines of data. In other words it's small. I have noticed that if I try and open the smallest of these tables while on a 'go slow' period it also takes around 15 minutes to return the data.

The database was originally on SQL 2000. It is the only database on this powerful quad core server.
The SQL Server CPU usage never goes above 40%, and always has free memory.
No sign of locks.

I can't figure out why such a small database is going so slow with such a simple query. Any ideas?

Depends on the view definition, one mistake in the view definition and the results is a cartesian product resulting in e.g. 821x2560x1957 (or any other variation) of the data leading to a non-wanted data explosion. If opening one table also slows down the process, make sure that there is no *sophisticated* computed column on the table which could slow down the operation.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Perhaps this 'secret' dba trick will help.

Use the following database option:

SET OPTION RUN_FASTER ON

And then, I have a bridge to sell if you are interested...

(Please post your query and the VIEW definition, as well as the unlaying table DDL, and we 'might' be able to give you more directed guidance.)

|||

> SET OPTION RUN_FASTER ON

No I tried that already.

Here is the View:

USE [CRM]

GO

/****** Object: View [dbo].[vSearchListOpportunityItem] Script Date: 08/03/2007 09:41:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[vSearchListOpportunityItem] AS SELECT Prod_ProductId, Prod_Name, Prod_Description, Prod_ListPrice, Prod_ListPrice_CID, OpportunityItem.*,Opportunity.* FROM Opportunity,OpportunityItem LEFT OUTER JOIN Products ON OpIt_ProductId = Prod_ProductId WHERE OpIt_OpportunityId=Oppo_OpportunityId and OpIt_Deleted IS NULL

And the table Products (this is the table that also runs slow when returning its 821 rows):

USE [CRM]

GO

/****** Object: Table [dbo].[Products] Script Date: 08/03/2007 09:43:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Products](

[Prod_ProductId] [int] NOT NULL,

[Prod_Name] [nchar](64) NULL,

[Prod_Description] [nchar](60) NULL,

[Prod_CreatedBy] [int] NULL,

[Prod_CreatedDate] [datetime] NULL,

[Prod_UpdatedBy] [int] NULL,

[Prod_UpdatedDate] [datetime] NULL,

[Prod_Deleted] [tinyint] NULL,

[Prod_ListPrice] [numeric](24, 6) NULL,

[Prod_TimeStamp] [datetime] NULL,

[Prod_SegmentID] [int] NULL,

[Prod_ChannelID] [int] NULL,

[Prod_ListPrice_CID] [int] NULL,

[Prod_ICITEM] [nchar](24) NULL,

[Prod_ICType] [nchar](10) NULL,

[prod_nsrcategory] [nchar](40) NULL,

[prod_nsrtype] [nchar](40) NULL

) ON [PRIMARY]

Table Opportunity 2561 rows:

USE [CRM]

GO

/****** Object: Table [dbo].[Opportunity] Script Date: 08/03/2007 09:43:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Opportunity](

[Oppo_OpportunityId] [int] NOT NULL,

[Oppo_PrimaryCompanyId] [int] NULL,

[Oppo_PrimaryPersonId] [int] NULL,

[Oppo_AssignedUserId] [int] NULL,

[Oppo_ChannelId] [int] NULL,

[Oppo_Description] [nchar](40) NULL,

[Oppo_Type] [nchar](40) NULL,

[Oppo_Product] [nchar](30) NULL,

[Oppo_Source] [nchar](40) NULL,

[Oppo_Note] [ntext] NULL,

[Oppo_CustomerRef] [nchar](30) NULL,

[Oppo_Opened] [datetime] NULL,

[Oppo_Closed] [datetime] NULL,

[Oppo_Status] [nchar](40) NULL,

[Oppo_Stage] [nchar](40) NULL,

[Oppo_Forecast] [numeric](24, 6) NULL,

[Oppo_Certainty] [int] NULL,

[Oppo_Priority] [nchar](40) NULL,

[Oppo_TargetClose] [datetime] NULL,

[Oppo_CreatedBy] [int] NULL,

[Oppo_CreatedDate] [datetime] NULL,

[Oppo_UpdatedBy] [int] NULL,

[Oppo_UpdatedDate] [datetime] NULL,

[Oppo_TimeStamp] [datetime] NULL,

[Oppo_Deleted] [tinyint] NULL,

[Oppo_Total] [numeric](24, 6) NULL,

[Oppo_NotifyTime] [datetime] NULL,

[Oppo_SMSSent] [nchar](20) NULL,

[Oppo_WaveItemId] [int] NULL,

[Oppo_SegmentID] [int] NULL,

[Oppo_SecTerr] [int] NULL,

[Oppo_WorkflowId] [int] NULL,

[Oppo_LeadID] [int] NULL,

[Oppo_Forecast_CID] [int] NULL,

[Oppo_Total_CID] [int] NULL,

[oppo_scenario] [nchar](40) NULL,

[oppo_decisiontimeframe] [nchar](40) NULL,

[oppo_Currency] [int] NULL,

[oppo_TotalOrders_CID] [int] NULL,

[oppo_TotalOrders] [numeric](24, 6) NULL,

[oppo_totalQuotes_CID] [int] NULL,

[oppo_totalQuotes] [numeric](24, 6) NULL,

[oppo_NoDiscAmtSum] [numeric](24, 6) NULL,

[oppo_NoDiscAmtSum_CID] [int] NULL,

[Oppo_OrderNumber] [nchar](100) NULL,

[oppo_CurrentSystem] [nchar](15) NULL,

[oppo_ProductInterest] [nchar](40) NULL,

[oppo_deposit_received] [nchar](1) NULL,

[oppo_Install_Complete] [nchar](1) NULL,

[oppo_NewExisting] [nchar](40) NULL,

[oppo_InstallDate] [datetime] NULL,

[oppo_invoicenumber] [nchar](20) NULL,

[oppo_Soldby] [int] NULL,

[oppo_SaleDate] [datetime] NULL,

[oppo_Creditnoteno] [nchar](10) NULL,

[oppo_creditnoteamount] [numeric](24, 6) NULL,

[oppo_creditnoteamount_CID] [int] NULL,

[oppo_invoiceamount] [numeric](24, 6) NULL,

[oppo_invoiceamount_CID] [int] NULL,

[oppo_invoicedate] [datetime] NULL,

[oppo_Depositamoun] [numeric](24, 6) NULL,

[oppo_Depositamoun_CID] [int] NULL,

[oppo_SuppThisSale] [numeric](24, 6) NULL,

[oppo_SuppThisSale_CID] [int] NULL,

[oppo_SuppFreq] [nchar](40) NULL,

[oppo_SupportInAccpac] [nchar](1) NULL,

[oppo_SupportAddedBy] [int] NULL,

[oppo_supportAddedDate] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table OpportunityItem 1957 rows:

USE [CRM]
GO
/****** Object: Table [dbo].[OpportunityItem] Script Date: 08/03/2007 09:44:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OpportunityItem](
[OpIt_OpportunityItemId] [int] NOT NULL,
[OpIt_OpportunityId] [int] NOT NULL,
[OpIt_ProductId] [int] NOT NULL,
[OpIt_Quantity] [numeric](24, 6) NULL,
[OpIt_OriginalListPrice] [numeric](24, 6) NULL,
[OpIt_QuotedPrice] [numeric](24, 6) NULL,
[OpIt_QuotedPriceTotal] [numeric](24, 6) NULL,
[OpIt_Discount] [numeric](24, 6) NULL,
[OpIt_CreatedBy] [int] NULL,
[OpIt_CreatedDate] [datetime] NULL,
[OpIt_UpdatedBy] [int] NULL,
[OpIt_UpdatedDate] [datetime] NULL,
[OpIt_TimeStamp] [datetime] NULL,
[OpIt_Deleted] [tinyint] NULL,
[OpIt_SegmentID] [int] NULL,
[OpIt_ChannelID] [int] NULL,
[OpIt_Discount_CID] [int] NULL,
[OpIt_OriginalListPrice_CID] [int] NULL,
[OpIt_QuotedPrice_CID] [int] NULL,
[OpIt_QuotedPriceTotal_CID] [int] NULL,
[OpIt_Ordered] [nchar](1) NULL
) ON [PRIMARY]

|||

> SET OPTION RUN_FASTER ON

No I tried that already.

Since that didn't work for you, how about trying a RESTORE with the 'secret' DBA RESTORE option:

RESTORE DATEBASE MyDatabase

WITH FAST_QUERY_MODE_DWIM

*( DWIM = Do What I Mean )

I'll offer the following comments, some of which 'may' help with this issue.

First, change your datatype for Oppo_Note to varchar(max). Text, ntext, and image are on the deprecation list.

Second, do you really need ALL columns from Opportunity and OpportunityItem? -You are retrieving approximately 94 columns per row, and that is consuming a lot of memory in order to create the resultset. From a cursory look, it appears that each row needs a couple thousand bytes.

Then I would -re-organize the VIEW a bit to optimize the JOIN. (There is a chance that your VIEW is doing a cartesian JOIN between Opportunity and OpportunityItem (the resultset is over 5 million rows -check the execution plan. A cartesian JOIN would require in excess of 10GB just to hold the intermediate JOIN resultset.) The bottom line is that there is a lot of I/O happening here, perhaps some of which is unnecessary.

ALTER VIEW [dbo].[vSearchListOpportunityItem]
AS
SELECT
p.Prod_ProductId,
p.Prod_Name,
p.Prod_Description,
p.Prod_ListPrice,
p.Prod_ListPrice_CID,
o.*, --List Actual Columns Needed
oi.* --List Actual Columns Needed
FROM Opportunity o,
JOIN OpportunityItem oi
ON ( o.OpIt_OpportunityId = oi.Oppo_OpportunityId
AND o.OpIt_Deleted IS NULL
)
LEFT OUTER JOIN Products p
ON o.OpIt_ProductId = p.Prod_ProductId

Then, verify the indexing

Opportunity(OpIt_OpportunityId),

OpportunityItem(Oppo_OpportunityId)

OpportunityItem(OpIt_ProductId)

Products(Prod_ProductId)

You may also find value is using the Database Tuning Wizard to determine if additional indexing can be useful for this operation.

And lastly, the naming convention used is 'awkward' to be charitable. Prefixing a column name with the table indicator is quite 'out of favor'. You are wasting keystrokes EVERY time you refer to a column. Anytime a JOIN occurs, you 'should' be using an table ALIAS (as above, [ o, oi, p ] -and it actually makes the code more readible. A good 'rule of thumb' is that if the keystroke does not add value, it is wasted. With the column names above, [Prod_], [OpIt_], [Oppo_] are wasted keystokes, they don't add any value to distinguish the columns. The table alias clearly identifies where the column comes from AND also clarifies the JOIN conditions.

|||

Unfortunately the query and the views are created by a 3rd party application. I won't embarrass the company involved other than to say they are one of the bigger players in mid-range CRM software. The way their application uses the SQL database often leaves a lot to be desired, and I really can't mess with their choice of naming convention.

This application often contains lazy views which refer to 'table.*' rather than pick out the fields that are required. Unfortunately it is difficult for me to know which are needed or not.

I have made changes to the view as you have suggested, but it appears to have made little if any difference. I'm not really surprised however as if a view was slow, wouldn't you expect it it always be slow if run on exactly the same set of data? How can I explain that sometimes it runs in a second, while other times 14 minutes?|||

Hi!

To answer your question about why fast then slow... Caching...

Once the data has been cached it no longer has to go to the data pages to resolve it.

So as long as the data has not changed (or cache expired) it will resolve from cache.

Can you send me the showplan on this... maybe we can solve this with some simple indexing.

chas@.hyman.com

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||My initial guess would be from what you describe the table or index is locked. That is where you time difference is coming from.

Look at the locks and see when it takes long, if there is a lock on the table by someone.

|||

Please post the execution plan. Without that, everything else is just a WAG*.

* Wild-Assumptions Guess

Intermittent slow performing SP

Hello,
I have a stored proc that has started behaving strangely a few ws
after upgrading to SQL Server 2005. This problem cannot be replicated
in the test environment.
The stored proc responds within 1 second in the Production environment
until it slows down (not sure why) and takes about 2 minutes to run
when called by the application. The same proc in production contines
to run in less than a second when called from SQL Server Query
Analyzer. When I run an Alter Proc and run the script for altering
the proc (without ANY change in it), response gets back to what it
used to be - <1 sec when called by the application. Does this have
something to do with the optimizer and query plan reuse? If yes - what
causes the sudden change - for SP performance to be great part of the
day and then degrade at some point (and that too only when called by
the app, when it works fine in Query Analyzer - and only this SP -
since the rest of the app works fine)?
Indexes are defragged every night, statistics are updated with full
scan every night.
I plan to investigate this further but thought to check first so I
needn't reinvent a wheel that already exists. Thanks much.This is not that easy to answer. You've already ruled out the obvious one
of stat's not up to date.
Things to check next are....
1. what about the inputs? do they vary wildly?
2. the content of the procedure. Does it have several different execution
paths?
Regards
Colin Dawson
www.cjdawson.com
<smithabreddy@.gmail.com> wrote in message
news:1146338367.532365.141660@.g10g2000cwb.googlegroups.com...
> Hello,
> I have a stored proc that has started behaving strangely a few ws
> after upgrading to SQL Server 2005. This problem cannot be replicated
> in the test environment.
> The stored proc responds within 1 second in the Production environment
> until it slows down (not sure why) and takes about 2 minutes to run
> when called by the application. The same proc in production contines
> to run in less than a second when called from SQL Server Query
> Analyzer. When I run an Alter Proc and run the script for altering
> the proc (without ANY change in it), response gets back to what it
> used to be - <1 sec when called by the application. Does this have
> something to do with the optimizer and query plan reuse? If yes - what
> causes the sudden change - for SP performance to be great part of the
> day and then degrade at some point (and that too only when called by
> the app, when it works fine in Query Analyzer - and only this SP -
> since the rest of the app works fine)?
> Indexes are defragged every night, statistics are updated with full
> scan every night.
> I plan to investigate this further but thought to check first so I
> needn't reinvent a wheel that already exists. Thanks much.
>|||More than likely the plan for the sp is getting recompiled at times during
the day. This can be for many reasons. But the first time the sp is run
after it is marked for recompile it will create the plan based on the actual
values of the parameters passed in. If at some point you execute the sp at
one of those times when it recompiles and pass in values such that a table
scan is best it will continue to use a table scan until the next time it
recompiles. So if the selectivity of that value passed in was such that it
would return lots of rows you may not get the optimal plan for the majority
of the values you pass in after that. Running the sp in QA will generate a
new plan since it is most likely a different batch match than how it is
called from the app. If it is slow again try running sp_recompile for that
sp and see if that helps. If so then you are pretty much assured this is
the cause.
Andrew J. Kelly SQL MVP
<smithabreddy@.gmail.com> wrote in message
news:1146338367.532365.141660@.g10g2000cwb.googlegroups.com...
> Hello,
> I have a stored proc that has started behaving strangely a few ws
> after upgrading to SQL Server 2005. This problem cannot be replicated
> in the test environment.
> The stored proc responds within 1 second in the Production environment
> until it slows down (not sure why) and takes about 2 minutes to run
> when called by the application. The same proc in production contines
> to run in less than a second when called from SQL Server Query
> Analyzer. When I run an Alter Proc and run the script for altering
> the proc (without ANY change in it), response gets back to what it
> used to be - <1 sec when called by the application. Does this have
> something to do with the optimizer and query plan reuse? If yes - what
> causes the sudden change - for SP performance to be great part of the
> day and then degrade at some point (and that too only when called by
> the app, when it works fine in Query Analyzer - and only this SP -
> since the rest of the app works fine)?
> Indexes are defragged every night, statistics are updated with full
> scan every night.
> I plan to investigate this further but thought to check first so I
> needn't reinvent a wheel that already exists. Thanks much.
>|||I will try sp_recompile the next time this happens and update this
post. Thank you for your prompt responses.|||(smithabreddy@.gmail.com) writes:
> I will try sp_recompile the next time this happens and update this
> post. Thank you for your prompt responses.
Also, compare query plan before and after.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure what you mean by "Also, compare query plan before and after. "
I ran profiler while the problem was occuring and although it captured
stats and showplantext for some of the other things happening at that
time, I can't find that information for the SPID which was executintg
this SP.
--
All I could see for this SP:
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
select PROCEDURE_CATALOG...
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
SET NO_BROWSETABLE ON
declare @.p1 int set @.p1=25 exec sp_prepare @.p1 output,N'@.P1 int
OUTPUT,@.P2 int,@.P3 varchar(25),@.P4 bit',N' EXEC @.P1=app.usp_proc
@.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4 ',1 select @.p1
EXEC @.P1=app.usp_proc @.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4
if @.ModifyType = "create"
EXEC @.P1=app.usp_proc @.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4
SET FMTONLY ON exec sp_execute 25,0,0,' ',0 SET FMTONLY OFF
set fmtonly off
exec sp_unprepare 25
SET NO_BROWSETABLE OFF
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
SET NOCOUNT ON
CREATE TABLE #tmpPTID (ProjectTemplateID int)
if @.ModifyType = "create"
NULL
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
Also when I ran the SP from QA today, I ran DBCC FreeProccache and ran
it as exec app.usp_proc. Also ran it using the verbage captured via
Profiler (again after DBCC FreeProccache):
exec sp_prepare...
EXEC app.usp_proc...
They generated exactly the same stats and execution plan...|||NNTP-Posting-Host: gw-uu-virtual.levonline.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: black.telenor.se 1146509970 22605 217.70.32.2 (1 May 2006 18:59:30
GMT)
X-Complaints-To: postmaster@.telenordia.se
NNTP-Posting-Date: Mon, 1 May 2006 18:59:30 +0000 (UTC)
User-Agent: Xnews/2006.03.07 Mime-proxy/2.1.c.0 (Win32)
Path: TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!newsfeed00.sul.t-online.de
!t-online.de!130.59.10.21.MISMATCH!kanaga.switch.ch!switch.ch!newsfeed2.fune
t.fi!newsfeed1.funet.fi!newsfeeds.funet.fi!newsfeed1.telenor.se!news.telenor
.se!not-for-mail
Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:600229
(smithabreddy@.gmail.com) writes:
> Not sure what you mean by "Also, compare query plan before and after. "
I meant that you should compare the query plan when the procedure appears
to be slow, and after you have used sp_recompile or whatever to force a
new plan.

> I ran profiler while the problem was occuring and although it captured
> stats and showplantext for some of the other things happening at that
> time, I can't find that information for the SPID which was executintg
> this SP.
Did you filter on this spid? If you include too many events, the
Profiler will have to throw events away. (You can see this as a special
event in Profiler.)
But I hoped it would be easier to see this from QA - or it is not.
QA runs with SET ARITHABORT ON by default, but this is not the case
for an ASP client, and you get different plans because of this setting.
So capturing plans in Profiler may be better after all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SP slowed down again. I ran sp_recompile for the SP (did not have
a profiler template set up yet and it was too much of a fire-fighting
situation to do that right away) and this fixed performance
immediately. The page refreshed almost instantaneously instead of
taking the 2-4 minutes it was taking just before running sp_recompile.
So what is the solution for such a stored proc? Run Sp_Recompile
against it every so often?
There are two queries in this SP. The first one is an Insert into a
temp table (and this statement gets recompiled every time the SP is
run) and the second is a SELECT.
Should I
1) add a recompile hint to this SELECT query
2) set the SP to recompile everytime
3) run a job with sp_recompile <storedProc> every half hour
I realize I will need to test whatever solution...but would one of the
above be inherently better than the others or am I missing something
else that can be done? I suspect we have several such procs in the
database which may also need some help before too long.
P.S. I have created a Profiler template to capture events related to a
specific object ID - I will use this to perform a short capture the
next time I see this latency. This should help me figure out what
causes the bottleneck and if it can be eliminated.|||(smithabreddy@.gmail.com) writes:
> There are two queries in this SP. The first one is an Insert into a
> temp table (and this statement gets recompiled every time the SP is
> run) and the second is a SELECT.
Do you have any idea of what amount of data that goes into the temp table?
Maybe you get more consistent performance with a table variable, as table
variables do not cause recompilations?
Or would it be possible to integrate the query that fills the temp table
into the final SELECT?

> Should I
> 1) add a recompile hint to this SELECT query
> 2) set the SP to recompile everytime
> 3) run a job with sp_recompile <storedProc> every half hour
Of these I would actually lean towards the latter. Compilation can be costly
too. Then again, a recompilation hint on the SELECT query is the prettiest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SP I mentioned above has been behaving well the last couple of days
- haven't had to recompile or do anything so I'll let it be until I
need to touch it again.
I have ANOTHER SP (:\) which is giving me some problems. In the PROD
database running this PROC with one set of parameters causes it finish
executing in under 1 second. Another set of parameters causes the
query to take close to 6 minutes to execute.
With the problem set of parameters, if I run against:
- Prod Server:
PROD database: 6 minutes
- Development Server:
DB1: 0 seconds
DB2: 6 Minutes
DB3 (Copy of PROD from yesterday): 0 Seconds
All of the databases have the same data. They have the same defrag,
update usage and update stats job run on them. On the DEV server, I've
run DBCC FreeProccache and recompiled the proc before running it in
each database.
--
I've narrowed down the latency to one section of the SP. This set of
parameters causes this one section of the SP to run and this section
consists of:
IF @.maxrows>0
SET ROWCOUNT @.maxrows
INSERT INTO...SELECT
.
.
.
SET ROWCOUNT 0
--
This runs fine in DB1 and DB3 but not in PROD and DB2. The execution
plan is different in for this query and two more nested loops have been
added in the inefficient query plan, plus a Table Spool/Lazy Spool step
for which rowcount = 1.6 million.
If I remove the SET Rowcount - the SP executes within 1 second.
Replacing SET Rowcount with TOP also causes the query to take between 5
and 6 minutes.
Why the different execution plans for the same set of parameters? They
are different copies of the same database.
What causes the Table Spool/Lazy Spool to come in to play?
(Adding a recompile/force order query hint does not make a change.)

Integration Services Designer (Visual Studio) very very slow

Hi all

I have some performance issues when developing Integration Service Package ...

It is very very slow for example when I try to add new connection in
Connection Manages, it takes about 2 minutes just to open the property window.

On another pc in the same environment it works ok.
In the past I have doing a lot of SSIS Packages ... Is there any cache to empty ?

Thanks for any comments

Best regards
Frank Uray

Have you tried the 'work offline' option in SSIS menu. That option will prevent SSIS to validate connections in the package at design time. I am not sure if that is the source of your problem; bt you can try it.|||

Thank you for your answer,
but this does not help, it is still very slow ... :-(

Best regards
Frank Uray

|||Um, well... What are your machine specifications? CPU? RAM? etc...

What's the version number of SSIS?|||

Hi Phil

Here are the specifications:
- Dual Core AMD Opteron 2.20 GHz.
- 3.50 GB RAM
- 160 GB Disk
- HP xw9300 Workstation
- Windows XP SP2
- Visual Studio 8.0.50727.42 (RTM.050727-4200)

Hope you can find out something ...

Thanks and best regards
Frank Uray

|||

That's weird. I have half of that hardware and same VS version and run with no problem. Do you get the same behavior even when you create a new project/package? Check the task manager activity as well.

|||

Hi Rafael

Yes, I can create a new project and when I try to
add a new connection it takes 2 minutes to open
the first dialogbox ...
TaskManager is very quiet ...

Also when I run a package on my pc, it takes about
this 2 minutes to begin running the package.
When I run it on the server, the package finishes in 30 seconds.

Thanks and best regards
Frank Uray

|||This could also just be caused by network configurations when trying to do the connection discovery.|||

1.For integration service designer You should also install SQL 2005 SP1 and

the SQL2005 hotfix 2153.

2.another tip I found in the newsgroups was :

In internet explorer go to Tools-> options-> advanced

Uncheck the tickbox "check for publisher's certificate revocation"

(tnx to Nico Verheire)

3.When the designer is extremely slow in complex data flows, you have to break up the package in severaldataflows and use raw files to transfer data from one data flow to the other. See article <http://www.sqljunkies.com/WebLog/reckless/archive/2006/05/01/ssis_largedataflows.aspx>

I hope this can help.

Met vriendelijke groeten - Best Regards - Cordialement - Mit Freundlichen

Gruessen

Jan D'Hondt

Jade bvba

www.jadesoft.be

Integration Services Designer (Visual Studio) is very very slow

Hi all

I have some performance issues when developing Integration Service Package ...

It is very very slow for example when I try to add new connection in
Connection Manages, it takes about 2 minutes just to open the property window.

On another pc in the same environment (Domain etc.) it works ok.
In the past I have doing a lot of SSIS Packages ... Is there any cache to empty ?

Thanks for any comments

Best regards
Frank Uray

Do you have the SSIS service running? That can sometimes speed things up because it caches information about tasks and components.

Other than that - its more likely this can be attributed to hardware or network issues. I suppose it could be a million and one things.

-Jamie