As a DBA you will encounter processes, code and design decisions within your environment that require change for the better. The most common driver for such change is performance improvement. Oftentimes the changes you wish to drive will require design and implementation by someone other than yourself. Proposing changes(improvements) to an authors existing code/process/design can [...]
Posts under ‘Performance Tuning’
The Politics of SQL Server Performance
Like most DBA’s I’m sure you often find yourself delivering information to the business concerning the performance of your SQL Server platform but have you ever stopped to consider the wider implications of your actions? As a SQL Server Database Administrator you are responsible for the overall health and well being of your SQL Server [...]
The Definitive Introduction to SQL Server Indexes
It normally is not my style to write a blog post solely about a particular article someone else has published. Typically I just go ahead and add quality resources to the SQL Server Resources area of this site that I have either found myself or had recommended to me. In this case however an exception [...]
SQL Server Index Defrag Script: Update
Michelle Ufford (aka SQLFool) has released an update to her excellent index optimisation script. If you are not already using something similar to simplify your index optimisation database maintenance processes then you will find this to be extremely useful. Check it out here: Index Defrag Script, V3.0 Michelle Ufford (aka SQLFool) has released an update [...]
Generate a random number in SQL Server
What is the best way to efficiently generate a random number in SQL Server? One of my favourite SQL Server authors, Michelle Ufford recently posted her method for generating a random number, in the post Random Number Generator in T-SQL. This got me thinking and I decided to do a little investigation of my own [...]
Performing fast SQL Server delete operations
There have been quite a few queries on the forums this past week with regard to performing fast delete operations on large tables and I wanted to share some of the possible solutions here with you. Fast ordered delete using a view To perform a fast ordered delete, take a look at the article Fast [...]
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 [...]
Whitepaper: Troubleshooting Performance Problems in SQL Server 2008
No doubt you already know that I love nothing more than getting my hands on excellent technical resources for SQL Server. Well hot off the press is the latest Whitepaper offering from Microsoft that looks at troubleshooting performance problems in SQL Server 2008. Naturally you may already be familiar with some of the techniques and [...]
The SQL Server Buffer Pool and Buffer Cache Hit Ratio
There seems to be some confusion surrounding Buffer Cache Hit Ratio and the SQL Server Buffer Cache. Having answered several queries concerning this over the last few weeks on a variety of forums I thought I would post here in order set the record straight. What is the SQL Server Buffer Cache? The Buffer Cache [...]
