[dba-SQLServer] sql performance monitoring

Tue Apr 24 12:22:49 CDT 2018

Here are areas I would check...

How many records are shown on a form? Showing a table on a form (and/or multi join tables in a grid) can easily slow down the application.

The Front End (Access DB) may be the culprit. If all the processing happens on the FE you will need to rewrite queries so that processing happens on the server and only the data needed is sent to the client.

Does the front end have a lot of drop down lookup combo boxes? You might need to limit this (or at least delay when those drop downs are loaded).

How well normalized is the data?

How about indexes on those tables to speed up filtering.

You could have 100's of data requests on any given form that individually are not slow but when you add them up it could amount to major wait time.

Scott Marcus

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of J- P
Sent: Tuesday, April 24, 2018 12:11 PM
To: Discussion concerning MS SQL Server <dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] sql performance monitoring

That is part of the issue the database started as Access 95 or 97 (before I was around) the client , not willing to hire an actual programmer would use an in-house employee to add things to the Access DB, fast forward 10 years (2006 ish) its now up to the 4th "access programmer", and between him and the previous IT vendor decide, time to split Access to an sql back-end (sql express 2000).

Fast forward  its 2014/15 things are slowly added to the database, company grows some, more employees/users etc.. , I take over the IT services with the stipulation that  the database is outside the contract-

Still running on sql express , hardware / software refresh time the programmer states its time to upgrade to full sql as express has the one GB ram limit-

This gets rejected due to the cost-

As more bells and whistles are added things progressively get worse- finally this year they decide they have to bit the bullet and pay for SQL, and as its a virtualized environment they have to buy the 4 core model.

During the upgrade I deploy a sql 2012, restore the DB  the database launches, however, many, many, functions do not work- I am requested to deploy 2008 in 2k compatibility mode.  So now it is running  sql 08 on server 2012 as HyperV guest on  a raid10 ,8GB of ram and I see ZERO performance improvement- mind you express was running on a PowerEdge  860 with a Pentium cpu.

Thanks,

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

I've never used ManageEngine, but I've used soalrwinds, Redgate and Idera... I believe Idera has a free tool you can begin with, it'll run over the DB and provide a result of the poor performing queries.

I think it's a mistake to run the database in the lower compatibility mode unless there is a feature the current developer is supporting, in which case, we are in Sql Server 2012, and he should support the latest version, as bugs with the older compatibility mode will stop functioning.

 There are many faster better features in the latest version of sql server, plus in 2012 you can fully make use of RSCI.. ( https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/
[https://littlekendra.com/wp-content/uploads/2016/02/reads-vs-writes-RCSI.png]<https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/>

How to Choose Between Snapshot and RCSI - by Kendra Little<https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/>
littlekendra.com
SQL Server offers two flavors of optimistic locking for traditional disk-based tables: Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation. They are each great tools to reduce blocking and make applications faster, particularly in databases that have lots of tiny reads and writes that ...

 )

good luck!
Francisco Tapia

On Tue, Apr 24, 2018 at 7:30 AM J- P <jnatola at hotmail.com> wrote:

> Hi all,
>
>
> Client decided to upgrade from sql express to full sql, the database
> runs in 2000 compatibility mode, so it is currently sql2008 running on
> server 2012.
>
>
> Its a hyperV guest  with 1 vcpu and 8gb of ram (5 gb allocated to sql)
>
>
> The database is small (~2gb) it has an Access front end and  there are
> about 40 concurrent users, on multiple occasions the server slows down
> and the only recourse is restarting sql service.
>
>
> I have been tasked with "finding out what is wrong" , I'm not a sql
> admin but having been a network admin for almost 20 years , and having
> setup numerous commercial sql dbs (Great Plains, Share point, Sage) on
> much inferior hardware , with much larger databases I have to believe
> that the problem lies in the DB/FE configuration itself.
>
>
> Of course the in-house "dba" refuses to believe that, and has always
> shot me down when suggesting to get a real dba to come to assess the
> code and make it more efficient, (IMO  it should not 5 to 10 seconds to run a query).
>
>
> I have setup  some perf counters based on what I have read, in
> addition I would like to get a tool(s) specifically made for sql that
> pinpoints where the issue(s) are.
>
>
> I have budget so it does not have to be free, the two I have been
> looking at is Solarwinds and ManageEngine
>
>
>
> Does anyone have a preference or suggestion?
>
>
> TIA
>
>
> _______________________________________________
> 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

NOTICE:  This electronic mail transmission is for the use of the named individual or entity to which it is directed and may contain information that is privileged or confidential.  If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of any information contained herein is prohibited.  If you have received this electronic mail transmission in error, delete it from your system without copying or forwarding it, and notify the sender of the error by replying via email or calling TSS Technologies at (513) 772-7000, so that our address record can be corrected.
Any information included in this email is provided on an “as is” and “where as” basis, and TSS Technologies makes no representations or warranties of any kind with respect to the completeness or accuracy of the information contained in this email.