SQL compact edition: experiencing

Showing posts with label experiencing. Show all posts

Friday, March 30, 2012

Poor Performing Query

Hi
The answer to this one will be v simple for someone, alas my memory fails
me, what I am after is the term for whats I am experiencing. I have a stored
procedure which has a list of paramaters passed to it, one of these
parameters is set to NULL in the procedure header, within the code at the
begining is a IF NULL statement which then gives the variable a value. This
causes havoc with the execution plan as it believes a NULL value will be use
d
but the procedure uses the value assigned to in the IF NULL statement and th
e
time to execute can be significantly longer using the default. I am aware of
this and I know there is a term associated to this but for the life of me I
can't remember what its called.
anyoneParameter Sniffing:
http://www.google.co.uk/groups?as_e...lic.sqlserver.*
David Portas
SQL Server MVP
--|||"Parameter sniffing". The optimizer sniffs the parameter for the proc when i
t is to create a proc
plan. Apart from that, a DML statement has no context. When the optimizer lo
ok at a batch/proc it
only cares about the SELECT, INSERT, UPDATE ad DELETE statements. From that,
you can probably
realize that the SET command haven't executed and the value you passed to th
e proc is the one used
by the optimizer (parameter sniffing).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MysticMart" <MysticMart@.discussions.microsoft.com> wrote in message
news:6A43A4CB-F8DE-41C8-B53E-FB830599E976@.microsoft.com...
> Hi
> The answer to this one will be v simple for someone, alas my memory fails
> me, what I am after is the term for whats I am experiencing. I have a stor
ed
> procedure which has a list of paramaters passed to it, one of these
> parameters is set to NULL in the procedure header, within the code at the
> begining is a IF NULL statement which then gives the variable a value. Thi
s
> causes havoc with the execution plan as it believes a NULL value will be u
sed
> but the procedure uses the value assigned to in the IF NULL statement and
the
> time to execute can be significantly longer using the default. I am aware
of
> this and I know there is a term associated to this but for the life of me
I
> can't remember what its called.
> anyone

Poor performance with NEWID()

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
-----
|--Sort(ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[PK_MyTable]))
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married, ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnici ty,DateSent)
>SELECT TOP 250 FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married, ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnici ty,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes about
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>
|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data type
column which takes 2 bytes only.

Poor performance with NEWID()

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[
PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
----
--
|--Sort(ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[
PK_MyTable]))
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode
,Married,ChildrenAtHome,Education,E
m
> ploymentStatus,Occupation,Industry,Incom
e,Ethnicity,DateSent)
>SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode
,Married,ChildrenAtHome,Education,E
m
> ploymentStatus,Occupation,Industry,Incom
e,Ethnicity,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes abou
t
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data typ
e
column which takes 2 bytes only.

Poor performance with NEWID()

We're experiencing very poor performance on successive runs of queries such
as the following:
'---
SET NOCOUNT ON
TRUNCATE TABLE tblSurveyTemp
INSERT INTO tblSurveyTemp (FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
SELECT TOP 250 FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
ORDER BY NEWID()
SELECT FirstName,LastName,Email FROM tblSurveyTemp
'---
We're using the NEWID() function to randomize the sample. CPU is always at
100% when we run the query. The first time it runs successfully takes about
20 seconds. Second time, maybe 1 min. Third time it timed out.
Does anyone have any advice?
Thank you!!!I dont have an answer, but a question. Why would you ever want to order by
newid()?
--
TIA,
ChrisR
"Dean J Garrett" wrote:
> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>|||On Fri, 4 Nov 2005 14:12:01 -0800, ChrisR wrote:
>I dont have an answer, but a question. Why would you ever want to order by
>newid()?
Hi Chris,
The combination of TOP ... and ORDER BY NEWID() is often used to get a
pseudo-random sample. The ORDER BY NEWID() makes sure that the rows are
scrambled in an unpredictabable way; the TOP then takes only the few
rows that happen to be first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This method of choosing a random set of records (250 in your case) out of a
larger set of records is pretty effective when there are not many records in
the filtered select. In your case tblMember filtered by your where clause
must produce quite a few records. SQL Server has to pull those records
togther and sort them by the newid() value. Sorting a lot of records can take
a long time. To see how many records we're talking run:
SELECT COUNT(*)
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
In the end you may need to choose another method to choose your records
randomly.
Good luck!
-Phil
"Dean J Garrett" wrote:
> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>|||Here is the article we used to figure out this technique
http://www.windowsitpro.com/Articles/ArticleID/19842/19842.html but we don't
know if it is the best performing!
Thanks
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B2EF1E86-E0AF-4FA3-A787-67C30B2AEA32@.microsoft.com...
> I dont have an answer, but a question. Why would you ever want to order by
> newid()?
> --
> TIA,
> ChrisR
>
> "Dean J Garrett" wrote:
> > We're experiencing very poor performance on successive runs of queries
such
> > as the following:
> >
> > '---
> > SET NOCOUNT ON
> > TRUNCATE TABLE tblSurveyTemp
> >
> > INSERT INTO tblSurveyTemp (FirstName, LastName,
> >
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> > ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> > SELECT TOP 250 FirstName, LastName,
> >
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> > ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> > FROM tblMember t2
> > WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex
in
> > ('F')
> > AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email
AND
> > t.survey='grainactivity')
> > ORDER BY NEWID()
> >
> > SELECT FirstName,LastName,Email FROM tblSurveyTemp
> > '---
> >
> >
> > We're using the NEWID() function to randomize the sample. CPU is always
at
> > 100% when we run the query. The first time it runs successfully takes
about
> > 20 seconds. Second time, maybe 1 min. Third time it timed out.
> >
> > Does anyone have any advice?
> >
> > Thank you!!!
> >
> >
> >
> >|||Very kinky.
I have no idea why you would get such differential results, if indeed
you are using the same parameters each time.
On a toy table, you can see that the newid() gets called BEFORE the
top function.
select top 10 * from mytable
order by newid()
StmtText
-----
|--Sort(TOP 10, ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=newid()))
|--Clustered Index Scan(OBJECT:([HaxPlans].[dbo].[MyTable].[PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
-----
|--Sort(ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT:([HaxPlans].[dbo].[MyTable].[PK_MyTable]))
--
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
>SELECT TOP 250 FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes about
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data type
column which takes 2 bytes only.sql

Wednesday, March 28, 2012

Poor performance querying view from sproc

We are using SQL SERVER 2005.

We have been experiencing horrible performance running select queries from sprocs against views that do outer joins, despite having optimized all of the indexes.

Our solution in these cases (and arguably not the best) has been to first select the views into a temp table and then query the temp table for the desired result set. This has greatly improved the speed of which our sprocs run, but we are looking for a better solution.

Has anybody else experienced this problem?

Thanks in advance.

Hi Eric. Would it be possible for you to post some sample code that includes some of the view definitions, table structures, and sample data scripts? It's quite tough to try and guess what the issue may be.

One thing that comes to mind would be if you selecting only a subset of the columns from the view into a temp table and then joining/selecting from that multiple times, you could be bypassing many bookmark/cluster key lookups, but that is a total shot in the dark. If you can post some scripts, that would help us try and help you. You can get the DDL for the tables/indexes/views by simply scripting the appropriate tables from SSMS/QA...

HTH,

Chad

|||

Are you joining multiple views? Or just a view that joins multiple tables?

What I would do is use a trial replacement for your current implimentation where you create the join etc (as you have in the view) in the query where you actually use the view.

I am not 100% sure how views work in MSSQL, but I hope this can be of some assistance.

|||Can you please paste your stored procedure code. I suspect it is due to the selectivity of the predicates that means you are getting a poor plan. If you can past the output of show plan that would also help.|||We have experienced exactly the same thing. Queries which work fine in 1-5 seconds on SQL 2000 SP4 either run very long or never finish in hours under SQL 2005. I have posted several posts here and never gotten any responses.

We are running SQL 2005 9.0.2153 on SQL 2003 SP1 64bit. The problem seems to be related to views with RIGHT outer joins.

Here is my duplication script, which fails on 2 different SQL 2005 installations.

-- Create Test Data Table CustomerListTom and Views

USE AdventureWorks
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewATom'))
DROP VIEW [ViewATom]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ViewBTom'))
DROP VIEW [ViewBTom]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerListTom') AND type in (N'U'))
DROP TABLE CustomerListTom
GO

CREATE TABLE [dbo].[CustomerListTom](
[CustomerID] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RegionID] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressCountry] [dbo].[Name] NOT NULL,
[Phone] [dbo].[Phone] NULL,
[BillAddressLine] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCity] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressState] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressZip] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillAddressCountry] [dbo].[Name] NOT NULL,
[BillPhone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL,
)

INSERT INTO CustomerListTom
SELECT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,

BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate
--,*
FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

-- Create a big enough set of data for testing
DECLARE @.i INT
SET @.i = 1
WHILE (@.i < 30)
BEGIN
INSERT INTO CustomerListTom
SELECT TOP 15 PERCENT
CustomerID = RIGHT(cu.AccountNumber,6),
RegionID = RIGHT('000'+CAST(cu.TerritoryID+@.i AS VARCHAR(3)),3),
FirstName = ct.FirstName,
LastName = ct.LastName,
EmailAddress = ct.EmailAddress,
AddressLine = ad.AddressLine1,
AddressCity = ad.City,
AddressState = sp.StateProvinceCode,
AddressZip = ad.PostalCode,
AddressCountry = sp.[Name],
Phone = ct.Phone,
BillAddressLine = ad.AddressLine1,
BillAddressCity = ad.City,
BillAddressState = sp.StateProvinceCode,
BillAddressZip = ad.PostalCode,
BillAddressCountry = sp.[Name],
BillPhone = ct.Phone,

ModifiedDate = cu.ModifiedDate + CASE WHEN @.i > 3 THEN 10 ELSE -25 END + @.i

FROM Sales.Customer cu
JOIN Sales.Individual id ON id.CustomerID = cu.CustomerID
JOIN Person.Contact ct ON ct.ContactID = id.ContactID
JOIN Sales.CustomerAddress ca ON cu.CustomerID = ca.CustomerID
JOIN Person.Address ad ON ad.AddressID = ca.AddressID
JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID

SET @.i = @.i + 1
END

-- Cleanup - Delete Dups for PK
DELETE FROM CustomerListTom
WHERE CustomerID+RegionID IN (
SELECT CustomerID+RegionID
FROM CustomerListTom cu
GROUP BY CustomerID, RegionID
HAVING COUNT(*) > 1)

ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)

-- Create Views
GO
CREATE VIEW ViewATom
AS
SELECT *
FROM CustomerListTom cu
WHERE cu.RegionID = '004'
UNION
SELECT *
FROM CustomerListTom cu
WHERE CustomerID NOT IN
(SELECT CustomerID FROM CustomerListTom c2 WHERE c2.RegionID = '004')
AND (CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID IN
(SELECT MAX(CustomerID + CONVERT(char(8), ModifiedDate, 112) + RegionID)
FROM CustomerListTom
GROUP BY CustomerID))

GO
CREATE VIEW ViewBTom
AS
SELECT DISTINCT CustomerID
FROM CustomerListTom
GO
--return

USE AdventureWorks

-- FAILURE
-- This query FAILS to return in over 15 mins, cancelled
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- SOLUTIONS
-- Change WHERE TABLEB to WHERE TABLEA, this query returns in less than 1 second
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEA.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Remove RIGHT OUTER on TABLEB, this Query returns in less than 2 seconds
SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Drop PK and run ORIGINAL query, returns in less than 4 seconds

SELECT TABLEB.CustomerID,
TABLEC.RegionID,
TABLEC.LastName,
TABLEC.FirstName
FROM ViewATom TABLEA
RIGHT OUTER JOIN ViewBTom TABLEB ON TABLEA.CustomerID = TABLEB.CustomerID
RIGHT OUTER JOIN CustomerListTom TABLEC ON TABLEC.CustomerID = TABLEB.CustomerID
WHERE TABLEB.CustomerID IN
('012870','012997','011000','011001','011002','011003','011004','011005','011006','011007',
'011008','011009','011010','011011','011012','011013','011014','011015','011016','011017',
'011018','011019','011020','011150','011153','011144','017230','017220','017254','017257',
'025338','025333','025338','025397','025389','025424','025483','025485','025682','025673',
'029478','029405','029402','029317','029109','018393'
)

return

-- Create PK with NONCLUSTERED and run ORIGINAL query, returns in less than 1 second

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListTom]') AND name = N'PK_CustomerListTom')
ALTER TABLE [dbo].[CustomerListTom] DROP CONSTRAINT [PK_CustomerListTom]
GO
ALTER TABLE [CustomerListTom]
ADD CONSTRAINT [PK_CustomerListTom] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[RegionID] ASC
)
GO

return

Poor performance - Large memory consumption by app.

I have a SQL Server 2003sp3 running on a Windows 2003 server. The
users are experiencing performance problems on their apps that seem to
be related to large memory consumption in the sqlserver process.
We have two instances of SQL running. One running the old accounting
system, one running the new accounting software. Both are vertical
market proprietary apps, and I don't have much access the the inner
workings.
The problems seem to start when the service running the new app
starts to consume a lot of memory. While the old app will climb to
about 600Mg and stay there (read from Task Man), the new app will climb
to over 1.7 Gig. That's when things start to crawl. I reboot and
things return to normal, but the new app's memory usage continues to
creep up.
The software has been installed since February, but this just started
happening a few weeks ago. I'm not sure what could be causing this
(other than problems with the software itself, I've asked their tech
support about it, but haven't heard much back).
Everything else seems normal (all performance monitors are nominal).
The only other thing I've noticed that's strange are some errors in
SQLDIAG.txt that state: "This database optimized for 8 processes , this
has been exceeded by 2" I understand this is an error related to MSDE,
but I'm not running MSDE and have never run it on this machine. It's
always been SQLServer 2000.
I'm not really well educated on SQL Server, so I'm not sure where to
turn next. Any advice would be apreciated.
(The two apps in question are "Wind 2" and the problem child
"Vision", both AEC industry accouting/project management apps).
Thanks
JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 |
JAH222@.WTWARCH.COM
WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212
Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
Edition, else you wouldn't get that warning. These editions had a performance throttling mechanism
when > 8 concurrently executing queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:Ov7eVpVmHHA.4872@.TK2MSFTNGP03.phx.gbl...
> I have a SQL Server 2003sp3 running on a Windows 2003 server. The users are experiencing
> performance problems on their apps that seem to be related to large memory consumption in the
> sqlserver process.
> We have two instances of SQL running. One running the old accounting system, one running the new
> accounting software. Both are vertical market proprietary apps, and I don't have much access the
> the inner workings.
> The problems seem to start when the service running the new app starts to consume a lot of
> memory. While the old app will climb to about 600Mg and stay there (read from Task Man), the new
> app will climb to over 1.7 Gig. That's when things start to crawl. I reboot and things return to
> normal, but the new app's memory usage continues to creep up.
> The software has been installed since February, but this just started happening a few weeks ago.
> I'm not sure what could be causing this (other than problems with the software itself, I've asked
> their tech support about it, but haven't heard much back).
> Everything else seems normal (all performance monitors are nominal). The only other thing I've
> noticed that's strange are some errors in SQLDIAG.txt that state: "This database optimized for 8
> processes , this has been exceeded by 2" I understand this is an error related to MSDE, but I'm
> not running MSDE and have never run it on this machine. It's always been SQLServer 2000.
> I'm not really well educated on SQL Server, so I'm not sure where to turn next. Any advice would
> be apreciated.
> (The two apps in question are "Wind 2" and the problem child "Vision", both AEC industry
> accouting/project management apps).
> Thanks
> JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 | JAH222@.WTWARCH.COM
> WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212
>
>
|||Tibor Karaszi wrote:

> Read about sp_configure ands the "max server memory" setting. Also, you
> do have MSDE or Personal Edition, else you wouldn't get that warning.
> These editions had a performance throttling mechanism when > 8
> concurrently executing queries.
>
Huh. You're right, I have SQL Server Desktop Engine installed. No idea
why. It's an Proliant box, so maybe Compaq Insight Manager installed it.
With Max server memory, are you telling me to set this to limit the
amount of Ram this process uses? or to let it use it all? There are 4
Gig in the machine, and this is the most important program on this
machine, so I want to give it as many resources as I can.
Thanks
JIM HELFER | SYSTEMS ADMINISTRATOR
WTW ARCHITECTS
|||In your earlier post, you talk about "the old app" and "the new app", one stayed at 600MB and when
the other grew up to 1.7GB things got slow. Perhaps cap the "big" one at 1.5 GB? Or so...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:OYSU5FWmHHA.3704@.TK2MSFTNGP02.phx.gbl...
> Tibor Karaszi wrote:
>
> Huh. You're right, I have SQL Server Desktop Engine installed. No idea why. It's an Proliant
> box, so maybe Compaq Insight Manager installed it.
> With Max server memory, are you telling me to set this to limit the amount of Ram this process
> uses? or to let it use it all? There are 4 Gig in the machine, and this is the most important
> program on this machine, so I want to give it as many resources as I can.
> Thanks
> JIM HELFER | SYSTEMS ADMINISTRATOR
> WTW ARCHITECTS
|||Tibor Karaszi wrote:

> In your earlier post, you talk about "the old app" and "the new app",
> one stayed at 600MB and when the other grew up to 1.7GB things got slow.
> Perhaps cap the "big" one at 1.5 GB? Or so...
>
OK, I'll look into it. Thanks.
Jim Helfer

Poor performance - Large memory consumption by app.

I have a SQL Server 2003sp3 running on a Windows 2003 server. The
users are experiencing performance problems on their apps that seem to
be related to large memory consumption in the sqlserver process.
We have two instances of SQL running. One running the old accounting
system, one running the new accounting software. Both are vertical
market proprietary apps, and I don't have much access the the inner
workings.
The problems seem to start when the service running the new app
starts to consume a lot of memory. While the old app will climb to
about 600Mg and stay there (read from Task Man), the new app will climb
to over 1.7 Gig. That's when things start to crawl. I reboot and
things return to normal, but the new app's memory usage continues to
creep up.
The software has been installed since February, but this just started
happening a few weeks ago. I'm not sure what could be causing this
(other than problems with the software itself, I've asked their tech
support about it, but haven't heard much back).
Everything else seems normal (all performance monitors are nominal).
The only other thing I've noticed that's strange are some errors in
SQLDIAG.txt that state: "This database optimized for 8 processes , this
has been exceeded by 2" I understand this is an error related to MSDE,
but I'm not running MSDE and have never run it on this machine. It's
always been SQLServer 2000.
I'm not really well educated on SQL Server, so I'm not sure where to
turn next. Any advice would be apreciated.
(The two apps in question are "Wind 2" and the problem child
"Vision", both AEC industry accouting/project management apps).
Thanks
JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 |
JAH222@.WTWARCH.COM
WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212Read about sp_configure ands the "max server memory" setting. Also, you do h
ave MSDE or Personal
Edition, else you wouldn't get that warning. These editions had a performanc
e throttling mechanism
when > 8 concurrently executing queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:Ov7eVpVmHHA.4872@.TK2MSFTNGP03.phx.g
bl...
> I have a SQL Server 2003sp3 running on a Windows 2003 server. The users
are experiencing
> performance problems on their apps that seem to be related to large memory
consumption in the
> sqlserver process.
> We have two instances of SQL running. One running the old accounting sys
tem, one running the new
> accounting software. Both are vertical market proprietary apps, and I don
't have much access the
> the inner workings.
> The problems seem to start when the service running the new app starts t
o consume a lot of
> memory. While the old app will climb to about 600Mg and stay there (read
from Task Man), the new
> app will climb to over 1.7 Gig. That's when things start to crawl. I reb
oot and things return to
> normal, but the new app's memory usage continues to creep up.
> The software has been installed since February, but this just started hap
pening a few weeks ago.
> I'm not sure what could be causing this (other than problems with the soft
ware itself, I've asked
> their tech support about it, but haven't heard much back).
> Everything else seems normal (all performance monitors are nominal). The
only other thing I've
> noticed that's strange are some errors in SQLDIAG.txt that state: "This da
tabase optimized for 8
> processes , this has been exceeded by 2" I understand this is an error re
lated to MSDE, but I'm
> not running MSDE and have never run it on this machine. It's always been S
QLServer 2000.
> I'm not really well educated on SQL Server, so I'm not sure where to turn
next. Any advice would
> be apreciated.
> (The two apps in question are "Wind 2" and the problem child "Vision", b
oth AEC industry
> accouting/project management apps).
> Thanks
> JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 | JAH222@.
WTWARCH.COM
> WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212
>
>|||Tibor Karaszi wrote:

> Read about sp_configure ands the "max server memory" setting. Also, you
> do have MSDE or Personal Edition, else you wouldn't get that warning.
> These editions had a performance throttling mechanism when > 8
> concurrently executing queries.
>
Huh. You're right, I have SQL Server Desktop Engine installed. No idea
why. It's an Proliant box, so maybe Compaq Insight Manager installed it.
With Max server memory, are you telling me to set this to limit the
amount of Ram this process uses? or to let it use it all? There are 4
Gig in the machine, and this is the most important program on this
machine, so I want to give it as many resources as I can.
Thanks
JIM HELFER | SYSTEMS ADMINISTRATOR
WTW ARCHITECTS|||In your earlier post, you talk about "the old app" and "the new app", one st
ayed at 600MB and when
the other grew up to 1.7GB things got slow. Perhaps cap the "big" one at 1.5
GB? Or so...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:OYSU5FWmHHA.3704@.TK2MSFTNGP02.phx.g
bl...
> Tibor Karaszi wrote:
>
> Huh. You're right, I have SQL Server Desktop Engine installed. No idea wh
y. It's an Proliant
> box, so maybe Compaq Insight Manager installed it.
> With Max server memory, are you telling me to set this to limit the amoun
t of Ram this process
> uses? or to let it use it all? There are 4 Gig in the machine, and this i
s the most important
> program on this machine, so I want to give it as many resources as I can.
> Thanks
> JIM HELFER | SYSTEMS ADMINISTRATOR
> WTW ARCHITECTS|||Tibor Karaszi wrote:

> In your earlier post, you talk about "the old app" and "the new app",
> one stayed at 600MB and when the other grew up to 1.7GB things got slow.
> Perhaps cap the "big" one at 1.5 GB? Or so...
>
OK, I'll look into it. Thanks.
Jim Helfersql

Poor performance - Large memory consumption by app.

I have a SQL Server 2003sp3 running on a Windows 2003 server. The
users are experiencing performance problems on their apps that seem to
be related to large memory consumption in the sqlserver process.
We have two instances of SQL running. One running the old accounting
system, one running the new accounting software. Both are vertical
market proprietary apps, and I don't have much access the the inner
workings.
The problems seem to start when the service running the new app
starts to consume a lot of memory. While the old app will climb to
about 600Mg and stay there (read from Task Man), the new app will climb
to over 1.7 Gig. That's when things start to crawl. I reboot and
things return to normal, but the new app's memory usage continues to
creep up.
The software has been installed since February, but this just started
happening a few weeks ago. I'm not sure what could be causing this
(other than problems with the software itself, I've asked their tech
support about it, but haven't heard much back).
Everything else seems normal (all performance monitors are nominal).
The only other thing I've noticed that's strange are some errors in
SQLDIAG.txt that state: "This database optimized for 8 processes , this
has been exceeded by 2" I understand this is an error related to MSDE,
but I'm not running MSDE and have never run it on this machine. It's
always been SQLServer 2000.
I'm not really well educated on SQL Server, so I'm not sure where to
turn next. Any advice would be apreciated.
(The two apps in question are "Wind 2" and the problem child
"Vision", both AEC industry accouting/project management apps).
Thanks
JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 |
JAH222@.WTWARCH.COM
WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
Edition, else you wouldn't get that warning. These editions had a performance throttling mechanism
when > 8 concurrently executing queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:Ov7eVpVmHHA.4872@.TK2MSFTNGP03.phx.gbl...
> I have a SQL Server 2003sp3 running on a Windows 2003 server. The users are experiencing
> performance problems on their apps that seem to be related to large memory consumption in the
> sqlserver process.
> We have two instances of SQL running. One running the old accounting system, one running the new
> accounting software. Both are vertical market proprietary apps, and I don't have much access the
> the inner workings.
> The problems seem to start when the service running the new app starts to consume a lot of
> memory. While the old app will climb to about 600Mg and stay there (read from Task Man), the new
> app will climb to over 1.7 Gig. That's when things start to crawl. I reboot and things return to
> normal, but the new app's memory usage continues to creep up.
> The software has been installed since February, but this just started happening a few weeks ago.
> I'm not sure what could be causing this (other than problems with the software itself, I've asked
> their tech support about it, but haven't heard much back).
> Everything else seems normal (all performance monitors are nominal). The only other thing I've
> noticed that's strange are some errors in SQLDIAG.txt that state: "This database optimized for 8
> processes , this has been exceeded by 2" I understand this is an error related to MSDE, but I'm
> not running MSDE and have never run it on this machine. It's always been SQLServer 2000.
> I'm not really well educated on SQL Server, so I'm not sure where to turn next. Any advice would
> be apreciated.
> (The two apps in question are "Wind 2" and the problem child "Vision", both AEC industry
> accouting/project management apps).
> Thanks
> JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 | JAH222@.WTWARCH.COM
> WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212
>
>|||Tibor Karaszi wrote:
> Read about sp_configure ands the "max server memory" setting. Also, you
> do have MSDE or Personal Edition, else you wouldn't get that warning.
> These editions had a performance throttling mechanism when > 8
> concurrently executing queries.
>
Huh. You're right, I have SQL Server Desktop Engine installed. No idea
why. It's an Proliant box, so maybe Compaq Insight Manager installed it.
With Max server memory, are you telling me to set this to limit the
amount of Ram this process uses? or to let it use it all? There are 4
Gig in the machine, and this is the most important program on this
machine, so I want to give it as many resources as I can.
Thanks
JIM HELFER | SYSTEMS ADMINISTRATOR
WTW ARCHITECTS|||In your earlier post, you talk about "the old app" and "the new app", one stayed at 600MB and when
the other grew up to 1.7GB things got slow. Perhaps cap the "big" one at 1.5 GB? Or so...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:OYSU5FWmHHA.3704@.TK2MSFTNGP02.phx.gbl...
> Tibor Karaszi wrote:
>> Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
>> Edition, else you wouldn't get that warning. These editions had a performance throttling
>> mechanism when > 8 concurrently executing queries.
> Huh. You're right, I have SQL Server Desktop Engine installed. No idea why. It's an Proliant
> box, so maybe Compaq Insight Manager installed it.
> With Max server memory, are you telling me to set this to limit the amount of Ram this process
> uses? or to let it use it all? There are 4 Gig in the machine, and this is the most important
> program on this machine, so I want to give it as many resources as I can.
> Thanks
> JIM HELFER | SYSTEMS ADMINISTRATOR
> WTW ARCHITECTS|||Tibor Karaszi wrote:
> In your earlier post, you talk about "the old app" and "the new app",
> one stayed at 600MB and when the other grew up to 1.7GB things got slow.
> Perhaps cap the "big" one at 1.5 GB? Or so...
>
OK, I'll look into it. Thanks.
Jim Helfer

Friday, March 30, 2012

Poor Performing Query

Poor performance with NEWID()

Poor performance with NEWID()

Poor performance with NEWID()

Wednesday, March 28, 2012

Poor performance querying view from sproc

Poor performance - Large memory consumption by app.

Poor performance - Large memory consumption by app.

Poor performance - Large memory consumption by app.

SQL compact edition

Blog Archive

About Me