[dba-SQLServer] sql performance monitoring

J- P jnatola at hotmail.com
Fri Apr 27 11:57:50 CDT 2018


it seems i don't always get messages, and sometimes i cant post- but anyone interested please email me off list


jnatola at hotmail







________________________________
From: J- P <jnatola at hotmail.com>
Sent: Thursday, April 26, 2018 12:27 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] sql performance monitoring


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



More information about the dba-SQLServer mailing list