Category: Uncategorized

What are few ways to fix locking and blocking in SQL Server?

  1. Have enough indexes to make queries go fast but not so many that they make our queries go slow. That wont fix the 5000 rows problem. SQL server will escalate to table level lock if it needs to access/lock more than 5000 rows.
  2. Keep your transactions as short as possible. That helps but you have modify the query to make that happen.
  3. Last is changing your default isolation level -> RCSI. This is a server level setting and you need account for extra tempdb usage to keep track of all those transactions.

As long as you de-dupe and eliminate the indexes, then you add indexes that are desperately needed to help the queries go faster. Just the above talked point almost eliminates blocking problem.

Typically, you see users have blocking issues, they either had no indexes whatsoever or they had dozens of indexes.

Proxy (1) is not allowed for subsystem “SSIS” and user “xxxUser”

ERROR:

Proxy (1) is not allowed for subsystem “SSIS” and user “xxxUser”. Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.

EXPLANATION:

In fact, the content of the error message is clear. The reason for the error is that Login does not have the right to use the appropriate Proxy account. I got this error when trying to create a Job.

SOLUTION:

1EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N”, @login_name=N’xxxUser’

Connect from R Studio to SQL server

Recently, I have had to assist one of our newly added Data Science team to connect R with SQL server. This is the first time I had to deal with R server.

One of the things I found out was that R studio did not use LDAP for authentication. After much effort, we settled with granting users to access using native SQL authentication.

To install and load the RODBC package, do the following:

  • Open the RStudio console (make sure the R version is at least 3.1.3: If it isn’t, then use the updateR() function)
  • Run the following command: install.packages(“RODBC”)
  • Run the following command: library(RODBC)

You will need to load the following libraries:

library(rstudioapi)
libary(RODBC)

After this, run the following command to connect to the SQL server

con <- DBI::dbConnect(odbc::odbc(),
Driver = “SQLServer”,
Database = “enter_db_name“,
UID = “enter_user_name“,
PWD = rstudioapi::askforPassword(),
Server = “enter_IP_Address“,
Port = 1433)

The rstudioapi::askForPassword function will prompt the user for a password and reduce the need to store passwords in code.

Thats it! After doing this, users were able to connect to SQL server from R studio and run queries.

Credit: https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-

Favorite list of Blogs

https://www.brentozar.com/blog/

https://www.erikdarlingdata.com/blog/

SQL Server Assessment Using SSMS and SSRS

https://www.sqlskills.com/

https://blog.sqlauthority.com/

https://jmmv.dev/2021/04/always-be-quitting.html

https://architecturenotes.co/

Load data directly in SQL Server from a xlsx file, without OLEDB or Excel, using Powershell

SQL Server Performance tuning Check List

Best Practices:

  1. Power Plan – Make sure server is set to High Performance otherwise you have 30% performance loss (CPUz benchmark tool)
  2. Services – Make sure to keep your surface area is as small as possible (Ex: If you don’t use SSRS or SSIS, turn them off and keep the service disabled). The more services are enabled, the memory is used.
  3. Patching – Some of the bug fixes come up with new patches.
  4. Memory –
    • Check Min and Max Memory
    • Multiple Instances
    • Extra Services SSRS, SSIS, SSAS
    • Lock Pages in Memory
    • Set Memory Reservation for your VM on the Host
  5. TempDB
    • Pin to High Performing Disk
    • Multiple Files – 1 file per CPU core up to 8 cores
    • Trace flag – 1118 (mixed events)/1117 (allows filegroup to grow at the same time) pre 2016
    • Heavy Contention in Metadata fixed 2016 CU8 and 2017 CU5
    • Trace Flag 2453 allows table variables to trigger recompile
  6. Parallelism – check MAXDOP and CTFP

7. Instant File Initialization (IFI) – SQL Server Service account to be granted Perform Volume Maintenance Tasks permissions in windows

8. Blocking – Allow Snapshot Isolation & Read committed Snapshot Isolation. Make sure to allow higher storage for TempDB before you turn RCSI ON.

9. Virtual Log Files:

  • A large number of VLFs can slow things down.
  • VLF counts under 300 ideally
  • Shrink files to two VLFs
  • Grow in chunks back to the current size
  • DBCC LOG INFO or sys.dm_db_log_stats (2016 SP2)

10. Make Friends with Network & Storage Admins – Storage could be the problem and network

11. Over Provisioned VMs & Host

  • Too many VMs on One Host
  • What Happens on One VM Now Affects Another
  • VM Over/Under CPU Allocation (Watch SOS_Scheduler_Yields & CPU Ready Time)
  • Thin Provisioning (make sure you are thick provisioned or you make sure you grow the files to the higher end to take up the storage)

12. COMPRESSION

  • Row/Page Compression – More Data in MEMORY
  • Saves Space on Disk
  • Backup Compression: Less Data in STORAGE
  • Costs CPU speed, but worth it

13. STATISTICS

  • All about estimates versus actuals
  • Need to make sure we are keeping them up to date
  • Have a job that keeps stats up to date
  • Use DMV to check if they are updated frequently
  • When you have auto statistics set to ON, it will trigger a stats update. It will have a hiccup in performance. Set that to asynchronous stats update.
  • Trace Flag 2371 (defaulted in 2016).
  • Did you know you can create your own stats?

14. INDEXES

  • Remove UN-Used indexes (wasted Disk IO, disable then delete)
  • Find Missing Indexes (Wasted Read IO, Avoid Duplicates, Don’t just ADD, look at % impact it is going to make. See if you can modify existing to add a column rather than create an extra index)
  • Covering Indexes (Consolidate indexes, specific tuning). Quarterly go through your indexes, script them out, export to excel and look through them.

15. EXECUTION PLANS