Showing posts with label performance tuning. Show all posts
Showing posts with label performance tuning. Show all posts

Thursday, 15 March 2018

Querying the Execution Plan for Subtree Cost - Take 2

A few weeks ago I shared a query for probing the execution plan for the Estimated Subtree Cost value.

That query was rather simple but it got the job done. In that post I also explained how, although there are several ways of finding missing index suggestions, they were not always very helpful. After posting I was thinking that it would be helpful to combine the two, or at least display the results so that it is easy to determine which execution plans include missing index warnings.

Friday, 19 January 2018

Querying the Execution Plan XML for Subtree Cost

There are quite a lot of great tools and queries available to the DBA that aid in troubleshooting and performance tuning. The list is incredibly long, but I mainly use RedGate's SQL Monitor (which has come on in the last 5 years in leaps and bounds), Glenn Berry's DMV queries, Adam Machanic's sp_whoisactive and Brent Ozar Unlimited's sp_Blitz suite (predominantly sp_Blitz and sp_BlitzIndex). In writing this, I see that Brent Ozar has expanded his offering, so it looks like I have a weekend project ahead of me!

Wednesday, 25 November 2015

Script to Alter File Growth

I recently inherited a large estate of SQL Servers. Over the years, before I started, it grew organically and was managed by an insufficient number of accidental DBAs. As a result many SQL server instances were built and left with their default settings.

One such setting is file growth, which, tends to be set either at a growth rate of 10% or 1MB. Given the number of databases that can be on a single server I wrote a little script to aid me in making the changes.

I realise that every server and database has different requirements. I am using this script to get my databases and servers to a basic level of "best practice" and avoid any performance issues that may arise.

Thursday, 30 April 2015

Tracking Query Plan Changes

I was reading a blog post from Brent Ozar (SQL Server Query Store), which detailed Microsoft's announced future feature in SQL Server that aims to store a history of cached execution plans. The main purpose, it seems, of such a feature is to aid in performance troubleshooting and tuning. If something has changed recently and a query or store procedure begins behaving badly, there will be another tool in the DBA toolbelt to aid in finding what happened. Beyond that I'll let others explain it further.

Sunday, 18 August 2013

DMV Queries Tied to My Custom Index Stats Tables

I've mentioned a set of DMV queries that I use in a previous post on this blog. I've been chopping and changing the base set of queries for quite a while to suit my needs.

I have also written a series of posts describing how to perform index and statistics maintenance on a SQL Server's databases without losing the very important index stats that the queries mentioned above return.

So, in this post I will show you how I've modified a few of the index focused DMV queries to include the maintenance tables where I now store the cumulative index statistics.

Wednesday, 7 August 2013

Defragging Indexes Without Losing Index Stats - Part 5

The stored procedure

/*********************************************************************************
UPDATE: Bug fixes and increased FILLFACTOR control!
*********************************************************************************/
The first 4 posts of this series covered the concepts of recording index usage statistics for performance tuning while continuing to maintain a database’s tables and indexes. They also presented the individual SQL scripts to perform the various parts of that process.  The most recent post put all of those scripts together into a single set of queries that could be run on a single database.
Most of us, however, have more than one database on a SQL Server instance. Therefore, deploying that last script on a large number of databases is a bit messy. Any modifications will be difficult to manage.
In this post I will present a sample execute statement to run the stored procedure. It is designed to be run centrally, and will loop through all the databases in the instance (there is a parameter to include/exclude the system databases). I deployed the SP onto my Admin_DB where I also store the tables that keep the index usage statistics and table maintenance history.

Tuesday, 4 June 2013

Defragging Indexes Without Losing Index Stats - Part 4

Generate UPDATE STATISTICS scripts

In the previous post of this series I provided the script that both determines the indexes that need attention and builds the individual ALTER INDEX scripts. So, now that the indexes have been tended to it’s time to do the same for table and index statistics. 

The update statements created are based on the defrag type carried out on the table as well as a few thresholds to determine whether it is necessary to rebuild the statistics. Those thresholds are:
- (Time passed since last update: >3 days
- And Minimum row count of the table: >=1000 rows
- And Percentage change in number of rows: >20% more or less)
- Or Time passed since last update: >30 days

Script out the indexes in a database

I'm just about to begin a big performance improvement project. I'll be starting with an evaluation of indexes. This will involve synchronizing a development environment's indexes with those that are in live. I need to, however, keep a record of the existing indexes on that dev environment.

So I've written the code below. It will return a table with the object_id, table name, index name, index_id and a create index script. This script includes the drop and create statements for both primary keys and unique constraints. There are quite a few good scripts out in the wider web world, but I needed a few specific things so I just wrote my own.

Thursday, 23 May 2013

Defragging Indexes Without Losing Index Stats - Part 3

In Part 1 and Part 2 of this series I discussed synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine and the script for saving a database's index usage statistics. This part of the series will go into the query that both identifies the indexes that are fragmented and dynamically generates the defrag scripts.

Wednesday, 22 May 2013

Defragging Indexes Without Losing Index Stats - Part 2

In my last post, I introduced the concept of synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine. In this post I will start delving into the individual scripts involved.

UPDATE: The full script (see link at end of post) has been both updated and tested (a bit)
                  I've also augmented the script below to clean up the atblIndexUsageStats table.

As a recap, below are the main steps involved:
  1. Log Index usage stats
  2. Determine which indexes need defragging and generate scripts
  3. Generate update statistics scripts based on step 2
  4. Log defrag operation to maintenance history table
  5. Execute defrag and update stats scripts
  6. Cleanup logging tables (optional)

Sunday, 12 May 2013

Defragging Indexes Without Losing Index Stats - Part 1

Introduction

As part of my regular performance tuning and maintenance schedule I rely heavily on queries that interrogate the DMV sys.dm_db_index_usage_stats. Based on this DMV I can judge whether indexes should be removed, modified or left as they are. See my previous blog post on SQL Server DMVs. I also believe in the regular defragging of indexes in a database (although I recently read a post from Brent Ozar challenging this idea, but if your servers don’t have the RAM required for such caching as his article suggests and/or you'll never get the budget to upgrade, you’ll need to keep reading). 

Tuesday, 12 March 2013

Remove a Cursor From a Stored Procedure - Part 2

Why is it so slow?!?!?
In my last post, I used a stored procedure from a database I manage to illustrate a real world example of converting a cursor into set based SQL. In this post I will look into a the mechanics behind the scenes and explain at least one of the reasons why it is generally unwise to use cursors when a set based query will provide the same results.

Thursday, 7 March 2013

Remove a Cursor From a Stored Procedure - Part 1

A huge problem DBAs encounter with system performance is the ever-present cursor. While there are some scenarios where cursors are unavoidable (or at least difficult to avoid), many instances are down to the developer being more comfortable writing cursors - or some myths as to why a cursor is better. That said, here is a short post highlighting some cursor pros or (non-cons): When are TSQL Cursors the best or only option?

In my organisation, and no doubt many others, the problem stems from the fact that the developers writing SQL are not SQL developers. Rather they are [insert relevant coding language] developers. And in many programming languages processing is best performed on a row-by-row basis. Not so SQL, especially T-SQL.

Monday, 28 January 2013

DMV Starter Pack

A while back I discovered the DMV Starter Pack, a really useful set of queries exploiting the very powerful Dynamic Management Views and Functions in SQL Server 2005 and 2008.

What I've done is add a few bits to the queries I use most often. Mostly the index queries. In the missing and bad indexes queries I added CREATE and DROP statement columns. Obviously, the CREATE statements conform to the naming conventions of my company [IX_tablename_column1name_column2name...], but this is quite easily modified. I also use a fillfactor of 90 by default. I hope this is helpful.