SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

Hi Guys, Today a short midweek post!   We will talk about the composite index and statistics . Nothing particularly profound, but any information we can learn, I'm sure will be useful in the future. Enjoy!   Composite index What is a composite index?  Well, a composite index is simply an index that defined on more than one column . For example. Upon the following table ORDTES CREATE TABLE ORDTES (Id INT IDENTITY (1,1) PRIMARY KEY CLUSTERED , DOC_YEAR int , DOC_NUMBER int , CUSTOMER_NAME varchar (80) );   We can define an index on both the columns DOC_YEAR, DOC_NUMBER: CREATE INDEX IDX_ORDTES_DOC_YEAR_DOC_NUMBER ON ORDTES (DOC_YEAR, DOC_NUMBER)     This means that if I search for a specific pair of DOC_YEAR, DOC_NUMBER values I should arrive directly with a seek of the searched record. Is this true? Yes, but let's see more in detail by taking a look at the statistics ... we will find out interesting things .. Statistics Statistics are critic...

Hi Guys,  I hope you had a happy Easter! Today, as we often do, we talk about slowdowns .   To be honest, one of the questions I get asked most often is this: "I have a Query that works fine for me and is very fast but when I try it from on customer’s server it is very slow! ...Why?" To answer this question, let's think first of all of everything that changes from your environment to that of the customer:  everything !   The data Are you querying the same data ?  SQL Server Optimizer creates the execution plan based on the "data it finds to read" (and store the execution plan inside the cache plan for performance reason.) So suppose you have taken a copy of the customer data via a backup and therefore have the same data. Are you already okay? No, absolutely! What you need is to have the same statistics and the same index fragmentation . You will therefore understand the first reason why the tests made on your PC are not very significant. Tests must be done...

Hi Guys, I would say a big thanks to Jeff Moden who have commented my previous post SQL Server, statistics, performance and the Ascending key problem . He made me think more deeply about the statistics and so i decided to do this second post just to clarify and add some thoughts. Enjoy the reading!   Statistic, cardinality and indexes From the point of view of returning a correct cardinality estimate value (and therefore having a precise execution plan), reading my previous post in the example paragraph, we referred to the existence of a clustered index on the table. It should be noted that it does not matter whether there is a clustered index or a not clustered one . I will demonstrate this with an example.   Example Suppose we create a heap table (therefore without any index) Suppone our table will have an id field and a value field. Now let's fill our table with 100 rows by putting a value from 1 to 100 in both the id and value fields. Create Table Example (id in...

Hi guys, September is coming and it is time for our editorial staff to reopen its doors. I hope you have recharged your batteries to 100%. Today we will talk about statistics and performance . Yes, we have talked about it many times before but today we will focus on an issue that could lead to a poor performance situation .  Maybe your query is poor performing due to this issue? Ready? Go! Statististics and the Ascending Key Problem Statistics need to be updated because they help SQL server create a good execution plan. I like to do an example by making a parallel between extracting data from the database and a trip: if i have to go to work and my office is near me it is better to take a bicycle. If my work is far from me it is better take the car or the train. Likewise, if I need to extract a few records, the optimizer can implement a JOIN by choosing a nested loop. Otherwise it would be better to use other operators such as merge or hash. This is why statistics are important. Be...

Hi Guys! Here the post of today i wrote in one go. So don't care about the punctuation, please. In the last post we talked about Cardinality Estimation with a short introduction to the statistics. Before moving forward i would like to tell you some words about how they work and expecially about a problem that can arise with them called the "Ascending Key Problem" So, are you ready for another deep dive? GO! Statistics and Automatic update of statistics. I will try to explain an important concept through the example below. Suppose to have an already populated table (called listofdata) with statistics up to date. The structure of our table is: Create table listofdata (id integer, data datetime, value float) Create index idx_listodata_data on listofdata(data) Watching associated statistics for the index we see that we have 1000 rows each with the same value in the field data. dbcc show_statistics (listofdata, 'idx_listo...