Poor Performance

Jul 23, 2005

Hello Gurus,

SQL Server 2000
Windows 2003 Server, Standard Edition.

Firstly I'm not a SQL server DBA but have a little experience with
Oracle 9i and Oracle Rdb.

An application that I've inherited has started performing very very
slowly over the last few days, as far as I know there have been no
modifications or changes in the volume of data the db is holding. The
database is in simple logging mode and it's updated twice per day from
and Oracle Rdb database.

The SQL database is all in the primary filegroup and is sat on four
disks (RAID mirrored) which form one logical disk of 128gb. It
consists of approx 120 tables, each with a primary key and a number of
unique indicies per table.

Apart from a twice a day update in the small hours the only access to
this database is read only. Users are connecting via the web and
using predefined Crystal reports to retrieve data.

Questions then:

Should this db have more than one filegroup and should I put the
indicies in a different group? Is this relevant when the underlying
storage is RAID?

Should it be using mirrored RAID or should it be striped or should I
steer clear of RAID for a database?

The indicies have not been rebuilt for some time (probably one year).
Is this something I should be doing once per day/week as I do have the
downtime window?

Should I be doing a database/table reorg at fairly regular intervals?

I'm going to generate a workload file from SQL profiler and see what,
in conjunction with the Index tuning wizard it suggests.

Appreciate that poor performance & tuning is a huge subject and not an
exact science but any tips or comments would be gratefully received.

Regards
Dave.

View 2 Replies


ADVERTISEMENT

Extremely Poor Query Performance - Identical DBs Different Performance

Jun 23, 2006

Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***

View 2 Replies View Related

Very Poor Performance - Identical DBs But Different Performance

Jun 22, 2006

Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T

View 2 Replies View Related

SQL 7 Poor Performance

Apr 4, 2000

Hi,
I have just upgrade my sql 6.5 to 7.0 version. This sql box (compaq proliant 5500) has 1 gig ram and 4 pentium pro processors and smart array controller. There are about 200 users hit on that server constantly. I configured this server according compaq white paper and Microsoft recommendation; however, I am still suffering huge performance hit. Then I setup performance monitor to see what is happening. What I see is all of 4 processors are at 70% processor time constantly. What I heard is sql 7.0 runs much better on pentium III processors. Is that true? Or you have any recommendations??


Help!!
Thanks!!

Jim Zhong

View 6 Replies View Related

Poor Performance

Sep 24, 2007

Hi

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

View 3 Replies View Related

Poor Performance

Feb 4, 2008

Is there a way to improve this query?
The execution plan states that it's ,aking use of the Indexes as 'Clustered Index Seeks'
but the query takes 30 minutes to complete.
The Index statistics are also up to date!
If I use just an INNER JOIN the query completes in 2 seconds!
How can I make the LEFT OUTER JOIN more optimal?
SELECT A.MeterSerial AS 'PeaceMeter', B.MeterFrom, B.MeterTo,
CASE WHEN A.MeterSerial = B.MeterFrom
OR A.MeterSerial = B.MeterTo
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
THEN 'Y' ELSE 'N' END AS 'ComplexMeter', B.ComplexMeterType
FROM Peace.MeterData_DR1 A LEFT OUTER JOIN
(SELECT MeterFrom, MeterTo, ComplexMeterType
FROM Peace.ComplexMeters2) B ON A.MeterSerial = B.MeterFrom
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
WHERE A.MeterSerial != ''

View 3 Replies View Related

Poor Performance From Web Server

Dec 18, 2007

Hi,I am having a problem with one of my stored procedures in SQL Server 2005.  Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET. I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it.  More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig.  I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.If anyone has got any ideas on this then please let me know as I am completely stuck.  I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...Thanks in advance Tom    

View 1 Replies View Related

SSAS Poor Performance

Jul 9, 2007

Hi,

I created a cube in my development box, tested, performance is great, now if I try to deploy it into the production server from VS, the screen freezes without any error msg... after some time playing around with security I gave up and created a backup of the cube, and sent it over to the IT department( I have no direct control over the production server so they restored the cube ). Now with the cube in production, it was a matter of just processing it... well that didn't go well either... it would take forever from my development box to see results... then I tried to browse any dimension using the data already on the backup... a simple 4 values dimension would take 2-4 minutes to load on screen...

I can't understand why browsing the cube its so slow in the production server, IT admin even reported that when trying to browse locally it would be slow too... The server has 16gbs of RAM and its a dual processor, he didnt notice any lack of CPU or memory while browsing the cube...

Have you experienced this or can you help me troubleshoot whats going on?

View 1 Replies View Related

Poor Performance Due To LCK_M_S?

Nov 13, 2007

We just converted from SQL Server 2000 to SQL SERVER 2005 and it seems as though we are having trouble with our performance. Sets of queries that used to take about 15 seconds now take almost 2 minutes. We used a utility to find out what was taking so long and found that almost all of the wait time belonged to LCK_M_S. Does anyone have any suggestions?

Here is a snippet of code I used to test the speed against 2000:

declare @counter int
set @counter = 1

while @counter < 200
BEGIN
update UPR40500 set actindx = 96 where actindx = 96
set @counter = @counter+1
END

This takes 15 seconds when it used to take virtually no time at all.

View 3 Replies View Related

Poor Index Performance

Mar 24, 2007

Hey guys,

Having some trouble with indexes on sql server 2005. I'll explain it with a simplified example.
I have a customers table, and a sp to list customers :


create table Customers(
CusID int not null,
Name varchar(50) null,
Surname varchar(50) null,
CusNo int not null,
Deleted bit not null
)


create proc spCusLs (
@CusID int = null,
@Name varchar(50) = null,
@Surname varchar(50) = null,
@CusNo int = null
)
as

select
CusID,
Name,
Surname,
CusNo
from
Customers
where
Deleted = 0
and CusID <> 1000
and (@CusID is null or CusID = @CusID)
and (@CusNo is null or CusNo = @CusNo)
and (@Name is null or Name like @Name)
and (@Surname is null or Surname like @Surname)
order by
Name,
Surname


create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary



I've recently noticed that some tables, including 'Customers' don't have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like ('joh%' '%') but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using 'alter', works ok with CusNo, but not with name/surname. Recompile it, and it's reversed again. When run as a single query, the execution plan looks different.

What's happening? Perhaps something to do with statistics? This doesn't have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before...

View 1 Replies View Related

Poor Performance On Oracle

Aug 3, 2006

Attention: To all SSIS gods like Donald Farmer, Jamie Thompson, Michael Entin

I am so very disappointed with the performance of SSIS on Oracle. When I am doing a simple lookup, it is caching the entire lookup table that is killing my performance. What is worst? When I try to change from Full to Partial or None caching mode, the component throws an error (x) sign.

I am also so frustrated with the way I have warnings all over the place. The warnings are saying code page value for the column cannot be determined. But the code page value is defaulted to 1252. I looked around everywhere to find what the damn code page value of my Oracle database is without any luck.

The damn package that I created takes 10 minutes to process 10,000 records ONLY which is slower than a legacy Cognos Decision Stream!!! Unacceptable!!! My lookup tables on an average have 200,000 records and I DO NOT WANT TO CACHE THEM all for 10,000 records. Something seems messed up!!!

PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!

View 4 Replies View Related

Linked Server = Poor Performance

Jan 10, 2006

Using a query through a linked server is giving tremendously reduced performance.
Is part of the problem linking from a SQL 2000 to a SQL 7.0 database?
Are there any other tips out there?
Thanks.

View 1 Replies View Related

Poor Sql Performance In Oledb Source?

Mar 31, 2008

i need to select data by using a very complex sql statement. when i use a ole db source componente and choose SQL command as data access mode the process never ends. but when i put the sql statement in an sql task component it works fine and fast. isn't an oledb source always based on an sql statement (select *)? so how is it possible that this component becomes so slow?

View 11 Replies View Related

Poor Performance On Sql 2005 Vs. Sql 2000 - AGAIN!

May 15, 2008

I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.

I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.

1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).
2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.
3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)
4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.

I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.


SELECT InterimView.* ,TestView.*

FROM View_LabDataExport_TestFormData_55 TestView
RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.*
FROM View_LabDataExport_FormData_55 ReqView
LEFT OUTER JOIN View_LabDataExport_FormData_CD_55 CDView
ON ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID )

) InterimView

ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND

InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T )

The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list.

Here is the statistics ion on 2005


(21234 row(s) affected)

Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here is the statistics ion on 2000

Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0.

Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0.

Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0.

Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0.

Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.

Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.

Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0.

Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0.

Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24.

Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0.

Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4.

Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10.

Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308.

Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0.

Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701.

Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16.

Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0.

Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0.

Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0.

Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2.

Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23.

Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4.

Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306.

Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047.

(21234 row(s) affected)

(147 row(s) affected)


One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask.

What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general.

Thanks

View 20 Replies View Related

Merge Join's Poor Performance

Sep 4, 2006

Hello All,

I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.

Maybe someone had a little more luck with it?



View 7 Replies View Related

SQL 2000 Poor Performance On Windows 2003

Mar 30, 2006

Hi
we have a windows 2003 server with 4 xeon 3Gh and 8 gb of memory.
Now, when run a query or when click for properties is slowwwww very slow.

No error in the event view

Any idea
TIA
Abel
:angel:

View 4 Replies View Related

Poor Performance On Dual Xeon Machine

Jul 23, 2005

Hi,I've been creating a db application using MS Access and MSDE. Only twoof us are using the application, and the server and the app both rungreat on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problemis when I take my laptop home, my coworker loses access to the server.We recently purchased a dedicated server to run the db on at theoffice. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We alsobought SQL Server, but I installed the Personal Edition becuase we arenot using a server OS. It's my understanding that XP can utilize bothprocessors, and the Personal Edition can use both processors as well.(On a side note, why is Enterprise Manager showing that I have 4processors - why?) In addition, I understand PE has a work-loadgovernor that cripples performance when more than 5 TSQL commands arebeing run simultaneously.I backed up the db on my laptop and restored it on our new server. Butwhen I run the exact same queries with the exact same number of rows,my queries on the new server are take 3x longer(!?). Can someoneplease offer a few suggestions for why this is happening? What can Ido to improve performance on the server machine?Please let me know if I need to supply more information.Thanks,Alex

View 15 Replies View Related

Extremely Poor Performance On OLE DB Command Transform.

May 1, 2006

In one Data Flow Task (running by itself) I simply have a Raw File Source pushing rows to an OLE DB Command. This command executes an UPDATE command (UPDATE table SET field = ?, anotherfield = ? WHERE thisfield = ?) and performs extremely slow. It's possible to have 62K+ rows needing to be updated and it typically takes this task around 25 - 30 minutes to run.

Is there anything I can do to increase performance?
Are there any options other than the OLE DB Command to perform updates?

Thank you.

View 1 Replies View Related

Replication Monitor Reveals Poor Performance - But What To Do To Fix It?

Jun 29, 2006

The client production server CPU starts thrashing. Task manager indicates that SQL server is gobbling CPU cycles. Having a look at the replication monitor, it is obvious that indivual synchronisations to the mobile devices are taking significantly longer expected.

Observing an indivual synchronisation attempt, the Upload changes to Publisher rows are very quickly resolved.

The Download changes to Subscriber seems to take up a very long time.

Along the way, the estimated completion does a few interesting things, like going from 100% complete with no estimated time to complete, back to seomthing like 77% with 2 minutes left to complete.

This sort of behaviour occurs when there are only a hundred rows to download.

Synchronisations for minimal amounts of data suddenly taking anywhere from 2 to 15 minutes. Totally unacceptable form the client perspective but seems that 2005 behaves quite different from 2000 and the tricks are yet to reveal themselves.

Note - It is a server hardware issue as there is in excess of 3 GB ram, the database is on a SAN and there are 4 3Ghz CPUS in operation.


Any possible help appreciated as this issue is beginning to drag on.

View 4 Replies View Related

Oracle Linked Server Poor Performance

Apr 16, 2007

I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.



I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.



The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.



Any ideas would be appreciated

View 5 Replies View Related

Poor Performance - Nested Views && Complex Joins

Sep 27, 2006

The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?

SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

Thanks, Dave

View 9 Replies View Related

Poor Performance For Business Day Calculation From Aspfaq Sample

Jul 23, 2005

Greeting, below is the complete SQL taken from aspfaq.com (retrievedfrom this newsgroup I believe) The query takes about two minutes torun. Does anybody have a better set based way (sub-second response) todetermine business days?CREATE TABLE dbo.Calendar(dt SMALLDATETIME NOT NULL PRIMARY KEYCLUSTERED, -- Date valueIsWeekday BIT,-- Is this date a weekday (M -F)IsHoliday BIT,-- Is this date a holidayY SMALLINT,-- Year the date falls inFY SMALLINT,-- Fiscal Year (needed?)Q TINYINT,-- Quarter date falls inM TINYINT,-- Numeric month of dateD TINYINT,-- Numeric day of dateDW TINYINT,-- Numeric DayOfWeek(Sunda=1,Monday=2)MonthName VARCHAR(9),-- String name of monthDayName VARCHAR(9),-- String name of dayW TINYINT-- Week number)GO-- Start & End DatesDECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = DATEADD(d, -1, '20000101')SET @EndDate = DATEADD(d, -1, '20300101')-- Total number of dates to generateDECLARE @Days INTSET @Days = DATEDIFF(DAY, @StartDate, @EndDate)-- Create temporary Numbers tableCREATE TABLE #Numbers(Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED)-- Insert a number into our temp table for each date to be generatedWHILE COALESCE(SCOPE_IDENTITY(), 0) <= @DaysBEGININSERT #Numbers DEFAULT VALUESEND-- Generate a date for each day in our timespanINSERT Calendar(dt)SELECT DATEADD(DAY, Number, @StartDate)FROM #NumbersWHERE Number <= @DaysORDER BY Number-- Remove the temporary Numbers tableDROP TABLE #NumbersGO-- Update other columnsUPDATE dbo.Calendar SETIsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0ELSE 1 END,IsHoliday = 0,Y = YEAR(dt),FY = YEAR(dt),Q = CASEWHEN MONTH(dt) <= 3 THEN 1WHEN MONTH(dt) <= 6 THEN 2WHEN MONTH(dt) <= 9 THEN 3ELSE 4 END,M = MONTH(dt),D = DAY(dt),DW = DATEPART(DW, dt),MonthName = DATENAME(MONTH, dt),DayName = DATENAME(DW, dt),W = DATEPART(WK, dt)-- Query in question (takes almost 2 minutes to execute and return avalue)SELECTC.dtFROMCalendar CWHEREC.IsWeekDay = 1AND C.IsHoliday = 0AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )

View 18 Replies View Related

SQL Server 2005 Express Edition, Very Poor Performance

Nov 14, 2007



Hi,

I have upsized my access database to Mssql 2005 and I notice that SQL 2005 is much slower in data access than the access databse.

Is there something that needs to be done after the upsize? Have I missed something?

If you want to compare the dataabses here are the links:

Access: http://ozeldersverenler.net/beta/www
SQL2005: http://ozeldersverenler.net/alpha/www



View 3 Replies View Related

Poor Performance Temporarily Cured By Re-booting SQL Server

Feb 27, 2007

Poor Performance Temporarily Cured by Re-booting SQL Server

Please can you help?

SQL Server 2000.
Accessed by VB.Net 2003.
Server set up by SQL consultant (who is no longer available).

We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).

The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.

Additional information:
Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.

View 5 Replies View Related

Sending Blob Field To Client In Chunks - Poor Performance On W2K And OS X

May 6, 2004

I'm using the code below to send files that are in a blob file in my database to the browser client. The code sends the file in chunks in order to increase performance. The file I'm using to test with is 7MB. It works great on Windows XP with any browser. It takes virtually the same amount of time compared to downloading the file directly from the webserver. However, Windows 2000 and Mac OS X both take about 4x the amount of time it takes to download the file on XP machines. Why the performance difference? Is there anything I can do to fix this? I tried downloading the file directly from the webserver instead of getting it out of the database and it takes the same amount of time on all 3 OS. I had the same problem on Windows XP when I wasn't sending the file in chunks, but after using the code below, it started working for XP only.

Dim bufferSize As Integer = 24000
Dim outbyte(bufferSize - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0

Dim sql As String = "SELECT ..."
Dim cmd As New SqlCommand(sql, conn)
conn.open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
If dr.Read() Then
' Reset the starting byte for a new BLOB.
startIndex = 0

' Read bytes into outbyte() and retain the number of bytes returned.
retval = dr.GetBytes(DocCol, startIndex, outbyte, 0, bufferSize)
Current.Response.Clear()
Current.Response.Buffer = True
Current.Response.ContentType = "application/octet-stream"
Current.Response.AddHeader("Content-Disposition", "attachment; filename=" & myfile" & "." & myextension)

Do While retval = bufferSize
Current.Response.BinaryWrite(outbyte)
Current.Response.Flush()

' Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize
retval = dr.GetBytes(DocCol, startIndex, outbyte, 0, bufferSize)
Loop

'Write the remainder of the last chunk
Dim remaining(retval) As Byte
Array.Copy(outbyte, 0, remaining, 0, retval)
Current.Response.BinaryWrite(remaining)
Current.Response.Flush()
Current.Response.Close()
End If
dr.Close()
conn.Close()

View 1 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Insert Rows With SQLServerce V3.5 Is Very Slow Can Anyone Help Insert Performance Poor

Feb 22, 2008



Hi All

I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.

I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.

I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.

Details:
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)

Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260

TRY1

Direct Insert Using Data Adapter.

Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)

14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)


TRY 2

Using Record Sets

Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)

Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand()

cmd.CommandText = "SELECT * FROM BackupDatabase"
cmd.ExecuteNonQuery()
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)

Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()

rec.SetString(1, Group_Name1)
rec.SetString(2, Full_Folder_Address1)
rec.SetString(3, File1)
rec.SetSqlString(4, File_Size_KB1)
rec.SetSqlString(5, Schedule_To_Run1)
rec.SetSqlString(6, Backup_Time1)
rec.SetSqlString(7, Last_Run1)
rec.SetSqlString(8, Result1)
rec.SetSqlString(9, Last_Modfied1)
rec.SetSqlString(10, Latest_Modfied1)
rec.SetSqlBoolean(11, Backup_On_Next_Run1)
rec.SetSqlString(12, Total_Backup_Times1)
rec.SetSqlString(13, Server_File_Number1)
rec.SetSqlString(14, Server_Number1)
rec.SetSqlString(15, File_Break_Down1)
rec.SetSqlString(16, No_Of_Servers1)
rec.SetSqlString(17, Full_File_Address1)
rec.SetSqlBoolean(18, OverWriteQuick)
rec.SetSqlBoolean(19, CompressAns)
rs.Insert(rec)
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub

€™20 to 24 rows per sec

TRY 3

Using SQL Commands Direct

Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)

Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"

Try
conn = New SqlCeConnection(strConn)
conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = CommandText1
'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
cmd.ExecuteNonQuery()

Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub

€˜ 25 to 30 but mostly holds around 27 rows per sec I

Is this the best you can get or is there a better way. Any help would be greatly appericated

Kind Regards

John Galvin

View 3 Replies View Related

Is This A Poor Database Design?

Dec 29, 2004

Setup
I am using a simple aspx page to calculate a person’s tax filing status and capturing statistical information. Most of the items are drop down boxes or radio buttons but a few are checkboxes.

Question
My plan is to give each option in the check boxes its own column in the database this will lead to a table that is 70 columns wide. Is that to many? Most of the info will be very small like “yes� or “no�. I will be using a stored procedure to insert the info. This will be coming down a VPN

Goal
I would like to give the admin staff here real time access to the data via link tables and such. I would like there to be only one table so it will be easy for them to pars the info quickly without needing to join tables or write unions. Ideally this would get me out of the business of creating one off reports.

View 2 Replies View Related

How To Select The Poor Indexes?

Jul 28, 2004

hi,
I have a dba with more than 500 tables and I want to drop the indexes on the least selective columns. How do I know which are the poor indexes?
Please help.........thanks

View 1 Replies View Related

Poor Man's Trace Replay?

Apr 23, 2008

A while back I captured a large trace but only the SQL:BatchCompleted event and not the usual list of events so I'm not able to use the RML Utilities or Profiler to play it back. Really though all the queries are from the same login so there's no issue just playing them straight through I would think and using the stored duration to back calculate a start time and just play them all through. Are there any tools to do this or I am on my own having to whip something up?

View 6 Replies View Related

Poor Query Optimizing

Nov 4, 2007

Hi there,

I've got a SQL 2005 table(DataTable) with 5.5 million rows and a clustered index on an integer column. The column is a foreign key pointing to another table (LookupTable) that associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index.

I'm running equivalent select statements with slightly different where clauses, and am not seeing the performance similarities I'd expect.

select * from DataTable join LookupTable on DataTable.Integer = LookupTable.Integer

1. Setting the where clause to the nvarchar(50) value on the LookupTable takes 64 seconds.
2. Setting the where clause to Integer = (select integer from LookupTable where nvarchar(50) = 'mysearchstring') takes 61 seconds.
3. Setting the where clause to Integer = 526 takes 1 second.

Is this poor design in SQL, or is it a misconfiguration? I would have expected SQL to modify my query to essentially be #3 in all cases before touching the DataTable, which would cause all three versions to take 1 second.

Thanks,
Jason

View 2 Replies View Related

Page Life Expectancy Is POOR

Jan 16, 2008

Page Life Expectancy (PLE) is pretty bad on my server. PLE is hovering around 3 minutes "sometimes" but is usally around 20-30 seconds.

Total memory allocated to SQL ( a fixed amount ) is set at 3GB.

Of the total memory allocated, SQL Server is using 2.52GB ... so there is room if needed.

The Buffer Cache is sitting at 2.09GB with a hit rate around 99.8%.

The Procedure Cache is sitting at 378MB with a hit rate around 90.5%.


CPU is hovering around 10-20%


Free System Page Table Entries is LOW ... at 22343

Disc Queue Lengths spike quite often to above 5 and sometimes as high as 36. Usually sitting at .05 to 1.0 (and there are times when the DQLs are great and not measureable.

What I need to find out is how to get PLE above the recommended 5 minute mark???

Please let me know if there are any other items I need to note.

Thanks!

=========================================
Here are some hardware/Software/Implementation stats:
=========================================

SQL Server 2005 Standard w/ latest patch of 3152

Windows Sever 2003 R2 Standard w/ latest patches applied (says PAE is enabled in the System Properties.. General tab

4 Intel Xeon X5355 @266GHz

4GB RAM with 3GB dedicated to SQL Server via the /3GB switch


System Disc ( C: ) is 136GB (free space is 122GB) and is RAID10
Data/SQL Disc ( G: ) is 408GB (free space is 347GB) and is RAID10
The SQL files (MDFs/LDFs, TempDB, DB & TLog backups, SQL application and all that is SQL related) are all on the single array (G (** which I must note is NOT how I configured the SQL environment but aquired the setup when I started the position).

View 6 Replies View Related

Poor Peformance On Pulling Data

Nov 12, 2007

So this could be a long story.

Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM
Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram

This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross.
Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K.
Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.

Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?

Thanks in advance guys,

Regards.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved