SQL Server Performance Blog where milliseconds matter

Hi Guys! While in the previous post we talked about finding who's locking our table today i want to show you how to find deadlock! How many times does the customer call you to tell you that they were all blocked? Yeah, today let's talk about how to identify deadlocks! However, we use a method that can trace them in such a way as to avoid being called by the customer when the block occurs . Enjoy the reading! Activate trace flags First of all we need to activate two trace flags through the T-SQL commands below: DBCC TRACEON (1204,-1) GO DBCC TRACEON (1222,-1) GO Now deadlocks will be stored in the SQL Server error log Important: Just remember to disable these trace flag after detected your deadlock. Remember that in this manner these trace flag are enabled until the SQL Server restart Where is located the Error Log You can locate the error log under the SQL Server Agent menu. Look at the image below: Now click on the check hi...

Hi guys, Today i present to you a light post at base level but however very useful! There are questions that come back often. For example: Why doesn't my update come to an end? Why I am not be able to drop an index? The answer is always the same: Something is blocking ! Enjoy the reading! Find who is locking! Have you an update that doesn't come to an end? Are you trying to drop and index but is not possible? Well, your T-SQL statement is blocked by another T-SQL statement . In this case you can run this command: Sp_who2 This command display a list of processes (called SPID). From this list you can identify your process through the command column. In our example we are the SPID 106 and we are executing a DROP INDEX command. Now look at the blkBy (blocked by)) columns. A value in this column is the process id (SPID) of the command that is blocking . So now we konow that is the SPID 90 that is blocking our drop index command. If y...

Hi Guys, Welcome back! Today an exciting post like a detective story. The breath on the neck of our large customer reminds you that it is the end of the month and the administration must proceed with the billing! But SQL Server restarts randomly  From where can we start our investigations? First of all it must be remembered that tracing the cause of this type of error is never trivial. Never! Today I will tell you the procedure that I personally follow! First take a look to the error log : 2019-11-29 10:31:04.12 Server Error: 17311 , Severity: 16, State: 1. 2019-11-29 10:31:04.12 Server SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless ...

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...

Hi guys! After the simplification phase discussed in the last article, today we will speak about another fundamental step called Cardinality Estimation . During this step the optimizer try to predict the number of rows returned by each node of the execution plan. As you can imagine this is a step of primary importance because a good prediction generate an accurate execution plan with a lower processing cost to execute. The statistics In order to estimate the number of rows returned by a query the Cardinality Estimator use so called statistics . By default for each column and each index created, SQL Server create it's relative statistic. But what is a statistic? A statististic for query optimization is a binary large object called BLOBs that contain statistical information about the distribution of values for one o more columns of a table. We can observe statistics through the T-SQL command: DBCC SHOW_STATISTICS ( <TABLE> , <FIELD_NAME> ...

Hi guys, I am back! After the introduction done in the previous article , today we talk about the simplication step of the Query Optimizer. In my opinion, knowing what types of optimization SQL Server might apply is very important in order to write a Query that run Quickly! So, are you ready for the second part of this exciting journey to discover the Query Optimizer? Ready? Go! The Simplification Let now start speaking about the Simplification phase. Simplification is the first step of the optimization pipeline. It's an important step because during this phase the optimizer try to modify the logical tree in order to remove redundancies. Optimizer try also to change the order of the logical operation in order to facilitate later step . We can split the simplification phase into various sub phases that are: Constant Folding Domain simplification Predicate push-down Join simplification Contradiction detection The constant folding During this phase SQL s...