SQL SERVER – Right Aligning Numerics in SQL Server Management Studio (SSMS)

SQL Server Management Studio is my most favorite tool and the comfort it provides to user is sometime very amazing. Recently I was retrieving numeric data in SSMS and I found it is very difficult to read them as they were all right aligned. Please pay attention to following image, you will notice that it is not easier to read the digits as we are used to read the numbers which are right aligned.

I immediately thought before I go for any other tricks I should check the query properties. I right clicked on query properties and I found following option. I checked option Right align numeric values and it just worked fine.

Do you have any other similar tricks which do you practice often. I prefer to also include column headers in the result set as it gives me proper perspective of which column I have selected.

Sometime little tips like this helps a lot in productivity, I encourage you to share your tips. I will publish it with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Partition Parallelism Support in expressor 3.6

I am very excited to learn that there is a new version of expressor’s data integration platform coming out in March of this year.  It will be version 3.6, and I look forward to using it and telling everyone about it.  Let me describe a little bit more about what will be so great in expressor 3.6:

  • Greatly enhanced user interface
  • Parallel Processing
  • Bulk Artifact Upgrading

The User Interface

First let me cover the most obvious enhancements. The expressor Studio user interface (UI) has had some significant work done. Kudos to the expressor Engineering team; the entire UI is a visual masterpiece that is very responsive and intuitive. The improvements are more than just eye candy; they provide significant productivity gains when developing expressor Dataflows.

Operator shape icons now include a description that identifies the function of each operator, instead of having to guess at the function by the icon.

Operator shapes and highlighting depict the current function and status: Disabled, enabled, complete, incomplete, and error. Each status displays an appropriate message in the message panel with correction suggestions.

Floating or docking property panels provide descriptive tool tips for each property as well as auto resize when adjusting the canvas, without having to search Help or the need to scroll around to get access to the property.

Progress and status indicators let you know when an operation is working.

“No limit” canvas with snap-to-grid allows automatic sizing and accurate positioning when you have numerous operators in the Dataflow. The inline tool bar offers quick access to pan, zoom, fit and overview functions.

Selecting multiple artifacts with a right click context allows you to easily manage your workspace more efficiently.

Partitioning and Parallel Processing

Partitioning allows each operator to process multiple subsets of records in parallel as opposed to processing all records that flow through that operator in a single sequential set. This capability allows the user to configure the expressor Dataflow to run in a way that most efficiently utilizes the resources of the hardware where the Dataflow is running. Partitions can exist in most individual operators. Using partitions increases the speed of an expressor data integration application, therefore improving performance and load times. With the expressor 3.6 Enterprise Edition, expressor simplifies enabling parallel processing by adding intuitive partition settings that are easy to configure.

Bulk Artifact Upgrading

Bulk Artifact Upgrading sounds a bit intimidating, but it actually is not and it is a welcome addition to expressor Studio. In past releases, users were prompted to confirm that they wanted to upgrade their individual artifacts only when opened. This was a cumbersome and repetitive process. Now with bulk artifact upgrading, a user can easily select what artifact or group of artifacts to upgrade all at once.

As you can see, there are many new features and upgrade options that will prove to make expressor Studio quicker and more efficient.  I hope I’m not the only one who is excited about all these new upgrades, and that I you try expressor and share your experience with me.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Download Free eBook – Introducing Microsoft SQL Server 2012

Database Administration and Business Intelligence is indeed very key area of the SQL Server. My very good friend Ross Mistry and Stacia Misner has recently wrote book which is for SQL Server 2012. The best part of the book is it is totally FREE!

Well, this book assumes that you have certain level of SQL Server Administration as well Business Intelligence understanding. So if you are absolutely beginner I suggest you read other books of Ross as well attend Pluralsight course of Stacia Misner. Personally I read this book in last 10 days and I find it very easy to read and very comprehensive as well.

Part I Database Administration (by Ross Mistry)

1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

Part II Business Intelligence Development (by Stacia Misner)

6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services

Here are various versions of the eBook.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Transcript of Learning SQL Server Performance: Indexing Basics – Interview of Vinod Kumar by Pinal Dave

Recently I just wrote a blog post on about Learning SQL Server Performance: Indexing Basics and I received lots of request that if we can share some insight into the course.

Here is 200 seconds interview of Vinod Kumar I took right after completing the course. We have few free codes to watch the course, please your comment at http://facebook.com/SQLAuth and we will few of first ones, we will send the code.

There are many people who said they would like to read the transcript of the video. Here I have generated the same.

Pinal: Vinod, we recently released this course, SQL Server Indexing. It is about performance tuning. So tell me – how do indexes help performance?

Vinod: I think what happens in the industry when it comes to performance is that developers and DBAs look at indexes first.  So that’s the first step for any performance tuning exercise, indexing is one of the most critical aspects and it is important to learn it the right way.

Pinal: Correct. So what you mean to say is that if you know indexing you can pretty much tune any server and query.

Vinod: So I might contradict my false statement now. Indexing is usually a stepping stone but it does not lead you to the end. But it’s good to start with indexing and there are lots of nuances to indexing that you need to understand, like how SQL uses indexing and how performance can improve because of the strategies that you have made.

Pinal: But now I’m confused. First you said indexes are good, and then you said that indexes can degrade your performance.  So what is this course about?  I mean how does this course really make an impact?

Vinod: Ok -so from the course perspective, what we are trying to do is give you a capsule which gives you a good start. Every journey needs a beginning, you need that first step.  This course is that first step in understanding.

This is the most basic, fundamental course that we have tried to attack. This is the fundamentals of indexing, some of the key things that you must know about indexing.   Some of the basics of indexing are lesser known and so I think this course is geared towards each and every one of you out there who wants to understand little bit more about indexing.

Pinal: So what I understand is that if I enrolled in this course I will have a minimum understanding about indexing when dealing with performance tuning.  Right?

Vinod: Exactly. In this course is we have tried to give you a nice summary. We are talking about clustered indexing, non clustered indexing, too many indexes, too few indexes, over indexing, under indexing, duplicate indexing, columns tune indexing, with SQL Server 2012. There’s lot’s to learn.

Pinal: You can see the URL [http://bit.ly/sql-index] of the course on the screen. Go ahead, attend, and let us know what you think about it. Thank you.

Vinod: Thank you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video

Today’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Speed up! – Parallel Processes and Unparalleled Performance.

There are always special cases when it is about SQL Server. There are always few queries which gives optimal performance when they are executed on single processor and there are always queries which gives optimal performance when they are executed on multiple processors. I will be presenting the how to identify such queries as well what are the best practices related to the same.

In this quick video I am going to demonstrate if the query is giving optimal performance when running on single CPU how one can restrict queries to single CPU by using hint OPTION (MAXDOP 1).

More on Errors:
Difference Temp Table and Table Variable – Effect of Transaction
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – #TechEdIn – Presenting Tomorrow on Speed Up! – Parallel Processes and Unparalleled Performance at TechEd India 2012

Performance tuning is always a very hot topic when it is about SQL Server. SQL Server Performance Tuning is a very challenging subject that requires expertise in Database Administration and Database Development. I always have enjoyed talking about SQL Server Performance tuning subject. However, in India, it’s actually the very first time someone is presenting on this interesting subject, so this time I had the biggest challenge to present this session.

Frequently enough, we get these two kind of questions:

  1. How to turn off parallelism as it is reducing performance?
  2. How to turn on parallelism as I want more performance?

The reality is that not everyone knows what exactly is needed by their system. In this session, I have attempted to answer this very question. I’ve decided to provide a balanced view but stay away from theory, which leads us to say “It depends”. The session will have a clear message about this towards its end.

Deck Details

  • Slides: 45+
  • Demos: 7+ Bonus
  • Quiz: 5
  • Images: 10+
  • Session delivery time: 52 Mins + 8 Mins of Q & A

I have presented this session a couple of times to my friends and so far have received good feedback. Oftentimes, when people hear that I am going to present 45 slides, they all say it is too much to cover. However, when I am done with the session the usual reaction is that I truly gave justice to those slides.

Action Item

Here are a few of the action items for all of those who are going to attend this session:

If you want to attend the session, just come early. There’s a good chance that you may not get a seat because right before me, there is a session from SQL Guru Vinod Kumar. He performs a powerful delivery of million concepts in just a little time.

Quiz. I will be asking few questions during the session as well as before the session starts. If you get the correct answer, I will give unique learning material for you. You may not want to miss this learning opportunity at any cosst.

Session Details

Title: Speed Up! – Parallel Processes and Unparalleled Performance (Add to Calendar)

Abstract: “More CPU, More Performance” – A  very common understanding is that usage of multiple CPUs can improve the performance of the query. To get a maximum performance out of any query, one has to master various aspects of the parallel processes. In this deep-dive session, we will explore this complex subject with a very simple interactive demo. Attendees will walk away with proper understanding of CX_PACKET wait types, MAXDOP, parallelism threshold and various other concepts.

Date and Time: March 23, 2012, 12:15 to 13:15

Location: Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

Add to Calendar

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video

Today’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Misconception and Resolution.

Quite often I have seen people getting confused with certain behavior of the T-SQL. They expect SQL to behave certain way and SQL Server behave differently. This kind of issue often creates confusion and frustration. Sometime I have seen them also confusing it with bug and submitting the bug, where reality is totally different. Similar concept which are going to see today. I have seen quite commonly developer assuming that table various will be rolled back when transaction is rolled back. This sixty seconds video describes that table various are not rolled back when transactions are rolled back.

More on Errors:
Difference Temp Table and Table Variable – Effect of Transaction
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

Reference: Pinal Dave (http://blog.sqlauthority.com)