This is the 10th episode of memory lane. 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. My favorite articles this week are about cursor and truncate log. 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 – Truncate Log
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
Last Ran Query – Recently Ran Query
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
Find Longest Running Query – TSQL
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.
Time Delay While Running T-SQL Query – WAITFOR Introduction
 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.
Change Color of Status Bar of SSMS Query Editor
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 vs osql – Basic Comparison
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
Find Location of Data File Using T-SQL
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.
Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON
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)
Detect Virtual Log Files (VLF) in LDF and Reduce the Virtual Log Files (VLFs) from LDF file
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.
Missing Index Script and Unused Index Script – Download
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.
Copy Statistics from One Server to Another Server
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
Interview Questions and Answers – Perspectives of an Author
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 chancesin 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 (https://blog.sqlauthority.com)
