You may already be aware that I am a big advocate of the SQL Server Dynamic Management Views (DMV’s) and the benifits that they bring to the Database Administrator. They certainly came in handy with a requirement I had recently whereby I needed to identify the IP Address of a specific SQL Server Login and [...]
Posts under ‘SQL Server Tips’
How to identify the most costly SQL Server queries using DMV’s
I was investigating a performance issue recently and so thought I would share the T-SQL query that I created with you. There’s nothing new or magic here, the code snippet simply identifies the top 20 most expensive queries (currently cached) based on the cumulative CPU cost. The query returns both the SQL Text from the [...]
What is SQL Server Parameter Sniffing
Have you ever come across a stored procedure that utilises parameters and performs like a dream the majority of the time but for some reason crawls along on some other occasions? Well the chances are that this is down to SQL Server Parameter Sniffing. I was re-reading through the white paper Batch Compilation, Recompilation, and [...]
SQL Server Memory Configuration, Determining MemToLeave Settings
Determining the appropriate memory configuration for a SQL Server platform is a task that all database administrators are required to perform. It is essential to ensuring that an appropriate level of performance can be provided. I am going to discuss some of the additional memory configuration tweaking that you may wish to undertake so that [...]
SQL Server Stuff Discovered This Week
Virtualisation As you may already know, I am big fan of the work of Brent Ozar, a talented SQL Server professional and online author of excellent quality SQL Server content. I particularly enjoy his podcast series and think the clear, direct style of his presentations are brilliant. You can learn a great deal in a [...]
Identifying Possible Bad Indexes
SQL Server indexes are often promoted as the answer to all of our performance optimisation needs. It may then surprise you to hear that indexes can also be bad for performance. “What are you yapping on about John” I hear you shout. Well let me explain. You see, if the number of writes to a [...]
What is your biggest SQL Server mistake?
You know the one I am talking about. We have all been there at some point. You get that awful feeling of dread as a chilling realisation sets in and you ask yourself, oh my god did that actually just happen. Sure you can laugh about it now though, right. So come on and share [...]
Hidden RBAR: Triangular Joins
No matter how good a T-SQL programmer you think you are, it is always good to revisit the basics every once in a while and to ensure that you keep your high quality coding standards in check. One of my favourite series of articles regarding good T-SQL coding practices is by Jeff Moden over at [...]
Performance Dashboard Report – Datatype Overflow Error
When you try to execute the Performance Dashboard Reports for the first time, you may be presented with an error of the form: “the difference of two datetime columns caused overflow at runtime” This is because the DATEDIFF function returns an int value and once you have a connection that is more than 24 days [...]
