sloq response time in EM, but fast in QA

Similar Threads:

1.slow response time from the application, good response time from qa

Hi,
Since week ago, we're getting slow response time in our application (1 asp
page executes 5 stored procedures in sequence). At the same time, when I run
the same set od stored procedures through query analyzer, everything gets
completed quickly. In execution plan, I didn't find any table scans, or any
other expensive expensive operations. I ran blocking script, and didn't find
anything strange.
System administrators claim that there is no resource issues on the database
box. I suspected at IIS, but then, when we switched a different IIS to point
to db server, again the same issue...
When we reboot db box, and IIS box everything works fine for day or two, and
then again. The only strange thing that I noticed is that whenever
everything is fine, connections are getting released, and there is no more
than 10 concurrent IIS connections in the mix (sp_who). Whenever we have an
issue, this number goes up to around 70. Even then, I'm able to execute set
of stored procedures through qa in less than 1 second. Application guy said
that connection phase in that asp page lasts long.

Does anybody have any idea why is this happening? It is sql server 2000,
sp3, 3GB of memory, 4 processors...
Thanks,
Peja

2.SQL Server slow response, network client fast response

Hi All

We have a sqlserver running on a new 4 way machine with 2 gig of ram and
plenty of disk.  On the same server there is an application that loads
documents into SQL Server.  When the application first starts loading
documents into SQL Server it does so at the rate of about 5 per second.

After a few hours at this rate it suddenly runs very slow, only loading
documents at the rate of 1 every 2 or 3 seconds.  This continues
indefinitely until the server is restarted.  We have tried restarting the
document loading application and stopping and starting SQL Server, but it
doesn't help.

We have also noticed the queries performed on the local machine in query
analyser take 7 seconds, where as the same query executes in a fraction of
second when run from a workstation using query analyser.

Performance monitor indicates no disk queueing, CPU at 15 percent, plenty of
memory to spare.

We checked out the waitstates in SQL Server which indicated a high
percentage of CXPACKET waits.  We turned of parallelism and the CXPACKET
waits dropped significantly, so much so that it isn't an issue anymore. The
waitstate that features the highest at the moment is the NETWORK IO, which I
believe to be normal.

It looks like the application is the culprit, but the slow response from
Query Analyser on the local server tends not to confirm this.

Any idea's

3.Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific 
inserts, updates or selects) at a certain time each week.  
I've checked to make sure something else is not running 
and do not see any scheduled jobs during this time.  I've 
captured a trace file and don't see any reason why it 
would take a long time.  It is not a busy time on the 
server, either.

Any suggestions for some obvious things I could check?  
Our environment is SQL Server 2000 with Service Pack 3 on 
Windows 2000 with Service Pack 3.

TIA,

Jeanne Unger

4.QA returns different records from SQL Server EM

SQL Server 7.0

If I run the following in Query Analyzer I get no records returned:

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'

If, however, I run either :

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'

or

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'

I get a single record returned in each case (which is what I would
expect).

The SQL for GetLeadsOutcome_Dealer is:

CREATE PROCEDURE GetLeadsOutcome_Dealer
@FromDate smalldatetime,
@ToDate smalldatetime,
@OutcomeTypeID integer,
@DealerCode varchar(8000)
AS
DECLARE @TotalLeads integer

BEGIN
SELECT @TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE
(tblLeads.fldDealerID IS NOT NULL)


SELECT DISTINCT (dbo.tblDealers.fldDealerName),
COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@TotalLeads AS TotalLeads
FROM         tblLeads LEFT OUTER JOIN
                      tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
                      tblDealers RIGHT OUTER JOIN
                      tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (((dbo.tblLeads.fldEntered) BETWEEN @FromDate And @ToDate)) AND
tblOutcome.fldOutcomeID=@OutcomeTypeID AND
(dbo.tblDealers.fldDealerCode in (@DealerCode))
GROUP BY dbo.tblDealers.fldDealerName
ORDER BY dbo.tblDealers.fldDealerName

END
GO

However, if I open EM, open a table in query view, and paste this into
the SQL window,

SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) AS
LeadCount
FROM         tblLeads LEFT OUTER JOIN
                      tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
                      tblDealers RIGHT OUTER JOIN
                      tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE     (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND
(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,
183))
GROUP BY tblDealers.fldDealerName
ORDER BY tblDealers.fldDealerName

I get two records returned.

What is happening?

TIA

Edward

TABLE DEFS:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealers
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINT
FK_tblSystemUsers_tblDealers
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINT
FK_tblLeadNotes_tblLeads
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegion
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblDealers]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblLeads]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOutcome]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblRegion]
GO

CREATE TABLE [dbo].[tblDealers] (
	[fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,
	[fldDealerCode] [varchar] (50) NOT NULL ,
	[fldDealerName] [varchar] (50) NULL ,
	[fldDealerTel] [varchar] (20) NULL ,
	[fldDealerEmail] [varchar] (100) NULL ,
	[fldDealerContact] [varchar] (50) NULL ,
	[fldRegionID] [int] NULL ,
	[fldDealerActive] [smallint] NOT NULL ,
	[fldHeadOffice] [smallint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLeads] (
	[fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,
	[fldAccountNo] [varchar] (50) NULL ,
	[fldDealerID] [int] NULL ,
	[fldStatusID] [int] NOT NULL ,
	[fldOutcomeID] [int] NOT NULL ,
	[fldContacted] [smallint] NOT NULL ,
	[fldDateContacted] [smalldatetime] NULL ,
	[fldAppointment] [smallint] NULL ,
	[fldShowRoom] [smallint] NOT NULL ,
	[fldTestDrive] [smallint] NOT NULL ,
	[fldSalesPersonID] [int] NULL ,
	[fldCustomerName] [varchar] (50) NULL ,
	[fldCHouseNo] [varchar] (50) NULL ,
	[fldCStreet] [varchar] (50) NULL ,
	[fldCDistrict] [varchar] (50) NULL ,
	[fldCTown] [varchar] (40) NULL ,
	[fldCCounty] [varchar] (50) NULL ,
	[fldCPostcode] [varchar] (50) NULL ,
	[fldPhoneInd] [varchar] (20) NULL ,
	[fldCTel] [varchar] (50) NULL ,
	[fldNewBusinessDate] [smalldatetime] NULL ,
	[fldAgreementType] [varchar] (50) NULL ,
	[fldCashPrice] [smallmoney] NULL ,
	[fldBalanceFin] [smallmoney] NULL ,
	[fldCustRate] [varchar] (10) NULL ,
	[fldOrigTerm] [int] NULL ,
	[fldPPPType] [varchar] (10) NULL ,
	[fldBalloonValue] [smallmoney] NULL ,
	[fldMonthlyInstal] [smallmoney] NULL ,
	[fldRegNo] [varchar] (10) NULL ,
	[fldRegDate] [smalldatetime] NULL ,
	[fldModel] [varchar] (50) NULL ,
	[fldDescription] [varchar] (50) NULL ,
	[fldTheoPIFDate] [smalldatetime] NULL ,
	[fldMonthsToGo] [int] NULL ,
	[fldBalanceOS] [smallmoney] NULL ,
	[fldLeadPrinted] [smallint] NULL ,
	[fldMergeFile] [varchar] (255) NULL ,
	[fldTPS] [varchar] (10) NULL ,
	[fldMPS] [varchar] (10) NULL ,
	[fldUpdated] [smalldatetime] NULL ,
	[fldUpdatedBy] [int] NULL ,
	[fldEntered] [smalldatetime] NULL ,
	[fldReasonLeadNotProgressed] [varchar] (5000) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblOutcome] (
	[fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,
	[fldOutcomeCode] [int] NULL ,
	[fldOutcome] [varchar] (100) NULL ,
	[fldConvertedSales] [smallint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRegion] (
	[fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,
	[fldRegionCode] [varchar] (10) NULL ,
	[fldRegion] [varchar] (50) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblDealers] PRIMARY KEY  CLUSTERED 
	(
		[fldDealerID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblLeads] PRIMARY KEY  CLUSTERED 
	(
		[fldLeadID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblOutcome] PRIMARY KEY  CLUSTERED 
	(
		[fldOutcomeID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblArea] PRIMARY KEY  CLUSTERED 
	(
		[fldRegionID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR
[fldDealerActive],
	CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR
[fldHeadOffice],
	CONSTRAINT [IX_tblDealers] UNIQUE  NONCLUSTERED 
	(
		[fldDealerCode]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR
[fldOutcomeID],
	CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],
	CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR
[fldAppointment],
	CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],
	CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],
	CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR
[fldLeadPrinted],
	CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],
	CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]
GO

ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD 
	CONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR
[fldConvertedSales]
GO

ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD 
	CONSTRAINT [IX_tblRegion] UNIQUE  NONCLUSTERED 
	(
		[fldRegionCode]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblDealers] ADD 
	CONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY 
	(
		[fldRegionID]
	) REFERENCES [dbo].[tblRegion] (
		[fldRegionID]
	)
GO

ALTER TABLE [dbo].[tblLeads] ADD 
	CONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY 
	(
		[fldDealerID]
	) REFERENCES [dbo].[tblDealers] (
		[fldDealerID]
	)
GO

alter table [dbo].[tblLeads] nocheck constraint
[FK_tblLeads_tblDealers]
GO

5.Not able to connect EM & QA...

hi,
 I am not able to connect my SQL Server 2000 Machine, from client, EM & QA
both are giving following error.

A connection could not be established to [Server Name]
Reason: Data source name not found an dno default driver specified.


I am not able to understand before few minutes I was connected from same
client and now it's giving error  plz help .

6. QA goes, EM doesn't (previously posted on microsoft.public.sqlserver.server)

7. long text input in EM or QA

8. EM does not display table created using QA