Hi,
I am using SQL2K for a intranet web application database. And I have several database in it.
After a long periode of time (about 6 month) the the application is slowing down when accessing the data.
I use full log for the database.
I also set an autobackup for the database for every 15 minute, but I think this is not what cause the slow down.
The database seem to slow down more and more every day.
I keep only the last 3 days transaction data and every day I delete transaction data older then 4 day, so the number of records in the transaction table is just about 3000 to 5000 records only.
Please help me. Thanks.
Hi, We have a server that s too slow. The tables have about between 1 million and 3 million records each. We have about 30 tables for production. The other tables are mostly look up tables (just for personnel and categories and so on, only static tables)
1/ do u guys think that s too much data, I mean about 3 millions records in a table. Because the manager said, may be if u archive some records and delete them from the tables, the system might become quick and performant?
2/ When we check the activity monitor in management in entreprise manager, we find a process Id 55 that has the value 1 in the Blocking column, and other processes having that process ID 55 in the Blocked by column. Does the fact that the blocking column for a process equals 1 mean that the process is having a problem, and how do we know what causes the problem for sure?
3/When we check Performance monitor, pls note that we have RAID5 with 5 disks, we see that Avg. Disk Queue Length counter ranging between 20 and 70 and especially if we run a query for example, although simple select query in one of the tables, the Avg. Disk Queue Length counter goes to the roof to 100 % in performance monitor.
I don t think we have a pb with the CPU, it looks good, not going to the roof.
For your info, we have the web server on that same SQL server machine as well, but the web server s working well, only sql server and the client application that accesses it is slow, the web pages are pretty fast.
Am I following the good path to diagnose what s wrong. It s my first time doing this, and I am trying to learn.
Hello , I'm a newbie,I programmed a store procedure,and when it runs in query analyzer, I get results very fast,but when I add it in a job,then this job run very very slow, (I add records and delete records in the SP), WHY?
I have two tables:T1 : Key as bigint, Data as char(20) - size: 61M recordsT2 : Key as bigint, Data as char(20) - size: 5M recordsT2 is the smaller, with 5 million records.They both have clustered indexes on Key.I want to do:update T1 set Data = T2.Datafrom T2where T2.Key = T1.KeyThe goal is to match Key values, and only update the data field of T1if they match. SQL server seems to optimize this query fairly well,doing an inner merge join on the Key fields, however, it then does aHash match to get the data fields and this is taking FOREVER. Ittakes something like 40 mins to do the above query, where it seems tome, the data could be updated much more efficiently. I would expectto see just a merge and update, like I would see in the followingquery:update T1 set Data = [someconstantdata]from T2where T2.Key = T1.Key and T2.Data = [someconstantdata]The above works VERY quickly, and if I were to perform the above query5 mil times(assuming that my data is completely unique in T2 and Iwould need to) it would finish very quickly, much sooner than theprevious query. Why won't SQL server just match these up while it ismerging the data and update in one step? Can I make it do this? If Iextracted the data in sorted order into a flat file, I could write aprogram in ten minutes to merge the two tables, and update in onestep, and it would fly through this, but I imagine that SQL server iscapable of doing it, and I am just missing it.Any advice would be GREATLY appreciated!
I'm using a SQL Server 2000 with a dozen of databases. The databases are rather small (all sizes together sum up to 10 GB).
The entire server gets extremelly slow from time to time (lasting a few days when it happens and suddently coming back to normality). A profiler trace doesn't show anything strange (besides a lot of SQL Agent entries).
I pretty much tried to isolate every single application that makes use of the databases in that server and see if it was the cause of the problem, but I couldn't find any correlation.
I know the computer where this server runs is quite fragmented. Is there anyway I could get to know if this is the cause of my performance issues?
I don't know if this happens, but once the server simply went down for some 3 hours, and I wasn't able to bring it up in anyway. It eventually started working again by itself. The only thing I did in the meantime was to run DBCC CHECKDB a few times, always getting the response "No error found on the database".
Hi everybody !!! I have a problem with database SQL server 2000, and I need anyone help me.
I have PC machine with hardware configuration following:
+ RAM : 512M
+ CPU : 2.4 GHz (intel Pentium 4)
+ System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 1
(this is call Machine1 )
and other Server machine with hardware configuration the following :
+ RAM : 2G
+ CPU : 3.6 GHz( intel Xeon)
+System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 2
(use RAID 5)
(this is call Machine2 )
.And SQL Server 2000 is installed in these machine.
I created one table and one script to insert several records into this table. and script for inserting:
declare @count int , @max int
set @count =1
set @max = 5000
WHILE (@count < @max or @count = @max)
BEGIN
insert into TBAT_STOCKEOD(TRDT,SEQNO,COMPID,TRANSTYPE) values('20071219',cast(@count as varchar(5)),'13215','1')
set @count = @count +1
END
I execute this script on Machine1 and Machine2 :
+ Machine1 : spent 3 seconds.
+ Machine2 : spent 30 seconds. I don't understand why Machine2 is slower than Machine1 ( while configuration of Machine2 is better than Machine1's ). I don't know what happen.
I checked resource usages in perfmon when SQL server execute script and result following :
Machine1:
Proccessor used 100%
whereas,Machine2 used physical Disk 100%
Is there the way to configurate SQL server to improve speed writting of hard disk?
Hi All,I have a table that currently contains approx. 8 million records.I'm running a SELECT query against this table that in somecircumstances is either very quick (ie results returned in QueryAnalyzer almost instantaneously), or very slow (ie 30 to 40 seconds toreturn results), and I'm trying to work out how I improve performance.Essentially the query I'm running is nothing more complex than:SELECT TOP 1 * FROM Table1 WHERE tier=n ORDER BY member_id[tier] is a smallint column with a non-clustered, non-unique index onit. [member_id] is a numeric column with a clustered, unique index onit.When I supply a [tier] value of 1, it returns results instantaneously.I have no idea if this is meaningful, but the tier = 1 records wereloaded first into the table, and comprise approximately 5 millionrecords.When I supply a [tier] value of 2, the results take 30 to 40 seconds.tier =2 records were loaded second, and comprise approximately 3million records.I've tried running an execution plan, and while I'm no expert, itappears to me that the index on tier isn't being used, even if I use:tier = CAST(2 as SMALLINT)I'm wondering if anyone can give me ANY advice on how to get anybetter performance out of this SELECT statement?Also, out of curiosity, can a disk defragment have a positive impacton SELECT query performance?Any help very much appreciated!Much warmth,Murray
I have had a problem with Enterprise Manager connecting to SQL Server. At first this problem was experienced with one particular network user... whichever PC he logged onto, Enterprise Manager took ages to connect to SQL Server and every operation was painfully slow. Creating a new Windows NT logon fixed the problem.
I now have this same problem but only on my PC. It doesn't matter which Windows NT logon I use, using Enterprise Manager is painfully slow. I've tried creating a new Windows NT profile, checking the hard drive for errors, defragmenting the disk, reinstalling Enterprise Manager etc but nothing works.
What is strange is that connections from VB applications on my PC are fast. It is only Enterprise Manager that is slow.
I am using the latest service pack for SQL Server.
I thought it could be a problem with the Enterprise Manager registry values but don't want to start messing with them!
Has anyone else experienced this problem? Any advice would be great as I can't find any help from microsoft other than installing the latest service pack for SQL Server.
I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?
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
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.
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?
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?
I hav the following problem. I have written an stored procedure in sql server 2000 as the following CREATE PROCEDURE dbo.pa_rellena @pFechaInicio datetime
AS declare @pFechaFin datetime declare @auxcod_cen char(10)
--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado if object_id('tmpCentros') is not null drop table tmpCentros
if object_id('tmpCentros2') is not null drop table tmpCentros2
if object_id('tmpMaxCajas') is not null drop table tmpMaxCajas
if object_id('tmpCajasCentro') is not null drop table tmpCajasCentro
if object_id('tmpVales') is not null drop table tmpVales
if object_id('tmpDiarioEfectivo') is not null drop table tmpDiarioEfectivo
if object_id('tmpDiarioTalones') is not null drop table tmpDiarioTalones
if object_id('tmpDiarioTarjetas') is not null drop table tmpDiarioTarjetas
if object_id('tmpDiarioSegundaForma') is not null drop table tmpDiarioSegundaForma
if object_id('tmpDiarioGastosTarjetas') is not null drop table tmpDiarioGastosTarjetas
if object_id('temp1') is not null drop table temp1
--Seleccionamos todos los centros de Salvador Bachiller select * into tmpCentros2 from centros where centros.tienda=1 order by cod_cen
--Seleccionamos el maximo de cajas por cada centro
select cod_cen, max(cod_caja) as cajas into tmpMaxCajas from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen order by cod_cen
--Mezclamos los centros con el maximo de cajas select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen
--Cajas por centro select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin
--Los vales de cada centro select cod_cen,sum(importe) as imp1 into tmpVales from vales where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen
--Efectivo de cada centro select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo from diario where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01' group by cod_cen
--Talones por centro select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones from centros inner join diario on centros.cod_cen=diario.cod_cen where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02' group by centros.cod_cen
--Tarjetas por centro select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen
--Segunda Froma de Pago select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen
--Comisiones tarjetas de pago select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen /* --Venta neta por centro declare cursortemporal cursor for select cod_cen from TmpCentros2
open cursortemporal delete detallecaja_aux fetch next from cursortemporal into @auxcod_cen while @@fetch_status=0 Begin select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen
insert into detallecaja_aux (cod_cen,importe1) values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos) fetch next from cursortemporal into @auxcod_cen
Hello.I test some query on sql server 2000 (sp2 on OS windows 2000) and iwant to know why a simple query like this :select * from Table Where Column like '%value'is more slow on 2000 than on sql 7.And this case arrive only if the % character is in the begin.If you test this :select * from Table Where Column like 'v%alue'then it's more fast on 2000.I look the execution plan, there is a difference but this differenceis the same in all the case i test (for the two query i write in thismessage for example).I don't understand why and so, if someone have an explanation, andperhaps a solution ...Excuse my poor english language.And thanks for time people spend to answer me.
I have the following statement that takes quite a long time. Longestof any of my SQL statment updates.UPDATE F_REGISTRATION_STD_SESSIONSET PREVIOUS_YEAR_SESSION_ID = (SELECT s.previous_year_session_idFROM F_REGISTRATION_STD_SESSION R,DS_V4_TARGET.dbo.D_H_Session_By_SessQtr SWHEREr.STUDENT_ID = F_REGISTRATION_STD_SESSION.STUDENT_IDand s.previous_year_SESSION_ID = r.SESSION_IDand s.session_id = F_REGISTRATION_STD_SESSION.SESSION_ID)STUDENT_ID varchar(20) and SESSION_ID char(10) and are indexedWhat I want to accomplish:I want to know if there was a student registration from the prior yearof a registration.Example, if there is a registration for Fall 2004, was there also aregistration for the same student Fall 2003?Maybe there is a better way to approach this?TIARob
I posted this in one of the VB forums but I'm starting to think itmight be more appropriate to have it here, since it really seems to bea SQL server (MSDE/Express 2005) problem:Hey, all, I have a problem with queries against a SQL server instancethat I just found and is causing me fits. I hope someone can pointmein the right direction, please. TIA.Basically, I got a Vista OS machine to test my VB6 app on it as someof my clients will be switching over in the coming months. I went toa local Circuit City during early business hours in the middle of theweek and I installed my application on each of 5 PC's on the newVistaOS (Tuesday, when it was released). I had read that MSDE 2000, whichI normally use as my DB is not supported on Vista so I had downloadedand was using SQL Express 2005. Each system had at least a 1.9 GHzdual core processor and 1 GB of RAM. One process in my program findsrecords in one table that do not match records in another table andthen reports those un-matched entries. On my development machine(laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHzCentrino (IIRC)) the process takes less than 30 seconds consistently.On each of those 5 systems at Circuit City the process took 5 minutes(on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes oneach of two Gateway systems (the model numbers of which I forget atthe moment). Each of these computers should be much faster than mylaptop, and some had twice the RAM, and all had SATA or SATA IIdrivesinstead of my piddly 5400 laptop drive, I would have thought they'dall be faster but were abysmally slow.So, seeing a huge difference in the time, and to try to keep thisshort and sweet, I fired up another computer I have, running XP SP2,on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and myapplication and ran the process. < 30 seconds on each of multipleruns. Second, I unloaded MSDE 2000 and installed SQL Express 2005andmoved the DB to it (sp_attach_db) which caused some upgrading(messages reported in OSQL about update/upgrade). When it was done Irebooted, to be sure, and the ran the program and the process again.On the same data, on the same computer, the process took 7-9 minutesconsistently on each of several runs. This makes this part of theapplication unusable, and even the simple stuff like grabbing asinglerecord from the DB (maybe 5 columns of no more than 500 bytes total)is noticeably slower on the SQL Express 2005 than on MSDE 2000.So, the problem seems to be with my interaction with the DB. I amusing ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with aconnection string like: Driver={SQL Server};server=(local)caredata;database=caredata;Uid=sa; Pwd=<password>I use the RecordsSet Object to open the data similar to this:oRS.OpenstrSQL$, oCN, adOpenKeysetafter the oCN object has had the connection string set and the objectis opened.Considering that the same computer, against the same data, with thesame program, takes about 14 times (or more) longer to run, then ithas to be either that SQL Express 2005 is slow OR that my program isinteracting with it in an incorrect manner.Can someone point me in the right direction, please?Thank you.--HCSo, the problem isn't Vista
We are having SQL2000 Advance Server. 4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.
We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose. The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.
We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference. Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.
We have set just like the Tech support recommended.
I would appreciate, if someone give us some recommendations to run the double take without any problem.
Recently our database has been migrated from SQL 2000 to SQL 2005 on a new server(machine) with windows 2003(previously windows 2000). If the database is retained on the same machine but with a named instance of 2005, the application(websphere 5.1) is behaving normal whereas if i configure the aplication to the new server it is running slow for some of the queries but not all.
This change will have to be implemented in production very soon. Any advise will be of great benefit
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).
HiWe have a SQL server 2000 SP4 on a windows 2003 2x3Ghz XEON 4 GB ram.We have a table looking like this with currently 6 rows. Total data is aprox10 kb i all row all together.CREATE TABLE [dbo].[BIOMETRICPROFILE] ([BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,[FINGERPRINTTEMPLATE1] [image] NOT NULL ,[FINGERPRINTTEMPLATE2] [image] NOT NULL ,[FINGERPRINTTEMPLATE3] [image] NOT NULL ,[FINGERPRINTTEMPLATE4] [image] NOT NULL ,[FINGERPRINTTEMPLATE5] [image] NOT NULL ,[FINGERPRINTTEMPLATE6] [image] NOT NULL ,[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOselect * from BIOMETRICPROFILE takes ~4 seconds (!) to execute thourgh Queryanalyzer. Alle other tables has no performance problems.We have a SQL 2005 express instalation on the same server. If we restore abackup from the sql 2000 database the query takes aprox ~ 15 ms.What isgoing on here?Has SQL 2000 problems with image fields? or how can we find the problem?RegardsAnders
i was using sql 2000, the database contains 500+ tables, 3000+ sp. i moved to sql 2005 and found problem on generating script (right click database -> tasks -> generate scripts). i need to generate the table relations.... it is very very slow compared to sql 2000 which is done in about 30 seconds to few minutes. i already tried many ways including set options to false which in my thought could speed up a lot...but still very slow.
average generate script time with sql 2005 (sp 2): 70-90 minutes. average generate script time with sql 2000 (sp 4): 2-3 minutes.
To extract data from an ODBC source, try the following:
Add an ADO.Net Connection Manager. Edit the Connection Manager editor and select the ODBC Data Provider Configure the Connection Manager to use your DSN or connection string Add a Data Flow Task to your package. Add a Data Reader Source adapter to your data flow Edit the Data Reader source adapter to use the ADO.Net connection manager that you added. Edit the Data Reader source to query for the data you wish to extract.
hth
Donald
Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.
When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.
Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?
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.
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?
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.
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?
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??
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.
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?
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.
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!!
