SQL compact edition: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Poor SQL Server Performance on Windows 2003 Server

I'm having a problem with SQL Server 2000 SP4 running on Windows 2003 Server
SP1. The performance is just awful.
I'm running a benchmark that creates a table and inserts 10000 records. This
is an ODBC application written in C++.
The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
seconds to run the application that inserts the records.
On my laptop which is a 2.0G Pentium M with 1G RAM the same application
takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
Now if you're not a very astute reader of benchmark results the bottom line
is that it takes 6 times longer to run this test on a high spec server
machine than on my laptop.
This is what I've done so far to try and fix this:
1. Try various configurations of processors and memory in the SQL
server configuration.
2. Reinstall SQL server.
3. Install Win2003 SP1.
4. Investigate hardware conflicts on the machine.
5. Benchmark disk performance. It's about twice as fast as the disk in
my laptop for both sequential and burst access.
So does anyone have any idea why the performance on this high spec server
would be so bad?
Thanks,
ColinA likely cause is that the server does not have disk write caching on the
SQL Server log but your laptop does. Assuming each insert is an individual
transaction, 10000 log I/Os are required. At a typical rate of 200 physical
disk I/Os per second, this comes to about 50 seconds.
A decent disk controller with write caching (and importantly battery backup)
ought to address your performance issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
> Server SP1. The performance is just awful.
>
> I'm running a benchmark that creates a table and inserts 10000 records.
> This is an ODBC application written in C++.
>
> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
> seconds to run the application that inserts the records.
>
> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>
> Now if you're not a very astute reader of benchmark results the bottom
> line is that it takes 6 times longer to run this test on a high spec
> server machine than on my laptop.
>
> This is what I've done so far to try and fix this:
>
> 1. Try various configurations of processors and memory in the SQL
> server configuration.
> 2. Reinstall SQL server.
> 3. Install Win2003 SP1.
> 4. Investigate hardware conflicts on the machine.
> 5. Benchmark disk performance. It's about twice as fast as the disk
> in my laptop for both sequential and burst access.
>
> So does anyone have any idea why the performance on this high spec server
> would be so bad?
>
> Thanks,
> Colin
>|||Thanks for your reply Dan.
I checked the disk write cache option in the drive policies in the Win2003
device manager and it was enabled.
I also checked the SCSI card setup and that was fine too.
I should have also mentioned that the number of rows per transaction is
10000. So there is basically only 1 transaction.
Cheers,
Colin.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>A likely cause is that the server does not have disk write caching on the
>SQL Server log but your laptop does. Assuming each insert is an individual
>transaction, 10000 log I/Os are required. At a typical rate of 200
>physical disk I/Os per second, this comes to about 50 seconds.
> A decent disk controller with write caching (and importantly battery
> backup) ought to address your performance issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
>> Server SP1. The performance is just awful.
>>
>> I'm running a benchmark that creates a table and inserts 10000 records.
>> This is an ODBC application written in C++.
>>
>> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
>> seconds to run the application that inserts the records.
>>
>> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
>> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>>
>> Now if you're not a very astute reader of benchmark results the bottom
>> line is that it takes 6 times longer to run this test on a high spec
>> server machine than on my laptop.
>>
>> This is what I've done so far to try and fix this:
>>
>> 1. Try various configurations of processors and memory in the SQL
>> server configuration.
>> 2. Reinstall SQL server.
>> 3. Install Win2003 SP1.
>> 4. Investigate hardware conflicts on the machine.
>> 5. Benchmark disk performance. It's about twice as fast as the disk
>> in my laptop for both sequential and burst access.
>>
>> So does anyone have any idea why the performance on this high spec server
>> would be so bad?
>>
>> Thanks,
>> Colin
>>
>|||What kind of drive is this in the Win2003 Server? Is it a drive array or
just a drive? Where are the log files located? A typical drive in a laptop
has more cache than one you normally find in a server. It can be several
times more in fact and this can make a huge difference when you have lots of
writes such as this. You also want the log drive on a physically separate
(not logically) drive than the data file. Even though the cache may be
enabled it may not be 100% write back. Normally they come as a 50/50 or
60/40 split between read and write cache. You can get a big performance
boost on writes by changing it to 100% write back.
Andrew J. Kelly SQL MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:OauuqrD2FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply Dan.
> I checked the disk write cache option in the drive policies in the Win2003
> device manager and it was enabled.
> I also checked the SCSI card setup and that was fine too.
> I should have also mentioned that the number of rows per transaction is
> 10000. So there is basically only 1 transaction.
> Cheers,
> Colin.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>>A likely cause is that the server does not have disk write caching on the
>>SQL Server log but your laptop does. Assuming each insert is an
>>individual transaction, 10000 log I/Os are required. At a typical rate of
>>200 physical disk I/Os per second, this comes to about 50 seconds.
>> A decent disk controller with write caching (and importantly battery
>> backup) ought to address your performance issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
>> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
>> Server SP1. The performance is just awful.
>>
>> I'm running a benchmark that creates a table and inserts 10000 records.
>> This is an ODBC application written in C++.
>>
>> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
>> seconds to run the application that inserts the records.
>>
>> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
>> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>>
>> Now if you're not a very astute reader of benchmark results the bottom
>> line is that it takes 6 times longer to run this test on a high spec
>> server machine than on my laptop.
>>
>> This is what I've done so far to try and fix this:
>>
>> 1. Try various configurations of processors and memory in the SQL
>> server configuration.
>> 2. Reinstall SQL server.
>> 3. Install Win2003 SP1.
>> 4. Investigate hardware conflicts on the machine.
>> 5. Benchmark disk performance. It's about twice as fast as the disk
>> in my laptop for both sequential and burst access.
>>
>> So does anyone have any idea why the performance on this high spec
>> server would be so bad?
>>
>> Thanks,
>> Colin
>>
>>
>|||It seems a bit suspect that your throughput is as if each insert was in an
individual transaction. The log i/o during the COMMIT is synchronous so
your app will wait for completion. How are you doing the inserts?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:OauuqrD2FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply Dan.
> I checked the disk write cache option in the drive policies in the Win2003
> device manager and it was enabled.
> I also checked the SCSI card setup and that was fine too.
> I should have also mentioned that the number of rows per transaction is
> 10000. So there is basically only 1 transaction.
> Cheers,
> Colin.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>>A likely cause is that the server does not have disk write caching on the
>>SQL Server log but your laptop does. Assuming each insert is an
>>individual transaction, 10000 log I/Os are required. At a typical rate of
>>200 physical disk I/Os per second, this comes to about 50 seconds.
>> A decent disk controller with write caching (and importantly battery
>> backup) ought to address your performance issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
>> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
>> Server SP1. The performance is just awful.
>>
>> I'm running a benchmark that creates a table and inserts 10000 records.
>> This is an ODBC application written in C++.
>>
>> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
>> seconds to run the application that inserts the records.
>>
>> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
>> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>>
>> Now if you're not a very astute reader of benchmark results the bottom
>> line is that it takes 6 times longer to run this test on a high spec
>> server machine than on my laptop.
>>
>> This is what I've done so far to try and fix this:
>>
>> 1. Try various configurations of processors and memory in the SQL
>> server configuration.
>> 2. Reinstall SQL server.
>> 3. Install Win2003 SP1.
>> 4. Investigate hardware conflicts on the machine.
>> 5. Benchmark disk performance. It's about twice as fast as the disk
>> in my laptop for both sequential and burst access.
>>
>> So does anyone have any idea why the performance on this high spec
>> server would be so bad?
>>
>> Thanks,
>> Colin
>>
>>
>

Poor SQL Server Performance on Windows 2003 Server

Poor Server Performance When Running Data Driven Subscriptions

We are seeing poor server performance when running simple data driven
subscriptions. The subscription references a simple view that provides
parameter data. When we run the subscription, we see disk usage and memeory
usage increase dramatically on the server and the server becomes very slow
(almost to the point where other activity on the server stops).
Has anyone seen this behavior and does anyone have any recommendations?We are experiencing the same performance issue, our server gets 100% busy
when we run subscriptions, also, some subscriptions never get completed.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 On Cross-Server Join

Was running cross-server queries between SQL65 and SQL2000, query ran fine. Upgraded the SQL65 server to SQL2000, the cross-server queries starting running extremly slow. Nothing has changed but the upgrade to SQL200, can some one help why my cross-server queries are taking so long.perhaps delete & recreate the linked server definition (if it is the same one as created to the 6.5 server)|||Actually, after my search for info on this found nothing, I dropped and recreated all the links between the two servers and that did fix the issue.

So your advice was a good one

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

Poor performance

I have the following structure:

At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

Did you check 'Collation Compatible' option in the linked server options?

|||

Yes I did. This option is defined "FALSE". Is It right?

Tks

|||It should be set to TRUE.

Poor performance

I have the following structure:

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

Did you check 'Collation Compatible' option in the linked server options?

|||

Yes I did. This option is defined "FALSE". Is It right?

Tks

|||It should be set to TRUE.

Poor Linked Server performance when running a query with a variable

Hi
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
--
Regards
Steen Schlüter Persson
Database Administrator / System AdministratorWell, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus|||On Mar 28, 7:12 pm, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi
> We are facing a quite strange performance issue when using a linked
> server to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER =3D '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr =3D '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER =3D @.medanr
> This query runs for approx. 14 minutes and returns the same number of row=s=2E
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that
> the problem is that Oracle can't use an index when the query contains a
> WHERE clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded
> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
> the reason, but to verify this we have installed a new server with
> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
> version as we have on the affected server - 8.00.818) but we are facing
> the same problem with this new server.
> It could of course also be caused by the Oracle server, but we have
> tried it against 2 different Oracle databases (different applications)
> running on different physical servers and in both cases the problem are
> the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
We had the same problem . If you check dynamic Properties and nested
query in provider options , it may improve query performance for
variables . These options are available when you create link server|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> Hi
>> We are facing a quite strange performance issue when using a linked
>> server to an Oracle server.
>> When we run a query containing a WHERE clause using a variable, it takes
>> ages for SQL server to return the data. When we instead just type in the
>> value for the WHERE clause it returns the data as fast as expected.
>> The queries we have used for testing is these:
>> 1:
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
>> EMPLOYEENUMBER = '06131'
>> This query returns 6165 rows in 5 seconds which is normal performance.
>> 2:
>> declare @.medanr varchar(255)
>> set @.medanr = '06131'
>> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
>> EMPLOYEENUMBER = @.medanr
>> This query runs for approx. 14 minutes and returns the same number of rows.
>> We can see that the difference in execution times increases with the
>> number of rows the table contains. Without being sure, we suspect that
>> the problem is that Oracle can't use an index when the query contains a
>> WHERE clause using a variable, but why?
>> It have worked for us before (actually the code giving problems has been
>> running for years).
>> What has happened recently, is that the SQL server has been upgraded
>> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
>> the reason, but to verify this we have installed a new server with
>> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
>> version as we have on the affected server - 8.00.818) but we are facing
>> the same problem with this new server.
>> It could of course also be caused by the Oracle server, but we have
>> tried it against 2 different Oracle databases (different applications)
>> running on different physical servers and in both cases the problem are
>> the same.
>> Furthermore we have also tried it from a SQL2005 server against the same
>> Oracle databases but with same problem.
>> Right now we are running out if ideas and don't really know what to go
>> for..........
>> Has any one seem similar issues or has any good ideas?
>> --
>> Regards
>> Steen Schlüter Persson
>> Database Administrator / System Administrator
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Poor Linked Server performance when running a query with a variable

Hi
We are facing a quite strange performance issue when using a linked
server to an Oracle server.
When we run a query containing a WHERE clause using a variable, it takes
ages for SQL server to return the data. When we instead just type in the
value for the WHERE clause it returns the data as fast as expected.
The queries we have used for testing is these:
1:
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
EMPLOYEENUMBER = '06131'
This query returns 6165 rows in 5 seconds which is normal performance.
2:
declare @.medanr varchar(255)
set @.medanr = '06131'
select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
EMPLOYEENUMBER = @.medanr
This query runs for approx. 14 minutes and returns the same number of rows.
We can see that the difference in execution times increases with the
number of rows the table contains. Without being sure, we suspect that
the problem is that Oracle can't use an index when the query contains a
WHERE clause using a variable, but why?
It have worked for us before (actually the code giving problems has been
running for years).
What has happened recently, is that the SQL server has been upgraded
from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
the reason, but to verify this we have installed a new server with
SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
version as we have on the affected server - 8.00.818) but we are facing
the same problem with this new server.
It could of course also be caused by the Oracle server, but we have
tried it against 2 different Oracle databases (different applications)
running on different physical servers and in both cases the problem are
the same.
Furthermore we have also tried it from a SQL2005 server against the same
Oracle databases but with same problem.
Right now we are running out if ideas and don't really know what to go
for..........
Has any one seem similar issues or has any good ideas?
Regards
Steen Schlter Persson
Database Administrator / System AdministratorWell, I am pretty sure that I know what has happened, but not why. Of
course, every SP includes optimizer and other engine updates, so things do
change. Here is what I think is happening:
Where EMPLOYEENUMBER = '06131' -- Evaluated on the Oracle side of the link
Where EMPLOYEENUMBER = @.medanr -- Evaluated on the SQL Server side of the
link.
You should be able to trace the communication between the servers to see how
much I/O is happening between the servers. I believe that to evaluate
@.medanr the Oracle server is delivering every row from
MACONOMY..RAMAIN60.TIMESHEETL to the SQL Server so that the filter can be
applied.
To test this out, you could try first pushing the filter value to a work
table on Oracle. Then Oracle can do the whole join on its side and return
only the 6165 rows, by:
INSERT INTO MACONOMY..RAMAIN60.TIMESHEETWORKTABLE
(FilterValue) VALUES ('06131')
select T.PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE T
JOIN MACONOMY..RAMAIN60.TIMESHEETWORKTABLE W
ON T.EMPLOYEENUMBER = W.EMPLOYEENUMBER
If that gives you the performance you were used to, then the problem has
been identified. Maybe that will help you know what avenue you want to
pursue.
RLF
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:e1dCtMUcHHA.4388@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are facing a quite strange performance issue when using a linked server
> to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER = '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr = '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER = @.medanr
>
> This query runs for approx. 14 minutes and returns the same number of
> rows.
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that the
> problem is that Oracle can't use an index when the query contains a WHERE
> clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded from
> SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be the
> reason, but to verify this we have installed a new server with SQL2000
> SP3a + MS03-031 Security Fix (which ends up with the same version as we
> have on the affected server - 8.00.818) but we are facing the same problem
> with this new server.
> It could of course also be caused by the Oracle server, but we have tried
> it against 2 different Oracle databases (different applications) running
> on different physical servers and in both cases the problem are the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator|||I agree with Russell.
To avoid issues like this try using Openquery.
SELECT *
FROM OPENQUERY(OracleSvr, 'declare @.medanr varchar(255)
set @.medanr = '''06131'''
select PERIODSTART from RAMAIN60.TIMESHEETLINE
Where EMPLOYEENUMBER = @.medanr
')
GO
Markus|||On Mar 28, 7:12 pm, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi
> We are facing a quite strange performance issue when using a linked
> server to an Oracle server.
> When we run a query containing a WHERE clause using a variable, it takes
> ages for SQL server to return the data. When we instead just type in the
> value for the WHERE clause it returns the data as fast as expected.
> The queries we have used for testing is these:
> 1:
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE where
> EMPLOYEENUMBER =3D '06131'
> This query returns 6165 rows in 5 seconds which is normal performance.
> 2:
> declare @.medanr varchar(255)
> set @.medanr =3D '06131'
> select PERIODSTART from MACONOMY..RAMAIN60.TIMESHEETLINE Where
> EMPLOYEENUMBER =3D @.medanr
> This query runs for approx. 14 minutes and returns the same number of row=
s=2E
> We can see that the difference in execution times increases with the
> number of rows the table contains. Without being sure, we suspect that
> the problem is that Oracle can't use an index when the query contains a
> WHERE clause using a variable, but why?
> It have worked for us before (actually the code giving problems has been
> running for years).
> What has happened recently, is that the SQL server has been upgraded
> from SQL2000 SP3a (8.00.818) to SQL 2000 SP4. This could of course be
> the reason, but to verify this we have installed a new server with
> SQL2000 SP3a + MS03-031 Security Fix (which ends up with the same
> version as we have on the affected server - 8.00.818) but we are facing
> the same problem with this new server.
> It could of course also be caused by the Oracle server, but we have
> tried it against 2 different Oracle databases (different applications)
> running on different physical servers and in both cases the problem are
> the same.
> Furthermore we have also tried it from a SQL2005 server against the same
> Oracle databases but with same problem.
> Right now we are running out if ideas and don't really know what to go
> for..........
> Has any one seem similar issues or has any good ideas?
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
We had the same problem . If you check dynamic Properties and nested
query in provider options , it may improve query performance for
variables . These options are available when you create link server|||M A Srinivas wrote:
> On Mar 28, 7:12 pm, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> We had the same problem . If you check dynamic Properties and nested
> query in provider options , it may improve query performance for
> variables . These options are available when you create link server
>
Thanks for all the inputs. We have already been down the route with
the various providers options, and as far as I remember we have already
tried with the "Nested queries" option but to no avail.
Using OPENQUERY could be an option if it was a new Stored Proc or code
we where writing, but at the moments we have hundreds of SP's where
those linked servers are involved so it's not that easy to rewrite the
all these SP's.
Russels suspicion about how the where clause is being evaluated, is
along the lines we have found out as well based on the execution plan
for the query. I don't know if we can rely on that, but the execution
plan also indicates that when we are using a variable, all the rows from
the Oracle table is being retrieved and then the filter is being applied
on the SQL server side. This still doesn't help us a lot because it's
not that easy to rewrite the code and also it puzzles me why it has
happened in the first place. Especially because we have now set up a new
server with the same SQL server version/SP level as the affected server
had before we applied SP4, but that still causes the same problem.
I'll look into some of your ideas/suggstion tomorrow to see if anything
can point us in he right direction.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Monday, March 26, 2012

Polish characters displayed incorrect after post

Greetings,

I have a SQL server 2000 running on an english win2000 workstation. In a
database I have a table where one varchar column is set to polish
collation.
Regional settings for the system is polish.
Data entered in a client application looks fine until they are posted.
When reading the data with the client application, the special polish
characters are incorrect, they appears as e.g. '1' and '3'.
The strange thing is that when I use query analyzer to look at the data,
then the polish characters appears as they should!
My client app use ADO, the SQLOLEDB provider. I have tried to use
'Locale Identifier=xxxx' in the connection string, without any luck.
If I change the column to be nvarchar instead of varchar, then it work,
but unfortunately, this solution is not an option. This should work on
varchar columns, since polish is not multibyte.

What am I doing wrong??

TIA
Best regards
Philip KofoedPhilip Kofoed (kofoed@.tiscali.dk) writes:
> I have a SQL server 2000 running on an english win2000 workstation. In a
> database I have a table where one varchar column is set to polish
> collation.
> Regional settings for the system is polish.
> Data entered in a client application looks fine until they are posted.
> When reading the data with the client application, the special polish
> characters are incorrect, they appears as e.g. '1' and '3'.
> The strange thing is that when I use query analyzer to look at the data,
> then the polish characters appears as they should!
> My client app use ADO, the SQLOLEDB provider. I have tried to use
> 'Locale Identifier=xxxx' in the connection string, without any luck.
> If I change the column to be nvarchar instead of varchar, then it work,
> but unfortunately, this solution is not an option. This should work on
> varchar columns, since polish is not multibyte.

You say that the regional settings of the system are Polish, but which
system are you talking about?

What are the regional settings of the machine where the client application
runs? If that machine has for instance Danish settings, the Polish
characters will indeed be converted.

Could you give more examples on how the various Polish characters are
displayed as? How is a-ogonek displayed, c-acute, etc?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> You say that the regional settings of the system are Polish, but which
> system are you talking about?

The PC where _both_ the sql server and the client app runs, client app is
running on the same PC as the sql server. Windows is english, but with polish
regional settings. Collation of the varchar column where I'm saving the polish
string is polish.

> Could you give more examples on how the various Polish characters are
> displayed as? How is a-ogonek displayed, c-acute, etc?

I have placed some screenshots here:
http://www.provosoft.com/sql/sql.html

Data are stored correct, since query analyzer reads and displays data correct.
So the problem must be somewhere else.

Best regards
Philip Kofoed|||Philip Kofoed (kofoed@.tiscali.dk) writes:
> The PC where _both_ the sql server and the client app runs, client app
> is running on the same PC as the sql server. Windows is english, but
> with polish regional settings. Collation of the varchar column where I'm
> saving the polish string is polish.

But is Polish the default locale for the server? Or is it just the
setting for the user you are logged in as?

And what value do you have in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Nls\CodePage\ACP?
Does it say 1252 or 1250?

Looking at the examples, it seems clear that when the data is received
from SQL Server is taken to be Latin-1 data, and then there is a
conversion to CP1250, using fallback characters. For instance L-slash
is 163, which is the pound sign in Latin-1, whence the L. l-slash is
179 in Latin-2, while in Latin-1 this code point is 3-superscript, so
3 is used as the fallback.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But is Polish the default locale for the server? Or is it just the
> setting for the user you are logged in as?

Default locale is Polish. 'Language settings for the system' set to Central
Europe (default), 'Settings for current user' set to Polish locale.

> And what value do you have in
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Nls\CodePage\ACP?
> Does it say 1252 or 1250?

1250, i.e eastern europe.

I just tried running my client app on a polish win XP. Client app then
connects to sql server running on english win 2000, default locale polish.
Characters are _still_ corrupted??
Query Analyzer still displays characters correct.

I'm going to leave this problem, and send it back the support staff. Thank you
for your time and effort, Erland!

Best regards
Philip Kofoed|||Philip Kofoed (kofoed@.tiscali.dk) writes:
>> But is Polish the default locale for the server? Or is it just the
>> setting for the user you are logged in as?
> Default locale is Polish. 'Language settings for the system' set to
> Central Europe (default), 'Settings for current user' set to Polish
> locale.

The last straw:

SELECT serverproperty('Collation'),
databasepropertyex('yourdb', 'Collation')

And exactly how do the queries submitted by the application look like?

I refuse to believe that the language of Windows should matter.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

> I refuse to believe that the language of Windows should matter.

I have found out that if I create a new database with polish collation
then it works on english windows then regional settings is polish.
If collation of the DB isn't polish, but a column has polish collation,
then characters read from that column is incorrect.

Our installation crew that installed the original server in Poland,
installed it with Latin collation, realized their mistake, and used DTS to
copy the DB to a new DB with polish collation, and characters in the new
DB was dispalyed incorrect. I haven't tested this last scenario, because
our problem is solved when using polish collation when creating the DB.

Thanks again for all your help! :)

Best regards
Philip Kofoed|||Philip Kofoed (kofoed@.tiscali.dk) writes:
> I have found out that if I create a new database with polish collation
> then it works on english windows then regional settings is polish.
> If collation of the DB isn't polish, but a column has polish collation,
> then characters read from that column is incorrect.

Don't really see why this is happening if you are getting data directly
from the table. But if you for some reason first get data into local
variables, it's obvious, as local variables always have the collation
of the database.

Anyway, you got it working and that's the main thing.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 30, 2012

Poor SQL Server Performance on Windows 2003 Server

Poor SQL Server Performance on Windows 2003 Server

Poor SQL Server Performance on Windows 2003 Server

Poor Server Performance When Running Data Driven Subscriptions

Wednesday, March 28, 2012

Poor performance querying view from sproc

Poor Performance On Cross-Server Join

Poor performance - Large memory consumption by app.

Poor performance - Large memory consumption by app.

Poor performance - Large memory consumption by app.

Poor performance

Poor performance

Poor Linked Server performance when running a query with a variable

Poor Linked Server performance when running a query with a variable

Poor Linked Server performance when running a query with a variable

Monday, March 26, 2012

Polish characters displayed incorrect after post

SQL compact edition

Blog Archive

About Me