Saturday, February 16, 2019

SQL Saturday 282 - T-SQL Basics: Coding for performance

A great experience!
Thanks to all the atendess to my session about T-SQL, for being my first time as a speaker for a SQL Saturday it was good!

As I promised, the presentation and session material is available at the following links:

SQLSaturday #282 site:
(Please evaluate my session if you attend)

https://www.sqlsaturday.com/828/Sessions/Details.aspx?sid=87912


My personal GitHub:

https://github.com/Epivaral/Scripts/tree/master/T-SQL%20Basics%20coding%20for%20performance

Some pictures from the event:



SQL Server local users group board!




Monday, February 11, 2019

Quick tip: Speeding up deletes from SSIS execution log

If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.

If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow.

A proper deletion process must be put in place, so you not get into situations like this one in your msdb database:



If you are already on this situation, you can the following T-SQL Script to delete records by batches:


DECLARE @date_del datetime,
  @batch_size int = 1000, -- will delete on batches of 1000 records
  @RowsAffected int =1

-- Time to keep in the history, in our case 1 month
SET @date_del= DATEADD(mm,-1,getdate()); 

SET NOCOUNT ON;

WHILE (@RowsAffected >0)
BEGIN
 DELETE TOP(@batch_size) 
 FROM [dbo].[sysssislog]
 WHERE starttime < @date_del;

 SET @RowsAffected = @@ROWCOUNT;

 -- If you want to know rows affected, uncomment this:
 -- PRINT @RowsAffected;
END

SET NOCOUNT OFF;


After that you can implement the same query to your msdb maintenance job to have all in one place.

Tuesday, February 5, 2019

I am speaking at SQLSaturday Guatemala 2019




I’m very thrilled to announce that I will be participating as speaker in this year’s SQL Saturday #828 event in Guatemala city!
This will be my first time as a speaker on a SQLSaturday.

Event will take place on February 16 at Universidad Francisco Marroquin, Calle Manuel F. Ayau (6 Calle final), zona 10, Guatemala

Here are the details of the session I will be presenting (at 3:15 PM CST in Dev Room)

T-SQL Basics: Coding for performance


It is very common in the IT field for a developer to switch to database developer or administrator, even when the programming concepts are the same, the skillset required to code T-SQL is different.
In this session, we will learn some basic tips to improve our code and improve database performance from early application planning stages to already deployed applications.

We will also see some demos about:
  • Compatibility level and deprecated features
  • Filtering basics: SARGABLE arguments
  • Covering indexes
  • Indexed views
  • Implicit conversions
  • Memory Grants
  • Joining records with NULL 
  • DMOs to find top resource intensive queries
  • Collation: considerations when working with multiple databases

I will show you execution plans using an excellent tool called Plan Explorer from SentryOne, best thing is that is free.
You can download it from here


As any SQL Saturday event organized by SQLPass you can register for free, it takes less than 5 minutes to get in and sign up.:

https://www.sqlsaturday.com/828/registernow.aspx


Hoping to see you there!