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.
2007
In this blog post, I wrote a query only, no other words, no explanation and it turned out to be most desired query ever in history of this blog. So far 252 comments and still continuing.
2008
Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer
Though Synonyms are a very important concept and while we move from one SQL Server to another SQL Server (migration) this can be very essential to understand, I have seen very less adoption of this subject. I encourage you to read this blog post and understand what it suggests.
Export Data From SQL Server 2005 to Microsoft Excel Datasheet
Everybody talks about inserting data into SQL Server but there are cases when we need to export data to SQL Server and this blog post talks about the same concept.
Execute Same Query and Statement Multiple Times Using Command GO
This blog post answer very important question – how to execute same code multiple time in succession without doing copy and paste again and again! Well, if you are not familiar with the concepts of the how GO works, it is still not late.
Display Fragmentation Information of Data and Indexes of Database Table
Indexes are considered as a very essential details for performance tuning. They are indeed important but the same index which you think will help you with performance can be deadly if you do not know how to use them or understand the details inside the index. This small blog post attempts to explain you precisely how to identify fragmentation in SQL Server.
Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE
This is not something I will suggest to run every single time but – indeed something should be kept in mind as a tool when needed it. If you drop a Variable-length columns there are cases when we need to reclaim the spaces which they had occupied, it does not happen automatically but we can absolutely manually force it. In this blog post we discuss how this can be performed.
Change Compatibility Level – T-SQL Procedure
During this year, I have worked with many servers where there was a performance problem. Many places I noticed even though they have upgraded the database, it was still running in compatibility mode of earlier version – this is indeed not good. There was should be proper planning and strategy to upgrade the server. This blog post is just one step.
2009
Interesting Observation – Using sqlcmd From SSMS Query Editor
A little known fact that you can run sqlcmd from SSMS as well. Go to SSMS Menu >> Query >> (click on ) SQLCMD Mode and this is enabled. While running this I found one very interesting fact – out of complete batch all the operating system commands runs first and those are followed by SQL commands. The order of SQL and operating system does not matter in resultset.
Find Currently Running Query – T-SQL
If you want to know what are the query server is running currently – here is the script which just does the task.
sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query
If due to a long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to the server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is not possible. Except very extreme situations dedicated administrator connection (DAC) is always successful.
2010
Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior
When we have a situation where we are dealing with INSERT and TRANSACTION, we can see this feature in action. Let us consider an example where we have two tables. One table has all the data and the second table has partial data. If you want to insert all the data from the first table to the second table and insert all nonduplicate values, you can use this feature.
In personal life I have never struggelled on any error as hard as this one. This one error stopped me to do scheduled training at one of the organizations as well. I am now glad that it is out of my way.
Fragmentation – Detect Fragmentation and Eliminate Fragmentation
Two years ago in the same week, I wrote about fragmentation and now I am writing about how to remove the fragementation. There are many way to remove fragementation but there are few ways which can be just absolutely bad. We need to understand that though our intentation is to improve performance – we do not do mistakes which will reduces their performance.
2011
DMV – sys.dm_exec_query_optimizer_info – Statistics of Optimizer
Incredibly, SQL Server has so much information to share with us. Every single day, I am amazed with this SQL Server technology. Sometimes I find several interesting information by just querying few of the DMV. SQL Server keeps the record of most of the operations of the Query Optimizer. We can learn many interesting details about the optimizer which can be utilized to improve the performance of server.
Get File Statistics Using fn_virtualfilestats
Quite often when I am staring at my SSMS I wonder what is going on under the hood in my SQL Server. I often want to know which database is very busy and which database is bit slow because of IO issue. Sometime, I think at the file level as well. I want to know which MDF or NDF is busiest and doing most of the work. Following query gets the same results very quickly.
master Database Log File Grew Too Big
A Puzzling situation where master database had very big log file. It was indeed puzzling because there were no operation happening in mater databases at all.
What was the reason for it – I think you got to read this blog post for interesting story!
Here are few of the interesting points which I discussed in this blog post:
- To understand SQL Server Performance Tuning at a deeper Level
- Going further from Basic BI understanding
- Learning new features SQL Server 2012
2012
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.
- Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31
- Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31
- Indexed Views and Restrictions – Quiz – Puzzle – 7 of 31
- Using RANKING Functions Instead of SQL Looping Logic of Cursor – Quiz – Puzzle – 8 of 31
- Locking, Blocking and Deadlock – Quiz – Puzzle – 9 of 31
- Reasons for Using Output Clause – Quiz – Puzzle – 10 of 31
- Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31
Reference: Pinal Dave (https://blog.sqlauthority.com)
