SQL Server Performance Blog where milliseconds matter

Hi Guys, Welcome back! Topic of the Day is the TempDB database. We have already mentioned TempDB in some other posts such as: SQL Server & come spostare il database TEMPDB Il TEMPDB e la sua configurazione.. pronti per le Ferie? SQL Server 2019 ed il Memory-Optimized TempDB Metadata Using extended events to track the growth of the physical files that make up the tempDB database I'll show you some details! Ready? Create and extended event In order to show you some aspects of the tempdb database i will intercept the database_file_size_change event. First step I need to create an event session: CREATE EVENT SESSION [Whatis_inside_tempdb] ON SERVER ADD EVENT [sqlserver].[database_file_size_change] ( ACTION ( [sqlserver].[session_id], [sqlserver].[database_id], [sqlserver].[client_hostname], [sqlserver].[sql_text] ) WHERE ( [database_id] = ( 2 ) AND [session_id] > ( 50 ) ) ), ADD EVENT [sqlserver].[databases_log_file_used_size_cha...

Hi Guys, Welcome back! I am getting older, oh yes it's true. My 10 years old son is developing an app for the smartphone. Today he told me that we need to registry to a forecast wheather site in order to get an Api Key. Using this API he will parse the result returned in JSON format. OMG! Proud, i want to go back to the today's topic. Where are SQL Server Jobs stored? Well, such as a clear question deserves an aswer that is equally so! You SQL Server JOB are stored into the MSDB database. The MSDB database is created during the installation procedure. There are tree tables involved. The first table called sysjobs contain the list of your jobs. A second table called sysjobsteps contain for each job a list of its steps. The last table called syscategories contain the category of your job. In an easy way you can use the T-SQL command below to get you list.   SELECT job.job_id, job.notify_level_email, job. name , job. enabled , j...

Hi Guys, Welcome Back! Today another little step towards the machine learning with a first lesseon about the R language.                Introduction Yes, you remember very well! We already talked about SQL Server and the R language some posts ago. In a first post we talked about how to install the machine learning services in SQL Server While in a second post we talked briefly about using R language to read data from SQL Server Now if you want to do another step towards the machine learning you really need to learn a bit of R language. So today I will show some very basic concepts about this really powerful and widely used in data science, the R language. We will se how to do and assignment, how to use array, vector and matrix. Are you Ready? Let's go! A Basic lesson about R! R is a very powerful language and it is also free and expandible through varius library. I suppose you have already installed R and so you a re...

Hi guys, Welcome back! Today a light post about a question that has been asked to me several times. The question is: how to  search for text inside a trigger or a stored procedure ? We have two different ways. A first way using a system view called syscomments: SELECT obj.xtype, text , * FROM sysobjects obj JOIN syscomments com on obj.id = com.id WHERE TEXT like '%text_that_i_am_searching_for%' A second way using a system view called sql_module: SELECT o. name AS Object_Name, o. type_desc , m. definition FROM sys.sql_modules m JOIN sys.objects o ON m. object_id = o. object_id WHERE m. definition Like '%text_that_i_am_searching_for%' ;   Another question is? What differences do we have in using one way instead of the other? Which solution is better? You have to consider that the system view sys.syscomment is obsolete and will be removed sooner or later. So don't use it in production enviroiments. The sys.sql_m...

Hi Guys, Welcome back! Today we talk about SQL Server monitoring speaking about a software produced by redgate, a software company based in Cambridge, England. Redgate's SQL Monitor is not a free software but is a good choice for administratoes to perform Server monitoring, alerting.  Let take a look! Introduction When you need to monitor a huge number of SQL Server installations using a specially developed software is a good choice. I anticipate you the main pros of this products : The easy and clear interface available through a web interface. I like also it's customizable metrics. But now let's play a bit with the Redgate's SQL Monitor thanks to the free 14-day trial version. An easy installation Yes, installation is easy. Just download the executable from the redgate official site at https://www.red-gate.com The executable needs to be runned as administrator: Accept the license agreement e press next button: The program will be installed as a windows service : No...