[dba-SQLServer] sql performance monitoring

[Stuart McLachlan]

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
> 
>