[dba-SQLServer] sql performance monitoring

[Stuart McLachlan]

If remote working from Papua New Guinea is OK, I'd be interested :)

Sounds like you need both a front end and back end re-design :(


On 26 Apr 2018 at 16:27, J- P wrote:

> I don't know if this ok to post here, but would anyone be interested
> in, or recommend someone or company,  to get this fixed?
> 
> 
> 
> 
> 
> 
> ________________________________
> From: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> on
> behalf of Stuart McLachlan <stuart at lexacorp.com.pg> Sent: Wednesday,
> April 25, 2018 6:28 PM To: Discussion concerning MS SQL Server
> Subject: Re: [dba-SQLServer] sql performance monitoring
> 
> Absolutely!
> Poor database and query designs causing multiple table scans (that
> incudes the "index supression" lines) will make queries very slow.
> 
> 
> On 25 Apr 2018 at 21:18, J- P wrote:
> 
> > so i tried IDERA sqldoc for 5 minutes (thanks  Francisco)  and you
> > guys hit it on the head,  it would appear to me (and please correct
> > me if I'm wrong) that I now have evidence that  it is not
> > hardware/VM/server config  causing poor performance.
> >
> >
> >        Missing   index on [CLIENTDB_NAME].[dbo].[ScheduleItems] An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[Inventory].[InvenConflict] to data
> >        type
> >         "tinyint" may be causing index suppression Duplicate   index
> >        found on [CLIENTDB_NAME].[dbo].[JobHeader] Duplicate   index
> >        found on [CLIENTDB_NAME].[dbo].[JobHeader]       Single   use
> >        Ad-hoc plans are using 92.1 MB of procedure cache
> >          Index   [SchedEmpID] on
> > [CLIENTDB_NAME].[dbo].[ScheduleEmployees] is disabled
> >        The   function "like" on column
> >        [CLIENTDB_NAME].[dbo].[Contacts].[Company] may be causing a
> >        table scan       The   function "like" on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleItems].[JFunction] may be
> >        causing a table scan An   implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Inactive] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[Inventory].[InvenHideOnLoadSheet] to
> >        data type   "tinyint" may be causing index suppression An
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[Inventory].[InvenDisplayItem] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Inactive] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Driver] to data
> >        type
> >          "tinyint" may be causing index suppression An   implicit
> >        conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Inactive] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[Contacts].[Hide] to data   type
> >        "tinyint" may be causing index suppression An implicit
> >        conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Inactive] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Inactive] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[ScheduleEmployees].[Inactive] to data
> >        type   "tinyint" may be causing index suppression An  
> >        implicit conversion on column
> >        [CLIENTDB_NAME].[dbo].[Inventory].[InvenObsolete] to data
> >        type
> >         "tinyint" may be causing index suppression
> >
> >
> >
> >
> >
> >
> >
> >
> > From: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> on
> > behalf of Jim Lawrence <accessd at shaw.ca> Sent: Wednesday, April 25,
> > 2018 4:32 PM To: Discussion concerning MS SQL Server Subject: Re:
> > [dba-SQLServer] sql performance monitoring
> >
> > Just a note, and this observation is at least a decade old; UDFs
> > seemed to slow down performance and for that reason, at one point, I
> > never used them. OTOH, newer version of MS SQL may have optimized
> > these functions by loading them in  memory. Oracle had that feature
> > where an admin person could choose to keep certain functions loaded
> > in memory or just make them sticky...
> >
> > Jim
> >
> > ----- Original Message -----
> > From: "Arthur Fuller" gmail.com>
> > To: "Discussion concerning MS SQL Server" databaseadvisors.com>
> > Sent: Wednesday, April 25, 2018 10:11:16 AM Subject: Re:
> > [dba-SQLServer] sql performance monitoring
> >
> > One other thing concerning Access functions, including UDFs. Many
> > Access functions have SQL equivalents, and most UDFs can be ported
> > to SQL. Performance gains may not be dramatic regarding UDFs,
> > however.
> >
> > Also, has it been considered that the big problem might be the VM?
> > It might be useful to experiment with a dedicated server rather than
> > a VM, on a weekend or sometime when nobody's using the system.
> > Hardware is so inexpensive now that the performance penalty incurred
> > by the VM software may not be worth it.
> >
> >
> >
> > _______________________________________________
> > 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
> 
>