SQL Service Pack with MS Access

Question

Post reply

SQL Service Pack with MS Access

Christi

SSC-Addicted

Points: 402
More actions
August 25, 2005 at 12:50 pm

#67473

Hello I am hoping someone can help me out, this has been an ongoing problem for our company for the last few years. SQL Server Backend with MS Access .ade program front end.
First I will give you the specs and then describe the problem.
Microsoft SQL Server 2000 - 8.00.194
Dual 3.2Ghz Xeon with HT enabled
4GB Ram
C: - RAID 10 (2 x 36GB U320 15k)
D: - RAID 10 (4 x 72GB U320 15k)
E: - RAID 10 (6 x 72GB U320 15k) + 1 HotSpare
Windows AS2000 SP 4
MSAccess 2000 SP3, all hotfixes, etc

As you can see there are no service packs installed for SQL Server. If I add a service pack the CPU usage goes up to 100% and stays there for quite a bit of time. Memory is fine. We have approx. 200 employees and at any given time there are 500 -550 processes. (I run SELECT COUNT(spid) FROM sysprocesses WHERE dbid > 6).

We have other servers that run SQL Server (same specs as above) and they do not have issues with the service packs (all servers have sp3a installed, except this server). They are not tied to an MS Access application, they are tied either to VB6 application or a.NET application. They run perfectly fine with no issues. What could be causing the complete slow down and high CPU spikes on the server when service packs are added? This is not hardware as this is the third server we have purchased to try and elimanate this problem. I am at a loss.

We have hired a few more employees and now the server is slowing down with no service packs installed, is there a limit with an .ade application? Do I need to push for getting out of Access and into another front end?

Another bit of info...we do have remote users who log on through Citrix. This problem started well before we started using Citrix.

If I am missing any info that may be of help, please ask! I am trying to give you as much info as possible.

Thank you for any and all assistance!

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Do you know what queries make the proc hit 100% or how many employees it takes?

Christi SSC-Addicted Points: 402 More actions · Answer 2

I have ran multiple traces and there are no specific queries or stored procs. I have dropped and re-created indexes.

I also want to add that approx 31% of the processes are reading/writing to disc.

Also, I am wondering if I should have posted this in the SQL Administration area of the board. I do not want to spam the board, perhaps if I created a thread and posted a link to this thread?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ya you might wanna repost in administration.. this is not an access problem.

Christi SSC-Addicted Points: 402 More actions · Answer 4

Thanks, I did create a new thread in the administration forum. I figured it could be Access as our other servers (11 of them) run fine with sp3a installed. Only this production server that uses an Access front end has the problem.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Is it more loaded than the other servers (number of users/queries).

Do you see a lot of queries with sp_executesql that have a mile long of parameters?

Christi SSC-Addicted Points: 402 More actions · Answer 6

Same amount of users..and now that you mention it, I do see a lot of sp_executesql in the trace file..more than half of the queries are sp_executesql.

Would that cause issues?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

August 25, 2005 at 1:43 pm

#585161

Can you show me a few of the long ones?

Christi SSC-Addicted Points: 402 More actions · Answer 8

Here you go..I removed the server name and table name

exec sp_executesql N'UPDATE "<server>".."<table>" SET "PurposeRefinance"=@P1,"EstateHeldIn"=@P2,"SalesPrice"=@P3 WHERE "ClientID"=@P4 AND "subFinancing" IS NULL AND "Originator" IS NULL AND "ThirdPartyName" IS NULL AND "Buydownflag"=@P5 AND "OwnerFirst" IS NULL AND "TorrensNo" IS NULL AND "MICertNo" IS NULL AND "MIAmount" IS NULL AND "MICompany" IS NULL AND "SellerGrade" IS NULL AND "MERSID" IS NULL AND "MERSOriginalflag"=@P6 AND "MERSregistrationgroup" IS NULL AND "MERSrightsTransfered"=@P7 AND "MERSverified"=@P8 AND "DocType" IS NULL AND "ExportDate" IS NULL AND "ExportUser" IS NULL AND "ProductGroupCode" IS NULL AND "AVMConfidenceFactor" IS NULL AND "AVMProductCode" IS NULL AND "AVMVendorName" IS NULL AND "TransactionTYpe" IS NULL AND "AssetUsed"=@P9 AND "MortgageType"=@P10 AND "MortgageTypeOther" IS NULL AND "AmortizationType"=@P11 AND "AmortizationType1008" IS NULL AND "AmortizationTypeOther" IS NULL AND "AmortizationTypeARM" IS NULL AND "PurposeLoan"=@P12 AND "PurposeOther" IS NULL AND "LeaseholdDate" IS NULL AND "PurposeRefinance" IS NULL AND "MannerHeldIn" IS NULL AND "SourceDown" IS NULL AND "EstateHeldIn" IS NULL AND "Assumableflag"=@P13 AND "OtherIncome1BC" IS NULL AND "OtherIncome1Desc" IS NULL AND "OtherIncome1Amount" IS NULL AND "OtherIncome2BC" IS NULL AND "OtherIncome2Desc" IS NULL AND "OtherIncome2Amount" IS NULL AND "OtherIncome3BC" IS NULL AND "OtherIncome3Desc" IS NULL AND "OtherIncome3Amount" IS NULL AND "PrepayDays" IS NULL AND "AppBy"=@P14 AND "InterviewerPhone" IS NULL AND "InterviewerEmployer" IS NULL AND "AltName1" IS NULL AND "AltCreditor1" IS NULL AND "AltAcctno1" IS NULL AND "AltName2" IS NULL AND "AltCreditor2" IS NULL AND "AltAcctno2" IS NULL AND "DemandFeatureflag"=@P15 AND "VariableFeatureflag"=@P16 AND "LateChgDays"=@P17 AND "LateChgPercent"=@P18 AND "PrepayPentaltyflag"=@P19 AND "PrepayRefundflag"=@P20 AND "Insuranceflag"=@P21 AND "Assumption"=@P22 AND "FilingFee" IS NULL AND "BalloonPaymentflag"=@P23 AND "BalloonYears"=@P24 AND "BalloonAmt"=@P25 AND "BalloonDueDate" IS NULL

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Do you see anything wrong with this query???

Shouldn't the where condition be more like : where ClientId = @Param3??

This is a huge network load for the query and a lot of useless processing for the server.

Try this on a test server : Add a TimeStamp column in the client table (I usually call them all TsAccess because I never use them).

Then go back to the adp and rerun an update on the client table. If access has detected the timestamp the where condition will be changed to where clientid = @P3 and and TimeStamp = @P4. This is much faster to process on the server/network.

Christi SSC-Addicted Points: 402 More actions · Answer 10

What is the purpose of the timestamp? I don't understand.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

The timestamp is a column maintained by the server. Everytime a row is updated the timestamp is modified by the server (like a row version thing). So intead of checking all the column to see if the record was modified, access only has to check if its version of the timestamp is the same that the one on the server (dropping to a pair of conditions (id + timestamp) instead of one condition per column). Access does this check to send the warning (are you sure you want to save this record.. it might have been modified by someone else).

Christi SSC-Addicted Points: 402 More actions · Answer 12

Yes, I know the purpose of 'timestamp' I just do not see how adding timestamp is going to make this query run more efficent?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Here's an exemple on a big table WITH timestamp :

exec sp_executesql N'UPDATE "Ideal"."dbo"."Chaudières" SET "Succursale"=@P1 WHERE "no"=@P2 AND "TsAccess"=@P3', N'@P1 nvarchar(1),@P2 int,@P3 varbinary(8)', NULL, 867, 0x0000000000102078

Here's the SAME UPDATE without the timestamp :

exec sp_executesql N'UPDATE "Ideal"."dbo"."Chaudières" SET "Succursale"=@P1 WHERE "no"=@P2 AND "CNUM"=@P3 AND "Nom du client"=@P4 AND "Succursale"=@P5 AND "Numéro" IS NULL AND "Rue"=@P6 AND "Ville"=@P7 AND "Responsable" IS NULL AND "N° téléphone" IS NULL AND "Appareil" IS NULL AND "Date de dernière mise à jour"=@P8 AND "Appareil #"=@P9 AND "Eau Chaude"=@P10 AND "Vapeur"=@P11 AND "Huile Thermique"=@P12 AND "Huile#2"=@P13 AND "Huile#6"=@P14 AND "Huile usée"=@P15 AND "Gaz Naturel"=@P16 AND "Gaz Propane"=@P17 AND "Gaz Butane"=@P18 AND "Électrique"=@P19 AND "Autre1"=@P20 AND "Autre" IS NULL AND "Pression de gaz" IS NULL AND "Marque"=@P21 AND "Modèle"=@P22 AND "Série"=@P23 AND "commande" IS NULL AND "Capacité de la chaudière" IS NULL AND "Capacité de la chaudière2" IS NULL AND "Crn" IS NULL AND "Surface de chauffe" IS NULL AND "dappareil usagé" IS NULL AND "Trou de main qtée" IS NULL AND "trou de main dim" IS NULL AND "Trou de main qtée2" IS NULL AND "trou de main dim2" IS NULL AND "trou dhomme qtée" IS NULL AND "trou dhomme dim" IS NULL AND "Tubes Qtée" IS NULL AND "Tubes Diamètre" IS NULL AND "Tubes Longueur 1" IS NULL AND "Tubes Quantité 2" IS NULL AND "Tubes Diamètre 2" IS NULL AND "Tubes Longueur 2" IS NULL AND "Gauge Glass Ø" IS NULL AND "Gauge Glass longueur" IS NULL AND "Pression de conception" IS NULL AND "Pression dopération" IS NULL AND "Brûleur intégral"=@P24 AND "Brûleur Marque" IS NULL AND "Brûleur Modèle" IS NULL AND "Brûleur Série" IS NULL AND "Moteur HP" IS NULL AND "Moteur Volts" IS NULL AND "Moteur RPM" IS NULL AND "Moteur Frame" IS NULL AND "Nozzle débit" IS NULL AND "Nozzle degré" IS NULL AND "Nozzle type" IS NULL AND "Electrodes" IS NULL AND "Transformateur dallumage marque" IS NULL AND "Transformateur dallumage modèle" IS NULL AND "Type de ventilateur" IS NULL AND "Dimension du ventilateur" IS NULL AND "Sens de rotation du ventilateur" IS NULL AND "Régulateur marque" IS NULL AND "Régulateur modèle" IS NULL AND "Régulateur orifice" IS NULL AND "Régulateur spring" IS NULL AND "Valve principale #1 marque" IS NULL AND "Valve principale #1 modèle" IS NULL AND "Valve #1 actuateur" IS NULL AND "Valve principale #2 marque" IS NULL AND "Valve principale #2 modèle" IS NULL AND "Valve #2 actuateur" IS NULL AND "Vent valve marque" IS NULL AND "Vent valve modèle" IS NULL AND "Valve modulante marque" IS NULL AND "Valve modulante modèle" IS NULL AND "Actuateur modulant marque" IS NULL AND "Actuateur modulant modèle" IS NULL AND "Lgps marque" IS NULL AND "Lgps modele" IS NULL AND "Hgps marque" IS NULL AND "Hgps modele" IS NULL AND "Valve lubrifiée principale marque" IS NULL AND "Valve lubrifiée principale modèle" IS NULL AND "Régulateur de veilleuse marque" IS NULL AND "Régulateur de veilleuse modèle" IS NULL AND "Régulateur de veilleuse orifice" IS NULL AND "Régulateur de veilleuse spring" IS NULL AND "Valve veilleuse #1 marque" IS NULL AND "Valve veilleuse #1 modèle" IS NULL AND "Valve veilleuse #2 marque" IS NULL AND "Valve veilleuse #2 modèle" IS NULL AND "Vent valve de veilleuse marque" IS NULL AND "Vent valve de veilleuse modèle" IS NULL AND "Valve lubrifiée de veilleuse marque" IS NULL AND "Valve lubrifiée de veilleuse modèle" IS NULL AND "Valve principale dhuile #1 marque" IS NULL AND "Valve principale dhuile #1 modèle" IS NULL AND "Valve principale dhuile #2 marque" IS NULL AND "Valve principale dhuile #2 modèle" IS NULL AND "Valve modulante dhuile marque" IS NULL AND "Valve modulante dhuile modèle" IS NULL AND "Actuateur modulant dhuile marque" IS NULL AND "Actuateur modulant dhuile modèle" IS NULL AND "Valve de retour marque" IS NULL AND "Valve de retour modèle" IS NULL AND "Valve de purge marque" IS NULL AND "Valve de purge modèle" IS NULL AND "Réchaud dhuile électrique marque" IS NULL AND "Réchaud dhuile électrique modèle" IS NULL AND "Réchaud dhuile à vapeur marque" IS NULL AND "Réchaud dhuile à vapeur modèle" IS NULL AND "Contrôle temp dhuile marque" IS NULL AND "Contrôle temp dhuile modèle" IS NULL AND "Carte de sortie du contrôle marque" IS NULL AND "Carte de sortie du contrôle modèle" IS NULL AND "Compresseur à air marque" IS NULL AND "Compresseur à air modèle" IS NULL AND "Filtre à air marque" IS NULL AND "Filtre à air modèle" IS NULL AND "Filtre à huile marque" IS NULL AND "Filtre à huile modèle" IS NULL AND "Courroie" IS NULL AND "Moteur compresseur HP" IS NULL AND "Moteur compresseur Volts" IS NULL AND "Moteur compresseur RPM" IS NULL AND "Moteur compresseur Frame" IS NULL AND "Contrôle de bas niveau deau marque" IS NULL AND "Contrôle de bas niveau deau modèle" IS NULL AND "Contrôle de très bas niveau deau marque" IS NULL AND "Contrôle de très bas niveau deau modèle" IS NULL AND "Contrôle de haut niveau deau marque" IS NULL AND "Contrôle de haut niveau deau modèle" IS NULL AND "Haute limite marque" IS NULL AND "Haute limite modèle" IS NULL AND "Opérateur marque" IS NULL AND "Opérateur modèle" IS NULL AND "Contrôle modulation marque" IS NULL AND "Contrôle modulation modèle" IS NULL AND "Interrupteur de pression dair marque" IS NULL AND "Interrupteur de pression dair modèle" IS NULL AND "Superviseur de flamme marque" IS NULL AND "Superviseur de flamme modèle" IS NULL AND "Programmeur marque" IS NULL AND "Programmeur modèle" IS NULL AND "Amplificateur marque" IS NULL AND "Amplificateur modèle" IS NULL AND "Détecteur de flamme marque" IS NULL AND "Détecteur de flamme modèle" IS NULL AND "Valve de sureté #1 marque" IS NULL AND "Valve de sureté #1 modèle" IS NULL AND "Valve de sureté #1 pression" IS NULL AND "Valve de sureté #1 capacité" IS NULL AND "Valve de sureté #1 dimension" IS NULL AND "Valve de sureté #2 marque" IS NULL AND "Valve de sureté #2 modèle" IS NULL AND "Valve de sureté #2 pression" IS NULL AND "Valve de sureté #2 capacité" IS NULL AND "Valve de sureté #2 dimension" IS NULL AND "Valve de sureté #3 marque" IS NULL AND "Valve de sureté #3 modèle" IS NULL AND "Valve de sureté #3 pression" IS NULL AND "Valve de sureté #3 capacité" IS NULL AND "Valve de sureté #3 dimension" IS NULL AND "Valve de sureté #4 marque" IS NULL AND "Valve de sureté #4 modèle" IS NULL AND "Valve de sureté #4 pression" IS NULL AND "Valve de sureté #4 capacité" IS NULL AND "Valve de sureté #4 dimension" IS NULL AND "Valve de sureté #5 marque" IS NULL AND "Valve de sureté #5 modèle" IS NULL AND "Valve de sureté #5 pression" IS NULL AND "Valve de sureté #5 capacité" IS NULL AND "Valve de sureté #5 dimension" IS NULL AND "Fusibles du moteur du ventilateur" IS NULL AND "Fusibles de la metering pump" IS NULL AND "Fusibles du réchaud électrique" IS NULL AND "Fusibles du transformateur de contrôle" IS NULL AND "Démarreur du moteur du ventilateur marque" IS NULL AND "Démarreur du moteur du ventilateur modèle" IS NULL AND "O/L modèle" IS NULL AND "O/L Range" IS NULL AND "Transformateur de contrôle" IS NULL AND "DateInspection" IS NULL AND "Ouverte"=@P25 AND "DateNettoyage"=@P26 AND "InspecteurEstFaxe"=@P27 AND "FkContrat" IS NULL AND "ANettoyer"=@P28 AND "AnneeInspection" IS NULL', N'@P1 nvarchar(1),@P2 int,@P3 nvarchar(10),@P4 nvarchar(31),@P5 nvarchar(1),@P6 nvarchar(25),@P7 nvarchar(15),@P8 datetime,@P9 nvarchar(1),@P10 bit,@P11 bit,@P12 bit,@P13 bit,@P14 bit,@P15 bit,@P16 bit,@P17 bit,@P18 bit,@P19 bit,@P20 bit,@P21 nvarchar(14),@P22 nvarchar(9),@P23 nvarchar(6),@P24 bit,@P25 bit,@P26 datetime,@P27 bit,@P28 bit', NULL, 867, N'SODELCO...', N'140827 CANADA INC., ED. SODELCO', N'7', N'740, rue Galt Ouest, RC12', N'Sherbrooke (Qc)', 'juin 29 2005 11:22:00:000A', N'1', 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, N'CLEAVER BROOKS', N'CB196-125', N'S44374', 0, 1, 'juin 21 2005 12:00:00:000A', 0, 1

Need I say more??

Christi SSC-Addicted Points: 402 More actions · Answer 14

Yes, I see...thanks for the tip..I will pass that on to the developer. I do not see how that has anything to do with my server coming to a halt when I install a service pack..but a great tip nontheless.