Friday, 24 July 2015

SQL Server Optimization

This post aims to aggregate a lot of the best practice or otherwise recommended configuration for SQL Server performance. It is meant as a summary or quick-reference sheet. All of these concepts are discussed at length all over the internet, so I will not spend much time explaining them. 

As with any recommendations of this sort please use them carefully and test before deploying into a production environment. Also, depending on your specific environment's configuration, not all of the below may be advisable or bring about clear benefits. To quote Jonathan Kehayias: "While there is a lot of good guidance available for how to best configure SQL Server, the specifics of any given implementation is a very big “It Depends…”What this post should achieve is providing the reader with an awareness of features and options that influence a SQL Server instance's operation. 

Tuesday, 26 May 2015

Add an Operator to All SQL Server Agent Alerts

I was investigating a new server I'd been assigned, looking for gaps in configuration using the trusty sp_Blitz tool. The organisation that owns this server instance is small and doesn't use third-party monitoring. I decided to add some basic alerting as recommended by Brent Ozar and Co. They've kindly provided a script to do this: Blitz Result: No SQL Server Agent Alerts Configured.

A little later on I realised that I needed to add an additional operator to these alerts and was loath to step through them one at a time and tick the appropriate box. So, I knocked out a quick and simple script (I've included two versions, one that adds the operator to all alerts and one that allows you to select a subset). I hope it is helpful.

Tuesday, 19 May 2015

Study Materials for MCSE Microsoft Exams 70-457, 70-458, 70-459

/***UPDATE***/
I passed the 70-457! Now studying for 70-458...
/***UPDATE***/

I am studying for the MCSE Certification and will be taking the upgrade from MCITP 2008R8 tests 70-457, 70-458 and 70-459. After trawling the web for study materials I stumbled upon a series of posts on coffeeandsql.com by Carla Abanes. It is a great resource, touching on all the topics covered by the 70-457 exam. I've collated the links below:

Review Materials for 70-458

Exam 70-458 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012

Review Materials for 70-457

70-457 Reviewer #01, Rank Functions
70-457 Reviewer #02, What is Columnstore Index?
70-457 Reviewer #03, Planning an Installation of SQL Server 2012
70-457 Reviewer #04, Implement a Migration Strategy
70-457 Reviewer #05, Configuring SQL Server 2012 Components
70-457 Reviewer #06, Configuring and Managing Database in SQL Server 2012
70-457 Reviewer #07, Resolving Concurrency Problems in SQL Server 2012
70-457 Reviewer #08, Implementing Clustered Instance in SQL Server 2012
70-457 Reviewer #09, Auditing SQL Server 2012
70-457 Reviewer #10 Managing SQL Server Agent
70-457 Reviewer #11 Configuring and Maintaining a Backup Strategy
70-457 Reviewer #12 Restoring SQL Server Databases
70-457 Reviewer #13 Managing SQL Server Logins
70-457 Reviewer #14 Database and Application Roles
70-457 Reviewer #15 Implement Database Mirroring
70-457 Reviewer #16 Implement AlwaysOn in SQL Server 2012
70-457 Reviewer #17 Database Replication in SQL Server 2012

Friday, 15 May 2015

SQL Login and User for Performance Tuning

I've run into an interesting dilemma: as a new staff member in a security conscious organisation, I can't really get any work done. There is, however, a lot of work a DBA can do in terms of analyzing SQL Server instances right from day one. There are lots of best practice recommendations that can be made that are application or db design agnostic. Alternately, it can be possible to investigate the database design without access to the underlying data itself. With that all in mind, I have put some scripts together that creates login/user permissions and roles that can give a SQL consultant useful, but security minded, access to a SQL instance.

Thursday, 14 May 2015

Database Backup Monitoring

Not everyone has access to off-the-shelf monitoring tools. One of my clients is a very small shop with only a few databases and 2 database servers. But that doesn't mean they don't need monitoring. Over the past few weeks I've been implementing various system alerts and now I've deployed the backup monitoring stored procedure below.

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.

Friday, 17 April 2015

Tracking Page Splits

As a DBA, performance tuning is a regular ongoing task. In my experience it is both a reactive and proactive task. The goal, though, is for your proactive efforts to minimise the time spent on the reactive. This has lead me to focus more on page splitting and index fill factor. Specifically, their interaction. There is a lot of information on the web about the benefits and dangers of fill factor and the performance implications of high rates of page splitting, so I will not go into it too much. I will discuss what I've done to monitor page splits. I then tweak fillfactor on the worst offending tables and indexes.

Thursday, 9 April 2015

Running DBCC CHECKDB on TEMPDB

First, a confession. I've been doing it wrong for a long time. Only recently have I learned that TempDB should be included in regular CHECKDB maintenance. Ok, now that I've got that off my chest, I can continue.

So now I've been diligently setting up this task on all servers. NB: TempDB is not available within Maintenance Plans, so you'll need to write the tsql in a job. I add it as a second step in my dbcc maintenance plan job.

Wednesday, 8 April 2015

Full List of SQL Server 2014 DMVs

I'm not sure how useful this will be to others, but I keep searching for a single, comprehensive list of all the SQL Server DMVs with short descriptions. I am looking to complete a certification and this should prove handy for studying. This is certainly no replacement for the full descriptions and examples on MSDN, but I thought it worth putting together.

Thursday, 12 February 2015

Get table and row size data

It often happens that you need to know how big a table is. There are many reasons for this: finding which tables have the most rows; understanding which tables tend to grow the fastest; find which table is using the most space.

I found a nice query on stackexchange.com that listed the tables, row counts and data sizes in a database: