About a year ago, we launched Qure Analyzer V1.0 (previously called Qure Workload Analyzer). Since then, we had thousands of downloads and received a lot of great feedback from the community. We did not rest... Your feedback and bug reports kept us busy improving, fixing and enhancing Qure Analyzer to keep its position as the best workload analysis tool available for SQL Server.
...
Yesterday, at our 118th Israeli SQL Server User Group Meeting, Adi Cohn delivered a session on common performance pitfalls. One of the issues he mentioned is something every DBA and database designer should be aware of. Many applications use overly large size limit for their variable data types. The reasoning in many cases is simply "Why not?" If you have a variable size column and you made the choice to use one of the VAR data types anyway, why limit the size? Even if you expect to have up to 300 characters in the 'comments' column for example, what is wrong with defining it as 3,000? it's just metadata, right? Wrong...
Although physically, storage will be exactly the same if you define your VARCHAR as 300 or 3,000, it can still make a huge impact on performance. SQL Server needs to estimate the work space that is required for each query and allocate it before the query begins to execute. For variable type columns, the estimation is that the data size will be 1/2 of the max size defined for the column. So if you have a column that is defined as 300 bytes, the allocation estimation will be 150 but for 3,000 - the estimate will be 1,500. On systems that experience memory pressure, this can make a huge difference in performance as your queries will wait for memory allocations, potentially significantly hurting performance in addition to the fact that needless memory will be allocated that could serve SQL Server for much more useful purposes.
...An interesting discussion came up lately on the MVP private forums regarding index rebuilds, recompilation and statistics updates. Although the debate spanned too many aspects for me to cover here in this short post, I think that one important point should be emphasized as it might be making you do redundant work or even worse - unknowingly degrade the performance of your workload.
When you rebuild an index, a full scan of all index keys is performed. SQL Server uses this opportunity not only to rebuild the index, but also to update the related statistics. What is unique about this statistics update is the fact that it is equivalent to an update with the full-scan option. The default statistics sampling uses only a small fraction of the keys in order not to load your production server. A statistics based on a full scan of the data has the potential to be more accurate, therefore provide the query optimizer with better information, allowing it in turn to produce better performing execution plans for your workload queries. Also remember that a statistics update flushes all plans that use it from the procedure cache forcing a recompilation of the related queries.
...
SQL Server 2012 brings with it so many new features and enhancements that it is futile to try and sum it up. Many articles and blog posts have been and will be written on the shiny new HA/DR, T-SQL, Columnstore and batch processing and numerous others. In this short post, I want to draw your attention to some less ‘shiny’ work that will affect your first interaction with SQL Server 2012 – the installation process.
We would like to thank everyone who participated in the Qure Analyzer New iPad raffle. We have received a lot of great entries, but unfortunately there can only be two winners.
And the winners are:
...The long expected announcement has finally come...
Partners and customers can already download the evaluation version, GA to begin ~ April 1st.
...A big thank-you to the thousands of SQL Server professionals that have downloaded Qure Analyzer, our free tool for analyzing and comparing trace files. Every day we hear of really cool Qure Analyzer success stories. But we’d love to hear more – and we’re giving away a brand new iPad3 (yes, THREE) in the process!
So tell us how Qure Analyzer has helped you, and you’ll be entered into the March 31st raffle.
...This week, Microsoft is hosting the annual MVP Summit in Redmond - http://www.2012mvpsummit.com/. It's an amazing event where MVPs from all over the world and from all award categories join Microsoft staff for in-depth product and technology sessions, meet the program managers and do a lot of networking and even more so FUN!
I've been fortunate to attend two summits in the past, but this year I won't be going. I've been doing too much travelling recently and I'm coming the US at the end of March to speak at SQL Connections! I will deliver a session about "Physical Join Operators" and on the way back to Israel, I'll stop in London to deliver a session on "Clusters vs. Heaps" in SQL Bits X. Both events are official SQL Server 2012 Launch events in the US and UK respectively.
...Are you experiencing performance bottlenecks? Is this query taking forever to complete? Most DBAs would start their resolution attempts with one of SQL Server’s most powerful features – Indexes. Indexes are such an effective mean that a small change could mean a world of a difference in performance. Most DBAs are also aware that adding indexes will cause some degradation of data modification operations as more indexes need to be updated to reflect the changes. However, few are aware that sometimes adding indexes can be a double-edged sword, with far reaching implications on performance, and not always for the best… This article will help you understand a hidden danger of adding more indexes, that can result in surprising effects on your workload. It tells a story of a production server that was brought to its knees with the addition of a single, innocent looking index.
The SQL Server query optimizer is considered magical by many, even the top professionals. It is an extremely complicated piece of software whose intricate paths and logic are hard to perceive. In this article, the first in a series, We will try to tackle some of the query optimizer’s finer tricks to remove the magical aura and reveal the science underneath.
Writing a query that joins several tables is easy, right? There are just a few major join types and their syntax is pretty straightforward and clear. So how come such a simple join be the source of so many headaches? Why does one query with a simple JOIN take forever and another, similar one a fraction of a second? If you’ve ever wondered what goes on under the covers and how SQL Server implements your joins, this article will help you get started.
If you’ve ever used SQL Trace, STATISTICS IO or any other SQL Server monitoring, you should know what logical reads mean. Or do you? Logical reads server as one of the primary counters for evaluating a Query’s performance. The rule of thumb says “The lower the number of reads, the better the query performs”. I’m sure you’ve heard it before. But… did you notice that in some cases the exact opposite is true? Did you ever see a query improve dramatically in terms of duration and CPU but have it’s logical reads sky-rocket? If you want to understand the TRUE essence of logical reads and be able to tell when it is really a good indicator of performance, I highly recommend you to read this article.
RC stands for Release Candidate, it’s a much more stable and ‘close to final’ version than the previous CTP versions.
Go get your copy from http://www.microsoft.com/download/en/details.aspx?id=28145
In a previous post, I’ve presented the enhancements to failover cluster instances. In the mean time, Microsoftannounced some radical changes to it’s licensing and editions scheme. In SQL Server 2012, the standard and BI editions will support basic failover clustering of up to 2 nodes.
AlwaysOn Availability Groups
...2 days ago, Microsoft announced the new licensing scheme and available editions for SQL 2012.
In a nutshell:
...Availability is one of the areas where Microsoft have felt a bit behind the competition. In this release, huge amounts of resources have been spent to catch up.
Although i’m about sum it up in 3 short paragraphs, don’t let that fool you. These are major revisions to the product and are of the highest importance. If you ever needed one of those, you’ll know it.
...I’ve recently had the honor of having an article I’ve written being published in one of the most important projects of the SQL Server community IMHO, the MVP Deep Dives II book.
The original MVP Deep Dives book was published in November 2009, you can read more about the original project inthis blog post by Paul Randal, one of the forefathers of this amazing project.
...As you probably know already, earlier this month at the PASS Summit in Seattle, Microsoft has announced the official name for the next release of SQL Server, formerly code named “Denali”.
...
A new SQL Server blog site was born today!
I hope you will find this site as beneficial and interesting at least as much as I intend to.
...