Boost query performance with avoiding basic mistake with Select statement. Performance Tip – #42

 

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 any function in where clause which applied on table column.

lets understand this by few examples

Suppose your query written as follows

SELECT * FROM person.person WHERE Substring(FirstName,1,1)= ‘K’

OR

SELECT * FROM person.person WHERE LEFT(FirstName,1)= ‘K’

(b ) Avoid implicit conversion:-  Try to provide exact data type to avoid implicit conversion.

For example if we have firstname column in table having data type nvarchar(50) and we are comparing it with Varchar(100) then at the time of query it convert implicitly.

Lets understand with following query

DECLARE @Name AS VARCHAR(50)
SET @Name = ‘K%’
SELECT * FROM Person.Person WHERE FirstName Like @name

When we run above query we get following query plan

implicitConversion

If you see highlighted yellow it shows that query implicitly converted for changing data type

Now above query can be written as follow to avoid implicitly conversion

DECLARE @Name AS NVARCHAR(50)
SET @Name = N’K%’
SELECT * FROM Person.Person WHERE FirstName Like @name

Now when you run above query  you will get following query plan

NoConversion

So if you see there is no extra conversion in above figure.

I hope this small tips help you in  performance.

Thanks

RJ

How to check your SQL Server Performance using new Utility Performance Dashboard Reports ?

Hello friends,
I know after reading the post title you are very excited to know more, even I was excited too to know more for this topic which I like very much.
Microsoft Introduced Performance Dashboard Utility reports which help us in finding the cause of low performance of our SQL Server Database.
You can download the performance Dashboard utility reports setup from below link
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc
When you install this   installer you will find a new folder is created with Performance Dashboard name in tools folder of Microsoft SQL SERVER (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)
Now when you open this folder you will get many RDL files.
Next question is how to use it so here we go.
1) Firstly run the script  with name “setup.sql “ from Performance Dashboard folder (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)


2) Now  open our SQL Server Management studio
3) Open object explorer
4) Right click on the server select reports option  then select standard reports  as shown in below fig


5) Now select Performance_dashboard _main.rdl report
6) When you select this report you will find following screen

Great it means you are able to see performance dashboard report.
The performance dashboard report gives you following result
1) First graph of report shows System CPU utilization (With the help of this you can find how much CPU utilization done by SQL)
2) Just Below the CPU utilization graph you can see current Activity (CPU consumption time, wait time) We can see detail with two parameter either by User Session or User request (When you click on the header of the grid  you will get different report)
3) Below this we have miscellaneous information which will give basic information like number of database currently on server, active trace etc.
4) Waiting log just right side of CPU utilization  graph which shows different wait type for SQL
5) Below that one more tabular grid there which shows historical records of SQL according to different criteria like waits, IO Statics
6) Expensive SQL report with different parameters like BY CPU, BY Duration, BY Logical Reads, BY Logical Writes etc.
Checks out there are many more feature reports.
So with these different reports we can find where we are lacking. Where is the problem?

My personal feeling is the utility is good to finding the performance counter but it will not provide any suggestion and solution to improve performance.
So why are you waiting?
Just download and enjoy the performance dashboard.
Have fun with reports in weekends: D.
Thanks & Regards
Rajat Jaiswal