Here is the list of selected articles of
SQLAuthority.com across all these years. Instead of just listing all the
articles I have selected a few of my most favorite articles and have
listed them here with additional notes below it. Let me know which one
of the following is your favorite article from memory lane.
Special Note: Very last link in the blog post is one of my most favorite performance tuning trick.
2006
In year 2006 I started to blog and
honestly I had no idea what is the blogging? It was just a collection of
the bookmarks and I had a great time writing them up. I always thought I
will read it when I need them. Today when I often read my old blog post
– I feel nostalgic and also realize that I have improved a lot
technically as well professionally. One of the blog posts which I wrote
regarding how to truncate log file got
quite popular with DBA and Developers who got issues with growing log
files. My solution was not perfect as it was breaking the chain of log,
leading to create issues with point in time restore. In SQL Server 2008
the method I demonstrate to truncate the log was replaced. I wrote
following blog post regarding how to truncate the log file in SQL Server 2008 and later version.
2007
The cursor is the most interesting
subject of the database. I have never seen any concept more abused as
cursor in the history of SQL Server. However, there are few cases where
set theory fails and the cursor is the only right solution. In this blog
post I have demonstrated how to write a very fundamental cursor. Here
is the updated the post where I wrote cursor with AdventureWorks Database.
2008
How many times we have wondered what were
the last few queries ran on SQL Server? Following quick script
demonstrates last ran query along with the time it was executed on SQL
Server 2005 and later editions.
Here is the image which displays evolution of man.
2009
Identifying longest
running query is extremely important for any server because it consumes
very valuable server resources. I quickly wrote down a script which
produced the longest running queries in SQL Server. A performance tuning
expert can identify the queries and further tune them. The query was
also giving divide by zero error once in a while, so I wrote a following
blog post where I removed the bug of Divide by Zero for Longest Running Query.
In SQL Server sometime there are
required when a T – SQL script has to wait for some time before
executing next statement. It is quite common that developers depend on
applying to take over this delay issue. However, SQL Server itself has
very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.
Every developer is a victim of the
situation where they have run a query on an incorrect server or changed a
configuration where they did not intend to. In SQL Server 2008 there is
a special feature which can change the color of the task bar. This will
alert the developer to run queries on the server.
sqlcmd has all the feature which osql has to offer, additionally sqlcmd has many added features than osql. isql was introduced in earlier versions of SQL Server. osql was introduced in SQL Server 2000 version. sqlcmd is newly added in SQL Server 2005 and offers additionally functionality which SQL Server 2005 offers.
2010
This is a straight script when executed we know where are the data files stored in SQL Server.
In year 2010 I had the fantastic
opportunity to present in front of 1100 people at Infosys. The
presentation was done at the place where the screen was as big as a big
screen.
When a non-clustered index is created
without any option the default option is IGNORE_DUP_KEY = OFF, which
means when duplicate values are inserted it throws an error regarding
duplicate value. If the option is set with syntaxIGNORE_DUP_KEY = ON
when duplicate values are inserted it does not throw an error but just
displays a warning. This is an interesting blog post where I experiment
with this T-SQL property.
2011
The year 2010 was a fantastic year in my
career I had a great time traveling many countries for business trips
and have learned a lot from various cultures. Additionally this provided
me an opportunity to meet with local community and share learnings.
- Singapore (twice)
- Malaysia (twice)
- Sri Lanka (thrice)
- Nepal (once)
- United States of America (twice)
- United Arab Emirates (UAE) (once)
Multiple small Virtual Log Files commonly
known as VLFs together make an LDF file. The writing of the VLF is
sequential and resulting in the writing of the LDF file is sequential as
well. This leads to another talk that one does not need more than one
log file in most cases. Understanding the VLF is very important and at
times if the growth of the VLF is impacting performance it should be
reduced as well.
Performance Tuning is quite interesting
and Index plays a vital role in it. A proper index can improve the
performance and a bad index can hamper the performance. If you should
not create all the missing indexes this script suggest. This is just for
guidance. You should not create more than 5-10 indexes per table. If
you should not drop all the unused indexes this script suggests. This is
just for guidance.
Performance Tuning Consultancy should be
called Performance Tuning Practice as there is never end of learning new
thing. I had earlier learned this interesting trick which I would like
to call everybody to pay attention to learn. There are cases and
scenarios where the data is so much in the database it is impossible to
take a backup and restore them on the other server to practice out few
of the performance tuning tricks. If this is the scenario you face, you
can just take a backup of the schema and the statistics. Once statistics
are created along with the schema, without data in the table, all the
queries will work as how they will work on the production server. This
way, without access to the data, we were able to recreate the same
scenario as production server on the development server.
2012
My Book co-author Vinod Kumar writes his
perspective behind interview processes: Positioning yourself is close to
marketing yourself in this industry. Productivity is one of the assets
which management always loves to hear. Look at areas where you have made
process improvements and you are using the tools to the maximum
(Developer tools or Office productivity tools). This will sell at any
level – from developer productivity to operational productivity: you
surely are increasing your chances in getting qualified for the next interview cycle.
Jan 2012 was a quiz month and we had a
great time asking interesting questions and have received fantastic
responses. Do you know the answers of the questions – if yes, fantastic
if no, you still have time to go over them and refresh your memory.
- Importance of ANSI ISOLATION Levels in SQL Server Database – Quiz – Puzzle – 1 of 31
- Significance of Various Kinds of Triggers- Quiz – Puzzle – 2 of 31
- Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31
- An Important Part of Most SELECT statement – WHERE clause – Quiz – Puzzle – 4 of 31
Reference: Pinal Dave (http://blog.sqlauthority.com)
No comments:
Post a Comment