Slow Access To SQL Server
Sep 17, 2007
I have 25+ WinXP SP2 workstations on a Novell file server (Novell login) and also have a W2K SQL 2000 Server for our database. We are usint Win NT authentication to the SQL Server.
One AND ONLY ONE user is having trouble with this setup. Her workstation is PAINFULLY slow accessing the SQL server through my Delphi programs, through our third party programs, and through ODBC connections.
I thought it was her workstation until I configured a clean workstation for her, and it too had the same problem. I then took a brand new Dell 9200 workstation with 2gb memory, gigabit NIC, etc. and configured it to use our servers. It too had the same slowness connecting to the SQL server. Everything else is fast.
I deleted her entry in the NT domain users table, and re-entered her. No help.
Can anyone shed any light on this?
View 3 Replies
ADVERTISEMENT
Aug 27, 2001
We have several people accessing the SQL server 7.0 data thru' MS Access Server has been slow on queries etc.Everything looks good,other than this one finding of MS Access being used for update/select. Is MS Access a potential problem? How to solve the problem of slow response time?
Note: MS Access is running on the client desktops retrieving data from SQL Server and joining data from MS Access to SQL Server.
View 2 Replies
View Related
Jul 5, 2006
Hi
Here is the brief to my problemWe had our database on SQL Server 2000 and Windows 2000.This machine
had 2gb of RAM and dual Penitum 3 processors and about 25-30 users were
connected all the time. The size of database is around 2 gb. Even on this setup
rate of data retrival was good, never had any issues. We moved to SQL Server 2005 and Windows 2003. This machines has
2 Pentium Xeon 3.4 processors and 2 stick of KINGSTON 1024 MB 333
MHZ DDR DIMM ECC CL2.5 DUAL RANK X4 INTEL. The rate of data
retrival is awful and its very slow. It using about 1.7 to 1.9 gb of RAM all the
time. Page File usage is about 2.07 gb and Virtual Usage is about 1.7gb.I dont quiet understand why is it so slow to get data. We use bespoke software,
so nothing has changed there. Hardware specification of our server is far more better then the recommended
system requirement for SQL Server 2005.Am i missing something out or i havent set up the SQL Server properly? Any help would really be appreciated.Mits
View 3 Replies
View Related
Jun 6, 2006
Hi,
first time poster/newbie here.
I've
got a football (soccer for the yanks!) predictions league website that
is driven by and Access database. It basically calculates points
scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There
are two sections of the site however that have almost ground to halt
now that more users have registered throught the season. The players
section and league table section have gone progressively slower to load
throughout the year and almost taking 2 minutes to load.
http://www.pool-predictions.co.uk/home/players.asp?tab=a_d
http://www.pool-predictions.co.uk/home/table.asp
All
the calculations are performed in the Access database Ive written and
there are Access SQL queries to get the data out.
My
question is, is how can I speed the bloody thing up! ! Somone has
alos suggested to me that I use stored procedures and SQL Server to
speed things up? Ive never used SQL Server before so I am bit scared
about using it (Im only a hobbyist), and I dont even know what a SP is
or does. How easy will it be upgrading the whole thing to SQL Server
and will it be worth the hassle, bearing in mind I expect my userbase
to keep growing? Do SP help speed things up significantly? Would
appreciate some advice!
Thanks in advance,
John.
View 1 Replies
View Related
Nov 15, 2007
I have an application built with Access 2003 (MDB). It is running under Windows XP without any problems. If I run it under Vista, it works technically well but I get the data very slow from the server.
Server:
Windows Server 2003 R2, SP2
SQL Server 2005, version 9.00.3054.00
Firewall: off
Client:
Access 2003, SP3
Connection strings:
ODBC;DRIVER={SQL Native Client};UID=SD_Admin;DATABASE=SDX;SERVER=MARS;PWD=xxx;
or
ODBC;DRIVER={SQL Native Client};DATABASE=SDX;Trusted_Connection=Yes;SERVER=MARS;
Windows Firewall on client: off
Onecare Firewall on client: off
How can i Fix this issue?
View 2 Replies
View Related
Mar 13, 2007
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
Thanks in advance for any help on this
View 1 Replies
View Related
Jan 28, 2002
I am experiencing VERY slow connectivity between client and server SQL 2000. I have checked the network activity and it is low. It also takes Enterprise manager ages to load, and then browsing tables and trees is impossible!
HELP!!
View 1 Replies
View Related
Oct 5, 2004
I have a access database, the data store in another server. This noon, one of our user is runing the access database too slow. Open the database and search the data, etc. It took a long time to come out, Any body has experience on it, why, we had etrust install on each user machine, is that cause this too slow? Thanks in advance.
View 1 Replies
View Related
Feb 22, 2008
Has anyone else exported data to Access? If so, is it a slow process in SQL Server 2005?
I am exporting 3,000 records to an Access database using a view and it has taken 20 minutes. I am using the Export Data wizard. Any tips on how to speed it up would be appreciated.
View 7 Replies
View Related
Aug 3, 2007
Hii,
A bit new to SQL 2k5 but here goes, I recently installed SQL 2k5 on a preety decent box with about 4GB Ram and created a database called PointOfSale. As probably obvious by now, the application that accesses the Database is a PointOfSale application.
The store sells ladies clothing items, of which each is barcoded.
When an item is scanned via the barcode reader into the application, it takes like about 10-15 seconds for the item information to appear. Of course the item information resides on the database.
There are three registers and the same thing happens on all. I am running sp 2 for sql2k5 already.
Any thing I can do that would reduce the 15 sec delay significantly?
Any help at all would be appreciated immensley.
Regards,
Burt.
View 2 Replies
View Related
Nov 15, 2006
I have one aspx page that is very slow loading it is connected to SQL 2005 database. I am looking for any preformance tips out there
View 3 Replies
View Related
Jan 26, 2000
I have a stored procedure which creates 3 temporary tables. Every table is about ten rows and 25 columns. The inserts in the tables goes fast (< 30 ms). The selects from them is also that fast. BUT the first select takes about 3200 ms one each of the temptables. (I first do insert, then select from them.) So the SP executes at about 13 seconds instead of 3.
Any suggestions, anyone, please?
View 1 Replies
View Related
Jan 9, 2006
Hi,
I have migrated my app from VB to VB.Net. A 3-tier app with remoting and COM+.
I am experiencing a long wait time of about 3 times higher than what it would take in the VB App.
I am using DataAdapter.FiLL method to fill the datatable.
I have tried.
Using DataReader ( Made the things worse )
Using BeginLoadData and EndLoadData
Creating a Dataset and calling fill with the dataset so that the round trip to the middletier is saved to bring the SQL.
But i feel now that whatever is done. the problem is with the fill method only?
Is there any alternative?
Please suggest. It is one of the most important thing which if not possible may lead to scrapping up idea of upgrading to .Net.
Shri
View 10 Replies
View Related
Jun 27, 2006
We have an issue with accessing SQL Server 2000 where the access of data from the database is slow unless the user is logged in as an administrator to their computer.
The system is as follows: SQL Server 2000 on a W2K server. Users logging into a Win 2003 domain server. Users using W2K on their workstations. Application is VB.NET using the Enterprise Library Data Block, connection pooling ON, and windows authentication.
We are assuming that the issue is down to one of authentication and that when a user is set as an administrator then they have instant access. We have been able to replicate the issue using just SQL server on a W2K workstation and accessing from another W2K workstation. Again data access is way slow unless the account is an administrator.
Glad of any ideas folks!
Kind Regards
Ian Logan
View 10 Replies
View Related
Feb 1, 2008
I have a table that has appx 3.2 million rows. see sp_help
Name Owner Type Created_datetime
-------------------------------------------------------------------
TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893
Column_name Type Comp Lngth Prec Scale Nullable
------------------------------------------------------------------------
RowID int no 4 10 0 no
SPHInstID int no 4 10 0 no
BPDInstID int no 4 10 0 no
BMUID varchar no 11 no
InfoImblCfw numeric no 9 12 2 no
BMUPrdNonDel numeric no 9 12 2 no
PrdFPN numeric no 9 13 3 no
PrdBMUBalSrvVol numeric no 9 13 3 no
PrdInfoImblVol numeric no 9 13 3 no
PrdExpdMtrVol numeric no 9 13 3 no
BMUMtrVol numeric no 9 13 3 no
PrdBMUNonDelBidVol numeric no 9 13 3 no
PrdBMUNonDelOfrVol numeric no 9 13 3 no
TranLossFctr numeric no 9 15 7 no
TranLossMtpl numeric no 9 15 7 no
TradUnitName varchar no 30 no
TotTrdUnitMtrVol numeric no 9 13 3 no
BMUAppBalSrvVol numeric no 9 13 3 no
DTCreated datetime no 8 yes
DTUpdated datetime no 8 yes
Identity Seed Inc Not Repl
-----------------------------------------
RowID 0 1 0
RowGUIDcol
-----------------------------
No rowguidcol column defined.
Data Located on File Group
==========================
PRIMARY
Index Name Decsription Keys
----------------------------------------------------------------------------
idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID
This table has 1 clustered index based on its own unique record ID and that of its parent table record
I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds.
However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored.
The same transaction is now taking 8 to 9 minutes.
I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop..
I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed.
DBCC CHECKTABLE returns the following but takes 6 and a half minutes
DBCC results for 'TB_SAAI014_BPD'.
There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No errors are shown
A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds
Can anyone please point me in the direction of things to check, try or repair.
Any help greatfully recieved.
Jinx1966
View 10 Replies
View Related
Feb 10, 2007
I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.
Obviously, there is some idle timeout setting.
I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)
But what i'm actually wondering is:
1. Is it possible to adjust how long it would wait before timing out?
2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?
View 4 Replies
View Related
Dec 19, 2006
I have an Access2000 ADP that I want to run under Access2007. The problem I have is that some forms take up to 45 seconds to open in Access2007! These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:
SELECT * FROM ArReceivableType
Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows. They open instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'
It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys. My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000 works great for this enterprise app, but I really like the new Access2007 features (and I don't want to still be developing Access2000 apps in 2010).
View 3 Replies
View Related
Jun 29, 2007
One of our developers has a Microsoft Access 2000 database that runs queries that compare the Access db data to a SQL Server database. He uses pass through queries to get the data from SQL Server.
We're finding that the Access query runs quickly against our test server, even with copies of production data, but when we try the same query against our production server, the CPU on the local computer running Access is pegged and the query takes up to 10 minutes to run.
First I verified that the SQL Server structures between test and production were identical, including indexes. I checked index fragmentation, and productions indexes are less fragmented than tests. Again, test and production currently have the identical data.
I've run a profiler trace on our production SQL Server 2000 server, and I see the RPC for the query from Access running almost instantaneously.
Any ideas on what might be the cause of the difference in speed between test and production SQL Server servers, or any suggestions on other things I could look at/tools I could use to troubleshoot this issue further?
View 1 Replies
View Related
Jan 4, 2007
Hello,
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Thank you for your help!
cdun2
View 32 Replies
View Related
Apr 18, 2008
Hi there,
I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.
I could pin the problem down to this query:
DECLARE @Package_ID bigint
DECLARE @Email varchar(80)
DECLARE @Customer_ID bigint
DECLARE @Payment_Type tinyint
DECLARE @Payment_Status tinyint
DECLARE @Booking_Type tinyint
SELECT @Package_ID = NULL
SELECT @Email = NULL
SELECT @Customer_ID = NULL
SELECT @Payment_Type = NULL
SELECT @Payment_Status = NULL
SELECT @Booking_Type = NULL
CREATE TABLE #TempTable(
PACKAGE_ID bigint,
PRIMARY KEY (PACKAGE_ID))
INSERT INTO
#TempTable
SELECT
PACKAGE.PACKAGE_ID
FROM
PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID
LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID
WHERE
PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)
AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)
AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)
AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)
AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)
-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented
--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'
GROUP BY
PACKAGE.PACKAGE_ID
DROP TABLE #TempTable
The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.
On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:
942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000
Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.
Thanks again for any help
Best regards,
Pascal
View 9 Replies
View Related
Mar 29, 2001
Hi,
we have performance problems with MS SQL Server 2000. We upsized an Access
2000 application to MS SQL server, using linked tables. Most of the time
the performance is fine (there are at most 10 users connected to the server
at the same time). However, it regularly happens that the database stops
responding. Queries, which normally take 20 ms to execute, require 20
seconds or more. In the Access client this looks as if Access has hung, it
is not responding, even though it eventually comes back to live. What I
have found out is that if I restart the SQL server, the problem disappears
and the performance stays fine for some time. This in mind, I set up a
batch which stops and restarts the SQL at night. However, recently the
problem started appearing even when the SQL server was running for only a
few hours.
I also looked at the performance monitor at both the client workstation and
the server and even when the response time are slow, the processor usage
both at the workstations and the server is under 10 percent.
I wonder whether anybody could help me with this problem. I realise that
using linked tables in Access is not the best thing for achieving good
performance, but I still would expect at least decent performance. At the
moment the situation is worse than if we were using just Access.
For your information, the computer which the SQL server runs on is a dual
processor Pentium Pro 200 MHz, with 320 MB RAM and a SCSI RAID. The server
is the only Windows 2000 domain controller on the network and it runs
Active directory. Plus there is also Exchange server 5.5 installed on this
server. This looks like a lot for a single server, but please bear in mind
that there are only 15 users on the network.
It may be also interesting to know that we have only recently upgraded from
SQL server 7 to SQL server 2000, but we were experiencing the same problem
before, even though not so often.
Thank you for any help.
Tomas Koutny
Elink Ltd
View 4 Replies
View Related
Jul 15, 2004
When, at the same network, I run Windows Server 2003, with webserver on it, SQL clients connected to SQL server on other computer server, experience slow work with queries. What seems to be a problem? When I disconnect Windows 2003 Server everything go faster. Why?
View 2 Replies
View Related
Jul 20, 2004
Hiya folks,
This is more a request for some input from peeps with more experience of SQL than myself. A problem has shown itself on my SQL server over the last week or so, in that the server will 'slow down' intermitently, almost as if the the connection to the server has been lost for about 30 seconds. All will be fine for another minute or so and then the same problem occurs.
The only way I've found to get round this problem is to stop the SQL server and completely restart the server that SQL resides on, then restart SQL. This cures the problem for about a day.
Any offers??
View 9 Replies
View Related
Jan 14, 2005
Hello,
I've written a program, which communicates via ODBC with multipe database platforms. In a local network it seems to be everthing OK, but when I connect via VPN (2MBit/s S-DSL) to the MSSQL (2000 SP3) the connection is not only very slow, it seems that the MSSQL only uses 1 % of the bandwith. I don't think that 0,25 KByte/s is quite normal speed. A query takes about 5 - 10 minutes. (And I do a lot of queries...)
If I connect to an Oracle-DB the full bandwith will used (125 KByte/s).
Is there a problem with the SQL2000? How can I solve this behaviour?
so long
View 10 Replies
View Related
Nov 4, 2005
Hi,
I am having a problem accessing my sql server database using either Enterprise Manager or Query Analyser. It is awfully slow. Each time I click to expand a database in Enterprise Manager it takes about 25mins to do anything. I was running a DTS package yesterday which failed and have had this problem since. If I access the database via my app everything seems to be running at a normal speed. If I go to my task manager the sql server process is using up 750MB of memory and 750MB of virtual memory??
Has anyone seen this and know how to fix it???
Thanks in advance
View 2 Replies
View Related
Jun 16, 2008
Does anyone have any experience with a server slowing down over time to the point that it must be rebooted? This occurs over a time frame of from a few days to as long as a week. The server has a single Xeon 3.6 processor and 8 GB of ram. It executes production SQL scripts against databases contained on the server as well as a data warehouse stored on an AS400 server.
After rebooting, all jobs seem to execute in a reasonable time frame, according to their size and scope.
View 9 Replies
View Related
Jul 13, 2007
server was running out of space. drop a database to free up space. the server is slow now like its taking more time to query or delete records than normal. what happend and how do i fix it?
=============================
http://www.sqlserverstudy.com
View 3 Replies
View Related
Jul 20, 2005
When I query or browse databases or tables in SQL Server 2000 it worksextremely slow. It started working like this from one day to another.I tried reinstalling but it stayed the same.Now I'm installing Service Pack 3, it's curious that executingreplsys.sql, replcom.sql and repltran.sql scripts in the installationis extremely slow too.Did anyone experienced this or have any idea?Thanks in advance.
View 5 Replies
View Related
May 8, 2006
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
View 5 Replies
View Related
Mar 21, 2007
I had installed SQL 2005 in my server (very high spec server).
but i wonder why every time i launch the management studio, it tooks forever to load... about 5 minutes.
When i right click on a table and click on view properties, it can takes up to 5 minutes to display the detail.
Is there any setting / configuration that i configured wrongly?
View 4 Replies
View Related
May 2, 2001
Our server is running. There are no locks, and server has been rebooted but the problem is still there. This has been going on for some time now. I intend to restart the server. Does anybody have a quick solution, please help. Thanks for your assistance!!
View 4 Replies
View Related
May 17, 2001
Please what do I look out for 6.5 if I want to troubleshoot for slow running.
Thanks.
View 2 Replies
View Related
Aug 23, 2001
Our SQL server needs to be rebooted every two weeks sometimes even earlier. Otherwise it gets extremely slow and I can even open any tables in enterprize manager. Also the users cannot type any info into the application screen, it takes forever to change from one screen to other. Can somebody please suggest me how to avoid this situation or any idea of why it happens.
Thanks a lot.
View 2 Replies
View Related
