Archive for the ‘SQL Server’ Category

I recently come across the situation where my query was running very slow. I written a perfect query with good inner join and with proper condition. Have a look at query and its executing plan.

Query Execution Plan Before indexing

As you can see in the image query takes 1.28 minute to execute successfully. And as you can see in the figure , cause for the problem is Clustered Index Scan on LotDetails Table.

See the image below to get the clear idea about Clustered Index Scan Cost in this query.

Query Execution plan Analysis

What I did was Created Non-clustered index on “SlipID” and added “Active” column into Included Columns.

Create Index On Table

Include columns in index

After doing this when I run my query it took 0 second to execute successfully. And Clustered index scan is now turned into Index seek which is 3% of the total execution cost.

Query Execution Plan After indexing

So use index as and when required to boost the performance of your T-SQL Query.

Please correct me if I am wrong anywhere. 🙂


Read Full Post »

The topic is self explanatory :).

What would you do if you want to generate script that contains data and schema too. To generate script with schema is pretty simple but what about data. Microsoft Database Publishing Wizard is the tool that work with SQL server 2005 to generate that kind of script. Below are the steps to get started.

Step 1
database publishing wizard

Step 2
database publishing wizard

Step 3
database publishing wizard

Step 4
database publishing wizard

Step 5
database publishing wizard

Step 6
database publishing wizard

Step 7
database publishing wizard

Step 8
database publishing wizard

this is the example of database publishing wizard 1.1. I have not used the versions after 1.1 because this version served all my purpose.

Download Microsoft SQL Server Database Publishing Wizard 1.1

if you are sql server 2008 user then you can not use this 🙂 because good news is that sql server 2008 has this wizard by default.

Please follow http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx link for sql server 2008 database publishing.

Share your thought with me !!  :))

Read Full Post »

We all know how to create database, how to create tables etc… But do we know how to improve query performance when your database growing. Imaging the situation when your table will have more than a million rows. Just think what will be the query output time??

I personally working on the application where there are daily around more than 5000 transactions happens. So you can imagine what will be my table size after a few months. When I was writing this article, my table already has around ½ million rows.

I personally feel that this kind of large database will teach you a lot about the real power of SQL Server and best practices.

In my case, the query which was giving me result in less than a second, now taking more than 30 seconds. It’s because of half a million rows in transaction table. That’s the worst thing in terms of performance.

Luckily, I got help from Mr. Pinal Dave, he is Most Valuable Person (MVP) , he is SQL Guru, he is founder of sqlauthority.com, he has written more than 900 blogs till now. He is lot more than that. He is well known personality in the IT world.

He taught me and my of my co-workers, how to use joins, indexes and many more SQL related things. He solved my problem efficiently and also explain me the things he has done on tables. I learn a lot from that and I am confident that I can improve performance of large database.

You can contact me for the SQL Query Optimization related issues.

Read Full Post »