[dba-SQLServer] sql performance monitoring

[Jim Lawrence]

My knowledge on the latest versions of MS SQL is limited. 

In the past the things I attended to was, never use pass-through queries (at times they can be excessively slow and unstable), only in rare occasions posted sql code directly from the Access FE to MS SQL (just passed variables and used SPs (stored procedures)...very much faster, more security and they never fail), always use ADO when connecting to the server (ADO has and is built into every Windows version from 2000 to Win10) instead of DAO (ADO is more stable, flexible and faster in a relatively fluid network environment...and you can mix and match; DAO local and ADO remote) and set up your SPs to monitor how many records are returned on any one request...if a million records are returned Access will surely crash...when using Access 2003-2007, it seems to top out at around 50K. One more item but this may not be a concern; how many connection licences do you have?...I used a system of locking the tables and used a count loop file to manage too many users trying to the access the MS SQL server...the locking looping was so quick no one ever noticed the small pause.

The above described system that ran across all the regions, in the province and could have a hundred users pounding away at any one time. As far as I know, the system never failed. 

I have not been following this thread, so much of what I have added may have already been discussed at length.

Jim 

----- Original Message -----
From: "J- P" <jnatola at hotmail.com>
To: "Discussion concerning MS SQL Server" databaseadvisors.com>
Sent: Wednesday, April 25, 2018 10:26:32 AM
Subject: Re: [dba-SQLServer] sql performance monitoring

it doesn't stop automatically , what I was trying to convey is that once  the Access front end starts "crapping out"  the only immediate remedy is restarting the  SQL instance.







Jean-Paul Natola



________________________________
From: dba-SQLServer databaseadvisors.com> on behalf of Jim Lawrence <accessd at shaw.ca>
Sent: Wednesday, April 25, 2018 12:58 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] sql performance monitoring

Why does the SQL service stop?

Jim

----- Original Message -----
From: "J- P" <jnatola at hotmail.com>
To: "Discussion concerning MS SQL Server" databaseadvisors.com>
Sent: Tuesday, April 24, 2018 4:53:06 PM
Subject: Re: [dba-SQLServer] sql performance monitoring

Correct, Access functions-  one thing i have always noted even when it was on the physical server is that the when ever  you open any job , for example "Joe Blow reception "


You open the job and lets say i want to print/preview proposal, or what equipment is going on the job- ALWAYS at the bottom of the screen in access it says "calculating..................." then it opens- 5,10 even 15 seconds on the average- when things get bad it can take 20-30 or more, or it will crash Access completely -


That's when I get the infamous email or text "Access is acting up" or "We can't work"  then I login , or tell them to login to the server and  "restart the sql service"  then everything returns to normal , at least  by there standards


And when i login the server (before restarting it ) sql maybe at 2gb cpu at like 5%


The hyperv disk is Fixed VHDX




Jean-Paul Natola



________________________________
From: dba-SQLServer databaseadvisors.com> on behalf of fhtapia at gmail.com <fhtapia at gmail.com>
Sent: Tuesday, April 24, 2018 5:24 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] sql performance monitoring


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com