SQL Server Performance Blog where milliseconds matter

Hi Guys, Welcome back! Today i will show you, briefly, how to read and write data from Oracle directly from your SQL Server installation. Today Key word is linked server !   Are you ready? Yes i suppose! Three steps to access data from Oracle (12c) The first step is installing the Oracle Data Access components that provide the network library required to establish connectivity to the Oracle database. You must download a zip file named ODAC121021Xcopy_x64 from the oracle official site. The installation is easy infact you need only to unzip the files and run as administrator the install.bat file. The installation procedure will create the following dll and exe files: The second step . Open the ODBC Data source Aministrator and press add button in the system DNS. Enter the Oracle datasource name, a description and the user ID. Third step, the most interesting: create a linked server to Oracle . Open the SSMS and locate Server Objects item, then locate Linked Server item and finally...

Hi Guys, Welcome back! First of all thank you for the many visits to my blog: I am really proud of it Moreover more than 3.5K friend passionate are awesome!!! Today in this short post, i will show you a little supertips: Find the table involved in a lock from the Activity Monitor .   Ready? Let'go! Launch the Activity Monitor from the SSMS. From the Activity monitor you can for instance observe SPID locked! You can certainly view the T-SQL command involded! But amongst the many tables used in our Query, what is the table involved? Well pretty Easy! Identify a row in the grid process marked as suspened and watch at the waiting resource column. Now identify the value of the keylock hobtid property .   Finally insert this value in the query below: SELECT o.name, p.* FROM sys.partitions p INNER JOIN sys.objects o ON p. object_id = o. object_id  WHERE p.hobt_id like '7206088044%608' Executing this query you will have this result where then name field is t...

Hi Guys, Welcome back to my SQL Server tech blog Today we talk about UDF or User Defined Function. We'll see why UDFs are slow and mostly how to make them faster! Are you ready? What is an UDF? So, what is an User defined Function? An user defined function is a "procedure" written in T-SQL language. You can define a function through the syntax CREATE FUNCTION. For example: CREATE OR ALTER FUNCTION F_GET_PREZZO (@ARTID INTEGER) RETURNS FLOAT AS BEGIN DECLARE @Prezzo FLOAT SELECT @Prezzo = PREZZO FROM LISTINO WHERE ARTID=@ARTID; RETURN(@Prezzo) END;   Once defined you can use your function inside your query: SELECT F_GET_PREZZO (ARTID) FROM LISTINO UDFs may have none, one o more input parameters and one output parameters. Let's say right away that in SQL Server we can have different types of UDF : Scalar functions that return one simgle value Multi-statement table valued functions also called TVF which...

Hi Guys, Are you ready for another interesting post? Today we will inspect and solve a problem that afflict SQL SERVER 2017 and SQL Server 2019 that cause Slow query performance . Is this also an opportunity to talk about the latest SQL server CU: the CU4   The Upper , Lower and Rtrim... So where is the problem? Imagine to execute a Query that has a filter predicate on a column (e.g. in our example the field "nemail2"). Suppose also using the UPPER, LOWER, or RTRIM functions. In this scenario you may have an inaccurate cardinality estimates and so SQL Server may uses an inefficient execution plan. We already talked about cardinality estimates here: Inside the SQL Server Query Optimizer - part 3 Cardinality estimation   Let's go with our usual example . Take a database with compatibity level 140 or 150. Now filter on a column of nvarchar data type for example: SELECT id FROM Person WHERE nemail LIKE ( N'%@libero.it' ) SELECT id FROM Person W...