[dba-SQLServer] sql performance monitoring

Tue Apr 24 11:11:10 CDT 2018

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