1


I need some help/guidance to improve this stored procedure.

https://www.brentozar.com/pastetheplan/?id=ByTIs1w4X

This is my server configuration which I got from running the sp_blitz. I am running the above stored SP on MyDBOriginal

Priority 1: Backup:

  • Backups Not Performed Recently - Last backed up: never

    • MyDB

    • MyDBOriginal

    • DWConfiguration

    • DWDiagnostics

    • DWQueue

    • master

    • model

    • msdb

  • Full Recovery Model w/o Log Backups

    • MyDB - The 584.00MB log file has not been backed up in the last week.

    • MyDBOriginal - The 456.00MB log file has not been backed up in the last week.

    • DWConfiguration - The 8.00MB log file has not been backed up in the last week.

Priority 1: Reliability:

  • Last good DBCC CHECKDB over 2 weeks old - Last successful CHECKDB: never.

    • MyDB

    • MyDBOriginal

    • DWConfiguration

    • DWDiagnostics

    • DWQueue

    • master

    • model

    • msdb

Priority 10: DBCC Events:

  • DBCC FREEPROCCACHE Ran Recently - The user [email protected] has run DBCC FREEPROCCACHE 8 times between Jul 24 2018 3:30PM and Jul 25 2018 5:05PM. This has bad idea jeans written all over its butt, like most other bad idea jeans.

Priority 10: Performance:

  • DBCC DROPCLEANBUFFERS Ran Recently - The user [email protected] has run DBCC DROPCLEANBUFFERS 13 times between Jul 24 2018 3:24PM and Jul 25 2018 5:05PM. If this is a production box, know that you're clearing all data out of memory when this happens. What kind of monster would do that?

  • Plan Cache Erased Recently - The oldest query in the plan cache was created at Jul 26 2018 11:52AM. Someone ran DBCC FREEPROCCACHE, restarted SQL Server, or it is under horrific memory pressure.

Priority 20: File Configuration:

  • TempDB on C Drive tempdb - The tempdb database has files on the C drive. TempDB frequently grows unpredictably, putting your server at risk of running out of C drive space and crashing hard. C is also often much slower than other drives, so performance may be suffering.

Priority 20: Reliability:

  • User Databases on C Drive

    • MyDB - The MyDB database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.

    • MyDBOriginal - The MyDBOriginal database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.

    • DWConfiguration - The DWConfiguration database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.

    • DWDiagnostics - The DWDiagnostics database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.

    • DWQueue - The DWQueue database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.

Priority 50: DBCC Events:

  • Overall Events - 21 DBCC events have taken place between Jul 24 2018 3:24PM and Jul 25 2018 5:05PM. This does not include CHECKDB and other usually benign DBCC events.

Priority 50: Reliability:

  • Remote DAC Disabled - Remote access to the Dedicated Admin Connection (DAC) is not enabled. The DAC can make remote troubleshooting much easier when SQL Server is unresponsive.

Priority 50: Server Info:

  • Instant File Initialization Not Enabled - Consider enabling IFI for faster restores and data file growths.

Priority 100: Features:

  • Missing Features - SQL 2017 is being used but not Cumulative Update 3. We'd recommend patching to take advantage of increased analytics when running BlitzCache.

Priority 100: Performance:

  • Max Memory Set Too High - SQL Server max memory is set to 2147483647 megabytes, but the server only has 8063 megabytes. SQL Server may drain the system dry of memory, and under certain conditions, this can cause Windows to swap to disk.

Priority 170: File Configuration:

  • System Database on C Drive

    • master - The master database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    • model - The model database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    • msdb - The msdb database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

Priority 200: Informational:

  • Collation is Latin1_General_100_CI_AS_KS_WS - Collation differences between user databases and tempdb can cause conflicts especially when comparing string values

    • DWConfiguration

    • DWDiagnostics

    • DWQueue

Priority 200: Licensing:

  • Non-Production License - We're not the licensing police, but if this is supposed to be a production server, and you're running Developer Edition (64-bit) the good folks at Microsoft might get upset with you. Better start counting those cores.

Priority 200: Monitoring:

  • No Alerts for Corruption - SQL Server Agent alerts do not exist for errors 823, 824, and 825. These three errors can give you notification about early hardware failure. Enabling them can prevent you a lot of heartbreak.

  • No Alerts for Sev 19-25 - SQL Server Agent alerts do not exist for severity levels 19 through 25. These are some very severe SQL Server errors. Knowing that these are happening may let you recover from errors faster.

  • No failsafe operator configured - No failsafe operator is configured on this server. This is a good idea just in-case there are issues with the [msdb] database that prevents alerting.

  • No Operators Configured/Enabled - No SQL Server Agent operators (emails) have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.

  • Not All Alerts Configured - Not all SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.

Priority 200: Performance:

  • cost threshold for parallelism - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.

  • Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.

    • MyDB

    • MyDBOriginal

Priority 240: Wait Stats:

  • No Significant Waits Detected - This server might be just sitting around idle, or someone may have cleared wait stats recently.

Priority 250: Informational:

  • SQL Server Agent is running under an NT Service account - I'm running as NT Service\SQLAgent$SQLSERVER2017. I wish I had an Active Directory service account instead.

  • SQL Server is running under an NT Service account

    • I'm running as NT Service\MSSQL$SQLSERVER2017. I wish I had an Active Directory service account instead.

    • I'm running as NT Service\MSSQLLaunchpad$SQLSERVER2017. I wish I had an Active Directory service account instead.

Priority 250: Server Info:

  • Agent is Currently Offline - Oops! It looks like the SQL Server Agent (SQLSERVER2017) service is Stopped. The startup type is Manual.

  • Default Trace Contents - The default trace holds 361 hours of data between Jul 11 2018 10:16AM and Jul 26 2018 11:54AM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSERVER2017\MSSQL\Log

  • Drive C Space - 243966.00MB free on C drive

  • Drive E Space - 184492.00MB free on E drive

  • Drive F Space - 143192.00MB free on F drive

  • Hardware - Logical processors: 4. Physical memory: 7GB.

  • Hardware - NUMA Config - Node: 0 State: ONLINE Online schedulers: 4 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 20

  • Instance Stacking - Your Server has 2 Instances of SQL Server running. More than one is usually a bad idea. Read the URL for more info

  • Operating System Version - You're running Windows 10 Pro, version 10.0

  • Power Plan - Your server has 2.79GHz CPUs, and is in balanced power mode -- Uh... you want your CPUs to run at full speed, right?

  • Server Last Restart - Jul 24 2018 11:28AM

  • Server Name - DESKTOP-V9JGQ28\SQLSERVER2017

  • Services

    • Service: SQL Full-text Filter Daemon Launcher (SQLSERVER2017) runs under service account NT Service\MSSQLFDLauncher$SQLSERVER2017. Last startup time: not shown.. Startup type: Manual, currently Running.

    • Service: SQL Server (SQLSERVER2017) runs under service account NT Service\MSSQL$SQLSERVER2017. Last startup time: Jul 24 2018 11:29AM. Startup type: Automatic, currently Running.

    • Service: SQL Server Agent (SQLSERVER2017) runs under service account NT Service\SQLAgent$SQLSERVER2017. Last startup time: not shown.. Startup type: Manual, currently Stopped.

    • Service: SQL Server Launchpad (SQLSERVER2017) runs under service account NT Service\MSSQLLaunchpad$SQLSERVER2017. Last startup time: not shown.. Startup type: Automatic, currently Running.

  • SQL Server Last Restart - Jul 24 2018 11:32AM

  • SQL Server Service - Version: 14.0.1000.169. Patch Level: RTM. Edition: Developer Edition (64-bit). Availability Groups Enabled: 0. Availability Groups Manager Status: 2

Priority 254: Rundate:

  • Captain's log: stardate something and something...
Improve this question
5
  • you actual vs estimated read row have some difference. Can you update your statistics and check? Also, remove the index hint and try
    – Biju jose
    Commented Jul 26, 2018 at 7:31
  • The Index hint has been put there on purpose as I want that index to be used. How can I reduce the difference between the actual vs estimated.
    – Saurabh
    Commented Jul 26, 2018 at 7:35
  • does that improve the performance. Can you compare both with index hint and with out?
    – Biju jose
    Commented Jul 26, 2018 at 7:37
  • >>>The Index hint has been put there on purpose as I want that index to be used<<< This index will be used even without hint if there is no any other index that is narrower, your index is already covering and surely narrower than a table itself.
    – sepupic
    Commented Jul 26, 2018 at 7:44
  • Yes I know but I am still testing and hence also have another index which has more covering columns. And I don't want that index to be used. It causes more logical reads.
    – Saurabh
    Commented Jul 26, 2018 at 7:45

1 Answer 1

Reset to default
2

First of all you have index UD_ConversationID_ArrivalDt_RStatus_Type that can be seeked not only for ConversationID but also for ArrivalDate, but you need to rewrite your where condition DATEDIFF(D, M.ArrivalDate, GETDATE()) <= 50 as

M.ArrivalDate >= dateadd(d, -50, getdate())

The second thing is you may need to update statistics as the rows of dbo.Conversation are underestimated at least 70 times.

Improve this answer
3
  • The statistics were last updated on 2nd July. I checked with STATS_DATE . I will try to change the Arrival date condition.
    – Saurabh
    Commented Jul 26, 2018 at 7:43
  • You tagged your post with "stored procedure" but you posted plain code, is this code written "as is" or did you change the query before posting it, maybe your proc had some parameters but you substituted them with constants before posting here?
    – sepupic
    Commented Jul 26, 2018 at 7:47
  • The link will show the actual code that is used in the SP. No, the SP has only 2 parameters userID and companyid. The other parameters are constant in SP as well.
    – Saurabh
    Commented Jul 26, 2018 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.