[dba-SQLServer] sql performance monitoring

[J- P]

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.