SET XACT_ABORT is one of the hidden gem we can say. It is helpful in many ways.Let me explain by an example suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other …
SQL Performance
If your SQL performance is poor and your query statement is slow then what are the different ways to improve the performance of SQL statement ? How you can boost up the performance?
Why the SQL Server is running slow ? What are the processes running currently on SQL SERVER instance ? Many other like the above which might help us to understand our current SQL Server instance health can be answered by a simple command “sp_who2”. “sp_Who2” is an Undocumented command. You can utilize this command …
One of the good sentence I remember “When someone has teeth he/she is not having nuts and when someone has nuts he/she not having teeth”. Just joke a part. You understand what I mean to say here. If you have the resources then utilize it. One of the most most important aspect in performance …
This is very interesting feature and I recently come to know this awesome feature. Lets understand this by an example below Suppose we have a student table with following structure as shown in below image We have a detail column with VARCHAR(MAX) . Now as it is VARCHAR(MAX) column it may content a …
Problem:- One of the most important question comes in our mind what is the cause of slow SQL SERVER. We always struggle with following questions Which is highly CPU consuming query ? Which is highly RAM consuming query ? Who is blocking the transaction ? and many more other performance dragging questions. Believe me …
This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter. I know above statement is not digestive enough so lets understand this by an example. Suppose , I have a table tblStudent with …
Problem:- My query is slow what are the basic things I can do to get good performance without going for indexes. Solution:- May be this solution help you which I am describing here or it is possible you already aware of it. (a) Avoid function in column :-The most basic tip is avoid …
Dear Friends, One of the really important part of any database is performance. Everyone complaining your system is slow , your database performance is slow but why it is slow ? Most of the time we are not aware what is the source? Which SQL statement creating problem? Lets understand this thing why our SQL …
Dear Friends, To test the performance of a stored procedure we run the stored procedure several time. You have observed when you run the stored procedure first time it took time but when you run next time it is faster. It just because the stored procedure /data is cached. Now , due to this …
Sometimes, we need to understand what exactly going on with particular statement, stored procedure why it is slow? To check performance of statement what exactly going on we also interested how many logical reads, physical reads a particular statement taking. To achieve this we need to write SET STATISTICS IO ON For exampleGOSET STATISTICS IO …