[dba-SQLServer] dba-SQLServer Digest, Vol 159, Issue 1

[David Lewis]

I'm late onto this thread, but without any details on the table structures, the data itself, the indexes, and the udfs, it is hard to give much advice.  Tools like idera can give some indication as to issues, but the canned advice they give should be treated very much as 'trust but verify'.

Having said all that, udfs are query performance killers.  They often seem like a good idea, and when only one or a couple of rows are returned their load is not large, but because they execute FOR EACH ROW EVALUATED, as soon as the data set grows, performance plummets.  That is the first place to look.  More than that, I can't say without a lot more details.


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